Home » RDBMS Server » Performance Tuning » Tuning a SQL query (Oracle Database 10g Release 2)  () 1 Vote
Tuning a SQL query [message #505319] Mon, 02 May 2011 07:01 Go to next message
guptasum
Messages: 14
Registered: March 2011
Junior Member
Hello,

I'm trying to execute the below query, however, it is not getting executed as one of the joined tables is undergoing 'Full Tabe scan'.
Request you to provide me with the possible solution in reducing cost of the query and ultimately getting it executed.

   SELECT papf.per_information2                  Payroll_Tax_State
          ,haou.name                             Legal_Employer
          ,COUNT(DISTINCT paaf.assignment_id)    Emp_Count
    FROM per_all_assignments_f        paaf
         ,per_all_people_f            papf
         ,hr_soft_coding_keyflex      hsck
         ,hr_all_organization_units   haou
  WHERE paaf.soft_coding_keyflex_id  = hsck.soft_coding_keyflex_id
    AND hsck.segment1                = haou.organization_id -- This join is causing FT scan as 'segment1' column does not have any index defined over it
    AND haou.organization_id         IN (SELECT DISTINCT haou.organization_id
                                           FROM per_all_assignments_f       paaf
                                                ,hr_soft_coding_keyflex     hsck
                                                ,hr_all_organization_units  haou
                                          WHERE paaf.soft_coding_keyflex_id  = hsck.soft_coding_keyflex_id
                                            AND hsck.segment1                = haou.organization_id
                                            AND paaf.payroll_id              IN (61, 62)
                                            AND TRUNC(SYSDATE) BETWEEN paaf.effective_start_date AND paaf.effective_end_date
                                        )
    AND paaf.person_id               = papf.person_id
    AND papf.per_information2        IN (SELECT DISTINCT per_information2 
                                           FROM per_all_people_f 
                                          WHERE TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date
                                            AND per_information2 IS NOT NULL
                                        )
    AND paaf.primary_flag            = 'Y'
    AND papf.effective_start_date    BETWEEN '01-JAN-2010' AND '31-DEC-2010'
    AND papf.effective_end_date      BETWEEN '01-JAN-2010' AND '31-DEC-2010'
    AND paaf.effective_start_date    BETWEEN '01-JAN-2010' AND '31-DEC-2010'
    AND paaf.effective_end_date      BETWEEN '01-JAN-2010' AND '31-DEC-2010'
  GROUP BY papf.per_information2, haou.name;


Kindly help me in resolving this issue.

Thanks!
Re: Tuning a SQL query [message #505323 is a reply to message #505319] Mon, 02 May 2011 08:02 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Since NOBODY can optimize SQL just by looking at it, we need a few more details.
http://www.orafaq.com/forum/m/433888/136107/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof
Re: Tuning a SQL query [message #505324 is a reply to message #505319] Mon, 02 May 2011 08:07 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
guptasum wrote on Mon, 02 May 2011 08:01
    AND papf.effective_start_date    BETWEEN '01-JAN-2010' AND '31-DEC-2010'
    AND papf.effective_end_date      BETWEEN '01-JAN-2010' AND '31-DEC-2010'
    AND paaf.effective_start_date    BETWEEN '01-JAN-2010' AND '31-DEC-2010'
    AND paaf.effective_end_date      BETWEEN '01-JAN-2010' AND '31-DEC-2010'
  GROUP BY papf.per_information2, haou.name;



The string '21-JAN-2009' falls between those strings. Is that what you want?
Re: Tuning a SQL query [message #505512 is a reply to message #505319] Tue, 03 May 2011 07:41 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
Basics of Tuning an Oracle Database

1) have you used proper data types for your data?  Is a date storead as a DATE or a VARCHAR2 or a NUMBER?

2) have you defined NOT NULL where an attribute is in fact required?

3) have you defined constraints (PK/UK/FK).  You can in 11g use RELY constraints if you don't want the overhead of actually enforcing them.

4) are your foreign keys supported by an index?  Remember, indexes with the right leading columns are good enough for any constraint these days so your indexes no longer have to exactly match your constraints.

5) have you collected statistics on your tables and columns and indexes etc.?  Consider using SKEWONLY option and collecting histograms when faced with difficult situations.  Google it if you don't know what SKEWONLY is.

6) have you turned on CPU costing?  Test it first if you have not, it can change things.


An optimizer works only as well as the information you provide it is good. If you give the optimizer the information it needs, it will do a really good job for you. Just taking care of the six items above will ensure that 99 out of 100 plans will be very good plans.

