Virag Sharma

Subscribe to Virag Sharma feed
Blog create to share my Oracle DBA experience with Oracle User community to make Oracle usage easy and enjoyable.Virag Sharmahttp://www.blogger.com/profile/03899931825744684882noreply@blogger.comBlogger46125
Updated: 2 hours 47 min ago

Oracle 11g Database Replay

Thu, 2007-12-27 01:32

If your database currently running on 10g R2 , and want upgrade database to 11g then you can take advantage of Database Replay , As per Datasheet given on OTN workload capture on 10.2.0.4 can run/replay on 11g.

So , it simply mean , before you going to upgrade from 10g R2 to 11g , you can take advantage of database Replay feature i.e. capture work load on Production 10g R2 database , then copy workload to test system , upgrade test system to 11g , run workload captured on production and check how your system performing. This make life easier , isn't it ?

Check following links

Categories: DBA Blogs

Oracle 11g New Features DBMS_ADDM for RAC

Thu, 2007-09-20 08:37
Virag Sharma virag123@gmail.com

In OCP Oracle Database 10g Exam Guidechapter 3 “Automatic Database Management” there is question


5. To retrieve the ADDM reports using SQL, what do you need to do?

A. Run the addmrpt.sql SQL script
B. Use the DBA_ADDM view
C. Use the DBA_ADVISOR view
D. Use the DBMS_ADDM package

Answer “A” is correct and D is wrong because there is no PL/SQL package named DBMS_ADDM.
But in 11g , it is not true , i.e. Package DBMS_ADDM is there in 11g. In case of RAC script
addmrpt.sql
run give report for single instance, not report of all instance in RAC. But using DBMS_ADDM , we can generate report for all instance of RAC.

Different mode of DBMS_ADDM

  1. Database Mode Instance Mode
  2. Partial Mode

Database Mode of DBMS_ADDM


In database mode DBMS_ADDM , analyze all instance in RAC

VAR tname VARCHAR2(30);
VAR start_snap_id number;
VAR end_snap_id number;

BEGIN

:tname := 'DB_MODE_TEST_RAG';
:start_snap_id := 100 ;
:end_snap_id := 200 ;
DBMS_ADDM.ANALYZE_DB(:tname, :start_snap_id, :end_snap_id);

END;

Instance Mode of DBMS_ADDM
In Instance mode DBMS_ADDM , analyze one particular instance

VAR tname VARCHAR2(30);
VAR start_snap_id number;
VAR end_snap_id number;
VAR INST_NUM number;
BEGIN
:tname := 'INST_MODE_TEST_RAG';
:start_snap_id := 100 ;
:end_snap_id := 200 ;
:INST_NUM := 2;
DBMS_ADDM.ANALYZE_INST(:tname,:start_snap_id,:end_snap_id, :INST_NUM );
END;

/

Partial mode of DBMS_ADDM

In partial mode DBMS_ADDM analyze subset of instances. for example we want to analyze instance 2 and 4 out of four node RAC

VAR tname VARCHAR2(30);
VAR start_snap_id number;
VAR end_snap_id number;

BEGIN

:tname := 'PART_MODE_TEST_RAG';
:start_snap_id :=100;
:end_snap_id := 200;
DBMS_ADDM.ANALYZE_PARTIAL(:tname,'2,4', :start_snap_id, :end_snap_id);

END;

/

Displaying an ADDM Report
  SET LONG 1000000 PAGESIZE 0;
   SELECT DBMS_ADDM.GET_REPORT(:tname) FROM DUAL;

or

SELECT DBMS_ADDM.GET_REPORT('DB_MODE_TEST_RAG') FROM DUAL;


Categories: DBA Blogs

Oracle 11g Database New Features: Data Guard Enhancements

Wed, 2007-09-12 07:59

Why We need Data Guard ?? for

  • Data protection
  • Data Availability

11g Increase ROI from standby systems and enhance manageability, New feature like Active Data Guard , Snapshot standby make better ROI.

Here is some data guard category and there enhancement

1) Data Protection
  • Advanced Compression
  • Lost-write protection
  • Fast-Start Failover
2) Increase ROI
  • Active Data Guard
  • Snapshot Standby
3) High Availability
  • Faster Redo Apply
  • Faster failover & switchover
  • Automatic Failover using ASYNC
4) Manageability
  • Mixed Windows/Linux

Active Data Guard

