Search

OakieTags

Who's online

There are currently 0 users and 44 guests online.

Recent comments

Oakies Blog Aggregator

Ignoring hints

A hint is an instruction to the optimizer

This is what’s written in Oracle documentation. Instruction is defined as

a code that tells a computer to perform a particular operation

Which means Oracle CBO must obey the hints and must perform particular operation. The latter is hard to define correctly and explain precisely because it involves the logic of the block-box (what Cost Based Optimizer is). Some of the operations are mentioned in the standard Oracle documentation, some of them scattered across different places, and there are exceptions as usual. I think I’ll list here these cases which could lead to “ignoring hints” with the links to documentation/blogs.

Description Hints affected Reference
The hint has a syntax error, or doesn’t follow DELETE/INSERT/SELECT/MERGE/UPDATE keyword, or conflicts with other hints All http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements006.htm#sthref482
The optimizer ignores FIRST_ROWS in DELETE and UPDATE statement blocks and in SELECT statement blocks that include any blocking operations, such as sorts or groupings FIRST_ROWS http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements006.htm#sthref524
The LEADING hint is ignored if the tables specified cannot be joined first in the order specified because of dependencies in the join graph. If you specify two or more conflicting LEADING hints, then all of them are ignored. If you specify the ORDERED hint, it overrides all LEADING hints. LEADING, ORDERED http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements006.htm#sthref564
If a NO_INDEX hint and an index hint (INDEX, INDEX_ASC,INDEX_DESC, INDEX_COMBINE, or INDEX_FFS) both specify the same indexes, then the database ignores both the NO_INDEX hint and the index hint for the specified indexes and considers those indexes for use during execution of the statement. INDEX* http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements006.htm#sthref589
If two or more query blocks have the same name, or if the same query block is hinted twice with different names, then the optimizer ignores all the names and the hints referencing that query block. QB_NAME http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements006.htm#sthref692
If a hint specifies an unavailable access path, the optimizer ignores it Access path http://docs.oracle.com/cd/E11882_01/server.112/e16638/hintsref.htm#PFGRF94938
If the statement uses an alias for the table, then use the alias rather than the table name in the hint Access path http://docs.oracle.com/cd/E11882_01/server.112/e16638/hintsref.htm#PFGRF94938
The table name within the hint should not include the schema name if the schema name is present in the statement Access path http://docs.oracle.com/cd/E11882_01/server.112/e16638/hintsref.htm#PFGRF94938
For access path hints, Oracle Database ignores the hint if you specify the SAMPLE option in the FROM clause of a SELECT statement Access path http://docs.oracle.com/cd/E11882_01/server.112/e16638/hintsref.htm#PFGRF94938
The hints USE_NL & USE_MERGE are ignored if the referenced table is the outer table in the join Join operations http://docs.oracle.com/cd/E11882_01/server.112/e16638/hintsref.htm#autoId7
Oracle Database ignores global hints that refer to multiple query blocks ? http://docs.oracle.com/cd/E11882_01/server.112/e16638/hintsref.htm#i21188
Access path and join hints on referenced views are ignored unless the view contains a single table or references an Additional Hints view with a single table. ? http://docs.oracle.com/cd/E11882_01/server.112/e16638/hintsref.htm#autoId21
With nonmergeable views, optimization approach and goal hints inside the view are ignored. Access path hints on the view in the top-level query are ignored. ? http://docs.oracle.com/cd/E11882_01/server.112/e16638/hintsref.htm#autoId23
If an invalid hint is a valid SQL keyword, it causes other hints to be ignored All https://support.oracle.com/epmos/faces/DocumentDisplay?id=826893.1
When parallel_instance_group points to a non-existent service name, PARALLEL hint will be ignored PARALLEL https://support.oracle.com/epmos/faces/DocumentDisplay?id=1467447.1
INDEX hint may be “ignored” if materialized query rewrite produces plan with lower cost ? http://jonathanlewis.wordpress.com/2007/02/21/ignoring-hints/
Transitive closure and join elimination may produce a plan which ignores USE_HASH hint Join operations http://jonathanlewis.wordpress.com/2010/02/11/ignoring-hints-2/
Hints in ANSI joins could be ignored due to query transformation and introduction of new query blocks ? http://jonathanlewis.wordpress.com/2010/12/03/ansi-argh/
Undocumented limit of 20 chars for query block name causes QB_NAME to be ignored QB_NAME http://oracle-randolf.blogspot.com/2013/02/qbname-hint-query-block-name-length.html

Unsurprisingly, most of the cases are covered by the documentation. Good to know.
PS. Apart from documentation, an excellent source of information about hinting is presentation and paper Hint on Hints by Jonathan Lewis.

Filed under: CBO, Hints, Oracle

Everyone should write/present because…

Following on from my post about the ACE program, Yuri from Pythian asked what I get out of presenting that makes it worthwhile. In this post I will tell a few little stories to explain why I think writing and presenting are important skills for people, regardless of their ambitions.

Presenting

I mentioned in the previous post that I was originally scared of public speaking. There are only two reactions to that. You either avoid it, or face it head-on. In my case I chose the latter and it worked for me. I’m now really comfortable speaking to large groups of people. It’s always a bit nervy, but in a good way. At UKOUG last year I got up on stage and I could see my hands were shaking, so I pointed it out to the crowd and laughed at myself. Once I acknowledged the fear, I felt pretty calm and got on with it. The confidence to accept this sort of thing only comes if you put yourself through the ringer a few times. Preparation makes life alot easier, but no amount of practicing in your house can truly prepare you for the first time you get on stage.

If you do your preparation well, you will learn a lot more about your subject area. I spend a lot of time looking at what I am presenting and trying to think about the questions people are likely to ask me. If I come across anything I can’t answer in a convincing manner, I hit the books to find out what the answer is. There are always a few surprises, but you can incorporate those into your presentations to improve them over time.

In a similar vein, learning how to explain things to other people teaches you a lot about your subject. When you have to think of multiple approaches to explain a subject, you often gain more clarity yourself.

“Those who know, do. Those that understand, teach.” – Aristotle

There are pivotal moments in your life when being able to communicate clearly and calmly can have a big impact. I was speaking to some University students a few months back and asked how many of them had done formal presentations. The answer was pretty much zero. So then I posed the question, how do you think you are going to cope in a job interview if you’ve never actually put yourself under that sort of pressure before? I’m not saying presenting in front of your peers or at a conference will make you an interview demon, but these skills are transferable and they will help.

Likewise, when you are in a meeting and you have to present your arguments for following a specific route, if you babble inanely I doubt you will get the result you want. Communicating your thoughts and ideas in a clear manner is a skill everyone needs. Being able to communicate with people of differing technical backgrounds is a great skill too. It allows you to be the glue that binds the teams together. There is nothing worse than working in a company where all the teams are cool, but the interfaces between them are broken.

Above all, when you’ve done a good presentation you are on such a high. You feel like skipping out of the room. :)

Writing

I think everyone should write. Not just technical people, but everyone. I never kept a diary as a kid, but on reflection I wish I had. You don’t need to write fancy prose. Not every article has to been 50 pages long. It’s about ordering your thoughts. You don’t have to make them available on the internet, but I think it helps if you do.

I remember the first time I answered a question on a forum. It was dbasupport.com. I must have reread my answer about 20 times. I read the relevant pages in the documentation several times, making sure I’d not made a mistake. I hit submit and then refreshed the page every few seconds waiting to see if someone would criticise my answer. It was terrifying. The point is, putting your content out for public consumption opens you up to criticism, so you try a bit harder. I recently got one of my colleagues to start blogging. He kept his notes as word documents on a memory stick. In transferring stuff to his blog he commented on how scrappy some of his notes were and how putting them on his blog was forcing him to neaten things up. :) How many times have to looked back at scrappy notes and found them pretty much useless?

I’ve got 12+ years of notes to fall back on. You ask me to do anything, chances are the first thing I will do is read my article on that subject as a refresher. If it doesn’t fill in all the gaps, I’ll add to it. The fact I can rely on my notes is a big confidence boost for me. Without them I would be winging through the manuals desperately hoping I can find the right bit before I make a fool of myself.

If career progression is your thing, ask yourself this question. If you were an employer and you were faced with two candidates of equal ability and one maintained a blog with regular posts of a technical nature and the other didn’t, which would you pick? I would pick the blogger, just because they showed an extra level of enthusiasm for the subject. I would find that an attractive quality in a candidate.

I don’t think your career should be your main motive though. Most of my employers, including my current one, haven’t had a clue about my website when I’ve been hired. My colleagues tend to catch on over time when I follow up every answer to a question with a link to oracle-base.com. :)

OK. So it’s a bit of a raggedy post, but it gives you some idea of why I think presenting and writing are important and what I get out of them. The fact that occasionally people will give you good feedback or make you part of a community program is a nice bonus. :)

Cheers

Tim…


Everyone should write/present because… was first posted on April 2, 2013 at 5:34 pm.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.

Public Appearances and Exadata Performance Training

I will be doing a lot of (Exadata) talking and teaching in the coming months. Here’s a list of events where you’ll see me speaking, teaching, hacking, learning and hopefully also drinking beer:

  • 16 April 2013UKOUG Engineered Systems Summit
    • London, UK
    • I will talk about the common Exadata performance lessons learned in my “4 years of Exadata performance” talk
    • Andy Colvin and Martin Bach of Enkitec will also speak there (yes, Martin has joined Enkitec in Europe!!! :-)
  • 30 April 2013NYOUG Training Day
    • Manhattan, NYC
    • This is the first ever public delivery of my new Getting the Most Out of Oracle’s Active Session History, Time Model and Wait events seminar!
  • 2-3 May 2013Advanced Exadata Performance seminar – in-classroom!
    • First public delivery of this class – and we’ll be going very deep into Exadata internals and performance ;-)
    • Irving, TX (in Enkitec HQ)
  • 13-16 May 2013Advanced Exadata Performance seminar – online
    • Same as previous, but online.
  • 10-11 June 2013 – My new ASH seminar – online
    • Getting the Most Out of Oracle’s Active Session History, Time Model and Wait events
    • More details will appear in the training & seminar page soon
  • 13-14 June 2013 – Oracle Data Warehouse Global Leaders forum (by invitation-only event run by Oracle)
    • Amsterdam, Netherlands
    • I will speak about my “4 years of Exadata performance” experience and probably learn from others’ experience too
  • 5-6 Aug 2013Enkitec Extreme Exadata Expo (E4) conference
    • Irving, TX
    • I will be speaking there, haven’t set the exact topic yet, but it will include demos and hacking something I suspect ;-)
  • 22-26 September 2013Oracle OpenWorld conference
    • San Francisco, CA
    • I will attend the ACE Directors briefing before the conference, likely speak, hang out at Enkitec booth, probably hack something at Enkitec’s Conference HQ for fun and definitely drink beer there. Maybe I’ll even attend the Wednesday’s party for a change!

