Oracle World: 2016

Monday, July 4, 2016

Are we getting different plan by using bind variable for an SQL_ID?

I wonder why are we getting different plan for an SQL_ID while using bind variable. I have verified first plan is going for a full table scan and second plan is going for an index read instead of Full table scan.

Did Optimizer played a bad game again? No... Please do not blame optimizer every time J. There is a chance of skewed distribution of values in predicate column .

If Index histogram of the predicate column has an accurate information about the skewed distribution, we will get different plan sometime with respect to the predicate which we given.

How it will work ?

Adaptive Cursor is playing a major role for this scenario.

This feature will decide , if plan has to be recalculated when the bind variable values changes. Who is helping to make a good plan with respect to the bind variable value?

of course, it is Index Histogram , we should have accurate index histogram details otherwise they cannot make a decision for recalculation(Wrong Estimation of Cardinality) and again it will end up with bad performance

by using old cached execution plan.

Let me give a quick and simple test.

We have a table called JOB_MASTER and which is having two column named JOB_ID, JOB_CODE

SQL> DESC JOB_MASTER;

Name Null? Type

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

JOB_ID NUMBER(10)

JOB_CODE VARCHAR2(10)

And there is also a non-unique index in the JOB_CODE column.

Please see the query passing from application side against this table.

SQL_ID: cyt7c564y3nf6

SQL_TEXT :

select max(JOB_ID) from JOB_MASTER where JOB_CODE=:B1;

I checked AWR history of this query and found we are getting different plan for this particular SQL for most of the time.

PLAN_HASH_VALUE SQL_ID CHILD_NUMBER

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

4293876030 cyt7c564y3nf6 0

1405390124 cyt7c564y3nf6 1

Plan with Table Scan :

Execution Plan

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

Plan hash value: 1405390124

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

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

| 0 | SELECT STATEMENT | | 1 | 7 | 3923 (3)| 00:00:48

| 1 | SORT AGGREGATE | | 1 | 7 | |

|* 2 | TABLE ACCESS FULL| JOB_MASTER | 4196K| 28M| 3923 (3)| 00:00:48 =====> Full Table Scan

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

3 - access("JOB_CODE"='DBA')

Second Plan with Index Read:

Execution Plan

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

Plan hash value: 4293876030

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

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

| 0 | SELECT STATEMENT | | 1 | 7 | 16 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 7 | | |

| 2 | TABLE ACCESS BY INDEX ROWID| JOB_MASTER | 2311 | 16177 | 16 (0)| 00:00:01 |

|* 3 | INDEX RANGE SCAN | TEST_INDX | 2383 | | 7 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

3 - access("JOB_CODE"='DEVELOPER')

Why do we get this different plan, is this column having Skewed distribution of values?

Let me have a look,

SQL> select count(*),JOB_CODE from JOB_MASTER group by JOB_CODE;

COUNT(*) JOB_CODE

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

8388608 DBA

4608 DEVELOPER

Then we found its highly skewed values in the column JOB_CODE and we can see very less percentage of rows having "DEVELOPER" compared to "DBA" value in this column

There is a catch, It’s a thumb rule oracle following , Oracle will decide to go with full table scan when the bind variable (:B1) is "DBA" and will decide to go with index read

when bind variable (:B1) is "DEVELOPER"

It is really costly when oracle use full table scan while access predicate is "DEVELOPER" and on the other hand , when the access predicate is "DBA", index read is useless.

If the plan need to be recalculated by this way, cursor denoted as Bind Sensitive.

SQL> select plan_hash_value,sql_id,child_number,IS_BIND_SENSITIVE from gv$sql where sql_id='cyt7c564y3nf6';

PLAN_HASH_VALUE SQL_ID CHILD_NUMBER I

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

4293876030 cyt7c564y3nf6 0 Y

My request is , please do not scare, if we get multiple plan while using bind variable until we get a performance degradation over there. Please make sure to test SQL Profile couple of time to verify the performance improvement before getting to default category(Live).

Thanks,

Jyothish B

Sunday, March 20, 2016

