ASMCMD examples

asmcmd is a command line tool that we can use to manage Oracle ASM instances, ASM disk groups, files and dierctories, templates very much as in the same way we would do while working with say a UNIX file system.

ASMCMD can be launched in interactive or non-interactive modes and we need to first ensure that our environment points to the “Grid Infrastructure Home” as in 11g R2, ASM is not part of the standard database software installation. Also chec that the ORACLE_SID points to the ASM instance which should be running.

Let us have a quick look at some of the useful command options which are now available with 11g Release 2.

lsct: Lists information about current Oracle ASM clients from the V$ASM_CLIENT view.

ASMCMD> lsct
DB_Name  Status     Software_Version  Compatible_version  Instance_Name  Disk_Group
+ASM     CONNECTED        11.2.0.1.0          11.2.0.1.0  +ASM           DATA
testdb   CONNECTED        11.2.0.1.0          11.2.0.0.0  testdb         DATA

cp: We can copy files from ASM to OS as wel as from OS to ASM disk groups

ASMCMD> cp EXAMPLE.265.697907183 /tmp/example01.bak
copying +DATA/TESTDB/DATAFILE/EXAMPLE.265.697907183 -> /tmp/example01.bak

ASMCMD> cp /tmp/example01.bak +DATA/TESTDB/DATAFILE/example01.bak
copying /tmp/example01.bak -> +DATA/TESTDB/DATAFILE/example01.bak

ASMCMD> ls
EXAMPLE.265.697907183
SYSAUX.257.697907095
SYSTEM.256.697907095
UNDOTBS1.258.697907095
USERS.259.697907095
example01.bak

du: Total space in MB used by files – particular directory can also be specified

ASMCMD> pwd
+DATA/TESTDB/DATAFILE

ASMCMD> du
Used_MB      Mirror_used_MB
   1574                1574

ASMCMD> du +DATA/TESTDB/ONLINELOG
Used_MB      Mirror_used_MB
    153                 153

find: we can use the wildcard or can specify a particular file type by using the ‘–type’ clause

ASMCMD> find --type ONLINELOG +DATA *
+DATA/TESTDB/ONLINELOG/group_1.261.697907177
+DATA/TESTDB/ONLINELOG/group_2.262.697907179
+DATA/TESTDB/ONLINELOG/group_3.263.697907179

ASMCMD> find +DATA example*
+DATA/ASM/DATAFILE/example01.bak.267.698929915
+DATA/TESTDB/DATAFILE/EXAMPLE.265.697907183
+DATA/TESTDB/DATAFILE/example01.bak

ls: list the contents of an ASM Disk Group directory as well as attributes of files located in the directory

ASMCMD> ls -s
Block_Size  Blocks      Bytes      Space  Name
      8192   12801  104865792  106954752  EXAMPLE.265.697907183
      8192   89601  734011392  736100352  SYSAUX.257.697907095
      8192   89601  734011392  736100352  SYSTEM.256.697907095
      8192    7681   62922752   65011712  UNDOTBS1.258.697907095
      8192     641    5251072    6291456  USERS.259.697907095
                                          example01.bak => +DATA/ASM/DATAFILE/example01.bak.267.698929915

ASMCMD> ls -l
Type      Redund  Striped  Time             Sys  Name
DATAFILE  UNPROT  COARSE   SEP 30 11:00:00  Y    EXAMPLE.265.697907183
DATAFILE  UNPROT  COARSE   SEP 28 23:00:00  Y    SYSAUX.257.697907095
DATAFILE  UNPROT  COARSE   SEP 29 22:00:00  Y    SYSTEM.256.697907095
DATAFILE  UNPROT  COARSE   SEP 18 22:00:00  Y    UNDOTBS1.258.697907095
DATAFILE  UNPROT  COARSE   SEP 18 22:00:00  Y    USERS.259.697907095
                                            N    example01.bak => +DATA/ASM/DATAFILE/example01.bak.267.698929915

iostat: Uses the V$ASM_DISK_IOSTAT view to display I/O statistics of disks in mounted ASM disk groups

ASMCMD> iostat -G DATA
Group_Name  Dsk_Name   Reads        Writes
DATA        DATA_0000  25448671744  19818926592

ASMCMD> iostat -t
Group_Name  Dsk_Name   Reads        Writes       Read_Time    Write_Time
DATA        DATA_0000  25450195456  19819686912  6491.434444  8042.604156

lsdg: Uses V$ASM_DISKGROUP_STAT view to list information about a particular disk group

ASMCMD> lsdg DATA
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB    Name
MOUNTED  EXTERN  N         512   4096  1048576     51200    49375                0           49375    DATA/

ASMCMD> lsdsk -t -G DATA
Create_Date  Mount_Date  Repair_Timer  Path
18-SEP-09    18-SEP-09   0             /dev/raw/raw1

lsattr: List attributes of a disk group

ASMCMD> lsattr -l -G DATA
Name                     Value
access_control.enabled   FALSE
access_control.umask     066
au_size                  1048576
cell.smart_scan_capable  FALSE
compatible.asm           11.2.0.0.0
compatible.rdbms         10.1.0.0.0
disk_repair_time         3.6h
sector_size              512

