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.