Home » RDBMS Server » Performance Tuning » Oracle execution plan help needed (Oracle 8i)
Oracle execution plan help needed [message #310471] Tue, 01 April 2008 06:53 Go to next message
gangulyz
Messages: 6
Registered: April 2008
Junior Member
Hi
Is there a way in Oracle to check if the schema objects need to be re-analyzed to get the statistics?
Is there any data dictionary view to do this?

Actually We want to run the DBMS_STATS.GATHER_SCHEMA_STATS proc only if it is needed.

Kindly help...


Regards
Som

[Updated on: Tue, 01 April 2008 06:55]

Report message to a moderator

Re: Oracle execution plan help needed [message #310484 is a reply to message #310471] Tue, 01 April 2008 07:14 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
You can check DBA_TAB_MODIFICATIONS view.
In 10g all tables are created with MONITORING feature as default.
In 9i - you can do "ALTER TABLE <tabname> MONITORING";

Afterward you will see in DBA_TAB_MODIFICATIONS view the number of INSERTs/DELETEs/UPDATEs for each table and use it to run DBMS_STATS (look for STALE in documentation as well).

HTH.
Re: Oracle execution plan help needed [message #310488 is a reply to message #310484] Tue, 01 April 2008 07:22 Go to previous message
gangulyz
Messages: 6
Registered: April 2008
Junior Member
Thanks a lot Michael...
I selected from the USER_TAB_MODIFICATIONS view and then executed the proc based on the result.

Thanks again ..Much appreciated.

Regards
Som
Previous Topic: 'EXISTS' vs 'IN'
Next Topic: Bucketing Query Performance
Goto Forum:
  


Current Time: Tue Dec 06 10:40:58 CST 2016

Total time taken to generate the page: 0.05697 seconds