DATATYPE = information
NOT NULL = information
CONSTRAINT = information
INDEX = information
STATISTICS = information
SYSTEM INFO = information


As you can see, 99% of tuning is getting the basics right. No need for parallel query, no need for partitioning, no need for funky storage schemes like IOT or CLUSTERING etc. These all come later and are for special situations. They require some amount of planning and most people don't care to plan these days.

If after doing this stuff you still have a plan you don't like then you need to determine if there is in fact a better plan you should be looking for. For this you can start with two things:

1) you can use a TOW diagram (see his book on sql tuning in amazon (DAN TOW)).  This will help you to understand the most likely correct ordering of tables for getting data.

2) you can take the "AMOUNT OF DATA MOVEMENT" approach.  This method is full of so many holes you can drive a bus through it.  And yet, it seems to work really well just the same.  However, it would take some time to explain.


I present them above so you can have a part 2 to your tuning exercise after you fix things for part 1 as described initially. Once you have part 2 giving you some kind of approximate runtime you can test towards, you can move on to part 3, changing things:

1) check your query plan to make sure you are getting the right cardinality estimates particularly for the intial steps in the plan.  Cardinalities should be close for simple queries, and within 5x of the right number for complex situtations (multi-column indexes).  If you see cardinalities that are orders of magnitude off, then you have some problem that needs addressing (usually stats are off).

2) check the query plan to see if you have the right driving table.  Where you start is key.  The TOW diagram can assist with this.

3) check the query plan for table ordering.  Does it follow the order predicted by the TOW diagram reasonably well?  It does not have to match exactly, but it should make sense just the same.

4) check the query plan join methods.  Are you doing NESTED LOOP when joining millions of rows?  Why?  Should you be using HASH JOIN instsead?

5) check the query plan filtering and access predicate actions in the query plan.  Do they make sense to you.  If not figure out why either you are wrong, or the optimizer is wrong.

6) consider if there are situations where new indexes or enhancing existing indexes will help.

7) consider rewrites of the query.  This can get involved, especially of the query is calling PLSQL and you want to convert the PLSQL calls to some equivelant inline SQL.  But this usually results in the most gain.

8) have you checked database parameter settings for reasonableness.  Maybe your optimizer mode is CHOOSE or ALL_ROWS but you are working some kind of OLTP system and need a flavor of FIRST_ROWS_N.  Maybe your PGA_AGGREAGATE_TARGET (or whatever they call it these days) is not big enough for proper sorting/hash joins).


If you get past this stuff and you are still having issues, maybe you need to pull in some consulting from Oracle Corp. or other areas known for their performance tuning expertise.

Good luck, Kevin

Please find attached here the promotional chapter #1 of my new book on SQL TUNING. This chapter (#1) talks about the FILTERED ROWS PERCENTAGE method of Cardinality Based Tuning, and is the full chapter. Do not forget to download the free scripts as well, and if you choose to buy the book make sure to use the coupon code so you get 50% off list price at the noted web address.

[Updated on: Wed, 03 December 2014 13:28]

Report message to a moderator

