Creating scheduler jobs in oracle database

Take for example you are trying to run database stats every weekly on the whole database. 

You can do it either by a shell script and running it in Crontab

or

You can use DBMS_SCHEDULER package to create a scheduler job

Here i show how to use dbms_scheduler and setup stats job.

First create a procedure in the schema you are trying to run the job from

 CREATE OR REPLACE procedure SYS.Manual_full_db_stats as  
 BEGIN  
     SYS.DBMS_STATS.GATHER_DATABASE_STATS ( Granularity => 'DEFAULT' ,OPTIONS => 'GATHER' ,Gather_Sys => FALSE ,Estimate_Percent => 10 ,Method_Opt => 'FOR ALL COLUMNS SIZE 1' ,Degree => 6 ,CASCADE => TRUE ,No_Invalidate => FALSE);  
 END;  
 /  

Once the procedure is created you can create a scheduler job.. If you want to use plsql block instead of a procedure just modify the below code and create the job

    ,job_type    => 'PLSQL_BLOCK'  
    ,job_action   => 'BEGIN   
  SYS.MANUAL_FULL_DB_STATS;  
  COMMIT;   
 END;   
 '  

Main code

 BEGIN  
  SYS.DBMS_SCHEDULER.CREATE_JOB  
   (  
     job_name    => 'SYS.MANUAL_STATS_JOB'  
    ,start_date   => TO_TIMESTAMP_TZ('2016/08/20 14:00:00.000000 America/Los_Angeles','yyyy/mm/dd hh24:mi:ss.ff tzr')  
    ,repeat_interval => 'FREQ=WEEKLY;INTERVAL=1'  
    ,end_date    => NULL  
    ,job_class    => 'DEFAULT_JOB_CLASS'  
    ,job_type    => 'STORED_PROCEDURE'  
    ,job_action   => 'SYS.MANUAL_FULL_DB_STATS'  
    ,comments    => NULL  
   );  
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE  
   ( name   => 'SYS.MANUAL_STATS_JOB'  
    ,attribute => 'RESTARTABLE'  
    ,value   => FALSE);  
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE  
   ( name   => 'SYS.MANUAL_STATS_JOB'  
    ,attribute => 'LOGGING_LEVEL'  
    ,value   => SYS.DBMS_SCHEDULER.LOGGING_OFF);  
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL  
   ( name   => 'SYS.MANUAL_STATS_JOB'  
    ,attribute => 'MAX_FAILURES');  
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL  
   ( name   => 'SYS.MANUAL_STATS_JOB'  
    ,attribute => 'MAX_RUNS');  
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE  
   ( name   => 'SYS.MANUAL_STATS_JOB'  
    ,attribute => 'STOP_ON_WINDOW_CLOSE'  
    ,value   => FALSE);  
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE  
   ( name   => 'SYS.MANUAL_STATS_JOB'  
    ,attribute => 'JOB_PRIORITY'  
    ,value   => 3);  
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL  
   ( name   => 'SYS.MANUAL_STATS_JOB'  
    ,attribute => 'SCHEDULE_LIMIT');  
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE  
   ( name   => 'SYS.MANUAL_STATS_JOB'  
    ,attribute => 'AUTO_DROP'  
    ,value   => FALSE);  
  SYS.DBMS_SCHEDULER.ENABLE  
   (name         => 'SYS.MANUAL_STATS_JOB');  
 END;  
 /  

PS: Some time you may get error saying job not found or insufficient privileges

 ORA-20000: Insufficient privileges to analyze an object in Schema

 For this you need to give the grants to the user who is running it..

 grant ANALYZE ANY to username;
 grant SELECT ANY TABLE to username;

Purging sql from shared pool

To flush a sql or purge a sql from shared pool you need to find out the sql_id you are trying to purge..

Once you get the id

run the following command

select address, hash_value from v$sqlarea where sql_id like '8ktqrp3vwp51w';

This will give you address and hash_value of the sql

Now insert these values in the following query and execute

exec dbms_shared_pool.purge('0000000B6ECC55E8, 2891302865','C');

Some times when you execute above you will get the following error

*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_SHARED_POOL.PURGE' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

If this is the case

Goto $ORACLE_HOME/rdbms/admin

sqlplus / as sysdba

