Home » SQL & PL/SQL » SQL & PL/SQL » Last Analyzed from USER_INDEXES
Last Analyzed from USER_INDEXES [message #239213] Mon, 21 May 2007 14:51 Go to next message
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member
Hi,

We gather stats using

DBMS_STATS.gather_table_Stats('SCHEMA,'TABLE_NAME')
-- for normal unpartitioned tables Daily

and
dbms_stats.gather_table_stats(USER,'TABLE','P_'||(to_Char(sysdate,'YYYYMM')),50,);
For partitioned tables, and since partitioned indexes are in place i have added cascade =>TRUE option to this,

Question is why for normal tables LAST_Analyzed column in User_indexes shows some 2-3 months old dates not the latest one, even though stats are gathered daily.Will this not effect the optimizer if index analyzed date is outdated.

Please Clarify

Regards
Re: Last Analyzed from USER_INDEXES [message #239214 is a reply to message #239213] Mon, 21 May 2007 14:56 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
Is the number of rows in the partition significantly different from the last time it was analyzed?
If not, then no need to re-compute same statistics.
Re: Last Analyzed from USER_INDEXES [message #239282 is a reply to message #239214] Mon, 21 May 2007 22:32 Go to previous messageGo to next message
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member
Thanks for follow up,

Yes significant amount of rows are added daily in current partition,for some tables with 5 million rows, 8000-10,000 rows will be added daily, and for 50 million rows tables 20000-30000 rows will be added daily to the current partition,

Regards
Re: Last Analyzed from USER_INDEXES [message #239312 is a reply to message #239282] Tue, 22 May 2007 00:43 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Cascade should also be used for regular tables in order to automatically analyze indexes (afaik)

Supplied PL/SQL Packages manual
cascade


Gather statistics on the indexes for this table. Index statistics gathering is not parallelized. Using this option is equivalent to running the GATHER_INDEX_STATS procedure on each of the table's indexes.

From Oracle9i Supplied PL/SQL Packages and Types Reference

[Updated on: Tue, 22 May 2007 00:46]

Report message to a moderator

Previous Topic: Resolving ORA-01841 error
Next Topic: How to Drop a particular column in a table.
Goto Forum:
  


Current Time: Thu Dec 08 05:59:50 CST 2016

Total time taken to generate the page: 0.11391 seconds