Oracle Active Data Guard 11g – a new Database Option
Enables read-only access to a physical standby database while Redo Apply is active
It is now possible to query a physical standby database while Redo Apply is active.This new capability increases your return on investment in Data Guard technology because a physical standby database can now be used to offload queries from the primary database in addition to providing data protection.
( We can do same with logical standby , what is diffrent here , well redo apply method is faster in physical stand by ie redo apply methode is diffrent in physical standby)


  • Stop log apply
Alter database recover managed standby database cancel;
  • Open database for read-only access
alter database open
  • Once database open start redo apply
alter database recover managed standby database
using current logfile disconnect from session;


Snapshot Standby


This enhancement is good example of ROI. This feature allow us to better utilization of standby. This feature Truly leverages DR hardware for multiple purposes.For example convert physical database to snapshot standby database do testing/ application patching etc. After testing convert back snapshot database back to physical standby.













  • -- Convert physical database to snapshot
ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;
  • --
  • -- Do testing on database
  • --
  • -- Convert snapshot Database to physical database

ALTER DATABASE CONVERT TO PHYSICAL STANDBY;


This Similar to storage snapshot , but it useage same space , not additional space is needed ( I know this possibly can achieve in 10g R2 too , using flashback feature , But for sure it is not easy as it is in 11g )


Redo compression

To transport redo data in compressed form to destination use COMPRESSION attribute.
By default compression is disabled.


  • Alter system set log_archive_dest_1 = 'SERVICE=standby1 compression=ENABLE';

  • select dest_name , compression from v$archive_dest;

    SQL>
    DEST_NAME COMPRES
    --------------------------------- -------
    LOG_ARCHIVE_DEST_1 DISABLE
    LOG_ARCHIVE_DEST_2 ENABLE
    LOG_ARCHIVE_DEST_3 DISABLE
    LOG_ARCHIVE_DEST_4 DISABLE
    LOG_ARCHIVE_DEST_5 DISABLE
    LOG_ARCHIVE_DEST_6 DISABLE
    LOG_ARCHIVE_DEST_7 DISABLE
    LOG_ARCHIVE_DEST_8 DISABLE
    LOG_ARCHIVE_DEST_9 DISABLE
    LOG_ARCHIVE_DEST_10 DISABLE

    10 rows selected.

Data Protection

New Initialization parameter db_lost_write_protect Introduced. This parameter Compare versions of blocks on the standby with that in the incoming redo stream
Version discrepancy implies lost write on either primary or standby database

db_lost_write_protect


Managibilty Enhancement

SYS user and password files no longer required for redo transmission authentication. Non-SYS user can be specified through the parameter, This user must have the SYSOPER privileges, Requires password for this user to be the same at primary and all standbys
Upon SYSDBA / SYSOPER changes, password file must be copied from the primary to all physical standby databases

redo_transport_user

Data Guard Support for Heterogeneous Primary and Standby Systems in Same Data Guard Configuration (- Since 11g Linux installer only available, so not able to test this feature)

Fast-Start Failover

Immediate automatic failover for user-configurable health conditions



DGMGRL> show FAST_START FAILOVER
DGMGRL> ENABLE FAST_START FAILOVER [CONDITION ];

Condition examples:
  • Datafile Offline
  • Corrupted Controlfile
  • Corrupted Dictionary
  • Inaccessible Logfile
  • Stuck Archiver
  • Any explicit ORA-xyz error


Application can also request for failover using package DBMS_DG

DBMS_DG.INITIATE_FS_FAILOVER



Categories: DBA Blogs

Some Good Links / Blogs / site to know more about New Oracle Database 11g features

Sat, 2007-09-08 21:56

I have rated 5 *(star) for some sites , which I like Most

( these links collected from google alert )

  1. Oracle Database Online Documentation 11g Release 1 (11.1) *****
  2. ORACLE-BASE - Articles on Oracle 11g new features *****
  3. Oracle Database 11g on OTN
  4. Oracle Database 11g: The Top Features for DBAs and Developers
  5. Pythian Group Blog » Tuning Pack 11g : Real-Time SQL Monitoring
  6. PSOUG - New in 11gR1*****
  7. Robert G. Freeman’s Blog: 11g Security New Feature… A short one…
  8. Oracle 11g new Features Summary by Burleson
  9. My top 10 Oracle 11g New Features Part 3 - 5 nice optimizer statistics
  10. Oracle 11g Top New Features for DBA Virag Sharma
  11. Oracle Magazine: PL/SQL Practices: On the PL/SQL Function Result Cache
  12. Changes in Oracle 11.1 - Julian Dyke *****
  13. Blogging about 11g - Part 7 - Function Result Cache
  14. Oracle 11g New Features SQL plan management (SPM)
  15. Oracle 11g New feaures : Case Sensitive Password
  16. SQL Performance Analyzer (SPA) Part - 2
  17. Oracle 11g ADR Automatic Diagnostic Repository
  18. New Parameters in 11g (Part 2) | Dizwell Informatics
  19. Oracle 11g DRCP: Database Resident Connection Pooling - second attempt
  20. Oracle 11g internals part 1: Automatic Memory Management
  21. My top 10 Oracle 11g New Features Part 4 - the SQL Query Result Cache
  22. Oracle 11g Tips by Burleson Consulting