Use the setattr command to change an attribute

ASMCMD> setattr -G data compatible.rdbms 11.2.0.0.0
ASMCMD> lsattr -l -G DATA
Name                     Value
access_control.enabled   FALSE
access_control.umask     066
au_size                  1048576
cell.smart_scan_capable  FALSE
compatible.asm           11.2.0.0.0
compatible.rdbms         11.2.0.0.0
disk_repair_time         3.6h
sector_size              512

10g EM Grid Control Notification Rules and Corrective Action Jobs

In 10g Enterprise Manager Grid Control, we can create corrective action or ‘fixit’ jobs which can automatically run if say a metric threshold is crossed or a particular event occurs. For example, we may have situations where we monitor the disk space used by the archive log files and as soon as the disk space free or available comes below a threshold, we would like another action to automatically occur which in this case could be to backup the archive log files to tape and then delete them to free up the disk space.

Read more about this feature in this worked example …….

11g RMAN Substitution Variables

We can now use SQL*PLUS type substitution variables in RMAN like &1, &2 and so on and the same can be linked to shell scripts as shown below with the new clause USING where we are passing the Incremental Level and the backup tag to the actual RMAN command.

Also note that in 11g we can run RMAN scripts using the ‘@’ like a SQL script

testbkp.rcv

connect target sys/oracle
run{
backup incremental level &1
database
tag &2;
}
exit;

testbkp.sh

#! /bin/ksh
export level=$1
export tag=$2_`date +%d%b%y`
rman @testbkp.rcv using $level $tag

Note the actual RMAN command that is run …….. it adds a date along with the tag name (parameter 2) and runs a level 2 backup as the first parameter was ‘2′

apex:/u01/oracle> ./testbkp.sh 2 dly_inc_bkp

Recovery Manager: Release 11.1.0.6.0 – Production on Tue Sep 22 11:28:05 2009

Copyright (c) 1982, 2007, Oracle. All rights reserved.

RMAN> connect target *
2> run{
3> backup incremental level 2
4> database
5> tag dly_inc_bkp_22Sep09;
6> }
7> exit;
connected to target database: APEX (DBID=1312143933)

Starting backup at 22-SEP-09
using target database control file instead of recovery catalog
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=349 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: Data Protection for Oracle: version 5.5.1.0
channel ORA_SBT_TAPE_1: starting incremental level 2 datafile backup set
channel ORA_SBT_TAPE_1: specifying datafile(s) in backup set
input datafile file number=00003 name=/u02/oradata/apex/undotbs01.dbf
input datafile file number=00002 name=/u02/oradata/apex/sysaux01.dbf
input datafile file number=00001 name=/u02/oradata/apex/system01.dbf
input datafile file number=00005 name=/u02/oradata/apex/monitor_data01.dbf
input datafile file number=00007 name=/u02/oradata/apex/encrypt_data01.dbf
input datafile file number=00008 name=/u02/oradata/apex/disc10_data01.dbf
channel ORA_SBT_TAPE_1: starting piece 1 at 22-SEP-09
channel ORA_SBT_TAPE_1: finished piece 1 at 22-SEP-09
piece handle=a5kpsim9_1_1 tag=DLY_INC_BKP_22SEP09 comment=API Version 2.0,MMS Version 5.5.1.0
channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:25
Finished backup at 22-SEP-09

Starting Control File and SPFILE Autobackup at 22-SEP-09
piece handle=c-1312143933-20090922-05 comment=API Version 2.0,MMS Version 5.5.1.0
Finished Control File and SPFILE Autobackup at 22-SEP-09

Recovery Manager complete.

11g Release 2 Edition-based Redefinition

11g Release 2 Edition-based redefinition enables you to upgrade or change the database component of an application while it is in use, thereby minimising or eliminating down time which was earlier required when an object like a procedure required to be modified, but we could not do it without any outage as the application was accessing the procedure or package which needed to be modified.

An edition is like a workspace or private environment where database objects are redefined. When we are satisfied with the change that we have made, those changes in the edition can be then rolled out to all the application users.

Let us see an example of this where we create a procedure, make some changes to the procedure in a new edition and then make those changes visible to other database users.

SQL>  GRANT CREATE ANY EDITION, DROP ANY EDITION to sh;

Grant succeeded.

SQL> conn sh/sh
Connected.

SQL> CREATE OR REPLACE PROCEDURE hello IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE('Hello, edition 1.');
  END hello;
/
  2    3    4    5
Procedure created.

SQL> set serverout on

SQL> BEGIN hello(); END;
/
  2  Hello, edition 1.

PL/SQL procedure successfully completed.

SQL> CREATE EDITION e2;

Edition created.

SQL> ALTER SESSION SET EDITION = e2;

Session altered.

SQL> CREATE OR REPLACE PROCEDURE hello IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE('Hello, edition 2.');
  END hello;
/
  2    3    4    5
Procedure created.

SQL> BEGIN hello(); END;
  2  /
Hello, edition 2.

PL/SQL procedure successfully completed.

Note, now if we change the edition to the default edition ‘ORA$BASE’, we will now see the original procedure and not the updated one

SQL> ALTER SESSION SET EDITION = ora$base;

Session altered.

SQL>  BEGIN hello(); END;
  2  /
Hello, edition 1.

Let us now see how this can be rolled out to database user HR

SQL> grant execute on hello to hr;

Grant succeeded.

SQL> GRANT USE ON EDITION e2 to public;

Grant succeeded.

We now make the default edition of the database, E2 so that all changes in the new edition E2 will be visible to all database users

SQL> conn sys as sysdba
Enter password:
Connected.

SQL> ALTER DATABASE DEFAULT EDITION=e2;

Database altered.

SQL> conn hr/hr
Connected.

SQL> set serverout on

SQL>  exec sh.hello;
Hello, edition 2.

11g Pending and Published Statistics

Prior to Oracle 11g, the default behaviour was to make statistics available for the optimizer to use as soon as they were gathered.

While statistics are required to enable the optimizer to generate optimal execution plans, sometimes just by gathering fresh statistics tried and trusted execution plans can abruptly change and thereby adversely affect application performance.

In Oracle 11g we can now ‘defer’ the publication of statistics until they have been tried and tested and once we have confirmed that the execution plans are correct and optimal. Statistics remain in the pending state until they are published and the parameter optimizer_use_pending_statistics (default value FALSE) which when set at the session level will enables us to test the pending statistics independently of other database sessions.

Let us look at a test case using the SALES table in the SH schema.

If we see the query below, it appears that the data for the column CHANNEL_ID is skewed where majority of the rows have the value 3 and a very small majority have the value 9.

SQL> select channel_id,count(*) from sales
2 group by channel_id order by 2;

CHANNEL_ID COUNT(*)
———- ———-
9 2074
4 118416
2 258025
3 540328

But both the queries below are performing a full table scan of the SALES table when ideally it should be performing an index scan when the value 9 is used in the WHERE clause considering it accounts for a very small proportion of the rows in the SALES table.

SELECT
S.cust_id
,S.prod_id
,SUM(S.amount_sold)
FROM sh.sales S
WHERE
channel_id=9
GROUP BY S.cust_id, S.prod_id
ORDER BY S.cust_id, S.prod_id
/

SELECT
S.cust_id
,S.prod_id
,SUM(S.amount_sold)
FROM sh.sales S
WHERE
channel_id=3
GROUP BY S.cust_id, S.prod_id
ORDER BY S.cust_id, S.prod_id
/

As earlier mentioned, statistics for a table are published immediately by default, so we use the DBMS_STATS package to change this default behaviour.

SQL> exec dbms_stats.set_table_prefs('SH','SALES','PUBLISH','FALSE');

PL/SQL procedure successfully completed.

SQL> select dbms_stats.get_prefs('PUBLISH', 'SH', 'SALES' ) FROM DUAL;

DBMS_STATS.GET_PREFS('PUBLISH','SH','SALES')
----------------------------------------------------------------------------------------------
FALSE

Since we observed that the data in the table is skewed, we will gather histograms which may help the optimizer make more informed decisions when generating an execution plan.

