Saturday, October 11, 2014

OEM 12c Database Monitoring Concepts and Framework

Prepared a concept map for me to understand OEM 12c monitoring concepts and framework:



Thursday, June 12, 2014

Using analytic function can improve query performance greatly

Let's say we have a table: t (x,y,z), how to output the rows of the table with max value of z based on y?

For example if we have (1,1,2) (1,1,3),(2,1,1), we should get (1,1,3).

Below is a test case that demonstrats two approaches: Approach 1 uses subquery resulting in 38 consistent gets; Approach 2 uses analytic approach resulting in 7 consistent gets
SQL>@test
SQL>set echo on
SQL>drop table t;

Table dropped.

SQL>create table t(x number, y number, z number);

Table created.

SQL>
SQL>insert into t values(1,1,2);

1 row created.

SQL>insert into t values(1,1,3);

1 row created.

SQL>insert into t values(2,1,1);

1 row created.

SQL>
SQL>
SQL>set autotrace on
SQL>-- approach 1
SQL>
SQL>select x, y, z
  2  from t
  3  where
  4   exists ( select 1 from
  5            ( select y, max(z) mz
  6              from t
  7               group by y
  8             ) zz
  9             where t.y=zz.y
 10               and t.z=zz.mz
 11          )
 12  ;

         X          Y          Z
---------- ---------- ----------
         1          1          3

1 row selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3359768323

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |     3 |   117 |     5  (20)| 00:00:01 |
|*  1 |  FILTER              |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL  | T    |     3 |   117 |     2   (0)| 00:00:01 |
|*  3 |   FILTER             |      |       |       |            |          |
|   4 |    HASH GROUP BY     |      |     1 |    26 |     3  (34)| 00:00:01 |
|*  5 |     TABLE ACCESS FULL| T    |     1 |    26 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter( EXISTS (SELECT /*+ */ 0 FROM "T" "T" WHERE "Y"=:B1 GROUP
              BY "Y",:B2 HAVING MAX("Z")=:B3))
   3 - filter(MAX("Z")=:B1)
   5 - filter("Y"=:B1)

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
         78  recursive calls
          0  db block gets
         38  consistent gets
          0  physical reads
          0  redo size
        270  bytes sent via SQL*Net to client
        247  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>
SQL>-- approach 2
SQL>
SQL>
SQL>select x,y,z from
  2  (
  3     select x,y, z, max(z) over (partition by y) mz
  4     from t
  5  )
  6  where z=mz
  7  ;

         X          Y          Z
---------- ---------- ----------
         1          1          3

1 row selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2206009079

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     3 |   156 |     3  (34)| 00:00:01 |
|*  1 |  VIEW               |      |     3 |   156 |     3  (34)| 00:00:01 |
|   2 |   WINDOW SORT       |      |     3 |   117 |     3  (34)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| T    |     3 |   117 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("Z"="MZ")

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
          4  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
        272  bytes sent via SQL*Net to client
        247  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

Approach 1 is essetially used in a query in one of our production databases that causes performance issue. I suggested we re-write the query using approach 2. My test results of the production-like query show that bad query needs 2M consistent gets to get 6382 rows out while the re-written query needs only about 6k.

Monday, June 02, 2014

Bitmap plans can estimate wrong cardinality

There is a problem query involving 3-table join in one of our prodcutin databases. The difference between the good plan and bad plan is the join order in a hash join execution plan. The reason for Oracle to pick up the bad join order is that Oracle CBO chooses the bitmap plan and this plan gives very wrong cardinality. What I did is to set "_b_tree_bitmap_plans"=false at system level, which allowing the CBO to generate the good plan. Then I created a sql plan baseline for that query from the good plan ( using sqlt/utl/coe_load_sql_baseline.sql script). After that, considering that there may be some queries that benfit from the bitmap access path, I changed back "_b_tree_bitmap_plans" to be true.

Below tests demonstrated how wrong the cardinaltiy estimeated by the bitmap plan could be.

  • in the bitmap plan,  the number of rows estimated is 578K
SQL> select * from BOD_OTHR_SERIAL_ACTIVITY_I8400 I8400
  2  where
  3  (I8400.DVTQ_ACCT_NUM = 'A145779917' OR (
  4      I8400.UJTJPM_CUSTOMER_ID = '' AND I8400.UJTJPM_ACCOUNT_ID = ''
  5  ))
  6  ;

