RMAN: All Backups Are Not Created Equal

I came across an interesting behavior exhibited by RMAN in a recent test I was conducting. I thought I will share this here. The scenario has been explained with a sample below.

The test was conducted in a Oracle 11g single instance environment running on OEL 4. But I tested the similar response to be true in 10g as well.

Here is a simple question: Will the size of a level 1 incremental be bigger than a FULL backup?

Answer: It depends. If the volume of change after the full backup is higher than the size of the full backup, then the incremental backup will be bigger in size, irrespective of the fact that the incremental being a “delta”.

But If I were to ask the question a little differently, Will a "LEVEL 1 INCREMENTAL" backup without a "prior" level 0 backup, be of equal size compared to a "FULL" backup, then the answer most probably will be: YES.

The assumption is that a FULL backup is nothing but a LEVEL 0 backup. If in the absence of a LEVEL 0 backup, an incremental LEVEL 1 backup will act as a LEVEL 0 backup. So, this will exhibit similar behavior. In other words, the size of the "FULL BACKUP" and an "INCREMENTAL LEVEL 1" will be of the same size.

I was surprised to learn that this is not the case *always*.

We all know, that based on the compatibility settings, whether its >=10.0 or less, the behavior of the incremental backups change. Essentially, A “FULL” backup is not very useful in an incremental backup strategy. So it is important that we do a Level 0 and Level 1 backups to maintain a viable incremental backup and recovery strategy. A level 1 backup without a level 0 is akin to a FULL backup with the advantage of an implementable incremental backup strategy. However, lack of understanding of the behavior of incremental backup options will have serious consequences when we reach implementation.

Check this out.

create tablespace test_5mb_tbs
datafile '/oradata2/OSI11/tbs_tbs_tbs_1.dbf' size 5M
extent management local

11:12:07 SQL> create table rman_bkup_test(col1 number)
11:12:54 2 tablespace test_5mb_tbs;

Table created.

select count(extent_id)ext_id ,segment_name
from dba_extents
where segment_name='RMAN_BKUP_TEST' group by segment_name
11:16:41 SQL> /

EXT_ID SEGMENT_NAME
------ ---------------
1 RMAN_BKUP_TEST

11:16:41 SQL> declare
11:17:00 2 begin
11:17:02 3 for x in 1..50000 loop
11:17:07 4 insert into rman_bkup_test(col1) values (x);
11:17:16 5 end loop;
11:17:18 6 end;
11:17:20 7 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:07.50
11:17:28 SQL> get afiedt.buf
1 select count(extent_id)ext_id ,segment_name
2 from dba_extents
3* where segment_name='RMAN_BKUP_TEST' group by segment_name
11:17:41 SQL> /

EXT_ID SEGMENT_NAME
------ ---------------
11 RMAN_BKUP_TEST

By running a block, we fill it up to 11 extents.

*All the RMAN output is edited for conciseness*

RMAN> backup full tablespace test_5mb_tbs tag='full_backup';

Starting backup at 24-APR-09
piece handle=/oradata3/OSI11/BACKUP/17kd94q0_1_1 tag=FULL_BACKUP

RMAN> backup incremental level 1 tablespace test_5mb_tbs tag='inc_lvl1';

piece handle=/oradata3/OSI11/BACKUP/18kd94sr_1_1 tag=INC_LVL1

[ora11rac@linux1 BACKUP]$ ls -lrt
total 1636
-rw-r--r-- 1 ora11rac dbarac11 113 Apr 16 16:47 afiedt.buf
-rw-r----- 1 ora11rac dbarac11 843776 Apr 24 11:19 17kd94q0_1_1
-rw-r----- 1 ora11rac dbarac11 819200 Apr 24 11:21 18kd94sr_1_1

As we can see, the full backup and a level 1 incremental backup without a level 0 base results in a backup piece of approximately the same size.

11:35:39 SQL> delete from rman_bkup_test where rownum <48001;
11:35:58 SQL> commit;

RMAN> backup full tablespace test_5mb_tbs tag='full_aft_del';

piece handle=/oradata3/OSI11/BACKUP/19kd96ep_1_1 tag=FULL_AFT_DEL comment=NONE

