Welcome Guest ( Log In | Register )



Performance Tuning Reference poster
Oracle training in Linux 

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
post Aug 7 2017, 08:24 AM
Post #1


Group: Members
Posts: 3
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
post Aug 8 2017, 11:07 AM
Post #2

Advanced Member

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


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:


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:


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:


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: 22nd August 2017 - 02:23 AM