As you see, my great plans to not travel much are not going to work out well :)

Actually it is better, this year I’ve managed to only travel twice so far (and one of the trips was for vacation!) and I haven’t had to do too many of the crazy around-the-world in 5 days trips I did when living in back Singapore… but looks like I’ll get to 2 million km nevertheless this year:

Tanel's Tripit stats April 2013

Well, see you in some corner of the world some day!


Should you aim to become an Oracle ACE?

I tweeted the following yesterday,

“It’s 7 years ago today that I was made an Oracle ACE. Seriously. It was April Fools Day 2006… :)

The followup from that tweet included a number of questions about what you get out of becoming an Oracle ACE and what is the quickest way to become one. In my mind, these types of questions highlight the misunderstanding of what the Oracle ACE program is. You can hear Vikki, Debra, Alex and myself talking about the Oracle ACE program here, but I feel like I want to clarify a few things. This is just my opinion. Others may say different. :)

Should you aim to become an Oracle ACE?

IMHO No! You should try to get involved in the Oracle community. If you enjoy that experience, keep on doing it and eventually you may be nominated and accepted as an Oracle ACE. If you don’t enjoy being involved in the community, then there is little chance you will do enough to warrant being nominated and accepted into the program.

The community contributions of the ACEs are assessed each year and as a result people drop out of the program. You can’t just do a quick spurt of blogging and hope to wing your way into the program, only to kick back and think you are sorted for life. That’s not how it works. Writing, presenting and answering questions on forums takes a lot of time. If you don’t enjoy it, you will not continue to contribute over a long period of time.

Does being an Oracle ACE mean you are an Oracle guru?

No. It is basically a pat on the back from Oracle for all your contributions to the community. Some of the ACEs are completely awesome and will melt your brain when they get going. Others like myself are just regular DBAs and Developers that like spreading what we’ve learned over the years. The ACE program is not a certification. It is not proof of ability. It is not a natural progression from OCP to OCM to ACE. If you think that, you’ve completely misunderstood what it is all about.

There are some awesome DBAs and Developers out there who you will never hear of. Why? Because they don’t enjoy putting themselves out there. If you are that type of person, then why make yourself miserable, just to try and become an Oracle ACE?

What do you get out of being an Oracle ACE?

You don’t need to be an Oracle ACE to get most of the benefits of being an Oracle ACE. It is the process you go through that provides most of the benefits, not the program itself…

The Oracle ACE program is great for networking. You meet lots of really cool people and make friends with many of them. That circle of people contains a great wealth of information. Having said that, because we are all involved in the community, almost all of us are directly accessible by you. If you show an active interest in a specific subject area, you will probably get in to regular conversations (online) with the Oracle ACEs in that area, as well as many other people.

Presenting is a skill *everybody* should have. At school I found it almost impossible to read out loud in front of people. My head would spin and I would panic. I could chat in groups of people, but anything that was even remotely formal was a nightmare. My experiences of presenting during my PhD weren’t much better. When I started working in IT I found the work environment fine, but put me in a meeting and ask me to introduce myself to the people around the table and a part of me would die inside. The reason for saying this is to highlight that presenting was not a natural thing for me. It was only after being made an Oracle ACE that I felt I should do some presenting. Like most newbies I was terrified and the added pressure of being labelled an Oracle ACE did not help. Fortunately, I got some good advice from some great speakers along the way, which helped a lot. What was the knock-on effect of this? I now find it easy to speak in meetings and interviews. Presenting is still a little nerve wracking, but it is fun also. You don’t need to be an Oracle ACE to get this benefit. Start presenting to your colleagues. Try and present at a local Special Interest Group (SIG). Try ToastMasters. You don’t have to present to 1000 people at OpenWorld to get the benefits of the confidence this gives you. The ACE program was the nudge I needed to do this, but for others user group participation was the factor that influenced them.

I feel like if you are looking for what the Oracle ACE program will give you, you’ve kind of missed the point.

Conclusion

Being part of the Oracle ACE program has been a very positive thing for me. I will remain in the program as long as it exists and as long as they will have me. :) What’s really important is, if the program were to end tomorrow, I would still keep doing what I do. If you see the Oracle ACE program as a goal for you to achieve, then I don’t think the program is what you think it is.

It’s just my opinion. :)

Cheers

Tim…

Update: Jeff Smith just pointed me at this. Seems someone else was admitted to an evangelist program on April 1st too and was equally prompted to write on the subject. Kinda freaky to say the least!


Should you aim to become an Oracle ACE? was first posted on April 2, 2013 at 1:11 pm.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.

Accelerate SAP Projects with Virtual Databases

NASDAQ: KLAC

  • KLA-­-Tencor Corporation
  • One Technology Drive Milpitas, CA 95035 United States
  • Phone: 408.875.3000
  • Website: http://www.kla-­-tencor.com
  • Industry: Semiconductors

KEY RESULTS WITH DELPHIX

  • 2.5x faster project rollout
  • Consolidated 45 virtual databases across two Delphix Servers
  • Reduced refresh time from 2 weeks to 20 minutes on average
  • Performed 60 refreshes in 6 month
  • Reduced storage needs by 94%
  • Displaced need to purchase >$400K in Tier 1 storage

 

Business-Critical SAP Projects

Today’s businesses depend on critical applications like SAP to drive sales, finance, and marketing operations.  Application projects that deliver new or improved functionality can have an immediate and significant impact on top line revenues and bottom line earnings.  Most IT organizations can only manage a small number of projects in a given year, limiting the ability of businesses to capture market opportunities or improve operational efficiency.  Two factors frequently limit the speed and number of database projects: 1) cost and availability of hardware infrastructure, especially storage, and 2) complexity of managing data synchronization and refresh.

Heavy Infrastructure Required for Maintaining Landscapes

Supporting SAP landscapes is complex and resource-intensive for most organizations.  Multiple copies of each production database must be created for mandated development and testing environments.  Most organizations create 2 to 8 supporting environments, but large organizations expand to as many as 30 to 40 copies for various projects.  With production databases often averaging over a terabyte per SAP deployment, databases supporting these complex deployments can total 10s to 100s of terabytes.  As a result, storage costs and availability often become limiting factors for new projects.

Refreshing Databases Strains Organizations

Databases supporting applications for different functional areas (such as CRM and ERP) need to be synchronized to the same point in time for information consistency.  Provisioning databases may require the involvement of several teams in IT: storage administrators, system administrators, DBAs, and application administrators.  Due to cross-functional organizational dependencies, each supporting environment can take several days to weeks to provision or refresh.  The organizational and technical complexity of synchronizing and refreshing data for development and testing environments also limits the delivery of new projects.

KLA Tencor: 10 Databases to Provision, Refresh

KLA-Tencor supplies process control and yield management solutions for the semiconductor and related industries.  KLA-Tencor supports an SAP landscape with 7 key applications, from ERP to business warehousing, and 3 databases as web portals.  They have 10 Oracle databases in their production architecture, with 8 copies of the entire production environment for development, testing, trouble shooting, and disaster recovery, consuming over 40 TB of storage.  With iPhones and iPads driving incredible growth in global semiconductor chip usage, their business continues to scale, requiring fast, frequent additions to their SAP application environment.  Prior to implementing Delphix, it took KLA-Tencor an average of two weeks to refresh a testing or QA environment for any SAP project, with four to five administrators from different teams participating in the process.  Due to time and complexity, the IT teams could only deliver data refreshes on a monthly or bi-monthly basis, leaving stale data in project environments.  Since production databases continue to change, leaving stale data in project environments adds risk and uncertainty to project delivery and project quality.  Testing on stale data may not accurately represent the production environment, which can lead to project errors and failures after going live, which can be very expensive for a business.

Delphix Reduces Storage Needs by 94%

With a long backlog of key projects, KLA-Tencor would have needed to acquire more hardware infrastructure, especially storage, in order to support the oncoming project load.  Instead, they turned to Delphix to virtualize their databases.  As an Oracle gold-certified ISV partner, Delphix connects with Oracle databases through standard APIs, loads a first copy into Delphix, and then automatically maintains synchronization by requesting changed data – with little to no ongoing impact on production systems.  By policy, Delphix records data changes using its patent-pending TimeFlow technology for a set retention period (e.g.  two weeks).  From any point along the TimeFlow, DBAs can instantly provision or refresh VDBs, which look and behave like full copies of a database but only require a fraction of the storage space.  A single Delphix Server can provision and refresh multiple VDBs – all from a shared data footprint.  As a result, KLA-Tencor was able to provision more than 45 VDBs across two Delphix Servers, a consolidation ratio of more than 20:1 per server and a 94% reduction in storage requirements for their projects – displacing the need to purchase more than $400 thousand dollars worth in Tier 1 SAN storage.  “We tried a new technology and won big,” said Rajiv Gupta, Senior IT Manager, Enterprise Services at KLA-Tencor.  “Delphix provides the elasticity to expand our project infrastructure when we need it.”

Delphix_setup

Rapid Refresh of SAP Landscapes

SAP landscapes require synchronization across multiple SAP instances and their underlying databases.  Since business transactions frequently store data across multiple databases, all of the databases need to be provisioned or refreshed in concert – at the same point in time – in order to guarantee application coherency and consistency.  Provisioning and refreshing individual databases already strains IT organizations; the need to synchronize multiple databases only compounds the problem.  With integrated log synchronization, Delphix can provision multiple VDBs all at the same point in time, synchronizing data across multiple sources down to the second – dramatically simplifying this key requirement for provisioning and refreshing SAP landscapes.

Delphix_federated_provisioining

Self-Service Data Access, Refresh

With a steady stream of SAP projects on the horizon, KLA-Tencor needed to find ways to reduce the ongoing strain on their IT organization.  By configuring users, roles, and permissions in Delphix, KLA-Tencor enabled their SAP Basis team to have self-service access to provision or refresh VDBs for their projects.  Self-service eliminates the organizational dependencies and request/approval cycles that can tax workforces and slow projects.  In addition, by enabling the SAP Basis team to refresh VDBs according to the needs of their project schedules, they were able to better use the time of their contract developers – who would have had to wait for development environments on the clock.  “We were able to perform 60 refreshes in 6 months, which would have been impossible without virtualizing our databases,” said Shankar Bhavanasi, Senior Oracle DBA at KLA- Tencor.

Long-Term Benefits

By virtualizing the databases for their SAP architecture, KLA-Tencor accelerated their SAP projects, eliminated organizational dependencies by enabling self-service data provisioning and refresh, and reduced storage requirements by 94%.  More importantly, agile, virtual infrastructure allows KLA-Tencor to innovate faster while spending less, a critical competitive advantage in a fast-changing business environment – and one that will continue to pay dividends in years to come.

Understanding what a hint affects using the V$SQL_FEATURE views

You may have used the Oracle 11g V$SQL_HINT view already – it displays all the valid hints (both documented and undocumented ones) available in your Oracle version, for example:

SQL> @hint merge

NAME                                                             VERSION                   VERSION_OUTLINE           INVERSE
---------------------------------------------------------------- ------------------------- ------------------------- ----------------------------------------------------------------
MERGE_CONST_ON                                                   8.0.0
MERGE_AJ                                                         8.1.0                     8.1.7
MERGE_SJ                                                         8.1.0                     8.1.7
MV_MERGE                                                         9.0.0
MERGE                                                            8.1.0                     10.1.0                    NO_MERGE
NO_MERGE                                                         8.0.0                     10.1.0                    MERGE
USE_MERGE_CARTESIAN                                              11.1.0.6                  11.1.0.6
USE_MERGE                                                        8.1.0                     8.1.7                     NO_USE_MERGE
NO_USE_MERGE                                                     10.1.0.3                  10.1.0.3                  USE_MERGE

But there’s more, (semi)undocumented views like V$SQL_FEATURE and V$SQL_FEATURE_HIERARCHY do give us more information about what these hints relate to. For example, if you have ever wondered why is there a MERGE hint and then also a USE_MERGE hint, you can check what do these hints control using my hinth.sql (Hint Hierarchy) script:

SQL> @hinth MERGE
Display Hint feature hierarchy for hints like MERGE

NAME                                                             PATH
---------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------
MERGE                                                            ALL -> COMPILATION -> CBO -> CBQT -> CVM
MERGE                                                            ALL -> COMPILATION -> TRANSFORMATION -> CBQT -> CVM
MERGE                                                            ALL -> COMPILATION -> TRANSFORMATION -> HEURISTIC -> CVM

So, the MERGE hints seem to affect the CBO’s query transformation code – (CBQT means Cost-Based Query Transformation and CVM means Complex View Merging, but more about that later).

SQL> @hinth USE_MERGE
Display Hint feature hierarchy for hints like USE_MERGE

NAME                                                             PATH
---------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------
USE_MERGE                                                        ALL -> COMPILATION -> CBO -> JOIN_METHOD -> USE_MERGE

And the USE_MERGE hint is about controlling the use of a join method – the sort-merge join.

Let’s list all hints having NL in them:

SQL> @hinth %NL%
Display Hint feature hierarchy for hints like %NL%

NAME                                                             PATH
---------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------
INLINE_XMLTYPE_NT                                                ALL
NL_SJ                                                            ALL -> COMPILATION -> CBO
NL_AJ                                                            ALL -> COMPILATION -> CBO
NO_TABLE_LOOKUP_BY_NL                                            ALL -> COMPILATION -> CBO -> CBQT -> STAR_TRANS -> TABLE_LOOKUP_BY_NL
TABLE_LOOKUP_BY_NL                                               ALL -> COMPILATION -> CBO -> CBQT -> STAR_TRANS -> TABLE_LOOKUP_BY_NL
NO_USE_NL                                                        ALL -> COMPILATION -> CBO -> JOIN_METHOD -> USE_NL
USE_NL                                                           ALL -> COMPILATION -> CBO -> JOIN_METHOD -> USE_NL
USE_NL_WITH_INDEX                                                ALL -> COMPILATION -> CBO -> JOIN_METHOD -> USE_NL_WITH_INDEX
NO_TABLE_LOOKUP_BY_NL                                            ALL -> COMPILATION -> CBO -> STAR_TRANS -> TABLE_LOOKUP_BY_NL
TABLE_LOOKUP_BY_NL                                               ALL -> COMPILATION -> CBO -> STAR_TRANS -> TABLE_LOOKUP_BY_NL
NO_CONNECT_BY_CB_WHR_ONLY                                        ALL -> COMPILATION -> TRANSFORMATION
CONNECT_BY_CB_WHR_ONLY                                           ALL -> COMPILATION -> TRANSFORMATION
INLINE                                                           ALL -> COMPILATION -> TRANSFORMATION
NO_TABLE_LOOKUP_BY_NL                                            ALL -> COMPILATION -> TRANSFORMATION -> CBQT -> STAR_TRANS -> TABLE_LOOKUP_BY_NL
TABLE_LOOKUP_BY_NL                                               ALL -> COMPILATION -> TRANSFORMATION -> CBQT -> STAR_TRANS -> TABLE_LOOKUP_BY_NL
NO_NLJ_BATCHING                                                  ALL -> EXECUTION
NLJ_BATCHING                                                     ALL -> EXECUTION
NO_NLJ_PREFETCH                                                  ALL -> EXECUTION
NLJ_PREFETCH                                                     ALL -> EXECUTION

Plenty of interesting stuff here – the new hint TABLE_LOOKUP_BY_NL that has showed up recently seems to have to do with star transformations for example (I just learned this myself from this output).

Interestingly the NLJ_BATCHING and NLJ_PREFETCH hints are considered as execution phase hints apparently (that was my term, I’m thinking about hints (also) affecting a decision in the execution phase, not just during optimization). For example, normally the NLJ prefetch feature can be dynamically turned on & off during the query execution, I guess with a hint this feature would be always enabled (I’m not sure about this here, just trying to reason why a hint is shown to be related to “execution” phase).

If optimizer feature terms like CBQT and CVM do not immediately ring a bell, you can use the V$SQL_FEATURE view (or my sqlfh.sql script) to list some more info about what these SQL feature name abbreviations mean and where in the hierarchy does this particular feature stand.

The script below doesn’t accept any parameters, prints out the entire SQL feature hierarchy (except the temporary bugfix features you can see from V$SYSTEM_FIX_CONTROL):

SQL> @sqlfh

