Search
Navigation
OakieTags
Who's online
Affiliations
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.
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 2013 – UKOUG 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 2013 – NYOUG 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 2013 – Advanced 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 2013 – Advanced 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 2013 – Enkitec 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 2013 – Oracle 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:
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.”
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.
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
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
Why is my VirtualBox VM not starting properly?
I’ve just wasted quite a bit of time trying to figure out why one of my VirtualBox VMs wasn’t starting properly, so I thought I would share the experience here in case anyone else has a similar issue…
It all started because I decided to fire up an OL6 VM, that I hadn’t used for a little while. It started up fine and I figured I should probably patch the OS and the VirtualBox Guest Additions. The first thing I did was a “yum update” to bring it up to OL6.4 and the the latest UEK2. Once that was complete I did a reboot and that is where the issue started. The VM booted in the normal fashion, but then hung at this point.
I was able to SSH to the VM, so I knew it wasn’t a complete disaster, but something was definitely wrong.
I tried an interactive startup, just to see if the main part of the startup was actually OK, as the white bar suggests. Sure enough, everything started as expected, then just hung after the last prompt.
Next, I started up in single user mode. That went fine, so I switched to full user mode using “init 3″. That was also went OK. Rather than doing “init 5″ I just typed “startx”, which produced a big pile of garbage. So X was mangled, but I wasn’t getting kicked back to the prompt.
Before embarking on a mission to fix X I decided to upgrade the VirtualBox Guest Additions. That removed the old 4.2.6 version and installed the 4.2.10 version. Once complete I rebooted and everything worked fine.
Normally I try to keep my Linux kernels and VirtualBox Guest Additions up to date, but even when I’ve let VMs like this fall by the wayside I’ve not encountered this before.
Anyway, I can chalk that up to experience.
Cheers
Tim…
Why is my VirtualBox VM not starting properly? was first posted on March 29, 2013 at 3:46 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.
.png){kind=small}
TweetsPopular content
Today's:
Monthly archive
- May 2013 (51)
- April 2013 (79)
- March 2013 (71)
- February 2013 (73)
- January 2013 (57)
- December 2012 (52)
- November 2012 (58)
- October 2012 (71)
- September 2012 (89)
- August 2012 (63)
- July 2012 (74)
- June 2012 (70)
- May 2012 (87)
- April 2012 (86)
- March 2012 (86)
- February 2012 (93)
- January 2012 (88)
- December 2011 (92)
- November 2011 (120)
- October 2011 (108)
- September 2011 (110)
- August 2011 (98)
- July 2011 (114)
- June 2011 (101)
- May 2011 (86)
- April 2011 (81)
- March 2011 (131)
- February 2011 (106)
- January 2011 (126)
- December 2010 (112)
Recent comments
17 weeks 3 days ago
27 weeks 2 days ago
28 weeks 6 days ago
32 weeks 1 day ago
34 weeks 3 days ago
44 weeks 1 hour ago
45 weeks 3 days ago
46 weeks 3 days ago
46 weeks 4 days ago
49 weeks 3 days ago