11g Snapshot Standby for real time testing
- Posted by Gavin Soorma
- On September 2, 2009
- 2 Comments
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
2 Comments