Umar DBA

Wednesday, November 11, 2009

Implementing Data Guard?

Planning a Disaster Recovery (DR) strategy for your Oracle database may appear daunting but the good news is that Oracle Data Guard is a straightforward application of Oracle technologies, used to maintain one or more synchronized copies of your production database. This lets you leverage existing DBA skills already in-house, and gives your DBA team complete control over your standby databases. You will simultaneously increase data protection and high availability, while reducing cost and business risk compared to using traditional DR solutions implemented in-house or outsourced to 3rd party DR service providers [1], [2]. The overview provided below covers the main points you need to consider to get your Data Guard configuration up and running. For detailed information please refer to the complete Data Guard documentation [3].

Service Level Agreements: Determine your Recovery Point Objective (RPO). RPO is the maximum amount of data that can be lost and still satisfy Service Level Requirements (SLAs). Determine your Recovery Time Objective (RTO). RTO is the maximum amount of time that the database can be unavailable and still satisfy SLAs [4].

Workload: Data Guard ships redo data generated by the production database to the standby destination and applies redo data to the standby database. This makes redo volume one of the key considerations when implementing a Data Guard configuration. Determine the peak redo generation rate of the primary database; measured in Megabytes/second. Understand both the frequency and duration of these peak periods. Also determine the average redo generation. Note: if your Data Guard configuration includes a RAC primary database, the redo rate is the sum of the redo generated by all nodes

Network Requirements: The efficiency with which Data Guard can ship data to a remote DR site is dependent upon network bandwidth and latency. Determine the network bandwidth available to Data Guard for shipping redo data. Determine the round-trip network latency (RTT) between production and standby sites, measured in milliseconds. Make sure that these are adequate for Data Guard to ship redo data to the DR site at peak redo generation intervals on the production database. Data Guard best practices describe several database and OS network parameters that be tuned to enhance and optimize network transmission throughput [5].

Redo Apply or SQL Apply: Determine which type of Standby Database to implement. Redo Apply (physical standby database) is preferred by more conservative users where the emphasis is on scalability and/or where data types are not supported by SQL Apply (logical standby database). These preferences are rooted in the simplicity and efficiency of the media recovery mechanism used by Redo Apply. A physical standby database can also be used to offload the primary of doing online backups (RMAN can do online backups of a standby database while Redo Apply is on [6]), and it can be open read-only to enable reporting against a static copy of the production database.

SQL Apply (logical standby database) is preferred if the standby database must also serve as an up-to-date reporting database (read/write access is allowed while updates from the production database are being applied). By definition, SQL Apply requires more standby resources (both CPU and I/O) than Redo Apply, because it has to mine the incoming redo on the standby database, generate SQL, and apply this SQL to the standby database, and therefore is typically used for less demanding performance requirements.

Protection Mode: Determine if you will use Maximum Performance (asynchronous redo shipping, a lower level of data protection with least overhead on the production database), Maximum Availability (synchronous zero data loss protection, higher performance impact on the production database, but availability is NOT impacted should the production database be unable to communicate with the standby database), or Maximum Protection (the highest level of data protection possible that will, by definition, cause the production database to shut down if Data Guard can not confirm that redo data has been written to at least one standby location). The choice of protection mode is driven by the combination of RPO/RTO SLAs and the capacity of systems and network to ship and apply the required redo volumes.

Manual or Automatic Failover: All Data Guard configurations include role management services for the orderly transition of a standby database to the production role [7]. Starting with Oracle Database 10g Release 2, Data Guard configurations using Maximum Availability mode have the option of implementing unattended, automatic failover upon primary database failures [8].

Management Interface: Select one of three options:

Enterprise Manager Grid Control provides a GUI interface to automate the creation and simplify the centralized management of a Data Guard configuration.
Alternatively, the Data Guard Broker [9] command line interface (DGMGRL) can be used if Enterprise Manager is not available. The Broker enables single commands that perform the equivalent work of multiple SQL*Plus statements, simplifying the management of a Data Guard configuration. Broker creation of a Data Guard Configuration requires only 5 parameters to be configured (three on the production database and two on the standby database). The Broker requires no separate install.
Finally, for SQL*Plus "jockeys" - all Data Guard configuration and management can also be implemented using the SQL*Plus command line interface.

