Thanks for the question, Om.

Asked: August 12, 2002 - 9:17 am UTC

Last updated: May 10, 2005 - 8:56 am UTC

Version: 8.1.7

Viewed 1000+ times

You Asked

Hi Tom,

Many thanks for your previous response.

One of our PRODUCTION database is having buffer cache hit raio as
15% from the last few days.

The database size is about 12 GB and the db_block_buffer is 20Mb
at present.

Now there can be so many reasons for this low buffer cache hit ratio
as according to the manual this may be because of

1) may be because of FULL table scans.

2) Bad application design etc

My Question is
================

Question 1) We can find out about the FULL table scans by writing this query

select name,value from v$sysstat
where name like '%table_scans%';

WHICH GIVES FOLLOWING RESULT ON OUR production DATABASE

table scans (short tables) 89365825
table scans (long tables) 20006613
table scans (rowid ranges) 0
table scans (cache partitions) 0
table scans (direct read) 0

I really do not know how to interpret this.

How to find out which tables are performing full table scans ?

Ques 2) If I know the table names which are performing full table
scans ...How to conclude that these can be CACHED or not ?
What size is generally recommended for a table to be CACHED ?

Let me type the size of the TOP 5 tables in the database.

The size is in MB.

AE_APPL_ER 1189.0625
AE_APRORS 956.53125
AE_ERROR_B 556.25
AE_ERRO_AGES 417.875
A_MESSAGES 405.78125

The shared pool for the database is 50MB.

Ques 3) Also when I see

SQL> select name,value
2 from v$sysstat
3 where name='free buffer inspected';

NAME VALUE
---------------------------------------------------------------- ----------
free buffer inspected 5585949

SQL> select event,total_waits
2 from v$system_event
3 where event in
4 ('free buffer waits','buffer busy waits');

EVENT TOTAL_WAITS
---------------------------------------------------------------- -----------
free buffer waits 981
buffer busy waits 139668

It gives the above information.

How to interpret this ?

Please help ?

Best regards,
Om

and Tom said...

it might not be a problem at all.

HAVE YOU IDENTIFIED IT AS A PROBLEM.

It could be an indication that you have reached perfection, just as much as an indication that you are having a problem.

Have you identified that

a) you have a problem
b) you have found the cause of the problem.

suggestion:

goto </code> http://www.hotsos.com/ <code>and read the paper "Are you still using cache hit ratios?" -- you can get that free by signing up.

Hey, I have a question for you since you gave me those two random numbers without any other information:

I just took a car trip and counted the red lights I had to stop at. I had to stop at 50 red lights. Is that good or bad?

(no matter what you answer, I will successfully argue the counter is actually true).

20mill full table scans looks bad -- but, is that over 2 minutes, 2 days, 2 weeks, 2 months, 2 years, etc.

You want to use statspack to get a holistic view of your database in a window of time (say 15-30 minutes MAX between snapshots)

You need to back up first tho and

a) determine that you in fact have a problem. That is not clear hear at all. Could this be a case of CTD? (compulsive tuning disorder)?

b) if you have a problem, state what it is and try to understand WHY you have a problem (find the cause -- then the cure)

Similar to a patient walking into doctors office with a headache (full scans). Doctor prescribes painkiller (fixed sympton). Patient dies -- it was a brain tumor, the headache was simply a sympton.

Rating

(5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Index on dual table

Sasa Petkovic, May 09, 2003 - 4:59 am UTC

Hi Tom,
In the article on hotsos ("when to use index") written by
Cary Millsap/Hotsos Enterprises, Ltd. found next:
" In Oracle8i, we have found that creating and using an index on system.dual significantly reduces the number of LIOs required to select its single row. We have observed about a 10:1 associated improvement in response time for queries of dual. If your application makes, for example, millions of LIO calls per day against one-row reference tables, then our research suggests that you can conserve millions of LIOs per day and about 90% less CPU for those queries by indexing them."

Please would you comment this as it was big suprise for me.

Thanks

May 09, 2003 - 1:33 pm UTC

well, it would be a bit shaky as it would involve two things

a) indexing dual
b) analyzing dual

in order for the index to be used -- it would be an index fast full scan - in order to do that -- you need dual analyzed.

None of that would be supported.

What would be supported however is:

create table mydual ( x int primary key ) organization index;
insert into mydual values ( 1 );
commit;

that'll do it -- use mydual instead of dual.

Note, a future release (future as of may 9th 2003) resolves this issue.