Re: Tuning a SQL query [message #506408 is a reply to message #505319] Mon, 09 May 2011 02:21 Go to previous messageGo to next message
Yasir Hashmi
Messages: 304
Registered: April 2006
Senior Member
Very nicely summarised the essentials of PT Kevin!!
I request moderators to put it into PT wiki.
Re: Tuning a SQL query [message #506500 is a reply to message #506408] Mon, 09 May 2011 08:46 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>I request moderators to put it into PT wiki.
I added new post to Sticky thread at top of Performance sub-Forum to Kevin's post.
Thanks for the recommendation.
Re: Tuning a SQL query [message #506663 is a reply to message #505319] Tue, 10 May 2011 07:22 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
OK, so I'll go look at it and make it better if that is OK. I won't make it a lot longer but I will try to add a little more structure to it now that my name is in lights.

Thanks for thinking enough of it to post it. Kevin
Re: Tuning a SQL query [message #559164 is a reply to message #506663] Fri, 29 June 2012 06:25 Go to previous messageGo to next message
Sandeep S
Messages: 2
Registered: June 2012
Location: India
Junior Member

Hi Kevin,

Basics need to be in place indeed.. Nicely articulated.. Thanks!!
Whats your thought about change in execution plan/time before/after using bind variables?
I don't see any drawbacks with bind variables though haven't seen much of usage of it around & that too in real time application systems..
Can there by any particular reason?
Re: Tuning a SQL query [message #559189 is a reply to message #559164] Fri, 29 June 2012 09:45 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
Bind Variables is a big question.

1) the behavior of Oracle and Bind Variables is different for different releases.

2) there are some issues related to Bind Variables and the different behaviors are related to fixing problems related to these issues:

a) bind variables are absolutely essential for performance and concurrency for OLTP systems. This is because of the concept of PARSE HEAVY vs. PARSE LIGHT queries. A query that takes longer to parse than to execute is parse heavy. The problem with parse heavy queries is that you spend more time in overhead operations than you spend actually doing work. It means that there is a clear limit to the number of queries you can parse and execute in a given period of time and thus a limit to the number of concurrent users that can be on a system. Bind variables fix this problem for OLTP systems. Such systems usually execute a large number of queries with just changes in constant values in the WHERE clause. Using bind variables to replace these constant values creates a reusable query and opens the door to removing most of the overhead of working with all these queries.

b) but bind variables also remove the constant value which means it is no longer possible to know based on statistics how many rows will be filtered out by the constant value.

c) so to combat the problem of (b), Oracle introduced bind variable peeking. The first time a statement runs, Oracle looks at the value and uses the statistics related to that value to build a plan for the query and that plan is used for all subsequent executions of the query. But this introduce a new problem. The different values of a bind variable sometimes optimize with different plans. Take Male/Female in a table where 99% of all Rows are for Male. WHERE GENDER = 'M' should do a table scan, WHERE GENDER = 'F' should use an index. WHERE GENDER = :V1 ??? If the first time a query using this WHERE clause executes uses 'F' then the index based plan will be used. This plan will then be used every time the same query is seen. This eliminates most of the overhead with the query for the second and subsequent executions which is great for an OLTP system since these generally use the same querys over and over. This is good as long as V1 = 'F' but when the value changes to 'M', the plan is wicked suboptimal compared to a table scan. So to combat this issue Oracle has recently introduce QUERY FEEDBACK.

d) now Oracle takes a look at the plan and run times of queries and does some kind of magic to figure out if a plan should change, given new bind variable values. I do not know the details.

In any event, as you can see as Oracle versions have come and gone, the behavior of using BIND VARIABLES has been enhanced to fix the issues related to them. But make no mistake, they are absolutely necessary for OLTP systems.

If you are experiencing plan changes, then you need to explain a little about what you are doing for us to help determine why plans are changing:

You need to research your version of Oracle to see how it treats bind variables;

1) no bind variables
2) bind variables using default statistics
3) bind variables with peeking
4) bind variables with peeking and feedback

Then you can guess as to why plan might have changed w/respect to bind variables.

Remember too that your plan changes may be due to statistics changes or database design changes (new indexes etc.). Rule these out before you go with bind variables causality.

Here is the most common performance issue related to each bind variable scenario

1) no bind variables. # of concurrent users in on-line system is low because of massive parsing and associated latching related to flooding of the cursor cache from all SQL ONE-OFFS. Fix is to use bind variables.

2) bind variables (no peeking). Your OLTP system can now support large numbers of concurrent users. But... some (or many) queries that used to have constants and fast, now use bind variables and run slow. Cause: oracle has started to use default stats when doing plan generation since it does not know the value of the bind variables at execution time. Thus plans will have changed from before. Fix... no easy fix. Force the plan you want most of the time with hints. Hopefully you are beyond the versions of oracle that do not have bind variable peeking.

3) bind variables with peeking. Oracle is allowed to look at the value of bind variables the first time it sees a query. Now plans have a better idea of what statistics are relevent to the query and generate plans accordingly. But... when values for bind variables change you may again have slow queries. Solution: again ain't no easy solution for this. You have to live with it I think.

4) bind variables with peeking and feedback. Oracle is allowed to look at bind variable values on each execution and make a quick guess as to if the values are different enough to warrant generating a new plan from the current plan. This in theory should solve the problem of some queries going south because their bind variable values are not appropriate for a plan generated using different values as was the case before feedback was introduced. There should be nothing for you to do here.