Note that since we have disabled the publishing of statistics for the table SALES, even though we have analyzed the table, the LAST_ANALYZED column shows that the table has not been recently analyzed.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('SH','SALES',METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO');

PL/SQL procedure successfully completed.

SQL> SELECT LAST_ANALYZED FROM DBA_TABLES WHERE TABLE_NAME='SALES';

LAST_ANAL
---------
18-AUG-2009

However, we we query the DBA_TAB_PENDING_STATS view, it shows that the SALES table which is partitioned has been analyzed today.

SQL> SELECT TABLE_NAME,PARTITION_NAME ,LAST_ANALYZED  FROM DBA_TAB_PENDING_STATS;

TABLE_NAME                     PARTITION_NAME                 LAST_ANAL
------------------------------ ------------------------------ ---------
SALES                                                         17-SEP-09
SALES                          SALES_Q3_1998                  17-SEP-09
SALES                          SALES_Q3_1999                  17-SEP-09
SALES                          SALES_Q3_2000                  17-SEP-09
SALES                          SALES_Q3_2001                  17-SEP-09
SALES                          SALES_Q3_2002                  17-SEP-09
SALES                          SALES_Q3_2003                  17-SEP-09
SALES                          SALES_Q4_1998                  17-SEP-09
SALES                          SALES_Q4_1999                  17-SEP-09
SALES                          SALES_Q4_2000                  17-SEP-09
SALES                          SALES_Q4_2001                  17-SEP-09
SALES                          SALES_Q4_2002                  17-SEP-09
SALES                          SALES_Q4_2003                  17-SEP-09
SALES                          SALES_1995                     17-SEP-09
SALES                          SALES_1996                     17-SEP-09
SALES                          SALES_H1_1997                  17-SEP-09
SALES                          SALES_H2_1997                  17-SEP-09
SALES                          SALES_Q1_1998                  17-SEP-09
SALES                          SALES_Q1_1999                  17-SEP-09
SALES                          SALES_Q1_2000                  17-SEP-09
SALES                          SALES_Q1_2001                  17-SEP-09
SALES                          SALES_Q1_2002                  17-SEP-09
SALES                          SALES_Q1_2003                  17-SEP-09
SALES                          SALES_Q2_1998                  17-SEP-09
SALES                          SALES_Q2_1999                  17-SEP-09
SALES                          SALES_Q2_2000                  17-SEP-09
SALES                          SALES_Q2_2001                  17-SEP-09
SALES                          SALES_Q2_2002                  17-SEP-09
SALES                          SALES_Q2_2003                  17-SEP-09

We would like to see if the query execution plan has changed or improved after the recent gathering of statistics. We can use the parameter optimizer_use_pending_statistics for this by setting the value to TRUE (default is FALSE)

SQL> alter session set optimizer_use_pending_statistics=TRUE;

Session altered.

We now find that the optimizer is performing an index scan when the value 9 is used as a predicate value for CHANNEL_ID while it performs a full table scan when the value 3 is used which is a much more optimal plan than the original one before gathering fresh statistics.

explain plan
for
SELECT
     S.cust_id
    ,S.prod_id
    ,SUM(S.amount_sold)
  FROM sh.sales S
 WHERE
channel_id=3
 GROUP BY S.cust_id, S.prod_id
 ORDER BY S.cust_id, S.prod_id
/  2    3    4    5    6    7    8    9   10   11   12

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
Plan hash value: 3701591983

----------------------------------------------------------------------------------------------
| Id  | Operation            | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |       |  4992 | 79872 |  2649   (4)| 00:00:32 |       |       |
|   1 |  SORT GROUP BY       |       |  4992 | 79872 |  2649   (4)| 00:00:32 |       |       |
|   2 |   PARTITION RANGE ALL|       |   541K|  8461K|  2601   (2)| 00:00:32 |     1 |    28 |
|*  3 |    TABLE ACCESS FULL | SALES |   541K|  8461K|  2601   (2)| 00:00:32 |     1 |    28 |
----------------------------------------------------------------------------------------------

SQL> explain plan
for
SELECT
     S.cust_id
    ,S.prod_id
    ,SUM(S.amount_sold)
  FROM sh.sales S
 WHERE
channel_id=9
 GROUP BY S.cust_id, S.prod_id
 ORDER BY S.cust_id, S.prod_id
/
  2    3    4    5    6    7    8    9   10   11   12
Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------
Plan hash value: 3360497850

--------------------------------------------------------------------------------------------------------
-

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

-------------------------------------------------------------------------------------------------------
-

|   0 | SELECT STATEMENT                    |                   |   913 | 14608 |   315   (1)| 00:00:04
|

|   1 |  SORT GROUP BY                      |                   |   913 | 14608 |   315   (1)| 00:00:04
|

|   2 |   PARTITION RANGE ALL               |                   |  1423 | 22768 |   314   (0)| 00:00:04
|

|   3 |    TABLE ACCESS BY LOCAL INDEX ROWID| SALES             |  1423 | 22768 |   314   (0)| 00:00:04
|

|   4 |     BITMAP CONVERSION TO ROWIDS     |                   |       |       |            |
|

|*  5 |      BITMAP INDEX SINGLE VALUE      | SALES_CHANNEL_BIX |       |       |            |
|

--------------------------------------------------------------------------------------------------------

Since we have now confirmed that the plan is acceptable and the new statistics can be used by the optimizer, we PUBLISH the statistics now for the SALES table. Note that the LAST_ANALYZED column is also updated and the DBA_TAB_PENDING_STATS shows that there are no more pending statistics which need to be published.

SQL> EXEC DBMS_STATS.PUBLISH_PENDING_STATS ('SH','SALES');

PL/SQL procedure successfully completed.

SQL> SELECT LAST_ANALYZED FROM DBA_TABLES WHERE TABLE_NAME='SALES';

LAST_ANAL
---------
17-SEP-09

SELECT COUNT(*) FROM DBA_TAB_PENDING_STATS;

  COUNT(*)
----------
         0

SQL>  alter session set optimizer_use_pending_statistics=FALSE;

Session altered.

11g Interval Partitioning

In versions prior to Oracle 11g, we were very likely to have faced the error shown below especially if we used range partitioning and the partition column was a date field.

We needed to ensure that we precreated all the partitions before hand based on the expected values of data that would be inserted (or updated) in a table.

ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition

While we could use the MAXVALUE clause to create a ‘catch-all’ partition, this would not help us when we would like to perform any partition maintenance operations based on a date range or if wanted to use the partitioning feature to perform some kind of data archiving at the partition level.

11g Interval Partitioning

In Oracle 11g, the creation of partitions (for range) is automated and partitions are created as and when needed and takes the task of managing the creation of new partitions from the DBA. All that is required is to define the interval criteria and create the first partition. Subsequent partitions are created automatically based on the interval criteria.

create table mypart
   (ename varchar2(20), doj date)
   partition by range (doj)
   INTERVAL (NUMTOYMINTERVAL(1,'YEAR'))
   STORE IN (tbs1,tbs2)
  (partition p_2009 values less than (to_date('01-JAN-2010','DD-MON-YYYY'))
  )
;

Note the NUMTOYMINTERVAL is an SQL Funtion used to convert a number to an INTERVAL YEAR TO MONTH literal. The accepted values are ‘YEAR’ and ‘MONTH’.

The STORE IN clause will create in the partitions in a round robin manner in tablespaces tbs1 and tbs2 as we will see below.

Let us now insert some values into the table.

SQL> insert into mypart
  2   values
  3   ('Tom','21-SEP-2009');

1 row created.

SQL> insert into mypart
  2  values
  3  ('Joe','02-JAN-2010');

1 row created.

What has happened after the second insert? – a new partition ‘SYS_P42′ has been created for the year 2010 with a high value of ‘01-JAN-2011′.

The first partition that we had precreated has been created in the default tablespace USERS since no tablespace name has been prescribed and the new partition has been created in the tablespace TBS2. The next partition that comes along will be created in tablespace TBS1 and so on.

SQL> select partition_name,high_value from user_tab_partitions
  2  where table_name='MYPART';

PARTITION_NAME                 HIGH_VALUE
------------------------------ --------------------------------------------------------------------------------
P_2009                         TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SYS_P42                        TO_DATE(' 2011-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

SQL> select partition_name,tablespace_name from user_tab_partitions
  2  where table_name='MYPART';

PARTITION_NAME                 TABLESPACE_NAME
------------------------------ ------------------------------
P_2009                         USERS
SYS_P43                        TBS2

We can use the ALTER TABLE SET INTERVAL command to convert a range partitioned table to an interval partitioned table as shown below.

SQL> create table mypart2
   (ename varchar2(20), doj date)
   partition by range (doj)
  (partition p_2009 values less than (to_date('01-JAN-2010','DD-MON-YYYY'))
  )
;    2    3    4    5    6

Table created.

SQL> alter table mypart2
  2  SET INTERVAL (NUMTOYMINTERVAL(1,'YEAR');

Table altered.

We need to keep the following points in mind when using Interval Partitioning:

The partitioning column can be only one and it must be of type NUMBER or DATE

We cannot use the MAXVALUE clause

We cannot use this with Index Organised Tables

11g Real Application Testing – Part 2

Analyze the impact of change on SQL statements using the SQL Performance Analyzer

In the earlier post, we had discussed the Database Capture and Replay component of 11g Real Application Testing – (http://gavinsoorma.wordpress.com/2009/09/11/11g-real-application-testing-making-changes-with-confidence/)

We can also analyse the impact of upgrading from 10.2.0.4 to 11.1.0.6 at the SQL statement level by using the other component of 11g Real Application Testing which is called SQL Performance Analyzer or SPA.

We will use the following two SQL statements to create a SQL Tuning Set (STS) in the 10.2.0.4 environment. This STS is then transported to the 11g target environment and then we will use the GUI Database Control to replay the statements in the STS and generate a Performance Analysis report which will help us identify if any statements have improved or digressed in the changed environment.

SELECT /*+TEST_SPA*/ p.prod_name, s.time_id, t.week_ending_day, SUM(s.amount_sold)
FROM sales s, products p, times t
WHERE s.time_id=t.time_id
AND s.prod_id = p.prod_id
GROUP BY p.prod_name, s.time_id, t.week_ending_day;

SELECT /*+TEST_SPA*/ p.prod_category, t.week_ending_day, s.cust_id, SUM(s.amount_sold)
FROM sales s, products p, times t
WHERE s.time_id = t.time_id
AND s.prod_id = p.prod_id
AND p.prod_category = ‘Peripherals and Accessories’
GROUP BY p.prod_category, t.week_ending_day, s.cust_id;

We are using a ‘tag’ TEST_SPA to help us easily identify these SQL statements in the shared pool.

Source 10.2.0.4 environment – Create and Export SQL Tuning Set

SQL> exec dbms_sqltune.drop_sqlset(‘SH_10204′);

PL/SQL procedure successfully completed.

SQL> exec dbms_sqltune.create_sqlset(‘SH_10204′,’Demo Workload to test SPA’);

PL/SQL procedure successfully completed.

SQL> DECLARE
2 stscur dbms_sqltune.sqlset_cursor;
3 BEGIN
4 OPEN stscur FOR
5 SELECT VALUE(P)
6 FROM TABLE(dbms_sqltune.select_cursor_cache(
7 ’sql_text like ”SELECT /*+TEST_SPA%”’,
8 null, null, null, null, null, null, ‘ALL’)) P;
9 — populate the sqlset
10 dbms_sqltune.load_sqlset(:sts_name, stscur);
11 end;
12 /

PL/SQL procedure successfully completed.

SQL> select sql_id,plan_hash_value, substr(sql_text,1,40) text, executions
2 from dba_sqlset_statements
3 where sqlset_name =’SH_10204′
4 order by sql_id, plan_hash_value;

SQL_ID PLAN_HASH_VALUE TEXT EXECUTIONS
————- ————— ————————————————– ———-
5930m31ryy4b1 3901800458 SELECT /*+TEST_SPA*/ p.prod_category, t. 1
5tbr81fmrnyc0 1679814994 SELECT /*+TEST_SPA*/ p.prod_name, s.time 1

In order to transport the SQL tuning set that we have just created, we need to create a staging table to hold the contents of the STS and then we will use the export utility to generate a dump file which will then be copied to the target 11g environment.

SQL> execute DBMS_SQLTUNE.CREATE_STGTAB_SQLSET(table_name =>’SH_STS’);

PL/SQL procedure successfully completed.

SQL> execute DBMS_SQLTUNE.PACK_STGTAB_SQLSET(sqlset_name => ‘SH_10204′,staging_table_name => ‘SH_STS’);

PL/SQL procedure successfully completed.

SQL> select count (*) from sh_sts;

COUNT(*)
———-
2

Read More …..

11g Real Application Testing – making changes with confidence

CASE STUDY:

Using 11g Real Application Testing to analyse the impact of making the following changes:

1) Upgrade the database from 10g to 11g
2) Convert from single instance database to a two node RAC database
3) Convert from file system based storage to 11g ASM

Overview of Real Application Testing

One of the best new features in Oracle 11g is the Real Application Testing option which enables us to test the result of a change without actually implementing the same in a production environment. Changes such as database version upgrades, optimizer changes, hardware and storage changes all carry with them an element of risk and uncertainty. Using this feature much of that uncertainty and guesswork can be avoided.

Real Application Testing comprises of two components:

a) Database Replay
b) SQL Performance Analyzer (SPA)

SPA enables us to test the impact of a change at the level of a particular SQL statement to see if any improvements or regressions occurred in the execution of the SQL statement because of the change.

Database Replay enables us to capture workload real time in a production environment and then replay the same in a clone or copy of the changed production environment with the exact timings, concurrency and transaction characteristics found in the original workload. The AWR comparison and Database Replay reports can easily help us pinpoint any potential problems and errors as well any divergence in performance.

This feature will afford businesses increased confidence and surety when faced with a system change as well as significantly reducing the costs as well as resources associated with the testing phase.

Database Capture is now supported in versions 9.2.0.8 as well as 10.2.0.3 and 10.2.0.4 enabling us to test the impact of upgrades from both 9i as well as 10g to 11g. Note, however, that replay can only be performed in a Oracle 11g database.

At a very high level, the database capture and replay will involve the following steps:

1) Prepare the source database for capture
2) Capture the workload
3) Copy the workload capture files to the target machine
4) Prepare the target database for replay and process captured workload files
5) Start the workload replay clients
6) Once the workload replay has finished, examine the generated reports and make the analysis

Read More ….

Oracle 11g Advanced Compression

Although table compression was introduced in Oracle 9i and 10g, it was aimed more at bulk load operations for data warehouse environments. The overhead associated with compression and uncompression made it unsuitable for OLTP type shops.

In Oracle 11g, Advanced Compression (which is an additional licensed feature) includes OLTP compression capabilities as well as compression of unstructured data like images and text with Secure Files and also direct compression of the export dump file generated by Data Pump which is also directly read without any uncompress while doing an import.

The compression feature has been greatly enhanced so as to remove any overhead associated with dealing with compressed data while performing any DML activities.The COMPRESS FOR ALL OPERATIONS keyword needs to be used for enabling OLTP data compression.

While Oracle does claim compression ratios of 3:1, we were able to see clearly 2:1 ratios in terms of storage reductions and no real performance overhead while performing deletes and inserts on compressed data as shown in the example below.

So this feature could provide us both Storage as well as Performance gains due to the reduced number of blocks which will need to be read while performing I/O.

UNCOMPRESSED DATA

SQL> create table myobjects
2 as select * from all_objects;

Table created.

SQL> select sum(bytes)/1048576 from user_segments where segment_name=’MYOBJECTS’;

SUM(BYTES)/1048576
——————
21

SQL> insert into myobjects
2 select * from all_objects;

