Re: Better cardinality estimate when dialing optimizer_features_enable back

  • From: Randolf Geist <info@xxxxxxxxxxxxxxxxxxxxx>
  • To: Randolf Geist <info@xxxxxxxxxxxxxxxxxxxxx>, Neil Kodner <nkodner@xxxxxxxxx>
  • Date: Wed, 25 Nov 2009 21:01:57 +0100

> As far as things that might have changed statistics-wise, I'm not too 
> sure. I can ask around, there are other DBAs in the org, but I dont 
> think anyone would have changed the stats gathering methods. the 
> gather_stats_job runs but it hasn't touched any of the tables in this 
> specific schema.

A definitive answer regarding what has changed (not why it has changed, but it 
might provide a clue into the right direction why it might have changed) is 
only possible by peeking into older stats as already mentioned.

> One other thing that Randolf touched upon was the single block read 
> time taking longer than multi-block. I calculated workload statistics 
> again and received a similar response. Is this worth bringing up to 
> the Sysadmins? I've since deleted the system stats and computed 
> noworkload statistics instead.

I would be careful with the System Statistics - if you have deleted now the 
WORKLOAD System Statistics then the MBRC value is gone and the setting of 
db_file_multiblock_read_count is going to be used instead if it is set (I think 
I remember it was set to 16 in your case) for the NOWORKLOAD calculations. This 
changes the synthesized MREADTIM value used by the NOWORKLOAD System Statistics 
- effectively changing (in this case approximately lowering the cost by factor 
2) the cost of every Full Table Scan (oh, and this is based on your previous 
gathered NOWORKLOAD System Statistics but you say that you have re-gathered 
them now, too).

What you can do to monitor the WORKLOAD System Statistics behaviour is e.g. 
schedule an hourly job that gathers System Statistics into a user-defined 
statistics table - this won't touch your existing System Statistics used by the 
optimizer, but allows you to see the different values measured at different 
times of the day.

Something like this (taken from Christian Antognini's TOP scripts):

execute dbms_stats.create_stat_table('sys','aux_stats_history')

VARIABLE job NUMBER

execute dbms_job.submit(:job,'declare statid varchar2(30) := 
''S''||to_char(sysdate,''yyyymmddhh24miss''); begin 
dbms_stats.gather_system_stats(''start'', null, ''aux_stats_history'', statid, 
''sys''); dbms_lock.sleep(3600); dbms_stats.gather_system_stats(''stop'', null, 
''aux_stats_history'', statid, ''sys''); end;',sysdate,'sysdate+1/24')

COMMIT;

PRINT job

You can query the results then with something like this:

SELECT n1 AS sreadtim, n2 AS mreadtim, n3 AS cpuspeed, n11 AS mbrc 
FROM sys.aux_stats_history 
WHERE c4 = 'CPU_SERIO' 
ORDER BY statid;

SELECT n1 AS maxthr, n2 AS slavethr 
FROM sys.aux_stats_history 
WHERE c4 = 'PARIO' 
ORDER BY statid;

Regards,
Randolf

Oracle related stuff blog:
http://oracle-randolf.blogspot.com/

Co-author of the forthcoming "OakTable Expert Oracle Practices" book:
http://www.apress.com/book/view/1430226684
http://www.amazon.com/Expert-Oracle-Practices-Database-Administration/dp/1430226684
______________________________________________________
GRATIS für alle WEB.DE-Nutzer: Die maxdome Movie-FLAT!
Jetzt freischalten unter http://movieflat.web.de

--
//www.freelists.org/webpage/oracle-l


Other related posts: