OraFAQ Forum: Client Tools » auto optimizer stats collection

Home » Other » Client Tools » auto optimizer stats collection (11.1.0.7)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

auto optimizer stats collection [message #566874]

Thu, 20 September 2012 09:43

sathik123
Messages: 37
Registered: December 2010
Location: chennai

Member

Hi

In my database auto optimizer stats collection job scheduled. It is successfully running,i am confirming this by viewing DBA_AUTOTASK_JOB_HISTORY. My doubt is whether stats gather job collect statistics when 10%(default value) of data modified in a hole table or table partitions.

Below is the output for user_tab_modification.I am able to see two entry for same table.

SQL> select TABLE_NAME,PARTITION_NAME,INSERTS,UPDATES,DELETES,TIMESTAMP,TRUNCATED from USER_tab_modifications where table_name='DLM_PERFORMANCE_DATA';

TABLE_NAME PARTITION_NAME INSERTS UPDATES DELETES TIMESTAMP TRU
------------------------------ ------------------------------ ---------- ---------- ---------- ----------- ---
DLM_PERFORMANCE_DATA 169812174 0 0 20-SEP-2012 NO
DLM_PERFORMANCE_DATA SYS_P2663580 4946409 0 0 20-SEP-2012 NO

Thanks in advance.

Report message to a moderator

Re: auto optimizer stats collection [message #566884 is a reply to message #566874]

Thu, 20 September 2012 11:20

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Quote:

doubt is whether stats gather job collect statistics when 10%(default value) of data modified in a hole table or table partitions.

Both.

Regards
Michel

Report message to a moderator

Re: auto optimizer stats collection [message #566935 is a reply to message #566884]

Fri, 21 September 2012 00:39

sathik123
Messages: 37
Registered: December 2010
Location: chennai

Member

Hi
My table analyzed date is on 17 sep. during that period num_rows is around 231crore. Insert happen on this table(user_tab_modification) is around 24crore is a 10% of total table record. My auto optimizer stats collection JOB_STATUS is succeeded. But I am not sure why last analyzed column for the table is not updated. I am not sure how to check the estimate percent used in the automatic stats gather job

Please find the attached file for reference.

Regards
iamsathk

Attachment: SQL_output.txt
(Size: 1.58KB, Downloaded 2410 times)

Report message to a moderator

Re: auto optimizer stats collection [message #566939 is a reply to message #566935]

Fri, 21 September 2012 02:27

sathik123
Messages: 37
Registered: December 2010
Location: chennai

Member

I verified , Stale percent is 10. I am not sure why the last_analyzed column is not updated. Query i used to get the stale percent is
"SELECT dbms_stats.get_prefs('stale_percent') FROM dual"

Report message to a moderator

Re: auto optimizer stats collection [message #566942 is a reply to message #566939]

Fri, 21 September 2012 04:50

sathik123
Messages: 37
Registered: December 2010
Location: chennai

Member

pleaes help me on this.

Report message to a moderator

Previous Topic:	sql+ gui version download (2 Merged)
Next Topic:	running client sqlplus

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Feb 06 00:03:14 CST 2025