Usage of Crontab on unix

Using Cron

To use cron for tasks meant to run only for your user profile, add entries to your own user’s crontab file. Start the crontab editor from a terminal window:

crontab -e

Edit the crontab using the format described in the next sections. Save your changes. (Exiting without saving will leave your crontab unchanged.)

Note that a great source of information about the format can be found at:

man 5 crontab

Commands that normally run with administrative privileges (i.e. they are generally run using sudo) should be added to the root user’s crontab (instead of the user’s crontab):

 sudo crontab -e

Crontab Sections

Each of the sections is separated by a space, with the final section having one or more spaces in it. No spaces are allowed within Sections 1-5, only between them. Sections 1-5 are used to indicate when and how often you want the task to be executed. This is how a cron job is laid out:

minute (0-59), hour (0-23, 0 = midnight), day (1-31), month (1-12), weekday (0-6, 0 = Sunday), command

01 04 1 1 1 /usr/bin/somedirectory/somecommand

The above example will run /usr/bin/somedirectory/somecommand at 4:01am on January 1st plus every Monday in January. An asterisk (*) can be used so that every instance (every hour, every weekday, every month, etc.) of a time period is used. Code:

01 04 * * * /usr/bin/somedirectory/somecommand

The above example will run /usr/bin/somedirectory/somecommand at 4:01am on every day of every month.

Comma-separated values can be used to run more than one instance of a particular command within a time period. Dash-separated values can be used to run a command continuously. Code:

01,31 04,05 1-15 1,6 * /usr/bin/somedirectory/somecommand

The above example will run /usr/bin/somedirectory/somecommand at 01 and 31 past the hours of 4:00am and 5:00am on the 1st through the 15th of every January and June.

The “/usr/bin/somedirectory/somecommand” text in the above examples indicates the task which will be run at the specified times. It is recommended that you use the full path to the desired commands as shown in the above examples. Enter which somecommand in the terminal to find the full path to somecommand. The crontab will begin running as soon as it is properly edited and saved.

You may want to run a script some number of times per time unit. For example if you want to run it every 10 minutes use the following crontab entry (runs on minutes divisible by 10: 0, 10, 20, 30, etc.)

*/10 * * * * /usr/bin/somedirectory/somecommand

which is also equivalent to the more cumbersome

0,10,20,30,40,50 * * * * /usr/bin/somedirectory/somecommand

Crontab Options

  • The -l option causes the current crontab to be displayed on standard output.
  • The -r option causes the current crontab to be removed.
  • The -e option is used to edit the current crontab using the editor specified by the EDITOR environment variable.

After you exit from the editor, the modified crontab will be checked for accuracy and, if there are no errors, installed automatically. The file is stored in /var/spool/cron/crontabs but should only be edited via the crontab command.

Enable User Level Cron

If the /etc/cron.allow file exists, then users must be listed in it in order to be allowed to run the crontab command. If the /etc/cron.allow file does not exist but the /etc/cron.deny file does, then users must not be listed in the /etc/cron.deny file in order to run crontab.

In the case where neither file exists, the default on current Ubuntu (and Debian, but not some other Linux and UNIX systems) is to allow all users to run jobs with crontab.

No cron.allow or cron.deny files exist in a standard Ubuntu install, so all users should have cron available by default, until one of those files is created. If a blank cron.deny file has been created, that will change to the standard behavior users of other operating systems might expect: cron only available to root or users in cron.allow.

Note, userids on your system which do not appear in /etc/shadow will NOT have operational crontabs, if you desire to enter a user in /etc/passwd, but NOT /etc/shadow that user’s crontab will never run. Place an entry in /etc/shadow for the user with a * for the password crypt,ie:

joeuser:*:15169::::::

Further Considerations

Crontab commands are generally stored in the crontab file belonging to your user account (and executed with your user’s level of permissions). If you want to regularly run a command requiring administrative permissions, edit the root crontab file:

sudo crontab -e

Depending on the commands being run, you may need to expand the root users PATH variable by putting the following line at the top of their crontab file:

PATH=/usr/sbin:/usr/bin:/sbin:/bin

It is sensible to test that your cron jobs work as intended. One method for doing this is to set up the job to run a couple of minutes in the future and then check the results before finalising the timing. You may also find it useful to put the commands into script files that log their success or failure, for example:

echo "Nightly Backup Successful: $(date)" >> /tmp/mybackup.log

For more information, see the man pages for cron and crontab (man is detailed on the BasicCommands page). If your machine is regularly switched off, you may also be interested in at and anacron, which provide other approaches to scheduled tasks. For example, anacron offers simple system-wide directories for running commands hourly, daily, weekly, and monthly. Scripts to be executed in said times can be placed in/etc/cron.hourly//etc/cron.daily//etc/cron.weekly/, and /etc/cron.monthly/. All scripts in each directory are run as root, and a specific order to running the scripts can be specified by prefixing the scripts’ filenames with numbers (see the man page for run-parts for more details). Although the directories contain periods in their names, run-parts will not accept a file name containing a period and will fail silently when encountering them (bug #38022). Either rename the file or use a symlink (without a period) to it instead (see, for example, python + cron without login? and Problems with Hourly Cron Job).

