How do I determine the what program is running an Oracle dbms_stats operation?

DJM75
DJM75 used Ask the Experts™
on
DB version: Oracle 10.2.0.4 on AIX 5L

I have noticed that every Monday at 4am a dbms_stats.gather_schema_stats operation runs.  After this stats job runs, the optimizer chooses a different plan for a specific query in the app code.  The new plan causes the query to run extremely slow.

My question:  How do I figure out the source program of this dbms_stats operation?  I have a 10g Scheduler job set to run each night at 10pm.  I cannot find any other job on the database that could be running this operation.  The vendor's app does have the ability to run stats; which they supposedly only run at 6pm Fridays (I'm still fighting to get that turned off).  So, could I use some sort of logon trigger to capture where this operation is being run from?  If so, does anyone have a DDL example?  Any other suggestions?  

Any and all help would be appreciated.  I am currently running stats manually each Monday morning to get Mr. Optimizer to use hash join rather than nested loops.

Thanks.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2009

Commented:
How are you checking the job queue? Are you using the older DBMS_JOB package / tables or are you looking in the newer table DBA_SCHEDULER_JOBS? The monitoring job is named 'GATHER_STATS_JOB'


select job_name, last_start_date from dba_scheduler_jobs;

Author

Commented:
The last scheduler stats job was on 7/18 (Saturday) at 06:00.  This is our weekend window.  No stats job runs between Saturday 06:00 and Monday 22:00 from the scheduler.  

JOB_NAME
------------------------------
LAST_START_DATE
---------------------------------------------------------------------------
GATHER_STATS_JOB
18-JUL-09 06.00.02.786743 AM -05:00

I checked dbms_jobs and anything that might have be in the crontab to see if another DBA snuck something in.  Haven't found anything.
Top Expert 2009

Commented:
In your original description you said your own job runs at 10pm nightly.

But the job above in your last post ran on Saturday at 6AM.

Top Expert 2009
Commented:
Consider turning on AUDITING for a while.

http://www.oracle-base.com/articles/10g/Auditing_10gR2.php

You could audit specific users with: AUDIT EXECUTE PROCEDURE BY SCOTT BY ACCESS;
Or you could audit all EXECUTE for the whole database.

Author

Commented:
The scheduler also gathers stats on Sat at 06:00 - I did leave that out of the initial message.  So, that entry was expected.  I didn't know you could audit EXECUTE PROCEDURE.  It sounds like that's the simplest choice at this point.  Thx

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial