Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> Stats job causing time-out, Statistics: dbms_stats job causing users to time-out on connection
MadHatter
post Aug 7 2017, 08:24 AM
Post #1


Newbie
*

Group: Members
Posts: 4
Joined: 11-November 16
Member No.: 51,634



Hello friends,

I'm still an Oracle Junior so I'm sorry if this might be an easy one. Also, English is my second language so it might be a little rusty.

I'm having an issue with the default daily dbms_stats.gather_database_stats_job_proc ( ) job. The job runs around 22:00 every day and on 06:00 in the weekend.

At some point (usualy after 7/8 minutes) during this stats job the job fires off a lock table sys.mon_mods_all$ in exclusive mode for around 30-60 seconds. This causes some issues with new connecting sessions. They probably stay in wait and get their connections dropped by a time-out and return the user requested cancel of current operation error. Our batch/cron jobs that run with a timeout of 30 seconds.

Is this as designed or is there something I can do about it to maybe not cause this lock?
Go to the top of the page
 
+Quote Post
burleson
post Aug 8 2017, 11:07 AM
Post #2


Advanced Member
***

Group: Members
Posts: 13,532
Joined: 26-January 04
Member No.: 13



Hi,

First, remember that you only fun dbms_stats when there have been enough changes that the execution plans will change! The ONLY reason to run dbms_stats is to fix sub-optimal plans!

If it ain't broke, don't fix it!

See here, a way to see if you need to run dbms_stats:

http://www.dba-oracle.com/bk_dba_tab_modifications.htm

Also, beware that a full schema analyze can be very resource intensive, and it should only be fun during times of low activity. Please read carefully:

http://dbaforums.org/oracle/lofiversion/in...php?t16986.html

Check AWR, and see if your fnms_stats changed execution plans. If not, then you are wasting computing resources.

For the full methodology, see my book here:

http://www.rampant-books.com/book_0314_ora...tuning_pack.htm

Good luck!


--------------------
Hope this helps . . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
MadHatter
post Aug 24 2017, 01:50 AM
Post #3


Newbie
*

Group: Members
Posts: 4
Joined: 11-November 16
Member No.: 51,634



Hi Donald,

According to the dba_tab_modifications view there are (multi-)millions of updates/inserts/deletes each day.

This job is triggered by the "Auto Optimizer stats collection" dba_autotask job. This is a default setting in Oracle, is this not supposed to be run daily? I assume the job itself calculates if the specific dbms_stats job for a table is needed?

The issue described above only happens sporadically, so it happens maybe only 1 to 3 times a month.

Thanks for your time and support!
Go to the top of the page
 
+Quote Post
burleson
post Aug 24 2017, 07:33 AM
Post #4


Advanced Member
***

Group: Members
Posts: 13,532
Joined: 26-January 04
Member No.: 13



Hi,

Remember, you only want to reanalyze stats when you want sql execution plans to change!

I always disable automatic re analysis, and only reanalyze when I see a bad plan!

Please read:

http://www.dba-oracle.com/t_disable_gather_stats_job.htm

Good luck!


--------------------
Hope this helps . . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 23rd September 2017 - 10:16 PM