Michigan OakTable Symposium (Advert)

18 02 2010

February 18, 2010 (Updated April 5, 2010)

Looking for an Oracle training session in the rust belt of the United States?

The Michigan members of the OakTable Network are organizing a two day Oracle training event on the Thursday and Friday (September 16th and 17th, 2010) just before Oracle OpenWorld. OakTable Network members from around the world will be providing sessions at the training event in Ann Arbor, Michigan (not far from the University of Michigan) at the Four Points by Sheraton hotel. Presented sessions will appeal to both DBAs and developers. Seating at the event will be limited to 300 people.

Confirmed Speaker List (See the Official Site for the Latest List):

Alex Gorbachev
Alex Gorbachev is a respected figure in the Oracle world, and a sought-after leader and speaker at Oracle conferences around the globe. He has been recognized as an Oracle ACE Director for his contributions to the community and unmatched set of skills. He is the founder of the Battle Against Any Guess movement promoting scientific troubleshooting techniques. He is currently the Chief Technology Officer at The Pythian Group. Alex has worked for The Pythian Group in several roles. He began by leading a team of database experts in Ottawa. He then moved to Australia to build the company’s presence in the East Asia Pacific region. Now he is back in Ottawa as The Pythian Group’s Chief Technology Officer. In all his work, Alex continues to work towards bridging the gap between business and technology. The search for the perfect fit between technology, engineering talents, and business process is what keeps him up at night. Alex co-authored the book “Expert Oracle Practices: Oracle Database Administration from the Oak Table”.

Andy Zitelli
Andrew Zitelli has thirty years of experience as a software developer, data architect and performance analyst. He has served as a consultant to the aerospace, semiconductor, steel, and pharmaceutical industries. Andrew is proficient working with a broad range of operating systems and languages and has 25 years experience working with ten different relational database products, including 17 years working with Oracle. He holds MS and BA degrees in Computer Science. During the past 30 years, Andrew has taught college-level courses and made presentations at technical conferences and other venues on a wide range of topics. He made presentations at the Hotsos Symposiums in 2007, 2008 and 2009.

Cary Millsap
Cary Millsap is the founder and president of Method R Corporation. He is widely known in the Oracle community as a speaker, educator, consultant and writer. He is the author (with Jeff Holt) of Optimizing Oracle Performance (O’Reilly 2003). Cary is also co-author of Oracle Insights: Tales of the Oak Table. Cary is one of the former founding partners of Hotsos and worked at Oracle for ten years. In the early 1990s, Cary created Oracle’s OFA Standard, which became the default configuration standard for Oracle software installations worldwide. Cary is also a woodworker who builds and repairs furniture in his own shop.

Charles Hooper
Charles Hooper is the IT Manager and an Oracle database administrator at K&M Machine-Fabricating, Inc., where he has been employed since 2000. His role in the company extends well beyond Oracle database administration responsibilities, providing opportunities for database performance tuning, network administration, programming, hardware/software troubleshooting, mentoring fellow IT staff, and end-user training for the Infor Visual Enterprise ERP system as well as other custom developed applications. Charles is well known among the user community of Infor Visual Enterprise due to his years of participation in various user forums answering technical questions, providing the only Oracle presentation at Infor’s 2008 user’s conference, and providing presentations to regional user groups. Prior to joining K&M, he was a computer/technology instructor and Novell Netware administrator. Charles co-authored the book “Expert Oracle Practices: Oracle Database Administration from the Oak Table”.

Dan Fink
Daniel Fink is a senior Oracle database engineer and consultant, specializing in Oracle optimization, troubleshooting, internals, and data recovery. He started as a DBA on Oracle 7.0.16 running Parallel Server on OpenVMS and has experience on major Unix platforms and releases up to 10g. He maintains a library of his research at www.optimaldba.com.

Doug Burns
Doug Burns is an independent contractor who has 20 years of experience working with Oracle in a range of industries and applications. As well as presenting at a number of conferences, he has developed, taught, and edited performance-related courses for both Oracle UK and Learning Tree International.

Jeff Needham
Jeff Needham currently works for Red Hat Software and is responsible for Oracle RDBMS, and GFS/RAC certifications. This includes tending the relationship between RHEL Engineering, Oracle Linux Engineering and Server Technologies. Having founded Scale Abilities with James Morle in 1989, Jeff pioneered high performance NFS for Oracle at Yahoo! using Netapp and AMD technologies. Jeff worked on performance and scalability for Oracle 7 and Oracle 8 during his tenure in the Redwood Shores kernel group. When not at work, he attempts fatherhood, collects Opteron servers and tries to maintain a vintage electronic music studio.

Jeremiah Wilton
Jeremiah Wilton has worked with Oracle technology since 1994. His main claim to fame is having been Amazon.com’s first database administrator, back in the pre-IPO days. For seven years, he helped Amazon.com survive exponential scaling, and a wide variety of nearly-catastrophic technology failures. Jeremiah owned and ran ORA-600 Consulting for a number of years, until it was acquired by Blue Gecko, Inc., a global provider of remote administration for Oracle, MySQL, and E-Business Suite. Jeremiah also teaches the Oracle certificate program for the University of Washington. Jeremiah is an Oracle Certified Master, and a frequent presenter at industry conferences and user groups. His publications and whitepapers can be found at www.bluegecko.net. Jeremiah co-authored the book “Expert Oracle Practices: Oracle Database Administration from the Oak Table”.

Jonathan Lewis
Jonathan Lewis is a well-known figure in the Oracle world with more than 21 years experience of using the database (and several years on other products). He has published two books, contributed to three others, runs a couple of websites and contributes fairly regularly to newsgroups, forums, User Group magazines, and speaking events around the world. Jonathan has been self-employed for most of his time in the IT industry. He specialises in short-term assignments, typically of a design, review, or trouble-shooting  nature.

Joze Senegacnik
Jože Senegačnik has more than 20 years of experience in working with Oracle products. He began in 1988 with Oracle Database version 4 while working for the City of Ljubljana, where he had charge over the city’s municipal and geographic information systems. From 1993 to 2003, he worked in developing GIS systems for the Surveying and Mapping Authority of the Republic of Slovenia, and in the development of applications for other governmental institutions, all based on the Oracle database. More recently, he has specialized in performance optimization, having developed his own toolset for monitoring performance and analyzing trace files. Jože is an internationally recognized speaker. He is a regular speaker at user-group conferences, especially those put on by the Slovenian Oracle Users Group (SIOUG), the Independent Oracle Users Group (IOUG), and the United Kingdom Oracle Users Group (UKOUG). He also speaks routinely at the Hotsos Symposium and Oracle Open World. In addition to sharing his knowledge through conference talks, Jože conducts technical seminars organized either by Oracle University or himself. He was awarded Oracle ACE membership for his long record of positive contributions to the Oracle community. Jože co-authored the book “Expert Oracle Practices: Oracle Database Administration from the Oak Table”.

Mogens Nørgaard
Mogens Nørgaard is technical director at Miracle A/S (http:www.miracleas.dk), a database knowledge center and consulting/training company based in Denmark, and is the cofounder and “father figure” of the OakTable Network. He is a renowned speaker at Oracle conferences all over the world and organizes some highly respected events through Miracle A/S, including the annual MasterClass (2001: Cary Millsap, 2002: Jonathan Lewis, 2003: Steve Adams, 2004: Tom Kyte) and the Miracle Database Forum, which is a 3-day conference for database people. He is also the cofounder of the Danish Oracle User Group (OUGKD) and was voted “Educator of the Year” in Oracle Magazine’s Editor’s Choice Awards, 2003.

Randolf Geist
Randolf Geist has been working with Oracle software for 15 years. Since 2000 he has operated as a freelance database consultant focusing primarily on performance related issues, and in particular helping people to understand and unleash the power of the Oracle cost based optimizer (CBO). He is writing on his blog about CBO-related issues and is also regularly contributing to the official OTN forums. Randolf is a member of the Oracle ACE program and is an Oracle Certified Professional DBA for Oracle Versions 8i, 9i, and 10g. He also maintains SQLTools++, an open-source Oracle GUI for Windows. Randolf co-authored the book “Expert Oracle Practices: Oracle Database Administration from the Oak Table”.

Riyaj Shamsudeen
Riyaj Shamsudeen has 17+ years of experience in Oracle and 16+ years as an Oracle DBA/Oracle Applications DBA. He is the principal DBA for OraInternals, a consulting company resolving many advanced performance, recovery, RAC, and EBS11i issues. Riyaj specializes in RAC and performance issues and has authored many articles, mostly about performance, database internals, optimizer internals, etc. He is a frequent presenter in major conferences such as Hotsos Symposia, UKOUG, RMOUG etc. co-authored the book “Expert Oracle Practices”.

Robyn Sands
Robyn Anderson Sands is a software engineer for Cisco Systems. In a previous incarnation, she worked in Industrial Engineering, Manufacturing Development, and Quality Engineering at Lockheed Martin, supporting the P-7, F-22, and C-130J programs. Robyn has been working with databases and Oracle software since around 1996. She began her work with Oracle by developing tools for scheduling, composite fabrication capacity modeling, and engineering work flow, and progressing to the implementation and administration of data warehouse, PeopleSoft, and SAP systems. Current projects include “architecting” and managing the development of embedded database systems for Cisco customers, and searching for new ways to design and develop database systems with consistent performance and minimal maintenance requirements. She has been a speaker at UKOUG, Miracle conferences, Oracle Open World, and the Hotsos Symposium.  Robyn co-authored the book “Expert Oracle Practices: Oracle Database Administration from the Oak Table”.

Tanel Poder
Tanel Põder is an experienced consultant with deep expertise in Oracle database internals, advanced performance tuning and end-to-end troubleshooting. He specializes in solving complex problems spanning multiple infrastructure layers such as UNIX, Oracle, application servers and storage. He is one of the first Oracle Certified Masters in the world, passing the OCM DBA exam in 2002; and is a frequent speaker at major Oracle conferences worldwide.

Tim Gorman 
Tim Gorman began his IT career in 1984 as a C programmer on UNIX and VMS systems, working on medical and financial systems as an application developer, systems programmer, and systems administrator. He joined Oracle Corporation in 1990 as a consultant, then became an independent consultant in 1998, and has worked for SageLogix since 2000. Gorman is the co-author of Essential Oracle8i Data Warehousing and Oracle8 Data Warehousing. He specializes in performance tuning applications, databases, and systems, as well as data warehouse design and implementation, backup and recovery, architecture and infrastructure, and database administration. Gorman still considers himself a pretty good coder, although the market for C programs has dried up somewhat lately. Tim co-authored the book “Expert Oracle Practices: Oracle Database Administration from the Oak Table”.

———-

Awaiting Confirmation (Subject to Change):

