Showing results for 
Search instead for 
Did you mean: 
Create Post

SQL Query Tuning Tips for Oracle - December 13, 2012

Below are answers to questions asked during the recent webinar. If you have additional questions, please contact Dean Richards at

Q: I assume your focus in this session is on 11g
A: No, the ideas I presented will work on Oracle 7.3 and higher. For obtaining plans, v$sql_plan exists in 10g and higher.

Q: Besides SQL Developer, AWR, ADDM Reports, Grid Control, which other tools or approach should we use to get Faster and Better Findings inside a Low Performance Scenario?
A: There are many tools available including Ignite for Oracle which is, by design, the toll that can get to the root of a problem quickly.

Q: What is event Cursor S Pin X ??
A: The "cursor" waits mean a session/SQL is waiting to parse something. If you see a lot of waits on this event, you may be running into Oracle bug 5184776. This wait specifically means a session is trying to obtain a mutex in shared mode but some other session is holding it in exclusive mode. Typically this wait is a symptom of another issues such as 1) a lot of hard parsing; 2) many versions of SQL statements; 3) bugs as suggested above. For solutions, this Oracle support article should help find the blocker session: Note:786507.1 How to Determine the Blocking Session for Event: ‘cursor: pin S wait on X’

Q: What is Plan_Value and Child number? How is explain plan is related to plan_value etc?
A: Plan_Hash_Value is a unique identifer for a plan much like sql_hash_value is for a SQL statement. The child number value ties to a SQL statement. Explain Plan is similar to a plan in V$SQL_PLAN but is for right now and based on whatever application you ran it from, e.g. SQL*Plus. Your application probably does not run from SQL*Plus so there is a difference in environments that could cause a difference in plans. Also, since explain plan is for right now, are you sure that's the same plab used last night at 3am when there was a performance issue. Maybe not, so why not go to a more accurate place (V$SQL_PLAN and DBMS_XPLAN) to get the data.

Q: If we enable the TRace on the Program, then that will give us the correct Explain Plan - Correct ?
A: Yes, if you configure the trace to collect plans, then yes.

Q: What is relationship between sql_id and plan_value?
A: SQL_ID is a unique identifier for a SQL statement while a plan_hash_value is a unique identified for a plan.

Q: Why would different apps generate different execution plans?
A: Because they might have different environment settings that could influence the plan.

Q: Since this is using FTS, shouldn't we be concentrating on physical reads rather than logical reads?
A: The first time you run a SQL statement. It might do 500 physical reads and 600 logical reads. You could then run it 5 seconds later and, because of data caching, might get 50 physical reads and 600 logical reads. Is performance better, probably, but only because data was cached. Logical reads give a much better indication of the work a query needs to do while physical reads depend on the state of your buffer cache, and nothing more.

Q: Shouldn't we consider proportion of DMLs on a table before adding index to tune a sql statement?
A: Yes, definitely. If you know a table has a high amount of DML against it, an index could hinder performance more than it helps the SELECT statement.

Q: What should I look at instead of cost? I presume the time waited?
A: Wait time is what I use because costs can be incorrect based on bad stats. Wait time per object will tell you exactly which part of the plan is taking the longest.

Q: What if a query is flipping plans and even after creating an SQL profile it doesnt pick up the fixed plan. What could be the reaons for this and what should be done in such a case?
A: A query that keeps switching plans is highly unusual. That means the query is getting aged out of the shared pool and then picking a new plan the next time it's parsed. I would check into the values being passed in and see if they you are dealing with data skew issues. Bad stats could be another cause so check that as well.

Q: If in case Logical reads are high, indexes are in place and stats are recently gathered.What could be the approach to tune such SQL?
A: If you are saying the query is tuned as well as it can be, then it is what it is. See if it can be executed less often to reduce the times and resource consumption. Can a materialized view be used to reduce the logical reads for this query. You might be stuck if, for example, you have a query that just has to read a lot of data to get its work done. Parallelizing the query might also be an option too so it executes in less wall time.

Q: Does SQLDeveloper (free product ) from Oracle gives the True Execution Plan - isn't that a reliable Plan?
A: No, it does an explain plan so hence it can be wrong.

Q: In our environment sometimes the stats are stale and locked over tables. What if we cannot gather latest stats and the query is performing slow?
A: You could use hints or profiles to help tell Oracle the best way to execute a query. If stats are locked, someone locked them so present your case why the stats are outdated and need to be refreshed.

Q: Does Confio use any Tuning pack tables creating any licencing concerns?
A: No, Confio Ignite collects its own data from the X$ tables that sit underneath V$SESSION, V$SQL, etc.

Q: Why would we have more than one version for a query?
A: There are many reasons why you could have mutiple versions of query. One reason I see often is bind variable mismatch, but there are many others. The view named v$sql_shared_cursor will help you understand the reasons.

Q: Is using hints a better way of making optimiser run?
A: I prefer to not use hints if at all possible because you might override something in the optimizer that makes a lot of sense at times.

Q: Is there any performance hit with ignite sampling every second
A: Ignite causes less then 1% overhead.

Q: How do I make a query force using a particular plan?
A: See Oracle documentation obout SQL Plan Management, baselines, profiles, etc.

Labels (1)
Version history
Revision #:
1 of 1
Last update:
‎01-08-2015 09:35 AM
Updated by: