| Buffer Cache Hit Ratio - 1235%, what happened? [message #59043] |
Fri, 24 October 2003 14:34  |
Dennis Bustos
Messages: 5
Registered: October 2003
|
Junior Member |
|
|
The buffer cache hit ratio went up to 1235%. The Physical reads direct was 4298177707. All indexes are parallel. Any clue as to why it is happening? Thanks.
SQL> select p.value, d.value, l.value,s.value,(1 - (p.value - d.value - l.value) / (s.value))*100 fr
om
2 v$sysstat p,
3 v$sysstat d,
4 v$sysstat l,
5 v$sysstat s
6 where
7 p.name = 'physical reads' and
8 d.name = 'physical reads direct' and
9 l.name = 'physical reads direct (lob)' and
10 s.name = 'session logical reads'
11 /
VALUE VALUE VALUE VALUE (1-(P.VALUE-D.VALUE-L.VALUE)/(S.VALUE))*100
---------- ---------- ---------- ---------- -------------------------------------------
9928699 4298177707 0 377659638 1235.47983
|
|
|
|
|
|
| Re: Buffer Cache Hit Ratio - 1235%, what happened? [message #59045 is a reply to message #59044] |
Fri, 24 October 2003 17:28  |
Dennis Bustos
Messages: 5
Registered: October 2003
|
Junior Member |
|
|
Thanks for the reply, Here's the version:
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE 9.2.0.1.0 Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production
|
|
|
|
| Re: Buffer Cache Hit Ratio - 1235%, what happened? [message #59051 is a reply to message #59045] |
Sat, 25 October 2003 16:51 |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
hmm.. Whats Statspack report telling you ? I tend to rely more on that . Also its beneficial to get the statistics over a well defined time interval , instead of getting the cumulative statistics.
I dont generally rely on the buffer cache hit ratio ,becos it may not actually tell you if the database is performing good or not. I've seen databases with hit ratio of 80% performing much better than those that have about 99% hit ratio..
-Thiru
|
|
|
|
| Re: Buffer Cache Hit Ratio - 1235%, what happened? [message #59074 is a reply to message #59051] |
Mon, 27 October 2003 09:07 |
Dennis Bustos
Messages: 5
Registered: October 2003
|
Junior Member |
|
|
Thank you.
I found out that the SQL Statement I ran consumes a lot of physical reads direct. When I tried to use /*+ rule */ then it was quick. Because my SQL statement was doing a count(*), do you think that a full-table-scan will be more appropriate.
This is the execution plan from CHOOSE (CBO)
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2959 Card=4 Bytes=24)
1 0 SORT* (GROUP BY) (Cost=2959 Card=4 Bytes=24) :Q402100 3
2 1 SORT* (GROUP BY) (Cost=2959 Card=4 Bytes=24) :Q402100 2
3 2 HASH JOIN* (SEMI) (Cost=988 Card=1716060 Bytes=1029636 :Q402100 0) 2
4 3 INDEX* (FAST FULL SCAN) OF 'COMPLAINTLEVELS_LEVELID_ :Q402100 IDX' (NON-UNIQUE) (Cost=494 Card=1716060 Bytes=5148180) 0
5 3 INDEX* (FAST FULL SCAN) OF 'COMPLAINTLEVELS_LEVELID_ :Q402100 IDX' (NON-UNIQUE) (Cost=494 Card=1716060 Bytes=5148180) 1
This is the plan when I put the hint /*+ RULE */
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: RULE
1 0 SORT (GROUP BY)
2 1 NESTED LOOPS
3 2 VIEW OF 'VW_NSO_1'
4 3 SORT (UNIQUE)
5 4 TABLE ACCESS (FULL) OF 'COMPLAINTLEVELS'
6 2 INDEX (RANGE SCAN) OF 'COMPLAINTLEVELS_LEVELID_IDX' (NON-UNIQUE)
|
|
|
|
| Re: Buffer Cache Hit Ratio - 1235%, what happened? [message #59075 is a reply to message #59074] |
Mon, 27 October 2003 10:49 |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
Whats the sql statement ? Did you analyze all the objects involved ? RULE optimizer has been desupported in the latest release and hence shouldnt be used.
A full table scan is not required for count(*).It should be satisfied by an Index scan(Primary key or a Not NULL column).
|
|
|
|
| Re: Buffer Cache Hit Ratio - 1235%, what happened? [message #59076 is a reply to message #59074] |
Mon, 27 October 2003 12:09 |
Dennis Bustos
Messages: 5
Registered: October 2003
|
Junior Member |
|
|
Thanks,
The SQL Statement was
select count(*) from complaintlevels
where levelid in (
select distinct levelid from complaintlevels)
group by levelid
Is this a good approach of viewing efficiency
Should the total of db block gets + consistent gets + physical reads be less than the combined total blocks of all base objects?
I have this in my autotrace statistics:
4 db block gets + 155909 consistent gets + 8665 physical reads = 164578
The total blocks in all base objects is 73216 blocks.
Is this not efficient because it reads 2.247 times as many blocks, eventhough the performance of the report is less than 5 seconds? thanks
Thanks
|
|
|
|
| Re: Buffer Cache Hit Ratio - 1235%, what happened? [message #59077 is a reply to message #59075] |
Mon, 27 October 2003 12:13 |
Dennis Bustos
Messages: 5
Registered: October 2003
|
Junior Member |
|
|
The SQL Statement was
select count(*) from complaintlevels
where levelid in (
select distinct levelid from complaintlevels)
group by levelid
Is this a good approach of viewing efficiency
Should the total of db block gets + consistent gets + physical reads be less than the combined total blocks of all base objects?
I have this in my autotrace statistics:
4 db block gets + 155909 consistent gets + 8665 physical reads = 164578
The total blocks in all base objects is 73216 blocks.
Is this not efficient because it reads 2.247 times as many blocks, eventhough the performance of the report is less than 5 seconds? thanks
Thanks
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
