db file scattered read

[oddbod1]

I have a problem. Statspack shows our highest wait event is db file scattered reads against a reporting instance, queries often perform full table scans but they have been optimised. I submit one query and then the same query 10 seconds later. The first query completes in 3 minutes, the second in about 5 minutes, a trace replicates the statspack as it is always waiting on db file scattered read. Does anyone know how to get around this?

 

[dbtoo2001]

Ask Tom...

http://asktom.oracle.com/pls/ask/f?...P8_DISPLAYID,F4950_P8_CRITERIA:4433887271030,

(Read down through the article, it will get to the scattered reads.)

What is your db_file_multiblock_read_count set to?

try (as always, on non-production systems first!):

select value from v$parameter where name = 'db_file_multiblock_read_count';

alter session set db_file_multiblock_read_count = 1000;

select value from v$parameter where name = 'db_file_multiblock_read_count';

alter session set events '10046 trace name context forever, level 8';

select /*+ FULL(t) */ count(*) from sys.source$ t;

 

[sem]

Are you sure that all useful indexes are utilized? Is your statistics up to date? It's possible that you're trying to cure symptoms, not the illness. IMHO db_file_multiblock_read_count = 1000 kills your system.

Regards, Dima

http://seminihin.narod.ru

 

[oddbod1]

Thanks Guys, we have played with the db_file_multiblock_read_count, altering this does not improve performance. The OS limit is 64K, the db_BLOCK_SIZE is 8k so setting the db_file_multiblock_read_count to 8 is the optimal setting. Any value above this causes a degfradation in performance. With regards to indexes, the data growth has caused the optimizer to choose a full table scan. The issue refers to a reporting instance where redo logs are applied from a production instance. The indexes were developed to maximise performance against the production instance (which is already huge) but the queries against the reporting instance are different in their construct and therefore tend not to use the indexes. We have investigated lots of areas, most init.ora parameters and materialised views, nothing improves performance. The tables are not partitioned so I cannot get any major performance benefit from parallel queries, could anyone point me in a direction with regards to setting up efficient parallel queries with non-paritioned tables, we are also unable to insert a parallel hint in the SQL due to third party software configuration of the queries.

 

[dbtoo2001]

What is the optimizer mode? Do you use a single userid to query the reports?
(Dima, I guess I left out a little information on that....
Look at

http://www.oracledba.co.uk/

click tuning, then db_file_multiblock_read_count for a better explanation.)
We got around some of the 'slowness' of reports by having an after logon trigger set the optimizer mode for the user to all_rows.

 

[oddbod1]

Thanks dbtoo2001, I have checked that the db_file_multiblock_read_count and it is set to its maximum value. For further info, I set up parallel queries with up to 32 concurrent slaves, this had no positive impact whatsoever. In fact, the system stats fairly welll replicate what happens without parallel query. That is, top output and vmstat output show that only one or two proceses are actually running with the rest of the parallel slaves in a blocked/sleeping state. Can anyone help with this?