Fixing Archive Gap in Standby From Archive Log Backup

Check the archive log gap in standby

SQL> select * from v$archive_gap;

Suppose you have archive gap from 2940 to 2954. We are going to restore Archive files from Backup in the primary.

RMAN> run
2> {
3> restore archivelog from logseq 2940 until logseq 2954;
4> }

This will restore the archive logs from Backup to Disk.

Note : If you are using SBT for taking Backups, Please add appropriate details in the script, Example is referring Disk Backups.

Once the archive restore completed, you can use below command in standby.

SQL>RECOVER AUTOMATIC STANDBY DATABASE ;

# This will automatically recover your standby using archive files which is created by the restore.

SQL PLAN Fixing Using SQLT

Tune Bad SQL Using SQLT

We are getting a good plan and bad plan for a sql. In production we have bad plan and getting very bad result so we are planning to export good plan from non production to production.
SQL history of SQL ID : 7yq9pgtsundq0
col begin_time for a25
col end_time for a11
col inst for 99999
col snapid for 999999
set lines 200
set pages 20000
select snap_id snapid,
(select substr(BEGIN_INTERVAL_TIME,1,18)||' '||substr(BEGIN_INTERVAL_TIME,24,2) from dba_hist_snapshot
b where b.snap_id=a.snap_id and a.INSTANCE_NUMBER=b.INSTANCE_NUMBER) begin_time
,(select substr(end_INTERVAL_TIME,11,8)||' '||substr(end_INTERVAL_TIME,24,2) from dba_hist_snapshot b
where b.snap_id=a.snap_id and a.INSTANCE_NUMBER=b.INSTANCE_NUMBER) end_time
,INSTANCE_NUMBER inst , PLAN_HASH_VALUE,
EXECUTIONS_DELTA Executions,
ROWS_PROCESSED_DELTA rows1,
round( CPU_TIME_DELTA /1000000,0) cpu_time,round(IOWAIT_DELTA /1000000,0) io_wait,
round( ELAPSED_TIME_DELTA /1000000,0) elapsed
from sys.wrh$_sqlstat a where sql_id in('7yq9pgtsundq0')
order by snap_id, INSTANCE_NUMBER;

Above query will help you to get a good plan and bad plan from the history with respect to the SNAP_ID

Example

SNAPID BEGIN_TIME END_TIME INST PLAN_HASH_VALUE EXECUTIONS ROWS1 CPU_TIME IO_WAIT ELAPSED

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

65849 16-FEB-16 09.00.07 AM 10.00.27 AM 1 857637189 9513 9512 2501 2 2539 =====

65850 16-FEB-16 10.00.27 AM 11.00.48 AM 1 857637189 13378 13378 3469 0 3519

65851 16-FEB-16 11.00.48 AM 12.00.08 PM 1 857637189 2568 2569 649 0 660

65855 16-FEB-16 03.00.10 PM 04.00.31 PM 1 857637189 3986 3985 1010 0 1023

65856 16-FEB-16 04.00.31 PM 05.00.51 PM 1 857637189 6042 6043 1520 0 1539 BAD PLAN

65875 17-FEB-16 10.58.22 AM 11.15.53 AM 1 1673898079 11271 11271 928 4 947

65876 17-FEB-16 11.15.53 AM 11.30.58 AM 1 1673898079 10955 10955 883 0 896

65877 17-FEB-16 11.30.58 AM 11.45.03 AM 1 1673898079 10207 10207 824 0 836

65878 17-FEB-16 11.45.03 AM 12.00.08 PM 1 1673898079 11115 11115 882 0 896

65879 17-FEB-16 12.00.08 PM 12.06.49 PM 1 1673898079 4029 4030 318 0 321 ======

65880 17-FEB-16 12.06.49 PM 12.15.13 PM 1 1749449121 349619 349619 12 0 12 ======

65881 17-FEB-16 12.15.13 PM 12.30.18 PM 1 1749449121 997986 997986 34 0 34

65885 17-FEB-16 01.15.34 PM 01.30.39 PM 1 1749449121 690501 690501 23 0 25 Good Plan