You can have a look on following forum as well. Good thing about this forum is that , it update time to time , with latest 11g Links. Thanks to TongucY

http://forums.oracle.com/forums/thread.jspa?threadID=542281&tstart=90

- Vi
Categories: DBA Blogs

Oracle 11g New Features SQL plan management (SPM)

Fri, 2007-09-07 07:54
You might have noticed that execution plan changed in CBO , specially in following case

  • Database Upgrade
  • Database / Schema Stats collection
  • Change in environment ( LinkUnix to Linux Migration )
  • Change in data














Case
:
You want to upgrade database from 10g to 11g and this change can cause regressions in SQL performance,and fixing them manually can be difficult and time consuming. Sql tuning can be used but, this is a reactive mechanism and cannot guarantee stable performance when drastic changes happen to the system. SQL tuning can only resolve performance issues after they have occurred and are identified. For example, a SQL statement may become a high-load statement due to a plan change, but this cannot be resolved by SQL tuning until after the plan change occurs.

Solution: Oracle 11g new features SQL plan management (SPM) records and evaluates the execution plans of SQL Statements over time, and builds SQL plan baselines composed of a set of existing plans known to be efficient. The SQL plan baselines are then used to preserve performance of corresponding SQL statements, regardless of changes occurring in the system.

Note:- Before upgrading database to 11g (11.1.0.6) , i have collect SQL Tuning set (STS) for 7 Day using "capture_cursor_cache_sqlset". By assuming that in 7 day all SQL stas will collected in STS. After that upgraded database to 11g ( from 10g 10.2.0.3) , i have used this STS to compare execution plan from 11g. Please check "SQL Performance Analyzer Part - 2"
for steps to transfer STS one database to other database


1) Capturing SQL Plan Baselines

· Automatic Plan Capture ( 11g Only )

· Manual Plan Capture ( 10g and 11g )

2) Make Changes Upgrade Database / collect stats / Migrate Database to Linux

3) Upload SQL Plan Baseline

4) Enable the use of SQL plan baselines

5) Evolving SQL Plan Baselines

1) Capturing SQL Plan Baselines in oracle 10g before upgrading to 11g

a)
Automatic Plan Capture(11g only)

When OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES set true , then database automatically creates and maintains the plan history for SQL statements using information provided by the optimizer.

>

In init.ora file

OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = true

OR

SQL>Alter system set OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = true;

Note : - Above automatic plan capture only work on 11g , if you want to capture plan in 10g , then check section “Manual Plan Capture”

b) Manual Plan Capture ( 10g and 11g )

Following script will capture sql plan in SQL tuning set for 7 Day ( this script will run for 7 day , you can change time according to your need )

exec dbms_sqltune.drop_sqlset('STS_RAG_CMUL');
exec dbms_sqltune.create_sqlset('STS_RAG_CMUL');

DECLARE
sts_cmul VARCHAR2(30) := 'STS_RAG_CMUL';
BEGIN

dbms_sqltune.capture_cursor_cache_sqlset(sts_cmul,
604800,
1,
'MERGE',
dbms_sqltune.MODE_ACCUMULATE_STATS
);

END;

capture_cursor_cache_sqlset : The procedure captures a workload from the cursor cache into a SQL tuning set, polling the cache multiple times over a time period and updating the workload data stored there. It can execute over as long a period as required to capture an entire system workload.

  • time_limit : 604800 ( The total amount of time, in seconds, to execute , 7 day = 7 * 24 * 60 * 60 = 604800 )
  • repeat_interval : 1 ( The amount of time, in seconds, to pause between sampling )
  • capture_option During capture, either insert new statements, update existing ones, or both.
    • 'INSERT'
    • 'UPDATE',
    • 'MERGE'
  • capture_mode capture Option (UPDATE and MERGE capture options).
    • MODE_REPLACE_OLD_STATS - Replace statistics when the number of executions
      seen is greater than that stored in the SQL tuning set

