Saturday, March 28, 2015

BAD SQL on database? No Problem ..!!

Hi Guys,

When a sql query is running beyond the query response time (SLA), Application will notify database admin to check what exactly is going on database, After identifying problematic sql that is running on database, we need to understand how optimizer is generating execution plans and where our query is waiting  on database ? we can gather evidence in different methods, a few mentioned here now 

 SQL query comes from application to database level,
look at different levels

1) Application level
2) Network level
3) Server level
4) Database level

when you are engaged, check if there are any errors in application logs, server logs, network errors and ping application machine. When you confirmed  first 3 is good, we need to identify how problematic query's are running on database level

Initial Investigation

1) Check for blocking session on database?
2) Check for wait events running on database, take AWR report on database look at wait events on database, work on it?
3) check for database alert log file for any errors, i.e database have any space issues, memory issues ?
4) check for any long running jobs running on database, i.e batch jobs ( ask application team (or) check any dbms_scheduler jobs running on database), RMAN backup jobs may be root cause ? 

if you don't find any thing here, we can use different methods to resolve the long running query's or problematic sql's running on databases

Method 1: 

we know that, when query's are fired on database,  oracle process receives request's and load the sql in the shared sql area in sharedpool(SGA).  The information about the query execution is stored in the cursor i.e is opened in shared sql area. with this information, Now we need to find out what is stored in the cursor using v$sql (process information of the sql query is stored in v$sql i.e child cursor information stored in v$sql)

You can find the SQL_ID of a statement from an AWR or ASH report or you can select it from the database using the V$SQL view.

If you are able to identify the SQL with a particular identifiable string or by some kind of unique comment such as: /* TARGET SQL */ then this will make it easier to locate.


SELECT sql_id, hash_value, substr(sql_text,1,40) sql_text FROM v$sql WHERE sql_text like 'SELECT /* TARGET SQL */%'

------------- ----------------------------------------
0xzhrtn5gkpjs SELECT /* TARGET SQL */ * FROM dual

When you find out "sql_id"  run sql tuning advisor from OEM, follow recommendations suggested by OEM

If it is asking you to create sql profile or any other suggestions like gather stats, rebuild index , go ahead ..:)

 Method 2:

When you follow this method, i say you should be expertise in reading execution plan generated by Oracle optimizer, when SQL query's are problematic, we need to understand how the optimizer is behaving with current sql query

To know about it, I request you take the current the explain plan of the sql statement
you can load current explain of the sql statement into the  plan table as follows

explain plan for select ename.dname from me natural join dept;

select * from table(dbms_xplan.display)
This command will displays you the plan that you loaded into the plan table

start reading the explain plan, look at how optimizer is behaving with current sql on database, look at child operations by the optimizer

 Find out the sql id from v$sql and display the cursor what is the information store in library cache, what happened actually can be known from DMS_XPLAN.DISPLAY_CURSOR

1) get output of DBMS_XPLAN.DISPLAY_CURSOR as follows:

SQL> set linesize 150
SQL> set pagesize 2000
SQL> select * from TABLE(dbms_xplan.display_cursor('&SQL_ID', &CHILD));

By this you can read what exactly oracle optimizer is executing the sql query's but you can't get exact information that where your query is exactly waiting on database 

Enable SQL TRACE  to know about where exactly your query is waiting on database !!

... to be continued 

---- Oracle in Memory -------

Thursday, March 26, 2015


How to skip long running transactions in goldengate ?

GGSCI> send extract ENTNEW, showtrans
Sending SHOWTRANS request to EXTRACT ENTNEW ...
Oldest redo log file necessary to restart Extract is:

Redo Log Sequence Number 6, RBA 2239791
XID: 1.8.209025
Items: 0
Extract: ENTNEW
Redo Thread: 1
Start Time: 2015-26-22:00:10:06

SCN: 0.2239791 (2239791)
Redo Seq: 6
Redo RBA: 1470

Status: Running

use following command to skip long running transaction in goldengate 
we use XID to skip transactions 

GGSCI > send extract ENTNEW, skiptrans 1.8.209025

Thank you
Oracle in Memory _______

Create IPS Package: ADRCI

THIS DOCUMENT WILL HELP YOU TO Create a package for incident ...
This will help beginners to RAISE ORACLE SR
[oracle@tnc61 ~]$ . oraenv
ORACLE_SID = [oval007p] ? 
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@tnc61 ~]$ adrci

ADRCI: Release - Production on Thu Mar 26 21:41:27 2015

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

ADR base = "/u01/app/oracle"
adrci> show homepath
ADR Homes: 

adrci> set homepath diag/rdbms/oval007p/oval007p
adrci> show homepath
ADR Homes: 