Troubleshooting and Common Problems

Edits to a user’s crontab and jobs that are run on their behalf are all logged by default to /var/log/syslog and that’s the first place to check if things are not running as you expect.

When adding a new entry to a blank crontab, forgetting to add a newline at the end is a common source for the job not running. If the last line in the crontab does not end with a newline, no errors will be reported at edit or runtime, but that line will never run. See man crontab for more information. This has already been suggested as a bug.

If a user was not allowed to execute jobs when their crontab was last edited, just adding them to the allow list won’t do anything. The user needs to re-edit their crontab after being added to cron.allow before their jobs will run.

When creating a crontab for the root user, the user name must be specified as a parameter after the date/time parameters. Accidentally including the user name that way in a user-specific crontab will result in trying to run the user’s name as a command, rather than what was expected.

Entries in cron may not run with the same environment, in particular the PATH, as you expect them to. Try using full paths to files and programs if they’re not being located as you expect.

The “%” character is used as newline delimiter in cron commands. If you need to pass that character into a script, you need to escape it as “\%”.

If you’re having trouble running a GUI application using cron, see the GUI Applications section below.

Advanced Crontab

The Crontabs discussed above are user crontabs. Each of the above crontabs is associated with a user, even the system crontab which is associated with the root user. There are two other types of crontab.

Firstly, as mentioned above anacron uses the run-parts command and /etc/cron.hourly/etc/cron.weekly, and /etc/cron.monthlydirectories. However anacron itself is invoked from the /etc/crontab file. This file could be used for other cron commands, but probably shouldn’t be. Here’s an example line from a ficticious /etc/crontab:

00 01 * * * rusty /home/rusty/rusty-list-files.sh

This would run Rusty’s command script as user rusty from his home directory. However, it is not usual to add commands to this file. While an experienced user should know about it, it is not recommended that you add anything to /etc/crontab. Apart from anything else, this could cause problem if the /etc/crontab file is affected by updates! Rusty could lose his command.

The second type of crontab is to be found in /etc/cron.d. Within the directory are small named crontabs. The directory is often used by packages, and the small crontabs allows a user to be associated with the commands in them.

Instead of adding a line to /etc/crontab which Rusty knows is not a good idea, Rusty might well add a file to /etc/cron.d with the name rusty, containing his cron line above. This would not be affected by updates but is a well known location.

When would you use these alternate crontab locations? Well, on a single user machine or a shared machine such as a school or college server, auser crontab would be the way to go. But in a large IT department, where several people might look after a server, then /etc/cron.d is probably the best place to install crontabs – it’s a central point and saves searching for them!

You may not need to look at /etc/crontab or /etc/cron.d, let alone edit them by hand. But an experienced user should perhaps know about them and that the packages that he/she installs may use these locations for their crontabs.

Special strings

Cron also offers some special strings:

  • string meaning
    @reboot Run once, at startup.
    @yearly Run once a year, “0 0 1 1 *”.
    @annually (same as @yearly)
    @monthly Run once a month, “0 0 1 * *”.
    @weekly Run once a week, “0 0 * * 0”.
    @daily Run once a day, “0 0 * * *”.
    @midnight (same as @daily)
    @hourly Run once an hour, “0 * * * *”.

Usage: “@reboot /path/to/execuable1” will execute /path/to/executable1 when the system starts. See “man 5 crontab” for more info.

Advertisements

Oracle – Check tablespace sizes

One of the tasks a DBA undertakes is to monitor the amount of space consumed by the database. If a tablespace becomes spacebound than the database will freeze up as data cannot be written down to disk.

Check Tablespace Free Space

The below script will identify the available free space for each tablespace.

