I would like to know how to calculate blocks, or time remaining for a parallel table copy.
(What's the answer to a previous reviewer's comments below)
Perhaps there is a way to calculate the blocks written which have not yet been committed yet?
TIME_REMAINING not useful for parallel full table scan?
February 11, 2011 - 1:34 am UTC
Reviewer: Kim Berg Hansen from Middelfart, Denmark
Sorry, Tom - I must not have been all clear :-)
Yes, I am aware that Oracle did the math for me in the TIME_REMAINING column of the view. And the TIME_REMAINING can be calculated independent of what the "unit of work" is, since TOTALWORK and SOFAR is the same unit. So the unit could be "lightyears" or "kilometers" - no matter for the TIME_REMAINING column.
My scenario was parallel full table scan with three child processes each doing "chunks" of rowid range scans. The TIME_REMAINING can tell me how long time is approximately left of EACH of the chunks of rows - but it cannot tell me how many "chunks" are left to scan...
What I tried to estimate was this:
I started the scan at 12 noon. After my table scan had been running for half an hour, I selected gv$sesssion_longops for the sids of the three processes. There were 17 entries in gv$session_longops after half an hour. The sum of TOTALWORK of these 17 entries was about 1.6 million and the units said "blocks". The table was 6.5 million blocks, and therefore I estimated that the parallel full table scan must be about 25% done, and the scan would therefore finish at 2 pm having scanned 6.5 million blocks in about 68 "chunks".
But in reality the scan did not finish until 8 pm having rowid range scanned 258 "chunks" and the sum of TOTALWORK of those 258 entries in gv$session_longops was 26 million.
Thus I concluded that the estimate I did at 12:30 pm was terribly wrong :-) And since the factor of 4 immediately stared me in the eye, I took a wild guess that the programmer who wrote the parallel table scan code perhaps was hardcoding blocksize of 2K in his calls to dbms_application_info.set_session_longops? It was the more comfortable guess for me, since I did not wish to believe that Oracle actually scanned each of the 6.5 million blocks 4 times :-)
But is there another way of guesstimating how much work is left for a parallel full table scan using rowid range scans? If I had another way then I would not have to worry about blocksize in longops :-)
Thanks for your time, as always.
So here's a potential option you could explore - I'll start with big table that we can scan in parallel
SQL> create table bigtab as
2 select d.* from dba_objects d,
3 ( select 1 from dual connect by level <= 1000 );
Table created.
SQL>
SQL> select bytes from dba_segments
2 where segment_name = 'BIGTAB';
BYTES
----------
1.3355E+10
SQL>
SQL> select bytes/1024/1024/1024 from dba_segments
2 where segment_name = 'BIGTAB';
BYTES/1024/1024/1024
--------------------
12.4375
SQL> select blocks from user_tables
2 where table_name = 'BIGTAB';
BLOCKS
----------
1622079
Now I'm going to scan it in parallel and see what we can glean from the timings etc
SQL> set timing on
SQL> select /*+ parallel(8) */ owner, sum(object_id)
2 from bigtab
3 group by owner;
...
...
Whilst that was running, I took a look at v$session_longops - here's some sample data
SQL> select * from v$session_longops
2 where username = 'MCDONAC'
3 @pr
==============================
SID : 1109
SERIAL# : 52999
OPNAME : Rowid Range Scan
TARGET : MCDONAC.BIGTAB
TARGET_DESC :
SOFAR : 9853
TOTALWORK : 15568
UNITS : Blocks
START_TIME : 03-FEB-22
LAST_UPDATE_TIME : 03-FEB-22
TIMESTAMP :
TIME_REMAINING : 5
ELAPSED_SECONDS : 7
CONTEXT : 0
MESSAGE : Rowid Range Scan: MCDONAC.BIGTAB: 9853 out of 15568 Blocks done
USERNAME : MCDONAC
SQL_ADDRESS : 00007FFECCABF580
SQL_HASH_VALUE : 364932852
SQL_ID : 6a4r6pnaw0vrn
SQL_PLAN_HASH_VALUE : 3136705186
SQL_EXEC_START : 03-FEB-22
SQL_EXEC_ID : 16777220
SQL_PLAN_LINE_ID : 8
SQL_PLAN_OPERATION : TABLE ACCESS
SQL_PLAN_OPTIONS : FULL
QCSID : 124
CON_ID : 3
==============================
SID : 737
SERIAL# : 17228
OPNAME : Rowid Range Scan
TARGET : MCDONAC.BIGTAB
TARGET_DESC :
SOFAR : 9604
TOTALWORK : 15568
UNITS : Blocks
START_TIME : 03-FEB-22
LAST_UPDATE_TIME : 03-FEB-22
TIMESTAMP :
TIME_REMAINING : 5
ELAPSED_SECONDS : 7
CONTEXT : 0
MESSAGE : Rowid Range Scan: MCDONAC.BIGTAB: 9604 out of 15568 Blocks done
USERNAME : MCDONAC
SQL_ADDRESS : 00007FFECCABF580
SQL_HASH_VALUE : 364932852
SQL_ID : 6a4r6pnaw0vrn
SQL_PLAN_HASH_VALUE : 3136705186
SQL_EXEC_START : 03-FEB-22
SQL_EXEC_ID : 16777220
SQL_PLAN_LINE_ID : 8
SQL_PLAN_OPERATION : TABLE ACCESS
SQL_PLAN_OPTIONS : FULL
QCSID : 124
CON_ID : 3
==============================
...
... (8 rows in total)
...
PL/SQL procedure successfully completed.
As you've pointed out, there is nothing directly in there that says "Hey, this is how long things are going to take"...but we can get some clues using the info that is there.
We can the blocks being scanned by a parallel worker is 15568 ("Rowid Range Scan: MCDONAC.BIGTAB: 9604 out of 15568 Blocks done").
We know that there are 1622079 in the table from USER_TABLES, so from these two things we can deduce:
SQL> select 1622079 / 15568 from dual;
1622079/15568
-------------
104.193153
there will need to be about 104 scans done. The time elapsed / time remaining figures add to 12 seconds (I deliberately put this table on an old IDE drive to make it easy to monitor) which means total scanning time is:
SQL> select 104*12 from dual;
104*12
----------
1248and we have 8 parallel workers going at this concurrently, which means the estimated time is:
SQL> select 1248 / 8 from dual;
1248/8
----------
156So at a guess, we think this will take around 2mins36s to complete. Lets see what really happened:
SQL> select /*+ parallel(8) */ owner, sum(object_id)
2 from bigtab
3 group by owner;
OWNER SUM(OBJECT_ID)
------------------------------ --------------
SYS 2.9489E+12
MYUSER 932761000
DVF 1595229000
ORDSYS 3.5752E+10
...
49 rows selected.
Elapsed: 00:02:53.18
So I think we did pretty well there.
Clearly as queries get more complicated, this math will break down because the results of this group by could easily be the input to a join, which is the input to another aggregation etc etc... and all of that takes time...