Friday Mar 02, 2012

Additional Information on SQL Patches

Our last post on SQL patches generated a lot of comments and questions.  Today I will address a couple of questions that are quick to answer.  For those that require more discussion, we'll post more details in the next few weeks.[Read More]

Monday Feb 27, 2012

Using SQL Patch to add hints to a packaged application

In my last post, I showed how you can use the BIND_AWARE hint to skip the monitoring phase of adaptive cursor sharing.  If you have a packaged application, you might be wondering how you can use this hint if you can't edit a query directly.  In this post I'll who you how to do just that, for any hint (or set of hints).[Read More]

Friday Feb 17, 2012

Upcoming events : Hotsos Symposium 2012

I have been lucky enough to be invited to the tenth annual Hotsos Symposium in Irving Texas on March 4-8. The Hotsos Symposium differs from other Oracle conferences because it is a dedicated Oracle performance conference and attracts some of the top performance speakers like, Tom Kyte, Cary Millsap, and Jonathan Lewis. If you get a chance you should really check it out. I have two sessions at this years symposium.[Read More]

Monday Feb 13, 2012

How do I force a query to use bind-aware cursor sharing?

Way back in our original post on adaptive cursor sharing, a commenter asked if there was a way to skip the monitoring for a query and go straight to bind-aware cursor sharing.  We mentioned then that we would add a hint for this purpose, but we never addressed the new hint here on the blog.  Starting in 11.1.0.7, the BIND_AWARE hint can be used to force bind-aware cursor sharing from the first execution.[Read More]

Wednesday Jan 25, 2012

My cursor wasn’t shared because of STB_OBJECT_MISMATCH. What does that mean?

I got a question recently about cursor sharing and why a cursor wasn’t shared due to an STB_OBJECT_MISMATCH.I thought the question and subsequent investigation might make an interesting blog post. Let me begin by outlining the question in detail.[Read More]

Monday Jan 16, 2012

Upcoming events : RMOUG 2012

Even though the new year has just begun my calendar is already filling up with conferences. The first conference of the year is the Rocky Mountain Oracle User Groups Training days on Febuary 14 - 16 at the Colorado Convention Center in Denver, Colorado. It a great conference with over 700
attendee and some really great speakers. I am going to be delivering three session at this years conference:

[Read More]

Monday Jan 09, 2012

Fixed Objects Statistics and why they are important

Fixed objects are the x$ tables and their indexes. The v$performance views in Oracle are defined in top of X$ tables (for example V$SQL and V$SQL_PLAN). Since V$ views can appear in SQL statements like any other user table or view then it is important to gather optimizer statistics on these tables to help the optimizer generate good execution plans. However, unlike other database tables, dynamic sampling is not automatically use for SQL statement involving X$ tables when optimizer statistics are missing. The Optimizer uses predefined default values for the statistics if they are missing. These defaults may not be representative and could potentially lead to a suboptimal execution plan, which could cause severe performance problems in your system. It is for this reason that we strong recommend you gather fixed objects statistics.[Read More]

Monday Dec 19, 2011

Why was the RULE hint ignored?

Recently I got a call from a customer that had just upgraded to Oracle
Database 11.2.0.2 and was panicking because they thought the Rule Based
Optimizer (RBO) had been removed from Oracle Database 11g. The reason
they thought this was because when they tried to add the RULE hint to a
SQL statement they still got a Cost Based Optimizer (CBO) plan.

[Read More]

Friday Dec 02, 2011

Capturing 10053 trace files continued

In our previous blog post I described how you can use the new diagnostic event infrastructure in Oracle Database 11g to capture an Optimizer trace (10053) for any SQL statement once you have its SQL_ID. The approach I showed using the traditional ‘Alter session set event’ approach. What I forgot to mention (and have been scolded for) was that you can also use the new infrastructure to generate an Optimizer trace for any SQL statement in the cursor cache, without having to re-execute it.[Read More]

Wednesday Nov 16, 2011

How do I capture a 10053 trace for a SQL statement called in a PL/SQL package?

Traditionally if you wanted to capture an Optimizer trace (10053) for a SQL statement you would issue an alter session command to switch on a 10053 trace for that entire session, and then issue the SQL statement you wanted to capture the trace for. Once the statement completed you would exit the session to disable the trace. You would then look in the USER_DUMP_DEST directory for the trace file. But what if the SQL statement you were interested  in was actually called as part of a PL/SQL package. How do generate an Optimizer trace for just that statement?[Read More]

Tuesday Nov 08, 2011

Lies, damned lies, and statistics

There was huge interest in our OOW session on Managing Optimizer Statistics. It seems statistics and the maintenance of them continues to baffle people. However, understanding and managing Optimizer statistics is key to optimal SQL execution. Knowing when and how to gather statistics in a timely manner is critical to maintaining acceptable performance. In order to clarify all of the information surrounding statistics we have put together a two part whitepaper on Optimizer statistics and the management of them.[Read More]

Monday Oct 31, 2011

Upcoming events : UKOUG 2011

UK Oracle User Group Conference 2011, takes place in Birmingham on December 4th - 7th. This year there will be 15 different streams and over 250 sessions to choose from. I am lucky enough to be presenting two session at this years conference.

Hope to see you at the conference!

Maria Colgan+

Monday Oct 24, 2011

Does the use of SQL Plan Management and the DBMS_SPM database package require a tuning or diagnostic pack license?

Recently during the Open World conference a lot of people asked me about what additional licenses are need to use SQL Plan Management. No additional licenses are needed to use SQL Plan Management (SPM) or any of the procedures in the DBMS_SPM package. SPM is available as part of Oracle Database Enterprise Edition 11g.