Tuning Redo Apply: Redo Apply uses Oracle Media Recovery to apply change records to the standby database. Default settings will address most requirements. If tuning is required refer to Oracle Database 10g Best Practices [10].

Tuning SQL Apply: The SQL Apply process mines incoming redo at the standby database, converts it to SQL, and applies SQL to the standby database. The SQL Apply process requires more CPU and I/O resources than does Redo Apply. Plan accordingly by referencing SQL Apply Best Practices [11] and applicable MetaLink notes [12].

Maximum Availability Architecture: The Oracle Maximum Availability Architecture (MAA) provides a best practices blueprint for deploying the full range of Oracle HA technologies. If, in addition to Data Guard you are using RAC, RMAN, Flash Recovery Area, Flashback Database, or ASM, please refer to the MAA home page on OTN for more information [13].

References:

Oracle Data Guard and Remote Mirroring Solutions - http://www.oracle.com/technology/deploy/availability/htdocs/DataGuardRemoteMirroring.html
Data Guard, It's Not Just About Disasters - http://www.oracle.com/technology/deploy/availability/htdocs/dataguardprotection.html
Oracle Data Guard Concepts and Administration, 10g Release 2 - http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14239/toc.htm
Oracle Database High Availability Architecture and Best Practices, 10g Release 1 - http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10726/toc.htm
Using Recovery Manager (RMAN) with Oracle Data Guard in Oracle Database 10g - http://www.oracle.com/technology/deploy/availability/pdf/RMAN_DataGuard_10g_wp.pdf
Oracle Data Guard 10g Release 2, Switchover and Failover Best Practices - http://www.oracle.com/technology/deploy/availability/pdf/MAA_WP_10gR2_SwitchoverFailoverBestPractices.pdf
Fast-Start Failover Best Practices: Oracle Data Guard 10g Release 2 - http://www.oracle.com/technology/deploy/availability/pdf/MAA_WP_10gR2_FastStartFailoverBestPractices.pdf
Oracle Data Guard Broker , 10g Release 2 - http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14230/toc.htm
Data Guard Redo Apply and Media Recovery Best Practices - http://www.oracle.com/technology/deploy/availability/pdf/MAA_WP_10gRecoveryBestPractices.pdf
SQL Apply Best Practices, Oracle Database 10g Release 2 - http://www.oracle.com/technology/deploy/availability/pdf/MAA_WP_10gR2_SQLApplyBestPractices.pdf
Oracle MetaLink Note 274170.1: "Logical Standby Master Index Page" - https://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=274170.1
Maximum Availability Architecture (MAA) - http://www.oracle.com/technology/deploy/availability/htdocs/maa.htm
Implementing Data Guard?, by Joseph Meeks, Server Technologies, Oracle Corporation

Literals, Substitution Variables and Bind Variables

If you've read anything about writing OLTP applications that talk to Oracle databases, you will know that bind variables are very important.

Each time an SQL statement is sent to the database, an exact text match is performed to see if the statement is already present in the shared pool. If no matching statement is found a hard parse is performed, which is a resource intensive process. If the statement is found in the shared pool this step is not necessary and a soft parse is performed. Concatenating variable values into an SQL statement makes the statement unique, forcing a hard parse. By contrast, using bind variables allow reuse of statements as the text of the statement remains the same. Only the value of the bind variable changes.

Why do we care?

Holding many similar SQL statements in the shared pool is a waste of memory.
Filling the shared pool with similar statements will cause well written statements to get paged out of the shared pool quickly, forcing them to be reparsed also.
Parsing SQL statements is a resource intensive process. Reducing the number of hard parses results in reduced CPU usage.

The following example shows the affect of using literals on the shared pool. First the shared pool is cleared of previously parsed statements. Then two queries are issued, both specifying literal values in the WHERE clause. Finally the contents of the shared pool is displayed by querying the V$SQL view.

SQL> ALTER SYSTEM FLUSH SHARED_POOL;
System altered.


