If a list of indexes is specified, the optimizer chooses the one with the lowest cost. If no index is specified then the optimizer chooses the available index for the table with the lowest cost. If more than one table is accessed, this is ignored. Combines the bitmapped indexes on the table if the cost shows that to do so would give better performance. This hint causes a merge on several single column indexes. Two must be specified, five can be. Inserted into the EXISTS subquery; This converts the subquery into a special type of hash join between t1 and t2 that preserves the semantics of the subquery.
That is, even if there is more than one matching row in t2 for a row in t1, the row in t1 is returned only once. Inserted into the EXISTS subquery; This converts the subquery into a special type of merge join between t1 and t2 that preserves the semantics of the subquery. Inserted into the EXISTS subquery; This converts the subquery into a special type of nested loop join between t1 and t2 that preserves the semantics of the subquery.
This hint forces tables to be joined in the order specified. If you know table X has fewer rows, then ordering it first may speed execution in a join. Forces the largest table to be joined last using a nested loops join on the index. Makes the optimizer use the best plan in which a start transformation is used. When performing a star transformation use the specified table as a fact table.
When performing a star transformation do not use the specified table as a fact table. This causes nonmerged subqueries to be evaluated at the earliest possible point in the execution plan. If possible forces the query to use the specified materialized view, if no materialized view is specified, the system chooses what it calculates is the appropriate view.
Turns off query rewrite for the statement, use it for when data returned must be concurrent and can ' t come from a materialized view. This causes Oracle to join each specified table with another row source without a sort-merge join. This causes Oracle to join each specified table with another row source with a hash join. This operation forces a nested loop using the specified table as the controlling table.
This operation forces a sort-merge-join operation of the specified tables. The hint forces query execution to be done at a different site than that selected by Oracle. Specifying DEFAULT or no value signifies the query coordinator should examine the settings of the initialization parameters described in a later section to determine the default degree of parallelism. Do this by specifying how rows of joined tables should be distributed among producer and consumer query servers.
Using this hint overrides decisions the optimizer would normally make. Existing free space in the block is not used. If a table or an index is specified with nologging, this hint applied with an insert statement produces a direct path insert which reduces generation of redo. The CACHE hint specifies that the blocks retrieved for the table in the hint are placed at the most recently used end of the LRU list in the buffer cache when a full table scan is performed.
This option is useful for small lookup tables. This is the normal behavior of blocks in the buffer cache. Oracle can replace literals in SQL statements with bind variables, if it is safe to do so.
In other words, Oracle executes the SQL statement without any attempt to replace literals by bind variables. The higher the level, the more effort the compiler puts into dynamic sampling and the more broadly it is applied.
Sampling defaults to cursor level unless you specify a table. This hint omits some of the compile time optimizations of the rules, mainly detailed dependency graph analysis, on spreadsheets. Some optimizations such as creating filters to selectively populate spreadsheet access structures and limited rule pruning are still used.
You must specify at least two indexes. Too Cool. Thanks, Chuck. G'day Tom Speaking of when to use hints and last resorts May 14, - am UTC.
G'day Never mind. I just wanna kick myself. I have a table T with 1. There is a nonuniqe index on this column. Any suggestions? December 29, - pm UTC. Sorry different field in select, this might help, it shows same issue. The cardinalities are computed a little better with the histograms here right now you have size 1, insufficient. It is "guessing" wrong. Hi Tom, I agree with you about hints. Just to say a few more things: 1. Oracle did not get rid of hints becouse CBO is not perfect 2.
Huge tables are usually partitioned. What you should do? Oracle recommended to not use hints in Oracle Apps 11i because much more developers does not know to write hints then to write the complete hint, even developers from Oracle. Also this is the generic application for 's of companies and many SQL's need to execute differently dependant on the data skew with different column histograms and sizes.
In this case hints are bad. May way of thinking, maybe wrong. Hints are advanced thing that somebody should understand before try to use it at all. As we said one time everything is good if you are using that in the proper case.
You do not want to gather statistics for some huge tables because you do not want to waste hardware resources. For that you have set statistics option but in this case you are playing partly blind game, relying on your knowledge of future data not easy in some cases. It looks easier to use hints in some particular cases. At the end hints are funny because you can understand which kind of crazy things CBO is using. Does Oracle 10g have hints? I am sure YES. I am going back to play chess, I have got headache.
Regards, Zoran. December 30, - am UTC. It is that you are doing the optimizers job, you won't be able to take advantage of new things in the future and you are hand tuning each and every single sql statment in the system.
I am just saying that hints are sometimes very useful. Important SQL's that should never execute slow if possible. What do you mean by new functionality in the future? If I have some particular SQL that should run that way for many years which new functionality I should use?
I am not that sure that it is a big deal to change a few critical SQL's from time to time. We are doing code changes in any case from time to time. You should just be aware of hints. Are you saying that gathering statistics here is not wasting resources, reducing scalability?
If you do not need to do this why should your collecting statistics will be better then to put a few hints into a few SQL's not all.
Are you using hints at all? What about bind peeking imperfection? Do we have hints in 10g? Why there is not dessuport for hints as Oracle was saying it will be the case in 9i?
It looks to me that there are more and more hints, to not say optimizer hidden parameters. I use hints -- the "good ones". I am loathe to use the others. T3, KS. Do you have any suggestions, or do I just have developers use hints for these queries? Thanks Tom. December 30, - pm UTC. I can demonstrate the same thing without the bind variable, just trying to eliminate issues. By having them put RULE hint on the querys the users are much happier with the performance of the system.
I realize that if they enter 1 character the query probably will run slower with the RULE hint. I would like very much to use the CBO but in the end user satisfaction with the system is more important than my wants.
I am also concerned about the potential for RBO to not be available in future release 10g? If you have any suggestions on how I can get the same overall performance using CBO, I would love to hear them. And again, you are the best resource for learning anything that I have ever discovered. Happy New Year. December 31, - am UTC. I like to use this substr technique with LIKE whenever possible since most of the time the bind variable does have a minimum length typically people must enter at least A charcter, if not more.
Tom, Thanks for your response about hints. I agree with you what you said. Your idea is to not use hints or use less constraining hints as much as possible. These SQL's are the most important in many cases. I just have a little bit different attitude when you have some critical SQL's that span fast-growing OLTP tables partitioned in most cases.
I am trying to optimize my time spent in optimizing some SQL. In your book in chapter 6, on page , you said in the NOTE: do not worry if you cannot replicate my findings exactly. To not mention system statistics that are very relevant for CBO. Of course Oracle is recommended collecting them and tweaking them during the different workload.
You described in the book the most important of course. That means sometimes we need to mask hints with using many alter session commands.
Is this true? Are you using stored outlines at all? Do you think that Oracle outlines are good at all? Does this mean that even in 0. Also System stats can harm other SQL's that are working in the fastest way. At the end as you said the great argument, you should beleive in Oracle CBO more because it is smarter then you and will make your SQL's running the fastest way.
Also you should use what is provided to you much more and not invent water, but sometimes you need to direct Oracle CBO like a child to listen what you have to say.
In some small number of cases I will rely on old-fashion do what I am saying to you hints. Maybe in 10g this will change and I will forget hints forever. Your good response can change all my wrong thinking of course. Thanks for any response in advance. January 05, - pm UTC. I come down on the side of "don't do that". Tom, Thanks a lot for always good response.
There are too many ways to somebody to overuse some Oracle feature. You talked a lot about overusing partitioning even when there is not any need for them. I am fighting against partitioning used in some stupid ways for many years. Idea is to use it when is good to use it. If there is not hints in the SQL you are free to tune that SQL up to the limit with changing the environment for SQL I can say that there are more things not to use "bad hints" as you numbered to us.
Just to say that sometimes because of Oracle 8. Have you ever looked into setting the database parameters for Oracle Applications 11i.
And this is Oracle product where Oracle knows everything about database internals. Imagine now some third party product, how easy is to setup the proper environment for all SQL's. Oracle invented first hints and later on stored outlines to help with fixing execution plans for non-perfect CBO.
They are all specific to the database version you have even different parameters between 8. It is becoming better and better with new database versions. Do we have "bad hints" in Oracle Apps 11i? I've been doing oracle work for about 20 years so this blew me away because I didn't know about it.
The original author is Garry Robinson, presented also is the original material published in Quest Pipeline newsletter hope I'm not breaking some copyright. Material from Quest is like as from you usually quite reliable so you can imagine my concern. For OLTP applications that rely on packaged stored procedures, this can be bad news for response times. The first way is to use a hint when declaring the cursor. A further disadvantage is that there is no instance parameter that can "turn off" the hint if you decide to call the same procedure from a batch job.
February 12, - pm UTC. I've never used hints other then what I consider "good" hints in plsql. But in your case I want quality not quantity. Not that your response was bad, but it leaves me wanting. I am with you on "good vs. This assumption made it easy to test execution plans. Just use simple "set autotrace on" or other "explain plan" stuff for quick and dirty results.
Hence the need to run sql standalone. Are these statements accurate? I don't want to over react. I only want to understand something I did not realize was going on and correct my own testing and tuning methodologies that I have used for many years but which now I may have to consider invalid. Here is my dilemma: if a junior developer asks me, can you show me how to see the plans for the sql in my package, what do I tell him? Considered a senior oracle guy wherever I go, I am expected to be able to handle this kind of stuff and I don't feel confident anymore just showing him how to hack his code and do autotrace on.
So, Would you say the two statements above are accurate? Is my only resort to use tkprof? Would you suggest I use the hidden parameter as much as you and I both don't like this? Do you have any other advice to add? Kevin Meade. I've always found explain plans to be "of great academic interest" but when the rubber meets the road -- there is nothing but nothing that will supplant tkprof. If you run this script in 8. Only if you set first rows in your session would you discover a difference and only in the older releases.
I don't really see even that much of a change from the 8. Quick and Dirty I would only use set autotrace on for 'quick and dirty' tests. Things in sqlplus like arraysize, and other's i'm sure. I'd tell him "plans are of great academic interest, the proof is in run time performance. Thanks Brent, point well taken. I have been bitten by arraysize a couple of times was not aware of other factors but that don't mean there ain't any as you point out.
So share a little with me. If so, to what and why? If not, why not? Indeed they all seemed a little agitated that the usual "quick and dirty" may be a little too dirty than we all hade expected. As for tkprof, well, its great if you can get easy access to it.
I know what some people will say I have read the posts here about this issue and possible ways to work tkprof, and I was hoping to avoid a discussion of this topic , but the fact is not all oracle experiences are the same a good thing really. Though many oracle people have used tkprof consistently in their careers, many others have worked for outfits that divide IM lines on a hardware basis.
In practicality this means that you don't get access to the host systems and you certainly can't run programs on them whenever you like. I work in one such environment. This makes it difficult at best to run tkprof and get its results. I am trying to gauge how important this issue is and if I need to now insist on the ability to use tkprof on a regular basis for both myself and other developers here. We have several hundered oracle people of many levels of experience.
Giving access to many host systems to this many people becomes a protocol problem of sorts gonna need a meeting of the architects, then the support areas, then the app areas, then the infrastructure area, then the security area hmm.. Ah but I don't want to complain. Its good to see others offer help, makes me feel more like a community member. Thanks, Kevin Meade.
You're right about tkprof I don't think I trusted it's answers as much as tkprof. This will help a lot for getting an accurate plan for a query that would normally take tens of minutes to run Very nice indeed. Wish I'd though of that a couple months ago! February 13, - pm UTC. Why isn't the CBO using the index if I try to return other columns?
Can you help me understand why the CBO is working the way it is? Thanks very much for your help. March 04, - pm UTC. I'll guess "not very much". It is saying "hey, table is just a block, why bother with an index if I have to a index range scan and b table access" that is also why it is index full scanning I have analyzed the table.
March 05, - am UTC. You are basically issueing: select
0コメント