I believe the confusion arises from the wording in the Oracle Database Licensing documentation  that says the Tuning Pack includes "Automatic Plan Evolution of SQL Plan Management". What this is actually referring to is the interaction between the SQL Tune Advisor's nightly tuning task and SPM. This interaction does require you to have a license to use the Tuning pack.

In Oracle Database 11g, the SQL Tuning Advisor, runs automatically during the maintenance window. This automatic SQL tuning task targets high-load SQL statements. These statements are identified by the execution performance data collected in the Automatic Workload Repository (AWR) snapshots and not from SPM. If the SQL Tuning Advisor finds a better execution plan for a SQL statement it will recommend a SQL profile. Some of these high-load SQL statements may already have SQL plan baselines created for them. If a SQL profile recommendation is implemented, the execution plan found by the SQL Tuning Task will be added as an accepted plan to the existing SQL plan baseline. 


The Tuning pack or RAT pack are also required if you want to load plans from a SQL Tuning Set (STS) into SPM. Again this is because an STS can only be created if you have licensed one of these packs. It has nothing to do with SPM.

Maria Colgan+

Thursday Oct 20, 2011

Optimizer Technical Papers

We recently got a comment about our paper on adaptive cursor sharing and cardinality feedback, in which the author wished for more technical papers from the optimizer team. We've actually published papers on a few different topics over the years, so we wanted to summarize and post links to them here. Unfortunately they are not all available online for free, but where possible, we've posted links to the full text.

Cost-Based Query Transformation in Oracle
Rafi Ahmed, Allison W. Lee, Andrew Witkowski, Dinesh Das, Hong Su, Mohamed Zait, Thierry Cruanes: Cost-Based Query Transformation in Oracle. VLDB 2006: 1026-1036
This paper talks about many of the query transformations in Oracle 11g which we have discussed here before, including join elimination, view merging, subquery unnesting, join predicate pushdown, join factorization, plus some others that we have not covered here before.  It also describes the cost-based framework itself, some of the challenges of transforming queries in a cost-based manner, and presents performance results for some experiments on a query workload from the Oracle E-Business Suite.

Optimizer Plan Change Management (full text)
Mohamed Ziauddin, Dinesh Das, Hong Su, Yali Zhu, Khaled Yagoub: Optimizer plan change management: improved stability and performance in Oracle 11g. PVLDB 1(2): 1346-1355 (2008)
This describes SQL plan management, which we discussed on here in a four-part SPM series.  The paper gives a bit more detail about the internals of SPM, plus analyzes the behavior of SPM on a large query workload from Oracle E-Business Suite.

Closing the Query Processing Loop in Oracle 11g
(full text)
Allison W. Lee, Mohamed Zait: Closing the query processing loop in Oracle 11g. PVLDB 1(2): 1368-1378 (2008)
This paper discusses adaptive cursor sharing and cardinality feedback, plus the general concept of using feedback from execution to improve plan selection.  We've covered both of these topics on here before, but the paper also includes an analysis of the performance impact of the two features on a query workload from Oracle E-Business Suite.

Efficient and scalable statistics gathering for large databases in Oracle 11g
Sunil Chakkappen, Thierry Cruanes, Benot Dageville, Linan Jiang, Uri Shaft, Hong Su, Mohamed Zait: Efficient and scalable statistics gathering for large databases in Oracle 11g. SIGMOD Conference 2008: 1053-1064
This paper discusses two major improvements to statistics gathering in 11g -- approximate NDV and incremental statistics gathering.  We've discussed both of these topics here before, but the paper has some additional details of the algorithms, plus results of experiments on a few different data sets.

Enhanced Subquery Optimizations in Oracle, VLDB 2009 (full text)
Srikanth Bellamkonda, Rafi Ahmed, Andrew Witkowski, Angela Amor, Mohamed Zait, Chun Chieh Lin: Enhanced Subquery Optimizations in Oracle. PVLDB 2(2): 1366-1377 (2009)
This paper talks about several of the newer subquery optimizations in Oracle.  We talked about basic subquery unnesting in two posts here a while ago, and alluded to some of the other subquery transformations we perform -- this paper talks about all of those.  It also talks about a new form of join elimination which we introduced in 11gR2.  Nearly all of the topics in this paper are NOT covered here on the blog yet, but we hope to fix that soon!

For future reference, we will be adding a new section on the right side of the page called "Optimizer Technical Papers" with links to all of these papers.

Monday Oct 17, 2011

Open World 2011 recap

We had a great time talking to everyone at Open World 2011 both at our Demogrounds booth and  in our  technical sessions.  We got asked a lot of interesting questions and we plan to address them in future blog posts... so stay tuned.


The slides from our session on managing optimizer statistics are now available on Oracle Open World Content Catalog. The slides for our session on preventing suboptimal plans should be posted soon.


We are still working on a good mechanism for sharing the exercises from the hands - on lab but we will blog more about that once its in place.


If you didn't get to attend the conference, but have a question about the Optimizer you would like to see answered on the blog, you can submit it through via email.



We look forward to see you all next year at Open World 2012

Maria Colgan+

About

The Oracle Optimizer blog is written by members of the Optimizer development team. The goal of this blog is to provide an insight into the workings of the Optimizer and the statistics it relies on. The views expressed on this blog are our own and do not necessarily reflect the views of Oracle and its affiliates. The views and opinions expressed by visitors on this blog are theirs solely and may not reflect ours.

Search

Archives
« August 2015
SunMonTueWedThuFriSat
      
1
2
3
4
5
6
7
8
9
10
11
13
14
15
16
17
18
19
20
21
22
23
24
26
27
28
29
30
31
     
Today