| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> How often to analyze sys tables on 10.2
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
<https://metalink.oracle.com/help/usaeng/Search/search.html#file> :
Note:316889.1 Type: BULLETIN Last Revision Date: 29-MAR-2007
which talks about stats when upgrading but not after.
Recently saw
http://forums.oracle.com/forums/thread.jspa?threadID=488102&start=0&tsta
rt=0
snippet:
"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
http://www.gplivna.eu <http://www.gplivna.eu/> "
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.
"Overview
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:
BEGIN DBMS_STATS.DELETE_TABLE_STATS('SYS','SEG$'); DBMS_STATS.LOCK_TABLE_STATS('SYS','SEG$'); END; /"
THANKS LARRY
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.
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Apr 05 2007 - 13:03:17 CDT
 |
 |