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