MODE_ACCUMULATE_STATS - Add new values to current values for SQL we already store. Note that this mode detects if a statement has been aged out, so the final value for a statistics will be the sum of the statistics of all cursors that statement existed under.

The CAPTURE_CURSOR_CACHE_SQLSET function enables the capture of the full system workload by repeatedly polling the cursor cache over a specified interval. This function is a lot more efficient than repeatedly using the SELECT_CURSOR_CACHE and LOAD_SQLSET procedures to capture the cursor cache over an extended period of time. This function effectively captures the entire workload, as SQL Profiles opposed to the AWR—which only captures the workload of high-load SQL statements or the LOAD_SQLSET procedure, which accesses the data source only once.

2) Make Changes e.g. Upgrade Database / collect stats / Migrate Database to Linux Collect stats

exec dbms_stats.set_param('ESTIMATE_PERCENT','100');

exec dbms_stats.set_param('method_opt','FOR ALL COLUMNS SIZE 254');

exec dbms_stats.Gather_Database_Stats;

3) Upload SQL Plan Baseline

If you have collected stats manually in SQL tuning sets ( STS) , then you need to
upload baseline from STS

-- Upload plan manually using dbms_spm
--

variable pls number;

exec :pls := dbms_spm.load_plans_from_sqlset(sqlset_name=>'STS_RAG_CMUL',-
basic_filter=>'parsing_schema_name like ''APPS'' and plan_hash_value!=0', -
fixed=>'NO',commit_rows=>1000);

4) Enable the use of SQL plan baselines

To enable the use of SQL plan baselines, set the OPTIMIZER_USE_SQL_PLAN_BASELINES
initialization parameter to TRUE. By default, this parameter is set to TRUE.

--In init.ora file
--

OPTIMIZER_USE_SQL_PLAN_BASELINES = TRUE

OR

SQL> alter system set OPTIMIZER_USE_SQL_PLAN_BASELINES= true;

5) Evolving SQL Plan Baselines

I have captured 10g plans in STS and after upgrading database to 11g uploaded those plans ,using dbms_spm.load_plans_from_sqlset. After uploading plans manually , I have set

OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES to true.

--Check not accepted Plans
--

SQL> select sql_handle, plan_name, enabled, accepted, fixed
from dba_sql_plan_baselines
Where ACCEPTED='NO' and
PARSING_SCHEMA_NAME like 'APPS';

SQL_HANDLE PLAN_NAME ENABLED ACCEPTED FIXED
--------------------- ------------------------- ------- --------- -------
SYS_SQL_9295397103ae5ebe SYS_SQL_PLAN_74720f16b0fcefa7 YES NO NO
SYS_SQL_9295397103ad3eba SYS_SQL_PLAN_03ad3ebaa086802f YES NO NO
SYS_SQL_434ef30d9da6a29b SYS_SQL_PLAN_9da6a29b1f6e321d YES NO NO
SYS_SQL_13836d6b3da62bbb SYS_SQL_PLAN_3da62bbbe5990995 YES NO NO
.......................................... ....................................

40 rows selected.

-- Displaying SQL Plan Baselines
--

SQL> select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle=>'SYS_SQL_13836d6b3da62bbb',format=>'basic'));

--------------------------------------------------------------------------------
SQL handle: SYS_SQL_13836d6b3da62bbb
SQL text: ( I have removed SQL text and changed table name in below execution plan)
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SYS_SQL_PLAN_3da62bbb91266099
Enabled: YES Fixed: NO Accepted: YES Origin: MANUAL-LOAD
--------------------------------------------------------------------------------

Plan hash value: 2877140902
-------------------------------------------------
Id Operation Name
-------------------------------------------------
0 SELECT STATEMENT
1 SORT ORDER BY
2 HASH JOIN SEMI
3 TABLE ACCESS FULL PROD
4 INDEX FULL SCAN COMP_PROD
-------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SYS_SQL_PLAN_3da62bbbe5990995
Enabled: YES Fixed: NO Accepted: NO Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------

Plan hash value: 3245492848

-----------------------------------------
Id Operation Name
-----------------------------------------
0 SELECT STATEMENT
1 SORT ORDER BY
2 HASH JOIN SEMI
3 TABLE ACCESS FULL PROD
4 TABLE ACCESS FULL COMP
-----------------------------------------