Christian Antognini
Since 1995, Christian Antognini has focused on understanding how the Oracle database engine works. His main interests include logical and physical database design, the integration of databases with Java applications, the query optimizer and basically everything else related to application performance management and optimization. He is currently working as a principal consultant and trainer at Trivadis AG (http://www.trivadis.com) in Zürich, Switzerland. If Christian is not helping one of his customers get the most out of Oracle, he is somewhere lecturing on application performance management or new Oracle database features for developers. In addition to classes and seminars organized by Trivadis, he regularly presents at conferences and user group meetings. He is a proud member of the Trivadis Performance Team and of the OakTable Network. Christian is the author of the book Troubleshooting Oracle Performance (Apress, 2008).

Eric Grancher
Eric Grancher has been working at CERN since 1996 in the Information Technology Division. He has been working on different aspects of databases and application server products: database design consulting, cluster database administration and usage with commodity hardware, application server consulting, database and application server monitoring. He is currently responsible for a team that focuses on database oriented application deployment. He holds an engineer diploma from the French telecommunication engineer school “ENST – Telecom Paris” and a Magistre (Master) of Parallel Computing from “Ecole Normale Suprieure de Lyon”.

James Morle
With 20 years’ experience in professional computing, James Morle has been personally responsible for the architecture and implementation of some of the world’s largest and most complex business systems, including a 3-node Oracle Parallel Server configuration that services 3000 online users. James is a well-respected member of the Oracle community and is the author of the critically acclaimed book Scaling Oracle8i. He is the cofounder of Scale Abilities (http://www.scaleabilities.com), a specialist consulting and training company focusing on aspects of system engineering related to building very large and complex computer systems.  Authored the book “Scaling Oracle8i: Building Highly Scalable OLTP System Architectures”, co-authored the book “Oracle Insights: Tales of the Oak Table”

Marco Gralike
Marco Gralike, working for AMIS Services BV as a Principal Oracle Database Consultant in the Netherlands, has experience as a DBA since 1994 (Oracle 6). Marco is also eager and skillful in other fields, like Operating System Administration and Application Servers, mainly in finding working, performing solutions. Marco has been specializing in Oracle XMLDB, since 2003, focusing on his old love, database administration and performance. He is an Oracle XMLDB enthusiast. He is also a dedicated contributor of the Oracle User Group community, helping people with their steep XMLDB learning curve. To this purpose, Marco also devoted his personal blog site to XMLDB and other Oracle issues. Marco has presented several times in Holland, UKOUG (2007, 2008) and Oracle Open World (2008, 2009). He has been awarded the Oracle ACE title for his work in the Oracle XMLDB area (2007).

———-

The MOTS Executive Committee:

Carol Dacko – OakTable member
Mark Bobak – Oak Table member
Mark Powell – OakTable member
Charles Hooper – OakTable member
Ric Van Dyke – Hotsos Friend
Myke Ratcliff – Esteemed Friend

The Michigan OakTable Symposium (MOTS) is not intended as a money making event. As such, the cost for attending the event has been set as low as possible to essentially “break-even”. The primary mission of the symposium is to provide attendees with logical, structured processes that lead to reproducible success, rather than treating the Oracle Database as a magical, unpredictable, black-box that yields unexpected behavior.

Early registration at a rate of $450 per person will end April 30, 2010. Registration between May 1 and July 31 is $600 per person, and increases to $750 per person after July 31. These prices do not include room hotel costs (roughly $100 per night), but will include a high quality buffet lunch. You may register for the event at the site:

Watch for future updates, agenda, registration details, and more information on the official MOTS site: http://michigan.oaktable.net.

Preliminary Schedule for Thursday, 16 September 2010 and Friday, 17 September 2010: 

Michigan OakTable Schedule

Michigan OakTable Schedule

Abstracts for each of the sessions are located on the official Michigan OakTable site.





Parallel Reasoning

17 02 2010

February 17, 2010 (Updated October 7, 2010: 11.2 documentation links fixed)

This article is a light-weight exploration of parallel query which is available in the Enterprise Edition of Oracle Database.  There are a lot of great articles on the Internet that discuss parallel query benefits and pitfalls, and Metalink contains a couple of decent articles also.  For example, Metalink Doc ID 263153.1 includes the following bit of advice:

“The parallel hint on the object make sure that we access this object in parallel.”

Neat.  Let’s see if it’s true.  We will experiment with the test table found in this blog article, being sure to collect statistics on all of the columns, and executing the test queries on Oracle Database 11.2.0.1 using my VBS tool for automatically generating execution plans.  Here is the query that will be executed:

SELECT /*+ PARALLEL(2) */
  *
FROM
  T1
WHERE
  C1 BETWEEN 1 AND 10000;

The following execution plan was generated for the query:

SQL_ID  gjcx2fjkwt7xc, child number 0
-------------------------------------
SELECT /*+ PARALLEL(2) */    *  FROM    T1  WHERE    C1 BETWEEN 1 AND 
10000

Plan hash value: 2332705540

-----------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |       |       |   343 (100)|          |        |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1           | 10000 |  2236K|   343   (0)| 00:00:05 |  OR112 |
|*  2 |   INDEX RANGE SCAN          | SYS_C0018049 | 10000 |       |    20   (0)| 00:00:01 |  OR112 |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C1">=1 AND "C1"<=10000)

Note
-----
   - Degree of Parallelism is 1 because of hint

That’s a strange note at the end of the execution plan.  Maybe Doc ID 263153.1 does not apply to Oracle 11.2.0.1, because the query did not execute in parallel?  Let’s try again:

SELECT /*+ PARALLEL(3) */
  *
FROM
  T1
WHERE
  C1 BETWEEN 1 AND 10000;

SQL_ID  45mgp20qp9rfu, child number 0
-------------------------------------
SELECT /*+ PARALLEL(3) */    *  FROM    T1  WHERE    C1 BETWEEN 1 AND 
10000

Plan hash value: 2494645258

--------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |       |       |   329 (100)|          |        |      |            |
|   1 |  PX COORDINATOR      |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)| :TQ10000 | 10000 |  2236K|   329   (0)| 00:00:04 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    PX BLOCK ITERATOR |          | 10000 |  2236K|   329   (0)| 00:00:04 |  Q1,00 | PCWC |            |
|*  4 |     TABLE ACCESS FULL| T1       | 10000 |  2236K|   329   (0)| 00:00:04 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access(:Z>=:Z AND :Z<=:Z)
       filter(("C1"<=10000 AND "C1">=1))

Note
-----
   - Degree of Parallelism is 3 because of hint

Well, that’s odd.  Doc ID 263153.1 first doesn’t apply to Oracle 11.2.0.1, and then does apply to Oracle 11.2.0.1.  Notice that the calculated total query cost of the first plan is 343 and the calculated total query cost of the second plan is 329.  Time for another test:

SELECT /*+ PARALLEL(4) */
  *
FROM
  T1
WHERE
  C1 BETWEEN 1 AND 10000;

SQL_ID  1m142v1crjs2j, child number 0
-------------------------------------
SELECT /*+ PARALLEL(4) */    *  FROM    T1  WHERE    C1 BETWEEN 1 AND 
10000

Plan hash value: 2494645258

--------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |       |       |   247 (100)|          |        |      |            |
|   1 |  PX COORDINATOR      |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)| :TQ10000 | 10000 |  2236K|   247   (1)| 00:00:03 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    PX BLOCK ITERATOR |          | 10000 |  2236K|   247   (1)| 00:00:03 |  Q1,00 | PCWC |            |
|*  4 |     TABLE ACCESS FULL| T1       | 10000 |  2236K|   247   (1)| 00:00:03 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access(:Z>=:Z AND :Z<=:Z)
       filter(("C1"<=10000 AND "C1">=1))

Note
-----
   - Degree of Parallelism is 4 because of hint

The calculated total query cost decreased to 247.  OK, if we keep bumping up the parallel degree, the calculated cost becomes lower, so we might as well get it over with and do this:

SQL_ID  9xccz9nu4hrqx, child number 0
-------------------------------------
SELECT /*+ PARALLEL(1000) */    *  FROM    T1  WHERE    C1 BETWEEN 1 
AND 10000

Plan hash value: 2494645258

--------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |       |       |     2 (100)|          |        |      |            |
|   1 |  PX COORDINATOR      |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)| :TQ10000 | 10000 |  2236K|     2   (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    PX BLOCK ITERATOR |          | 10000 |  2236K|     2   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|*  4 |     TABLE ACCESS FULL| T1       | 10000 |  2236K|     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access(:Z>=:Z AND :Z<=:Z)
       filter(("C1"<=10000 AND "C1">=1))

Note
-----
   - Degree of Parallelism is 1000 because of hint

There, we now have a cost of 2 – the query should now finish instantaneously.  :-)  But why didn’t the query excute in parallel when PARALLEL(2) was specified in the hint?  A search of the Internet provides the answer:

Hints on Hinting (page 2)

“The hint parallel(t1, N) does not tell Oracle to run the query in parallel at degree N – it tells the optimizer that when it is calculating the cost of a tablescan on table t1 it should cost for parallel degree N, and if that plan then happens to be the cheapest of all the plans to consider that that’s the plan to use.”

Hints – Again

“But the hint we supplied is only relevant to the tablescan – and we can see it being used in the arithmetic above: the cost_io is the serial I/O cost of the tablescan, the resp_io is the ‘parallel response’ time for the query which is (cost_io / (0.9 * degree)).”

Calculated costs of the access paths matter… so that’s why the first query did not execute in parallel.

For those who decide to rebuild indexes for whatever reason, and decide to do so in parallel, you might be setting yourself up for headaches:

A Gotcha with Parallel Index Builds, Parallel Degree and Query Plans

I haven’t enabled parallelism for my table! How can Oracle go parallel without my consent?  Parallel index (re)build will persistently set the index parallel degree in data dictionary to the value used during build!

Oracle 11.2.0.1 introduced a couple of changes to parallel query, and a couple of those changes are listed below:

http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/sql_elements006.htm#BABHFDDH

“Beginning with Oracle Database 11g Release 2, the PARALLEL and NO_PARALLEL hints are statement-level hints and supersede the earlier object-level hints: PARALLEL_INDEX, NO_PARALLEL_INDEX, and previously specified PARALLEL and NO_PARALLEL hints. For PARALLEL, if you specify integer, then that degree of parallelism will be used for the statement. If you omit integer, then the database computes the degree of parallelism. All the access paths that can use parallelism will use the specified or computed degree of parallelism.”

http://download.oracle.com/docs/cd/E11882_01/server.112/e16541/parallel002.htm

“When the parameter PARALLEL_DEGREE_POLICY is set to AUTO, Oracle Database automatically decides if a statement should execute in parallel or not and what DOP it should use. Oracle Database also determines if the statement can be executed immediately or if it is queued until more system resources are available. Finally, it decides if the statement can take advantage of the aggregated cluster memory or not.

The following example illustrates computing the DOP the statement should use:

SELECT /*+ parallel(auto) */ ename, dname FROM emp e, dept d
WHERE e.deptno=d.deptno;

The following example forces the statement to use Oracle Database 11g, Release 1 (11.1) behavior:

SELECT /*+ parallel(manual) */ ename, dname FROM emp e, dept d WHERE e.deptno=d.deptno;"

Also from the documentation:

“Oracle ignores parallel hints on temporary tables. Refer to CREATE TABLE and Oracle Database Concepts for more information on parallel execution.”

As I mentioned at the beginning of this article, I only intend to provide a light-weight exploration of parallel query, so I will stop here… or better yet after providing a couple of potentially helpful Metalink article references that you can review using your shiny new Apple iPad:

  • Doc ID 263153.1 “FAQ’s about Parallel/Noparallel Hints” (briefly mentioned at the beginning of this article)
  • Doc ID 203238.1 “Using Parallel Execution”
  • Doc ID 826893.1 “Invalid Hint in 10g Can Cause Other Hints To Be Ignored, Including Parallel Hints”
  • Doc ID 199272.1 “Why didn’t my parallel query use the expected number of slaves?”
  • Doc ID 233754.1 “Why doesn’t my query run in parallel?”
  • Doc ID 267330.1 “SQL statements that run in parallel with NO_PARALLEL hints”
  • Doc ID 196938.1 “Why did my query go parallel?”
  • Doc ID 752816.1 “Parallel Update On Table Does Not Work If Statistics Have Been Calculated”
  • Doc ID 237287.1 “How To Verify Parallel Execution is running”
  • Doc ID 457857.1 “Script to monitor parallel queries”
  • Doc ID 280939.1 “Checklist for Performance Problems with Parallel Execution”
  • Doc ID 46234.1 “Interpreting Explain plan”
  • Doc ID 260845.1 “Old and new Syntax for setting Degree of Parallelism”
  • Doc ID 235400.1 “Disable Parallel Execution on Session/System Level”
  • Doc ID 763419.1 “Neither Table DOP nor parallel hint is saved in the Stored Outline hints”
  • Doc ID 6474009.8 “Bug 6474009 – On ASSM tablespaces Scans using Parallel Query can be slower than in 9.2″
  • Doc ID 6748058.8 “Bug 6748058 – A parallel query executes serially due to correlation variable”
  • Doc ID 6402957.8 “Bug 6402957 – Suboptimal parallel query performance for Top-n rank queries.”
  • Doc ID 873392.1 “Ora-04030 with high PGA allocation for ‘kxs-heap-p’ under session heap”
  • Doc ID 844538.1 “Wrong Results For ROWNUM During Parallel Execution on RAC”
  • Doc ID 755975.1 “Parallel Query With Virtual Private Database Returns Wrong Result or Fails With ORA-600[kzrtgpp - kxfxslavesql]“