Execution Plan
----------------------------------------------------------
Plan hash value: 972526864

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name                           | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                                |   578K|   170M|   161K (11)| 00:05:17 |
|   1 |  TABLE ACCESS BY INDEX ROWID     | BOD_OTHR_SERIAL_ACTIVITY_I8400 |   578K|   170M|   161K (11)| 00:05:17 |
|   2 |   BITMAP CONVERSION TO ROWIDS    |                                |       |       |            |          |
|   3 |    BITMAP OR                     |                                |       |       |            |          |
|   4 |     BITMAP CONVERSION FROM ROWIDS|                                |       |       |            |          |
|*  5 |      INDEX RANGE SCAN            | IX_I8400_04                    |       |       |     1   (0)| 00:00:01 |
|   6 |     BITMAP CONVERSION FROM ROWIDS|                                |       |       |            |          |
|*  7 |      INDEX RANGE SCAN            | IX_I8400_07                    |       |       |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("I8400"."UJTJPM_CUSTOMER_ID"='' AND "I8400"."UJTJPM_ACCOUNT_ID"='')
   7 - access("I8400"."DVTQ_ACCT_NUM"='A145779917')


  • -- disable the bitmap plan
SQL> alter session set "_b_tree_bitmap_plans"=false;

Session altered.


  • -- Now the estimated rows is 2
SQL> select * from BOD_OTHR_SERIAL_ACTIVITY_I8400 I8400
  2  where
  3  (I8400.DVTQ_ACCT_NUM = 'A145779917' OR (
  4      I8400.UJTJPM_CUSTOMER_ID = '' AND I8400.UJTJPM_ACCOUNT_ID = ''
  5  ))
  6  ;

Execution Plan
----------------------------------------------------------
Plan hash value: 7978316

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                           | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                                |     2 |   618 |     9   (0)| 00:00:01 |
|   1 |  CONCATENATION               |                                |       |       |            |       |
|   2 |   TABLE ACCESS BY INDEX ROWID| BOD_OTHR_SERIAL_ACTIVITY_I8400 |     1 |   309 |     6   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IX_I8400_07                    |     1 |       |     4  (25)| 00:00:01 |
|*  4 |   TABLE ACCESS BY INDEX ROWID| BOD_OTHR_SERIAL_ACTIVITY_I8400 |     1 |   309 |     3   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN          | IX_I8400_04                    |     1 |       |     2  (50)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("I8400"."DVTQ_ACCT_NUM"='A145779917')
   4 - filter(LNNVL("I8400"."DVTQ_ACCT_NUM"='A145779917'))
   5 - access("I8400"."UJTJPM_CUSTOMER_ID"='' AND "I8400"."UJTJPM_ACCOUNT_ID"='')


  •  the acutall number of row is 1:
SQL> select count(*)  from BOD_OTHR_SERIAL_ACTIVITY_I8400 I8400
  2      where
  3      (I8400.CUST_ACCT_NUM = 'A145779917' OR (
  4          I8400.UJTJPM_CUSTOMER_ID = '' AND I8400.UJTJPM_ACCOUNT_ID = ''
  5      ))
  6      ;

  COUNT(*)
----------
         1

1 row selected.

Sunday, April 13, 2014

SQL Tuning

Things I think important to know for sql tuning:

  • Be able to read execution plan
         - right-most first, from top to bottom
         - visual approach to assist understanding   
         - pay attention to the Predicate Information section
  • Obtaining the execution plan
         - AUTOTRACE 
         - dbms_xplan.display_curosr
  • Cardinality
         - the predicted number of rows generated by an operation
         - cardinality = selectivity * ( number of input rows)
  • COST
         - represent the optimizer's best estimate of the time it will take to execute the statement; CBO always chooses the execution plan with minimum cost.
         - using dbms_xplan.display_cursor to obtain E-row vs A-row ( set statistical_level=all;  or using /* gather_plan_statistics */ )
  • AWR, ADDM, ASH and wait interface - able to identify the offensive SQLs
  • bind variable peeking and plan instability
  • 10046 trace & tkprof
         - a rule of thumb to check efficiency: query+ current/rows < 20
         ie.  select * from ( select col_of_insterest,count(*) from table_A group by col_of_interest order by 2 desc) where rownun <=50
  • Talk to lead developers
         - tune questions not just tune queries
  • Fix a bad query or method of improvement
        - index re-design,
        - hints,
        - gather stats,
        - explore parallelism,
        - plan stability: sql profile( sqlt coe* script) and sql plan baseline (sqlt coe* script)

Friday, April 11, 2014

Creating a DG Broker Configuration and Perform Swithover testing

1. Register Static service

To enable DGMGRL to restart instances during the course of broker operations, a static service must be registered In the listener.ora, the following entries should be presented:
 LISTENER = (DESCRIPTION =
      (ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=host_name)
      (PORT=port_num))))
 SID_LIST_LISTENER=(SID_LIST=(SID_DESC=(SID_NAME=sid_name)
      (GLOBAL_DBNAME=db_unique_name_DGMGRL.db_domain)
      (ORACLE_HOME=oracle_home)
      (ENVS="TNS_ADMIN=oracle_home/network/admin")))
In my case, for example in the listener.ora, including:
 SID_LIST_LISTENER =
     (SID_DESC =
       (SID_NAME = etsdb)
       (GLOBAL_DBNAME=etsdb_DGMGRL)          
       (ORACLE_HOME = /apps/opt/oracle/product/11.2.0)
       (ENVS="TNS_ADMIN=/apps/opt/oracle/product/11.2.0/network/admin")
     )
   )