SELECT a.tablespace_name
, a.fileCount
, a.MaxTSBytes
, b.TSDataBytes
, a.MaxTSBytes - b.TSDataBytes FreeSpace
, ROUND((a.TotalFilesizeMB/a.MaxTSBytes)*100,2) DFAlloc
, ROUND((b.TSDataBytes/a.MaxTSBytes)*100,2) AS DATAAlloc
FROM
(SELECT tablespace_name
, COUNT(*) filecount
, SUM(CASE WHEN maxbytes > bytes THEN maxbytes/1048576 ELSE bytes/1048576 end) AS MaxTSBytes
, SUM(bytes)/1048576 TotalFilesizeMB
FROM dba_data_files GROUP BY tablespace_name) a,
(SELECT tablespace_name
, SUM(bytes)/(1048576) AS TSDataBytes
FROM dba_extents GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name(+)
ORDER BY 1
/
TABLESPACE_NAME FILECOUNT MAXTSBYTES TSDATABYTES FREESPACE  DFALLOC DATAALLOC
--------------- --------- ---------- ----------- ---------  ------- ---------
INTERFACE       13        22250      21906.4375  343.5625   100      98.46
LOAD            6         8550       7629.125    920.875    100      89.23
MLOG            1         1250       617.5625    632.4375   100      49.41
NOTES           3         4200       3728.6875   471.3125   100      88.78
SYSTEM          1         500        406.625     93.375     100      81.33
UNDO            3         4500       323.085938  4176.91406 100       7.18
XDB             1         100        44.9375     55.0625    100      44.94
6 rows selected.

Query Datafile Sizes

The below script queries how much free space is available per datafile. In this example only the system tablespace has been queried:

SELECT a.file_id,
SUBSTR(a.tablespace_name,1,10) tablespace,
a.autoextensible,
(CASE WHEN a.maxbytes > a.bytes THEN a.maxbytes/1048576 ELSE bytes/1048576 END) MaxFileSize,
a.bytes/1048576 filesize,
(CASE WHEN a.maxbytes > a.bytes THEN a.maxbytes/1048576 ELSE bytes/1048576 END) - nvl(b.usedbytes,0) free_Mb,
round(100*(1-(nvl(b.Usedbytes,0))/(CASE WHEN a.maxbytes > a.bytes THEN a.maxbytes/1048576 else bytes/1048576 END)),2) "%_FREE",
a.file_name
FROM  dba_data_files a,
(SELECT file_id
, sum(bytes)/1048576 Usedbytes
FROM dba_extents GROUP BY file_id) b
WHERE a.file_id = b.file_id(+)
AND a.tablespace_name = '&tablespace_name'
ORDER BY
a.tablespace_name,
a.file_id
/
FILE_ID TABLESPACE AUT MAXFILESIZE FILESIZE FREE_MB %_FREE FILE_NAME
------- ---------- --- ----------- -------- ------- ------ ---------
1       SYSTEM     NO  500         500      93.375  18.68 /u05/oracle/oradata/SYSTM_01.dbf

Once you have found the file_id for the datafile in the tablespace that needs expanding then you can increase the size of the tablespace.

Oracle – Fix unusable indexes

If you want to find a list of unusable indexes and fix them you can issue the following SQL that will print out a list of alter commands that can be copied and pasted to the SQL command line then executed to fix the indexes.

SQL> select 'alter index '||index_name||' rebuild;' from all_indexes  where status ='UNUSABLE';
'ALTERINDEX'||INDEX_NAME||'REBUILD;'
--------------------------------------------------
alter index I_WRI$_OPTSTAT_HH_ST rebuild;
alter index I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST rebuild;
alter index I_WRI$_OPTSTAT_H_ST rebuild;
alter index I_WRI$_OPTSTAT_OPR_STIME rebuild;

This will help resolve errors such as the following:

SQL>  exec dbms_stats.purge_stats(to_date('28-JUN-11','DD-MON-YY'));
BEGIN dbms_stats.purge_stats(to_date('28-JUN-11','DD-MON-YY')); END;
*
ERROR at line 1:
ORA-01502: index 'SYS.I_WRI$_OPTSTAT_TAB_OBJ#_ST' or partition of such index is in unusable state
ORA-06512: at "SYS.DBMS_STATS", line 1100
ORA-06512: at "SYS.DBMS_STATS", line 17203
ORA-06512: at line 1

SQL Plan Management -2-

1. Optimizer_capture_sql_plan_baselines              2. Optimizer_use_sql_plan_baseline 

As known “optimizer_capture_sql_plan_baseline if set to true, let oracle automatically capture sql plan baselines.”

Does it mean it will not capture any plan for any sql if set to false ?

What if we want to capture baselines for only selected queries automatically.

The whole answer lies in values of “ENABLED,ACCEPTED AND FIXED” column if dba_sql_plan_baselines. How? Lets find out…

As usual, lets create a table and insert few records.

Session1>show parameter capture

NAME                                 TYPE        VALUE

———————————— ———– ——————————

optimizer_capture_sql_plan_baselines boolean     FALSE

create table test (name varchar2(10), id number(*));

begin

for i in 1..1000

loop

insert into test values (‘SUMIT’,i);

end loop;

commit;

end;

/

begin

for i in 1001..10000

loop

insert into test values (‘BHATIA’,i);

end loop;

commit;

end;

/

begin

for i in 10001..100000

loop

insert into test values (‘BASELINE’,i);

end loop;

commit;

end;

/

begin

for i in 100001..1000000

loop

insert into test values (‘SPM’,i);

end loop;

commit;

end;

/

SELECT COUNT(*),name from test group by name order by 1;

COUNT(*) NAME

———- ———-

1000 SUMIT

9000 BHATIA

90000 BASELINE

900000 SPM

4 rows selected.

Time to query table and load the plan into the baseline.

–Inserting hint to easily identify the query

Select /*+ id=1000 */  * FROM TEST where id=1000

NAME               ID

———- ———-

SUMIT            1000

select sql_id,exact_matching_signature,force_matching_signature from v$sql where sql_text like ‘%id=1000%’;

SQL_ID                EXACT_MATCHING_SIGNATURE         FORCE_MATCHING_SIGNATURE

————- ——————————– ——————————–

duk2ypk5fz9g6              7784548270786280511              4428329137525653294

SELECT * FROM TABLE(dbms_xplan.display_cursor(‘duk2ypk5fz9g6’));

PLAN_TABLE_OUTPUT

———————————————————————-

SQL_ID  duk2ypk5fz9g6, child number 0

————————————-

select /*+ id=1000 */  * FROM TEST where id=1000

Plan hash value: 1357081020

———————————-

| Id  | Operation         | Name |

———————————-

|   0 | SELECT STATEMENT  |      |

|*  1 |  TABLE ACCESS FULL| TEST |

———————————-

Predicate Information (identified by operation id):

—————————————————

1 – filter(“ID”=1000)

Note

—–

– rule based optimizer used (consider using cbo)

–Creating the baseline for the plan

var v_num number;

exec :v_num:=dbms_spm.load_plans_from_cursor_cache(sql_id => ‘duk2ypk5fz9g6’,plan_hash_value => 1357081020 );

select sql_handle, plan_name, enabled, accepted,fixed from dba_sql_plan_baselines where signature=7784548270786280511;

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX

—————————— —————————— — — —

SYS_SQL_6c0845687118fc3f       SYS_SQL_PLAN_7118fc3f97bbe3d0  YES YES NO

As we can see, If we load the plan manually, that will plan will be enabled and accepted but not fixed.  Its time create an index and run the query again.

create index test_id on test(id);

Index created.

select /*+ id=1000 */  * FROM TEST where id=1000

NAME               ID

———- ———-

SUMIT            1000

Oracle is still using SYS_SQL_PLAN_7118fc3f97bbe3d0 baseline.

However, if we query dba_sql_plan_baselines, we will see that oracle has started capturing baselines for this sql. (plan name SYS_SQL_PLAN_7118fc3f642e4a26)

PLAN_TABLE_OUTPUT

———————————————————–

SQL_ID  duk2ypk5fz9g6, child number 1

————————————-

select /*+ id=1000 */  * FROM TEST where id=1000

Plan hash value: 1357081020

————————————————————————–

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

————————————————————————–

|   0 | SELECT STATEMENT  |      |       |       |   554 (100)|          |

|*  1 |  TABLE ACCESS FULL| TEST |  1643 | 32860 |   554   (2)| 00:00:07 |

————————————————————————–

Predicate Information (identified by operation id):

—————————————————

1 – filter(“ID”=1000)

Note

—–

– SQL plan baseline SYS_SQL_PLAN_7118fc3f97bbe3d0 used for this statement

select sql_handle, plan_name, enabled, accepted,fixed,signature from dba_sql_plan_baselines where signature=7784548270786280511;

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX             SIGNATURE

—————————— —————————— — — — ———————

SYS_SQL_6c0845687118fc3f       SYS_SQL_PLAN_7118fc3f642e4a26  YES NO NO   7784548270786280511

SYS

So even if capture baseline is set to false, oracle will still capture the new plans and baselines for that particular sql onwards. We can evolve the  new plan and can check if index is getting used or not.

/*Evolving new plan */

SET SERVEROUTPUT ON

SET LONG 10000

set lines 175

DECLARE

report clob;

BEGIN

report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(

sql_handle => ‘&sql_handle’, time_limit => &time);

DBMS_OUTPUT.PUT_LINE(report);

END;

/

Enter value for sql_handle:SYS_SQL_6c0845687118fc3f

Enter value for time:60

——————————————————————————-

Evolve SQL Plan Baseline

Report

——————————————————————————-

Inputs:

——-

SQL_HANDLE = SYS_SQL_6c0845687118fc3f

PLAN_NAME  =

TIME_LIMIT = 60

VERIFY     = YES

COMMIT     = YES

Plan: SYS_SQL_PLAN_7118fc3f642e4a26

———————————–

Plan was verified: Time used .05 seconds.

Passed

performance criterion: Compound improvement ratio >= 504.88

Plan was changed to an accepted plan.

Baseline Plan      Test Plan     Improv. Ratio

————-      ———     ————-

Execution Status:        COMPLETE       COMPLETE

Rows Processed:                 1              1

Elapsed Time(ms):               26             0

CPU Time(ms):                  25              0

Buffer Gets:                 2017              4            504.25

Disk Reads:                     0              0

Direct Writes:                  0              0

Fetches:                        0              0

Executions:                     1              1

——————————————————————————-

Report

Summary

——————————————————————————-

Number of SQL plan baselines verified: 1.

Number of SQL plan baselines evolved: 1.

As we can read in the report, Oracle has marked the new plan as accepted. Oracle keeps a tab if the plan was loaded manually or captured automatically. This can be verified from origin column of dba_sql_plan_baselines.

select sql_handle, plan_name, enabled, accepted,fixed,origin from dba_sql_plan_baselines where signature=7784548270786280511;

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX ORIGIN

—————————— —————————— — — — ————–

SYS_SQL_6c0845687118fc3f       SYS_SQL_PLAN_7118fc3f642e4a26  YES YES NO AUTO-CAPTURE

SYS_SQL_6c0845687118fc3f       SYS_SQL_PLAN_7118fc3f97bbe3d0  YES YES NO MANUAL-LOAD

So far, the story was pretty much straight. Here’s a quick recap what we have done
1. Created Table and Inserted records
2. Query the table and baseline the query
3. Created Index and verified new plan is loaded but not accepted.
4. Evolve the new plan and accepted=yes.
5. New plan(Index Range Scan) started getting used.

Its time to discuss the usage of enabled,accepted and fixed parameters.

TEST1:  ENABLED=NO.

For every sql_id, oracle checks if the baselines exists or not. If yes, then it will filter all the enabled plans. The plans for which enabled=NO will not be considered even if accepted and fixed are set to yes.

Other than this, if any plan is set enabled=yes for a query,oracle will start capturing new plans also.

Therefore, enabled is the first level. If enabled is set to NO,oracle will mark not to use and evolve that plan.

Lets have a test case. I will mark enabled =no for the plan that was using index and check which plan is used.

select sql_handle, plan_name, enabled, accepted,fixed,origin from dba_sql_plan_baselines where signature=7784548270786280511;

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX ORIGIN

—————————— —————————— — — — ————–

SYS_SQL_6c0845687118fc3f       SYS_SQL_PLAN_7118fc3f642e4a26  YES YES NO AUTO-CAPTURE

SYS_SQL_6c0845687118fc3f       SYS_SQL_PLAN_7118fc3f97bbe3d0  YES YES NO MANUAL-LOAD

/* Setting Enabled=NO for SYS_SQL_PLAN_7118fc3f642e4a26 */

declare

myplan pls_integer;

begin

myplan:=DBMS_SPM.ALTER_SQL_PLAN_BASELINE (sql_handle => ‘&sql_handle’,plan_name  => ‘&plan_name’,attribute_name => ‘ENABLED’,   attribute_value => ‘NO’);

end;

/

Enter value for sql_handle: SYS_SQL_6c0845687118fc3f

Enter value for plan_name: SYS_SQL_PLAN_7118fc3f642e4a26

select sql_handle, plan_name, enabled, accepted,fixed,origin from dba_sql_plan_baselines where signature=7784548270786280511;

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX ORIGIN

—————————— —————————— — — — ————–

SYS_SQL_6c0845687118fc3f       SYS_SQL_PLAN_7118fc3f642e4a26  NO YES  NO  AUTO-CAPTURE

SYS_SQL_6c0845687118fc3f       SYS_SQL_PLAN_7118fc3f97bbe3d0  YES YES NO  MANUAL-LOAD

2 rows selected.

So, plan SYS_SQL_PLAN_7118fc3f642e4a26 is accepted but not enabled. Lets execute the query again and see which plan is getting used.

PLAN_TABLE_OUTPUT

——————————————————————–

SQL_ID  duk2ypk5fz9g6, child number 1

————————————-

select /*+ id=1000 */  * FROM TEST where id=1000

Plan hash value: 1357081020

————————————————————————–

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

————————————————————————–

|   0 | SELECT STATEMENT  |      |       |       |   554 (100)|          |

|*  1 |  TABLE ACCESS FULL| TEST |  1643 | 32860 |   554   (2)| 00:00:07 |

————————————————————————–

Predicate Information (identified by operation id):

—————————————————

1 – filter(“ID”=1000)

Note

—–

– SQL plan baseline SYS_SQL_PLAN_7118fc3f97bbe3d0 used for this statement

Oracle choses SYS_SQL_PLAN_7118fc3f97bbe3, since that plan is still enabled and accepted.
Therefore, minimum requirement for oracle to use a plan as baseline is, it should be marked both as ENABLED and ACCEPTED.

The next level is Accepted. In this level oracle will pick all the plans that are accepted to the users and discard all “ACCEPTED=NO” plans. As we had already seen,there can be more than one plan for accepted=yes. All these plans are already evolved plans and are candidate for being  the final execution plan.

If no plan is marked “FIXED=YES”, then oracle will pick any of these plans based on the costing.

All those plans which are “ENABLED=YES” and “ACCEPTED=NO”, are candidates for evolution. We can also however marked then accepted=yes manually.

Next and the last level is Fixed. If any plan is fixed oracle will use that plan only. If more than one plan is fixed, oracle will use costing as criteria to select the plan among those.

TEST2:  Setting any one plan as FIXED=YES

Lets mark index plan as accepted again and FTS plan as fixed.

select sql_handle, plan_name, enabled, accepted,fixed,origin from dba_sql_plan_baselines where signature=7784548270786280511;

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX ORIGIN

—————————— —————————— — — — ————–

SYS_SQL_6c0845687118fc3f       SYS_SQL_PLAN_7118fc3f642e4a26  NO  YES NO  AUTO-CAPTURE

SYS_SQL_6c0845687118fc3f       SYS_SQL_PLAN_7118fc3f97bbe3d0  YES YES NO  MANUAL-LOAD

/*Resetting SYS_SQL_PLAN_7118fc3f642e4a26 to Enabled */

declare

myplan pls_integer;

begin

myplan:=DBMS_SPM.ALTER_SQL_PLAN_BASELINE (sql_handle =>’&sql_handle’,plan_name=>’&plan_name’,attribute_name =>’ENABLED’,attribute_value =>’YES’);

end;

/

Enter value for sql_handle: SYS_SQL_6c0845687118fc3f

Enter value for plan_name: SYS_SQL_PLAN_7118fc3f642e4a26

/*Setting SYS_SQL_PLAN_7118fc3f97bbe3d0 to FIXED */

declare

myplan pls_integer;

begin

myplan:=DBMS_SPM.ALTER_SQL_PLAN_BASELINE (sql_handle =>’&sql_handle’,plan_name=>’&plan_name’,attribute_name=>’FIXED’,attribute_value=>’YES’);

end;

/

Enter value for sql_handle: SYS_SQL_6c0845687118fc3f

Enter value for plan_name: SYS_SQL_PLAN_7118fc3f97bbe3d0

select sql_handle, plan_name, enabled, accepted,fixed,origin from dba_sql_plan_baselines where signature=7784548270786280511;

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX ORIGIN

—————————— —————————— — — — ————–

SYS_SQL_6c0845687118fc3f       SYS_SQL_PLAN_7118fc3f642e4a26  YES YES NO  AUTO-CAPTURE

SYS_SQL_6c0845687118fc3f       SYS_SQL_PLAN_7118fc3f97bbe3d0  YES YES YES MANUAL-LOAD

Executing the query and checking the plan again

SELECT * FROM TABLE(dbms_xplan.display_cursor(‘duk2ypk5fz9g6’));

PLAN_TABLE_OUTPUT

—————————————————————————

SQL_ID  duk2ypk5fz9g6, child number 1

————————————-

select /*+ id=1000 */  * FROM TEST where id=1000

Plan hash value: 1357081020

————————————————————————–

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

————————————————————————–

|   0 | SELECT STATEMENT  |      |       |       |   554 (100)|          |

|*  1 |  TABLE ACCESS FULL| TEST |  1643 | 32860 |   554   (2)| 00:00:07 |

————————————————————————–

Predicate Information (identified by operation id):

—————————————————

1 – filter(“ID”=1000)

Note

—–

– SQL plan baseline SYS_SQL_PLAN_7118fc3f97bbe3d0 used for this statement

As we can confirm oracle start using FTS as that plan is marked fixed.

TEST3: Setting FIXED=YES for more than one plan

Lets mark second plan “FIXED=YES” and re-execute the query

/*Setting SYS_SQL_PLAN_7118fc3f642e4a26 to FIXED */

declare

myplan pls_integer;

begin

myplan:=DBMS_SPM.ALTER_SQL_PLAN_BASELINE (sql_handle=>’&sql_handle’,plan_name=>’&plan_name’,attribute_name=>’FIXED’,attribute_value=>’YES’);

end;

/

Enter value for sql_handle: SYS_SQL_6c0845687118fc3f

Enter value for plan_name: SYS_SQL_PLAN_7118fc3f642e4a26

select sql_handle, plan_name, enabled, accepted,fixed,origin from dba_sql_plan_baselines where signature=7784548270786280511;

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX ORIGIN

—————————— —————————— — — — ————–

SYS_SQL_6c0845687118fc3f       SYS_SQL_PLAN_7118fc3f642e4a26  YES YES YES AUTO-CAPTURE

SYS_SQL_6c0845687118fc3f       SYS_SQL_PLAN_7118fc3f97bbe3d0  YES YES YES MANUAL-LOAD

select /*+ id=1000 */  * FROM TEST where id=1000

NAME               ID

———- ———-

SUMIT            1000

1 row selected.

SELECT * FROM TABLE(dbms_xplan.display_cursor(‘duk2ypk5fz9g6’));

PLAN_TABLE_OUTPUT

—————————————————————

SQL_ID  duk2ypk5fz9g6, child number 0

————————————-

select /*+ id=1000 */  * FROM TEST where id=1000

Plan hash value: 1699862855

—————————————————————————————

| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

—————————————————————————————

|   0 | SELECT STATEMENT            |         |       |       |     1 (100)|          |

|   1 |  TABLE ACCESS BY INDEX ROWID| TEST    |     1 |    20 |     1   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | TEST_ID |     1 |       |     1   (0)| 00:00:01 |

—————————————————————————————

Predicate Information (identified by operation id):

—————————————————

2 – access(“ID”=1000)

Note

—–

– dynamic sampling used for this statement

– SQL plan baseline SYS_SQL_PLAN_7118fc3f642e4a26 used for this statement

Test 4:

Also, if any of the plan is marked as fixed, oracle will stop capturing and evolving new plans.
To simulate the test, lets again mark the plan(using index) as fixed=no and accepted=no.

select sql_handle, plan_name, enabled, accepted,fixed,origin from dba_sql_plan_baselines where signature=7784548270786280511;

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX ORIGIN

—————————— —————————— — — — ————–

SYS_SQL_6c0845687118fc3f       SYS_SQL_PLAN_7118fc3f642e4a26  YES YES YES AUTO-CAPTURE

SYS_SQL_6c0845687118fc3f       SYS_SQL_PLAN_7118fc3f97bbe3d0  YES YES YES MANUAL-LOAD

/* Resetting SYS_SQL_PLAN_7118fc3f642e4a26 to FIXED=NO */

declare

myplan pls_integer;

begin

myplan:=DBMS_SPM.ALTER_SQL_PLAN_BASELINE (sql_handle => ‘&sql_handle’,plan_name  => ‘&plan_name’,attribute_name => ‘FIXED’,   attribute_value => ‘NO’);

end;

/

Enter value for sql_handle: SYS_SQL_6c0845687118fc3f

Enter value for plan_name: SYS_SQL_PLAN_7118fc3f642e4a26

/* Resetting SYS_SQL_PLAN_7118fc3f642e4a26 to ACCEPTED=NO */

declare

myplan pls_integer;

begin

myplan:=DBMS_SPM.ALTER_SQL_PLAN_BASELINE (sql_handle => ‘&sql_handle’,plan_name  => ‘&plan_name’,attribute_name => ‘ACCEPTED’,   attribute_value => ‘NO’);

end;

/

Enter value for sql_handle: SYS_SQL_6c0845687118fc3f

Enter value for plan_name: SYS_SQL_PLAN_7118fc3f642e4a26

select sql_handle, plan_name, enabled, accepted,fixed,origin from dba_sql_plan_baselines where signature=7784548270786280511;

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX ORIGIN

—————————— —————————— — — — ————–

SYS_SQL_6c0845687118fc3f       SYS_SQL_PLAN_7118fc3f642e4a26  YES NO  NO  AUTO-CAPTURE

SYS_SQL_6c0845687118fc3f       SYS_SQL_PLAN_7118fc3f97bbe3d0  YES YES YES MANUAL-LOAD

Lets try to evolve the index plan (SYS_SQL_PLAN_7118fc3f642e4a26) again, like we did previously. The only difference is fixed=yes this time for plan using FTS

/* TRYING TO EVOLVE SYS_SQL_PLAN_7118fc3f642e4a26 */

SET SERVEROUTPUT ON

SET LONG 10000

set lines 175

DECLARE

report clob;

BEGIN

report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(

sql_handle => ‘&sql_handle’, time_limit => &time);

DBMS_OUTPUT.PUT_LINE(report);

END;

/

Enter value for sql_handle: SYS_SQL_6c0845687118fc3f

Enter value for time: 60

——————————————————————————-

Evolve SQL Plan Baseline

Report

——————————————————————————-

Inputs:

——-

SQL_HANDLE = SYS_SQL_6c0845687118fc3f

PLAN_NAME  =

TIME_LIMIT = 60

VERIFY     = YES

COMMIT     = YES

——————————————————————————-

Report

Summary

——————————————————————————-

There were no SQL plan baselines that required processing.

As we can confirm, plan SYS_SQL_PLAN_7118fc3f642e4a26 is not evolved since SYS_SQL_PLAN_7118fc3f97bbe3d0 was already fixed.

TEST5:

Lets go back a step further and drop the baseline as well as index. We will try to simulate a test case to check if oracle captures new plan if any plan is marked as FIXED=YES

SET SERVEROUTPUT ON

DECLARE

l_plans_dropped  PLS_INTEGER;

BEGIN

l_plans_dropped := DBMS_SPM.drop_sql_plan_baseline (

sql_handle => ‘SYS_SQL_6c0845687118fc3f’,

plan_name  => ‘SYS_SQL_PLAN_7118fc3f642e4a26’);

DBMS_OUTPUT.put_line(l_plans_dropped);

END;

/

drop index test_id;

Index dropped.

select sql_handle, plan_name, enabled, accepted,fixed,origin from dba_sql_plan_baselines where signature=7784548270786280511;

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX ORIGIN

—————————— —————————— — — — ————–

SYS_SQL_6c0845687118fc3f       SYS_SQL_PLAN_7118fc3f97bbe3d0  YES YES YES MANUAL-LOAD

1 row selected.

So, we are back to square one from where we have started. Lets again create the same index and query the table to see if it still capture new plans.

create index test_id on test(id);

Index created.

select /*+ id=1000 */  * FROM TEST where id=1000;

NAME               ID

———- ———-

SUMIT            1000

1 row selected.

select sql_handle, plan_name, enabled, accepted,fixed,origin from dba_sql_plan_baselines where signature=7784548270786280511;

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX ORIGIN

—————————— —————————— — — — ————–

SYS_SQL_6c0845687118fc3f       SYS_SQL_PLAN_7118fc3f97bbe3d0  YES YES YES MANUAL-LOAD

1 row selected.

As we can see, oracle has stopped capturing new plans for this sql as SYS_SQL_PLAN_7118fc3f97bbe3d0 is already fixed.

Lets mark fixed=NO again and re-execute the query. Oracle should capturing the plan again for this query.

select sql_handle, plan_name, enabled, accepted,fixed,origin from dba_sql_plan_baselines where signature=7784548270786280511;

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX ORIGIN

—————————— —————————— — — — ————–

SYS_SQL_6c0845687118fc3f       SYS_SQL_PLAN_7118fc3f97bbe3d0  YES YES YES MANUAL-LOAD

1 row selected.

declare

myplan pls_integer;

begin

myplan:=DBMS_SPM.ALTER_SQL_PLAN_BASELINE (sql_handle => ‘&sql_handle’,plan_name  => ‘&plan_name’,attribute_name => ‘FIXED’,   attribute_value => ‘NO’);

end;

/

Enter value for sql_handle: SYS_SQL_6c0845687118fc3f

Enter value for plan_name: SYS_SQL_PLAN_7118fc3f97bbe3d0

select /*+ id=1000 */  * FROM TEST where id=1000

NAME               ID

———- ———-

SUMIT            1000

1 row selected.

select sql_handle, plan_name, enabled, accepted,fixed,origin from dba_sql_plan_baselines where signature=7784548270786280511;

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX ORIGIN

—————————— —————————— — — — ————–

SYS_SQL_6c0845687118fc3f       SYS_SQL_PLAN_7118fc3f642e4a26  YES NO  NO  AUTO-CAPTURE

SYS_SQL_6c0845687118fc3f       SYS_SQL_PLAN_7118fc3f97bbe3d0  YES YES NO  MANUAL-LOAD

2 rows selected.

Here’s the quick summary,

1.  ENABLED=YES (For any of the plan) :- Oracle will start capturing new plans for those queries.

2. Enabled=NO :-  Than plan won’t we used.

3.  Accepted=Yes (Any one Plan) :- That plan will be used for execution

4.  Accepted=YES (For Multiple plans):- Any one plan can be used if fixed=no for all plans.

5. Accepted=No: Plan wont be used.

6.  FIXED=YES (for only one plan) Only that plan will be used and oracle will stop capturing/evolving new plans.

7.  FIXED=YES (for many plans) Oracle will chose execution plan only from that pool.

SPECIAL CASE :-ENABLED=YES,FIXED=YES BUT ACCEPTED=NO.

What if in our given scenario, we mark SYS_SQL_PLAN_7118fc3f97bbe3d0 as fixed and enabled but not accepted. Will oracle capture the new plan and will it evolve it.

select sql_handle, plan_name, enabled, accepted,fixed,origin from dba_sql_plan_baselines where signature=7784548270786280511;

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX ORIGIN

—————————— —————————— — — — ————–

SYS_SQL_6c0845687118fc3f       SYS_SQL_PLAN_7118fc3f97bbe3d0  YES NO  YES MANUAL-LOAD

1 row selected.

Lets discuss the above scenario and try to figure out who takes precedence under what condition

select /*+ id=1000 */  * FROM TEST where id=1000;

NAME               ID

———- ———-

SUMIT            1000

1 row selected.

select sql_handle, plan_name, enabled, accepted,fixed,origin from dba_sql_plan_baselines where signature=7784548270786280511;

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX ORIGIN

—————————— —————————— — — — ————–

SYS_SQL_6c0845687118fc3f       SYS_SQL_PLAN_7118fc3f642e4a26  YES NO  NO  AUTO-CAPTURE

SYS_SQL_6c0845687118fc3f       SYS_SQL_PLAN_7118fc3f97bbe3d0  YES NO  YES MANUAL-LOAD

2 rows selected.

Oracle has captured new plan. Lets try to evolve that one also.

SET SERVEROUTPUT ON

SET LONG 10000

set lines 175

DECLARE

report clob;

BEGIN

report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(

sql_handle => ‘&sql_handle’, time_limit => &time);

DBMS_OUTPUT.PUT_LINE(report);

END;

/

——————————————————————————-

Evolve SQL Plan Baseline

Report

——————————————————————————-

Inputs:

——-

SQL_HANDLE = SYS_SQL_6c0845687118fc3f

PLAN_NAME  =

TIME_LIMIT =

60

VERIFY     = YES

COMMIT     = YES

——————————————————————————-

Report

Summary

——————————————————————————-

There were no SQL plan baselines that required processing.

select sql_handle, plan_name, enabled, accepted,fixed,origin from dba_sql_plan_baselines where signature=7784548270786280511;

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX ORIGIN

—————————— —————————— — — — ————–

SYS_SQL_6c0845687118fc3f       SYS_SQL_PLAN_7118fc3f642e4a26  YES NO  NO  AUTO-CAPTURE

SYS_SQL_6c0845687118fc3f       SYS_SQL_PLAN_7118fc3f97bbe3d0  YES NO  YES MANUAL-LOAD

2 rows selected.

ENABLED takes precedence over FIXED in case of capturing the new plan and vice versa in case of evolving.