Home » SQL & PL/SQL » Client Tools » auto optimizer stats collection (11.1.0.7)
auto optimizer stats collection [message #566874] Thu, 20 September 2012 09:43 Go to next message
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.
Re: auto optimizer stats collection [message #566884 is a reply to message #566874] Thu, 20 September 2012 11:20 Go to previous messageGo to next message
Michel Cadot
Messages: 58605
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: auto optimizer stats collection [message #566935 is a reply to message #566884] Fri, 21 September 2012 00:39 Go to previous messageGo to next message
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
Re: auto optimizer stats collection [message #566939 is a reply to message #566935] Fri, 21 September 2012 02:27 Go to previous messageGo to next message
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"
Re: auto optimizer stats collection [message #566942 is a reply to message #566939] Fri, 21 September 2012 04:50 Go to previous message
sathik123
Messages: 37
Registered: December 2010
Location: chennai
Member
pleaes help me on this.
Previous Topic: sql+ gui version download (2 Merged)
Next Topic: running client sqlplus
Goto Forum:
  


Current Time: Mon Jul 28 18:04:40 CDT 2014

Total time taken to generate the page: 0.10461 seconds