adrci> show alert -term -p "MESSAGE_TEXT like ‘%ORA%’ and ORIGINATING_TIMESTAMP > '2015-03-23 23:11:10.209000 -04:00’”


adrci> show incident -p "problem_key='ORA 4031'"

ADR Home = /u01/app/oracle/diag/rdbms/oval007p/oval007p:
INCIDENT_ID          PROBLEM_KEY                   CREATE_TIME                              
-------------------- ----------------------------------------------------------- 
29217                ORA 4031                               2015-03-23 23:11:07.208000 -04:00       
29225                ORA 4031                               2015-03-23 23:11:10.209000 -04:00       
29233                ORA 4031                               2015-03-23 23:11:10.524000 -04:00       
29169                ORA 4031                               2015-03-23 23:11:26.496000 -04:00       
29153                ORA 4031                               2015-03-23 23:11:26.694000 -04:00
5 rows fetched


Package the trace files invoking IPS 

adrci> ips pack incident 29217 in /tmp
Generated package 1 in file /tmp/, mode complete

Upload the ZIP file generated to the Service Request you created on the My Oracle Support website . This file will include all the trace files, instance alert file and other diagnostic information for the critical error. Make sure that the ZIP file is uploaded WITHOUT any post processing (such as TAR).

------NIKHIL TATINENI ------
Oracle in Memory _______

Saturday, March 21, 2015


An index organized table keeps its data sorted according to the primary key column values for the table. An Index- organized table stores entire data inthe table was stored in an index 

Index’s serve two main-purposes 
  1. To enforce uniqueness, when a primary key or unique constraint is created, oracle created an index to enforce the uniqueness of the indexed columns 
  2.  To improve performance when a query can use an index, the performance of the query may dramatically improve. 