Be on the lookout for hard hitting parallel query articles by other authors (such as Doug Burns), and try not to abuse this feature.  I will leave you with this paraphrase from the book “Practical Oracle 8i”

Parallel query’s objective is to maximize resource utilization in order to reduce query execution time.  Oracle may select a high CPU consumption plan using a full tablescan, rather than a lower CPU consuming index range scan – this situation may cause performance problems in an OLTP system.  Parallel query is a data crunching, non-scalable, batch processor.





How to Determine which First Rows OPTIMIZER_MODE was Specified

16 02 2010

February 16, 2010

As you are probably aware, recent releases of Oracle (10.1 and above) support several variants of the first rows OPTIMIZER_MODE, in addition to ALL_ROWS, RULE, CHOOSE, and the original FIRST_ROWS (RULE and CHOOSE are now deprecated, and generally FIRST_ROWS should not be used).  When a 10046 trace is generated for a session, the trace file includes a reference (og=) that indicates the optimizer mode in effect for that SQL statement, as described in this article.  Unfortunately, 10046 extended trace files do not make a distinction between the original FIRST_ROWS optimizer mode and the newer variants FIRST_ROWS_1, FIRST_ROWS_10, FIRST_ROWS_100, and FIRST_ROWS_1000 – all will show og=2 in the 10046 trace file.  When the OPTIMIZER_MODE is set to FIRST_ROWS_10, for instance, the optimizer assumes that the client will only read the first 10 rows, and the client will discard the rest of the rows in the result set.  If the optimizer predicts that only eight rows will be returned (or a number less than or equal to 10), the optimizer will likely generate an execution plan similar to what would be generated with the OPTIMIZER_MODE set to ALL_ROWS.  As such, a different execution plan could be generated if the OPTIMIZER_MODE is set to FIRST_ROWS_10 rather than FIRST_ROWS_1000 when the result set is predicted to include, for instance, 120 rows.

Last year a question appeared on the OTN forums asking how to determine which of the FIRST_ROWS optimizer modes was in use based on the contents of a raw 10046 trace file.  Sounds like an interesting request.  If a simultaneous 10053 trace was enabled, or a 10132 trace (as suggested by Jonathan Lewis in the thread), the optimizer mode will be written directly to the same trace file that includes the 10046 trace information, if the SQL statement is hard parsed.  What else can you do?  A trace file generated on Oracle 11.1 and above includes the SQL_ID for the SQL statement in the raw 10046 trace file, while earlier versions include the HASH_VALUE which can be used to determine the SQL_ID.  Unfortunately, there could be multiple child cursors for the same SQL_ID, each potentially using a different value for the OPTIMIZER_MODE.

As a test, we could use a script like the following (using the sample table from this blog article):

ALTER SESSION SET TRACEFILE_IDENTIFIER = 'SQL_OP_MODE_TEST';
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 8';
ALTER SESSION SET OPTIMIZER_MODE='ALL_ROWS';

SELECT
  C1,
  C2,
  C3
FROM
  T1
WHERE
  C1<=50000;

SELECT SYSDATE FROM DUAL;

ALTER SESSION SET OPTIMIZER_MODE='FIRST_ROWS_1';

SELECT
  C1,
  C2,
  C3
FROM
  T1
WHERE
  C1<=50000;

SELECT SYSDATE FROM DUAL;

ALTER SESSION SET OPTIMIZER_MODE='FIRST_ROWS_10';

SELECT
  C1,
  C2,
  C3
FROM
  T1
WHERE
  C1<=50000;

SELECT SYSDATE FROM DUAL;

ALTER SESSION SET OPTIMIZER_MODE='FIRST_ROWS_100';

SELECT
  C1,
  C2,
  C3
FROM
  T1
WHERE
  C1<=50000;

SELECT SYSDATE FROM DUAL;

ALTER SESSION SET OPTIMIZER_MODE='FIRST_ROWS_1000';

SELECT
  C1,
  C2,
  C3
FROM
  T1
WHERE
  C1<=50000;

SELECT SYSDATE FROM DUAL;

ALTER SESSION SET OPTIMIZER_MODE='ALL_ROWS';

ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF';

The resulting trace file might contain entries like ths following (when executed on Oracle 10.2.0.4):

PARSING IN CURSOR #1 len=51 dep=0 uid=31 oct=3 lid=31 tim=1934865102 hv=1321303491 ad='94ec5058'
...
PARSE #1:c=0,e=1474,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1934865097
...
STAT #1 id=1 cnt=50000 pid=0 pos=1 obj=114250 op='TABLE ACCESS FULL T1 (cr=3323 pr=0 pw=0 time=50079 us)'
...
PARSING IN CURSOR #1 len=51 dep=0 uid=31 oct=3 lid=31 tim=1943577683 hv=1321303491 ad='94ec5058'
...
PARSE #1:c=0,e=1694,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=2,tim=1943577677
...
STAT #1 id=1 cnt=50000 pid=0 pos=1 obj=114250 op='TABLE ACCESS BY INDEX ROWID T1 (cr=1803 pr=0 pw=0 time=150071 us)'
STAT #1 id=2 cnt=50000 pid=1 pos=1 obj=114251 op='INDEX RANGE SCAN SYS_C0020571 (cr=143 pr=0 pw=0 time=333 us)'

...
PARSING IN CURSOR #1 len=51 dep=0 uid=31 oct=3 lid=31 tim=1952576477 hv=1321303491 ad='94ec5058'
...
PARSE #1:c=0,e=100,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=2,tim=1952576474
...
STAT #1 id=1 cnt=50000 pid=0 pos=1 obj=114250 op='TABLE ACCESS BY INDEX ROWID T1 (cr=1803 pr=0 pw=0 time=150075 us)'
STAT #1 id=2 cnt=50000 pid=1 pos=1 obj=114251 op='INDEX RANGE SCAN SYS_C0020571 (cr=143 pr=0 pw=0 time=285 us)'
...
PARSING IN CURSOR #1 len=51 dep=0 uid=31 oct=3 lid=31 tim=1961574186 hv=1321303491 ad='94ec5058'
...
PARSE #1:c=0,e=58,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=2,tim=1961574184
...
STAT #1 id=1 cnt=50000 pid=0 pos=1 obj=114250 op='TABLE ACCESS BY INDEX ROWID T1 (cr=1803 pr=0 pw=0 time=150075 us)'
STAT #1 id=2 cnt=50000 pid=1 pos=1 obj=114251 op='INDEX RANGE SCAN SYS_C0020571 (cr=143 pr=0 pw=0 time=298 us)'
...
PARSING IN CURSOR #1 len=51 dep=0 uid=31 oct=3 lid=31 tim=1970536100 hv=1321303491 ad='94ec5058'
...
PARSE #1:c=0,e=28,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=2,tim=1970536097
...
STAT #1 id=1 cnt=50000 pid=0 pos=1 obj=114250 op='TABLE ACCESS BY INDEX ROWID T1 (cr=1803 pr=0 pw=0 time=150075 us)'
STAT #1 id=2 cnt=50000 pid=1 pos=1 obj=114251 op='INDEX RANGE SCAN SYS_C0020571 (cr=143 pr=0 pw=0 time=294 us)'

If you are able to determine the SQL_ID based on the contents of the trace file, you could do something like this:

SELECT
  CHILD_NUMBER CN,
  NAME,
  VALUE,
  ISDEFAULT DEF
FROM
  V$SQL_OPTIMIZER_ENV
WHERE
  SQL_ID='fnk3z1j7c2zf3'
ORDER BY
  CHILD_NUMBER,
  NAME;

 CN NAME                          VALUE           DEF
--- ----------------------------- --------------- ---
  0 active_instance_count         1               YES
  0 bitmap_merge_area_size        1048576         YES
...

Unfortunately, the 10.2.0.4 trace file lacks the SQL_ID, so we need an extra trip into V$SQL:

SELECT
  SE.SQL_ID,
  SE.CHILD_NUMBER CN,
  SE.ADDRESS,
  SE.NAME,
  SE.VALUE,
  SE.ISDEFAULT DEF
FROM
  V$SQL_OPTIMIZER_ENV SE,
  V$SQL S
WHERE
  S.HASH_VALUE=1321303491
  AND S.SQL_ID=SE.SQL_ID
  AND S.CHILD_NUMBER=SE.CHILD_NUMBER
  AND S.ADDRESS=SE.ADDRESS
ORDER BY
  SE.CHILD_NUMBER,
  SE.NAME;

