OraFAQ Forum: Performance Tuning » Using a stats table instead of regular stats to get a plan

Home » RDBMS Server » Performance Tuning » Using a stats table instead of regular stats to get a plan (9i)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Using a stats table instead of regular stats to get a plan [message #490872]

Thu, 27 January 2011 17:32

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

Hi all.

DBMS_STATS.CREATE_STATS_TABLE
DBMS_STATS.EXPORT_SCHEMA_STATS
DBMS_STATS.GATHER_TABLE_STATS

I have used the above to get a copy of schema stats and gather new stats for specific tables into a STATS TABLE in my personal schema. What I want to do now is use this stats table to generate plans for queries where I believe stats are off. Anyone know how to do this? Is it even possible? To be clear, I do not want to import stats because this replaces the stats currently there. I just want to point the CBO to my stats table for generating plans.

I was hoping there was a session parameter I could set to tell oracle to use my stats table when generating plans, or an explain plan clause I could use or a DBMS_XPLAN paramter I could provide that would tell these tools to use my stats table when generating a plan, or even some way to tell autotrace. But I have found none of this. I wonder now if it is even possible. So here I am to ask you guys.

Kevin

Report message to a moderator

Re: Using a stats table instead of regular stats to get a plan [message #490895 is a reply to message #490872]

Fri, 28 January 2011 01:47

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

Hi, man. I'm open to correction, but....
If you used gather_table_stats with the stattab argument, I think that will have written the existing statistics to your stats table, and generated new ones into the data dictionary. So you are already using the new statistics!
I don't think it is possible to do what you want - until the 11g facility of "pending" startistics.
John.

Report message to a moderator

Re: Using a stats table instead of regular stats to get a plan [message #490908 is a reply to message #490895]

Fri, 28 January 2011 02:48

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

Boy I sure hope not. I'll check that though. I read the documentation and unless I missed it, it did not indicate it would update real stats. I will see.

Kevin

Report message to a moderator

Previous Topic:	Long Running Query
Next Topic:	Index usage in order by clause on nullable column

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun May 05 14:46:10 CDT 2024