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.
Example:
SELECT sql_id, hash_value, substr(sql_text,1,40) sql_text FROM v$sql WHERE sql_text like 'SELECT /* TARGET SQL */%'
SQL_ID SQL_TEXT
------------- ----------------------------------------
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
next
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
next
Enable SQL TRACE to know about where exactly your query is waiting on database !!
... to be continued
---- NIKHIL TATINENI
---- Oracle in Memory -------
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.
Example:
SELECT sql_id, hash_value, substr(sql_text,1,40) sql_text FROM v$sql WHERE sql_text like 'SELECT /* TARGET SQL */%'
SQL_ID SQL_TEXT
------------- ----------------------------------------
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
next
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
next
Enable SQL TRACE to know about where exactly your query is waiting on database !!
... to be continued
---- NIKHIL TATINENI
---- Oracle in Memory -------