About 48K rows gets deleted and a full backup is taken again. Before I do this backup, I delete the backup sets from previous iteration. I delete the RMAN backups for every iteration, throughout this exercise.

[ora11rac@linux1 BACKUP]$ ls -lt
total 832
-rw-r----- 1 ora11rac dbarac11 843776 Apr 24 11:47 19kd96ep_1_1

12:02:31 SQL> alter table rman_bkup_test enable row movement;
12:02:43 SQL> alter table rman_bkup_test shrink space;
RMAN> backup full tablespace test_5mb_tbs tag='full_aft_shrink';

piece handle=/oradata3/OSI11/BACKUP/1akd97e9_1_1

[ora11rac@linux1 BACKUP]$ ls -lrt
total 632
-rw-r--r-- 1 ora11rac dbarac11 113 Apr 16 16:47 afiedt.buf
-rw-r----- 1 ora11rac dbarac11 638976 Apr 24 12:04 1akd97e9_1_1

After the shrink, the full backup results in a 630K backup piece instead of the 800K size from before.

RMAN> backup incremental level 1 tablespace test_5mb_tbs tag='level1_aft_shrink ';

piece handle=/oradata3/OSI11/BACKUP/1bkd97hd_1_1
[ora11rac@linux1 BACKUP]$ ls -lrt
total 1436
-rw-r--r-- 1 ora11rac dbarac11 113 Apr 16 16:47 afiedt.buf
-rw-r----- 1 ora11rac dbarac11 638976 Apr 24 12:04 1akd97e9_1_1
-rw-r----- 1 ora11rac dbarac11 819200 Apr 24 12:06 1bkd97hd_1_1

However, if I did an incremental level 1, even though this is *exactly* similar to a full backup with the advantage of qualifying to be part of an incremental backup strategy, the size of the backup does not change from the first test. If you notice, this size is similar to the size we noticed from the initial level 0 backup, before the 48K rows were deleted.

RMAN> backup incremental level 0 tablespace test_5mb_tbs tag='level0_aft_shrink';

piece handle=/oradata3/OSI11/BACKUP/1ckd9e5s_1_1 tag=LEVEL0_AFT_SHRINK comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 24-APR-09

[ora11rac@linux1 BACKUP]$ ls -l
total 632
-rw-r----- 1 ora11rac dbarac11 638976 Apr 24 13:59 1ckd9e5s_1_1

Backup incremental level 0 produces a backup size that is similar to a FULL backup.

To summarize: Though a Level 1 w/o a Level 0 is similar to a full backup and can be part of an incremental backup strategy, various factors, influence the backup size, and performance (time taken) to perform the level 1. Understanding the implications of the various backup types is critical when implementing a backup strategy.

It is important to implement an incremental backup strategy with a level 0 base backup, no matter if a level 1 could be (indirectly) used as a starting point.


As always, comments/suggestions are appreciated.

Plan Capture and Playback

Quite often performance tuning activity mainly involves working with execution plans and trying to identify the root cause of the problem and subsequently taking corrective actions.

Oracle's CBO is getting better by the day in determining the optimal execution plan and thus providing the best performance possible. However, there is always room for improvement and performance experts could help since they happen to have better information regarding the "data". In cases where we are tasked to work on sql performance issues, it is an often heard comment that, "this was working fine till last week and *NO CHANGE* was made to the system", yet the performance is horrible since morning. We need a fix.

In situations like these, we wish we had access to the execution plan from the "best performing" time so we can compare to the current plan. In many cases, this will help us quickly nail down the reason for the poor sql execution and take appropriate fix.

There are many ways to get plans. One of them being from the AWR data collected itself. But this would mean doing a lot more work, collecting more data than necessary. One solution would be to collect and store the execution plans for all SQLs in SQL CACHE based on a repeated interval.

Assuming the SQL itself remained unchanged; the SQL_ID will remain the same. This piece of information could be used to query for versions of sql plans for a given sql and start narrowing down to fix the problem.

The script below provides a simple solution to query and capture the execution plans for future troubleshooting.

