I’m creating a temp table with following SQL. Table temp2 is 50GB in size with 120M rows. I noticed during SORT operation , it’s performing lots of single block reads on Temp tablespace. Why it’s doing single block reads on temp??
Also, Is there any other way to make this quicker?
I'm on 10gR2 with Auto PGA/SGA. PGA Aggregate is 5GB and SGA is 12GB.
create table t_temp nologging parallel(degree 4 )
as
select /*+ parallel(t1,4) */ * from temp2 partition(P2008) t1
order by custkey
SID Wait State EVENT P1 P2 P3 SEQ# % TotTime TEventTime(ms) DistEvnts Avgtime(ms)/Evnt
------- ------------- ----------------------------------- -------------------------- --------------- --------------- ---------- --------- -------------- ---------- ----------------
2165 WAITING direct path read temp file number= 5001 first dba= block cnt= 1 .02 .253 1 .253
88791
2165 WAITING direct path read temp file number= 5001 first dba= block cnt= 1 .02 .253 1 .253
412771
2165 WAITING direct path read temp file number= 5001 first dba= block cnt= 1 .02 .253 1 .253
421465
2165 WAITING direct path read temp file number= 5001 first dba= block cnt= 1 .02 .253 1 .253
691141
2165 WAITING direct path read temp file number= 5001 first dba= block cnt= 1 .02 .253 1 .253
1295425
Here is Temp table space properties…
Name INITK NEXTK MINEX MAXEX PCTI CNTS EXMGMT ALOCTYPE SEGMGM STATUS
-------------------- -------- -------- ----- ----- ---- --------- ---------- --------- ------ --------
TEMP 1024 1024 1 0 TEMPORARY LOCAL UNIFORM MANUAL ONLINE
Edited by: user4529833 on Feb 5, 2009 9:05 AM