Wednesday, December 27, 2017

How to Start Oracle RAC Database

In Starting Real Application Clusters Database, We will also follow a set of sequence. The order will be just opposite to Shutting down the Real Application Clusters Database. Here we are following all such sequences because we stopped databases, node-apps and other processes manually before stopping CRS. CRS can re-start resources which were up while it was going down.

1. Start Oracle Clusterware and ASM.
2. Start Node applications running on nodes.
3. Start RAC Database Instances on all nodes.
4. Start Oracle Home process accessing database.

1. Start Oracle Clusterware and ASM: 
If Oracle HAS auto-start was not disabled before restarting server, CRS starts automatically. However if auto-start is disabled you need to execute crsctl start crs command to start CRS and ASM. In mine case auto-start is enabled so I just verified the status.
[root@rac121 ~]# crsctl config crs
CRS-4622: Oracle High Availability Services autostart is enabled.

[root@rac121 ~]# crsctl check crs
CRS-4638: Oracle High Availability Services is online
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online

[root@rac121 ~]# crsctl check cluster -all
**************************************************************
rac121:
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
**************************************************************
rac122:
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
**************************************************************

2. Start Node applications running on nodes:
To start node applications running on a node, enter the following command for each nodes.
[oracle@rac121 ~]$ srvctl status nodeapps
VIP rac121-vip.localdomain is enabled
VIP rac121-vip.localdomain is not running
VIP rac122-vip.localdomain is enabled
VIP rac122-vip.localdomain is not running
Network is enabled
Network is running on node: rac121
Network is running on node: rac122
ONS is enabled
ONS daemon is running on node: rac121
ONS daemon is running on node: rac122

[oracle@rac121 ~]$ srvctl start nodeapps -n rac121
PRKO-2421 : Network resource is already started on node(s): rac121,rac122
PRKO-2422 : ONS is already started on node(s): rac121,rac122

[oracle@rac121 ~]$ srvctl start nodeapps -n rac122
PRKO-2421 : Network resource is already started on node(s): rac121,rac122
PRKO-2422 : ONS is already started on node(s): rac121,rac122

[oracle@rac121 ~]$ srvctl status nodeapps
VIP rac121-vip.localdomain is enabled
VIP rac121-vip.localdomain is running on node: rac121
VIP rac122-vip.localdomain is enabled
VIP rac122-vip.localdomain is running on node: rac122
Network is enabled
Network is running on node: rac121
Network is running on node: rac122
ONS is enabled
ONS daemon is running on node: rac121
ONS daemon is running on node: rac122



3. Start RAC Database Instances on all nodes:-
Now, We will start database instances on database nodes.
[oracle@rac121 ~]$ srvctl start database -d racdb
[oracle@rac121 ~]$ srvctl status database -d racdb
Instance racdb1 is running on node rac121
Instance racdb2 is running on node rac122

4.Start Oracle Home process accessing database:-
let's start listener and OEM to access database and allow users to connect to the database.
[oracle@rac121 ~]$ srvctl start listener -n rac121
[oracle@rac121 ~]$ srvctl start listener -n rac122
[oracle@rac121 ~]$ srvctl status listener
Listener LISTENER is enabled
Listener LISTENER is running on node(s): rac121,rac122

[oracle@database ~]$ emctl start dbconsole
. started.

Verify Status
Now we can use below commands to verify status of cluster and services.
[oracle@rac121 ~]$ cd grid_home/bin
[oracle@rac121 ~]$ crsctl status resource -t
[oracle@rac121 ~]$ crsctl check crs
[oracle@rac121 ~]$ crsctl check cluster -all

Related Links
How to Stop Processes in Oracle RAC

How to Stop Processes in Oracle RAC

When you want to stop an entire node or cluster (for maintenance purposes, for example), you run either the crsctl stop crs command on the node or the crsctl stop cluster -all command, provided you have the required cluster privileges. These commands stop all database instances running on a server or in the cluster and ensure that their state is recovered after you restart the cluster.


Using either of these CRSCTL commands to stop all database instances on a server or in the cluster can lead to the database instances being stopped similar to shutdown abort, which requires an instance recovery on startup. If you use SRVCTL to stop the database instances manually before stopping the cluster, then you can prevent a shutdown abort, but this requires that you manually restart the database instances after restarting Oracle Clusterware.

Since, RAC involves so many components like Database instance, ASM instance, Node application and CRS services, So you need to follow below steps if you don't want to abort your database services and perform manual shutdown of services.

Here I am taking an example of Two node RAC database to perform all operations. Steps followed are as:

1. Shutdown Oracle Home process accessing database.
2. Shutdown RAC Database Instances on all nodes.
3. Shutdown Node applications running on nodes.
4. Shut down the Oracle Clusterware processes and the Oracle ASM instance


1. Shutdown Oracle Home process accessing database :-
There could be oracle and non-Oracle application which access database, So first step is to stop all the applications accessing database. Shut down any processes in the Oracle home on each node that can access a database like Enterprise Manager Grid Control and Database Listener which access database for monitoring and database connections.

[oracle@rac1 ~]$ emctl stop dbconsolc
Stopping Oracle Enterprise Manager………
Stopped…………

