Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> How often to analyze sys tables on 10.2

How often to analyze sys tables on 10.2

From: Wolfson Larry - lwolfs <>
Date: Thu, 5 Apr 2007 13:03:17 -0500
Message-ID: <>

Was wondering when or how often we should be analyzing 10.2 sys tables and what problems may occur?  

I did see:  

Complete checklist for manual upgrades to 10gR2

          Doc ID
<> :

Note:316889.1	 Type: 	BULLETIN	 
 	 Last Revision Date: 	29-MAR-2007	 

which talks about stats when upgrading but not after.    

Recently saw rt=0
"So in 10g it had hash join instead of nested loop join at least for
this particular select. Probably time to gather stats on sys tables? The difference in time wasn't so big though 4.11 vs 1.70 so it doesn't explain all the time taken.
But you can probably check whether you haven't more difference. Also you can download Thomas Kyte runstats_pkg and run it on both environments to compare whether some stats or latches haven't very big difference.
Gints Plivna <> "    

Dave Herring sent me a problem he had SEG$ and I'm sure I saw another post about an issue someone was having with another table. Just can't find it again.  


Queries against DBA_EXTENTS were extremely slow under 10g, database
"gmrec" on Asgard. Deleting dictionary and fixed object statistics
resolved the problem, but the job "gather_stats_job" runs each night at 10pm and puts stats back on all tables that have them missing."

skipped details

"The query is really 2 queries of segments: 1 on dictionary-managed
tablespace objects and the other on LMT-managed tablespace objects. The latter was the problem, as no dictionary-managed tablespaces existed.

A comparison of the explain plans showed that access of object X$KTFBUE changed greatly between stats and no stats, with the latter being accessed by index and the former having this object the lead object of a nested loop query against SYS_DBA_SEGS.

One option was to stop the gather stats job, but that seems rather drastic to resolve this. After deleting stats on a number of SYS-owned objects, I found that the problem was with stats on SYS.SEG$. I ran the following and that fixed the problem:



The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged.

If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited.

If you have received this communication in error, please resend this communication to the sender and delete the original message or any copy of it from your computer system.

Thank you.

Received on Thu Apr 05 2007 - 13:03:17 CDT

Original text of this message