SQL_FEATURE                                             DESCRIPTION
------------------------------------------------------- ----------------------------------------------------------------
ALL                                                     A Universal Feature
  COMPILATION                                           SQL COMPILATION
    CBO                                                 SQL Cost Based Optimization
      ACCESS_PATH                                       Query access path
        AND_EQUAL                                       Index and-equal access path
        BITMAP_TREE                                     Bitmap tree access path
        FULL                                            Full table scan
        INDEX                                           Index
        INDEX_ASC                                       Index (ascending)
        INDEX_COMBINE                                   Combine index for bitmap access
        INDEX_DESC                                      Use index (descending)
        INDEX_FFS                                       Index fast full scan
        INDEX_JOIN                                      Index join
        INDEX_RS_ASC                                    Index range scan
        INDEX_RS_DESC                                   Index range scan descending
        INDEX_SS                                        Index skip scan
        INDEX_SS_ASC                                    Index skip scan ascending
        INDEX_SS_DESC                                   Index skip scan descending
        SORT_ELIM                                       Sort Elimination Via Index
      CBQT                                              Cost Based Query Transformation
        CVM                                             Complex View Merging
        DIST_PLCMT                                      Distinct Placement
        JOINFAC                                         Join Factorization
        JPPD                                            Join Predicate Push Down
        PLACE_GROUP_BY                                  Group-By Placement
        PULL_PRED                                       pull predicates
        STAR_TRANS                                      Star Transformation
          TABLE_LOOKUP_BY_NL                            Table Lookup By Nested Loop
        TABLE_EXPANSION                                 Table Expansion
        UNNEST                                          unnest query block
      CURSOR_SHARING                                    Cursor sharing
      DML                                               DML
      JOIN_METHOD                                       Join methods
        USE_HASH                                        Hash join
        USE_MERGE                                       Sort-merge join
        USE_MERGE_CARTESIAN                             Merge join cartesian
        USE_NL                                          Nested-loop join
        USE_NL_WITH_INDEX                               Nested-loop index join
      JOIN_ORDER                                        Join order
      OPT_MODE                                          Optimizer mode
        ALL_ROWS                                        All rows (optimizer mode)
        CHOOSE                                          Choose (optimizer mode)
        FIRST_ROWS                                      First rows (optimizer mode)
      OR_EXPAND                                         OR expansion
      OUTLINE                                           Outlines
      PARTITION                                         Partition
      PQ                                                Parallel Query
        PARALLEL                                        Parallel table
        PQ_DISTRIBUTE                                   PQ Distribution method
        PQ_MAP                                          PQ slave mapper
        PX_JOIN_FILTER                                  Bloom filtering for joins
      STAR_TRANS                                        Star Transformation
        TABLE_LOOKUP_BY_NL                              Table Lookup By Nested Loop
      STATS                                             Optimizer statistics
        CARDINALITY                                     Cardinality computation
        COLUMN_STATS                                    Basic column statistics
        CPU_COSTING                                     CPU costing
        DBMS_STATS                                      Statistics gathered by DBMS_STATS
        DYNAMIC_SAMPLING                                Dynamic sampling
        DYNAMIC_SAMPLING_EST_CDN                        Estimate CDN using dynamic sampling
        GATHER_PLAN_STATISTICS                          Gather plan statistics
        INDEX_STATS                                     Basic index statistics
        OPT_ESTIMATE                                    Optimizer estimates
        TABLE_STATS                                     Basic table statistics
    QUERY_REWRITE                                       query rewrite with materialized views
    RBO                                                 SQL Rule Based Optimization
    SQL_CODE_GENERATOR                                  SQL Code Generator
    SQL_PLAN_MANAGEMENT                                 SQL Plan Management
    TRANSFORMATION                                      Query Transformation
      CBQT                                              Cost Based Query Transformation
        CVM                                             Complex View Merging
        DIST_PLCMT                                      Distinct Placement
        JOINFAC                                         Join Factorization
        JPPD                                            Join Predicate Push Down
        PLACE_GROUP_BY                                  Group-By Placement
        PULL_PRED                                       pull predicates
        STAR_TRANS                                      Star Transformation
          TABLE_LOOKUP_BY_NL                            Table Lookup By Nested Loop
        TABLE_EXPANSION                                 Table Expansion
        UNNEST                                          unnest query block
      HEURISTIC                                         Heuristic Query Transformation
        CNT                                             Count(col) to count(*)
        COALESCE_SQ                                     coalesce subqueries
        CSE                                             Common Sub-Expression Elimination
        CVM                                             Complex View Merging
        FILTER_PUSH_PRED                                Push filter predicates
        FULL_OUTER_JOIN_TO_OUTER                        Join Conversion
        JPPD                                            Join Predicate Push Down
        OBYE                                            Order-by Elimination
        OLD_PUSH_PRED                                   Old push predicate algorithm (pre-10.1.0.3)
        OUTER_JOIN_TO_ANTI                              Join Conversion
        OUTER_JOIN_TO_INNER                             Join Conversion
        PRED_MOVE_AROUND                                Predicate move around
        SET_TO_JOIN                                     Transform set operations to joins
        SVM                                             Simple View Merging
        TABLE_ELIM                                      Table Elimination
        UNNEST                                          unnest query block
        USE_CONCAT                                      Or-optimization
    XML_REWRITE                                         XML Rewrite
      CHECK_ACL_REWRITE                                 Check ACL Rewrite
      COST_XML_QUERY_REWRITE                            Cost Based XML Query Rewrite
      XMLINDEX_REWRITE                                  XMLIndex Rewrite
  EXECUTION                                             SQL EXECUTION

I highlighted the CVM and CBQT lines above…

Just for reference (and if you’re too lazy to run these scripts yourself), I’ve pasted the full output of the hint feature hierarchy script too (executed in my 11.2.0.3 DB):

SQL> @hinth %
Display Hint feature hierarchy for hints like %