what Cary was trying to point out there was that the rule "do not index small tables" is a fallacy, mythology.

How Does This Save LIOs?

Doug Wingate, May 12, 2003 - 5:42 pm UTC

It seems quite reasonable that a one-block index of a three-block table would save logical reads: To select a particular row, one makes two single-block reads instead of scanning the three-block table. But how does an index on a single-row table reduce logical reads?

Thanks.

May 12, 2003 - 7:10 pm UTC

A full scan does some amount of work to figure out what to scan, the index does less setup work (and then finds out "oh, i'm done" right away).

Your MILEAGE WILL VARY when running this test -- in various releases, the full scan will do more (or less, or different kinds) of IO, but in this example, for this small dual like table, it is a 7:1 difference:

ops$tkyte@ORA920> create table t ( x int primary key );

Table created.

ops$tkyte@ORA920> insert into t values ( 1 );

1 row created.

ops$tkyte@ORA920> commit;

Commit complete.

ops$tkyte@ORA920> select * from t;

         X
----------
         1

ops$tkyte@ORA920> select * from t where x = 1;

         X
----------
         1

ops$tkyte@ORA920>
ops$tkyte@ORA920> set autotrace on
ops$tkyte@ORA920> select * from t;

         X
----------
         1


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF 'T'




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
        372  bytes sent via SQL*Net to client
        499  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

ops$tkyte@ORA920> select * from t where x = 1;

         X
----------
         1


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   INDEX (UNIQUE SCAN) OF 'SYS_C0010309' (UNIQUE)




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          1  consistent gets
          0  physical reads
          0  redo size
        372  bytes sent via SQL*Net to client
        499  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

ops$tkyte@ORA920> set autotrace off

Investigating the 7:1 Ratio

Doug Wingate, October 23, 2003 - 6:36 pm UTC

Okay, I understand now that for very small objects, the cost of "setup" may overwhelm the cost of reading the objects themselves, so it may for instance cost less to read a one-block index than to read a one-block table. Would the causes of the 7:1 ratio of consistent gets in your example be exposed if I examined the recursive SQL and wait events in an extended SQL trace?

Thanks.

October 23, 2003 - 7:39 pm UTC

i had no recursive sql in this case.

wait events maybe -- if you did this after flushing everything (no physical io) but that would be self defeating as it would imply the dictionary cache was probably killed as well so there would be recursive calls and all as well

Neeraj Ranjan Rath,Hyderabad, May 06, 2005 - 5:34 am UTC

Tom,
I was trying to get queries from v$sqltext which are doing full table scan on specific table.
I got one query from our database(This is a CRM database Ora Apps)using the following query

select sql_text from v$sqltext where hash_value='&hash' order by piece

Also trying to get no of executions and module to which that query is associated.

please see the following used query

select executions,module from v$sqlarea where hash_value='&hash'

Result:

EXECUTIONS MODULE
---------- ------------------
0 XXXXXXXX

I found the query from the v$sqltext view and executed 0 time.
What does it mean exactly?

May 06, 2005 - 8:02 am UTC

means it was never executed.  it was parsed, but not run.


ops$tkyte@ORA9IR2> declare
  2          l_cursor number;
  3  begin
  4          l_cursor := dbms_sql.open_cursor;
  5          dbms_sql.parse( l_cursor, 'select * from dual  look_for_me', dbms_sql.native );
  6          dbms_sql.close_cursor(l_cursor);
  7  end;
  8  /
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select executions, sql_text from v$sql where sql_text like '%look_for_me';
 
EXECUTIONS
----------
SQL_TEXT
-------------------------------------------------------------------------------
         0
select * from dual  look_for_me

In which condition Sqls parsed but not executed in production

Neeraj Ranjan Rath,Hyderabad, May 10, 2005 - 4:48 am UTC

Yeh it's really nice example.
But Tom in which situation the queries parsed and not executed in production.(Here we are discussioning abt CRM applications)

May 10, 2005 - 8:56 am UTC

the coders of your application did what I did above in their application.

I merely demonstrated "how", they are doing the equivalent in their code. they are parsing but not executing. There is nothing from the database side we can do -- if they say to us "parse this", we must comply.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Index on dual table

How Does This Save LIOs?

Investigating the 7:1 Ratio

In which condition Sqls parsed but not executed in production

More to Explore

Performance

About Buffer Cache Hit ratio

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Index on dual table

How Does This Save LIOs?

Investigating the 7:1 Ratio

In which condition Sqls parsed but not executed in production

More to Explore

Performance