SQL> SELECT * FROM dual WHERE dummy = 'LITERAL1';
no rows selected

SQL> SELECT * FROM dual WHERE dummy = 'LITERAL2';
no rows selected


SQL> COLUMN sql_text FORMAT A60

SQL> SELECT sql_text,
2         executions
3  FROM   v$sql
4  WHERE  INSTR(sql_text, 'SELECT * FROM dual WHERE dummy') > 0
5  AND    INSTR(sql_text, 'sql_text') = 0
6  ORDER BY sql_text;


SQL_TEXT                                                     EXECUTIONS

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

SELECT * FROM dual WHERE dummy = 'LITERAL1'                           1
SELECT * FROM dual WHERE dummy = 'LITERAL2'                           1

2 rows selected.

SQL>

From this we can see that both queries were parsed separately.

Substitution variables are a feature of the SQL*Plus tool. They have nothing to do with the way SQL is processed by the database server. When a substitution variable is used in a statement, SQL*Plus requests an input value and rewrites the statement to include it. The rewritten statement is passed to the database. As a result, the database server knows nothing of the substitution variable. The following example illustrates this by repeating the previous test, this time using substitution variables.

SQL> ALTER SYSTEM FLUSH SHARED_POOL;
System altered.


SQL> SELECT * FROM dual WHERE dummy = '&dummy';

Enter value for dummy: SUBSTITUTION_VARIABLE1

old   1: SELECT * FROM dual WHERE dummy = '&dummy'

new   1: SELECT * FROM dual WHERE dummy = 'SUBSTITUTION_VARIABLE1'

no rows selected


SQL> SELECT * FROM dual WHERE dummy = '&dummy';

Enter value for dummy: SUBSTITUTION_VARIABLE2

old   1: SELECT * FROM dual WHERE dummy = '&dummy'

new   1: SELECT * FROM dual WHERE dummy = 'SUBSTITUTION_VARIABLE2'

no rows selected


SQL> COLUMN sql_text FORMAT A60

SQL> SELECT sql_text,
2         executions
3  FROM   v$sql
4  WHERE  INSTR(sql_text, 'SELECT * FROM dual WHERE dummy') > 0
5  AND    INSTR(sql_text, 'sql_text') = 0
6  ORDER BY sql_text;

SQL_TEXT                                                     EXECUTIONS
------------------------------------------------------------ ----------
SELECT * FROM dual WHERE dummy = 'SUBSTITUTION_VARIABLE1'             1
SELECT * FROM dual WHERE dummy = 'SUBSTITUTION_VARIABLE2'             1

2 rows selected.

SQL>

Once again, both statements were parsed separately. As far as the database server is concerned, literals and substitution variables are the same thing.

The following example illustrates the affect of bind variable usages on the shared pool. It follows the same format as the previous examples.

SQL> ALTER SYSTEM FLUSH SHARED_POOL;

System altered.

SQL> VARIABLE dummy VARCHAR2(30);

SQL> EXEC :dummy := 'BIND_VARIABLE1';

PL/SQL procedure successfully completed.

SQL> SELECT * FROM dual WHERE dummy = :dummy;

no rows selected

SQL> EXEC :dummy := 'BIND_VARIABLE2';

PL/SQL procedure successfully completed.

SQL> SELECT * FROM dual WHERE dummy = :dummy;

no rows selected

SQL> COLUMN sql_text FORMAT A60

SQL> SELECT sql_text,
2         executions
3  FROM   v$sql
4  WHERE  INSTR(sql_text, 'SELECT * FROM dual WHERE dummy') > 0
5  AND    INSTR(sql_text, 'sql_text') = 0
6  ORDER BY sql_text;

SQL_TEXT                                                     EXECUTIONS
----------------------------------------------------------- ----------
SELECT * FROM dual WHERE dummy = :dummy                               2

1 row selected.

SQL>

This clearly demonstrates that the same SQL statement was executed twice.