LOCAL_LISTENER initialization parameter shout set,ie

SQL> alter system set local_listener=(ADDRESS=(PROTOCOL=TCP) (HOST=server_name) (PORT=15xx)) scope=both

More details check: Oracle Data Guard Broker and Static Service Registration (Doc ID 1387859.1)

2. Create a broker configuration

pre-requisite: DG_BROKER_START should set to TRUE in order for DMON background process to start
xxx.com:/db1/opt/oracle/product/11.2.0/dbs [ettdb] $ dgmgrl              
DGMGRL for Solaris: Version 11.2.0.3.0 - 64bit Production
 
Copyright (c) 2000, 2009, Oracle. All rights reserved.
 
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys       
Password:
Connected.
DGMGRL>gt; create configuration 'DG_ETS_CONFIG' as
> primary database is 'ettdb'
> connect identifier is ettdb;
Configuration "DG_ETS_CONFIG" created with primary database "ettdb"
DGMGRL> add database 'etsdb' as
> connect identifier is etsdb;
Database "etsdb" added
DGMGRL> show configuration
 
Configuration - DG_ETS_CONFIG
 
  Protection Mode: MaxPerformance
  Databases:
    ettdb - Primary database
    etsdb - Physical standby database
 
Fast-Start Failover: DISABLED
 
Configuration Status:
DISABLED
 
3. Enable the configuration
DGMGRL> enable configuration
Enabled.
DGMGRL> show configuration
 
Configuration - DG_ETS_CONFIG
 
  Protection Mode: MaxPerformance
  Databases:
    ettdb - Primary database
    etsdb - Physical standby database
 
Fast-Start Failover: DISABLED
 
Configuration Status:
SUCCESS

4. Test switchover
xxx.com:/home/oracle [ettdb] $ dgmgrl                
DGMGRL for Solaris: Version 11.2.0.3.0 - 64bit Production
 
Copyright (c) 2000, 2009, Oracle. All rights reserved.
 
Welcome to DGMGRL, type "help" for information.

DGMGRL> connect sys
Password:
Connected.
DGMGRL> show configuration
 
Configuration - DG_ETS_CONFIG
 
  Protection Mode: MaxPerformance
  Databases:
    ettdb - Primary database
    etsdb - Physical standby database
 
Fast-Start Failover: DISABLED
 
Configuration Status:
SUCCESS
 
DGMGRL> switchover to etsdb
Performing switchover NOW, please wait...
New primary database "etsdb" is opening...
Operation requires shutdown of instance "ettdb" on database "ettdb"
Shutting down instance "ettdb"...
ORACLE instance shut down.
Operation requires startup of instance "ettdb" on database "ettdb"
Starting instance "ettdb"...
ORACLE instance started.
Database mounted.
Database opened.
Switchover succeeded, new primary is "etsdb"
DGMGRL> show configuration

DGMGRL> switchover to ettdb
Performing switchover NOW, please wait...
New primary database "ettdb" is opening...
Operation requires shutdown of instance "etsdb" on database "etsdb"
Shutting down instance "etsdb"...
ORACLE instance shut down.
Operation requires startup of instance "etsdb" on database "etsdb"
Starting instance "etsdb"...
ORACLE instance started.
Database mounted.
Database opened.
Switchover succeeded, new primary is "ettdb"


 
Configuration - DG_ETS_CONFIG
 
  Protection Mode: MaxPerformance
  Databases:
    etsdb - Primary database
    ettdb - Physical standby database
 
Fast-Start Failover: DISABLED
 
Configuration Status:
SUCCESS

A few issues encountered during testing:

1. When set up configuration in DGMGRL, receive ORA-00088: command cannot be executed by shared server

The application this database supports requires using shared server, the fix is to add "(server=dedicated)" in the tnsnames.ore i.e.
ettdb =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xxx.com)(PORT = 1523))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ettdb )
      (server=dedicated) 
    )
  )

2. ORA-16664: unable to receive the result from a database

It turns out due to I have a typo when doing "create configuration":

DGMGRL> create configuration 'DG_ETS_CONFIG' as
> primary database is 'ettdb'
> connect identifier is etsdb;    ===========> should be ettdb obviously


3. switchover failed in an attempt


When I use dgmgrl connect to the primary db, I also have a dgmgrl session connected to the standby db. This appears to be the reason. In the standby site Data Guard Broker log file I saw:
SQL Execution error=604, sql=[ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WAIT WITH SESSION SHUTDOWN]. See error stack below.
  ORA-00604: error occurred at recursive SQL level 1
  ORA-01093: ALTER DATABASE CLOSE only permitted with no sessions connected
Sswitchover to primary command failed

After disconnecting any sessions connected to the standby db, switchover to the standby succeeded.

References: http://docs.oracle.com/cd/E11882_01/server.112/e40771/configure.htm#i1021661