| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> 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
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: anjo_at_oraperf.com 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: ListGuru_at_fatcity.com (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: naveen_nahata_at_mindtree.com 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: ListGuru_at_fatcity.com (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: Anjo Kolk INET: anjo_at_oraperf.com 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: ListGuru_at_fatcity.com (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).Received on Wed Nov 20 2002 - 06:09:19 CST
 |
 |