Home » RDBMS Server » Performance Tuning » System statistics: demonstration (11.2.0.3)
System statistics: demonstration [message #586437] Thu, 06 June 2013 05:34 Go to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
We all know that system statistics are vital for performance. Without them, Oracle doesn't really know how long a single block read or a multiblock read will actually take, so it can't make a sensible decision between scan and index access paths. And we all know how to gather them, and how to see them in sys.aux_stats$.
I'm trying to develop a little demo on this, showing how different stats push the CBO in different directions, by setting them to ludicrous values. For example,
exec dbms_stats.set_system_stats('sreadtim',10000)
exec dbms_stats.set_system_stats('mreadtim',0.01)
exec dbms_stats.set_system_stats('mbrc',512)
exec dbms_stats.set_system_stats('cpuspeed',1000)
exec dbms_stats.set_system_stats('maxthr',40000000)
exec dbms_stats.set_system_stats('slavethr',10000000)
but no matter what I do, I'm still getting the same exec plans. I must be missing something stupid. If anyone can help me through this mental block, I would appreciate it.

Thank you for any insight.

Re: System statistics: demonstration [message #586440 is a reply to message #586437] Thu, 06 June 2013 05:48 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Probably because changing the stats doesn't invalidate the existing plans in the shared pool.
When you use dbms_stats.set_table_stats it has a parameter no_invalidate which defaults to TRUE. You have to set it to FALSE to get oracle to regenerate plans for the affected table.
set_system_stats doesn't appear to have an equivalent parameter but I assume it's the same problem.
Re: System statistics: demonstration [message #586441 is a reply to message #586437] Thu, 06 June 2013 05:49 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do you test this with tables with significant data (or faked statistics to large tables)?

Regards
Michel
Re: System statistics: demonstration [message #586443 is a reply to message #586441] Thu, 06 June 2013 05:58 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Thank you for replying (and so quickly).

I think that the SQL is getting re-parsed, I'm flushing the shared pool between tests and also using EXPLAIN PLAN which I believe always does a hard parse. I've been caught by that one before! the default for _optimizer_invalidation_period is a horrifying number of seconds (though it usually doesn't take anything like that long).

I've been testing on tiny amounts of data with genuine object stats, trying to make the CBO do table scans (rather than index scans) with queries that use an equality predicate on the primary key. I'll try to come up with a test on a large amount of data instead.
Re: System statistics: demonstration [message #586444 is a reply to message #586443] Thu, 06 June 2013 06:04 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
This is an example of what I'm doing:
create table sys.aux_stats$_backup as select * from sys.aux_stats$;

exec dbms_stats.set_system_stats('sreadtim',10000)
exec dbms_stats.set_system_stats('mreadtim',0.01)
exec dbms_stats.set_system_stats('mbrc',512)
exec dbms_stats.set_system_stats('cpuspeed',1000)
exec dbms_stats.set_system_stats('maxthr',40000000)
exec dbms_stats.set_system_stats('slavethr',10000000)

alter system flush buffer_cache;
alter system flush shared_pool;
alter session set "_serial_direct_read"=never;

explain plan for select * from dept where deptno=10;
select * from table(dbms_xplan.display);
I really would have expected a scan for this.
Re: System statistics: demonstration [message #586449 is a reply to message #586444] Thu, 06 June 2013 06:33 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Thank you for your replies, guys, they made me think through my mental block and I do now have a working demo. Heaven knows if it will work repeatedly, playing games with the CBO is not exactly reliable. If any one is interested (probably not, but just for completeness) here it is:
--backup the stats
create table sys.aux_stats$_backup as select * from sys.aux_stats$;

--set up the test: a biggish table, indexed on a not null column
drop table t1;
create table t1 (c1 number);
insert into t1 (select rownum from dual connect by level < 500000);
create index i1 on t1(c1);
alter table t1 modify c1 not null;
exec dbms_stats.gather_table_stats(user,'T1')

--tell the CBO that scan operations are going to be really REALLY slow
--in comparison with single block IO operations

exec dbms_stats.set_system_stats('sreadtim',0.01)
exec dbms_stats.set_system_stats('mreadtim',10000)
exec dbms_stats.set_system_stats('mbrc',1)
exec dbms_stats.set_system_stats('cpuspeed',1000)
exec dbms_stats.set_system_stats('maxthr',40000000)
exec dbms_stats.set_system_stats('slavethr',10000000)

--clean up the SGA, disable direct reads
alter system flush buffer_cache;
alter system flush shared_pool;
alter session set "_serial_direct_read"=never;

--and I get an index full scan
explain plan for select c1 from t1;
select * from table(dbms_xplan.display);

--now tell the CBO that scans are much faster than single block reads
exec dbms_stats.set_system_stats('sreadtim',10000)
exec dbms_stats.set_system_stats('mreadtim',0.01)
exec dbms_stats.set_system_stats('mbrc',512)
exec dbms_stats.set_system_stats('cpuspeed',1000)
exec dbms_stats.set_system_stats('maxthr',40000000)
exec dbms_stats.set_system_stats('slavethr',10000000)

alter system flush buffer_cache;
alter system flush shared_pool;
alter session set "_serial_direct_read"=never;

--and now I get a table scan 
explain plan for select c1 from t1;
select * from table(dbms_xplan.display);

--put the stats back
delete from sys.aux_stats$;
insert into sys.aux_stats$ select * from sys.aux_stats$_backup;
commit;
Re: System statistics: demonstration [message #586451 is a reply to message #586449] Thu, 06 June 2013 07:12 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I have not so much time to play with it but anyway thanks for the complete test case I will try it as soon as I can.

Note that you can multiply the test without changing the table itself by setting its statistics in the same you changed the system statistics.

Regards
Michel

[Updated on: Thu, 06 June 2013 07:13]

Report message to a moderator

Previous Topic: How to avoid repeat where clause in oracle sql
Next Topic: SQL not using index from application.
Goto Forum:
  


Current Time: Thu Mar 28 05:38:15 CDT 2024