**********************************************************************************
The script has been tested in both 10g and 11g environments. Please test the script in your sandbox environment and make sure it conforms to your needs and satisfaction before proceeding.
**********************************************************************************

Couple of comments:

1. The script prompts for SYS password to perform some "grants".
2. It expects a db username that will own the package, table and indexes to accomplish the task set forth.
3. I have commented couple of statements: "a drop table" before creating it, and a "drop job". I did not want to drop something with the same name in your schema inadvertently. Please make appropriate changes before executing.
4. The package currently captures the plan for a given SQL once per day. This is mainly to avoid capturing the same SQL/PLAN more than necessary. You may have to comment out the "where clause" in cursor c1 is this is not desirable and you want the plan captured no matter what.
5. The scheduler job's repeat interval is set to "1 minute". Make changes to fit your needs.





prompt please enter sys password
accept sys_password
prompt please enter the username that will own package
accept username
prompt please enter password
accept password
conn sys/&&sys_password as sysdba
grant resource, connect, create job to &&username;
grant select on v_$sql_plan to &&username;
conn &&username/&&password
--drop table plan_capture_hist;
CREATE TABLE PLAN_CAPTURE_HIST
(
EX_PLAN VARCHAR2(2000 BYTE) NULL,
SQL_ID VARCHAR2(20 BYTE) NULL,
PLAN_HASH_VALUE NUMBER NULL,
CAPTURED_DATE DATE DEFAULT SYSDATE NULL,
SQL_HASH_VALUE NUMBER NULL,
ITERATION NUMBER NULL,
COMMENTS VARCHAR2(2000 BYTE) NULL,
CHILD_NUMBER NUMBER(38) NULL,
RN NUMBER NULL
)
/
CREATE INDEX PCH_SQL_ID ON PLAN_CAPTURE_HIST
(SQL_ID)
/
create index fn_pch_captured_dt on plan_capture_hist(trunc(captured_date))
/
drop package xx_plan_capture;
CREATE OR REPLACE PACKAGE xx_plan_capture IS
PROCEDURE sqlcache_capture;
PROCEDURE sqlid_capture(v_sql_id IN VARCHAR2,v_child_number IN NUMBER);
END;
/
CREATE OR REPLACE PACKAGE BODY xx_plan_capture IS
PROCEDURE sqlcache_capture IS
v_sql_id VARCHAR2(20);
b NUMBER := NULL;
--defaults to the parameters passed to display_cursor
c VARCHAR2(30) := 'TYPICAL ALLSTATS -NOTE';
-- set the plan_capture iteration count for the day
v_iteration NUMBER;
CURSOR c1 IS
SELECT DISTINCT a.sql_id,a.plan_hash_value,a.hash_value,a.child_number
FROM sys.v_$sql_plan A
MINUS
SELECT sql_id,plan_hash_value,sql_hash_value,child_number
FROM plan_capture_hist
where trunc(captured_date) = trunc(sysdate);
CURSOR c2 IS
SELECT ROWNUM rn, plan_table_output
FROM (SELECT * FROM TABLE(dbms_xplan.display_cursor(v_sql_id,b,c)));
BEGIN
SELECT nvl2(MAX(iteration),MAX(iteration)+1,1) INTO v_iteration
FROM plan_capture_hist WHERE TRUNC(captured_date)=TRUNC(SYSDATE);
FOR c_plan_details IN c1 LOOP
BEGIN
v_sql_id := c_plan_details.sql_id;
b :=c_plan_details.child_number;
FOR c_sql_plan IN c2 LOOP
INSERT INTO plan_capture_hist(ex_plan,sql_id,plan_hash_value,sql_hash_value,iteration,child_number,rn)
VALUES (c_sql_plan.plan_table_output,c_plan_details.sql_id,c_plan_details.plan_hash_value,
c_plan_details.hash_value,v_iteration,c_plan_details.child_number,c_sql_plan.rn);
END LOOP; --c_sql_plan
END;
END LOOP; --c_plan_details
COMMIT;
END; --sqlcache_capture
PROCEDURE sqlid_capture(v_sql_id IN VARCHAR2,v_child_number IN NUMBER) IS
b NUMBER := NULL;
--defaults to the parameters passed to display_cursor
c VARCHAR2(30) := 'TYPICAL ALLSTATS -NOTE';
-- set the plan_capture iteration count for the day
v_iteration NUMBER;
x_sql_id VARCHAR2(20);
v1_sql_id VARCHAR2(20);
CURSOR c1(v1_sql_id VARCHAR2) IS
SELECT DISTINCT a.sql_id,a.plan_hash_value,a.hash_value,a.child_number
FROM sys.v_$sql_plan A
WHERE a.sql_id = v_sql_id AND child_number=v_child_number;
CURSOR c2 IS
SELECT ROWNUM rn, plan_table_output
FROM (SELECT * FROM TABLE(dbms_xplan.display_cursor(v_sql_id,v_child_number,c)));
BEGIN
SELECT nvl2(MAX(iteration),MAX(iteration)+1,1) INTO v_iteration
FROM plan_capture_hist WHERE TRUNC(captured_date)=TRUNC(SYSDATE);
dbms_output.put_line(v_iteration);
FOR c_plan_details IN c1(v1_sql_id) LOOP
dbms_output.put_line(c_plan_details.sql_id);
BEGIN
FOR c_sql_plan IN c2 LOOP
--dbms_output.put_line(c_sql_plan.plan_table_output);
INSERT INTO plan_capture_hist(ex_plan,sql_id,plan_hash_value,sql_hash_value,iteration,child_number,rn)
VALUES (c_sql_plan.plan_table_output,c_plan_details.sql_id,c_plan_details.plan_hash_value,
c_plan_details.hash_value,v_iteration,c_plan_details.child_number,c_sql_plan.rn);
END LOOP; -- c_sql_plan
END;
END LOOP; --cursor c_plan_details
COMMIT;
END; --sqlcache_capture
END; --package body
/
--exec dbms_scheduler.drop_job('sql_plan_capture');
exec DBMS_SCHEDULER.create_job( job_name => 'sql_plan_capture', job_type=>'STORED_PROCEDURE' -
,job_action=>'xx_plan_capture.sqlcache_capture' -
, start_date => SYSTIMESTAMP -
, repeat_interval => 'FREQ=MINUTELY; INTERVAL=1' -
, end_date => NULL, enabled => TRUE, comments => 'Plan Capture for all SQL Cache Cursors');

