| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Negative value for Consistent gets etc. in V$Sesstat
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.
OUTPUT ONE HOUR AFTER THE REPORT STARTED:
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
--
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). Received on Tue Nov 19 2002 - 23:34:09 CST
 |
 |