Skip to Main Content
  • Questions
  • Observing a "create table as select statement"

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Ronald.

Asked: November 22, 2015 - 9:45 pm UTC

Last updated: January 16, 2018 - 3:46 am UTC

Version: 11gR2

Viewed 10K+ times! This question is

You Asked

Hi Tom,

In a customer project we are using quite a lot of "create table as select ...." (cannot change them), that are taking quite a while (some hours).
I would like to observe the growth of these objects, while they are created, but I'm bumping into an issue.

Example:

(tall_objects is a plain "create table tall_objects as select * from all_abjects")

This statement takes a few minutes (a nonsens long running Statement just for demonstrating the problem):

create table o1 as
select * from (
select a.owner,a.object_name,b.last_ddl_time
from tall_objects a, tall_objects b
where b.object_type = 'SYNONYM'
and a.object_type = 'SYNONYM'
) where rownum < 100000000;

from another session I can issue:

SQL> select uss.segment_name Table_Name, bytes/1024/1024 MByte from user_segments uss
2 where uss.segment_name in (
3 select * from (
4 select us.segment_name from user_segments us
5 minus
6 select uo.object_name from user_objects uo
7 )
8 where segment_name not like 'SYS%'
9 and segment_name not like 'BIN%')
10 ;

TABLE_NAME MBYTE
-------------------------------------------------------------------------------- ----------
4.106770 2946

SQL> /

TABLE_NAME MBYTE
-------------------------------------------------------------------------------- ----------
4.106770 3138

SQL> /

TABLE_NAME MBYTE
-------------------------------------------------------------------------------- ----------
4.106770 3202


SQL> /

TABLE_NAME MBYTE
-------------------------------------------------------------------------------- ----------
4.106770 3970

SQL> /

TABLE_NAME MBYTE
-------------------------------------------------------------------------------- ----------
4.106770 4098

SQL> /

TABLE_NAME MBYTE
-------------------------------------------------------------------------------- ----------
4.106770 4802

SQL> /

TABLE_NAME MBYTE
-------------------------------------------------------------------------------- ----------

SQL>
(statement done)

Ah, nice! one can observe the growth of the object "o1" during creation
Q: Do you have any idea to get rid of this ugly "4.106770" but display an "o1" instead (the object beeing created)?

Thx in advance!

and Connor said...

You cannot get rid of the "ugly" object name, because that is indeed the name of the object. It is a temporary seegment that only becomes a "real" one at the point the creation is finished. Here's the same query running from my db (whilst creating the table).

SQL> select
  2    uss.segment_name Table_Name,
  3    segment_type,
  4    bytes/1024/1024 MByte
  5  from user_segments uss;

TABLE_NAME                     SEGMENT_TYPE            MBYTE
------------------------------ ------------------ ----------
6.41434                        TEMPORARY                   0




You can also pick this up from V$SESSION ( to get the SQL_ID) and then V$SQL

SQL> select child_name, sql_fulltext from v$sql where sql_id = '3fpqv6mbh93n6';

CHILD_NUMBER SQL_FULLTEXT
------------ -------------------------------------------------
           0 create table t2 as
             select * from (
             select a.owner,a.object_name,b.last_ddl_time
             from t1 a, t1 b
             where b.object_type = 'SYNONYM'
             and a.object_type = 'SYNONYM'
             ) where rownum < 100000000

SQL> select elapsed_time/1000000, rows_processed, PHYSICAL_WRITE_BYTES
  2  from v$sql
  3  where sql_id = '3fpqv6mbh93n6';

ELAPSED_TIME/1000000 ROWS_PROCESSED PHYSICAL_WRITE_BYTES
-------------------- -------------- --------------------
           23.665873              0           1665826816

SQL> /

ELAPSED_TIME/1000000 ROWS_PROCESSED PHYSICAL_WRITE_BYTES
-------------------- -------------- --------------------
           23.665873              0           1665826816

SQL> /

ELAPSED_TIME/1000000 ROWS_PROCESSED PHYSICAL_WRITE_BYTES
-------------------- -------------- --------------------
           25.665924              0           1803059200

SQL> /

ELAPSED_TIME/1000000 ROWS_PROCESSED PHYSICAL_WRITE_BYTES
-------------------- -------------- --------------------
           27.665987              0           1997897728

SQL> /

ELAPSED_TIME/1000000 ROWS_PROCESSED PHYSICAL_WRITE_BYTES
-------------------- -------------- --------------------
           29.666028              0           2126741504

SQL>
SQL> /

ELAPSED_TIME/1000000 ROWS_PROCESSED PHYSICAL_WRITE_BYTES
-------------------- -------------- --------------------
           65.648563       99999999           5196685312



Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

thx for the quick answer!

A reader, November 23, 2015 - 9:27 am UTC

Ok, it seems that not everything is exposed by some v$ views... i'll use your proposal and modify the display of the monitor script observing the actions.

Thx for the very fast answer!

Helpful

Eric Mason, January 16, 2018 - 3:18 am UTC

This helping me currently to monitor the progress of my CTAS. I appreciate the simple approach.

For my case, my CTAS is

<create table PPCALLS_NEW tablespace rgs parallel 4 nologging as
select /*+parallel(source 4) */ * from ppcalls@DBLINK_HIST;>

<SQL> select elapsed_time/1000000, rows_processed, PHYSICAL_WRITE_BYTES
2 from v$sql
3 where sql_id = 'gkjdh6qb00muu';

ELAPSED_TIME/1000000 ROWS_PROCESSED PHYSICAL_WRITE_BYTES
-------------------- -------------- --------------------
11686.8919 0 7.4215E+10
91674.3629 0 4.1419E+11

SQL> /
>

I would just like to know why there are two statements rather than one?

Thanks,

Eric