[oracle@rac121 ~]$ srvctl status listener
Listener LISTENER is enabled
Listener LISTENER is running on node(s): rac121,rac122

[oracle@rac121 ~]$ srvctl stop listener -n rac121

[oracle@rac121 ~]$ srvctl stop listener -n rac122

[oracle@rac121 ~]$ srvctl status listener
Listener LISTENER is enabled
Listener LISTENER is not running

2. Shutdown RAC Database Instances on all nodes:-
Shut down all Oracle RAC instances on all nodes. First, I am checking on which server database is running and then stopping and verify for the same.

[oracle@rac121 ~]$ srvctl status database -d racdb
Instance racdb1 is running on node rac121
Instance racdb2 is running on node rac122

[oracle@rac121 ~]$ srvctl stop database -d racdb

[oracle@rac121 ~]$ srvctl status database -d racdb
Instance racdb1 is not running on node rac121
Instance racdb2 is not running on node rac122

We just need to execute one command from any one of the server having database and it will stop all database instances on all servers. If you have more than one database configured on Nodes, then you have to execute this command for each database.
3. Shutdown Node applications running on nodes:-
To stop node applications running on a node, enter the following command, where node is the name of the node where the applications are running. Repeat same command for all nodes one by one. If you face any issue in stopping node applications use "-f" as force option to stop applications.

[oracle@rac121 ~]$ grid_home/srvctl stop nodeapps -n rac121 -f

[oracle@rac121 ~]$ grid_home/srvctl stop nodeapps -n rac122 -f

[oracle@rac121 ~]$ srvctl status nodeapps
VIP rac121-vip.localdomain is enabled
VIP rac121-vip.localdomain is not running
VIP rac122-vip.localdomain is enabled
VIP rac122-vip.localdomain is not running
Network is enabled
Network is not running on node: rac121
Network is not running on node: rac122
ONS is enabled
ONS daemon is not running on node: rac121
ONS daemon is not running on node: rac122 

4. Shut down the Oracle Clusterware processes and the Oracle ASM instance
Shut down the Oracle Clusterware processes and the Oracle ASM instance on each node by entering the "crsctl stop cluster " command on all nodes as the root user.

Note:-To stop Oracle Clusterware and Oracle ASM instances on all nodes in the cluster, you can use a single command: crsctl stop cluster -all.

[oracle@rac121 ~]$ grid_env
[oracle@rac121 ~]$ crsctl check cluster -all
**************************************************************
rac121:
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
**************************************************************
rac122:
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
**************************************************************
[root@rac121 ~]# crsctl stop cluster -all
CRS-2673: Attempting to stop 'ora.crsd' on 'rac122'
CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on 'rac122'
CRS-2673: Attempting to stop 'ora.DATA.dg' on 'rac122'
CRS-2673: Attempting to stop 'ora.crsd' on 'rac121'
CRS-2677: Stop of 'ora.DATA.dg' on 'rac122' succeeded
CRS-2673: Attempting to stop 'ora.asm' on 'rac122'
CRS-2677: Stop of 'ora.asm' on 'rac122' succeeded
CRS-2792: Shutdown of Cluster Ready Services-managed resources on 'rac122' has completed
CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on 'rac121'
CRS-2673: Attempting to stop 'ora.mgmtdb' on 'rac121'
CRS-2673: Attempting to stop 'ora.oc4j' on 'rac121'
CRS-2677: Stop of 'ora.crsd' on 'rac122' succeeded
CRS-2673: Attempting to stop 'ora.ctssd' on 'rac122'
CRS-2673: Attempting to stop 'ora.evmd' on 'rac122'
CRS-2673: Attempting to stop 'ora.storage' on 'rac122'
CRS-2677: Stop of 'ora.storage' on 'rac122' succeeded
CRS-2673: Attempting to stop 'ora.asm' on 'rac122'
CRS-2677: Stop of 'ora.ctssd' on 'rac122' succeeded
CRS-2677: Stop of 'ora.evmd' on 'rac122' succeeded
CRS-2677: Stop of 'ora.mgmtdb' on 'rac121' succeeded
CRS-2673: Attempting to stop 'ora.DATA.dg' on 'rac121'
CRS-2673: Attempting to stop 'ora.MGMTLSNR' on 'rac121'
CRS-2677: Stop of 'ora.DATA.dg' on 'rac121' succeeded
CRS-2673: Attempting to stop 'ora.asm' on 'rac121'
CRS-2677: Stop of 'ora.asm' on 'rac121' succeeded
CRS-2677: Stop of 'ora.MGMTLSNR' on 'rac121' succeeded
CRS-2677: Stop of 'ora.oc4j' on 'rac121' succeeded
CRS-2792: Shutdown of Cluster Ready Services-managed resources on 'rac121' has completed
CRS-2677: Stop of 'ora.crsd' on 'rac121' succeeded
CRS-2673: Attempting to stop 'ora.ctssd' on 'rac121'
CRS-2673: Attempting to stop 'ora.evmd' on 'rac121'
CRS-2673: Attempting to stop 'ora.storage' on 'rac121'
CRS-2677: Stop of 'ora.storage' on 'rac121' succeeded
CRS-2673: Attempting to stop 'ora.asm' on 'rac121'
CRS-2677: Stop of 'ora.ctssd' on 'rac121' succeeded
CRS-2677: Stop of 'ora.evmd' on 'rac121' succeeded
CRS-2677: Stop of 'ora.asm' on 'rac122' succeeded
CRS-2673: Attempting to stop 'ora.cluster_interconnect.haip' on 'rac122'
CRS-2677: Stop of 'ora.cluster_interconnect.haip' on 'rac122' succeeded
CRS-2673: Attempting to stop 'ora.cssd' on 'rac122'
CRS-2677: Stop of 'ora.cssd' on 'rac122' succeeded
CRS-2677: Stop of 'ora.asm' on 'rac121' succeeded
CRS-2673: Attempting to stop 'ora.cluster_interconnect.haip' on 'rac121'
CRS-2677: Stop of 'ora.cluster_interconnect.haip' on 'rac121' succeeded
CRS-2673: Attempting to stop 'ora.cssd' on 'rac121'
CRS-2677: Stop of 'ora.cssd' on 'rac121' succeeded

[root@rac121 ~]# crsctl check cluster -all
**************************************************************
rac121:
CRS-4535: Cannot communicate with Cluster Ready Services
CRS-4530: Communications failure contacting Cluster Synchronization Services daemon
CRS-4534: Cannot communicate with Event Manager
**************************************************************
rac122:
CRS-4535: Cannot communicate with Cluster Ready Services
CRS-4530: Communications failure contacting Cluster Synchronization Services daemon
CRS-4534: Cannot communicate with Event Manager
**************************************************************
[root@rac121 ~]# crsctl stop has #run this on each node
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'rac121'
CRS-2673: Attempting to stop 'ora.gpnpd' on 'rac121'
CRS-2673: Attempting to stop 'ora.mdnsd' on 'rac121'
CRS-2673: Attempting to stop 'ora.crf' on 'rac121'
CRS-2677: Stop of 'ora.gpnpd' on 'rac121' succeeded
CRS-2677: Stop of 'ora.mdnsd' on 'rac121' succeeded
CRS-2677: Stop of 'ora.crf' on 'rac121' succeeded
CRS-2673: Attempting to stop 'ora.gipcd' on 'rac121'
CRS-2677: Stop of 'ora.gipcd' on 'rac121' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'rac121' has completed
CRS-4133: Oracle High Availability Services has been stopped.

[root@rac121 ~]# crsctl check cluster -all
CRS-4639: Could not contact Oracle High Availability Services
CRS-4000: Command Check failed, or completed with errors.

Note :-
"crsctl stop crs", command will stop Oracle High availability services (OHAS) and Clustware stack in a single command. So we need to run this command on each nodes. From 11g R2, you can do this in two steps, as we showed in above example.

1. To stop Oracle Clusterware and Oracle ASM instances on each nodes in the cluster.
       $crsctl stop cluster #local node
       $crsctl stop cluster –all #all nodes in the cluster

2. Stop Oracle High availability service demon on each node in the cluster.
       $crsctl stop has

Now RAC is completely down. Now you can move ahead with your patching, maintenance activity. Next let's see how to start Oracle RAC cluster database.

Related Links
How to Start Oracle RAC Database

Sunday, December 17, 2017

How To Change Execution Plan Of Query Without Modifying SQL Code

All of us have encountered the situation of performance regression in production environment where your company runs a packaged application or you are not allowed to make any changes in SQL code of application.

You get some poorly performing SQL here and root cause happens to be the bad execution plan. One of the options, is to put this SQL through the SQL tuning advisor and see if it comes up with a SQL profile that improves the execution plan, and if it does, to accept the profile.

Some of these performance issues could be critical, and require urgent resolution. In such urgent situations, you might be thinking, “I wish i could hint this query to get it to pick a better execution plan”. However, this being a packaged application or critical production environment, So your hands are tied. Let's figure out some other ways to tackle this situation. In this post we'll use sql plan management.

The original Plan
Note the full table scan on the products table and query is not doing well. As a DBA we are recommending index hint but we are not allowed to make any changes in application code.
SQL> select * from table (dbms_xplan.display_cursor(null,null,'TYPICAL'));
SQL_ID nfitk8askq3j, child number 0
-------------------------------------
select p.prod_name,sum(s.amount_sold) amt from sales s,products p where
s.prod_id = p.prod_id and p.supplier_id = :sup_id group by p.prod_name

Plan hash value: 896757596
----------------------------------------------------------------------------------------------------
| Id  | Operation    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |Pstart| Pstop |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |      |     |     |   587 (100)|      |    |    |
|   1 |  HASH GROUP BY                 |      | 71  |  3550 |   587  (12)| 00:00:08 |    |    |
|*  2 |   HASH JOIN                      |      | 72  |  3600 |   586  (12)| 00:00:08 |    |    |
|   3 |    VIEW                | VW_GBC_5  | 72  |  1224 |   583  (12)| 00:00:07 |    |    |
|   4 |     HASH GROUP BY             |      | 72  |   648 |   583  (12)| 00:00:07 |    |    |
|   5 |      PARTITION RANGE ALL  || 918K|  8075K|  533   (3)| 00:00:07 |  1 | 28 |
|   6 |       TABLE ACCESS FULL  | SALES|918K|  8075K|533(3)| 00:00:07 |  1 | 28 |
|*  7 |    TABLE ACCESS FULL    | PRODUCTS  | 72  |  2376 |  3   (0) | 00:00:01 |    |    |
----------------------------------------------------------------------------------------------------
 Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ITEM_1"="P"."PROD_ID")
   7 - filter("P"."SUPPLIER_ID"=:SUP_ID)