create table state_lookup_iot (
area_code number(3)
state_name varchar2(100),
city_name varchar2(100),
primary key (area_code)
organization index
tablespace users;


create table customers
trans_id varchar2(10),
country varchar2(10),
year varchar2(10),
constraint trans_id_pk PRIMARY KEY (trans_id)
organization index;

cardinality & Cardinality Feedback !

What is cardinality ?

cardinality when we are talking to database modeling, a table can have one to one relationship or it can have many to many relationship or one to many relationship
& cardinality of table, and also cardinality defines the number of unique values in the table.

what is cardinality Feedback?

Cardinality Feedback is a process whereby the optimizer automatically improve plans for repeatedly executed queries where the optimizer may not have been able to generate a completely accurate cardinality estimate in the plan. The optimizer may miscalculate cardinality estimates for a variety of reasons, such as missing or inaccurate statistics, or complex predicates. Whatever the reason, cardinality feedback may be able to help.

when comes to sql - when a sql query is parsed, optimizer calculates statistics as possible, complex predicates ( columns used in where condition), joins and generates execution plan.  For some reasons  joins used in predicate can cause estimate cardinality is inaccurate

on first execution of sql statement an execution plan is generated by optimizer. during the plan optimization, certain values are noted and the cursor is produced monitored by optimizer. After the execution actual, cardinality is compared with predicted cardinality and then stores for later use. When the query is executed again, as it stored accurate values, predicts accurate values and optimizer generates different execution plan, which is more accurate 

After few executions of query’s, one plan will be picked out and used for all subsequent executions.


default value  is 2 on our oracle databases 

----- Nikhil Tatineni -------

SQL Plan Baselines

SQL Plan Baselines are a new feature in Oracle Database relaease 11g that helps to prevent repeatedly used SQL statements  to generate different execution plans

DBA_SQL_PLAN_BASELINES view tell about what are base lines stores on oracle database 

select sql_handle, sql_text, plan_name, origin, enabled, accepted, fixed, autopurge from dba_sql_plan_baselines;

FOR AUTOMATIC CAPTURE OF SQL BASE LINES, we can enable following parameter on database

when we set this parameter to True, automatic plan capture is enabled on database. SPM repository is populated for every repeated sql on database. To identify repeated sql’s on database, the optimizer will log the repeated sql into the statement log after first time sql statement is compiled. If the same sql is fired again on database, the optimizer identify the sql from statement log and uses the execution plan history of sql statement and reproduce execution plan by using SQL text, outline, bind variables and compilation environment. The current cost of the sql statement is added to SQL BASE LINE and marked as accepted  

If the statistics on the table are changed, again same sql is fired on database, A new baselines is loaded into SPM and this plan marked for verification. 

           |                         YES                                                       YES 
           | if No                                                            |  No
Add entry in sql Log                             CREATE  SQL BASE LINE 
           |                                                                    |
 execute this plan                                  Execution this Plan 

Starting from 11g, baselines are manually loaded for statements directly from the cursor cache into the SQL Management Base. Loading plans directly from the cursor cache can be extremely useful if application SQL has been tuned by hand using hints. Since it is unlikely the application SQL can be changed to include the hint, by capturing the tuned execution plan as a SQL plan baseline you can ensure that the application SQL will use that plan in the future.

we need to identify the sql’s to load plans for statements directly from the cursor cache into the SQL Management Base

when a sql is fired on the database, sql is loaded into the shared sql are in SGA, cursor is opened same time. find out the SQL_ID for the sql statement from view V$SQL; using DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE we will load sql base lines into SQL management base . 

Then find the SQL_ID for the statement in the V$SQL view. 
SQL> SELECT sql_id, sql_fulltext FROM V$SQL WHERE sql_text LIKE '%SELECT prod_name, SUM(%';

SQL_ID               SQL_FULLTEXT
 -------------        ——————————————————— 
74hnd835n81yv  select SQL_ID, SQL_FULLTEXT from v$SQL chj6q8z7ykbyy SELECT PROD_NAME,SUM(AMOUNT_SOLD)

SQL> variable cnt number; 
SQL> EXECUTE :cnt :=DBMS_SPM.LOAD_PLAN_FROM_CURSOR_CACHE( sql_id=>'chj6q8z7ykbyy');

we can enable and disable the plans loaded in SQL Management Base using DBMS_SPM.ALTER_SQL_PLAN_BASELINE 
In-order to disable the plan, we need to get SQL_HANDLE & PLAN_NAME from dba_sql_plan_baselines; 

SQL> variable cnt number;
 SQL_HANDLE => 'SYS_SQL_bf5c9b08f72bde3e’, 
 PLAN_NAME => 'SQL_PLAN_byr4v13vkrrjy42949306’,
 ATTRIBUTE_NAME => 'enabled’,

SQL> SELECT sql_handle, sql_text, plan_name, enabled FROM dba_sql_plan_baselines; 

To check the detailed execution plan for any SQL plan baseline you can use the procedure DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE.

select * from table (dbms_plan.display_sql_plan_baseline(
sql_handle =>'SYS_SQL_bf5c9b08f72bde3e’,
plan_name =>'SQL_PLAN_byr4v13vkrrjy42949306’,
format => ‘basic’));

The SQL management base (SMB) is a part of the data dictionary that resides in the SYSAUX tablespace. It stores statement logs, plan histories, SQL plan baselines, and SQL profiles. To allow weekly purging of unused plans and logs, the SMB uses automatic space management.

what is the percentage of space allocated in sysaux tablespace for SQL BASELINES OR SQL profiles and how long they are stored can be know known from "DBA_SQL_MANAGEMENT_CONFIG" view on database;

columns parameter_name
columns parameter_value 

we can configure plan_retention_weeks & space_budget_percent  to manage SQL plan base lines or profiles in sysaux tablespace 

1) Disk Space Usage:-

Disk space used by the SMB is regularly checked against a limit based on the size of the SYSAUX tablespace. By default, the limit for the SMB is no more than 10% of the size of SYSAUX. The allowable range for this limit is between 1% and 50%.
A weekly background process measures the total space occupied by the SMB. When the defined limit is exceeded, the process writes a warning to the alert log. The database generates alerts weekly until one of the following conditions is met:
A weekly background process measures the total space occupied by the SMB. When the defined limit is exceeded, the process writes a warning to the alert log. The database generates alerts weekly until one of the following conditions is met:
  • The SMB space limit is increased
  • The size of the SYSAUX tablespace is increased
  • The disk space used by the SMB is decreased by purging SQL management objects (SQL plan baselines or SQL profiles)
  • To change the percentage limit, use the CONFIGURE procedure of the DBMS_SPM package. The following example changes the space limit to 30%:
  • SQL> DBMS_SPM.CONFIGURE(‘space_budget_percent’,30);
2) Purge Policy :-

A weekly scheduled purging task manages the disk space used by SQL plan management. The task runs as an automated task in the maintenance window.

The database purges plans not used for more than 53 weeks, as identified by the LAST_EXECUTED timestamp stored in the SMB for that plan. The 53-week period ensures plan information is available during any yearly SQL processing. The unused plan retention period can range between 5 and 523 weeks (a little more than 10 years).

To configure the retention period, use the CONFIGURE procedure of the DBMS_SPM PL/SQL package. The following example changes the retention period to 105 weeks:

DBMS_SPM.CONFIGURE( ‘plan_retention_weeks’,105); 

---- Nikhil Tatineni -----