Access Path Changed after analzing table !

[Chris Fung]

Hi All,

I just encountered a strange behaviour of optimzer of my database (8i).

Perviously, all the tables do not have statistics information and I think the optimizer is rule-based. When I run explain plan for some of the query, index scan is observed for those table with indexes defined.

After I analyze some of the tables, some of the query access those analyzed table cannot access the table through indexes which I expect it could. FYI, the table is very large and contains several million of records.

I am just wondering whether I need to analyze those indexes associated with those tables ? Or I couldn't control the behaviour of the optimizer ?

By the way, I didn't set any init.ora parameter for the optimizer and I would expect cost-based optimizer is the default if anyone of my tables contains statistics.

Any idea?

Cheers,

Chris,

[Jean-Luc Oudart]

Hi Chris,

to check the behaviour :
select * from v$parameter where name like 'opti%'

If the optimizer_mode is 'CHOOSE' you must analyse your tables.

The bahaviour can change with the amount of data, when you analyzed last your data.

You can also add Hints on your SQL statments to force a specific path.

Regards,
Jean-Luc

[Graham Cameron_1]

Cost based optimiser is the default and after 9i onwards it's the only optimiser supported.

You are obviously using cost based as optimisation has changed after you have analyzed some tables.

You really need to analyze all tables and indexes. Use the following syntax for tables and indexes respectively:

exec dbms_stats.gather_table_stats('owner', 'tabname');
exec dbms_stats.gather_index_stats('owner', 'idxname');

But even then, the choice taken by the optimiser can be very fickle, and if you think you know better, add some hints...

-- Graham

[Michael Schulte zur Sur]

Hi,

the default for the optimizer_mode, if not specified, is choose, meaning depending on the existence of statistics, the optimizer will be rule-based or cost-based. It is advisable to calculate statistics also on the indices, since the optimizer must know, how selectiv the index is. Once I thought it a good idea to calculate statistics on the tables, but just as in your incidence, the plan backfired and everything was five times slower, how embarressing!! :-(

so just do everything or nothing,

Michael

[Brian Crabtree · Accepted solution]

Chris,

By analyzing the tables (and indexes), the optimizer gets a layout of the data being accessed on the system. The "cost" of getting the rows requested are defined by a number of things, including block size the db_multiblock_read_count setting. These will determine how much data can be brought in each pass: (ie: block size of 8k, db_multiblock_read_count of 32, means that 256k of data gets pulled each time. This could mean that it is less costly to get all of the rows, and remove the ones that are not needed than to get them from the index.

Also, the FTS is marked if you are pulling more than 25% of the rows on the table. Oracle has determined that pulling anymore than 25% with the index is more costly than not.

The optimizer is not always right, and can sometimes be very incorrect. Oracle has allowed for 'hints' to be generated with SQL statements to allow prodding of the CBO. This can do a number of things, from telling the query to use a specific index, to forcing a parallel query to take over. The following is a link to a metalink document that I like that has the hints and information on them:

http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=29236.1

You should be careful, as using hints will hamstring the CBO into what it is doing, however it can greatly improve performance on queries if the wrong plan is taken. For a quick example that might help you here, this is a very simple SQL statement. The key points are below:

select /*+ INDEX(INDEXA A) */ colA from tableA A where A.colA = '1';

"/*+" and "*/": These enclose the hint. The syntax is important.

"INDEX(INDEXA A)": This is the hint itself. The "INDEXA" is the name of the index on tableA. The "A" is the alias for tableA defined in the from clause (this is required as far as I can tell).

"tableA A": Alias for tableA. Better to be used with the hint. A more descriptive entry should be used.

After all of this, check the following init.ora parameter. 'Compatible' should be set to your current version. The CBO will not use the most recent abilities unless it is set to the current version. The 'optimizer_index_cost_adj' marks how less or more costly indexes are to full table scans. I do not recommend changing this if it is set, or playing with it without planning, as this will affect every query on the database. Using hints are safer and more effective.

Let me know if you have any questions.

Thanks,

Brian

[Hein van den Heuvel]

>Access Path Changed after analzing table !

Yeah well, that's the plan! (sic)


> After I analyze some of the tables, some of the query access those analyzed table cannot access the table through indexes which I expect it could. FYI, the table is very large and contains several million of records.

The way you wrote this _suggests_ that the optimizer did not choose the optimal path.
Please indicate whether the new solution was better, worse or similar! Was the elapsed time better or worse? How much? Was the cpu time better or worse?

Cheers,
Hein.

[Michael Schulte zur Sur]

Hi Hein,

going through a table with a few million rows without index compared to with index before, you can be sure, that the result was dramatically worse ;-).

greetings,

Michael

[Chris Fung]

Dear All,

Thanks for the responds. I think I really need to test out in the development environment.

Cheers,

Best,

[Yogeeraj_1]

hi,

to add to the above replies..

You should use a method that would analyze the table, the indexes and in most cases --> the indexed columns (although for unique or data with fairly uniform distributions, you could be more specific and just analyze the indexes that are on skewed data).

The way to do it is by using DBMS_STATS package.
So,

ALTER TABLE MONITORING

I.e. we tell Oracle to keep track for us and only analyze tables in which about 10% of the data has changed.

then you can gather statistics JUST on tables that need it

exec dbms_stats.gather_schema_stats( ownname => user, options => 'GATHER STALE' );

This way the analyzes will go much faster.

Hence, if you have several schemas, you will

for x in ( select username
from dba_users
where username not in ( 'SYS', 'SYSTEM' ) )
loop
dbms_stats.gather_SCHEMA_stats( ... for that username )
end loop;


hope this helps too!

regards
Yogeeraj

[Yogeeraj_1]

hi again,

If you want to see the tables which have stale statistics, you can use the following PLSQL block: (e.g. to be run on SQLPLUS)

set serveroutput on size 1000000

declare
l_objList dbms_stats.objectTab;
begin
dbms_stats.gather_schema_stats
( ownname => USER,
options => 'LIST STALE',
objlist => l_objList );

for i in 1 .. l_objList.count
loop
dbms_output.put_line( l_objList(i).objType );
dbms_output.put_line( l_objList(i).objName );
end loop;
end;
/

hth
yogeeraj