Thanx Anjo, Can you elaborate on 'values may wrap'?
Regards Naveen -----Original Message----- Sent: Wednesday, November 20, 2002 2:34 PM To: Multiple recipients of list ORACLE-L 1) values may wrap (and there are some obscure bugs that cause negative values) 2) You want to get the block in mode CR and there is actually no work needed (like cleanout or rollback) to get to that mode. Anjo. On Tuesday 19 November 2002 21:34, you wrote: > Hi All, > > There is one report which takes 12 hours to run during the off hours. And > that too on a database 6Gb in size!!!!! The report was designed by the > consultants and all the queries in the report were doing a nested loops > joins on many tables, optimizer mode was RULE. After changing the optimizer > mode to CHOOSE, still no effect. Then I rewrote the report to use joins > instead of 'SELECT a row, run query for that row, then select another row, > run query for that row......'(written in Oracle Reports) the run time came > crashing down to 10 Secs. (Hard to believe!!). > > Now for the question. > > After running the report for a few hours I terminate the report and see the > values in V$sesstat. It is showing me negative values for Consistent gets > etc.... When the report was running i ran the same query on v$sesstat, at > that moment it was showing Consistent gets - 47 million approx. > > 1. Why is it showing negative values? > 2. What does stat 'no work - consistent read gets' mean? > > OUTPUT ONE HOUR AFTER THE REPORT STARTED: > ----------------------------------------- > SQL> SELECT NAME, VALUE FROM V$SESSTAT A, V$STATNAME B WHERE A.SID=16 AND > VALUE != 0 > 2 AND A.STATISTIC# = B.STATISTIC# > 3 ORDER BY VALUE DESC > 4 / > > NAME VALUE > ----------------------------------------------------------- ---------- > session connect time 478385736 > process last non-idle time 478385736 > consistent gets 47024111 > session logical reads 47024106 > no work - consistent read gets 36724753 > buffer is not pinned count 36650911 > table fetch by rowid 36643847 > buffer is pinned count 36569847 > session pga memory 1651312 > session pga memory max 1651312 > session uga memory 1589476 > session uga memory max 1589476 > bytes sent via SQL*Net to client 156588 > CPU used when call started 81035 > CPU used by this session 81035 > bytes received via SQL*Net from client 48012 > sorts (rows) 16390 > table fetch continued row 6650 > SQL*Net roundtrips to/from client 2043 > user calls 2033 > execute count 788 > calls to get snapshot scn: kcmgss 786 > parse count (total) 17 > opened cursors cumulative 12 > table scan blocks gotten 11 > recursive calls 9 > parse count (hard) 9 > db block gets 9 > opened cursors current 6 > enqueue requests 5 > enqueue releases 5 > cursor authentications 5 > parse time elapsed 4 > table scans (short tables) 3 > parse time cpu 2 > logons cumulative 1 > sorts (memory) 1 > logons current 1 > > 38 rows selected. > > STATS 10 HOURS AFTER THE REPORT STARTED RUNNING > ----------------------------------------------- > > SQL> SELECT NAME, VALUE FROM V$SESSTAT A, V$STATNAME B WHERE A.SID=16 AND > VALUE != 0 > 2 AND A.STATISTIC# = B.STATISTIC# > 3 ORDER BY VALUE DESC > 4 / > > NAME VALUE > --------------------------------------------------------- ---------- > session connect time 478385736 > process last non-idle time 478385736 > bytes sent via SQL*Net to client 8649748 > CPU used when call started 4599084 > CPU used by this session 4599084 > bytes received via SQL*Net from client 3243913 > session pga memory 1659824 > session pga memory max 1659824 > session uga memory 1589476 > session uga memory max 1589476 > table fetch continued row 418866 > SQL*Net roundtrips to/from client 123579 > user calls 123569 > execute count 49284 > calls to get snapshot scn: kcmgss 49282 > sorts (rows) 16390 > redo size 60 > consistent changes 17 > parse count (total) 17 > data blocks consistent reads - undo records applied 17 > opened cursors cumulative 12 > free buffer requested 11 > CR blocks created 11 > table scan blocks gotten 11 > rollbacks only - consistent read gets 10 > recursive calls 9 > db block gets 9 > parse count (hard) 9 > opened cursors current 6 > enqueue requests 5 > enqueue releases 5 > cursor authentications 5 > parse time elapsed 4 > table scans (short tables) 3 > parse time cpu 2 > logons cumulative 1 > logons current 1 > redo small copies 1 > cleanouts and rollbacks - consistent read gets 1 > sorts (memory) 1 > immediate (CR) block cleanout applications 1 > redo entries 1 > db block changes 1 > consistent gets -1.331E+09 > session logical reads -1.331E+09 > no work - consistent read gets -1.980E+09 > buffer is not pinned count -1.985E+09 > > NAME VALUE > --------------------------------------------------------- ---------- > table fetch by rowid -1.985E+09 > buffer is pinned count -1.990E+09 > > Regards > Naveen -- ---------------------------------------------------------------- Anjo Kolk http://www.oraperf.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Anjo Kolk INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Naveen Nahata INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
