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


Advanced Member
***

Group: Members
Posts: 13,492
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

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