Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: ORACLE PAGING PERFORMANCE

Re: ORACLE PAGING PERFORMANCE

From: <sandro.salari_at_gmail.com>
Date: 14 Feb 2006 00:05:00 -0800
Message-ID: <1139904300.441065.199430@o13g2000cwo.googlegroups.com>


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  Total
Waited

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  Total
Waited

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  Total
Waited

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  Total
Waited

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  Total
Waited

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  Total
Waited

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  Total
Waited

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  Total
Waited

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  Total
Waited

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  Total
Waited

    3 user SQL statements in session.
   12 internal SQL statements in session.    15 SQL statements in session.



Trace file: asvz_ora_680_sigraf_ricerche.trc Trace file compatibility: 9.00.01
Sort options: default
       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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US