NAME                                                             PATH
---------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------
NO_XDB_FASTPATH_INSERT                                           ALL
XDB_FASTPATH_INSERT                                              ALL
NO_USE_HASH_GBY_FOR_PUSHDOWN                                     ALL
USE_HASH_GBY_FOR_PUSHDOWN                                        ALL
XMLINDEX_SEL_IDX_TBL                                             ALL
NO_DST_UPGRADE_INSERT_CONV                                       ALL
DST_UPGRADE_INSERT_CONV                                          ALL
NO_CONNECT_BY_ELIM_DUPS                                          ALL
CONNECT_BY_ELIM_DUPS                                             ALL
NO_MONITOR                                                       ALL
MONITOR                                                          ALL
NO_NATIVE_FULL_OUTER_JOIN                                        ALL
NATIVE_FULL_OUTER_JOIN                                           ALL
NO_CONNECT_BY_COMBINE_SW                                         ALL
CONNECT_BY_COMBINE_SW                                            ALL
OPT_PARAM                                                        ALL
OUTLINE_LEAF                                                     ALL
OUTLINE                                                          ALL
NO_CARTESIAN                                                     ALL
INCLUDE_VERSION                                                  ALL
RESTRICT_ALL_REF_CONS                                            ALL
NO_ACCESS                                                        ALL
HASH                                                             ALL
DRIVING_SITE                                                     ALL
CACHE_TEMP_TABLE                                                 ALL
QB_NAME                                                          ALL
NO_STATS_GSETS                                                   ALL
NO_USE_HASH_AGGREGATION                                          ALL
USE_HASH_AGGREGATION                                             ALL
NO_MODEL_PUSH_REF                                                ALL
MODEL_NO_ANALYSIS                                                ALL
SCN_ASCENDING                                                    ALL
TIV_GB                                                           ALL
PIV_GB                                                           ALL
TIV_SSF                                                          ALL
PIV_SSF                                                          ALL
NO_CONNECT_BY_FILTERING                                          ALL
CONNECT_BY_FILTERING                                             ALL
BYPASS_RECURSIVE_CHECK                                           ALL
SYS_RID_ORDER                                                    ALL
NO_BASETABLE_MULTIMV_REWRITE                                     ALL
NO_MULTIMV_REWRITE                                               ALL
REMOTE_MAPPED                                                    ALL
NO_GBY_PUSHDOWN                                                  ALL
GBY_PUSHDOWN                                                     ALL
IGNORE_OPTIM_EMBEDDED_HINTS                                      ALL
DB_VERSION                                                       ALL
OPTIMIZER_FEATURES_ENABLE                                        ALL
USE_WEAK_NAME_RESL                                               ALL
IGNORE_WHERE_CLAUSE                                              ALL
INLINE_XMLTYPE_NT                                                ALL
NESTED_TABLE_FAST_INSERT                                         ALL
NESTED_TABLE_SET_SETID                                           ALL
PRESERVE_OID                                                     ALL
NESTED_TABLE_GET_REFS                                            ALL
DEREF_NO_REWRITE                                                 ALL
NO_SQL_TUNE                                                      ALL
NO_MONITORING                                                    ALL
NO_OUTER_JOIN_TO_ANTI                                            ALL -> COMPILATION -> CBO
OUTER_JOIN_TO_ANTI                                               ALL -> COMPILATION -> CBO
NO_FULL_OUTER_JOIN_TO_OUTER                                      ALL -> COMPILATION -> CBO
FULL_OUTER_JOIN_TO_OUTER                                         ALL -> COMPILATION -> CBO
APPEND_VALUES                                                    ALL -> COMPILATION -> CBO
NUM_INDEX_KEYS                                                   ALL -> COMPILATION -> CBO
NO_DOMAIN_INDEX_FILTER                                           ALL -> COMPILATION -> CBO
DOMAIN_INDEX_FILTER                                              ALL -> COMPILATION -> CBO
NO_PARTIAL_COMMIT                                                ALL -> COMPILATION -> CBO
SKIP_UNQ_UNUSABLE_IDX                                            ALL -> COMPILATION -> CBO
X_DYN_PRUNE                                                      ALL -> COMPILATION -> CBO
ROWID                                                            ALL -> COMPILATION -> CBO
CLUSTER                                                          ALL -> COMPILATION -> CBO
NO_SWAP_JOIN_INPUTS                                              ALL -> COMPILATION -> CBO
SWAP_JOIN_INPUTS                                                 ALL -> COMPILATION -> CBO
INDEX_RRS                                                        ALL -> COMPILATION -> CBO
NO_SUBQUERY_PRUNING                                              ALL -> COMPILATION -> CBO
SUBQUERY_PRUNING                                                 ALL -> COMPILATION -> CBO
USE_SEMI                                                         ALL -> COMPILATION -> CBO
USE_ANTI                                                         ALL -> COMPILATION -> CBO
QUEUE_ROWP                                                       ALL -> COMPILATION -> CBO
QUEUE_CURR                                                       ALL -> COMPILATION -> CBO
CACHE_CB                                                         ALL -> COMPILATION -> CBO
NO_PARALLEL                                                      ALL -> COMPILATION -> CBO
CURSOR_SHARING_EXACT                                             ALL -> COMPILATION -> CBO
NO_BUFFER                                                        ALL -> COMPILATION -> CBO
BUFFER                                                           ALL -> COMPILATION -> CBO
NO_QKN_BUFF                                                      ALL -> COMPILATION -> CBO
BITMAP                                                           ALL -> COMPILATION -> CBO
RESTORE_AS_INTERVALS                                             ALL -> COMPILATION -> CBO
SAVE_AS_INTERVALS                                                ALL -> COMPILATION -> CBO
CUBE_GB                                                          ALL -> COMPILATION -> CBO
SYS_PARALLEL_TXN                                                 ALL -> COMPILATION -> CBO
OVERFLOW_NOMOVE                                                  ALL -> COMPILATION -> CBO
HWM_BROKERED                                                     ALL -> COMPILATION -> CBO
LOCAL_INDEXES                                                    ALL -> COMPILATION -> CBO
BYPASS_UJVC                                                      ALL -> COMPILATION -> CBO
NL_SJ                                                            ALL -> COMPILATION -> CBO
HASH_SJ                                                          ALL -> COMPILATION -> CBO
MERGE_SJ                                                         ALL -> COMPILATION -> CBO
NL_AJ                                                            ALL -> COMPILATION -> CBO
HASH_AJ                                                          ALL -> COMPILATION -> CBO
MERGE_AJ                                                         ALL -> COMPILATION -> CBO
SEMIJOIN_DRIVER                                                  ALL -> COMPILATION -> CBO
SKIP_EXT_OPTIMIZER                                               ALL -> COMPILATION -> CBO
DOMAIN_INDEX_NO_SORT                                             ALL -> COMPILATION -> CBO
DOMAIN_INDEX_SORT                                                ALL -> COMPILATION -> CBO
ORDERED_PREDICATES                                               ALL -> COMPILATION -> CBO
ORDERED                                                          ALL -> COMPILATION -> CBO
FBTSCAN                                                          ALL -> COMPILATION -> CBO
MERGE_CONST_ON                                                   ALL -> COMPILATION -> CBO
STREAMS                                                          ALL -> COMPILATION -> CBO
EXPR_CORR_CHECK                                                  ALL -> COMPILATION -> CBO
VECTOR_READ_TRACE                                                ALL -> COMPILATION -> CBO
VECTOR_READ                                                      ALL -> COMPILATION -> CBO
DML_UPDATE                                                       ALL -> COMPILATION -> CBO
SQLLDR                                                           ALL -> COMPILATION -> CBO
SYS_DL_CURSOR                                                    ALL -> COMPILATION -> CBO
NO_REF_CASCADE                                                   ALL -> COMPILATION -> CBO
REF_CASCADE_CURSOR                                               ALL -> COMPILATION -> CBO
NOAPPEND                                                         ALL -> COMPILATION -> CBO
APPEND                                                           ALL -> COMPILATION -> CBO
AND_EQUAL                                                        ALL -> COMPILATION -> CBO -> ACCESS_PATH -> AND_EQUAL
BITMAP_TREE                                                      ALL -> COMPILATION -> CBO -> ACCESS_PATH -> BITMAP_TREE
FULL                                                             ALL -> COMPILATION -> CBO -> ACCESS_PATH -> FULL
NO_USE_INVISIBLE_INDEXES                                         ALL -> COMPILATION -> CBO -> ACCESS_PATH -> INDEX
USE_INVISIBLE_INDEXES                                            ALL -> COMPILATION -> CBO -> ACCESS_PATH -> INDEX
NO_INDEX                                                         ALL -> COMPILATION -> CBO -> ACCESS_PATH -> INDEX
INDEX                                                            ALL -> COMPILATION -> CBO -> ACCESS_PATH -> INDEX
INDEX_ASC                                                        ALL -> COMPILATION -> CBO -> ACCESS_PATH -> INDEX_ASC
INDEX_COMBINE                                                    ALL -> COMPILATION -> CBO -> ACCESS_PATH -> INDEX_COMBINE
INDEX_DESC                                                       ALL -> COMPILATION -> CBO -> ACCESS_PATH -> INDEX_DESC
NO_INDEX_FFS                                                     ALL -> COMPILATION -> CBO -> ACCESS_PATH -> INDEX_FFS
INDEX_FFS                                                        ALL -> COMPILATION -> CBO -> ACCESS_PATH -> INDEX_FFS
INDEX_JOIN                                                       ALL -> COMPILATION -> CBO -> ACCESS_PATH -> INDEX_JOIN
INDEX_RS_ASC                                                     ALL -> COMPILATION -> CBO -> ACCESS_PATH -> INDEX_RS_ASC
INDEX_RS_DESC                                                    ALL -> COMPILATION -> CBO -> ACCESS_PATH -> INDEX_RS_DESC
NO_INDEX_SS                                                      ALL -> COMPILATION -> CBO -> ACCESS_PATH -> INDEX_SS
INDEX_SS                                                         ALL -> COMPILATION -> CBO -> ACCESS_PATH -> INDEX_SS
INDEX_SS_ASC                                                     ALL -> COMPILATION -> CBO -> ACCESS_PATH -> INDEX_SS_ASC
INDEX_SS_DESC                                                    ALL -> COMPILATION -> CBO -> ACCESS_PATH -> INDEX_SS_DESC
NO_MERGE                                                         ALL -> COMPILATION -> CBO -> CBQT -> CVM
MERGE                                                            ALL -> COMPILATION -> CBO -> CBQT -> CVM
NO_PLACE_DISTINCT                                                ALL -> COMPILATION -> CBO -> CBQT -> DIST_PLCMT
PLACE_DISTINCT                                                   ALL -> COMPILATION -> CBO -> CBQT -> DIST_PLCMT
NO_FACTORIZE_JOIN                                                ALL -> COMPILATION -> CBO -> CBQT -> JOINFAC
FACTORIZE_JOIN                                                   ALL -> COMPILATION -> CBO -> CBQT -> JOINFAC
NO_PLACE_GROUP_BY                                                ALL -> COMPILATION -> CBO -> CBQT -> PLACE_GROUP_BY
PLACE_GROUP_BY                                                   ALL -> COMPILATION -> CBO -> CBQT -> PLACE_GROUP_BY
NO_PULL_PRED                                                     ALL -> COMPILATION -> CBO -> CBQT -> PULL_PRED
PULL_PRED                                                        ALL -> COMPILATION -> CBO -> CBQT -> PULL_PRED
NO_FACT                                                          ALL -> COMPILATION -> CBO -> CBQT -> STAR_TRANS
FACT                                                             ALL -> COMPILATION -> CBO -> CBQT -> STAR_TRANS
NO_STAR_TRANSFORMATION                                           ALL -> COMPILATION -> CBO -> CBQT -> STAR_TRANS
STAR_TRANSFORMATION                                              ALL -> COMPILATION -> CBO -> CBQT -> STAR_TRANS
STAR                                                             ALL -> COMPILATION -> CBO -> CBQT -> STAR_TRANS
NO_TABLE_LOOKUP_BY_NL                                            ALL -> COMPILATION -> CBO -> CBQT -> STAR_TRANS -> TABLE_LOOKUP_BY_NL
TABLE_LOOKUP_BY_NL                                               ALL -> COMPILATION -> CBO -> CBQT -> STAR_TRANS -> TABLE_LOOKUP_BY_NL
NO_EXPAND_TABLE                                                  ALL -> COMPILATION -> CBO -> CBQT -> TABLE_EXPANSION
EXPAND_TABLE                                                     ALL -> COMPILATION -> CBO -> CBQT -> TABLE_EXPANSION
NO_UNNEST                                                        ALL -> COMPILATION -> CBO -> CBQT -> UNNEST
UNNEST                                                           ALL -> COMPILATION -> CBO -> CBQT -> UNNEST
NO_BIND_AWARE                                                    ALL -> COMPILATION -> CBO -> CURSOR_SHARING
BIND_AWARE                                                       ALL -> COMPILATION -> CBO -> CURSOR_SHARING
RETRY_ON_ROW_CHANGE                                              ALL -> COMPILATION -> CBO -> DML
CHANGE_DUPKEY_ERROR_INDEX                                        ALL -> COMPILATION -> CBO -> DML
IGNORE_ROW_ON_DUPKEY_INDEX                                       ALL -> COMPILATION -> CBO -> DML
NO_USE_HASH                                                      ALL -> COMPILATION -> CBO -> JOIN_METHOD -> USE_HASH
USE_HASH                                                         ALL -> COMPILATION -> CBO -> JOIN_METHOD -> USE_HASH
NO_USE_MERGE                                                     ALL -> COMPILATION -> CBO -> JOIN_METHOD -> USE_MERGE
USE_MERGE                                                        ALL -> COMPILATION -> CBO -> JOIN_METHOD -> USE_MERGE
USE_MERGE_CARTESIAN                                              ALL -> COMPILATION -> CBO -> JOIN_METHOD -> USE_MERGE_CARTESIAN
NO_USE_NL                                                        ALL -> COMPILATION -> CBO -> JOIN_METHOD -> USE_NL
USE_NL                                                           ALL -> COMPILATION -> CBO -> JOIN_METHOD -> USE_NL
USE_NL_WITH_INDEX                                                ALL -> COMPILATION -> CBO -> JOIN_METHOD -> USE_NL_WITH_INDEX
LEADING                                                          ALL -> COMPILATION -> CBO -> JOIN_ORDER
ALL_ROWS                                                         ALL -> COMPILATION -> CBO -> OPT_MODE -> ALL_ROWS
CHOOSE                                                           ALL -> COMPILATION -> CBO -> OPT_MODE -> CHOOSE
FIRST_ROWS                                                       ALL -> COMPILATION -> CBO -> OPT_MODE -> FIRST_ROWS
OR_EXPAND                                                        ALL -> COMPILATION -> CBO -> OR_EXPAND
NO_PARALLEL_INDEX                                                ALL -> COMPILATION -> CBO -> PQ
PARALLEL_INDEX                                                   ALL -> COMPILATION -> CBO -> PQ
NO_STATEMENT_QUEUING                                             ALL -> COMPILATION -> CBO -> PQ -> PARALLEL
STATEMENT_QUEUING                                                ALL -> COMPILATION -> CBO -> PQ -> PARALLEL
SHARED                                                           ALL -> COMPILATION -> CBO -> PQ -> PARALLEL
NOPARALLEL                                                       ALL -> COMPILATION -> CBO -> PQ -> PARALLEL
PQ_DISTRIBUTE                                                    ALL -> COMPILATION -> CBO -> PQ -> PQ_DISTRIBUTE
PQ_NOMAP                                                         ALL -> COMPILATION -> CBO -> PQ -> PQ_MAP
PQ_MAP                                                           ALL -> COMPILATION -> CBO -> PQ -> PQ_MAP
NO_PX_JOIN_FILTER                                                ALL -> COMPILATION -> CBO -> PQ -> PX_JOIN_FILTER
PX_JOIN_FILTER                                                   ALL -> COMPILATION -> CBO -> PQ -> PX_JOIN_FILTER
NO_FACT                                                          ALL -> COMPILATION -> CBO -> STAR_TRANS
STAR                                                             ALL -> COMPILATION -> CBO -> STAR_TRANS
FACT                                                             ALL -> COMPILATION -> CBO -> STAR_TRANS
NO_STAR_TRANSFORMATION                                           ALL -> COMPILATION -> CBO -> STAR_TRANS
STAR_TRANSFORMATION                                              ALL -> COMPILATION -> CBO -> STAR_TRANS
NO_TABLE_LOOKUP_BY_NL                                            ALL -> COMPILATION -> CBO -> STAR_TRANS -> TABLE_LOOKUP_BY_NL
TABLE_LOOKUP_BY_NL                                               ALL -> COMPILATION -> CBO -> STAR_TRANS -> TABLE_LOOKUP_BY_NL
CARDINALITY                                                      ALL -> COMPILATION -> CBO -> STATS
TABLE_STATS                                                      ALL -> COMPILATION -> CBO -> STATS
INDEX_STATS                                                      ALL -> COMPILATION -> CBO -> STATS
COLUMN_STATS                                                     ALL -> COMPILATION -> CBO -> STATS
NO_CPU_COSTING                                                   ALL -> COMPILATION -> CBO -> STATS -> CPU_COSTING
CPU_COSTING                                                      ALL -> COMPILATION -> CBO -> STATS -> CPU_COSTING
DBMS_STATS                                                       ALL -> COMPILATION -> CBO -> STATS -> DBMS_STATS
DYNAMIC_SAMPLING                                                 ALL -> COMPILATION -> CBO -> STATS -> DYNAMIC_SAMPLING
DYNAMIC_SAMPLING_EST_CDN                                         ALL -> COMPILATION -> CBO -> STATS -> DYNAMIC_SAMPLING_EST_CDN
GATHER_PLAN_STATISTICS                                           ALL -> COMPILATION -> CBO -> STATS -> GATHER_PLAN_STATISTICS
OPT_ESTIMATE                                                     ALL -> COMPILATION -> CBO -> STATS -> OPT_ESTIMATE
RBO_OUTLINE                                                      ALL -> COMPILATION -> RBO
RULE                                                             ALL -> COMPILATION -> RBO
NO_PRUNE_GSETS                                                   ALL -> COMPILATION -> TRANSFORMATION
MODEL_DONTVERIFY_UNIQUENESS                                      ALL -> COMPILATION -> TRANSFORMATION
MODEL_PUSH_REF                                                   ALL -> COMPILATION -> TRANSFORMATION
MODEL_COMPILE_SUBQUERY                                           ALL -> COMPILATION -> TRANSFORMATION
MODEL_DYNAMIC_SUBQUERY                                           ALL -> COMPILATION -> TRANSFORMATION
MODEL_MIN_ANALYSIS                                               ALL -> COMPILATION -> TRANSFORMATION
NO_EXPAND_GSET_TO_UNION                                          ALL -> COMPILATION -> TRANSFORMATION
EXPAND_GSET_TO_UNION                                             ALL -> COMPILATION -> TRANSFORMATION
MV_MERGE                                                         ALL -> COMPILATION -> TRANSFORMATION
NO_CONNECT_BY_COST_BASED                                         ALL -> COMPILATION -> TRANSFORMATION
CONNECT_BY_COST_BASED                                            ALL -> COMPILATION -> TRANSFORMATION
INLINE                                                           ALL -> COMPILATION -> TRANSFORMATION
MATERIALIZE                                                      ALL -> COMPILATION -> TRANSFORMATION
REWRITE_OR_ERROR                                                 ALL -> COMPILATION -> TRANSFORMATION
NO_REWRITE                                                       ALL -> COMPILATION -> TRANSFORMATION
REWRITE                                                          ALL -> COMPILATION -> TRANSFORMATION
NO_SEMIJOIN                                                      ALL -> COMPILATION -> TRANSFORMATION
SEMIJOIN                                                         ALL -> COMPILATION -> TRANSFORMATION
ANTIJOIN                                                         ALL -> COMPILATION -> TRANSFORMATION
NO_PUSH_SUBQ                                                     ALL -> COMPILATION -> TRANSFORMATION
PUSH_SUBQ                                                        ALL -> COMPILATION -> TRANSFORMATION
NO_QUERY_TRANSFORMATION                                          ALL -> COMPILATION -> TRANSFORMATION
OPAQUE_XCANONICAL                                                ALL -> COMPILATION -> TRANSFORMATION
OPAQUE_TRANSFORM                                                 ALL -> COMPILATION -> TRANSFORMATION
NO_CONNECT_BY_CB_WHR_ONLY                                        ALL -> COMPILATION -> TRANSFORMATION
CONNECT_BY_CB_WHR_ONLY                                           ALL -> COMPILATION -> TRANSFORMATION
NO_TRANSFORM_DISTINCT_AGG                                        ALL -> COMPILATION -> TRANSFORMATION
TRANSFORM_DISTINCT_AGG                                           ALL -> COMPILATION -> TRANSFORMATION
PRECOMPUTE_SUBQUERY                                              ALL -> COMPILATION -> TRANSFORMATION
LIKE_EXPAND                                                      ALL -> COMPILATION -> TRANSFORMATION
NO_ORDER_ROLLUPS                                                 ALL -> COMPILATION -> TRANSFORMATION
GBY_CONC_ROLLUP                                                  ALL -> COMPILATION -> TRANSFORMATION
USE_TTT_FOR_GSETS                                                ALL -> COMPILATION -> TRANSFORMATION
MERGE                                                            ALL -> COMPILATION -> TRANSFORMATION -> CBQT -> CVM
NO_MERGE                                                         ALL -> COMPILATION -> TRANSFORMATION -> CBQT -> CVM
NO_PLACE_DISTINCT                                                ALL -> COMPILATION -> TRANSFORMATION -> CBQT -> DIST_PLCMT
PLACE_DISTINCT                                                   ALL -> COMPILATION -> TRANSFORMATION -> CBQT -> DIST_PLCMT
FACTORIZE_JOIN                                                   ALL -> COMPILATION -> TRANSFORMATION -> CBQT -> JOINFAC
NO_FACTORIZE_JOIN                                                ALL -> COMPILATION -> TRANSFORMATION -> CBQT -> JOINFAC
PLACE_GROUP_BY                                                   ALL -> COMPILATION -> TRANSFORMATION -> CBQT -> PLACE_GROUP_BY
NO_PLACE_GROUP_BY                                                ALL -> COMPILATION -> TRANSFORMATION -> CBQT -> PLACE_GROUP_BY
PULL_PRED                                                        ALL -> COMPILATION -> TRANSFORMATION -> CBQT -> PULL_PRED
NO_PULL_PRED                                                     ALL -> COMPILATION -> TRANSFORMATION -> CBQT -> PULL_PRED
NO_FACT                                                          ALL -> COMPILATION -> TRANSFORMATION -> CBQT -> STAR_TRANS
NO_STAR_TRANSFORMATION                                           ALL -> COMPILATION -> TRANSFORMATION -> CBQT -> STAR_TRANS
STAR_TRANSFORMATION                                              ALL -> COMPILATION -> TRANSFORMATION -> CBQT -> STAR_TRANS
STAR                                                             ALL -> COMPILATION -> TRANSFORMATION -> CBQT -> STAR_TRANS
FACT                                                             ALL -> COMPILATION -> TRANSFORMATION -> CBQT -> STAR_TRANS
TABLE_LOOKUP_BY_NL                                               ALL -> COMPILATION -> TRANSFORMATION -> CBQT -> STAR_TRANS -> TABLE_LOOKUP_BY_NL
NO_TABLE_LOOKUP_BY_NL                                            ALL -> COMPILATION -> TRANSFORMATION -> CBQT -> STAR_TRANS -> TABLE_LOOKUP_BY_NL
NO_EXPAND_TABLE                                                  ALL -> COMPILATION -> TRANSFORMATION -> CBQT -> TABLE_EXPANSION
EXPAND_TABLE                                                     ALL -> COMPILATION -> TRANSFORMATION -> CBQT -> TABLE_EXPANSION
NO_UNNEST                                                        ALL -> COMPILATION -> TRANSFORMATION -> CBQT -> UNNEST
UNNEST                                                           ALL -> COMPILATION -> TRANSFORMATION -> CBQT -> UNNEST
NO_COALESCE_SQ                                                   ALL -> COMPILATION -> TRANSFORMATION -> HEURISTIC -> COALESCE_SQ
COALESCE_SQ                                                      ALL -> COMPILATION -> TRANSFORMATION -> HEURISTIC -> COALESCE_SQ
MERGE                                                            ALL -> COMPILATION -> TRANSFORMATION -> HEURISTIC -> CVM
NO_MERGE                                                         ALL -> COMPILATION -> TRANSFORMATION -> HEURISTIC -> CVM
NO_PUSH_PRED                                                     ALL -> COMPILATION -> TRANSFORMATION -> HEURISTIC -> FILTER_PUSH_PRED
PUSH_PRED                                                        ALL -> COMPILATION -> TRANSFORMATION -> HEURISTIC -> FILTER_PUSH_PRED
ELIMINATE_OBY                                                    ALL -> COMPILATION -> TRANSFORMATION -> HEURISTIC -> OBYE
NO_ELIMINATE_OBY                                                 ALL -> COMPILATION -> TRANSFORMATION -> HEURISTIC -> OBYE
OLD_PUSH_PRED                                                    ALL -> COMPILATION -> TRANSFORMATION -> HEURISTIC -> OLD_PUSH_PRED
NO_OUTER_JOIN_TO_INNER                                           ALL -> COMPILATION -> TRANSFORMATION -> HEURISTIC -> OUTER_JOIN_TO_INNER
OUTER_JOIN_TO_INNER                                              ALL -> COMPILATION -> TRANSFORMATION -> HEURISTIC -> OUTER_JOIN_TO_INNER
SET_TO_JOIN                                                      ALL -> COMPILATION -> TRANSFORMATION -> HEURISTIC -> SET_TO_JOIN
NO_SET_TO_JOIN                                                   ALL -> COMPILATION -> TRANSFORMATION -> HEURISTIC -> SET_TO_JOIN
ELIMINATE_JOIN                                                   ALL -> COMPILATION -> TRANSFORMATION -> HEURISTIC -> TABLE_ELIM
NO_ELIMINATE_JOIN                                                ALL -> COMPILATION -> TRANSFORMATION -> HEURISTIC -> TABLE_ELIM
NO_UNNEST                                                        ALL -> COMPILATION -> TRANSFORMATION -> HEURISTIC -> UNNEST
UNNEST                                                           ALL -> COMPILATION -> TRANSFORMATION -> HEURISTIC -> UNNEST
NO_EXPAND                                                        ALL -> COMPILATION -> TRANSFORMATION -> HEURISTIC -> USE_CONCAT
USE_CONCAT                                                       ALL -> COMPILATION -> TRANSFORMATION -> HEURISTIC -> USE_CONCAT
XML_DML_RWT_STMT                                                 ALL -> COMPILATION -> XML_REWRITE
NO_XML_DML_REWRITE                                               ALL -> COMPILATION -> XML_REWRITE
NO_XML_QUERY_REWRITE                                             ALL -> COMPILATION -> XML_REWRITE
FORCE_XML_QUERY_REWRITE                                          ALL -> COMPILATION -> XML_REWRITE
CHECK_ACL_REWRITE                                                ALL -> COMPILATION -> XML_REWRITE -> CHECK_ACL_REWRITE
NO_CHECK_ACL_REWRITE                                             ALL -> COMPILATION -> XML_REWRITE -> CHECK_ACL_REWRITE
NO_COST_XML_QUERY_REWRITE                                        ALL -> COMPILATION -> XML_REWRITE -> COST_XML_QUERY_REWRITE
COST_XML_QUERY_REWRITE                                           ALL -> COMPILATION -> XML_REWRITE -> COST_XML_QUERY_REWRITE
NO_XMLINDEX_REWRITE_IN_SELECT                                    ALL -> COMPILATION -> XML_REWRITE -> XMLINDEX_REWRITE
XMLINDEX_REWRITE_IN_SELECT                                       ALL -> COMPILATION -> XML_REWRITE -> XMLINDEX_REWRITE
NO_XMLINDEX_REWRITE                                              ALL -> COMPILATION -> XML_REWRITE -> XMLINDEX_REWRITE
XMLINDEX_REWRITE                                                 ALL -> COMPILATION -> XML_REWRITE -> XMLINDEX_REWRITE
NO_SUBSTRB_PAD                                                   ALL -> EXECUTION
NO_NLJ_BATCHING                                                  ALL -> EXECUTION
NLJ_BATCHING                                                     ALL -> EXECUTION
NO_NLJ_PREFETCH                                                  ALL -> EXECUTION
NLJ_PREFETCH                                                     ALL -> EXECUTION
CACHE                                                            ALL -> EXECUTION
NO_RESULT_CACHE                                                  ALL -> EXECUTION
RESULT_CACHE                                                     ALL -> EXECUTION
TRACING                                                          ALL -> EXECUTION
NOCACHE                                                          ALL -> EXECUTION
NO_LOAD                                                          ALL -> EXECUTION