56261 rows created.

Elapsed: 00:00:09.39

SQL> select count(*) from myobjects where object_type=’TABLE’;

COUNT(*)
———-
444

Elapsed: 00:00:00.66

SQL> delete myobjects;

168780 rows deleted.

Elapsed: 00:00:12.22

COMPRESSED DATA

SQL> create table myobjects_compress
2 compress for all operations
3 as select * from all_objects;

SQL> select sum(bytes)/1048576 from user_segments where segment_name=’MYOBJECTS_COMPRESS’;

SUM(BYTES)/1048576
——————
9

SQL> insert into myobjects_compress
2 select * from all_objects;

56261 rows created.

Elapsed: 00:00:09.08

SQL> select count(*) from myobjects_compress where object_type=’TABLE’;

COUNT(*)
———-
444

Elapsed: 00:00:00.21

SQL> delete myobjects_compress;

168783 rows deleted.

Elapsed: 00:00:10.96

Compression of Data Pump Exports

While doing an export, we can use the parameter COMPRESSION=ALL (other options are DATA_ONLY or METADATA) and we see that the size of the dumpfile has reduced very significantly while using this compression feature.

The uncompressed dumpfile was 113 MB while the compressed dumpfile was just 13 MB. This feature can be very useful when we need to take a large export on disk but are limited by disk space. The earlier methods required us to use a pipe while performing an export, but we had to then uncompress the file while performing the import as well which is a very costly operation in terms of time and resources.

