stats collection job using dbms_scheduler

set servoutoutput on

BEGIN
     DBMS_SCHEDULER.CREATE_JOB (
          job_name => 'RAJ_STATS_REFRESH'
          ,job_type => 'PLSQL_BLOCK'
          ,job_action => 'Begin dbms_stats.gather_schema_stats(ownname => ''RAJ'', cascade => true); end;'
          ,start_date => '30-JAN-19 10.00.00PM US/Pacific'
          ,repeat_interval => 'FREQ=DAILY; INTERVAL=1'
          ,enabled => TRUE
          ,comments => 'Refreshes the RAJ Schema stats every night at 10 PM'
          );
END;
/


col JOB_NAME form a30
col STATE form a10
col SOURCE form a5

SELECT JOB_NAME,STATE,LAST_START_DATE,LAST_RUN_DURATION, NEXT_RUN_DATE FROM DBA_SCHEDULER_JOBS  WHERE JOB_NAME = 'RAJ';

select JOB_NAME, FAILURE_COUNT, LAST_START_DATE, LAST_RUN_DURATION from dba_scheduler_jobs WHERE JOB_NAME = 'RAJ';

SELECT JOB_NAME FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME = 'RAJ';

--select * from dba_scheduler_job_run_details where job_name = 'RAJ';

col status form a10
col ACTUAL_START_DATE form a40
col RUN_DURATION form a15
select JOB_NAME, STATUS,  ERROR#, ACTUAL_START_DATE, RUN_DURATION from dba_scheduler_job_run_details where job_name ='RAJ';

SELECT JOB_NAME,STATE,LAST_START_DATE,LAST_RUN_DURATION, NEXT_RUN_DATE FROM DBA_SCHEDULER_JOBS  WHERE JOB_NAME = 'RAJ';

JOB_NAME                       STATE      LAST_START_DATE                                                             LAST_RUN_DURATION
------------------------------ ---------- --------------------------------------------------------------------------- ---------------------------------------------------------------------------
NEXT_RUN_DATE
---------------------------------------------------------------------------
RAJ        RUNNING    03-FEB-19 12.55.00.156811 PM US/PACIFIC
03-FEB-19 12.55.00.100000 PM US/PACIFIC

--- Rollback:


> conn / as sysdba
Connected.
12:21:46 SYS@RAJ> BEGIN
12:21:51   2  DBMS_SCHEDULER.DROP_JOB( JOB_NAME => 'RAJ');
12:21:56   3   END;
12:21:59   4  /

PL/SQL procedure successfully completed.

How to flush shared pool

select address, hash_value from v$sqlarea
where sql_text = ‘select count(c2) from skew where c1 = :bind’;
ADDRESS HASH_VALUE
——– ———-
27308318 2934790721

exec DBMS_SHARED_POOL.PURGE ('00000008BB871740, 3842003817', 'C');
on you database


reason is that this SQL has produced many versions, appears to be bug in CS as on other databases we see only 1 or 2
10:03:04 SQL> select parsing_schema_name, version_count from v$sqlarea where sql_id='ak6up2gkh0nv9';

PARSING_SCHEMA_NAME            VERSION_COUNT
------------------------------ -------------
OPS$ORACLE                             57448


ak6up2gkh0nv9 select a.MRP, b.InTraf from (select decode(count(1), 0, 'N', 'Y') MRP from v$session where program like '%MRP%' and type='BACKGROUND') a, (select count(1) InTraf from gv$session where machine like '%occ%' or machine like '%paypal.com%' or machine like '%etl%') b

=== verification

select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID = 'ak6up2gkh0nv9';

should return none