Good luck, Kevin
Re: Tuning a SQL query [message #559343 is a reply to message #559189] Mon, 02 July 2012 12:24 Go to previous messageGo to next message
Flyby
Messages: 188
Registered: March 2011
Location: Belgium
Senior Member
Oracle 11 has "adaptive cursor sharing" for bind-peeking described above (up to 14 bind parameters). It is also possible to disable it with the underscore _optim_peek_user_binds=false parameter.
Regarding BI-statistics
Re: Tuning a SQL query [message #559408 is a reply to message #559343] Tue, 03 July 2012 03:33 Go to previous messageGo to next message
LNossov
Messages: 318
Registered: July 2011
Location: Germany
Senior Member
With "_optim_peek_user_binds"=false you disable not only advanced cursor sharing but also the bind peeking. To disable advanced cursor sharing you have to set "_optimizer_extended_cursor_sharing_rel"= NONE (and "_optimizer_extended_cursor_sharing"=NONE, if you use user-defined operators).
Re: Tuning a SQL query [message #587449 is a reply to message #559164] Fri, 14 June 2013 16:59 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
Here is something you might try if you want the 10g plan.

19.1.2.2 Hints for Enabling Optimizer Features
The OPTIMIZER_FEATURES_ENABLE hint acts as an umbrella parameter for enabling a series of optimizer features based on an Oracle Database release number. This hint is a useful way to check for plan regressions after database upgrades.

Specify the release number as an argument to the hint. The following example runs a query with the optimizer features from Oracle Database 11g Release 1 (11.1.0.6):

SELECT /*+ optimizer_features_enable(11.1.0.6') */ employee_id, last_name
FROM    employees
ORDER BY employee_id;


Use 10.2.0.3 to downgrade the query to using 10g features and thus get 10g plans. Works pretty good most of the time. Of course doing this as part of a production system is the quintessential example of how using a hint stops forward improvements in performance. You are stuck with the 10g plan until you remove the hint. In some ways it is the poster child of why not to use hints. But it works as advertised.

Kevin
Re: Tuning a SQL query [message #587467 is a reply to message #587449] Sat, 15 June 2013 06:13 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
@Kevin - Thanks.

With your experience, what do you suggest is better in this scenario. Pinning the plan or using optimizer_features_enable hint?
Re: Tuning a SQL query [message #588155 is a reply to message #587449] Fri, 21 June 2013 08:10 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
I never got involved with pinning plans so I can't reall attest to its benefits if any over a simple hint like this.

You'll need to ask someone else. If it were me though I'd ask the support teams I work with which way they want to go.
Re: Tuning a SQL query [message #588163 is a reply to message #588155] Fri, 21 June 2013 10:05 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Ok.

What I think is that using the optimizer hint would be a better idea over pinning the plan, because, in course of time, if we keep pinning the execution plans, it will make things more complex while version up-gradation etc.

So, I am finally going with the optimizer hint.

Thanks for your prompt replies.
Re: Tuning a SQL query [message #588164 is a reply to message #588163] Fri, 21 June 2013 10:09 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://docs.oracle.com/cd/E11882_01/server.112/e16638/hintsref.htm#PFGRF501

READ & reread last sentence below!

A hint is an instruction to the optimizer. When writing SQL code, you may know information about the data unknown to the optimizer. Hints enable you to make decisions normally made by the optimizer, sometimes causing the optimizer to select a plan that it sees as higher cost.

In a test or development environments, hints are useful for testing the performance of a specific access path. For example, you may know that a certain index is more selective for certain queries. In this case, you may use hints to instruct the optimizer to use a better execution plan.

The disadvantage of hints is the extra code that must be managed, checked, and controlled. Changes in the database and host environment can make hints obsolete or even have negative consequences. For this reason, test by means of hints, but use other techniques to manage the SQL execution plans, such as SQL Tuning advisor and SQL Plan Baselines.
Re: Tuning a SQL query [message #588165 is a reply to message #588164] Fri, 21 June 2013 10:11 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
so What is a Baseline?

I know what it is...

[Updated on: Fri, 21 June 2013 10:12]

Report message to a moderator

Re: Tuning a SQL query [message #588166 is a reply to message #588165] Fri, 21 June 2013 10:15 Go to previous message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
For that matter what is a

Stored Outline (9i)
SQL Profile (10g)
Plan Baseline (11g)

A: a list of hints.

so why is it better for us to let a piece of software put 30+ hints in a piece of sql in order to force a specific plan over a DBA putting one hint to revert back to prior behavior?

no one is advocating massive use of hints. Only remarking that a total denial of hints of just as wrong.
Previous Topic: DB designing from performance point of view.
Next Topic: Top wait events
Goto Forum:
  


Current Time: Thu Mar 28 10:06:05 CDT 2024