RE: Negative value for Consistent gets etc. in V$Sesstat
When the max value of the 4 bytes or 8 bytes have been reached the values may become negative, if oracle keeps on adding to them. Anjo. -Original Message- Nahata Sent: Wednesday, November 20, 2002 11:34 AM To: Multiple recipients of list ORACLE-L 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 gets36724753 > buffer is not pinned count36650911 > table fetch by rowid 36643847 > buffer is pinned count36569847 > session pga memory 1651312 > session pga memory max 1651312 > session uga memory 1589476 > session uga memory max 1589476 > bytes sent via SQL*Net to client156588 > 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 calls2033 > execute count 788 > calls to get snapshot scn: kcmgss 786 > parse count (total) 17 > opened cursors cumulative 12 > table scan blocks gotten11 > recursive calls 9 > parse count (hard) 9 > db block gets9 > 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 cumulative1 > sorts (memory) 1 > logons current 1 > > 38 rows selected. > > STATS 10 HOURS AFTER THE REPORT STARTED RUNNING >
RE: Negative value for Consistent gets etc. in V$Sesstat
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 gets36724753 > buffer is not pinned count36650911 > table fetch by rowid 36643847 > buffer is pinned count36569847 > session pga memory 1651312 > session pga memory max 1651312 > session uga memory 1589476 > session uga memory max 1589476 > bytes sent via SQL*Net to client156588 > 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 calls2033 > execute count 788 > calls to get snapshot scn: kcmgss 786 > parse count (total) 17 > opened cursors cumulative 12 > table scan blocks gotten11 > recursive calls 9 > parse count (hard) 9 > db block gets9 > 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 cumulative1 > 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
Re: Negative value for Consistent gets etc. in V$Sesstat
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 gets36724753 > buffer is not pinned count36650911 > table fetch by rowid 36643847 > buffer is pinned count36569847 > session pga memory 1651312 > session pga memory max 1651312 > session uga memory 1589476 > session uga memory max 1589476 > bytes sent via SQL*Net to client156588 > 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 calls2033 > execute count 788 > calls to get snapshot scn: kcmgss 786 > parse count (total) 17 > opened cursors cumulative 12 > table scan blocks gotten11 > recursive calls 9 > parse count (hard) 9 > db block gets9 > 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 cumulative1 > 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