26 rows selected.

Create the plan baseline for this sql
SQL> variable spb number;
SQL> execute :spb := dbms_spm.load_plans_from_cursor_cache(sql_id=>'nfitk8askq3j');
PL/SQL procedure successfully completed.

SQL> select sql_handle,sql_text,plan_name,enabled from dba_sql_plan_baselines where sql_text like '%select p.prod_name%' ;
SQL_20yt3803a09c8ew1    select p.prod_name,          SQL_PLAN_09v9s0fh9t3z1c47b6be0 YES
                                             sum(s.amount_sold) amt  
                                             from sales s,products p
                                            where s.prod_i

Disable the original plan baseline from being used
SQL> exec :spb := dbms_spm.alter_sql_plan_baseline(sql_handle =>'SQL_20yt3803a09c8ew1',plan_name => 'SQL_PLAN_09v9s0fh9t3z1c47b6be0',attribute_name=>'enabled',attribute_value=>'NO');

SQL> select sql_handle,sql_text,plan_name,enabled from dba_sql_plan_baselines where sql_text like '%select p.prod_name%' ;
SQL_20yt3803a09c8ew1    select p.prod_name,           SQL_PLAN_09v9s0fh9t3z1c47b6be0  NO
                                             sum(s.amount_sold) amt  
                                             from sales s,products p
                                            where s.prod_i