@dbmspool.sql

After this run the exec command again and this should fix the issue..

You can also flush the entire shared pool using the following command

alter system flush shared_pool;

creating sql baselines in oracle 11g

First create a test table

CREATE TABLE test (
  id           NUMBER,
  description  VARCHAR2(50)
);



Insert 10000 rows into it using the following code

BEGIN
  FOR v_LoopCounter IN 1..10000 LOOP
 INSERT INTO test (id)
 VALUES (v_LoopCounter);
END LOOP;
END;
 /


Gather stats on the test table

EXEC DBMS_STATS.gather_table_stats(USER, 'test', cascade=>TRUE);


Check the explain plan for the following query.. It should do a full table scan

SELECT description
FROM   test
WHERE  id = 13;


Find the sql_id for the above sql

SELECT *
FROM   v$sql
WHERE  sql_text LIKE '%test%'
AND    sql_text NOT LIKE '%dba_sql_plan_baselines%'
AND    sql_text NOT LIKE '%EXPLAIN%';


 Since we are loading manually in this scenario it is called manual baseline.. There is a parameter which enables system wide automatic loading of baselines.. To enable it you have to set OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES to True. Default is False. Please dont enable on production systems without proper testing

 Manual method

 SET SERVEROUTPUT ON
DECLARE
  l_plans_loaded  PLS_INTEGER;
BEGIN
  l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(
    sql_id => 'gat6z1bc6nc2d');
 
  DBMS_OUTPUT.put_line('Plans Loaded: ' || l_plans_loaded);
END;
/

After the above baseline will be loaded..

You can check the loaded baselines using the following query

SELECT sql_handle, plan_name, enabled, accepted
FROM   dba_sql_plan_baselines

You can see in the output.. It will be yes for both enabled and accepted.


This completes the manual loading of baselines.

To test if baseline is working.. Flush shared pool( not recommended on production again) this is for hard parsing..

ALTER SYSTEM FLUSH SHARED_POOL;

Now create index on the table

CREATE INDEX test_idx ON test(id);

Gather stats

EXEC DBMS_STATS.gather_table_stats(USER, 'test', cascade=>TRUE);

Check the explain plan for the following query.. It should do a full table scan even after creating index..

SELECT description
FROM   test
WHERE  id = 13;

Now run the following query..

SELECT sql_handle, plan_name, enabled, accepted
FROM   dba_sql_plan_baselines

you will see a new baseline but it says not enabled.. This is the plan for new index we created but it wont be used as it is not tested.. Oracle uses new baseline only if performance is tested and better than previous baseline.

So to test that and evolve the new plan we grab the sql_handle from dba_sql_baselines and run the following..
SELECT DBMS_SPM.evolve_sql_plan_baseline(sql_handle => 'SQL_7b76323ad90440b9') FROM   dual;


Now if you run the above select query and check explain plan it should be using the plan with index.

ORA-02327: cannot create index on expression with datatype LOB

I got this error when i am trying to rebuild an index on a different tablespace.

Here is the query i ran..

Alter index  xxx.SYS_IL0000194446C00010$$ REBUILD tablespace xxx_data
Error at line 1
ORA-02327: cannot create index on expression with datatype LOB

Previously these tablespaces are in Users tablespace.

So in order to move them we should do the following since these are LOB's


ALTER TABLE xxx.TBL_REQUEST_STATUS_DETAIL MOVE LOB (TECH_ERROR)
STORE AS (TABLESPACE xxx_DATA);

After running this i was able to move them successfully



my reference:

ALTER TABLE xxx.TBL_REQUEST_STATUS_DETAIL MOVE LOB (TECH_ERROR)
STORE AS (TABLESPACE xxx_DATA);
ALTER TABLE xxx.TBL_REQUEST_STATUS_DETAIL MOVE LOB (REQUEST_BODY)
STORE AS (TABLESPACE xxx_DATA);
ALTER TABLE xxx.TBL_REQUEST_STATUS_DETAIL MOVE LOB (RESPONSE_BODY)
STORE AS (TABLESPACE xxx_DATA);
ALTER TABLE xxx.TBL_REQUEST_STATUS_DETAIL MOVE LOB (ERROR_MESSAGE)
STORE AS (TABLESPACE xxx_DATA);