SQL_ID         CN ADDRESS          NAME                          VALUE           DEF
------------- --- ---------------- ----------------------------- --------------- ---
fnk3z1j7c2zf3   0 000007FF94EC5058 active_instance_count         1               YES
fnk3z1j7c2zf3   0 000007FF94EC5058 bitmap_merge_area_size        1048576         YES
fnk3z1j7c2zf3   0 000007FF94EC5058 cpu_count                     4               YES
fnk3z1j7c2zf3   0 000007FF94EC5058 cursor_sharing                exact           YES
fnk3z1j7c2zf3   0 000007FF94EC5058 hash_area_size                131072          NO
fnk3z1j7c2zf3   0 000007FF94EC5058 optimizer_dynamic_sampling    2               YES
fnk3z1j7c2zf3   0 000007FF94EC5058 optimizer_features_enable     10.2.0.4        NO
fnk3z1j7c2zf3   0 000007FF94EC5058 optimizer_index_caching       0               YES
fnk3z1j7c2zf3   0 000007FF94EC5058 optimizer_index_cost_adj      100             YES
fnk3z1j7c2zf3   0 000007FF94EC5058 optimizer_mode                all_rows        YES
fnk3z1j7c2zf3   0 000007FF94EC5058 optimizer_secure_view_merging true            YES
fnk3z1j7c2zf3   0 000007FF94EC5058 parallel_ddl_mode             enabled         YES
fnk3z1j7c2zf3   0 000007FF94EC5058 parallel_dml_mode             disabled        YES
fnk3z1j7c2zf3   0 000007FF94EC5058 parallel_execution_enabled    false           YES
fnk3z1j7c2zf3   0 000007FF94EC5058 parallel_query_mode           enabled         YES
fnk3z1j7c2zf3   0 000007FF94EC5058 parallel_threads_per_cpu      2               YES
fnk3z1j7c2zf3   0 000007FF94EC5058 pga_aggregate_target          204800 KB       YES
fnk3z1j7c2zf3   0 000007FF94EC5058 query_rewrite_enabled         true            YES
fnk3z1j7c2zf3   0 000007FF94EC5058 query_rewrite_integrity       enforced        YES
fnk3z1j7c2zf3   0 000007FF94EC5058 skip_unusable_indexes         true            YES
fnk3z1j7c2zf3   0 000007FF94EC5058 sort_area_retained_size       0               YES
fnk3z1j7c2zf3   0 000007FF94EC5058 sort_area_size                65536           YES
fnk3z1j7c2zf3   0 000007FF94EC5058 sqlstat_enabled               true            NO
fnk3z1j7c2zf3   0 000007FF94EC5058 star_transformation_enabled   false           YES
fnk3z1j7c2zf3   0 000007FF94EC5058 statistics_level              typical         YES
fnk3z1j7c2zf3   0 000007FF94EC5058 workarea_size_policy          auto            YES
fnk3z1j7c2zf3   1 000007FF94EC5058 active_instance_count         1               YES
fnk3z1j7c2zf3   1 000007FF94EC5058 bitmap_merge_area_size        1048576         YES
fnk3z1j7c2zf3   1 000007FF94EC5058 cpu_count                     4               YES
fnk3z1j7c2zf3   1 000007FF94EC5058 cursor_sharing                exact           YES
fnk3z1j7c2zf3   1 000007FF94EC5058 hash_area_size                131072          NO
fnk3z1j7c2zf3   1 000007FF94EC5058 optimizer_dynamic_sampling    2               YES
fnk3z1j7c2zf3   1 000007FF94EC5058 optimizer_features_enable     10.2.0.4        NO
fnk3z1j7c2zf3   1 000007FF94EC5058 optimizer_index_caching       0               YES
fnk3z1j7c2zf3   1 000007FF94EC5058 optimizer_index_cost_adj      100             YES
fnk3z1j7c2zf3   1 000007FF94EC5058 optimizer_mode                first_rows_1    NO
fnk3z1j7c2zf3   1 000007FF94EC5058 optimizer_secure_view_merging true            YES
fnk3z1j7c2zf3   1 000007FF94EC5058 parallel_ddl_mode             enabled         YES
fnk3z1j7c2zf3   1 000007FF94EC5058 parallel_dml_mode             disabled        YES
fnk3z1j7c2zf3   1 000007FF94EC5058 parallel_execution_enabled    false           YES
fnk3z1j7c2zf3   1 000007FF94EC5058 parallel_query_mode           enabled         YES
fnk3z1j7c2zf3   1 000007FF94EC5058 parallel_threads_per_cpu      2               YES
fnk3z1j7c2zf3   1 000007FF94EC5058 pga_aggregate_target          204800 KB       YES
fnk3z1j7c2zf3   1 000007FF94EC5058 query_rewrite_enabled         true            YES
fnk3z1j7c2zf3   1 000007FF94EC5058 query_rewrite_integrity       enforced        YES
fnk3z1j7c2zf3   1 000007FF94EC5058 skip_unusable_indexes         true            YES
fnk3z1j7c2zf3   1 000007FF94EC5058 sort_area_retained_size       0               YES
fnk3z1j7c2zf3   1 000007FF94EC5058 sort_area_size                65536           YES
fnk3z1j7c2zf3   1 000007FF94EC5058 sqlstat_enabled               true            NO
fnk3z1j7c2zf3   1 000007FF94EC5058 star_transformation_enabled   false           YES
fnk3z1j7c2zf3   1 000007FF94EC5058 statistics_level              typical         YES
fnk3z1j7c2zf3   1 000007FF94EC5058 workarea_size_policy          auto            YES

From the above, we see that there were only two child cursors in the library cache for our SQL statement.  Changing the OPTIMIZER_MODE between the various first rows modes did not trigger the generation of a new child cursor (this same behavior is present on Oracle 11.1.0.7 also), so the OPTIMIZER_MODE recorded in V$SQL_OPTIMIZER_ENV is first_rows_1.

A 10046 trace file captured with Oracle 11.1.0.7, executing the SQL statement with just the ALL_ROWS and FIRST_ROWS_1000 OPTIMIZER_MODEs, included the following:

PARSING IN CURSOR #14 len=51 dep=0 uid=60 oct=3 lid=60 tim=5884684690220 hv=1321303491 ad='23b698b8' sqlid='fnk3z1j7c2zf3'
...
PARSE #14:c=31250,e=43048,p=0,cr=140,cu=0,mis=1,r=0,dep=0,og=1,plh=3617692013,tim=5884684690220
...
STAT #14 id=1 cnt=50000 pid=0 pos=1 obj=86190 op='TABLE ACCESS FULL T1 (cr=6497 pr=3268 pw=0 time=0 us cost=890 size=700000 card=50000)'
...
PARSING IN CURSOR #17 len=51 dep=0 uid=60 oct=3 lid=60 tim=5884708721111 hv=1321303491 ad='23b698b8' sqlid='fnk3z1j7c2zf3'
...
PARSE #17:c=15625,e=31248,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=2,plh=4259601085,tim=5884708721111
...
STAT #17 id=1 cnt=50000 pid=0 pos=1 obj=86190 op='TABLE ACCESS BY INDEX ROWID T1 (cr=8260 pr=0 pw=0 time=0 us cost=37 size=14028 card=1002)'
STAT #17 id=2 cnt=50000 pid=1 pos=1 obj=86191 op='INDEX RANGE SCAN SYS_C0015323 (cr=3422 pr=0 pw=0 time=0 us cost=4 size=0 card=0)'

Since the SQL_ID was written to the 10046 trace file, we are able to see the optimizer parameters in effect for the query with the following SQL statement:

SELECT
  CHILD_NUMBER CN,
  NAME,
  VALUE,
  ISDEFAULT DEF
FROM
  V$SQL_OPTIMIZER_ENV
WHERE
  SQL_ID='fnk3z1j7c2zf3'
ORDER BY
  CHILD_NUMBER,
  NAME;

 CN NAME                                  VALUE           DEF
--- ------------------------------------- --------------- ---
  0 _optim_peek_user_binds                false           NO
  0 _optimizer_adaptive_cursor_sharing    false           NO
  0 active_instance_count                 1               YES
  0 bitmap_merge_area_size                1048576         YES
  0 cell_offload_compaction               ADAPTIVE        YES
  0 cell_offload_plan_display             AUTO            YES
  0 cell_offload_processing               true            YES
  0 cpu_count                             2               YES
  0 cursor_sharing                        exact           YES
  0 db_file_multiblock_read_count         32              YES
  0 hash_area_size                        131072          YES
  0 is_recur_flags                        0               YES
  0 optimizer_capture_sql_plan_baselines  false           YES
  0 optimizer_dynamic_sampling            2               YES
  0 optimizer_features_enable             11.1.0.7        YES
  0 optimizer_index_caching               0               YES
  0 optimizer_index_cost_adj              100             YES
  0 optimizer_mode                        all_rows        YES
  0 optimizer_secure_view_merging         true            YES
  0 optimizer_use_invisible_indexes       false           YES
  0 optimizer_use_pending_statistics      false           YES
  0 optimizer_use_sql_plan_baselines      true            YES
  0 parallel_ddl_mode                     enabled         YES
  0 parallel_degree                       0               YES
  0 parallel_dml_mode                     disabled        YES
  0 parallel_execution_enabled            false           YES
  0 parallel_query_default_dop            0               YES
  0 parallel_query_mode                   enabled         YES
  0 parallel_threads_per_cpu              2               YES
  0 pga_aggregate_target                  143360 KB       YES
  0 query_rewrite_enabled                 true            YES
  0 query_rewrite_integrity               enforced        YES
  0 result_cache_mode                     MANUAL          YES
  0 skip_unusable_indexes                 true            YES
  0 sort_area_retained_size               0               YES
  0 sort_area_size                        65536           YES
  0 sqlstat_enabled                       true            NO
  0 star_transformation_enabled           false           YES
  0 statistics_level                      typical         YES
  0 transaction_isolation_level           read_commited   YES
  0 workarea_size_policy                  auto            YES
  1 _optim_peek_user_binds                false           NO
  1 _optimizer_adaptive_cursor_sharing    false           NO
  1 active_instance_count                 1               YES
  1 bitmap_merge_area_size                1048576         YES
  1 cell_offload_compaction               ADAPTIVE        YES
  1 cell_offload_plan_display             AUTO            YES
  1 cell_offload_processing               true            YES
  1 cpu_count                             2               YES
  1 cursor_sharing                        exact           YES
  1 db_file_multiblock_read_count         32              YES
  1 hash_area_size                        131072          YES
  1 is_recur_flags                        0               YES
  1 optimizer_capture_sql_plan_baselines  false           YES
  1 optimizer_dynamic_sampling            2               YES
  1 optimizer_features_enable             11.1.0.7        YES
  1 optimizer_index_caching               0               YES
  1 optimizer_index_cost_adj              100             YES
  1 optimizer_mode                        first_rows_1000 NO
  1 optimizer_secure_view_merging         true            YES
  1 optimizer_use_invisible_indexes       false           YES
  1 optimizer_use_pending_statistics      false           YES
  1 optimizer_use_sql_plan_baselines      true            YES
  1 parallel_ddl_mode                     enabled         YES
  1 parallel_degree                       0               YES
  1 parallel_dml_mode                     disabled        YES
  1 parallel_execution_enabled            false           YES
  1 parallel_query_default_dop            0               YES
  1 parallel_query_mode                   enabled         YES
  1 parallel_threads_per_cpu              2               YES
  1 pga_aggregate_target                  143360 KB       YES
  1 query_rewrite_enabled                 true            YES
  1 query_rewrite_integrity               enforced        YES
  1 result_cache_mode                     MANUAL          YES
  1 skip_unusable_indexes                 true            YES
  1 sort_area_retained_size               0               YES
  1 sort_area_size                        65536           YES
  1 sqlstat_enabled                       true            NO
  1 star_transformation_enabled           false           YES
  1 statistics_level                      typical         YES
  1 transaction_isolation_level           read_commited   YES
  1 workarea_size_policy                  auto            YES

As long as someone did not use more than one of the first rows OPTIMIZER_MODEs, the above query will show the OPTIMIZER_MODE that was in effect at the time of the hard parse.  Of course, if another of the above parameters was also changed when the OPTIMIZER_MODE changed, there is a very good chance that a new child cursor would have been generated.





Plan Cardinality Estimates Problem with 11.1.0.7 and 11.2.0.1

15 02 2010

February 15, 2010

Here is a fun test on Oracle Database 11.1.0.7 and 11.2.0.1 (all posted tests are from 11.2.0.1).

First, we will create a table and collect statistics for the table with indexes on a couple of the columns and histograms on a couple of the columns:

CREATE TABLE T1 (
  C1 NUMBER NOT NULL,
  C2 NUMBER NOT NULL,
  C3 NUMBER NOT NULL,
  C4 NUMBER NOT NULL,
  C5 VARCHAR2(30) NOT NULL,
  C6 VARCHAR2(30) NOT NULL,
  FILLER VARCHAR2(200),
  PRIMARY KEY (C1));

INSERT INTO T1
SELECT
  ROWNUM,
  ROWNUM,
  TRUNC(ROWNUM/100+1),
  TRUNC(ROWNUM/100+1),
  CHR(65+TRUNC(ROWNUM/10000))||TRUNC(ROWNUM/100+1),
  CHR(65+TRUNC(ROWNUM/10000))||TRUNC(ROWNUM/100+1),
  LPAD('A',200,'A')
FROM
  DUAL
CONNECT BY
  LEVEL<=100000;

COMMIT;

CREATE INDEX IND_T1_C3 ON T1(C3);
CREATE INDEX IND_T1_C4 ON T1(C4);
CREATE INDEX IND_T1_C5 ON T1(C5);

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1',CASCADE=>TRUE,ESTIMATE_PERCENT=>100,METHOD_OPT=>'FOR COLUMNS SIZE 254 C2, C4, C6')

Just for a review of that GATHER_TABLE_STATS procedure, from the documentation:

When gathering statistics on a table, DBMS_STATS gathers information about the data distribution of the columns within the table. The most basic information about the data distribution is the maximum value and minimum value of the column. However, this level of statistics may be insufficient for the optimizer’s needs if the data within the column is skewed. For skewed data distributions, histograms can also be created as part of the column statistics to describe the data distribution of a given column. Histograms are described in more details in “Viewing Histograms“.

