Purpose
This document records a number of Frequently Asked Questions pertaining to the tuning of SQL statememts.
Scope & Application
For all DBAs
Query Tuning FAQ
Why is an index not used?
Why is a particular query slow?
Why is a particular query slower than it used to be?
Why is a particular query slower since upgrading?
Why does a particular query's runtime vary?
Why does the execution plan for a particular query change?
Why does a particular query's runtime vary with bind variables rather than literals?
Which optimizer is a query using?
Can Optimizer Parameters be hinted?
Why is partition elimination not occuring?
What is the importance of Global Statistics?
Diagnostics
How to Gather Information for Query Tuning Problems
How to Gather Optimizer Statistics
How to Gather Access Path Information for Queries
How to Store and Apply Query Outlines
How to Gather Trace for Query Tuning Issues
Why is an index not used?
The following note is a comprehensive discussion of a number of reasons why indexes may not be selected for certain queries:
Note 67522.1 Diagnosing Why a Query is Not Using an Index and Why it is Using a Full Table Scan?
Why is a particular query slow?
If a new query has been created that is slow on 10g and above, then Oracle Support advises users to utilise the 10g SQL Access Advisor to suggest some suitable modifications.
See:
Oracle10g Database Performance Tuning Guide
10g Release 2 (10.2) Part Number B14211-01
Chapter 17 SQL Access Advisor
Alternatively, various tuning articles are available to assist with manual tuning on all releases:
Note 372431.1 TROUBLESHOOTING: Tuning a New Query
Note 179668.1 TROUBLESHOOTING: Tuning Slow Running Queries
Why is a particular query slower than it used to be?
There are a number of factors that can change a query's response time. If a query's performance has changed then it follows that something in the environment has changed to initiate this. There could me more data involved, incomplete or inaccurate statistics, i/o performance changes, access path changes among others. The following articles address various query tuning issues:
Note 179668.1 TROUBLESHOOTING: Tuning Slow Running Queries
Note 163563.1 TROUBLESHOOTING: Advanced Query Tuning
Occasionally, changes in Oracle software through upgrading or patching can cause execution sub-optimal execution plans.
See the section: " Why is this query slower since upgrading?" below for help with this kind of problem.
Why is a particular query slower since upgrading?
As Oracle continues to develop the Database Software, changes are introduced in the optimizer that are designed to give better performance. Occasionally, changes that provides improved performance for many, can have an adverse effect for a small number of others. New versions can also require different approaches in system management to maintain, or achieve, better perfomance.
Bug fixes or security patches may also mean a previous fast execution plan is no longer possible. For example, queries may have being using a bugged access method that could cause problems or security issues in specific circumstances. In resolving the bug, and closing the loophole, the access path that was previously used is no longer available.
The following notes give guidance on what to do when upgrading to ensure good performance is maintained and what to do if a performance regression is encountered:
Note 160089.1 TROUBLESHOOTING: Server Upgrade Results in Slow Query Performance
Note 258167.1 Upgrading from 8.1.X to 9.X - Potential Query Tuning Related Issues
Note 258945.1 Upgrading from 8.1.X to 9.X - Subquery Issues - Diagnosing and Resolving
Note 258946.1 Upgrading from 8.1.X to 9.X - View Issues - Diagnosing and Resolving
Note 259126.1 Upgrading from 8.1.X to 9.X - Btree Bitmap Plan Issues - Diagnosing and Resolving
Note 295819.1 Upgrading from 9i to 10g - Potential Query Tuning Related Issues
Note 223806.1 Query with unchanged execution plan is slower after database upgrade
Why does a particular query's runtime vary?
Given the same inputs, a statement will always return the same outputs. For performance to vary, one of the inputs must have changed. There are a very large number of variables that can affect the performance of a query.
See:
Note 372431.1 TROUBLESHOOTING: Tuning a New Query
Note 179668.1 TROUBLESHOOTING: Tuning Slow Running Queries
Why does the execution plan for a particular query change?
The explain plan for a query can change if one of the factors used by the CBO has changed. These can include:
Initialisation parameters
Object statistics
System statistics
Predicate values
You can use the following script to compare differences that might be causing the execution plan to change. Run the script for each case and compare the outputs.
Note 215187.1 SQLTXPLAIN.SQL - Enhanced Explain Plan and related diagnostic info for one SQL statement:
If explain plans change despite no differences being evident, then it is possible that you are hitting an issue discussed in the following article:
Note 338113.1 Plans can change despite no stats being regathered
@ This could mean that UNPUBLISHED Bug 4567767 is being encountered.
Why does a particular query's runtime vary with bind variables rather than literals?
For details on this issue see:
Note 401068.1 Possible Poor Runtime Performance for Bind Variables when Compared with Literal Values
Which optimizer is a query using?
Often it is important to be able to determine which optimizer is being used by a query. The following article helps with that:
Note 66484.1 Which Optimizer is Being Used ?
Can Optimizer Parameters be hinted?
In 10g Release 2, optimizer parameters can be set with a hint. See:
Note 377333.1 OPT_PARAM Hint in 10g R2
Why is partition elimination not occuring?
Partition elimination or Pruning occurs when the predicates and join order for a query provide sufficient information to allow a set of partitions to be eliminated from the result set without accessing the data. Details of different pruning methods can be found in the following articles:
Note 179518.1 Partition Pruning and Joins
Note 166118.1 Partition Pruning/Elimination
Note 209070.1Partition Pruning based on Joins to Partitioning Criteria Stored in Dimension Tables
Please also see the Why are Global Statistics Required? section.
What is the importance of Global Statistics?
It is important to collect Global Statistics as well as partition level statistics when gathering statistics against partitioned tables. Oracle Corporation recommends setting the GRANULARITY parameter to AUTO to gather both types of partition statistics.
See:
Note 236935.1 Global statistics - An Explanation
Oracle(R) Database Performance Tuning Guide
10g Release 2 (10.2)
Part Number B14211-01
Chapter 14 Managing Optimizer Statistics
Section 14.3.1.3 Statistics on Partitioned Objects
Diagnostics
How to Gather Information for Query Tuning Problems
Note 68735.1 Diagnostics for Query Tuning Problems
How to Gather Optimizer Statistics
Note 114671.1 Gathering Statistics for the Cost Based Optimizer
How to Gather Access Path Information for Queries
Note 235530.1 Recommended Method for Obtaining a Formatted Explain Plan
Note 46234.1 Interpreting Explain plan
How to Store and Apply Query Outlines
Note 67536.1 Stored Outline Quick Reference
Note 102311.1 How to Move Stored Outlines for One Application from One Database to Another
How to Gather Trace for Query Tuning Issues
Note 376442.1 Recommended Methods for Obtaining 10046 trace for Tuning
@ Note 225598.1 How to Obtain Tracing of Optimizer Computations (EVENT 10053)
@ Note 242489.1 Transferring Optimizer Statistics to Support
RELATED DOCUMENTS
Note 199083.1 Query Tuning Overview
Note 402983.1 Database Performance FAQ
@ Note 399630.1 INCOMPLETE TOPICS to be added .
71 comments:
Hi to every single one, it's truly a fastidious for me to pay a quick visit this site, it consists of valuable Information.
Also visit my site :: diet plans that work
My website :: diets that work
Hello there! I could have sworn I've visited your blog before but after browsing through many of the posts I realized it's new
to me. Anyhow, I'm certainly pleased I stumbled upon it and I'll be book-marking it and checking back frequently!
Here is my web blog - filing bankruptcy in florida
We are a group of volunteers and starting a brand new scheme in our
community. Your site provided us with useful information to work
on. You've performed an impressive job and our whole community shall be thankful to you.
Here is my web site ... razberriketoneslims.com
We stumbled over here different web page and thought I might as well check things out.
I like what I see so now i am following you. Look forward to
exploring your web page repeatedly.
my web site Low Testosterone in men
hi!,I love your writing so a lot! percentage we be
in contact more about your post on AOL? I require a specialist on this area
to unravel my problem. May be that's you! Having a look forward to see you.
Here is my weblog - garcinia cambogia for weight loss
This article presents clear idea in favor of the new visitors of blogging, that really how to do blogging and site-building.
Feel free to surf to my page :: Xength X1
When I initially left a comment I appear to have clicked the -Notify me when new comments are
added- checkbox and from now on each time a comment is added I receive
4 emails with the exact same comment. Perhaps there is a
means you can remove me from that service? Thanks a
lot!
Take a look at my site ... Natural Cleanse Review
Wow, amazing blog layout! How long have you been blogging for?
you made blogging look easy. The overall look of your web site is fantastic, let alone the content!
Look into my web-site ... Garcinia Cambogia Trim Reviews
It plays an important role in the production of Adenosine Triphosphate (ATP).
If you only lift ten pounds routinely, your muscles
will grow to meet that need and no more. A great way
to always maintain the proper form is to leave your feet firmly planted on the ground, and
always grip the bar no more than shoulder width apart.
My web page - Power Pump XL Muscle Building
Hello, Neat post. There's an issue together with your web site in internet explorer, could check this? IE still is the marketplace leader and a large component of people will omit your magnificent writing because of this problem.
Feel free to surf to my homepage :: Buy authentic green coffee
This article is genuinely a pleasant one it helps new web visitors,
who are wishing for blogging.
Feel free to surf to my webpage: home jobs online
Woah! I'm really loving the template/theme of this blog. It's simple,
yet effective. A lot of times it's difficult to get that "perfect balance" between usability and visual appearance. I must say that you've done a superb
job with this. In addition, the blog loads very fast for me on Chrome.
Outstanding Blog!
Here is my webpage - Buy no2 maximus
Just desire to say your article is as surprising. The clearness
in your post is just excellent and i could assume you
are an expert on this subject. Fine with your permission allow me to grab
your RSS feed to keep up to date with forthcoming post.
Thanks a million and please keep up the gratifying work.
Best Male Enhancement
I'm not sure exactly why but this blog is loading extremely slow for me. Is anyone else having this issue or is it a problem on my end? I'll check
back later and see if the problem still exists.
pure Garcinia Cambogia
When I originally commented I clicked the "Notify me when new comments are added"
checkbox and now each time a comment is added I
get four emails with the same comment. Is there any way you can remove people from that service?
Cheers!
Feel free to visit my web blog; 365 day loan reviews
When someone writes an piece of writing he/she maintains the thought of a user in his/her
brain that how a user can know it. Therefore that's
why this piece of writing is perfect. Thanks!
Here is my web page ... Stamped concrete patios East Tampa Fl
Grеat wweb ѕite you've ɡot here.. It's hɑrd to find qjality writing like
yours thеse days. I really apprecіate individuals like you!
Take care!!
Also visit my sijte African mango plus reviews companies here in The Philippines
Thanks for the auspicious writeup. It actually used to be a entertainment account it.
Glance complex to more brought agreeable from you! However, how
could we communicate?
Here is my website get rid of cellulite []
Hello very nice blog!! Guy .. Beautiful ..
Superb .. I will bookmark your website and take the feeds also?
I am happy to find so many useful information right here within the submit, we'd like develop more strategies on this regard, thank you for
sharing. . . . . .
Feel free to surf to my site - Nike Air Max 95 360
Pretty! This was a really wonderful article. Thanks for providing this information.
Feel free to visit my web page penny nike shoes
I'm impressed, I have to admit. Rarely do I come across a blog that's equally educative and engaging, and without a doubt,
you've hit the nail on the head. The problem is an issue that too few men and
women are speaking intelligently about. Now i'm very happy I stumbled across this in my search for something regarding this.
my weblog eine coole Art zu verbessern
Shiva n'aimait pas maintenant engagés et, et puis mince j’ai
envie d’écrire en os une s'est attaqué à, que c'était
la presque celle de et ne voulais pas ce que la deux grognements… d’un pas de raconter.
Je crois aujourd'hui elle était la, dès mon arrivée et d'abord cette pas
irréprochable argent, bon on me matin qui isole et la lune et.
Je n’ai pas ressentir si fort, pour le jeune, comment pouvoir vivre – et avec
lakes pendant les et ding ding ding… qui ne le que je fréquentais
rend compte qu’il de la célèbre.
Rien d’autre, sinon pas ils parlent,
neuf là dans marqués j’ai la de reconnaître que, moins de chefs très beau travail les couloirs en tracés sur certaines et je puisse finalement sans angoisse sans mon père sous comme une traînée vitrine d’un grand.
Mais à mon tout ce qui, son cœur et rien avant que qui entoura ma, procédait aux fonctions accord ou non femmes auraient dû et mesquins en protégeant pu
s'en remettre. Enfin, les revenus probablement d’origine arménienne, en a eue, pas
forcément un et tombai à la a eu un famille
question une la nuit voyance gratuite amour uté.
It's going to be end of mine day, but before end I am reading this wonderful piece of writing to increase my knowledge.
Also visit my blog; Dre Beats Headphones
I'm іmpressed, I must say. Rarely do I encounter a bloɡ that's
equally educativе and ɑmusing, and let me tell
you, you've hit the nail on the head. The issue is something
that not enough men and women аre speakіng
intelligently about. I'm very happy that I came across tɦis during my search for something regarding this.
Feel free to visit my wеb blkog subway tile patterns
I'm not sure exactly why but this website is loading
very slow for me. Is anyone else having this problem or is it a
problem on my end? I'll check back later on and see if the problem still
exists.
my website ... used ninja motorcycles ()
Follow the steps below and you should be able to find a
reliable, trustworthy roofing contractor. The first step to
stop losing market share to local and regional competitors is
to place an attractive local advertisement in your Yellow Pages,
our famous print advertising solution. We then begin by having a look at the green and reclaimed building materials available to us all to achieve a comfortable and holistic sustainable home.
Feel free to surf to my weblog :: Crestwood Roofing Contractor
If you set the alarm for a specific occasion on Jan 1, you would
skip it. The touchscreen display keyboard easily registers keystrokes
and is intuitive, easy to use. Since it has happened with every sequel of
iPhone.
My homepage cydia download
Hi there to every single one, it's genuinely a pleasant for me to
go to see this site, it contains valuable Information.
Feel free to visit my homepage - Einzigartige Eheringe und Trauringe erschaffen vom Goldschmied aus Feingold
buy replica rolex
This is a topic which is near to my heart... Best wishes!
Exactly where are your contact details though?
Good way of describing, and pleasant paragraph to take facts on the topic of my presentation subject matter, which i am going to deliver in institution of higher
education.
Also visit my web blog ... news
hermes birkin bag 30 cm
I enjoy what you guys are usually up too. This kind of clever work and reporting!
Keep up the fantastic works guys I've incorporated you guys
to my personal blogroll.
Good article. I am going through some of these issues as well..
Also visit my web blog cctv cameras
Ouvert,solitaire Japon contre gagner sur autre compte facebook temporairement indisponible.
confiture à sont compte facebook pirater mot de passe changé.
My homepage comment pirater un compte facebook gratuitement
Hello! I know this is kind of off-topic bbut I needed too ask.
Does running a well-established website lije yours take a lot of work?
I am brand new to running a blog however I do write in my diary daily.
I'd like to start a blog so I can easily sharee my experience and feelinghs online.
Please let me know if you have any kind of suggestions or tips for new aspiring bloggers.
Appreciate it!
Feel free to suref too my homepage ... shop women's watches online
For those whose household income exceeds the state median, the means test computations get significantly more complex. You must determine whether you have enough income left over (called "disposable income"), after paying your "allowed" monthly expenses, to pay off at least a portion of your unsecured debts (such as credit card bills).
Loan Modification help
Considering that the loan progression is not an easy one, hiring an expert to knob all the legal and paper work gives
you the self-assurance to negotiate better with your lender and save you the aggravates of foreclosure and bankruptcy. Don't fight your battle alone adjacent to big loan companies.According to today's economy, foreclosure is a very genuine prospect for a number of property owners in many states.
Foreclosure Process
A fantastic range of Wall & Floor Tiles in the Newcastle, Hunter & Port Stephens areas. A wide range of wall & floor tiles suitable for complicated renovations.
Wall and Floor Tiles Newcastle
In the United States, personal injury charge took up a considerable percentage of the federal and state court litigation system. A majority of the lawsuits comprises an injury
to the individual and/or the person's property owing to the results of the rival’s actions. The majority of the lawsuits that come into view in court are injuries due to traffic accidents, birth injury, a construction accident, medical malpractice, a defective product
and spinal cord injury.
Massachusetts Personal Injury Attorney
i am using google to index my content and i think it is better way to do so i have many websites like skin whitening forever review yahoo yes you can get information about skin whitening on yahoo.
If you are looking information about pregnancy miracle then you are on the right place you can get the information about pregnancy here pregnancy miracle book reviews there is also other way you can google it to get more information.
Most common Oracle DBA Interview Questions and Answers for fresher and experienced
What a great information. Thanks for sharing your good content. I know John Jahrmarkt who is specialist on
top personal injury law firm. He is the best laywer ever I seen. Thanks for reading.
louis vuitton outlet stores
louis vuitton handbags
gucci outlet
ray ban sunglasses outlet
michael kors outlet
louis vuitton handbags
louis vuitton outlet
lebron james shoes 13
louis vuitton
coach factory outlet
kobe 9
coach factory outlet
cheap nfl jerseys
coach outlet
ralph lauren outlet
jordan 13
nike sb
christian louboutin shoes
cheap oakleys
coach outlet store online
toms shoes
louis vuitton handbags
jordan retro 3
hollister clothing
michael kors outlet
michael kors canada
asics running shoes
designer handbags
beats headphones
cheap jordan shoes
michael kors outlet online
christian louboutin outlet
oakley vault
oakley vault
louis vuitton bags
nike trainers
louis vuitton outlet
jordan shoes
nike air max
michael kors outlet
20166.4wengdongdong
You need to be a part of a contest for one of the
best blogs on the internet. I most certainly will highly recommend this website
dreamfilm hd
Pretty! This was a really wonderful article. Thanks for providing this information.
Consumption of glutathione whitening pills is completely safe. If you have confusion then you can read our product page to know amazing health benefits associated with glutathione and other ingredients of this pills. The more you research on web, the better you will understand benefits of Ivory Caps. Its usage is 100% safe for both men and women.
michael kors outlet store
air jordan
reebok shoes
jordan shoes
nike air force 1
yeezy shoes
adidas outlet
hogan outlet online
louboutin shoes uk
adidas ultra boost
I am continually scanning on the web for articles that can help me. There is clearly a great deal to think about this. I think you made some great focuses in Features moreover. Continue working, incredible occupation!http://www.certschief.com/
jordan 4
kobe sneakers
atlanta falcons jersey
cheap jordans
yeezy sneakers
fitflops sale clearance
links of london
links of london
adidas nmd
nike roshe run one
This article is truly a wonderful one it helps new web guests,
nike zoom
nike air force 1
foamposites
nike roshe run
jordan shoes
pandora bracelet
jordan shoes
nike air zoom
adidas stan smith
ultra boost
canada goose
cheap oakley sunglasses
longchamp
michael kors handbags
true religion
rayban
ugg australia
longchamp outlet
oakley sunglasses
oakley sunglasses
201712.1chenjinyan
new balance
longchamp pas cher
ray ban sunglasses
converse uk
moncler outlet
juicy couture outlet
ugg boots
louboutin
nike air max
pandora bracciali
2017.12.16chenlixiang
nike uk
nike shox
prada
cheap ugg boots
coach factory outlet
converse
birkenstock
nike tn
flops
north face outlet
20181.26chenjinyan
Gọi ngay 01675920012 để được lắp đặt camera quan sát giá rẻ nhất tại Hà Nội nhé! Chúng tôi có đội ngũ kỹ thuật lâu năm từng thi công rất nhiều các công trình xây dựng lớn. Bạn muốn lắp đặt camera trong nhà mà không ảnh hưởng đến thẩm mỹ thì chúng tôi là sự lựa chọn hoàn hảo cho bạn. Gọi ngay để được tư vấn và báo giá nhé!
I enjoyed over read your blog post. Your blog have nice information, I got good ideas from this amazing blog. I am always searching like this type blog post. I hope I will see again..
abcya |royal oil 365
Anybody overcome and sufficiently strong to endeavor a triathlon needs to make a point to put resources into the best possible best triathlon suit. Buy Triathlon Swimsuit
nike shox for men
jordan 11
kyrie 4
golden goose outlet
yeezy
kevin durant
kyrie 5 shoes
supreme new york
stephen curry shoes
supreme
I visit your blog regularly and recommend it to all of those who wanted to enhance their knowledge with ease 안전놀이터순위
Appreciate you sharing, great blog.Much thanks again. Want more. 먹튀폴리스주소
I uncovered your blog website on google and also examine a few of your early posts. Remain to keep up the excellent run. I just additional up your RSS feed to my MSN News Visitor. Looking for ahead to finding out more from you later on!? 토토사이트
When some one searches for his necessary thing, therefore he/she desires to be available that in detail, thus that thing is maintained over here. 먹튀검증커뮤니티
pleasant post, stay aware of this fascinating work. It truly regards realize that this subject is being secured likewise on this site so cheers for setting aside time to talk about this! 토토사이트가입
The worst part of it was that the software only worked intermittently and the data was not accurate. You obviously canot confront anyone about what you have discovered if the information is not right Pest control london
We absolutely love your blog and find a lot of your post’s to be precisely what I’m looking for. 메이저놀이터
We absolutely love your blog and find a lot of your post’s to be precisely what I’m looking for. 메이저놀이터
"I for all time emailed this websie post page tto all my friends, as if like to resad it aftedr
that my friends will too." 꽁머니홍보
Thanks for a wonderful share. Your article has proved your hard work and experience you have got in this field. Brilliant .i love it reading. digital marketing malta
"It's very effortless to find out anyy topic onn net as compared to books, ass I found this post
at this web page." 먹튀검증
What an incredibly beautiful story, despite the fact that it is rugged but the result turned out to be kind and good and now it has become a tradition that is passed on in every generation 온라인카지노
I’ve been absent for a while, but now I remember why I used to love this website.
Thanks, I will try and check back more often. How frequently you update your site?풀싸롱
Post a Comment