Skip to Main Content
  • Questions

  • Is dbms_stats transactional in 10gR2

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Winston.

Asked: November 09, 2009 - 11:50 am UTC

Last updated: November 10, 2009 - 2:56 pm UTC

Version: 10gR2

Viewed 1000+ times

You Asked

I remember I read it on asktom that dbms_stats has a commit inside its application but that behaviour has changed in the recent releases. I couldn't find it anywhere.

I plan to call dbms_stats inside a DDL trigger when it detects a new table is created. I've having problem doing that in 10g, the test case and error message has been posted at:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:486421585357#2092251700346324559

If I am using the releases where DBMS_STATS call is not transactional, what kind of workaround would you recommend?

Thanks in advance,
-Winston.

and Tom said...

I would not do that - it would fail and basically "be wrong" for a couple of reasons.

First of all, in the DDL trigger, the table isn't quite there yet, you are in the midst of building the table. There would be nothing there to gather statistics on

Second of all - the table would be empty - zippo, zilch. No sense really in gathering quite yet.

Third, dynamic sampling is set to level 2 in 10g and above. We will - at hard parse time - dynamically sample the table to figure it out. This will work well until it gets officially gathered which will happen with the next "gather stale"


So, *do not do this*.

It not only will not work - it should not be done even if it could work.


At best - at best, if the table is created via "create table as select" and you *really really really* wanted to gather statistics - use dbms_job to submit a gather stats call that will run shortly after the DDL statement commits (or fails, it could fail too and the job would disappear)

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

very good point ...

Jiri, November 10, 2009 - 9:55 pm UTC

if I can add two tiny details (there is really not much to add to this)

1. possibly AUDIT CREATE TABLE would do similar job as insert to your custom table (just a friendly suggestion)

2. I would use GATHER_SCHEMA_STATS with option GATHER EMPTY and schedule it at certain probably off-peak time (even several times a day)
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library