Histograms are specified using the METHOD_OPT argument of the DBMS_STATS gathering procedures. Oracle recommends setting the METHOD_OPT to FOR ALL COLUMNS SIZE AUTO. With this setting, Oracle Database automatically determines which columns require histograms and the number of buckets (size) of each histogram. You can also manually specify which columns should have histograms and the size of each histogram.

The table created by the above script will have 100,000 rows with indexes on columns C3, C4, and C5.  Columns C2, C4, and C6 will have histograms with 254 buckets.  Let’s see the maximum values for the table columns (column C2 values are identical to C1, column C4 values are identical to C3, column C6 values are identical to C5)):

SELECT
  MAX(C1) MAX_C1,
  MAX(C3) MAX_C3,
  MAX(C5) MAX_C5
FROM
  T1;

MAX_C1     MAX_C3 MAX_C5
------ ---------- ------
100000       1001 K1001

The maximum value for column C3 is 1001 – most of the distinct values in that column have 100 matching rows, except for the value 1001 which has a single row.  We will try a couple of tests with my VBS tool for automatically generating DBMS_XPLANs, with the DBMS_XPLAN Type set to “ALLSTATS LAST” and Stats Level set to “ALL“.  First, we will try the maximum value for column C3 (and also C4):

SELECT
  *
FROM
  T1
WHERE
  C3=1001;

The returned execution plan is displayed below (after this point the execution plan will be displayed directly below the SQL statement):

SQL_ID  0vcvak7bgbdzt, child number 0
-------------------------------------
SELECT    *  FROM    T1  WHERE    C3=1001

Plan hash value: 1220227203

---------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |      1 |        |      1 |00:00:00.01 |       3 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1        |      1 |   1000 |      1 |00:00:00.01 |       3 |
|*  2 |   INDEX RANGE SCAN          | IND_T1_C3 |      1 |    400 |      1 |00:00:00.01 |       2 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C3"=1001)

OK, it was more or less expected that the cardinality estimate would be incorrect here because there is no histogram on column C3.  It is a bit odd that the optimizer predicts that the index will return 400 rows that then causes 1000 rows to be returned from the table.  Now let’s try the same SQL statement referencing column C4:

SELECT
  *
FROM
  T1
WHERE
  C4=1001;

SQL_ID  d3zfa447tsjrz, child number 0
-------------------------------------
SELECT    *  FROM    T1  WHERE    C4=1001

Plan hash value: 7035821

---------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |      1 |        |      1 |00:00:00.01 |       3 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1        |      1 |    100 |      1 |00:00:00.01 |       3 |
|*  2 |   INDEX RANGE SCAN          | IND_T1_C4 |      1 |    100 |      1 |00:00:00.01 |       2 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C4"=1001)

100 is closer to the actual number of rows than were predicted for the same SQL statement using column C3, so the histogram probably helped.  What happens when we specify values for C3 and C4 that exceed the maximum values in those columns?

SELECT
  *
FROM
  T1
WHERE
  C3=1101;

SQL_ID  7hy399svng33n, child number 0
-------------------------------------
SELECT    *  FROM    T1  WHERE    C3=1101

Plan hash value: 1220227203

---------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |      1 |        |      0 |00:00:00.01 |       2 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1        |      1 |   1000 |      0 |00:00:00.01 |       2 |
|*  2 |   INDEX RANGE SCAN          | IND_T1_C3 |      1 |    400 |      0 |00:00:00.01 |       2 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C3"=1101)

-

SELECT
  *
FROM
  T1
WHERE
  C4=1101;

SQL_ID  at676unwj7uk1, child number 0
-------------------------------------
SELECT    *  FROM    T1  WHERE    C4=1101

Plan hash value: 7035821

---------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |      1 |        |      0 |00:00:00.01 |       2 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1        |      1 |     90 |      0 |00:00:00.01 |       2 |
|*  2 |   INDEX RANGE SCAN          | IND_T1_C4 |      1 |     90 |      0 |00:00:00.01 |       2 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C4"=1101)

In the above, the SQL statement that accesses column C3 continued to estimate the same number of rows would be returned when the value exceeded the maximum value for the column by roughly 10%.  When the same restriction was applied to column C4, the optimizer predicted that 10% less rows would be returned.  Interesting…

SELECT
  *
FROM
  T1
WHERE
  C3=1201;

SQL_ID  f94b21zwvsn11, child number 0
-------------------------------------
SELECT    *  FROM    T1  WHERE    C3=1201

Plan hash value: 1220227203

---------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |      1 |        |      0 |00:00:00.01 |       2 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1        |      1 |   1000 |      0 |00:00:00.01 |       2 |
|*  2 |   INDEX RANGE SCAN          | IND_T1_C3 |      1 |    400 |      0 |00:00:00.01 |       2 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C3"=1201)

-

SELECT
  *
FROM
  T1
WHERE
  C4=1201;

SQL_ID  4sf3hjx44u1sn, child number 0
-------------------------------------
SELECT    *  FROM    T1  WHERE    C4=1201

Plan hash value: 7035821

---------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |      1 |        |      0 |00:00:00.01 |       2 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1        |      1 |     80 |      0 |00:00:00.01 |       2 |
|*  2 |   INDEX RANGE SCAN          | IND_T1_C4 |      1 |     80 |      0 |00:00:00.01 |       2 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C4"=1201)

Exceeding the maximum value by 20% returned the same cardinality estimate for the SQL statement using column C3 as seen earlier, while the cardinality estimate decreased by 20% for the SQL statement accessing column C4.

SELECT
  *
FROM
  T1
WHERE
  C3=1901;

SQL_ID  86z7nbb5u2p26, child number 0
-------------------------------------
SELECT    *  FROM    T1  WHERE    C3=1901

Plan hash value: 1220227203

---------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |      1 |        |      0 |00:00:00.01 |       2 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1        |      1 |   1000 |      0 |00:00:00.01 |       2 |
|*  2 |   INDEX RANGE SCAN          | IND_T1_C3 |      1 |    400 |      0 |00:00:00.01 |       2 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C3"=1901)

-

SELECT
  *
FROM
  T1
WHERE
  C4=1901;

SQL_ID  08rg4uf562h4x, child number 0
-------------------------------------
SELECT    *  FROM    T1  WHERE    C4=1901

Plan hash value: 7035821

---------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |      1 |        |      0 |00:00:00.01 |       2 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1        |      1 |     10 |      0 |00:00:00.01 |       2 |
|*  2 |   INDEX RANGE SCAN          | IND_T1_C4 |      1 |     10 |      0 |00:00:00.01 |       2 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C4"=1901)

When exceeding the maximum value by 90% we see the same pattern, the cardinality estimates for the first SQL statement were unaffected, while the cardinality estimate for the second SQL statement decreased by 90%.  With a value of 2001 specified for column C4 the optimizer’s predicted cardinality decreased to 1 row.

What about value ranges?

SELECT
  *
FROM
  T1
WHERE
  C3 BETWEEN 1101 AND 1201;

SQL_ID  95zzq8vb523gf, child number 0
-------------------------------------
SELECT    *  FROM    T1  WHERE    C3 BETWEEN 1101 AND 1201

Plan hash value: 1220227203

---------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |      1 |        |      0 |00:00:00.01 |       2 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1        |      1 |    250 |      0 |00:00:00.01 |       2 |
|*  2 |   INDEX RANGE SCAN          | IND_T1_C3 |      1 |    450 |      0 |00:00:00.01 |       2 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C3">=1101 AND "C3"<=1201)

Notice that the optimizer is estimating that 250 rows will be returned, which is less than the sum of the estimated 1,000 rows that would be returned for the value 1101 and the estimated 1,000 rows for value 1201.  Let’s try again specifying column C4:

SELECT
  *
FROM
  T1
WHERE
  C4 BETWEEN 1101 AND 1201;

SQL_ID  9t2fv8dcz7jsv, child number 0
-------------------------------------
SELECT    *  FROM    T1  WHERE    C4 BETWEEN 1101 AND 1201

Plan hash value: 7035821

---------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |      1 |        |      0 |00:00:00.01 |       2 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1        |      1 |    100 |      0 |00:00:00.01 |       2 |
|*  2 |   INDEX RANGE SCAN          | IND_T1_C4 |      1 |     90 |      0 |00:00:00.01 |       2 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C4">=1101 AND "C4"<=1201)

Interesting – the index is predicted to return 90 rows which then causes the table to return 100 rows (edit: Oracle 10.2.0.2 predicts 90 rows for both the index and the table).  Let’s try again specifying ranges that exceed the maximum values by 90% to 100%:

SELECT
  *
FROM
  T1
WHERE
  C3 BETWEEN 1901 AND 2001;

SQL_ID  99btm350uvvbp, child number 0
-------------------------------------
SELECT    *  FROM    T1  WHERE    C3 BETWEEN 1901 AND 2001

Plan hash value: 1220227203

---------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |      1 |        |      0 |00:00:00.01 |       2 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1        |      1 |    250 |      0 |00:00:00.01 |       2 |
|*  2 |   INDEX RANGE SCAN          | IND_T1_C3 |      1 |    450 |      0 |00:00:00.01 |       2 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C3">=1901 AND "C3"<=2001)

-

SELECT
  *
FROM
  T1
WHERE
  C4 BETWEEN 1901 AND 2001;

SQL_ID  bnwyf98m74q26, child number 0
-------------------------------------
SELECT    *  FROM    T1  WHERE    C4 BETWEEN 1901 AND 2001

Plan hash value: 7035821

---------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |      1 |        |      0 |00:00:00.01 |       2 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1        |      1 |    100 |      0 |00:00:00.01 |       2 |
|*  2 |   INDEX RANGE SCAN          | IND_T1_C4 |      1 |     10 |      0 |00:00:00.01 |       2 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C4">=1901 AND "C4"<=2001)

The first query is predicting the same number of rows will be returned as seen earlier.  The second query is again predicting that 100 rows will be returned from the table as a result of the 10 predicted rows that will be returned from the index (edit: Oracle 10.2.0.2 predicts 10 rows for both the index and the table).  When the range was changed to 1101 through 2001, the same cardinality estimates displayed for the range of 1101 to 1201 were again returned for both SQL statements.

If the above accurately depicts what happens when the maximum recorded value for a column is exceeded, what might happen when statistics are not gathered on a regular basis?  Hold that thought.

Let’s try again using a value range for C3 and C4 that are not beyond the maximum values for the columns:

SELECT
  *
FROM
  T1
WHERE
  C3 BETWEEN 101 AND 201;

SQL_ID  8jd9h693mbkvc, child number 0
-------------------------------------
SELECT    *  FROM    T1  WHERE    C3 BETWEEN 101 AND 201

Plan hash value: 1220227203

---------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |      1 |        |  10100 |00:00:00.02 |     547 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1        |      1 |    250 |  10100 |00:00:00.02 |     547 |
|*  2 |   INDEX RANGE SCAN          | IND_T1_C3 |      1 |    450 |  10100 |00:00:00.01 |     124 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C3">=101 AND "C3"<=201)

-

SELECT
  *
FROM
  T1
WHERE
  C4 BETWEEN 101 AND 201;

SQL_ID  2bk0njs0atfpw, child number 0
-------------------------------------
SELECT    *  FROM    T1  WHERE    C4 BETWEEN 101 AND 201

Plan hash value: 7035821

---------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |      1 |        |  10100 |00:00:00.02 |     547 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1        |      1 |  10039 |  10100 |00:00:00.02 |     547 |
|*  2 |   INDEX RANGE SCAN          | IND_T1_C4 |      1 |  10039 |  10100 |00:00:00.01 |     124 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C4">=101 AND "C4"<=201)