$ expdp directory=data_file_dir dumpfile=uncompress.dmp schemas=SH compression=ALL

middba1:/u01/oracle/product/11.1.0/db/demo/schema/sales_history> ls -l *.dmp
-rw-rw—- 1 oracle dba 113610752 Sep 3 12:25 uncompress.dmp
-rw-rw—- 1 oracle dba 13426688 Sep 3 12:21 compress.dmp

DBA Monitoring Menu

I have created a simple but very useful Unix shell script driven menu to enable easy monitoring of any number of Oracle databases from a single central location. This covers most of the normal DBA day to day standard monitoring requirements as well as a good interface for aiding in performance tuning.

Set up is pretty simple requiring a single user called MONITOR in each database with SELECT privilege on the data dictionary tables.

Drop me an email at gavin.soorma@bankwest.com.au so that I can send you the zip file along with the installation instructions.

A few screen shots of the various menu options are shown below

       #######################################################
                        [DBA Menu - PRDU010]

                        Connected to Database- cpsprd
        #######################################################

        (1) Database Overview
        (2) Database Storage
        (3) Database Objects
        (4) Database Security
        (5) Database Monitor
        (6) Database Performance

        Enter your choice (q to quit):

        ########################################################

        #######################################################
                        Database Monitoring Menu - cpsprd
        #######################################################

        (1)  User Sessions
        (2)  Redo Log File Switches
        (3)  Current SQL (by Unix PID)
        (4)  Current SQL (by SID)
        (5)  Current SQL (by Oracle Username)
        (6)  Rollback Segment Usage
        (7)  Locked Sessions
        (8)  Locked Objects
        (9)  Datafile I/O
        (10) DBMS Jobs Status
        (11) View Long Operations
        (12) Active Transactions
        (13) Idle Sessions
        (14) Flashback Logging
        (15) Sessions With High I/O
        (16) Data Guard Log Shipping
        (17) Redo Generation By Day

        Enter your choice (q to quit):

        #######################################################

             #######################################################
                        Database Performance Menu - cpsprd
        	  #######################################################

        (1)  Waits - Instance Level (by time waited)
        (2)  Waits - By Class
        (3)  Waits - Waiting Session's SQL
        (4)  Waits - What are users currently waiting on?
        (5)  Waits - Datafile Level
        (6)  Waits - Block & Object Level
        (7)  Locked Sessions
        (8)  Large Objects In Shared Pool
        (9)  Hit Ratios - Quick Check
        (10) Top 5 Problem Queries (Disk Reads)
        (11) Top 5 Problem Queries (Buffer Gets)
        (12) Top 5 Problem Queries (Disk Reads/Executions)
        (13) Top 5 Queries (Executions)
        (14) Top 5 Latch Waits (by sleeps)
        (15) Top 5 Latch Waits (by misses)
        (16) Current Waits On Latches
        (17) Enqueue Waits - By Session
        (18) Enqueue Waits - By Database
        (19) Enqueue Waits - By Object
        (20) Key CBO init.ora Parameters
        (21) Tables ANALYZE Status
        (22) Indexes ANALYZE Status
        (23) Top Segments with most I/O

        Enter your choice (q to quit):

        #######################################################

11g Snapshot Standby for real time testing

One of the very good new features in Oracle 11g is the Snapshot Standby database where we can basically convert the physical standby database into a read-write real time clone of the production database and we can then temporarily use this environment for carrying out any kind of development testing, QA type work or to test the impact of a proposed production change on an application.

Basically, the Snapshot Standby database in turn uses the Flashback Database technology to create a guaranteed restore point to which the database can be later flashed back to.

So we need to keep in mind that whatever limitations are present in the Flashback database technology, the same will also be inherent in the Snapshot database fetaure.

Let us illustrate this feature by using an example where we would like to test the impact of the change of some important optimizer related parameters on a key application query using the actual production data.

In production, the SQL query below is shown to be peforming a full table/partition scan and using hash joins. We would like to see if a nested loop join and index usage will prove beneficial to this SQL query that is used frequently by the application.

explain plan for
SELECT c.cust_last_name, sum(s.amount_sold) AS dollars,sum(s.quantity_sold) as quantity
FROM sales s , customers c, products p
WHERE c.cust_id = s.cust_id
AND s.prod_id = p.prod_id
AND c.cust_state_province IN ('Dublin','Galway')GROUP BY c.cust_last_name
/

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------
Plan hash value: 1248656060