Re-run the sql with an Index Hint
Note that we are using a index hint (/*+ index(p) */ , to illustrate the point that, the execution plan has changed, and now it picks an index as opposed to a full table scan from the original query.
SQL> select /*+ index(p) */ p.prod_name,sum(s.amount_sold) amt
from sales s,products p
where s.prod_id = p.prod_id
and p.supplier_id = :sup_id
group by p.prod_name
/
SQL> select * from table (dbms_xplan.display_cursor(null,null,'TYPICAL'))
/
SQL_ID utp5ov6qmpqqw, child number 0
-------------------------------------
select /*+ index(p) */ p.prod_name,sum(s.amount_sold) amt from sales
s,products p where s.prod_id = p.prod_id and p.supplier_id = :sup_id
group by p.prod_name
Plan hash value: 567802669
-------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |        |     |     | 587 (100)|      |     |     |
|   1 |  HASH GROUP BY          |        |  71 |  3550 | 587  (12)| 00:00:08 |     |     |
|*  2 |   HASH JOIN           |        |  72 |  3600 | 586  (12)| 00:00:08 |     |     |
|   3 |    VIEW            | VW_GBC_5     |  72 |  1224 | 583  (12)| 00:00:07 |     |     |
|   4 |     HASH GROUP BY         |        |  72 | 648 | 583  (12)| 00:00:07 |     |     |
|   5 |      PARTITION RANGE ALL| | 918K|  8075K| 533   (3)| 00:00:07 |   1 |  28 |
|   6 |       TABLE ACCESS FULL| SALES| 918K|  8075K| 533(3)| 00:00:07|1| 28 |
|*  7 |    TABLE ACCESS BY INDEX ROWID | PRODUCTS|72|2376|3 (0)| 00:00:01 |     |     |
|   8 |     INDEX FULL SCAN         | PRODUCTS_PK  |  72 |     |   1   (0)| 00:00:01 |     |     |
-------------------------------------------------------------------------------------------------------------
 Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ITEM_1"="P"."PROD_ID")
   7 - filter("P"."SUPPLIER_ID"=:SUP_ID)
 28 rows selected.

Switch the execution plan for the original, unhinted sql
We can then use dbms_spm to associate this new execution plan, to the sql_handle we created, for the original un-hinted sql, in the sql plan baseline. We use the new sql_id and plan_hash_value, from our hinted plan and we associate it to the sql_handle from the original query.

Note that the sql_handle we are using here, is the sql_handle for the original unhinted sql.
SQL> execute :spb := dbms_spm.load_plans_from_cursor_cache(sql_id => 'utp5ov6qmpqqw',plan_hash_value =>567802669,sql_handle=>'SQL_20yt3803a09c8ew1');
PL/SQL procedure successfully completed.

#Check that a new plan has been added to the baseline
SQL> select sql_handle,sql_text,plan_name,enabled from dba_sql_plan_baselines where sql_text like '%select p.prod_name%' ;
SQL_20yt3803a09c8ew1        select p.prod_name,     SQL_PLAN_09v9s0fh9t3z1c47b6be0 NO
                                                 sum(s.amount_sold) amt
                                                 from sales s,products p
                                                 where s.prod_i
SQL_20yt3803a09c8ew1        select p.prod_name,     SQL_PLAN_09v9s0fh9t3z1c47yptu8 YES
                                                 sum(s.amount_sold) amt 
                                                 from sales s,products p
                                                  where s.prod_i
   
Re-Run the original sql and check the execution Plan
SQL> select p.prod_name,sum(s.amount_sold) amt
from sales s,products p
where s.prod_id = p.prod_id
and p.supplier_id = :sup_id
group by p.prod_name
/
SQL> select * from table (dbms_xplan.display_cursor(null,null,'TYPICAL'));
SQL_ID fmjmws8askq3j, child number 1
-------------------------------------
select p.prod_name,sum(s.amount_sold) amt from sales s,products p where
s.prod_id = p.prod_id and p.supplier_id = :sup_id group by p.prod_name
Plan hash value: 567802669
-------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |        |     |     | 587 (100)|      |     |     |
|   1 |  HASH GROUP BY          |        |  71 |  3550 | 587  (12)| 00:00:08 |     |     |
|*  2 |   HASH JOIN           |        |  72 |  3600 | 586  (12)| 00:00:08 |     |     |
|   3 |    VIEW            | VW_GBC_5     |  72 |  1224 | 583  (12)| 00:00:07 |     |     |
|   4 |     HASH GROUP BY         |        |  72 | 648 | 583  (12)| 00:00:07 |     |     |
|   5 |      PARTITION RANGE ALL|| 918K|  8075K| 533   (3)| 00:00:07 |   1 |  28 |
|   6 |       TABLE ACCESS FULL| SALES| 918K| 8075K|533(3)| 00:00:07 |1|  28 |
|*  7 |    TABLE ACCESS BY INDEX ROWID | PRODUCTS| 72|2376|3 (0)| 00:00:01 |     |     |
|   8 |     INDEX FULL SCAN         | PRODUCTS_PK  |  72 |     |   1   (0)| 00:00:01 |     |     |
-------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ITEM_1"="P"."PROD_ID")
   7 - filter("P"."SUPPLIER_ID"=:SUP_ID)
Note
-----   - SQL plan baseline SQL_PLAN_09v9s0fh9t3z1c47yptu8 used for this statement

So it looks a very good approach to fix the performance regression issues without changing any sql in application code.


Moving an Oracle SQL execution plan from one environment to another using SQLT

" A query is running very slow in production, faster in development, and never finishing in test.” Have you heard such issues before?

Here we can copy sql execution plan from good environment to problematic environment. There can be multiple ways to accomplish this. Let’s give a shot to SQLT !!

If the query has executed successfully in one environment and table structure is the same across environments, you can create a sql profile and that can be used in your other environments.

Step 1: Find the sql_id and plan hash value from the good environment you want to use to create your profile
#Query to get sql_id and PHV :-
SELECT sql_id, plan_hash_value, SUBSTR(sql_text,1,40) Text
FROM v$sql
WHERE sql_text LIKE '%&An_Identifiable_String%';

Click "How to Determine the SQL_ID of a SQL Statement" for more details.

After getting SQL_ID , you can see the plan from below queries:-
select * from table(dbms_xplan.display_awr('SQL_ID'));  — run this if the sql_id is not in memory
select * from table(dbms_xplan.display_cursor('SQL_ID')); — use this if the sql_id is still in memory

Step 2: Create a SQL profile. SQLT has a great script to help create a sql profile that can be used in your other environments. If you don’t already have sqlt installed, see Oracle Support Doc.

Step 3: Run this script in SQL Plus. It will prompt you for the sql_id and plan hash value.
sql> @/home/oracle/sqlt/utl/coe_xfr_sql_profile.sql
This script will produce a file similar to coe_<sqlid>_planhash.sql.

Step 4: Copy this script "coe_<sqlid>_planhash.sql" to the target environment you want to create the profile in and run it.
sql> @coe_<sqlid>_planhash.sql

Step 5: Run your query again and verify the execution plan is now using the newly created sql profile!
select * from table(dbms_xplan.display_cursor('SQL_ID'));
 ...........
 Note
 – Degree of Parallelism is n because of hint
 – SQL profile coe_<sqlid>_planhash used for this statement

From this example it's very clear that SQLT is very simple approach to move execution plan between databases.


Related Links :-
How to Determine the SQL_ID of a SQL Statement

Saturday, December 16, 2017

How to Determine the SQL_ID of a SQL Statement

Let's fetch details from v$sql for sql_id and PHV:

select sql_id, plan_hash_value, SUBSTR(sql_text,1,40) Text from v$sql where sql_text LIKE '%&Identifiable_String%';

If SQL is not available in v$sql, DBA_HIST_SQLTEXT and DBA_HIST_SQLSTAT can be used:

select t.sql_id,t.sql_text,s.executions_total,s.elapsed_time_total
   from DBA_HIST_SQLSTAT s, DBA_HIST_SQLTEXT t 
  where
   s.snap_id between 200 and 240;   <====use snapid in which the query was ran

If you have a PL/SQL block such as:

declare v1 number;
 begin
  select /* Test */ sum(sal) into v1 from emp;
 end;
 /

Then if you try to find the SQL_ID from v$sql then you will see the SQL_ID of the PL/SQL block NOT the SQL itself:
 
SQL> select sql_id, sql_text from v$sql where sql_text like '%Test%';
 SQL_ID        SQL_TEXT
 ------------- ----------------------------------------------------------------------------------
 55htre9qgwtzm declare v1 number; begin select /* Test */ sum(sal) into v1 from emp; end;

The SQL statement within the PL/SQL block is actually stored separately, but you cannot see it because:
            • every sql statement in a PL/SQL block is stored as capital letters
            • every comment and INTO clause are removed
Note that optimizer hints are preserved.
In other words, select /* Test */ sum(sal) into v1 from emp is stored as SELECT SUM(SAL) FROM EMP

To find it's SQL_ID you would need to search on something similar to the following:
 
SQL> select sql_id, sql_text from v$sql where sql_text like '%SUM(SAL)%EMP%';
 SQL_ID        SQL_TEXT
 -----------------------------------------------------------------------------
 3hhde98jk458a SELECT SUM(SAL) FROM EMP



Cursor Merging

Cursor Merging

If the optimizer creates a plan for a bind-aware cursor, and if this plan is the same as an existing cursor, then the optimizer can perform cursor merging.

In cursor merging database merges cursors to save space in the library cache. The database increases the selectivity range for the cursor to include the selectivity of the new bind value.

When a query uses a new bind variable, the optimizer tries to find a cursor that it thinks is a good fit based on similarity in the selectivity or cardinality of the bind value. If the database cannot find such a cursor, then it creates a new one. If the plan for the new cursor is the same as one of the existing cursors, then the database merges the two cursors to save space in the library cache which involves storing the combined cardinality statistics into one child cursor, and deleting the other one. The merge results in the database marking one cursor as not sharable. If the library cache is under space pressure, then the database ages out the non-sharable cursor first.

For more detailed explanation on when and how optimizer merge cursors, please refer How Adaptive Cursor Sharing Works.

Related Links :-

Bind-Aware Cursors

Bind-Aware Cursors

A bind-aware cursor is a bind-sensitive cursor that is now eligible to use different plans for different bind values. That means when a cursor has been made bind-aware, the optimizer chooses plans for future executions based on the bind value and its cardinality estimate.


When a statement with a bind-sensitive cursor executes, the optimizer uses an internal algorithm to determine whether to mark the cursor bind-aware or keep it as bind-sensitive.  This decision depends on whether the cursor produces significantly different data access patterns - plans for different bind values, resulting in a performance cost that differs from expectations.

If the database marks the cursor bind-aware, then the next time that the cursor executes the database does the following:
•       Generates a new plan based on the bind value.
•       Marks the original cursor generated for the statement as not sharable (V$SQL.IS_SHAREABLE is N). That means original cursor is no longer usable and is eligible to age out of the library cache.

When the same query repeatedly executes with different bind values, the database examines the bind values and considers whether the current bind value results in a significantly different data volume, or whether an existing plan is sufficient. The database does not create a new plan for each new value.

During the initial hard parses, the optimizer is essentially mapping out the relationship between bind values and the appropriate execution plan.
After this initial period, the database eventually reaches a steady state. Executing with a new bind value results in picking the best child cursor
in the cache, without requiring a hard parse. Thus, the number of parses does not scale with the number of different bind values.
 

This example continues the example in "Bind-Sensitive Cursor". The following code again issues query on emp table with the bind variable set to 60:
   
 


EXEC :dept_id := 60;
SELECT COUNT(*), MAX(emp_id) FROM scott.emp WHERE dept_id = :dept_id;
COUNT(*) MAX(EMPLOYEE_ID)
---------------------------------------------------------
100045           100999


During the first two executions, the database was monitoring the behavior of the queries, and determined that the different bind values caused the queries to differ significantly in cardinality. Based on this difference, the database adapts its behavior so that the same plan is not always shared for this query. Thus, the optimizer generates a new plan based on the current bind value, which is 60 :
      

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  a5upref8bn9 , child number 1
------------------------------------
SELECT COUNT(*), MAX(emp_id) FROM scott.emp WHERE dept= :dept_id
Plan hash value: 1756356638
-----------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost(%CPU)| Time   |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |       |       |254 (100)|          |
|   1 |  SORT AGGREGATE    |           |     1 |     8 |         |          |
|*  2 |   TABLE ACCESS FULL| EMP |  100K |  770K |254  (15)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("DEPT_ID"=:DEPT_ID) 


The following query of V$SQL obtains information about the cursor:

   

SELECT SQL_TEXT, CHILD_NUMBER AS CHILD#, EXECUTIONS AS EXEC,
                         BUFFER_GETS AS BUFF_GETS, IS_BIND_SENSITIVE AS BIND_SENS,
                         IS_BIND_AWARE AS BIND_AWARE, IS_SHAREABLE AS SHARABLE
FROM   V$SQL
WHERE  SQL_TEXT LIKE '%mployee%'
AND    SQL_TEXT NOT LIKE '%SQL_TEXT%';

SQL_TEXT             CHILD#  EXEC  BUFF_GETS BIND_SENS BIND_AWARE SHARABLE
----------------------   ------ ----- ---------- --------- ---------- --------
SELECT COUNT(*),         0     2        1649         Y          N        Y
MAX(emp_id) FROM scott.em
p WHERE dept_id = :dept_id

SELECT COUNT(*),         1     1        700         Y          Y        Y
MAX(emp_id) FROM scott.em
p WHERE dept_id = :dept_id
 

The preceding output shows that the database created an additional child cursor (CHILD# of 1). Cursor 0 is now marked as not shareable. Cursor 1 shows a number of buffers gets lower than cursor 0, and is marked both bind-sensitive and bind-aware. A bind-aware cursor may use different plans for different bind values, depending on the selectivity of the predicates containing the bind variable.

Lets execute below query again with dept_id 10 :-
           

VARIABLE dept_id NUMBER
            EXEC :dept_id := 10;

            SELECT COUNT(*), MAX(emp_id) FROM scott.emp WHERE dept_id = :dept_id;
            COUNT(*) MAX(EMP_ID)
---------- ----------------
             1              100
 

Optimizer picked the best plan, which is an index scan, based on the low cardinality estimate for the current bind value of 10:



SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);

PLAN_TABLE_OUTPUT
-------------------------------------

SQL_ID  a5upref8bn9, child number 0
-------------------------------------
SELECT COUNT(*), MAX(emp_id) FROM scott.emp WHERE dept_id = :dept_id;
Plan hash value: 4565465905
-------------------------------------------------------------------------------------
| Id| Operation                           | Name       |Rows|Bytes|Cost (%CPU)|Time |
-------------------------------------------------------------------------------------
| 0| SELECT STATEMENT                     |                   |  |  |2(100)|        |
| 1|  SORT AGGREGATE                      |                   |1 |8 |      |        |
| 2|   TABLE ACCESS BY INDEX ROWID BATCHED| EMP            |1 |8 |2  (0)|00:00:01|
|*3|    INDEX RANGE SCAN                  | EMP_DEPT_IX    |1 |  |1  (0)|00:00:01|
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("DEPT_ID"=:DEPT_ID)
  


The V$SQL output now shows that three child cursors exist:
    


  SELECT SQL_TEXT, CHILD_NUMBER AS CHILD#, EXECUTIONS AS EXEC,
       BUFFER_GETS AS BUFF_GETS, IS_BIND_SENSITIVE AS BIND_SENS,
       IS_BIND_AWARE AS BIND_AWARE, IS_SHAREABLE AS SHARABLE
FROM   V$SQL
WHERE  SQL_TEXT LIKE '%mployee%'
AND    SQL_TEXT NOT LIKE '%SQL_TEXT%';

SQL_TEXT             CHILD#  EXEC  BUFF_GETS BIND_SENS BIND_AWARE SHARABLE
----------------------   ------ ----- ---------- --------- ---------- --------
SELECT COUNT(*),         0     2        1649         Y          N        Y
MAX(emp_id) FROM scott.em
p WHERE dept_id = :dept_id

SELECT COUNT(*),         1     1        700         Y          Y        Y
MAX(emp_id) FROM scott.em
p WHERE dept_id = :dept_id

SELECT COUNT(*),         2     1        3         Y          Y        Y
MAX(emp_id) FROM scott.em
p WHERE dept_id = :dept_id


The database discarded the original cursor (CHILD# of 0) when the cursor switched to bind-aware mode. This is a one-time overhead. The database marked cursor 0 as not shareable (SHAREABLE is N), which means that this cursor is unusable and will be among the first to age out of the cursor cache.

Related Links :-



Tuesday, December 12, 2017

Bind-Sensitive Cursors

Bind-Sensitive Cursors

A bind-sensitive cursor is a cursor whose optimal plan may depend on the value of a bind variable.
The optimizer uses the below criteria to decide whether a cursor is bind-sensitive:
 • The optimizer has peeked at the bind values to generate cardinality estimates.
 • The bind is used in an equality or a range predicate.

For each execution of the query with a new bind value, the database records the execution statistics for the new value and compares them to the execution statistics for the previous value. If execution statistics vary greatly, then the database marks the cursor bind-aware.


Let's assumes that the scott.emp.dept_id column has significant data skew.

Below code adds 100,000 employees in department 60 to the emp table in the scott schema and then gathers table statistics:


 DELETE FROM scott.emp WHERE emp_id > 999;

 ALTER TABLE scott.emp DISABLE NOVALIDATE CONSTRAINT emp_email_uk;

 DECLARE
 v_counter NUMBER(7) := 1000;
 BEGIN
  FOR i IN 1..100000 LOOP   
  INSERT INTO scott.emp
    VALUES (v_counter,null,'abc','abc@test.com',null,'17-JAN-17','A_ACC',null,null,null,60);
  v_counter := v_counter + 1;
  END LOOP;
 END;
 /
 COMMIT;

 EXEC DBMS_STATS.GATHER_TABLE_STATS ( ownname => 'scott', tabname => 'emp');

 ALTER SYSTEM FLUSH SHARED_POOL;


A histogram on the emp.dept_id column:


 SELECT TABLE_NAME, COLUMN_NAME, HISTOGRAM FROM   DBA_TAB_COLS WHERE  OWNER =  'SCOTT' AND    TABLE_NAME = 'EMP' AND    COLUMN_NAME = 'DEPT_ID';

 TABLE_NAME      COLUMN_NAME          HISTOGRAM
 --------------- -------------------- ---------
 EMP       DEPT_ID        FREQUENCY


The following query shows that the value 10 has extremely low cardinality for the column dept_id:


  VARIABLE dept_id NUMBER
  EXEC :dept_id := 10;

  SELECT COUNT(*), MAX(emp_id) FROM scott.emp WHERE dept_id = :dept_id;
  COUNT(*) MAX(EMP_ID)
---------- ----------------
         1              100


The optimizer chooses an index range scan, as expected for such a low-cardinality query:


  SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  a5upref8bn9, child number 0
-------------------------------------
SELECT COUNT(*), MAX(emp_id) FROM scott.emp WHERE dept_id = :dept_id;
Plan hash value: 4565465905
-------------------------------------------------------------------------------------
| Id| Operation                           | Name       |Rows|Bytes|Cost (%CPU)|Time |
-------------------------------------------------------------------------------------
| 0| SELECT STATEMENT                     |                   |  |  |2(100)|        |
| 1|  SORT AGGREGATE                      |                   |1 |8 |      |        |
| 2|   TABLE ACCESS BY INDEX ROWID BATCHED| EMP            |1 |8 |2  (0)|00:00:01|
|*3|    INDEX RANGE SCAN                  | EMP_DEPT_IX    |1 |  |1  (0)|00:00:01|
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("DEPT_ID"=:DEPT_ID)


Let's obtains information about the cursor

  
SELECT SQL_TEXT, CHILD_NUMBER AS CHILD#, EXECUTIONS AS EXEC,
       BUFFER_GETS AS BUFF_GETS, IS_BIND_SENSITIVE AS BIND_SENS,
       IS_BIND_AWARE AS BIND_AWARE, IS_SHAREABLE AS SHARABLE
FROM   V$SQL
WHERE  SQL_TEXT LIKE '%emp%'
AND    SQL_TEXT NOT LIKE '%SQL_TEXT%';

SQL_TEXT                 CHILD#  EXEC  BUFF_GETS BIND_SENS BIND_AWARE SHARABLE
----------------------   ------ ----- ---------- --------- ---------- --------
SELECT COUNT(*),         0     1        169         Y          N        Y
MAX(emp_id) FROM scott.em
p WHERE dept_id = :dept_id


Output shows one child cursor that has been executed once for the low-cardinality query. The cursor has been marked bind-sensitive because the optimizer believes the optimal plan may depend on the value of the bind variable.

When a cursor is marked bind-sensitive, Oracle Database monitors the behavior of the cursor using different bind values, to determine whether a different plan for different bind values is more efficient. The database marked this cursor bind-sensitive because the optimizer used the histogram on the dept_id column to compute the selectivity of the predicate WHERE dept_id = :dept_id.

Presence of the histogram indicates that the column is skewed, different values of the bind variable may require different plans.

The following code re-executes the same query using the value 60, which occupies most of the rows:

  
EXEC :dept_id := 60;
SELECT COUNT(*), MAX(emp_id) FROM scott.emp WHERE dept_id = :dept_id;
  COUNT(*) MAX(EMPLOYEE_ID)
---------- ----------------
    100045           100999


Even though such an unselective query would be more efficient with a full table scan, the optimizer chooses the same index range scan used for dept_id=10. This reason is that the database assumes that the existing plan in the cursor can be shared.


  
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  a5upref8bn9, child number 0
-------------------------------------
select COUNT(*), MAX(emp_id) FROM scott.emp WHERE dept_id = :dept_id
Plan hash value: 4565465905
-------------------------------------------------------------------------------------
| Id| Operation                           | Name       |Rows|Bytes|Cost (%CPU)|Time |
-------------------------------------------------------------------------------------
| 0| SELECT STATEMENT                     |                   |  |  |2(100)|        |
| 1|  SORT AGGREGATE                      |                   |1 |8 |      |        |
| 2|   TABLE ACCESS BY INDEX ROWID BATCHED| EMP            |1 |8 |2  (0)|00:00:01|
|*3|    INDEX RANGE SCAN                  | EMP_DEPT_IX    |1 |  |1  (0)|00:00:01|
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("DEPT_ID"=:DEPT_ID)


A query of V$SQL shows that the child cursor has now been executed twice:



SELECT SQL_TEXT, CHILD_NUMBER AS CHILD#, EXECUTIONS AS EXEC,
       BUFFER_GETS AS BUFF_GETS, IS_BIND_SENSITIVE AS BIND_SENS,
       IS_BIND_AWARE AS BIND_AWARE, IS_SHAREABLE AS SHARABLE
FROM   V$SQL
WHERE  SQL_TEXT LIKE '%mployee%'
AND    SQL_TEXT NOT LIKE '%SQL_TEXT%';

SQL_TEXT             CHILD#  EXEC  BUFF_GETS BIND_SENS BIND_AWARE SHARABLE
----------------------   ------ ----- ---------- --------- ---------- --------
SELECT COUNT(*),         0     2        1649         Y          N        Y
MAX(emp_id) FROM scott.em
p WHERE dept_id = :dept_id


At this stage, the optimizer has not yet marked the cursor as bind-aware.

In next post we'll see when and how optimizer will mark this bind-sensitive cursor to bind-aware.