Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: ORACLE PAGING PERFORMANCE
This is the trace:
TKPROF: Release 9.2.0.1.0 - Production on Mon Feb 13 17:30:11 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Trace file: asvz_ora_680_sigraf_ricerche.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 events '10046 trace name context forever, level 12'
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 0 0.00 0.00 0 0 0 0
total 3 0.01 0.00 0 0 0 0
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 655
Elapsed times include waiting on following events:
Event waited on Times Max. Wait TotalWaited
select value$
from
props$ where name = 'GLOBAL_DB_NAME'
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 2 0.00 0.00 0 4 0 1
total 4 0.00 0.00 0 4 0 1
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- --------------------------------------------------- 1 TABLE ACCESS FULL PROPS$ ********************************************************************************
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 2 0.06 0.06 0 0 0 0 Fetch 2 0.00 0.00 0 8 0 2
total 5 0.06 0.06 0 8 0 2
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)
select cols,audit$,textlength,intcols,property,flags,rowid
from
view$ where obj#=:1
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.01 0.01 0 0 0 0 Fetch 1 0.00 0.01 1 3 0 1
total 3 0.01 0.02 1 3 0 1
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait TotalWaited
select owner#,name,namespace,remoteowner,linkname,p_timestamp,p_obj#,
d_owner#, nvl(property,0),subname
from
dependency$,obj$ where d_obj#=:1 and p_obj#=obj#(+) order by order#
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.01 0.01 0 0 0 0 Fetch 2 0.01 0.03 2 8 0 1
total 4 0.03 0.04 2 8 0 1
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- --------------------------------------------------- 1 SORT ORDER BY 1 NESTED LOOPS OUTER 1 TABLE ACCESS BY INDEX ROWID DEPENDENCY$ 1 INDEX RANGE SCAN I_DEPENDENCY1 (object id 106) 1 TABLE ACCESS BY INDEX ROWID OBJ$ 1 INDEX UNIQUE SCAN I_OBJ1 (object id 31) Elapsed times include waiting on following events: Event waited on Times Max. Wait TotalWaited
select order#,columns,types
from
access$ where d_obj#=:1
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 1 0.00 0.02 1 2 0 0
total 3 0.00 0.03 1 2 0 0
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- --------------------------------------------------- 0 TABLE ACCESS BY INDEX ROWID ACCESS$ 0 INDEX RANGE SCAN I_ACCESS1 (object id 108) Elapsed times include waiting on following events: Event waited on Times Max. Wait TotalWaited
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 1 0.00 0.01 0 0 0 0 Fetch 29 0.00 0.00 0 4 0 28
total 31 0.00 0.01 0 4 0 28
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)
select text
from
view$ where rowid=:1
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 2 0 1
total 3 0.00 0.00 0 2 0 1
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- --------------------------------------------------- 1 TABLE ACCESS BY USER ROWID VIEW$ ********************************************************************************
select node,owner,name
from
syn$ where obj#=:1
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 3 0 1
total 3 0.00 0.00 0 3 0 1
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)
select sumobj#, inline#, textspos, textlen, instance#
from
suminline$ where hashval=:1 order by inline#
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 1 1 0 0
total 3 0.00 0.00 1 1 0 0
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- --------------------------------------------------- 0 SORT ORDER BY 0 TABLE ACCESS BY INDEX ROWID SUMINLINE$ 0 INDEX RANGE SCAN I_SUMINLINE$_3 (object id 901100) Elapsed times include waiting on following events: Event waited on Times Max. Wait TotalWaited
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.00 0.00 0 0 0 0 Execute 2 0.00 0.00 0 0 0 0 Fetch 2 0.00 0.00 1 8 0 2
total 5 0.00 0.00 1 8 0 2
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 2)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait TotalWaited
select distinct obj#,containerobj#,pflags,xpflags,mflags
from
sum$, suminline$ where sumobj#=obj# and inline#=:1
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0 Execute 2 0.00 0.00 0 0 0 0 Fetch 2 0.00 0.00 1 2 0 0
total 6 0.00 0.00 1 2 0 0
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- --------------------------------------------------- 0 SORT UNIQUE 0 NESTED LOOPS 0 TABLE ACCESS BY INDEX ROWID SUMINLINE$ 0 INDEX RANGE SCAN I_SUMINLINE$_2 (object id 901099) 0 TABLE ACCESS BY INDEX ROWID SUM$ 0 INDEX UNIQUE SCAN I_SUM$_1 (object id 281067) Elapsed times include waiting on following events: Event waited on Times Max. Wait TotalWaited
SELECT *
FROM
(SELECT ROWNUM AS N, a.* FROM (( SELECT /*+USE_NL(nodi) */ nodi.*
FROM
V_MT_NODI nodi,W_MT_NRL linee WHERE 1=1 and linee.IL_LINEA=0 AND
linee.IK_LINEA=16705 AND linee.IM_LINEA=1000 AND nodi.IK_NODO =
linee.IK_NODO ) ORDER BY nodi.COD_ORG,nodi.SER_NODO,nodi.NUM_NODO)a
where
rownum<=20)WHERE N>0
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
Parse 1 0.34 2.11 1 8 1 0 Execute 1 0.00 0.17 0 0 0 0 Fetch 3 0.04 88.33 0 0 0 20
total 5 0.39 90.62 1 8 1 20
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 655
Rows Row Source Operation
------- --------------------------------------------------- 20 VIEW 20 COUNT STOPKEY 20 VIEW 20 SORT ORDER BY STOPKEY 38 NESTED LOOPS 38 REMOTE 38 REMOTE Elapsed times include waiting on following events: Event waited on Times Max. Wait TotalWaited
alter session set events '10046 trace name context off'
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 0 0.00 0.00 0 0 0 0
total 2 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 655
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
Parse 3 0.34 2.11 1 8 1 0 Execute 4 0.01 0.17 0 0 0 0 Fetch 3 0.04 88.33 0 0 0 20
total 10 0.40 90.62 1 8 1 20
Misses in library cache during parse: 1
Elapsed times include waiting on following events:
Event waited on Times Max. Wait TotalWaited
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
Parse 12 0.00 0.00 0 0 0 0 Execute 14 0.09 0.11 0 0 0 0 Fetch 44 0.01 0.09 7 45 0 37
total 70 0.10 0.21 7 45 0 37
Misses in library cache during parse: 2
Elapsed times include waiting on following events:
Event waited on Times Max. Wait TotalWaited
3 user SQL statements in session.
12 internal SQL statements in session.
15 SQL statements in session.
0 session in tracefile. 3 user SQL statements in trace file. 12 internal SQL statements in trace file. 15 SQL statements in trace file. 14 unique SQL statements in trace file. 432 lines in trace file.
Excuse me... Received on Tue Feb 14 2006 - 02:05:00 CST