------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |             |   519 | 23355 |   935   (6)| 00:00:12 |       |       |
|   1 |  HASH GROUP BY         |             |   519 | 23355 |   935   (6)| 00:00:12 |       |       |
|*  2 |   HASH JOIN            |             | 99644 |  4378K|   927   (5)| 00:00:12 |       |       |
|   3 |    INDEX FULL SCAN     | PRODUCTS_PK |    72 |   288 |     1   (0)| 00:00:01 |       |       |
|*  4 |    HASH JOIN           |             | 99644 |  3989K|   924   (5)| 00:00:12 |       |       |
|*  5 |     TABLE ACCESS FULL  | CUSTOMERS   |   766 | 18384 |   408   (1)| 00:00:05 |       |       |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------
|   6 |     PARTITION RANGE ALL|             |   918K|    14M|   507   (6)| 00:00:07 |     1 |    28 |
|   7 |      TABLE ACCESS FULL | SALES       |   918K|    14M|   507   (6)| 00:00:07 |     1 |    28 |
------------------------------------------------------------------------------------------------------

We will now convert the physical standby database to a Snapshot Standby database and note that after the conversion takes place, internally a guaranteed restore point has been created and the database has been opened in read-write mode. Also note the DATABASE_ROLE column.

SQL>  ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;

Database altered.

SQL> shutdown immediate;
ORA-01507: database not mounted

ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area  521936896 bytes
Fixed Size                  2139784 bytes
Variable Size             415238520 bytes
Database Buffers           96468992 bytes
Redo Buffers                8089600 bytes
Database mounted.
Database opened.

SQL> select NAME,GUARANTEE_FLASHBACK_DATABASE from  v$restore_point;

NAME                                                         GUA
------------------------------------------------------------ ---
SNAPSHOT_STANDBY_REQUIRED_09/02/2009 10:12:21                YES

SQL> select open_mode,database_role from v$database;

OPEN_MODE  DATABASE_ROLE
---------- ----------------
READ WRITE SNAPSHOT STANDBY

We will now make some changes to some optimizer related parameters:

SQL> alter system set optimizer_index_cost_adj=20;

System altered.

SQL> alter system set optimizer_index_caching=10;

System altered.

SQL> alter system set optimizer_mode=FIRST_ROWS;

System altered.

We run the same query again and we can see that the plan has changed and the optimizer is now using the index scan with nested loop joins.

--------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                      |   519 | 23355 |   588  (11)| 00:00:08 |
|   1 |  HASH GROUP BY                        |                      |   519 | 23355 |   588  (11)| 00:00:08 |
|   2 |   NESTED LOOPS                        |                      | 99644 |  4378K|   580  (10)| 00:00:07 |
|*  3 |    HASH JOIN                          |                      | 99644 |  3989K|   577  (10)| 00:00:07 |
|*  4 |     TABLE ACCESS BY INDEX ROWID       | CUSTOMERS            |   766 | 18384 |   241   (2)| 00:00:03 |
|   5 |      BITMAP CONVERSION TO ROWIDS      |                      |       |       |            |          |
|   6 |       BITMAP INDEX FULL SCAN          | CUSTOMERS_GENDER_BIX |       |       |            |          |
|   7 |     PARTITION RANGE ALL               |                      |   918K|    14M|   326  (12)| 00:00:04 |
|   8 |      TABLE ACCESS BY LOCAL INDEX ROWID| SALES                |   918K|    14M|   326  (12)| 00:00:04 |
|   9 |       BITMAP CONVERSION TO ROWIDS     |                      |       |       |            |          |
|  10 |        BITMAP INDEX FULL SCAN         | SALES_PROMO_BIX      |       |       |            |          |
|* 11 |    INDEX UNIQUE SCAN                  | PRODUCTS_PK          |     1 |     4 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------

While the original physical standby database has been now converted to snapshot database, some changes are happening on the Primary database and those changes are shipped to the standby site but not yet applied. They will accumulate on the standby site and will be applied after the snapshot standby database is converted back to a physical standby database.

SQL> create table mytest as select * from sales;

Table created.

SQL> update mytest set prod_id=1;

918843 rows updated.

On the standby site, we will now convert the shapshot standby database to the original mode of physical standby database in Active Data Guard mode.

Note that after the physical standby database is opened, changes that were made on the Primary database while it was open in shapshot standby mode are now applied as well.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  521936896 bytes
Fixed Size                  2139784 bytes
Variable Size             415238520 bytes
Database Buffers           96468992 bytes
Redo Buffers                8089600 bytes
Database mounted.
SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

Database altered.

SQL> shutdown immediate;
ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.

Total System Global Area  521936896 bytes
Fixed Size                  2139784 bytes
Variable Size             415238520 bytes
Database Buffers           96468992 bytes
Redo Buffers                8089600 bytes
Database mounted.
SQL>  recover managed standby database disconnect;
Media recovery complete.
SQL> recover managed standby database cancel;
Media recovery complete.
SQL> alter database open;

Database altered.

Elapsed: 00:01:06.29
SQL> recover managed standby database using current logfile disconnect;
Media recovery complete.

SQL> select count(*) from sh.mytest;

  COUNT(*)
----------
    918843

SQL> select distinct prod_id from sh.mytest;

   PROD_ID
----------
         1