Once again, the first SQL statement is estimating that the index range scan will return 450 rows which will then cause the table to return 250 rows.  In actuality, both the index and the table return 10,100 rows (do we possibly have a statistics problem here?).  The second query returns cardinality estimates that are closer to the actual number of rows returned – the histogram helped here.

Let’s try again with a wider value range:

SELECT
  *
FROM
  T1
WHERE
  C3 BETWEEN 101 AND 1401;

SQL_ID  0bapwrbn3ch8j, child number 0
-------------------------------------
SELECT    *  FROM    T1  WHERE    C3 BETWEEN 101 AND 1401

Plan hash value: 1220227203

---------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |      1 |        |  90001 |00:00:00.13 |    4865 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1        |      1 |    250 |  90001 |00:00:00.13 |    4865 |
|*  2 |   INDEX RANGE SCAN          | IND_T1_C3 |      1 |    450 |  90001 |00:00:00.04 |    1090 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C3">=101 AND "C3"<=1401)

-

SELECT
  *
FROM
  T1
WHERE
  C4 BETWEEN 101 AND 1401;

SQL_ID  3ppn43z5ukur6, child number 0
-------------------------------------
SELECT    *  FROM    T1  WHERE    C4 BETWEEN 101 AND 1401

Plan hash value: 3617692013

------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |  90001 |00:00:00.05 |    4147 |
|*  1 |  TABLE ACCESS FULL| T1   |      1 |  89961 |  90001 |00:00:00.05 |    4147 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(("C4">=101 AND "C4"<=1401))

Again, the cardinality estimates and execution plan are the same as before for the query using column C3, even though we are selecting 90% of the rows in the table.  The query using column C4 switched to a full table scan, and has a much more accurate cardinality estimate.  This same pattern holds true regardless of the values specified for the low and high ends of the range.

Let’s switch back to SQL*Plus and try a couple of experiments with bind variables (note that I am actually submitting the SQL statements using my Toy Project so that I do not have to watch all of the rows scroll on the screen):

VARIABLE N1 NUMBER
VARIABLE N2 NUMBER

ALTER SESSION SET STATISTICS_LEVEL='ALL';

EXEC :N1:=101
EXEC :N2:=1401

SET ARRAYSIZE 100

SELECT
  *
FROM
  T1
WHERE
  C3 BETWEEN :N1 AND :N2;

...

SELECT
  *
FROM
  TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

SQL_ID  2ksn64btq6fx4, child number 0
-------------------------------------
SELECT    *  FROM    T1  WHERE    C3 BETWEEN :1 AND :2

Plan hash value: 108045900

----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |      1 |        |  90001 |00:00:00.18 |    4865 |
|*  1 |  FILTER                      |           |      1 |        |  90001 |00:00:00.18 |    4865 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1        |      1 |    250 |  90001 |00:00:00.13 |    4865 |
|*  3 |    INDEX RANGE SCAN          | IND_T1_C3 |      1 |    450 |  90001 |00:00:00.04 |    1090 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(:1<=:2)
   3 - access("C3">=:1 AND "C3"<=:2)

The same execution plan as returned when we used literals (constants), so bind peeking is probably working.  Now let’s try the query that accesses column C4:

SELECT
  *
FROM
  T1
WHERE
  C4 BETWEEN :N1 AND :N2;

...

SELECT
  *
FROM
  TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

SQL_ID  gwgk5h8u3k4tp, child number 0
-------------------------------------
SELECT    *  FROM    T1  WHERE    C4 BETWEEN :1 AND :2

Plan hash value: 3332582666

-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |  90001 |00:00:00.10 |    4147 |
|*  1 |  FILTER            |      |      1 |        |  90001 |00:00:00.10 |    4147 |
|*  2 |   TABLE ACCESS FULL| T1   |      1 |  89961 |  90001 |00:00:00.05 |    4147 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(:1<=:2)
   2 - filter(("C4">=:1 AND "C4"<=:2))

Bind variable peeking was used to obtain a close estimate for the cardinalities.  So, what happens when we change the value of bind variable N2?

EXEC :N2:=101

SELECT
  *
FROM
  T1
WHERE
  C3 BETWEEN :N1 AND :N2;

SELECT
  *
FROM
  TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

SQL_ID  2ksn64btq6fx4, child number 0
-------------------------------------
SELECT    *  FROM    T1  WHERE    C3 BETWEEN :1 AND :2

Plan hash value: 108045900

----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |      1 |        |    100 |00:00:00.01 |       7 |
|*  1 |  FILTER                      |           |      1 |        |    100 |00:00:00.01 |       7 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1        |      1 |    250 |    100 |00:00:00.01 |       7 |
|*  3 |    INDEX RANGE SCAN          | IND_T1_C3 |      1 |    450 |    100 |00:00:00.01 |       3 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(:1<=:2)
   3 - access("C3">=:1 AND "C3"<=:2)

-

SELECT
  *
FROM
  T1
WHERE
  C3 BETWEEN :N1 AND :N2;

SELECT
  *
FROM
  TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

SQL_ID  gwgk5h8u3k4tp, child number 0
-------------------------------------
SELECT    *  FROM    T1  WHERE    C4 BETWEEN :1 AND :2

Plan hash value: 3332582666

-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |    100 |00:00:00.01 |    3263 |
|*  1 |  FILTER            |      |      1 |        |    100 |00:00:00.01 |    3263 |
|*  2 |   TABLE ACCESS FULL| T1   |      1 |  89961 |    100 |00:00:00.01 |    3263 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(:1<=:2)
   2 - filter(("C4">=:1 AND "C4"<=:2))

The bind variable in the presence of bind peeking with a histogram came back to bit us – ideally Oracle would have used an index range scan for this set of bind variables.

Let’s add another 10,000 rows to the table without gathering statistics and see what happens to the cardinality estimates in the plans:

INSERT INTO T1
SELECT
  (100000+ROWNUM),
  (100000+ROWNUM),
  TRUNC((100000+ROWNUM)/100+1),
  TRUNC((100000+ROWNUM)/100+1),
  CHR(65+TRUNC((100000+ROWNUM)/10000))||TRUNC((100000+ROWNUM)/100+1),
  CHR(65+TRUNC((100000+ROWNUM)/10000))||TRUNC((100000+ROWNUM)/100+1),
  LPAD('A',200,'A')
FROM
  DUAL
CONNECT BY
  LEVEL<=100000;

COMMIT;

ALTER SYSTEM FLUSH SHARED_POOL;

Here are the execution plans:

SELECT
  *
FROM
  T1
WHERE
  C3=1901;

SQL_ID  86z7nbb5u2p26, child number 0
-------------------------------------
SELECT    *  FROM    T1  WHERE    C3=1901

Plan hash value: 1220227203

---------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |      1 |        |    100 |00:00:00.01 |       8 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1        |      1 |   1000 |    100 |00:00:00.01 |       8 |
|*  2 |   INDEX RANGE SCAN          | IND_T1_C3 |      1 |    400 |    100 |00:00:00.01 |       4 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C3"=1901)

-

SELECT
  *
FROM
  T1
WHERE
  C4=1901;

SQL_ID  08rg4uf562h4x, child number 0
-------------------------------------
SELECT    *  FROM    T1  WHERE    C4=1901

Plan hash value: 7035821

---------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |      1 |        |    100 |00:00:00.01 |       8 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1        |      1 |     10 |    100 |00:00:00.01 |       8 |
|*  2 |   INDEX RANGE SCAN          | IND_T1_C4 |      1 |     10 |    100 |00:00:00.01 |       4 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C4"=1901)

The cardinality estimates are unchanged from what we saw earlier.  The first query is estimating 10 times too many rows will be returned, and the second query is estimating 10 times too few rows will be returned.  Let’s delete the rows that were just added and check a couple of statistics:

DELETE FROM
  T1
WHERE
  C1>100000;

100000 rows deleted.

COMMIT;

SELECT
  SUBSTR(COLUMN_NAME,1,3) COL,
  DENSITY,
  NUM_BUCKETS,
  LAST_ANALYZED
FROM
  DBA_TAB_COLUMNS
WHERE
  TABLE_NAME='T1'
ORDER BY
  1;

COL    DENSITY NUM_BUCKETS LAST_ANAL
--- ---------- ----------- ---------
C1
C2      .00001         254 14-FEB-10
C3
C4   .00099998         254 14-FEB-10
C5
C6   .00099998         254 14-FEB-10
FIL

No density values for columns C1, C3, or C5 and the LAST_ANALYZED column is NULL for those same entries (edit: same results on Oracle 10.2.0.2).  Let’s try collecting statistics again without specifying the columns for which histograms should be created:

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1',CASCADE=>TRUE,ESTIMATE_PERCENT=>100,NO_INVALIDATE=>FALSE)

SELECT
  SUBSTR(COLUMN_NAME,1,3) COL,
  DENSITY,
  NUM_BUCKETS,
  LAST_ANALYZED
FROM
  DBA_TAB_COLUMNS
WHERE
  TABLE_NAME='T1'
ORDER BY
  1;

COL    DENSITY NUM_BUCKETS LAST_ANAL
--- ---------- ----------- ---------
C1      .00001           1 14-FEB-10
C2      .00001           1 14-FEB-10
C3  .000999001           1 14-FEB-10
C4  .000999001           1 14-FEB-10
C5  .000999001           1 14-FEB-10
C6  .000999001           1 14-FEB-10
FIL          1           1 14-FEB-10

Now we have no histograms, but the density and LAST_ANALYZED columns are populated for all rows returned by the above query (edit: same results on Oracle 10.2.0.2).  For fun let’s retry one of the queries that returned odd cardinality estimates earlier:

SELECT
  *
FROM
  T1
WHERE
  C3 BETWEEN 101 AND 201;

SQL_ID  8jd9h693mbkvc, child number 0
-------------------------------------
SELECT    *  FROM    T1  WHERE    C3 BETWEEN 101 AND 201

Plan hash value: 1220227203

---------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |      1 |        |  10100 |00:00:00.02 |     547 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1        |      1 |  10200 |  10100 |00:00:00.02 |     547 |
|*  2 |   INDEX RANGE SCAN          | IND_T1_C3 |      1 |  10200 |  10100 |00:00:00.01 |     124 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C3">=101 AND "C3"<=201)

The estimated cardinality is just about as accurate as it was with the query that accessed column C4 which had a histogram.

What are your thoughts?  Is the METHOD_OPT parameter of DBMS_STATS.GATHER_TABLE_STATS only used to specify how histograms will be collected for columns, or does that parameter also specify for which columns column-level statistics should be gathered?  If you again collect statistics using the DBMS_STATS.GATHER_TABLE_STATS command found near the start of this article, the query of DBA_TAB_COLUMNS returns the following, which is what was originally expected (edit: same results on Oracle 10.2.0.2):

COL    DENSITY NUM_BUCKETS LAST_ANAL
--- ---------- ----------- ---------
C1      .00001           1 14-FEB-10
C2      .00001         254 14-FEB-10
C3  .000999001           1 14-FEB-10
C4   .00099998         254 14-FEB-10
C5  .000999001           1 14-FEB-10
C6   .00099998         254 14-FEB-10
FIL          1           1 14-FEB-10

——-

Follow-up (8 hours after this article was scheduled to appear):

It is important at times to check more than one source in the Oracle documentation (bold/italic emphasis is mine):

“METHOD_OPT – The value controls column statistics collection and histogram creation.”

Here is an example where Christian Antognini tried to drive that point home:

“Since the syntax is FOR ALL INDEXED COLUMNS, you are gathering statistics for all indexed columns only. I.e. not for all columns. FOR ALL COLUMNS should be used for that…”

“When the option FOR ALL INDEXED COLUMNS is specified, columns statistics are gathered only for the indexed columns.”

“My point was that the parameter method_opt not only impacts histograms, but also column statistics.”