305 rows selected.

Lots of hints to remember and to try out some day… nah, I’ll just run on modern Oracle versions and gather the stats properly ;-)


Index Selectivity

Here’s a summary of a recent posting on OTN:

I have two indexes (REFNO, REFTYPESEQNO) and (REFNO,TMSTAMP,REFTYPESEQNO). When I run the following query the optimizer uses the second index rather than the first index – which is an exact match for the predicates, unless I hint it otherwise:

select *
from   RefTable
where  RefTypeSeqNo = :1
and    RefNo = :2;

Default plan:
---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |     3 |   126 |     6   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| REFTABLE      |     3 |   126 |     6   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | REFTABLE_CX03 |     3 |       |     4   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("REFNO"=TO_NUMBER(:2) AND "REFTYPESEQNO"=TO_NUMBER(:1))
       filter("REFTYPESEQNO"=TO_NUMBER(:1))

Hinted plan:
---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |     3 |   126 |    15   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| REFTABLE      |     3 |   126 |    15   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | REFTABLE_CX02 |    14 |       |     4   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("REFNO"=TO_NUMBER(:2) AND "REFTYPESEQNO"=TO_NUMBER(:1))

This is actually an example of a feature of the optimizer that I described a few years ago. The original note described a change as you moved from 10.1 to 10.2 and on to 11.1 – but once you’ve seen the basic issue there are a number of variations on how it might appear. In this case the OP seems to be using 10gR2, where the distinct_keys value from an index is used to calculate the cost and row estimate for the index and for the table access cost when that specific indexed access path is being considered.

