RE: 9.2 V$ views

From: John Kanagaraj <john.kanagaraj_at_hds.com>
Date: Mon, 26 Jul 2004 17:31:43 -0700
Message-ID: <35CFD500D7BDCE43B9030BBA5979DC18015A38AA@ussccem13.corp.hds.com>

>Btw, the dba_extents performance problem you mentioned earlier probably
>comes from the fact that in LMT configuration you can get
>detailed extent
>information only from the segment header itself, this means at
>least one
>consistent get per segment which you're querying. Given that
>lots of segment
>headers aren't in buffer cache, a count(*) on dba_extents may cause
>thousands of physical IO's, recursive calls and latching...

See below for a 'select count(*) from dba_extents' on an Apps database (total of 217,167) extents sitting on 452 datafiles, and the snapshot of V$SESSTAT and V$SESSION_EVENT for that SID. The query too about 13 minutes (dev server, slightly slow disk). Interesting stats to note? Look at 'revursive calls', 'CPU used%', 'cluster key scans' and 'cluster key block gets' stats, 'session logical reads', number of dbfile reads. Most were against the C_FILE#_BLOCK#, UET$, SEG$, SYS_IOT_TOP_132603, I_FILE#_BLOCK#, OBJ$, C_TS#, TS$, FET$ objects among others.

John Kanagaraj <><
DB Soft Inc
Phone: 408-970-7002 (W)

http://tahiti.oracle.com - Manuals for DBAs (English only) http://www.bibleserver.com - Manual for Life (in English, Deutsch, French, Italian, Spanish, Portugese, Turkish,...)

The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers **

EVENT                               TOT_WAITS TOT_TMOUTS TIME_WAITED

AV_WAIT

----------------------------------- --------- ---------- -----------
--------
db file sequential read                 51353          0       53449
1.0408
db file scattered read                  13226          0       16985
1.2842
file open                                 344          0           4
.0116
latch free                                342        318         828
2.4211
SQL*Net message to client                  42          0           0
0
SQL*Net message from client                41          0        4302
104.9268
SQL*Net more data to client                 2          0           0
0
SQL*Net break/reset to client               2          0           0
0
buffer busy waits                           1          0           0
0
log file sync                               1          0           0

10 rows selected.

Session Statistics......

Name
VALUE

logons cumulative
1
logons current
1
opened cursors cumulative
37587
opened cursors current
2
user commits
1
user calls
55
recursive calls
832946
recursive cpu usage
1964
session logical reads
949653
CPU used when call started
10244
CPU used by this session
10244
session uga memory
87892
session uga memory max
17917028
messages sent
175
session pga memory
20911716
session pga memory max
20911716
enqueue requests
40367
enqueue releases
40365
total file opens
344
db block gets
99737
consistent gets
849916
physical reads
154822
db block changes
750
consistent changes
1388
change write time
7
redo synch writes
1
free buffer requested
154933
dirty buffers inspected
35
pinned buffers inspected
5
hot buffers moved to head of LRU
1334
free buffer inspected
67
commit cleanouts
37
commit cleanouts successfully completed
37
CR blocks created
101
current blocks converted for CR
345
switch current to new buffer
2
prefetched blocks
90243
prefetched blocks aged out before use
14
calls to kcmgcs
6
calls to kcmgas
10
calls to get snapshot scn: kcmgss
46294
redo entries
379
redo size
109624
data blocks consistent reads - undo records applied 1388
no work - consistent read gets
680044
rollbacks only - consistent read gets
447
immediate (CURRENT) block cleanout applications 4
deferred (CURRENT) block cleanout applications 15
table scans (short tables)
130
table scans (long tables)
12
table scan rows gotten
635230
table scan blocks gotten
109082
table fetch by rowid
183
cluster key scans
285099
cluster key scan block gets
571231
rows fetched via callback
61
parse time cpu
202
parse time elapsed
982
parse count (total)
37588
parse count (hard)
14
execute count
37856
bytes sent via SQL*Net to client
19409
bytes received via SQL*Net from client
8487
SQL*Net roundtrips to/from client
42
sorts (memory)
270
sorts (rows)
1402
session cursor cache hits
37530
session cursor cache count
8
cursor authentications
9
buffer is pinned count
548161
buffer is not pinned count
680714

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Mon Jul 26 2004 - 19:28:43 CDT