SQL Query tuning using TOAD
Date: Thu, 22 Oct 2015 16:27:14 +0000
Message-ID: <6907b8256e184eda881312523f6318f3_at_mspm1bmsgm17a.ent.core.medtronic.com>
I was under impression that we could tune a SQL query using TOAD but I am not able see any option. Does anyone have any idea?
Thanks,
Ashoke
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Dominic Brooks
Sent: Thursday, October 22, 2015 4:11 AM
To: Ram Raman
Cc: gogala.mladen_at_gmail.com; ORACLE-L
Subject: RE: Database perfortmance degraded drastically after upgrading the database from Oracle 102.0.4 to Oracle 11.2.0.3
Ram,
Not sure there is a solution as such to direct reads because it's deliberate behaviour.
When direct reads are a problem it's often just inefficient execution plan - either a flip to a bad plan or just previous inefficiencies being hidden by the buffer cache?
So if it's a problem then do fewer full segment scans? Tune queries...
Otherwise you can influence the inputs into the adaptive decision - there are quite a few articles out there about this.
E.g
Larger buffer cache
Fiddle with the thresholds
Etc
Regarding changes in 12c, this is not something I've investigated personally. Nor do we have any prod systems on 12c within my scope at work.
Mladen said earlier that he had an impression it was less of an issue.
Maybe?
Big table cache perhaps?
http://progeeking.com/2014/10/03/rac-12c-direct-path-reads/
Hope this helps.
Dom.
Sent from my Windows Phone
From: Ram Raman<mailto:veeeraman_at_gmail.com> Sent: 22/10/2015 03:47
To: dombrooks_at_hotmail.com<mailto:dombrooks_at_hotmail.com> Cc: gogala.mladen_at_gmail.com<mailto:gogala.mladen_at_gmail.com>; ORACLE-L<mailto:oracle-l_at_freelists.org> Subject: Re: Database perfortmance degraded drastically after upgrading the database from Oracle 102.0.4 to Oracle 11.2.0.3
For direct path reads, it's worth it. It's one of the most common cause of problems on this upgrade path.
Dominic, do you know the solution to the issue?
does this happen with upgrades to 12 from 10g also?
Thanks.
[CONFIDENTIALITY AND PRIVACY NOTICE] Information transmitted by this email is proprietary to Medtronic and is intended for use only by the individual or entity to which it is addressed, and may contain information that is private, privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please delete this mail from your records. To view this notice in other languages you can either select the following link or manually copy and paste the link into the address bar of a web browser: http://emaildisclaimer.medtronic.com
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Oct 22 2015 - 18:27:14 CEST