So with the hint to use the accurate index we see an index cardinality estimate of 14 rows with a table cardinality of 3 despite the fact that the plan shows no extra predicates applied at the table; the cost of accessing the table is also clearly related to the cardinality estimate on the index line.

In the default plan when the wrong index is used, the optimizer doesn’t pay any attention to the distinct_keys from the other index, and simply uses the standard “product of column selectivities”.

11g introduces two changes – when calculating the table cardinality the distinct_keys value for the index is carried forward (so the plan with the high index cardinality but low table cardinality would report the same cardinality for both operations), and the distinct_keys from the first index would be used when doing the calculations for the second index – which would increase the cost of using the wrong index.

There’s really very little you can do to find a strategic fix for this type of problem in 10g – obviously you could add hints whenever Oracle used the wrong index, but that’s not reallya desirable approach, and it is possible to adjust column statistics in such a way that the calculations the optimizer uses give better approximations, but that’s not always very easy to do well. Ultimately you just have to be very careful about your choice of indexes – and when you think that two indexes show a significant overlap in columns consider the possibility that one carefully defined index may be able to do the job of both.

 

 

Observing Oracle Exadata SmartScan internals, part 1

In order to look how Exadata smartscans are different, first let’s have a peek the Oracle full segment/multiblock read evolution as short as possible:

a) Traditional multiblock reads, visible via the event ‘db file scattered read’
The essence is: Multiple adjacent blocks are read from disk, and put in the buffercache. Because every read is sequentially processed, IO latency is a performance penalty for every physical read. This works roughly this way: get a set of adjacent blocks from the segment header, fetch these blocks from disk, process these blocks, then get the next set of adjacent blocks, fetch these blocks from disk, process these blocks, etc.

b) Direct path multiblock reads, visible via the event ‘direct path read’
The essence is: Multiple IOs are done asynchronously, one or more IOs are reaped and processed, after which the number of IOs is brought back to the number of IOs the process want to keep in flight. Blocks are read to the process’ PGA (which means the IO result is not shared with other processes). Because of the asynchronous way of issuing multiple requests, the process does not suffer from the IO latency penalty of every single IO. This works roughly this way: get a set of adjacent blocks from the segment header, issue an asynchronous IO request for these, get a next set of adjacent blocks from the segment header, issue another asynchronous IO request, process one or more of the IO requests which are ready, issue IO requests for the number requests reaped, process one or more of the IO requests ready, etc. During processing, Oracle measures CPU and IO times, and can decide to add one or more concurrent requests to the two IO’s which it tries to keep in flight.

Back to smartscans

It’s not very hard to understand that direct path multiblock reads can perform much better than traditional multiblock reads. Probably at this time you think: yes, I know, but what does this have to do with Exadata? This is all information about regular processing! Well, Exadata uses the regular Oracle database executable. This means that part of the codepath of smartscans is shared with the normal/non-Exadata Oracle database. Obviously, there is a part that is unique to Exadata.

This is best viewed with a backtrace of the call to submit an IO request. This a full backtrace of the submit of an IO request of a full table scan on Linux to a database on ASM:

io_submit
skgfqio
ksfd_skgfqio
ksfdgo
ksfdaio
kfk_ufs_async_io
kfk_submit_ufs_io
kfk_submit_io
kfk_io1
kfkRequest
kfk_transitIO
kfioSubmitIO
kfioRequestPriv
kfioRequest
ksfd_kfioRequest
ksfd_osmgo
ksfdgo
ksfdaio
kcflbi
kcbldio
kcblrs
kcblgt
kcbldrget
kcbgtcr
ktrget3
ktrget2
kdst_fetch
kdstf00000010000kmP
kdsttgr
qertbFetch
qergsFetch
opifch2
kpoal8
opiodr
ttcpip
opitsk
opiino
opiodr
opidrv
sou2o
opimai_real
ssthrdmain
main

Of course the functions used internally in the executable are not documented. But it’s very useful to look at them to gain a better understanding of what is happening. First look at the function at line line 24, kcbgtcr (Kernel Cache Buffers GeT Consistent Read). This is the function to perform a logical IO. One line up on line number 23 is the function kcbldrget (Kernel Cache Buffers direct path LoaDeR GET). This function indicates that the execution did choose the direct path read code path. In fact, the kcbl prefixed functions are believed to belong to Oracle direct path read codepath. Then roughly the ksfd, kfio, kfk, ksfd, and lastly skgfqio is executed, which performs the actual submit of an IO using io_submit().

Now let’s look how the equivalent submit of an IO request looks like on Exadata with smartscan turned off:

sskgxp_sndmsg
skgxpfragsnd
skgxp_send_next_fragment
skgxpxmit
skgxpivsnd
skgxpivrpc
skgxpvrpc
ossnet_issue_vrpc
ossnet_queue_vrpc
ossdisk_issue_read
ossdisk_read
oss_read
kfk_submit_one_oss_io
kfk_submit_oss_io
kfk_submit_io
kfk_io1
kfkRequest
kfk_transitIO
kfioSubmitIO
kfioRequestPriv
kfioRequest
ksfd_kfioRequest
ksfd_osmgo
ksfdgo
ksfdaio
kcflbi
kcbldio
kcblrs
kcblgt
kcbldrget
kcbgtcr
ktrget3
ktrget2
kdst_fetch
kdstf00000010000kmP
kdsttgr
qertbFetch
qergsFetch
opifch2
kpoal8
opiodr
ttcpip
opitsk
opiino
opiodr
opidrv
sou2o
opimai_real
ssthrdmain
main

First locate the kcbgtcr function, which is on line 31 (forget about trying to find logic in the line numbers; backtraces are read from bottom to top, whilst the numbering logic is from top to bottom). One line up on number 30 is the function kcbldrget again. Okay, that looks the same. If we read the backtrace up, it’s easy to spot the same layers, in fact the same functions: ksfd, kfio up to the kfk layer.