42 rows selected.

SQL>


Evolving Plans with pls/sql function DBMS_SPM.EVOLVE_SQL_PLAN_BASELIN

--Evolving Plans With DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE
--

SQL> DECLARE
report clob;
BEGIN

report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(
sql_handle => 'SYS_SQL_13836d6b3da62bbb');
DBMS_OUTPUT.PUT_LINE(report);
END;
/

-------------------------------------------------------------------------------
Evolve SQL Plan Baseline Report
-------------------------------------------------------------------------------
Inputs:
-------
SQL_HANDLE =SYS_SQL_13836d6b3da62bbb
PLAN_NAME =
TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
VERIFY = YES
COMMIT = YES

Plan: SYS_SQL_PLAN_3da62bbbe5990995
-----------------------------------
Plan was verified: Time used .06 seconds.
Failed performance criterion: Compound improvement ratio <= .3.

Baseline Plan Test Plan Improv. Ratio

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

Execution Status: COMPLETE COMPLETE
Rows Processed: 1460 1460
Elapsed Time(ms): 10 10 1
CPU Time(ms): 9 9 1
Buffer Gets: 84 286 .29
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: 0.

PL/SQL procedure successfully completed.

SQL>

Just for demo purpose , lets accept plan 2 ('SYS_SQL_PLAN_3da62bbbe5990995')

-- Acccept plan 2 ('SYS_SQL_PLAN_3da62bbbe5990995') and Fixed it
--

variable cnt number;
exec :cnt := dbms_spm.alter_sql_plan_baseline(sql_handle =>
'SYS_SQL_13836d6b3da62bbb', -
plan_name => 'SYS_SQL_PLAN_3da62bbbe5990995', -
attribute_name => 'ACCEPTED', attribute_value => 'YES');

SQL> exec :cnt := dbms_spm.alter_sql_plan_baseline(sql_handle =>
'SYS_SQL_13836d6b3da62bbb', -
plan_name => 'SYS_SQL_PLAN_3da62bbbe5990995', -
attribute_name => 'ACCEPTED', attribute_value => 'YES');

PL/SQL procedure successfully completed.
SQL>

-- Same pl/sql function used to fix plans
-- Optimizer always picked FIXED plans

SQL> exec :cnt := dbms_spm.alter_sql_plan_baseline(sql_handle =>
'SYS_SQL_13836d6b3da62bbb', -
plan_name => 'SYS_SQL_PLAN_3da62bbbe5990995', -
attribute_name => 'FIXED', attribute_value => 'YES');

PL/SQL procedure successfully completed.

SQL>

- - Check status of plans
--

SQL> select sql_handle, plan_name, enabled, accepted, fixed from dba_sql_plan_baselines
2 Where FIXED='YES' and PARSING_SCHEMA_NAME like '';

SQL_HANDLE PLAN_NAME ENABLED ACCEPTED FIXED
----------------------------- ---------------------------- -------------- --------
SYS_SQL_13836d6b3da62bbb SYS_SQL_PLAN_3da62bbbe5990995 YES YES YES

SQL>

>

>

-- Displaying SQL Plan Baselines after changes
--

SQL> select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle=>'SYS_SQL_13836d6b3da62bbb',format=>'basic'));

--------------------------------------------------------------------------------
SQL handle: SYS_SQL_13836d6b3da62bbb
SQL text: ( I have removed SQL text and changed table name in below execution plan)
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SYS_SQL_PLAN_3da62bbb91266099
Enabled: YES Fixed: NO Accepted: YES Origin: MANUAL-LOAD
--------------------------------------------------------------------------------

Plan hash value: 2877140902

-------------------------------------------------
Id Operation Name
-------------------------------------------------
0 SELECT STATEMENT
1 SORT ORDER BY
2 HASH JOIN SEMI
3 TABLE ACCESS FULL PROD
4 INDEX FULL SCAN COMP_PROD
-------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SYS_SQL_PLAN_3da62bbbe5990995
Enabled: YES Fixed: YES Accepted: YES Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------

Plan hash value: 3245492848

-----------------------------------------
Id Operation Name
-----------------------------------------
0 SELECT STATEMENT
1 SORT ORDER BY
2 HASH JOIN SEMI
3 TABLE ACCESS FULL PROD
4 TABLE ACCESS FULL COMP
-----------------------------------------
42 rows selected.


ReferenceSQL Plan Management Chapter 15
Categories: DBA Blogs

Pages