>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 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@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------