Greg Rahn also made the point in this blog article.

“The METHOD_OPT parameter of DBMS_STATS controls two things:

  1. on which columns statistics will be collected
  2. on which columns histograms will be collected (and how many buckets)”

The message of this blog article is to make certain that you know what the GATHER_TABLE_STATS procedure is actually doing when you use the METHOD_OPT parameter (or alter the default value for the parameter).  The apparent benefit from having the histogram in place might actually be a false benefit – it might be that previously you were not updating the column statistics for that column, until you started creating a histogram on that column (assuming that you were previously collecting histograms on other columns in the table, and you assumed that column statistics were updated for the remaining columns).

Incidentally, if instead of using this to collect statistics for the table:

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1',CASCADE=>TRUE,ESTIMATE_PERCENT=>100,METHOD_OPT=>'FOR COLUMNS SIZE 254 C2, C4, C6')

You used this:

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1',CASCADE=>TRUE,ESTIMATE_PERCENT=>100,METHOD_OPT=>'FOR ALL INDEXED COLUMNS size skewonly')

You would end up with the following when executing the query against DBA_TAB_COLUMNS:

COL    DENSITY NUM_BUCKETS LAST_ANAL
--- ---------- ----------- ---------
C1      .00001           1 15-FEB-10
C2
C3  .000999001           1 15-FEB-10
C4  .000999001           1 15-FEB-10
C5   .00099998         254 15-FEB-10
C6
FIL

The above output confirms Christian Antognini’s comment in the OTN thread – column-level statistics were not collected for columns C2, C6, and FILLER.  There is effectively no histogram on columns C3 and C4 (a single bucket, therefore no histogram), but the cardinality estimates in the plan will be close because column-level statistics are present for columns C3 and C4.





V$FILESTAT is Wrong?

13 02 2010

February 13, 2010

While describing file monitoring in chapter 8 of the Expert Oracle Practices book, one of the interesting items that Randolf and I mentioned was an apparent inconsistency in V$FILESTAT, but we then went on to explain why the inconsistency is present.  To see this inconsistency, I will bounce an 11.1.0.7 database, execute a couple of SQL statements to force physical reads (the SQL statements are not important to this experiment), and then check the current values in V$FILESTAT.  The following script will be executed to show the V$FILESTAT statistics and also the statistics from V$FILE_HISTOGRAM:

spool fileactivity.txt
set linesize 160
set pagesize 2000

SELECT
  FILE#,
  PHYRDS,
  PHYWRTS,
  PHYBLKRD,
  PHYBLKWRT,
  SINGLEBLKRDS,
  READTIM,
  WRITETIM,
  SINGLEBLKRDTIM,
  AVGIOTIM,
  LSTIOTIM,
  MINIOTIM,
  MAXIORTM,
  MAXIOWTM
FROM
  V$FILESTAT
WHERE
  FILE# IN (6,7);

SELECT
  FILE#,
  MAX(DECODE(SINGLEBLKRDTIM_MILLI,1,SINGLEBLKRDS,0)) MILLI1,
  MAX(DECODE(SINGLEBLKRDTIM_MILLI,2,SINGLEBLKRDS,0)) MILLI2,
  MAX(DECODE(SINGLEBLKRDTIM_MILLI,4,SINGLEBLKRDS,0)) MILLI4,
  MAX(DECODE(SINGLEBLKRDTIM_MILLI,8,SINGLEBLKRDS,0)) MILLI8,
  MAX(DECODE(SINGLEBLKRDTIM_MILLI,16,SINGLEBLKRDS,0)) MILLI16,
  MAX(DECODE(SINGLEBLKRDTIM_MILLI,32,SINGLEBLKRDS,0)) MILLI32,
  MAX(DECODE(SINGLEBLKRDTIM_MILLI,64,SINGLEBLKRDS,0)) MILLI64,
  MAX(DECODE(SINGLEBLKRDTIM_MILLI,128,SINGLEBLKRDS,0)) MILLI128,
  MAX(DECODE(SINGLEBLKRDTIM_MILLI,256,SINGLEBLKRDS,0)) MILLI256,
  MAX(DECODE(SINGLEBLKRDTIM_MILLI,512,SINGLEBLKRDS,0)) MILLI512,
  MAX(DECODE(SINGLEBLKRDTIM_MILLI,1024,SINGLEBLKRDS,0)) MILLI1024,
  MAX(DECODE(SINGLEBLKRDTIM_MILLI,2048,SINGLEBLKRDS,0)) MILLI2048,
  MAX(DECODE(SINGLEBLKRDTIM_MILLI,4096,SINGLEBLKRDS,0)) MILLI4096
FROM
  V$FILE_HISTOGRAM
WHERE
  FILE# IN (6,7)
GROUP BY
  FILE#
ORDER BY
  FILE#;

spool off

So, what is the inconsistency?  Here is the output of the above SQL statements:

FILE# PHYRDS PHYWRTS PHYBLKRD PHYBLKWRT SINGLEBLKRDS READTIM WRITETIM SINGLEBLKRDTIM AVGIOTIM LSTIOTIM MINIOTIM MAXIORTM MAXIOWTM
----- ------ ------- -------- --------- ------------ ------- -------- -------------- -------- -------- -------- -------- --------
    6     13       9       13         9            2      25        0              8        0        0        0        9        0
    7  27983   39608   138431    107305        13693    6073  3702202           1719        0        0        0       36      447 

FILE# MILLI1 MILLI2 MILLI4 MILLI8 MILLI16 MILLI32 MILLI64 MILLI128 MILLI256 MILLI512 MILLI1024 MILLI2048 MILLI4096
----- ------ ------ ------ ------ ------- ------- ------- -------- -------- -------- --------- --------- ---------
    6      0      0      0      0       0       0       2        0        0        0         0         0         0
    7  12816    222     68     79     108     304      80       12        3        1         0         0         0

So, what is the inconsistency?  Let’s ignore file #7 for a moment and take a look at file #6.  In the above output, the SINGLEBLKRDS column indicates that there were 2 single block reads of file #6, and the SINGLEBLKRDTIM column indicates that the two single block reads required 8 centiseconds (0.08 seconds).  The V$FILE_HISTOGRAM output shows that there were two block reads of file #6 that completed in the range of 32ms (0.032 seconds) and 63.99999ms (0.06399999 seconds).  Those two sets of statistics are consistent, so where is the inconsistency?

Let’s check a couple of the statistics from the output of V$FILESTAT and check the definitions from the documentation:

 2 SINGLEBLKRDS - Number of single block reads
13 PHYBLKRD - Number of physical blocks read
13 PHYRDS - Number of physical reads done

Is the inconsistency clear now?  If 2 of the 13 blocks that were read from the tablespace were read by single block reads, that means that 11 of the blocks were read by something other than single block reads.  Single block reads, by definition, read one block during each read request, so 2 of the 13 physical reads were read by single block reads and that means 11 of the read request were something other than single block reads.  That means 11 blocks were read in 11 read calls by something other than single block reads – were they read by multi-block reads?  Think about that for a moment.

Do those columns really mean something other than what the documentation states?  Is this another bug in the V$FILESTAT view?  Bugs?  From Metalink:

  • V$FILESTAT WRITETIM Shows Very High Values [ID 168930.1]
  • Bug 4942939 – Very high values for ODM/ASM readtim/writetim in V$FILESTAT [ID 4942939.8]
  • Bug 2481002 – V$FILESTAT.AVGIOTIM is incorrect [ID 2481002.8]

All of the above bugs were fixed prior to Oracle 11.1.0.7.  Maybe the next step is to look for more information about V$FILESTAT:

OK, now that you are back from reading the above links, how is it possible to read 11 blocks in a total of 11 read requests when none of the 11 reads are single block reads?  Maybe another script will help answer that question:

CONNECT SYS/SYSPASSWORD AS SYSDBA

CREATE OR REPLACE TRIGGER STARTUP_10046_TRACE AFTER STARTUP ON DATABASE
BEGIN
  EXECUTE IMMEDIATE 'ALTER SYSTEM SET MAX_DUMP_FILE_SIZE=UNLIMITED';
  EXECUTE IMMEDIATE 'ALTER SYSTEM SET TIMED_STATISTICS=TRUE';
  EXECUTE IMMEDIATE 'ALTER SYSTEM SET EVENTS ''10046 TRACE NAME CONTEXT FOREVER, LEVEL 12''';
END;
/

SHUTDOWN IMMEDIATE;

STARTUP

spool fileactivitystartup.txt
set linesize 160
set pagesize 2000

SELECT
  FILE#,
  PHYRDS,
  PHYWRTS,
  PHYBLKRD,
  PHYBLKWRT,
  SINGLEBLKRDS,
  READTIM,
  WRITETIM,
  SINGLEBLKRDTIM,
  AVGIOTIM,
  LSTIOTIM,
  MINIOTIM,
  MAXIORTM,
  MAXIOWTM
FROM
  V$FILESTAT
WHERE
  FILE# IN (6,7);

SELECT
  FILE#,
  MAX(DECODE(SINGLEBLKRDTIM_MILLI,1,SINGLEBLKRDS,0)) MILLI1,
  MAX(DECODE(SINGLEBLKRDTIM_MILLI,2,SINGLEBLKRDS,0)) MILLI2,
  MAX(DECODE(SINGLEBLKRDTIM_MILLI,4,SINGLEBLKRDS,0)) MILLI4,
  MAX(DECODE(SINGLEBLKRDTIM_MILLI,8,SINGLEBLKRDS,0)) MILLI8,
  MAX(DECODE(SINGLEBLKRDTIM_MILLI,16,SINGLEBLKRDS,0)) MILLI16,
  MAX(DECODE(SINGLEBLKRDTIM_MILLI,32,SINGLEBLKRDS,0)) MILLI32,
  MAX(DECODE(SINGLEBLKRDTIM_MILLI,64,SINGLEBLKRDS,0)) MILLI64,
  MAX(DECODE(SINGLEBLKRDTIM_MILLI,128,SINGLEBLKRDS,0)) MILLI128,
  MAX(DECODE(SINGLEBLKRDTIM_MILLI,256,SINGLEBLKRDS,0)) MILLI256,
  MAX(DECODE(SINGLEBLKRDTIM_MILLI,512,SINGLEBLKRDS,0)) MILLI512,
  MAX(DECODE(SINGLEBLKRDTIM_MILLI,1024,SINGLEBLKRDS,0)) MILLI1024,
  MAX(DECODE(SINGLEBLKRDTIM_MILLI,2048,SINGLEBLKRDS,0)) MILLI2048,
  MAX(DECODE(SINGLEBLKRDTIM_MILLI,4096,SINGLEBLKRDS,0)) MILLI4096
FROM
  V$FILE_HISTOGRAM
WHERE
  FILE# IN (6,7)
GROUP BY
  FILE#
ORDER BY
  FILE#;

spool off

ALTER SYSTEM SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12';

DROP TRIGGER STARTUP_10046_TRACE;

The output of the above script follows:

FILE# PHYRDS PHYWRTS PHYBLKRD PHYBLKWRT SINGLEBLKRDS READTIM WRITETIM SINGLEBLKRDTIM AVGIOTIM LSTIOTIM MINIOTIM MAXIORTM MAXIOWTM
----- ------ ------- -------- --------- ------------ ------- -------- -------------- -------- -------- -------- -------- --------
    6      4       1        4         1            1       2        0              2        0        2        0        2        0
    7      4       1        4         1            1       2        0              2        0        2        0        2        0

FILE# MILLI1 MILLI2 MILLI4 MILLI8 MILLI16 MILLI32 MILLI64 MILLI128 MILLI256 MILLI512 MILLI1024 MILLI2048 MILLI4096
----- ------ ------ ------ ------ ------- ------- ------- -------- -------- -------- --------- --------- ---------
    6      0      0      0      0       0       1       0        0        0        0         0         0         0
    7      0      0      0      0       0       1       0        0        0        0         0         0         0