In the kfk layer there is a slight difference, which is understandable: on line 7 of the non-Exadata backtrace we see the function kfk_submit_ufs_io, while on Exadata the same function is kfk_submit_oss_io on line 14. I think this deserves a little Explanation. Exadata is the marketing name of the database machine, which internally was called ‘Sage’ in Oracle, and this name still surfaces sometimes, like in Exadata naming, or in patch descriptions. Quite probably OSS means ‘Oracle Sage Software’. So, this means that the process is aware it needs to read something from an Exadata storage server, and chooses a function that is meant to set that up.

The next two functions (kfk_ufs_async_io on line 6 and kfk_submit_one_oss_io on line 13) probably do logically the same, but are different because the infrastructure is different.

The next function up in both backtraces is where it gets really interesting, because now the code has to do something entirely different: on the non-Exadata system the ksfd layer is entered again, in order to get to the function skgfqio, which submit’s the IO request using the io_submit call. On the Exadata system, we see a call which I have not encountered outside of Exadata: oss_read. With the knowledge gained above, we can tell this quite probably is an Exadata specific call, which is inside an entire layer: oss. From the function names we can guess it prepares the IO request, and then issues it. Once the oss layer is crossed, we enter another layer: skgxp. The skgxp (System Kernel Generic inter-process Communication (Xfer?) Protocol) layer is NOT unique to Exadata, it’s the communication layer which is used by an Oracle RAC database for inter-process communication. So it seems like Oracle re-used the knowledge gained with RAC inter-process communication for the communication with the (Exadata) storage server.

Can we now please get to smartscans?

Yes, we didn’t encounter a smartscan yet, I did show a backtrace of a regular direct path read on Exadata. The reason is to show the difference between a traditional system (meaning disks which are presented to the system as local disks) and an Exadata system, which has to use infiniband and has to fetch the information it needs from Exadata storage servers. This is how a smartscan read call backtrace looks like:

sskgxp_sndmsg
skgxpfragsnd
skgxp_send_next_fragment
skgxpxmit
skgxpivsnd
skgxpivrpc
skgxpvrpc
ossnet_issue_vrpc
ossnet_queue_vrpc
ossdisk_cread
oss_cread
kcfis_read
kcbl_predpush_get
kcbldrget
kcbgtcr
ktrget3
ktrget2
kdst_fetch
kdstf00000010010kmP
kdsttgr
qertbFetch
qergsFetch
opifch2
kpoal8
opiodr
ttcpip
opitsk
opiino
opiodr
opidrv
sou2o
opimai_real
ssthrdmain
main

The first thing which did struck me is the number of functions did decrease. On the other hand, this does not say much (you can make functions as long or as short as you wish). Also, a smartscan is done using a number of steps, of which some are already been done, which are not visible from this backtrace. After the submit of a smartscan there is another number of steps; of course reaping the submitted scan requests, but also verification of the reaped request.

It’s now interesting to see how much different the codepath looks like. The kcbgtcr function is still present, at line 15. One line up there’s the kcbldrget function, which reveals the process chose the direct read path codepath during execution. If we go up one line we see a function in the kcbl layer, which is Exadata specific as far as I know: kcbl_predpush_get. Again: all has been setup for doing a smartscan prior to the point where the process enters the point of this backtrace: submitting a request to an Exadata storage server. This means the process has the information needed (what information to ask from what storage server) prior to arriving at this point.

One layer up is a call to the kcfis (Kernel Cache File Intelligent Storage is my guess) layer, then approximately the same calls in the oss layer, but there’s a difference: it’s oss_cread and ossdisk_cread instead of oss_read and ossdisk_read, which make the call an Exadata request, instead of a request for database blocks. After the oss layer, there’s the skgxp layer again, which are exactly the same calls for both the non-smartscan and smartscan.

Conclusion

The purpose of this blogpost is to show the differences between submitting an IO request on a traditional system, on an Exadata system with smartscans turned off, and with smartscans turned on.

There is a lot of ground to cover on this. It’s not doable to cover this all in one blogpost. That’s the reason I try to pick a specific part, and work from that. Any comments are welcome.

From the backtraces it’s quite good visible regular IO’s are processed and done the same way on non-Exadata and Exadata, on Exadata the request is submitted via the skgxp layer to a storage server, instead of an IO request done via io_submit. This makes it very probable that single block IO’s are done the same way too, which means no ‘magic’ performance enhancement is possible, because it’s processed the same way on Exadata as on non-Exadata, the only difference is the IO request is done differently.

If we look at the comparison between smartscan and non-smartscan requests, it becomes prevalent there is something different happening. But the basic processing is the same, with which I mean the process is doing exactly the same as non-Exadata processes. From the smartscan backtrace it becomes visible that a process has to travel through the direct path code layer (kcbl), in order to get a smartscan, because otherwise it would be impossible to issue kcbl_predpush_get, which is the call for issuing a smartscan.

Disclaimer: I am not an Oracle employee. This information has all been obtained by profiling execution (using perf/nm/gdb). I have never seen any Oracle database and Exadata source code. There is a chance some assumptions are wrong.

Tagged: oracle exadata smartscan IO, stacktrace backtrace internals

dbms_dnfs and clone.pl

If you are using to use dbms_dnfs to clone your database as described in MOS note 1210656.1 or on Tim's Hall blog - Direct NFS (DNFS) Clonedb in Oracle Database 11g Release 2 (Patchset 11.2.0.2) and your database version is 11.2.0.3 there is one additional step which is not covered by clone.pl script and has to be done manually.

In 11.2.0.2 it was enough to run crtdb.sql and dbren.sql scripts generated by clone.pl but this is what happen when you will run dbren.sql on 11.2.0.3
SQL> !cat dbren.sql
declare
begin
dbms_dnfs.clonedb_renamefile('/u01/app/oracle/backup/data_D-TESTSE_I-4169104998_TS-SYSAUX_FNO-2_22o1pnm0' , '/u01/clone/ora_data_CLONE2.dbf');
dbms_dnfs.clonedb_renamefile('/u01/app/oracle/backup/data_D-TESTSE_I-4169104998_TS-SYSTEM_FNO-1_20o1pnjq' , '/u01/clone/ora_data_CLONE3.dbf');
dbms_dnfs.clonedb_renamefile('/u01/app/oracle/backup/data_D-TESTSE_I-4169104998_TS-UNDOTBS1_FNO-3_23o1pnmp' , '/u01/clone/ora_data_CLONE4.dbf');
dbms_dnfs.clonedb_renamefile('/u01/app/oracle/backup/data_D-TESTSE_I-4169104998_TS-USERS_FNO-4_21o1pnkt' , '/u01/clone/ora_data_CLONE5.dbf');
end;
/
show errors;
alter database open resetlogs;
drop tablespace TEMP;
create temporary tablespace TEMP;

SQL> begin
dbms_dnfs.clonedb_renamefile('/u01/app/oracle/backup/data_D-TESTSE_I-4169104998_TS-SYSAUX_FNO-2_22o1pnm0' , '/u01/clone/ora_data_CLONE2.dbf');
dbms_dnfs.clonedb_renamefile('/u01/app/oracle/backup/data_D-TESTSE_I-4169104998_TS-SYSTEM_FNO-1_20o1pnjq' , '/u01/clone/ora_data_CLONE3.dbf');
dbms_dnfs.clonedb_renamefile('/u01/app/oracle/backup/data_D-TESTSE_I-4169104998_TS-UNDOTBS1_FNO-3_23o1pnmp' , '/u01/clone/ora_data_CLONE4.dbf');
dbms_dnfs.clonedb_renamefile('/u01/app/oracle/backup/data_D-TESTSE_I-4169104998_TS-USERS_FNO-4_21o1pnkt' , '/u01/clone/ora_data_CLONE5.dbf');
end;
7 /
begin
*
ERROR at line 1:
ORA-01511: error in renaming log/data files
ORA-01141: error renaming data file 2 - new file '/u01/clone/ora_data_CLONE2.dbf' not found
ORA-01110: data file 2: '/u01/app/oracle/backup/data_D-TESTSE_I-4169104998_TS-SYSAUX_FNO-2_22o1pnm0'
ORA-17515: Creation of clonedb failed using snapshot file /u01/app/oracle/backup/data_D-TESTSE_I-4169104998_TS-SYSAUX_FNO-2_22o1pnm0
ORA-06512: at "SYS.X$DBMS_DNFS", line 10
ORA-06512: at line 2

After that operation I found following errors in alert log

Fri Mar 29 13:31:20 2013
ERROR: clonedb parameter not set. Make sure clonedb=TRUE is set
Fri Mar 29 13:31:20 2013
Signalling error 1152 for datafile 1!
Signalling error 1152 for datafile 2!
Signalling error 1152 for datafile 4!
Checker run found 4 new persistent data failures

Let's check parameter 

SQL> show parameter clone

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
clonedb boolean FALSE



I have changed that parameter in init.ora and restarted instance.

SQL> show parameter clone

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
clonedb boolean TRUE

SQL> begin
dbms_dnfs.clonedb_renamefile('/u01/app/oracle/backup/data_D-TESTSE_I-4169104998_TS-SYSAUX_FNO-2_22o1pnm0' , '/u01/clone/ora_data_CLONE2.dbf');
dbms_dnfs.clonedb_renamefile('/u01/app/oracle/backup/data_D-TESTSE_I-4169104998_TS-SYSTEM_FNO-1_20o1pnjq' , '/u01/clone/ora_data_CLONE3.dbf');
dbms_dnfs.clonedb_renamefile('/u01/app/oracle/backup/data_D-TESTSE_I-4169104998_TS-UNDOTBS1_FNO-3_23o1pnmp' , '/u01/clone/ora_data_CLONE4.dbf');
dbms_dnfs.clonedb_renamefile('/u01/app/oracle/backup/data_D-TESTSE_I-4169104998_TS-USERS_FNO-4_21o1pnkt' , '/u01/clone/ora_data_CLONE5.dbf');
end;
7 /

PL/SQL procedure successfully completed.

Now it is working again so time to come back to other tests with cloning.

regards,
Marcin