V$Views and Permissions. Interesting Issue.

I was working on creating a package to capture and store runtime execution plans for historical review and troubleshooting purposes. More on that later. While I was working on the package, I came across this interesting issue. I thought I might share this. In the snippet below, I have reduced the code to illustrate the issue at hand.

I named the schema "SQLCAPTURE". So going ahead with the example:

By logging in as SYS I create the SQLCAPTURE database user and assign privileges.

SQL> create user sqlcapture identified by sqlcapture;

User created.

SQL> grant dba,scheduler_admin to sqlcapture;

Grant succeeded.

Now I log in as SQLCAPTURE and Compile the Package.


THE COMPLETE CODE IN SQL_CAPTURE1 Script

CREATE OR REPLACE PACKAGE xx_plan_capture1 IS
PROCEDURE sqlcache_capture;
END;
/
CREATE OR REPLACE PACKAGE BODY xx_plan_capture1 IS
PROCEDURE sqlcache_capture IS
v_iteration NUMBER;
begin
SELECT count( a.sql_id ) into v_iteration
FROM v$sql_plan A where rownum <2;
dbms_output.put_line(v_iteration);
END; --sqlcache_capture

END; --package body
/

SQL> conn sqlcapture/sqlcapture
Connected.
SQL> @E:\sql_capture1
Package created.


Warning: Package Body created with compilation errors.

It returns an error. Upon finding out the offending statement:

SQL> sho err
Errors for PACKAGE BODY XX_PLAN_CAPTURE1:

LINE/COL ERROR
-------- --------------------------------------------------
5/1 PL/SQL: SQL Statement ignored
6/6 PL/SQL: ORA-00942: table or view does not exist
SQL> l 6
6* FROM v$sql_plan A where rownum <2; href="https://metalink.oracle.com/metalink/plsql/f?p=130:14:5850005794970861733::::p14_database_id,p14_docid,p14_show_header,p14_show_help,p14_black_frame,p14_font:NOT,1062335.6,1,1,1,helvetica">Metalink Note), I do that.

SQL> sho user
USER is "SYS"
SQL> grant select on v_$sql_plan to sqlcapture;

SQL> sho user
USER is "SQLCAPTURE"
SQL> @E:\sql_capture1

Package created.


Package body created.

All is well, the Package and Package Body is created successfully.

Now comes the interesting part.

By logging in as SYS, I revoke the grant on V_$SQL_PLAN
SQL> revoke select on v_$sql_plan from sqlcapture;

Then as SQLCAPTURE, I recompile the script.

SQL> sho user
USER is "SQLCAPTURE"
SQL> @E:\sql_capture1

Package created.


Package body created.
SQL> col object_name for a19
SQL> select object_name,object_type,status from user_objects where object_type like 'PACKAGE%';

OBJECT_NAME OBJECT_TYPE STATUS
------------------- ------------------- -------
XX_PLAN_CAPTURE1 PACKAGE VALID
XX_PLAN_CAPTURE1 PACKAGE BODY VALID

It Compiles!!!!!

Though at runtime it fails with 942, Interesting to know it compiled.

I guess the takeaway is while coding your own scripts using V$views, it is safer to use the underlying (SYS) tables. Otherwise, at runtime you might
end up with ugly errors and unsuccessful execution of your procedures.

I could not reproduce this in 11g as the package compiles in 11g without even having to give the GRANT on the base table.

Stored Outlines Last Usage Time

When it comes to Oracle performance tuning, one of the invaluable features that DBAs and Performance experts have in their toolbox is SQL Stored Outlines. Much has been written about stored outlines since it earlier days. One of such interesting articles, can be found here.

Though the supplied PL/SQL package provides many useful routines to play with the outlines, one critical item that is being missed in the package set is a routine to indicate whether a stored outline is consistently being used? And more importantly, when was the last time a stored outline was used? Granted, we can “clear” the stored outlines’ usage and see if it resets to ensure that the outline is being used but that seems too much intrusion to the live production system. While working on this issue, I discovered a simpler way to determine the last time an outline had been used for a given SQL.

With Oracle 10g there has been great improvements in the V$ views area providing a wealth of information without the need to access many V$ views. This tradition has been carried over to 10g R2 as well. The V$SQL view in 10g R2 has new columns that cater to stored outlines. One such column is the LAST_ACTIVE_TIME. By querying the SQL_ID, OUTLINE_CATEGORY, and LAST_ACTIVE_TIME columns from V$SQL you can verify whether a stored outline is being actively used and also the last time it was used.

Here is an example.

NOTE: The outline creation steps are assumed.

11:52:27 SQL> alter session set use_stored_outlines=HV;

Session altered.

11:55:43 SQL> select count(*) from ptn_tab where col2=2;

COUNT(*)
----------
300000
11:56:57 SQL> select name,used from dba_outlines where name='HV';

NAME USED
-------------------- ------
HV USED

11:58:00 SQL> exec dbms_outln.clear_used('HV');

PL/SQL procedure successfully completed.

11:58:10 SQL> select name,used from dba_outlines where name='HV';

NAME USED
-------------------- ------
HV UNUSED
11:58:18 SQL> select count(*) from ptn_tab where col2=2;

COUNT(*)
----------
300000

11:58:59 SQL> select name,used from dba_outlines where name='HV';

NAME USED
-------------------- ------
HV USED

11:59:04 SQL>

1 select sql_id,outline_category,exact_matching_signature,
2 force_matching_signature,last_active_time
3* from v$sql where sql_text like 'select count(*) from ptn_tab%'
11:59:06 SQL> /

SQL_ID OU EXACT_MATCHING_SIGNATURE FORCE_MATCHING_SIGNATURE
------------- -- ------------------------ ------------------------
LAST_ACTIVE_TIME
--------------------
10d6hmvy8t74n HV 0 0
08-FEB-2008 11:58:59