Again we see the inconsistency SINGLEBLKRDS = 1, PHYBLKRD = 4, PHYRDS = 4 – therefore there were 3 blocks read in 3 physical reads that were not single block reads.  How is this possible?  Maybe we should check the trace files?  The trace file for the session performing the STARTUP command contains the following:

...
WAIT #5: nam='direct path read' ela= 17 file number=1 first dba=1 block cnt=1 obj#=369 tim=7842773692
WAIT #5: nam='direct path read' ela= 11153 file number=2 first dba=1 block cnt=1 obj#=369 tim=7842784875
WAIT #5: nam='direct path read' ela= 19974 file number=3 first dba=1 block cnt=1 obj#=369 tim=7842804886
WAIT #5: nam='direct path read' ela= 1 file number=4 first dba=1 block cnt=1 obj#=369 tim=7842804932
WAIT #5: nam='direct path read' ela= 1 file number=5 first dba=1 block cnt=1 obj#=369 tim=7842804959
WAIT #5: nam='direct path read' ela= 1 file number=6 first dba=1 block cnt=1 obj#=369 tim=7842804981
...

The trace file for LGWR contains the following:

...
WAIT #0: nam='direct path read' ela= 5 file number=1 first dba=1 block cnt=1 obj#=-1 tim=7842890656
WAIT #0: nam='direct path read' ela= 1 file number=2 first dba=1 block cnt=1 obj#=-1 tim=7842890694
WAIT #0: nam='direct path read' ela= 1 file number=3 first dba=1 block cnt=1 obj#=-1 tim=7842890726
WAIT #0: nam='direct path read' ela= 1 file number=4 first dba=1 block cnt=1 obj#=-1 tim=7842890756
WAIT #0: nam='direct path read' ela= 0 file number=5 first dba=1 block cnt=1 obj#=-1 tim=7842890801
WAIT #0: nam='direct path read' ela= 1 file number=6 first dba=1 block cnt=1 obj#=-1 tim=7842890832
...

Let’s see: 1 single block read in the SYS session, 1 single block read in the LGWR trace file, therefore 1=2?  That makes complete sense.  What you probably will not find in the documentation is a statement that the SINGLEBLKRDS column of V$FILESTAT and the statistics in V$FILE_HISTOGRAM correspond to the “db file sequential read” wait event, and do not include statistics for single block reads associated with the “direct path read” wait event.  (At least in this case.)

What about Oracle 11.2.0.1?  Here is the output of the two SQL statements after executing a couple of queries following a database bounce:

FILE# PHYRDS PHYWRTS PHYBLKRD PHYBLKWRT SINGLEBLKRDS READTIM WRITETIM SINGLEBLKRDTIM AVGIOTIM LSTIOTIM MINIOTIM MAXIORTM MAXIOWTM
----- ------ ------- -------- --------- ------------ ------- -------- -------------- -------- -------- -------- -------- --------
    6     18      14       18        14           18       2        0              2        0        0        0        1        0
    7  57047  223616   535618    495511        42430   11588  5689141           4838        0        0        0      218      279

FILE# MILLI1 MILLI2 MILLI4 MILLI8 MILLI16 MILLI32 MILLI64 MILLI128 MILLI256 MILLI512 MILLI1024 MILLI2048 MILLI4096
----- ------ ------ ------ ------ ------- ------- ------- -------- -------- -------- --------- --------- ---------
    6      0      1      0      0       1       0       0        0        0        0         0         0         0
    7  39775    186    342    991     684     235     107       79        7        3         1         2         2

From the above:

18 SINGLEBLKRDS - Number of single block reads
18 PHYBLKRD - Number of physical blocks read
18 PHYRDS - Number of physical reads done

Don’t you hate it when Oracle is self-consistent, and yet at the same time not self-consistent?  One has to wonder if a bug was corrected regarding the counting of single block reads, or if direct path reads are no longer performed when the database is opened.  But, we have another problem – look closely at the output from V$FILE_HISTOGRAM.





Tracing Enabled for Sessions After Instance Restart

12 02 2010

February 12, 2010

An recent thread in the comp.databases.oracle.server Usenet group brought up an interesting question.  What would cause a 10046 trace at level 12 and and errorstack trace at level 3 for event 21700 to be enabled for sessions every time the original poster bounced the Oracle XE instance?  Is this possibly a sign that someone modified an initialization parameter?  Did someone modify the application to enabling tracing to log files?  Is it a bug in Oracle XE? 

Fortunately, the OP provided a portion of one of the trace files, as shown below:

*** SERVICE NAME:(SYS$USERS) 2010-02-02 09:46:36.593
*** SESSION ID:(31.46) 2010-02-02 09:46:36.593
=====================
PARSING IN CURSOR #6 len=69 dep=2 uid=0 oct=42 lid=0 tim=80934443123 hv=3164292706 ad='6688fd70'
alter session set events '10046 trace name context forever, level 12'
END OF STMT
EXEC #6:c=0,e=32,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=1,tim=80934443118
=====================
PARSING IN CURSOR #6 len=71 dep=2 uid=0 oct=42 lid=0 tim=80934681241 hv=681663222 ad='6b8cb08c'
alter session set events '21700 trace name errorstack forever, level 3'
END OF STMT
PARSE #6:c=0,e=33,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=1,tim=80934681236
BINDS #6:
EXEC #6:c=0,e=8426,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=1,tim=80934748960
=====================
PARSING IN CURSOR #6 len=34 dep=2 uid=0 oct=42 lid=0 tim=80934753347 hv=1152259314 ad='66a0f058'
alter session set sql_trace = TRUE
END OF STMT
PARSE #6:c=0,e=59,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=1,tim=80934753343
BINDS #6:
EXEC #6:c=0,e=29497,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=1,tim=80934836162
=====================
PARSING IN CURSOR #7 len=435 dep=1 uid=0 oct=47 lid=0 tim=80934862347 hv=545826169 ad='6b897000'
begin
      execute immediate 'alter session set max_dump_file_size = unlimited';
      execute immediate 'alter session set tracefile_identifier = ''10046via_app''';
      execute immediate 'alter session set events ''10046 trace name context forever, level 12''';
      execute immediate 'alter session set events ''21700 trace name errorstack forever, level 3''';
      execute immediate 'alter session set sql_trace = TRUE';
  end;
END OF STMT

So, from the above trace file, is the tracing caused by:

  • A bug in Oracle XE
  • An application that was modified
  • A modified parameter in the spfile/pfile
  • None of the above

The number following dep= in the trace file is a significant clue.  SQL statements that are submitted by client application code will appear in 10046 trace files with dep= set to 0, so that probably rules out a change to the application code, unless of course the application is executing a PL/SQL block.  The discrete ALTER SESSION statements in the trace file that are wrapped in EXECUTE IMMEDIATE statements probably rules out the possibility of a parameter that was modified in the spfile/pfile and probably also rules out the possibilty of a bug in Oracle XE.  So, what might the next step be to find the cause of the trace files?

Another possibility is either a LOGON or STARTUP trigger that is enabling the trace.  The OP mentioned in the thread that he had enabled tracing earlier to troubleshoot a problem.  The tracing is apparently enabled in a PL/SQL procedure or anonymous PL/SQL block based on the value of dep= and the EXECUTE IMMEDIATE calls, so either a LOGON or STARTUP trigger is a possibility.  To test this possibility, you might execute a SQL statement like this:

SELECT
  TRIGGER_NAME,
  TRIGGER_TYPE,
  TRIGGERING_EVENT
FROM
  DBA_TRIGGERS
WHERE
  OWNER='SYS'
  AND TRIGGER_TYPE IN ('BEFORE EVENT','AFTER EVENT')
  AND SUBSTR(TRIGGERING_EVENT,1,5) IN ('START','LOGON');

TRIGGER_NAME           TRIGGER_TYPE TRIGGERING_EVENT
---------------------- ------------ ----------------
LOGON_FIX_MYAPP_PERF   AFTER EVENT  LOGON
LOGON_CAPTURE_10046    AFTER EVENT  LOGON
ENABLE_OUTLINES_TRIG   AFTER EVENT  STARTUP

If the above returned no rows, there is still a very small chance that a logon trigger or startup trigger is owned by a user other than SYS, so it might make sense to repeat the SQL statement without the OWNER=’SYS’ predicate in the WHERE clause.  In my case the SQL statement returned three rows – two logon triggers and one startup trigger.  The logon trigger name LOGON_CAPTURE_10046 is a significant indicator that a trigger is present that will enable a 10046 trace when the user logs into the database – it helps to use a consistent and descriptive naming convention.

The OP did not show the output of the above SQL statement, but did state that he found and removed a logon trigger that enabled the two traces.  If such a trigger is found, and you know that the trigger does not belong in the database, the trigger may be removed by connecting to the database as the SYS user and dropping the trigger:

DROP TRIGGER LOGON_CAPTURE_10046;




Automated DBMS_XPLAN, Trace, and Send to Excel

11 02 2010

February 11, 2010

If you have spent some time looking at the posts on this site you might have seen my Toy Project for performance tuning mentioned in a couple of those posts.  One of the windows in the program allows me to submit a SQL statement to the database and retrieve the execution plan using DBMS_XPLAN while simultaneously generating a 10046, 10053, 10032, or 10033 trace files.  That program window looks like this:

What would it take to implement something like the above using a VBS script with an Internet Explorer browser window acting at the user interface?  It might also be nice to have the ability to send the query results into Excel on demand.  The end result might look something like this (note that the array fetch setting might not have any effect):

If we use the sample tables from this blog post, what is the execution plan for the following SQL statement?

SELECT
  T3.C1 T3_C1,
  SUBSTR(T3.C2,1,10) T3_C2,
  T2.C1 T2_C1,
  SUBSTR(T2.C2,1,10) T2_C2,
  T1.C1 T1_C1,
  SUBSTR(T1.C2,1,10) T1_C2
FROM
  T3,
  T2,
  T1
WHERE
  T1.C1=T3.C1
  AND T1.C1=T2.C1
  AND T1.C1 BETWEEN 1 AND 10

If we submit the SQL statement with the TYPICAL format parameter specified, the following execution plan will appear (note that on Vista and Windows 7, the execution plan may hide behind the main window – a pop-under effect):

The first 100 rows from the SQL statement appear at the bottom of the window.  With the TYPICAL format parameter specified, we are only able to determine the estimated number of rows that will be returned, and the estimated execution time.

If we change the format parameter to ALLSTATS LAST and change the Statistics Level to ALL, we are able to see the actual execution statistics for the plan:

Of course at this point, we might wonder if nested loops joins might be more efficient than hash joins, so we could test the change in execution time with a hinted SQL statement:

Looking closely at the plans, we see that the plan with the hash joins completed in 4.31 seconds, while the plan with the nested loops joins completed in 5.0 seconds.  The cost-based optimizer correctly selected the fastest executing plan for the SQL statement.

We also have the option for enabling several types of trace files and determining what, if any, performance impact we see when various trace files are enabled:

Notice that the program assigned a unique trace filename (displayed on the Status line) so that it is easy to find the trace file for our test execution.

The final option on the web page sends the query results into an Excel workbook – do not close the Excel window until you first close the Internet Explorer window, a new worksheet will be created in the workbook every time the Send to Excel button is clicked:

If you want to experiment with this script, you may download it here: XPlanViewerWithTrace.vbs (version 1.0, save as XPlanViewerWithTrace.vbs).

—————————

Update February 18, 2010:

See the documentation for details of the permissions required to use DBMS_XPLAN.DISPLAY_CURSOR.  See comment #2 for the items that need to be changed in the script in order to connect to your database.








Follow

Get every new post delivered to your Inbox.

Join 142 other followers