The following example measures the amount of CPU used by a session for hard and soft parses when using literals. The shared pool is flushed and a new session is started. Dynamic SQL is used to mimic an application sending 10 statements to the database server. Notice that the value of the loop index is concatinated into the string, rather than using a bind variable. The CPU usage is retrieved from the V$MYSTAT view by querying the "parse time cpu" statistic. This statistic represents the total CPU time used for parsing (hard and soft) in 10s of milliseconds. The statements present in the shared pool are also displayed.

SQL> CONN sys/password AS SYSDBA

Connected.

SQL> ALTER SYSTEM FLUSH SHARED_POOL;

System altered.

SQL> CONN sys/password AS SYSDBA

Connected.

SQL> DECLARE
2    l_dummy  dual.dummy%TYPE;
3  BEGIN
4    FOR i IN 1 .. 10 LOOP
5      BEGIN
6        EXECUTE IMMEDIATE 'SELECT dummy FROM dual WHERE dummy = ''' || TO_CHAR(i) || ''''
7        INTO l_dummy;
8      EXCEPTION
9        WHEN NO_DATA_FOUND THEN
10          NULL;
11      END;
12    END LOOP;
13  END;
14  /

PL/SQL procedure successfully completed.

SQL> SELECT sn.name, ms.value
2  FROM   v$mystat ms, v$statname sn
3  WHERE  ms.statistic# = sn.statistic#
4  AND    sn.name       = 'parse time cpu';


NAME                                                                  VALUE
---------------------------------------------------------------- ----------
parse time cpu                                                           63

1 row selected.


SQL> COLUMN sql_text FORMAT A60

SQL> SELECT sql_text,
2         executions
3  FROM   v$sql
4  WHERE  INSTR(sql_text, 'SELECT dummy FROM dual WHERE dummy') > 0
5  AND    INSTR(sql_text, 'sql_text') = 0
6  AND    INSTR(sql_text, 'DECLARE') = 0
7  ORDER BY sql_text;


SQL_TEXT                                                     EXECUTIONS
------------------------------------------------------------ ----------
SELECT dummy FROM dual WHERE dummy = '1'                              1
SELECT dummy FROM dual WHERE dummy = '10'                             1
SELECT dummy FROM dual WHERE dummy = '2'                              1
SELECT dummy FROM dual WHERE dummy = '3'                              1
SELECT dummy FROM dual WHERE dummy = '4'                              1
SELECT dummy FROM dual WHERE dummy = '5'                              1
SELECT dummy FROM dual WHERE dummy = '6'                              1
SELECT dummy FROM dual WHERE dummy = '7'                              1
SELECT dummy FROM dual WHERE dummy = '8'                              1
SELECT dummy FROM dual WHERE dummy = '9'                              1

10 rows selected.


SQL>

The results show that 630 milliseconds of CPU time were used on parsing during the session. In addition, the shared pool contains 10 similar statements using literals.

The following example is a repeat of the previous example, this time using bind variables. Notice that the USING clause is used to supply the loop index, rather than concatenating it into the string.

SQL> CONN sys/password AS SYSDBA

Connected.

SQL> ALTER SYSTEM FLUSH SHARED_POOL;

System altered.


SQL> CONN sys/password AS SYSDBA

Connected.

SQL> 

SQL> DECLARE
2    l_dummy  dual.dummy%TYPE;
3  BEGIN
4    FOR i IN 1 .. 10 LOOP
5      BEGIN
6        EXECUTE IMMEDIATE 'SELECT dummy FROM dual WHERE dummy = TO_CHAR(:dummy)'
7        INTO l_dummy USING i;
8      EXCEPTION
9        WHEN NO_DATA_FOUND THEN
10          NULL;
11      END;
12    END LOOP;
13  END;
14  /

PL/SQL procedure successfully completed.

SQL> 

SQL> SELECT sn.name, ms.value
2  FROM   v$mystat ms, v$statname sn
3  WHERE  ms.statistic# = sn.statistic#
4  AND    sn.name       = 'parse time cpu';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
parse time cpu                                                           40

1 row selected.



SQL> 

SQL> COLUMN sql_text FORMAT A60

SQL> SELECT sql_text,
2         executions
3  FROM   v$sql
4  WHERE  INSTR(sql_text, 'SELECT dummy FROM dual WHERE dummy') > 0
5  AND    INSTR(sql_text, 'sql_text') = 0
6  AND    INSTR(sql_text, 'DECLARE') = 0
7  ORDER BY sql_text;

SQL_TEXT                                                     EXECUTIONS
------------------------------------------------------------ ----------
SELECT dummy FROM dual WHERE dummy = TO_CHAR(:dummy)                 10

1 row selected.

SQL>

The results show that 400 milliseconds of CPU time were used on parsing during the session, less than two thirds the amount used in the previous example. As expected, there is only a single statement in the shared pool.

These simple examples clearly show how replacing literals with bind variables can save both memory and CPU, making OLTP applications faster and more scalable. If you are using third-party applications that don't use bind variables you may want to consider setting the CURSOR_SHARING parameter.

For more information see:

REFERENCES

http://www.oracle-base.com

Oracle Parse to Execute Ratio

All Oracle SQL statements must be parsed the first time that they execute, and parsing involves a syntax check, a semantic check (against the dictionary), the creation of a decision tree, and the generation of the lowest cost execution plan. Once the execution plan is created, it is stored in the library cache (part of the shared_pool_size) to facilitate re-execution. There are two types of parses:

Hard parse - A new SQL statement must be parsed from scratch. (See hard parse ratio, comparing hard parses to executes). If the database is parsing every statement that is executing, the parse to execute ratio will be close to 1% (high hard parses), often indicating non-reentrant SQL that does not use host variables (see cursor_sharing=force).
Soft parse - A reentrant SQL statement where the only unique feature are host variables. (See soft parse ratio, comparing soft parses to executes). The best-case scenario is a parse to execute ratio of 100% which would indicate an application with fully reentrant SQL that "parses SQL once and executes many times" (also see your setting for session_cached_cursors, as this effects the reentrancy of an SQL statement).

In a real database, some SQL statements will be fully reentrant (execute to parse = 100%), while others must be re-parsed for every execution (execute to parse = 1%). You can see this is the instance efficiency of any STATSPACK and AWR report:

Instance Efficiency Percentages (Target 100%)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Buffer Nowait %: 99.99 Redo NoWait %: 100.00

Buffer Hit %: 97.97 In-memory Sort %: 100.00

Library Hit %: 98.30 Soft Parse %: 97.05

Execute to Parse %: 0.91 Latch Hit %: 99.73

Parse CPU to Parse Elapsd %: 72.59 % Non-Parse CPU: 95.52

High parses suggests that your system has many incoming unique SQL statements, or that your SQL is not reentrant (i.e. literal values in the WHERE clause, not using bind variables), and consider setting cursor_sharing=force can cause dramatic performance improvements for systems with ad-hoc query tools such as Crystal Reports or Business Objects.

A hard parse is expensive because each incoming SQL statement must be re-loaded into the shared pool; with the associated overhead involved in shared pool RAM allocation and memory management. Once loaded, the SQL must then be completely re-checked for syntax & semantics and an executable generated. Excessive hard parsing can occur when your shared_pool_size is too small (and reentrant SQL is paged out), or when you have non-reusable SQL statements without host variables. See the cursor_sharing parameter for an easy way to make SQL reentrant and remember that you should always use host variables in you SQL so that they can be reentrant.

Note that the "soft parse ratio" and the "hard parse ratio" are totally independent metrics, and it's possible to see all combinations:

Soft Parse Ratio	Hard parse Ratio
low	low
high	high
low	high
high	low

You can see these ratio's in any STATSPACK or AWR report, in the load profile and instance efficiency sections. You can also paste-in your STATSPACK or AWR reports into www.statspackanalyzer.com for a detailed analysis:

Load Profile

~~~~~~~~~~~~ Per Second Per Transaction

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

Redo size: 26,032.63 2,148.01

Logical reads: 4,943.63 407.91

Block changes: 158.17 13.05

Physical reads: 14.52 1.20

Physical writes: 26.77 2.21

User calls: 86.37 7.13

Parses: 148.80 12.28

Hard parses: 9.81 0.81

Sorts: 134.58 11.10

Logons: 12.18 1.01

Executes: 149.96 12.37