| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Parallel queries slow
Hi,
first our system: Oracle 10.1.0.4 on HP/UX (64 bit), 8GB Ram, 4 processors.
The problem: When I execute a query it takes about 3 seconds when executing it in parallel. When I specify the hint NOPARALLEL it takes only about 0.2 secs.
All tables have been created with the "PARALLEL" keyword, so Oracle automatically determines the best parallel degree when accessing the table.
Is the administrative overhead of parallel queries so big? How can I reduce it?
In order to investigate the problem I used tkprof. On SQL*Plus command prompt I executed the following:
T_ORGMON,ELAPSTIM, SUM(PAX) AS PAX from V_DM_MARKET_ETI4 where T_ORGMON=200511 AND T_ORGCRY='DE' AND T_DSTCRY='AT' group by T_ORGMON,ELAPSTIM) fact order by PAX desc/
Well, and tkprof then produced the following lenghtly report. It seems as if in order to do the parallel query Oracle needs to execute round about 25 "hidden" select statements before executing the final select-statement. No wonder, why it takes so long...
Does anybody have an idea how we can speed up that part? Parallel queries are great for us, because the application is an OLAP warehouse (basically users can "click" their select statements by choosing the proper filter values), thus some queries are fast, while others are very slow. And the slow queries can be speeded up using "parallel", as we have several processors.
I thought that Oracle is intelligent enough to see that in such a case it is better to use just a non-parallel query: the explain plan output shows that Oracle does not expect many rows, so that imho would be an ideal candidate for using a parallel degree of 1 (i.e. nonparallel).
Or am I completely wrong?
Best regards,
Alex
TKPROF: Release 10.1.0.4.0 - Production on Tue Dec 20 11:08:36 2005
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Trace file: advp_ora_8812.trc
Sort options: default
count = number of times OCI procedure was executed cpu = cpu time in seconds executing elapsed = elapsed time in seconds executing disk = number of physical reads of buffers from disk query = number of buffers gotten for consistent read current = number of buffers gotten in current mode (usually for update) rows = number of rows processed by the fetch or execute call ********************************************************************************
alter session set sql_trace = true
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0
0
Execute 1 0.00 0.00 0 0 0
0
Fetch 0 0.00 0.00 0 0 0
0
total 1 0.00 0.00 0 0 0
0
Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 75
select
name,intcol#,segcol#,type#,length,nvl(precision#,0),decode(type#,2,
nvl(scale,-127/*MAXSB1MINAL*/),178,scale,179,scale,180,scale,181,scale,182,
scale,183,scale,231,scale,0),null$,fixedstorage,nvl(deflength,0),default$,
rowid,col#,property,
nvl(charsetid,0),nvl(charsetform,0),spare1,spare2,
nvl(spare3,0)
from
col$ where obj#=:1 order by intcol#
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0
0
Execute 6 0.00 0.01 0 0 0
0
Fetch 51 0.00 0.00 2 20 0
45
total 58 0.00 0.01 2 20 0
45
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 2)
Rows Row Source Operation
------- ---------------------------------------------------
45 SORT ORDER BY (cr=20 pr=2 pw=0 time=1194 us)
45 TABLE ACCESS CLUSTER OBJ#(21) (cr=20 pr=2 pw=0 time=472 us)
6 INDEX UNIQUE SCAN OBJ#(3) (cr=12 pr=0 pw=0 time=98
us)(object id 3)
select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt,
timestamp#,
sample_size, minimum, maximum, distcnt, lowval, hival, density, col#,
spare1, spare2, avgcln
from
hist_head$ where obj#=:1 and intcol#=:2
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0
0
Execute 180 0.03 0.00 0 0 0
0
Fetch 180 0.00 0.05 12 535 0
174
total 361 0.04 0.07 12 535 0
174
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: RULE
Parsing user id: SYS (recursive depth: 4)
select /*+ rule */ bucket, endpoint, col#, epvalue
from
histgrm$ where obj#=:1 and intcol#=:2 and row#=:3 order by bucket
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0
0
Execute 114 0.00 0.00 0 0 0
0
Fetch 114 0.05 0.23 42 342 0
1852
------- ------ -------- ---------- ---------- ---------- ----------
1852
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: RULE
Parsing user id: SYS (recursive depth: 4)
select t.ts#,t.file#,t.block#,nvl(t.bobj#,0),nvl(t.tab#,0),t.intcols, nvl(t.clucols,0),t.audit$,t.flags,t.pctfree$,t.pctused$,t.initrans, t.maxtrans,t.rowcnt,t.blkcnt,t.empcnt,t.avgspc,t.chncnt,t.avgrln, t.analyzetime,t.samplesize,t.cols,t.property,nvl(t.degree,1), nvl(t.instances,1),t.avgspc_flb,t.flbcnt,t.kernelcols,nvl(t.trigflag, 0),
nvl(t.spare1,0),nvl(t.spare2,0),t.spare4,t.spare6,ts.cachedblk,ts.cachehit,
ts.logicalread
from
tab$ t, tab_stats$ ts where t.obj#= :1 and t.obj# = ts.obj# (+)
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0
0
Execute 3 0.00 0.00 0 0 0
0
Fetch 3 0.00 0.00 1 12 0
3
total 7 0.00 0.01 1 12 0
3
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 2)
Rows Row Source Operation
------- ---------------------------------------------------
3 NESTED LOOPS OUTER (cr=12 pr=1 pw=0 time=427 us)
3 TABLE ACCESS CLUSTER OBJ#(4) (cr=9 pr=1 pw=0 time=325 us)
3 INDEX UNIQUE SCAN OBJ#(3) (cr=6 pr=0 pw=0 time=95 us)(object
id 3)
0 TABLE ACCESS BY INDEX ROWID OBJ#(671) (cr=3 pr=0 pw=0 time=71
us)
0 INDEX RANGE SCAN OBJ#(672) (cr=3 pr=0 pw=0 time=57
us)(object id 672)
select
i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags,i.property,
i.pctfree$,i.initrans,i.maxtrans,i.blevel,i.leafcnt,i.distkey,i.lblkkey, i.dblkkey,i.clufac,i.cols,i.analyzetime,i.samplesize,i.dataobj#, nvl(i.degree,1),nvl(i.instances,1),i.rowcnt,mod(i.pctthres$,256),
i.indmethod#,i.trunccnt,nvl(c.unicols,0),nvl(c.deferrable#+c.valid#,0),
nvl(i.spare1,i.intcols),i.spare4,i.spare2,i.spare6,decode(i.pctthres$,null,
null,mod(trunc(i.pctthres$/256),256)),ist.cachedblk,ist.cachehit,
ist.logicalread
from
ind$ i, ind_stats$ ist, (select enabled, min(cols) unicols,
min(to_number(bitand(defer,1)))
deferrable#,min(to_number(bitand(defer,4)))
valid# from cdef$ where obj#=:1 and enabled > 1 group by enabled) c
where
i.obj#=c.enabled(+) and i.obj# = ist.obj#(+) and i.bo#=:1
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0
0
Execute 5 0.03 0.02 0 0 0
0
Fetch 13 0.00 0.00 5 45 0
8
total 19 0.03 0.03 5 45 0
8
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 2)
Rows Row Source Operation
------- ---------------------------------------------------
8 HASH JOIN OUTER (cr=45 pr=5 pw=0 time=7943 us)
8 NESTED LOOPS OUTER (cr=31 pr=3 pw=0 time=794 us)
8 TABLE ACCESS CLUSTER OBJ#(19) (cr=24 pr=3 pw=0 time=653 us)
5 INDEX UNIQUE SCAN OBJ#(3) (cr=10 pr=0 pw=0 time=132
us)(object id 3)
0 TABLE ACCESS BY INDEX ROWID OBJ#(673) (cr=7 pr=0 pw=0
time=86 us)
0 INDEX UNIQUE SCAN OBJ#(674) (cr=7 pr=0 pw=0 time=57
us)(object id 674)
0 VIEW (cr=14 pr=2 pw=0 time=738 us)
0 SORT GROUP BY (cr=14 pr=2 pw=0 time=712 us)
0 TABLE ACCESS CLUSTER OBJ#(31) (cr=14 pr=2 pw=0 time=356 us)
4 INDEX UNIQUE SCAN OBJ#(30) (cr=10 pr=0 pw=0 time=85
us)(object id 30)
select pos#,intcol#,col#,spare1,bo#,spare2
from
icol$ where obj#=:1
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0
0
Execute 8 0.00 0.00 0 0 0
0
Fetch 20 0.00 0.00 0 40 0
12
total 29 0.01 0.01 0 40 0
12
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 2)
Rows Row Source Operation
------- ---------------------------------------------------
12 TABLE ACCESS BY INDEX ROWID OBJ#(20) (cr=40 pr=0 pw=0 time=304
us)
12 INDEX RANGE SCAN OBJ#(40) (cr=28 pr=0 pw=0 time=319
us)(object id 40)
select text
from
view$ where rowid=:1
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
Parse 6 0.01 0.00 0 0 0
0
Execute 6 0.01 0.00 0 0 0
0
Fetch 6 0.00 0.00 1 12 0
6
total 18 0.02 0.00 1 12 0
6
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
0 TABLE ACCESS BY USER ROWID VIEW$ (cr=0 pr=0 pw=0 time=0 us)
********************************************************************************
select parttype, partcnt, partkeycols, flags, defts#, defpctfree,
defpctused,
definitrans, defmaxtrans, deftiniexts, defextsize, defminexts,
defmaxexts,
defextpct, deflists, defgroups, deflogging, spare1, mod(spare2, 256)
subparttype, mod(trunc(spare2/256), 256) subpartkeycols,
mod(trunc(spare2/65536), 65536) defsubpartcnt,
mod(trunc(spare2/4294967296),
256) defhscflags
from
partobj$ where obj# = :1
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
Parse 4 0.00 0.00 0 0 0
0
Execute 4 0.01 0.00 0 0 0
0
Fetch 4 0.00 0.00 0 12 0
4
total 12 0.01 0.01 0 12 0
4
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID PARTOBJ$ (cr=3 pr=0 pw=0 time=65
us)
1 INDEX UNIQUE SCAN I_PARTOBJ$ (cr=2 pr=0 pw=0 time=32
us)(object id 222)
select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.subname,
o.dataobj#,o.flags
from
obj$ o where o.obj#=:1
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0
0
Execute 10 0.00 0.00 0 0 0
0
Fetch 10 0.00 0.00 1 30 0
10
total 21 0.01 0.00 1 30 0
10
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 3)
select intcol#, col# , type#, spare1, segcol#, charsetform
from
partcol$ where obj# = :1 order by pos#
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
Parse 4 0.00 0.00 0 0 0
0
Execute 4 0.01 0.00 0 0 0
0
Fetch 8 0.00 0.00 0 12 0
4
total 16 0.01 0.01 0 12 0
4
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT ORDER BY (cr=3 pr=0 pw=0 time=114 us)
1 TABLE ACCESS BY INDEX ROWID PARTCOL$ (cr=3 pr=0 pw=0 time=78
us)
1 INDEX RANGE SCAN I_PARTCOL$ (cr=2 pr=0 pw=0 time=44
us)(object id 224)
select obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oid$, spare1,
spare2
from
obj$ where owner#=:1 and name=:2 and namespace=:3 and remoteowner is
null
and linkname is null and subname is null
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0
0
Execute 4 0.01 0.00 0 0 0
0
Fetch 4 0.00 0.00 5 16 0
4
total 9 0.01 0.01 5 16 0
4
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
select obj#, dataobj#, part#, hiboundlen, hiboundval, ts#, file#,
block#,
pctfree$, pctused$, initrans, maxtrans, flags, analyzetime,
samplesize,
rowcnt, blkcnt, empcnt, avgspc, chncnt, avgrln, length(bhiboundval),
bhiboundval
from
tabpart$ where bo# = :1 order by part#
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0
0
Execute 1 0.00 0.01 0 0 0
0
Fetch 2 0.02 0.01 4 96 0
26
total 4 0.02 0.03 4 96 0
26
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
26 SORT ORDER BY (cr=41 pr=4 pw=0 time=2771 us)
26 TABLE ACCESS BY INDEX ROWID TABPART$ (cr=12 pr=4 pw=0
time=614 us)
26 INDEX RANGE SCAN I_TABPART$ (cr=3 pr=2 pw=0 time=312
us)(object id 228)
select
type#,blocks,extents,minexts,maxexts,extsize,extpct,user#,iniexts,
NVL(lists,65535),NVL(groups,65535),cachehint,hwmincr, NVL(spare1,0),
NVL(scanhint,0)
from
seg$ where ts#=:1 and file#=:2 and block#=:3
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
Parse 104 0.02 0.00 0 0 0
0
Execute 104 0.00 0.00 0 0 0
0
Fetch 104 0.00 0.00 16 416 0
104
total 312 0.02 0.02 16 416 0
104
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 2)
Rows Row Source Operation
------- ---------------------------------------------------
0 TABLE ACCESS CLUSTER SEG$ (cr=0 pr=0 pw=0 time=0 us)
0 INDEX UNIQUE SCAN I_FILE#_BLOCK# (cr=0 pr=0 pw=0 time=0
us)(object id 9)
select cachedblk, cachehit, logicalread
from
tab_stats$ where obj#=:1
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0
0
Execute 24 0.01 0.00 0 0 0
0
Fetch 24 0.00 0.00 0 24 0
0
total 49 0.01 0.00 0 24 0
0
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 2)
Rows Row Source Operation
------- ---------------------------------------------------
0 TABLE ACCESS BY INDEX ROWID OBJ#(671) (cr=24 pr=0 pw=0
time=250 us)
0 INDEX UNIQUE SCAN OBJ#(672) (cr=24 pr=0 pw=0 time=161
us)(object id 672)
select owner#,name,namespace,remoteowner,linkname,p_timestamp,p_obj#,
nvl(property,0),subname,d_attrs
from
dependency$ d, obj$ o where d_obj#=:1 and p_obj#=obj#(+) order by
order#
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
Parse 3 0.01 0.00 0 0 0
0
Execute 3 0.01 0.00 0 0 0
0
Fetch 3 0.00 0.00 0 9 0
0
total 9 0.02 0.00 0 9 0
0
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
0 SORT ORDER BY (cr=3 pr=0 pw=0 time=115 us)
0 NESTED LOOPS OUTER (cr=3 pr=0 pw=0 time=72 us)
0 TABLE ACCESS BY INDEX ROWID DEPENDENCY$ (cr=3 pr=0 pw=0
time=65 us)
0 INDEX RANGE SCAN I_DEPENDENCY1 (cr=3 pr=0 pw=0 time=54
us)(object id 120)
0 TABLE ACCESS BY INDEX ROWID OBJ$ (cr=0 pr=0 pw=0 time=0 us)
0 INDEX UNIQUE SCAN I_OBJ1 (cr=0 pr=0 pw=0 time=0 us)(object
id 36)
select obj#, dataobj#, part#, hiboundlen, hiboundval, flags, ts#,
file#,
block#, pctfree$, initrans, maxtrans, analyzetime, samplesize,
rowcnt,
blevel, leafcnt, distkey, lblkkey, dblkkey, clufac, pctthres$,
length(bhiboundval), bhiboundval
from
indpart$ where bo# = :1 order by part#
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
Parse 3 0.00 0.00 0 0 0
0
Execute 3 0.00 0.01 0 0 0
0
Fetch 6 0.08 0.09 6 280 0
78
total 12 0.08 0.11 6 280 0
78
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
26 SORT ORDER BY (cr=36 pr=3 pw=0 time=9218 us)
26 TABLE ACCESS BY INDEX ROWID INDPART$ (cr=8 pr=3 pw=0
time=7481 us)
26 INDEX RANGE SCAN I_INDPART$ (cr=2 pr=0 pw=0 time=84
us)(object id 233)
select cachedblk, cachehit, logicalread
from
ind_stats$ where obj#=:1
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0
0
Execute 69 0.00 0.00 0 0 0
0
Fetch 69 0.00 0.00 0 69 0
0
total 139 0.00 0.00 0 69 0
0
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 2)
select order#,columns,types
from
access$ where d_obj#=:1
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
Parse 3 0.00 0.00 0 0 0
0
Execute 3 0.01 0.00 0 0 0
0
Fetch 3 0.00 0.00 0 6 0
0
total 9 0.01 0.00 0 6 0
0
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
0 TABLE ACCESS BY INDEX ROWID ACCESS$ (cr=2 pr=0 pw=0 time=26
us)
0 INDEX RANGE SCAN I_ACCESS1 (cr=2 pr=0 pw=0 time=22 us)(object
id 122)
select col#, grantee#, privilege#,max(mod(nvl(option$,0),2))
from
objauth$ where obj#=:1 and col# is not null group by privilege#, col#,
grantee# order by col#, grantee#
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0
0
Execute 2 0.01 0.00 0 0 0
0
Fetch 2 0.00 0.00 0 4 0
0
total 5 0.01 0.00 0 4 0
0
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 3)
select grantee#,privilege#,nvl(col#,0),max(mod(nvl(option$,0),2))
from
objauth$ where obj#=:1 group by grantee#,privilege#,nvl(col#,0) order
by
grantee#
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0
0
Execute 2 0.00 0.00 0 0 0
0
Fetch 2 0.00 0.00 0 4 0
0
total 5 0.00 0.00 0 4 0
0
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 3)
select con#,obj#,rcon#,enabled,nvl(defer,0)
from
cdef$ where robj#=:1
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0
0
Execute 2 0.00 0.00 0 0 0
0
Fetch 2 0.00 0.00 0 4 0
0
total 5 0.01 0.00 0 4 0
0
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 3)
select
con#,type#,condlength,intcols,robj#,rcon#,match#,refact,nvl(enabled,0),
rowid,cols,nvl(defer,0),mtime,nvl(spare1,0)
from
cdef$ where obj#=:1
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0
0
Execute 2 0.00 0.00 0 0 0
0
Fetch 9 0.00 0.00 0 12 0
7
total 12 0.00 0.00 0 12 0
7
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 3)
SELECT ORA_TQ_BASE$.NEXTVAL
FROM
DUAL
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0
0
Execute 1 0.00 0.00 0 0 0
0
Fetch 1 0.00 0.00 0 0 1
1
total 3 0.00 0.00 0 0 1
1
Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 SEQUENCE (cr=75 pr=3 pw=0 time=39284 us)
1 FAST DUAL (cr=0 pr=0 pw=0 time=8 us)
********************************************************************************
select intcol#,nvl(pos#,0),col#,nvl(spare1,0)
from
ccol$ where con#=:1
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0
0
Execute 7 0.00 0.00 0 0 0
0
Fetch 14 0.00 0.00 0 28 0
7
total 22 0.01 0.01 0 28 0
7
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 3)
update seq$ set
increment$=:2,minvalue=:3,maxvalue=:4,cycle#=:5,order$=:6,
cache=:7,highwater=:8,audit$=:9,flags=:10
where
obj#=:1
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0
0
Execute 1 0.00 0.00 0 1 2
1
Fetch 0 0.00 0.00 0 0 0
0
total 2 0.01 0.00 0 1 2
1
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 2)
Rows Row Source Operation
------- ---------------------------------------------------
1 UPDATE (cr=1 pr=0 pw=0 time=554 us)
1 INDEX UNIQUE SCAN I_SEQ1 (cr=1 pr=0 pw=0 time=36 us)(object
id 101)
select fact.T_ORGMON,fact.ELAPSTIM,fact.PAX
from
( select T_ORGMON,ELAPSTIM, SUM(PAX) AS PAX from V_DM_MARKET_ETI4
where
T_ORGMON=200511 AND T_ORGCRY='DE' AND T_DSTCRY='AT' group by
T_ORGMON,
ELAPSTIM) fact order by PAX desc
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
Parse 1 0.03 0.04 1 6 0
0
Execute 1 0.00 0.15 0 2 0
0
Fetch 2 0.01 0.13 0 13 0
11
total 4 0.04 0.32 1 21 0
11
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 75
Rows Row Source Operation
------- ---------------------------------------------------
11 PX COORDINATOR (cr=15 pr=0 pw=0 time=178621 us)
0 SORT ORDER BY (cr=0 pr=0 pw=0 time=0 us)(Orphan Entry)
0 PX RECEIVE (cr=0 pr=0 pw=0 time=0 us)
0 SORT GROUP BY (cr=0 pr=0 pw=0 time=0 us)(Orphan Entry)
0 PX RECEIVE (cr=0 pr=0 pw=0 time=0 us)
0 HASH JOIN (cr=0 pr=0 pw=0 time=0 us)(Orphan Entry)
0 PX RECEIVE (cr=0 pr=0 pw=0 time=0 us)
0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us)(Orphan Entry)
0 PX BLOCK ITERATOR (cr=0 pr=0 pw=0 time=0 us)
0 TABLE ACCESS FULL DIM_COUNTRIES (cr=0 pr=0 pw=0 time=0 us)
0 INDEX UNIQUE SCAN MONTHS_IDX (cr=0 pr=0 pw=0 time=0 us)(object
id 531784)
0 BUFFER SORT (cr=0 pr=0 pw=0 time=0 us)
0 PX RECEIVE (cr=0 pr=0 pw=0 time=0 us)
11 PARTITION RANGE SINGLE PARTITION: 25 25 (cr=13 pr=0 pw=0
time=2483 us)(Orphan Entry)
11 TABLE ACCESS BY LOCAL INDEX ROWID DM_MARKET_ETI4 PARTITION: 25
25 (cr=13 pr=0 pw=0 time=2437 us)
11 BITMAP CONVERSION TO ROWIDS (cr=12 pr=0 pw=0 time=2314 us)
1 BITMAP AND (cr=12 pr=0 pw=0 time=2289 us)
1 BITMAP MERGE (cr=6 pr=0 pw=0 time=1955 us)
1 BITMAP KEY ITERATION (cr=6 pr=0 pw=0 time=268 us)
1 TABLE ACCESS FULL DIM_COUNTRIES (cr=4 pr=0 pw=0 time=168
us)
1 BITMAP INDEX RANGE SCAN DM_MKT_ETI4_DESTCRY_ID PARTITION:
25 25 (cr=2 pr=0 pw=0 time=87 us)(object id 539284)
1 BITMAP MERGE (cr=6 pr=0 pw=0 time=283 us)
1 BITMAP KEY ITERATION (cr=6 pr=0 pw=0 time=98 us)
1 TABLE ACCESS FULL DIM_COUNTRIES (cr=4 pr=0 pw=0 time=68
us)
1 BITMAP INDEX RANGE SCAN DM_MKT_ETI4_ORGCRY_ID PARTITION:
25 25 (cr=2 pr=0 pw=0 time=23 us)(object id 539257)
select fact.T_ORGMON,fact.ELAPSTIM,fact.PAX
from
( select /*+NOPARALLEL(V_DM_MARKET_ETI4 )*/T_ORGMON,ELAPSTIM, SUM(PAX)
AS
PAX from V_DM_MARKET_ETI4 where T_ORGMON=200511 AND T_ORGCRY='DE' AND
T_DSTCRY='AT' group by T_ORGMON,ELAPSTIM) fact order by PAX desc
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.01 0 0 0
0
Execute 1 0.00 0.00 0 0 0
0
Fetch 2 0.00 0.00 0 16 0
11
total 4 0.02 0.02 0 16 0
11
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 75
Rows Row Source Operation
------- ---------------------------------------------------
11 SORT ORDER BY (cr=16 pr=0 pw=0 time=3507 us)
11 SORT GROUP BY (cr=16 pr=0 pw=0 time=3431 us)
11 HASH JOIN (cr=16 pr=0 pw=0 time=3279 us)
1 NESTED LOOPS (cr=3 pr=0 pw=0 time=123 us)
1 TABLE ACCESS BY INDEX ROWID DIM_COUNTRIES (cr=2 pr=0 pw=0
time=94 us)
1 INDEX UNIQUE SCAN COUNTRIES_IDX (cr=1 pr=0 pw=0 time=56
us)(object id 531766)
1 INDEX UNIQUE SCAN MONTHS_IDX (cr=1 pr=0 pw=0 time=12
us)(object id 531784)
11 PARTITION RANGE SINGLE PARTITION: 25 25 (cr=13 pr=0 pw=0
time=2420 us)
11 TABLE ACCESS BY LOCAL INDEX ROWID DM_MARKET_ETI4
PARTITION: 25 25 (cr=13 pr=0 pw=0 time=2395 us)
11 BITMAP CONVERSION TO ROWIDS (cr=12 pr=0 pw=0 time=2288
us)
1 BITMAP AND (cr=12 pr=0 pw=0 time=2264 us)
1 BITMAP MERGE (cr=6 pr=0 pw=0 time=1940 us)
1 BITMAP KEY ITERATION (cr=6 pr=0 pw=0 time=354 us)
1 TABLE ACCESS FULL DIM_COUNTRIES (cr=4 pr=0 pw=0
time=278 us)
1 BITMAP INDEX RANGE SCAN DM_MKT_ETI4_DESTCRY_ID
PARTITION: 25 25 (cr=2 pr=0 pw=0 time=67 us)(object id 539284)
1 BITMAP MERGE (cr=6 pr=0 pw=0 time=276 us)
1 BITMAP KEY ITERATION (cr=6 pr=0 pw=0 time=105 us)
1 TABLE ACCESS FULL DIM_COUNTRIES (cr=4 pr=0 pw=0
time=79 us)
1 BITMAP INDEX RANGE SCAN DM_MKT_ETI4_ORGCRY_ID
PARTITION: 25 25 (cr=2 pr=0 pw=0 time=20 us)(object id 539257)
alter session set sql_trace = false
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0
0
Execute 1 0.00 0.00 0 0 0
0
Fetch 0 0.00 0.00 0 0 0
0
total 2 0.01 0.00 0 0 0
0
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 75
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
Parse 3 0.06 0.06 1 6 0
0
Execute 4 0.00 0.15 0 2 0
0
Fetch 4 0.01 0.13 0 29 0
22
total 11 0.07 0.35 1 37 0
22
Misses in library cache during parse: 3
Misses in library cache during execute: 1
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
Parse 145 0.10 0.08 0 0 0
0
Execute 568 0.14 0.15 0 1 2
1
Fetch 654 0.15 0.44 95 2028 1
2345
------- ------ -------- ---------- ---------- ---------- ----------
2346
Misses in library cache during parse: 25 Misses in library cache during execute: 24
4 user SQL statements in session.
145 internal SQL statements in session.
149 SQL statements in session.
1 session in tracefile.
4 user SQL statements in trace file.
145 internal SQL statements in trace file.
149 SQL statements in trace file.
29 unique SQL statements in trace file.
2333 lines in trace file.
1 elapsed seconds in trace file.
Received on Tue Dec 20 2005 - 04:30:08 CST
 |
 |