65886 17-FEB-16 01.30.39 PM 01.45.48 PM 1 1749449121 657170 657170 22 0 22

65894 17-FEB-16 03.30.28 PM 05.00.58 PM 1 1749449121 1347708 1347708 44 0 46

65901 17-FEB-16 11.00.03 PM 12.00.26 AM 1 1749449121 1347765 1347765 44 0 44 ======

From the above SQL history we identified “1749449121” is a Good plan and “1673898079” is a bad plan.

Look at the CPU Time and Elapsed Time for bad plan, should be very high percentage CPU utilization hence very high elapsed time.

So we are planning to pin the plan using sql profile.

1) Download SQLT from below link.

https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=189245731844662&id=215187.1&_afrWindowMode=0&_adf.ctrlstate=

1dezgs4aky_4

2) Install SQLT as per the document.

3) Once it is installed, please follow below step to create SQL Profile.

SQL>@coe_xfr_sql_profile.sql 68gtz2b5hby5m 4270379085

It will create sql profile script in sqlt/utl coe_xfr_sql_profile_7yq9pgtsundq0_1749449121.sql

4) If you are using coe_gen_sql_profile.sql instead of coe_xfr_sql_profile.sql , you will get all possible sql profile from history and it will

includes good and bad execution plan with this profiles.

5) Once the SQL Profile script created you can able to create sql profile using the above script and check whether we have good result or bad after applying SQL profile using query mentioned earlier.

Now you have two choice, you can export the profile from non production or you can directly create the sql profile using sql which is created by sqlt.

Exporting SQL Profile

1) Create Staging table which will hold our SQL Profile.

BEGIN

DBMS_SQLTUNE.create_stgtab_sqlprof(

table_name => 'SQL_PROF_29_02_2016',

schema_name => 'JYOTHISH' );

END;

2) Packing SQL Profile content to the staging Table.

BEGIN

DBMS_SQLTUNE.pack_stgtab_sqlprof(

profile_name => 'SQL_PROF_7yq9pgtsundq0',

staging_table_name => 'SQL_PROF_29_02_2016',

staging_schema_owner => 'JYOTHISH' );

END;

3) Export Staging Table using Data pump

4) Import dump file in production.

5) Unpack SQL Profile from Staging Table.

SQL>conn JYOTHISH/xxxxxx

BEGIN

DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(

replace => TRUE,

staging_table_name => 'SQL_PROF_29_02_2016');

END;

6) Validate newly created sql profile in production.

SQL> select name,status from dba_sql_profiles where profile_name like ‘SQL_PROF_7%’;

7) We need to flush shared pool or need to wait for the Database bounce for getting new plan from SQL Profile.

Check with Application team and select a convenient way for this step.

SQL> Alter system flush shared_pool;

If you would like to Extract Hints from a PLAN. Please see the below example.

Extracting Hint from Plan

Suppose if you don’t have licence to use the Database Tuning Pack license, Please use below query to extract hints and adding these hints in SQL

query will help you improve the SQL.

select

extractvalue(value(d), '/hint') as outline_hints

from

xmltable('/*/outline_data/hint'

passing (

select

xmltype(other_xml) as xmlval

from

dba_hist_sql_plan

where

sql_id = '7yq9pgtsundq0'

and plan_hash_value = 1749449121

and other_xml is not null

)

) d

Webpage Reference : https://carlos-sierra.net/2012/04/03/what-is-sqltxplain/

RMAN Backup Status Script

We can see the RMAN Backup Status using below query.

col curtime for a11
set lines 200
select substr(systimestamp,11,8) curtime,
to_char(START_TIME, 'DD hh24:mi') START_TIME,
to_char(END_TIME, 'DD hh24:mi') END_TIME,
round(MBYTES_PROCESSED/1024,6) Processed,
round(INPUT_BYTES/1024/1024/1024,6) input_GB,
round(OUTPUT_BYTES/1024/1024/1024,6) output_GB
from v$rman_status where status='RUNNING';

Thanks,

Jyothish B