Re: OCP Question (Perf Tuning)
We'll thats exactly what I'm doing right now, studying Oracle University instructor guides to temporarily start thinking like OU myself again - I'll be instructing an OCP Review course next week, meant for people who want to pass OCP. And in order to not distract the students, I won't even mention the real life situation too much, except in really misleading cases... Hi Tanel I have a Dr. friend of mine who teaches heart surgeons how to pass their medical exams. Basically he simply teaches them to rip out the offending organ (generally found towards the left hand side of the upper chest), give it a bit of a squeeze, shake out any crap that might be inside, measure the Beats outside Chest Heart Ratio (BCHR), stick it back in, hope it's done some good and that the patient at least survives until they're 10 miles from the hospital. He tells the students that it's important to do the above steps in the correct order as it's in the medical exam. After the students have scribbled everything down, he then leans forward and quietly whispers to them that in the real world, heart surgeons actual first check whether or not it's actually necessary to cut out the heart *beforehand*. He then gives them a little wink and a nod, the students usually reply with an hh and the class moves on to discuss how to remove blood stains from their white surgical outfits. IMHO it's all a little scary and a touch surreal and yet it all sounds strangely familiar ... Cheers ;) Richard -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Richard Foote INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: OCP Question (Perf Tuning)
After the students have scribbled everything down, he then leans forward and quietly whispers to them that in the real world, heart surgeons actual first check whether or not it's actually necessary to cut out the heart *beforehand*. He then gives them a little wink and a nod, the students usually reply with an hh and the class moves on to discuss how to remove blood stains from their white surgical outfits. IMHO it's all a little scary and a touch surreal and yet it all sounds strangely familiar ... And they don't have too many backups either :( Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: OCP Question (Perf Tuning)
What I have heard is that all the OCP questions are taken from the Oracle University Student Guide. After all, you wouldn't you expect the class to prepare you? Someone suggested that you think like a computer. Well, for the philosophy behind the exam, think like an organization, namely Oracle University. We'll thats exactly what I'm doing right now, studying Oracle University instructor guides to temporarily start thinking like OU myself again - I'll be instructing an OCP Review course next week, meant for people who want to pass OCP. And in order to not distract the students, I won't even mention the real life situation too much, except in really misleading cases... Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Re[2]: OCP Question (Perf Tuning)
Btw, why do you want to take an errorstack on a behaviour-changing event? Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, January 06, 2004 11:24 PM Oracle doesn't but Jonathan Lewis does, in his Tutorials. I found out about that from Scott Gosset in 8i internals class in NYC. Seems still to be true. Gospel of Jonathan should suffice, however. I just discovered that my 10053 trace name errorstack forever, level 12 causes ORA-600 in 9.2. Let me look for a patch and I'll come back later. As for the question being nontrivial and unfair, I agree. On 2004.01.06 14:29, Jonathan Gennick wrote: Tuesday, January 6, 2004, 1:59:26 PM, Mladen Gogala ([EMAIL PROTECTED]) wrote: MG Nope. The answer is b). In the FIRST_ROWS mode, optimizer prefers NL to all other MG methos despite the price. Does Oracle themselves *document* that what you say is the case? I believe you, but I'm not sure that Oracle documents what you just said, so short of looking at the code, I'm not sure how anyone could be expected to really *know* what the answer to the original question was, which makes me wonder if the question is really even a fair question to ask. Do we *know* that B is the case, or have we just always *observed* that it *happens to be* the case? Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to [EMAIL PROTECTED] and include the word subscribe in either the subject or body. MG On 2004.01.06 13:44, Jay Wade wrote: Hello: I was looking through some OCP questions posted on the web and came across the one below. I believe the answer is (D), because the join type would be dependent on the number of rows within the table. Is this correct or does the OPTIMIZER_MODE set to FIRST_ROWS alter this behavior? The cost-based optimizer can choose between a nested loops join and a sort merge join operation. All tables are analyzed and the OPTIMIZER_MODE is set to FIRST_ROWS. Which execution plan will be the result? a. The sort-merge join. b. The nested loops join. c. This depends on some sort parameter values. d. This depends on the number of rows in each table. _ Check your PC for viruses with the FREE McAfee online computer scan. http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Wade INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). MG -- MG Mladen Gogala MG Oracle DBA MG -- MG Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Gennick INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L
OCP Question (Perf Tuning)
Hello: I was looking through some OCP questions posted on the web and came across the one below. I believe the answer is (D), because the join type would be dependent on the number of rows within the table. Is this correct or does the OPTIMIZER_MODE set to FIRST_ROWS alter this behavior? The cost-based optimizer can choose between a nested loops join and a sort merge join operation. All tables are analyzed and the OPTIMIZER_MODE is set to FIRST_ROWS. Which execution plan will be the result? a. The sort-merge join. b. The nested loops join. c. This depends on some sort parameter values. d. This depends on the number of rows in each table. _ Check your PC for viruses with the FREE McAfee online computer scan. http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Wade INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: OCP Question (Perf Tuning)
D is probably the answer, but most of the time oracle will choose nested loop. Beware the OCP tuning test. It is completely and totally inaccurate. I emailed the author of the Sybex tuning book and he agreed with me. He said he wrote the book to the test and knows its garbage. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, January 06, 2004 1:44 PM Hello: I was looking through some OCP questions posted on the web and came across the one below. I believe the answer is (D), because the join type would be dependent on the number of rows within the table. Is this correct or does the OPTIMIZER_MODE set to FIRST_ROWS alter this behavior? The cost-based optimizer can choose between a nested loops join and a sort merge join operation. All tables are analyzed and the OPTIMIZER_MODE is set to FIRST_ROWS. Which execution plan will be the result? a. The sort-merge join. b. The nested loops join. c. This depends on some sort parameter values. d. This depends on the number of rows in each table. _ Check your PC for viruses with the FREE McAfee online computer scan. http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Wade INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ryan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: OCP Question (Perf Tuning)
Nope. The answer is b). In the FIRST_ROWS mode, optimizer prefers NL to all other methos despite the price. On 2004.01.06 13:44, Jay Wade wrote: Hello: I was looking through some OCP questions posted on the web and came across the one below. I believe the answer is (D), because the join type would be dependent on the number of rows within the table. Is this correct or does the OPTIMIZER_MODE set to FIRST_ROWS alter this behavior? The cost-based optimizer can choose between a nested loops join and a sort merge join operation. All tables are analyzed and the OPTIMIZER_MODE is set to FIRST_ROWS. Which execution plan will be the result? a. The sort-merge join. b. The nested loops join. c. This depends on some sort parameter values. d. This depends on the number of rows in each table. _ Check your PC for viruses with the FREE McAfee online computer scan. http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Wade INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re[2]: OCP Question (Perf Tuning)
Tuesday, January 6, 2004, 1:59:26 PM, Mladen Gogala ([EMAIL PROTECTED]) wrote: MG Nope. The answer is b). In the FIRST_ROWS mode, optimizer prefers NL to all other MG methos despite the price. Does Oracle themselves *document* that what you say is the case? I believe you, but I'm not sure that Oracle documents what you just said, so short of looking at the code, I'm not sure how anyone could be expected to really *know* what the answer to the original question was, which makes me wonder if the question is really even a fair question to ask. Do we *know* that B is the case, or have we just always *observed* that it *happens to be* the case? Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to [EMAIL PROTECTED] and include the word subscribe in either the subject or body. MG On 2004.01.06 13:44, Jay Wade wrote: Hello: I was looking through some OCP questions posted on the web and came across the one below. I believe the answer is (D), because the join type would be dependent on the number of rows within the table. Is this correct or does the OPTIMIZER_MODE set to FIRST_ROWS alter this behavior? The cost-based optimizer can choose between a nested loops join and a sort merge join operation. All tables are analyzed and the OPTIMIZER_MODE is set to FIRST_ROWS. Which execution plan will be the result? a. The sort-merge join. b. The nested loops join. c. This depends on some sort parameter values. d. This depends on the number of rows in each table. _ Check your PC for viruses with the FREE McAfee online computer scan. http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Wade INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). MG -- MG Mladen Gogala MG Oracle DBA MG -- MG Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Gennick INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: OCP Question (Perf Tuning)
thought so, I'm not 100% certain the OCP will say that though. alot of inaccuracies in that test. btw, Ive been playing with first_rows lately. I've noticed that it has a preference for 'INDEX FULL SCAN' over 'INDEX RANGE SCAN'. Ive found that in some test cases where you have two tables approximately 3m and 1.5m rows in size, that INDEX RANGE SCAN actually returns the first 25 or so records faster, than 'INDEX FULL SCAN', there by making FIRST_ROWS, inferior. Surprised me. I've read some docs on this and I think that a range scan is always preferably when you only want a few rows? What am I missing? - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, January 06, 2004 1:59 PM Nope. The answer is b). In the FIRST_ROWS mode, optimizer prefers NL to all other methos despite the price. On 2004.01.06 13:44, Jay Wade wrote: Hello: I was looking through some OCP questions posted on the web and came across the one below. I believe the answer is (D), because the join type would be dependent on the number of rows within the table. Is this correct or does the OPTIMIZER_MODE set to FIRST_ROWS alter this behavior? The cost-based optimizer can choose between a nested loops join and a sort merge join operation. All tables are analyzed and the OPTIMIZER_MODE is set to FIRST_ROWS. Which execution plan will be the result? a. The sort-merge join. b. The nested loops join. c. This depends on some sort parameter values. d. This depends on the number of rows in each table. _ Check your PC for viruses with the FREE McAfee online computer scan. http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Wade INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ryan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: OCP Question (Perf Tuning)
Think like a computer. Which execution plan will be the result? result of what? an insert statement? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: OCP Question (Perf Tuning)
Note in-line. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, January 06, 2004 6:59 PM Nope. The answer is b). In the FIRST_ROWS mode, optimizer prefers NL to all other methos despite the price. Unless the alternative is a full tablescan on the inner table - in which case merge or hash joins can be considered. The question itself is non-trivial, as the cost of a nested loop is: Cost of outer acquisition + Cost of inner access * cardinality of outer acquisition. But the cost of a merge join is: Cost of first acquisition + cost of first sort + Cost of second acquisition + cost of second sort + Cost of merge It seems likely that if the first table returned 1 or 2 rows, then a nested loop with FTS could be cheaper than a sort merge, but if the outer table returned 3 Oracle would switch to a sort merge. (Assuming equijoin). On the other hand, if the second table required a very large sort, I'm sure you could engineer a sort_area_size that would make the sort cost more than three times the cost of a simple tablescan - which means you could change the access path by changing the sort_area_size. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: OCP Question (Perf Tuning)
FIRST_ROWS would alter the behavior regardless of the number of rows. -Original Message- Jay Wade Sent: Tuesday, January 06, 2004 1:44 PM To: Multiple recipients of list ORACLE-L Hello: I was looking through some OCP questions posted on the web and came across the one below. I believe the answer is (D), because the join type would be dependent on the number of rows within the table. Is this correct or does the OPTIMIZER_MODE set to FIRST_ROWS alter this behavior? The cost-based optimizer can choose between a nested loops join and a sort merge join operation. All tables are analyzed and the OPTIMIZER_MODE is set to FIRST_ROWS. Which execution plan will be the result? a. The sort-merge join. b. The nested loops join. c. This depends on some sort parameter values. d. This depends on the number of rows in each table. _ Check your PC for viruses with the FREE McAfee online computer scan. http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Wade INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: OCP Question (Perf Tuning)
What I have heard is that all the OCP questions are taken from the Oracle University Student Guide. After all, you wouldn't you expect the class to prepare you? Someone suggested that you think like a computer. Well, for the philosophy behind the exam, think like an organization, namely Oracle University. I can't find anything nearly this detailed in the Student Guide, therefore I conclude that this question will not appear on the exam. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, January 06, 2004 1:34 PM To: Multiple recipients of list ORACLE-L thought so, I'm not 100% certain the OCP will say that though. alot of inaccuracies in that test. btw, Ive been playing with first_rows lately. I've noticed that it has a preference for 'INDEX FULL SCAN' over 'INDEX RANGE SCAN'. Ive found that in some test cases where you have two tables approximately 3m and 1.5m rows in size, that INDEX RANGE SCAN actually returns the first 25 or so records faster, than 'INDEX FULL SCAN', there by making FIRST_ROWS, inferior. Surprised me. I've read some docs on this and I think that a range scan is always preferably when you only want a few rows? What am I missing? - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, January 06, 2004 1:59 PM Nope. The answer is b). In the FIRST_ROWS mode, optimizer prefers NL to all other methos despite the price. On 2004.01.06 13:44, Jay Wade wrote: Hello: I was looking through some OCP questions posted on the web and came across the one below. I believe the answer is (D), because the join type would be dependent on the number of rows within the table. Is this correct or does the OPTIMIZER_MODE set to FIRST_ROWS alter this behavior? The cost-based optimizer can choose between a nested loops join and a sort merge join operation. All tables are analyzed and the OPTIMIZER_MODE is set to FIRST_ROWS. Which execution plan will be the result? a. The sort-merge join. b. The nested loops join. c. This depends on some sort parameter values. d. This depends on the number of rows in each table. _ Check your PC for viruses with the FREE McAfee online computer scan. http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Wade INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ryan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to
Re: OCP Question (Perf Tuning)
Jonathan, you're right. Interesting thing is that bitmap indexes, which were made for DW processing and not for OLTP will also be considered for NL context in First_Rows mode. Here is the proof, which also proves that I'm a lousy typist: SQL set autorace on explain SP2-0158: unknown SET option autorace SQL set autotrace on explain SQL select /*+ first_rows ordered */ ename,dname,loc from emp e,dept d 2 where e.deptno=d.deptno 3 / ENAME DNAME LOC -- -- - ALLEN SALES CHICAGO WARD SALES CHICAGO JAMES SALES CHICAGO FORD RESEARCH DALLAS MILLER ACCOUNTING NEW YORK SMITH RESEARCH DALLAS JONES RESEARCH DALLAS MARTIN SALES CHICAGO BLAKE SALES CHICAGO CLARK ACCOUNTING NEW YORK SCOTT RESEARCH DALLAS ENAME DNAME LOC -- -- - KING ACCOUNTING NEW YORK TURNER SALES CHICAGO ADAMS RESEARCH DALLAS 14 rows selected. Execution Plan -- 0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=135 Card=8 2 Bytes=4100) 10 TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' (Cost=135 Card=1 B ytes=30) 21 NESTED LOOPS (Cost=135 Card=82 Bytes=4100) 32 TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=82 Bytes=164 0) 42 BITMAP CONVERSION (TO ROWIDS) 54 BITMAP INDEX (SINGLE VALUE) OF 'DEPT_DEPTNO' On 2004.01.06 14:49, Jonathan Lewis wrote: Note in-line. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, January 06, 2004 6:59 PM Nope. The answer is b). In the FIRST_ROWS mode, optimizer prefers NL to all other methos despite the price. Unless the alternative is a full tablescan on the inner table - in which case merge or hash joins can be considered. The question itself is non-trivial, as the cost of a nested loop is: Cost of outer acquisition + Cost of inner access * cardinality of outer acquisition. But the cost of a merge join is: Cost of first acquisition + cost of first sort + Cost of second acquisition + cost of second sort + Cost of merge It seems likely that if the first table returned 1 or 2 rows, then a nested loop with FTS could be cheaper than a sort merge, but if the outer table returned 3 Oracle would switch to a sort merge. (Assuming equijoin). On the other hand, if the second table required a very large sort, I'm sure you could engineer a sort_area_size that would make the sort cost more than three times the cost of a simple tablescan - which means you could change the access path by changing the sort_area_size. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: OCP Question (Perf Tuning)
What I meant is that the question cannot be answered without making human assumptions about the question itself. It is a little difficult (Note little not lot) to believe that such a poorly written question would appear on a test that costs money to take. -Original Message- Someone suggested that you think like a computer. Well, for the philosophy behind the exam, think like an organization, namely Oracle University. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Re[2]: OCP Question (Perf Tuning)
Oracle doesn't but Jonathan Lewis does, in his Tutorials. I found out about that from Scott Gosset in 8i internals class in NYC. Seems still to be true. Gospel of Jonathan should suffice, however. I just discovered that my 10053 trace name errorstack forever, level 12 causes ORA-600 in 9.2. Let me look for a patch and I'll come back later. As for the question being nontrivial and unfair, I agree. On 2004.01.06 14:29, Jonathan Gennick wrote: Tuesday, January 6, 2004, 1:59:26 PM, Mladen Gogala ([EMAIL PROTECTED]) wrote: MG Nope. The answer is b). In the FIRST_ROWS mode, optimizer prefers NL to all other MG methos despite the price. Does Oracle themselves *document* that what you say is the case? I believe you, but I'm not sure that Oracle documents what you just said, so short of looking at the code, I'm not sure how anyone could be expected to really *know* what the answer to the original question was, which makes me wonder if the question is really even a fair question to ask. Do we *know* that B is the case, or have we just always *observed* that it *happens to be* the case? Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to [EMAIL PROTECTED] and include the word subscribe in either the subject or body. MG On 2004.01.06 13:44, Jay Wade wrote: Hello: I was looking through some OCP questions posted on the web and came across the one below. I believe the answer is (D), because the join type would be dependent on the number of rows within the table. Is this correct or does the OPTIMIZER_MODE set to FIRST_ROWS alter this behavior? The cost-based optimizer can choose between a nested loops join and a sort merge join operation. All tables are analyzed and the OPTIMIZER_MODE is set to FIRST_ROWS. Which execution plan will be the result? a. The sort-merge join. b. The nested loops join. c. This depends on some sort parameter values. d. This depends on the number of rows in each table. _ Check your PC for viruses with the FREE McAfee online computer scan. http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Wade INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). MG -- MG Mladen Gogala MG Oracle DBA MG -- MG Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Gennick INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: OCP Question (Perf Tuning)
Thanks for the help. Do you have any ideas to the FIRST_ROWS behavior in regards to NL? I've been looking and found http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96533/optimops.htm#39473 it goes into detail about using hints for joins but now how the FIRST_ROWS forces a join selection on anything other then sample size. I'm interested to know more about the behavior and the links that I am finding don't seem to offer the depth I thought they would. Or maybe they do and I'm missing the boat From: Ryan [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: OCP Question (Perf Tuning) Date: Tue, 06 Jan 2004 11:34:26 -0800 thought so, I'm not 100% certain the OCP will say that though. alot of inaccuracies in that test. btw, Ive been playing with first_rows lately. I've noticed that it has a preference for 'INDEX FULL SCAN' over 'INDEX RANGE SCAN'. Ive found that in some test cases where you have two tables approximately 3m and 1.5m rows in size, that INDEX RANGE SCAN actually returns the first 25 or so records faster, than 'INDEX FULL SCAN', there by making FIRST_ROWS, inferior. Surprised me. I've read some docs on this and I think that a range scan is always preferably when you only want a few rows? What am I missing? - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, January 06, 2004 1:59 PM Nope. The answer is b). In the FIRST_ROWS mode, optimizer prefers NL to all other methos despite the price. On 2004.01.06 13:44, Jay Wade wrote: Hello: I was looking through some OCP questions posted on the web and came across the one below. I believe the answer is (D), because the join type would be dependent on the number of rows within the table. Is this correct or does the OPTIMIZER_MODE set to FIRST_ROWS alter this behavior? The cost-based optimizer can choose between a nested loops join and a sort merge join operation. All tables are analyzed and the OPTIMIZER_MODE is set to FIRST_ROWS. Which execution plan will be the result? a. The sort-merge join. b. The nested loops join. c. This depends on some sort parameter values. d. This depends on the number of rows in each table. _ Check your PC for viruses with the FREE McAfee online computer scan. http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Wade INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ryan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). _ Working moms: Find helpful tips here on managing kids, home, work and yourself. http://special.msn.com/msnbc/workingmom.armx -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Wade INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services
RE: OCP Question (Perf Tuning)
Jonathan noted that Nope. The answer is b). In the FIRST_ROWS mode, optimizer prefers NL to all other methos despite the price. Unless the alternative is a full tablescan on the inner table - in which case merge or hash joins can be considered. For some reason hash joins were excluded from the question. I can't speculate as to what, other than oversight or limiting the answers to 4, the reason for this might be. I suspect the answer *wanted* is b) the focus being that FIRST_ROWS favours index scans and NL joins. ISTM that d) ought to be ruled out as it refers to rows not blocks but as usual I am probably wrong. Niall OCP DBA (the last time you'll see that sig for a while). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Niall Litchfield INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: OCP Question (Perf Tuning)
I see you're running on Oracle 9 there, and that can make a big difference. After posting my hypothesis, I created a test case, which behaved as I had predicted - but the behaviour changed in Oracle 9, and I had to do some tweaking. Turns out my test case highlighted what looks like a but in the SORT costing in Oracle 8 for a sort/merge join. The Oracle 9 costing is better, so Oracle 9 didn't switch to an NL when Oracle 8 did. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, January 06, 2004 9:14 PM Jonathan, you're right. Interesting thing is that bitmap indexes, which were made for DW processing and not for OLTP will also be considered for NL context in First_Rows mode. Here is the proof, which also proves that I'm a lousy typist: -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: OCP Question (Perf Tuning)
A bigger error in option (d) is that it leaves open the ambiguity of whether the rows should, or should not, be part of the answer to the join. Oracle's choice of join could be affected by adding 100 rows to the table that should be included in the join, but remain unchanged if you add 100 rows that should not be included in the join. Frankly it's an appallingly bad question, and I think your assumption about the wrong answer you are required to give is correct. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, January 06, 2004 9:54 PM Jonathan noted that Nope. The answer is b). In the FIRST_ROWS mode, optimizer prefers NL to all other methos despite the price. Unless the alternative is a full tablescan on the inner table - in which case merge or hash joins can be considered. For some reason hash joins were excluded from the question. I can't speculate as to what, other than oversight or limiting the answers to 4, the reason for this might be. I suspect the answer *wanted* is b) the focus being that FIRST_ROWS favours index scans and NL joins. ISTM that d) ought to be ruled out as it refers to rows not blocks but as usual I am probably wrong. Niall -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Re[2]: OCP Question (Perf Tuning)
Thanks again for all your help. Does the ALL_ROWS hint force a preference in Join Types as well? If so would it try to force a HASH JOIN? From: Mladen Gogala [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: Re[2]: OCP Question (Perf Tuning) Date: Tue, 06 Jan 2004 13:24:25 -0800 Oracle doesn't but Jonathan Lewis does, in his Tutorials. I found out about that from Scott Gosset in 8i internals class in NYC. Seems still to be true. Gospel of Jonathan should suffice, however. I just discovered that my 10053 trace name errorstack forever, level 12 causes ORA-600 in 9.2. Let me look for a patch and I'll come back later. As for the question being nontrivial and unfair, I agree. On 2004.01.06 14:29, Jonathan Gennick wrote: Tuesday, January 6, 2004, 1:59:26 PM, Mladen Gogala ([EMAIL PROTECTED]) wrote: MG Nope. The answer is b). In the FIRST_ROWS mode, optimizer prefers NL to all other MG methos despite the price. Does Oracle themselves *document* that what you say is the case? I believe you, but I'm not sure that Oracle documents what you just said, so short of looking at the code, I'm not sure how anyone could be expected to really *know* what the answer to the original question was, which makes me wonder if the question is really even a fair question to ask. Do we *know* that B is the case, or have we just always *observed* that it *happens to be* the case? Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to [EMAIL PROTECTED] and include the word subscribe in either the subject or body. MG On 2004.01.06 13:44, Jay Wade wrote: Hello: I was looking through some OCP questions posted on the web and came across the one below. I believe the answer is (D), because the join type would be dependent on the number of rows within the table. Is this correct or does the OPTIMIZER_MODE set to FIRST_ROWS alter this behavior? The cost-based optimizer can choose between a nested loops join and a sort merge join operation. All tables are analyzed and the OPTIMIZER_MODE is set to FIRST_ROWS. Which execution plan will be the result? a. The sort-merge join. b. The nested loops join. c. This depends on some sort parameter values. d. This depends on the number of rows in each table. _ Check your PC for viruses with the FREE McAfee online computer scan. http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Wade INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). MG -- MG Mladen Gogala MG Oracle DBA MG -- MG Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Gennick INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing
Re: Re[2]: OCP Question (Perf Tuning)
ALL_ROWS would indeed prefer hash join and S/M over NL. On 2004.01.06 17:14, Jay Wade wrote: Thanks again for all your help. Does the ALL_ROWS hint force a preference in Join Types as well? If so would it try to force a HASH JOIN? From: Mladen Gogala [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: Re[2]: OCP Question (Perf Tuning) Date: Tue, 06 Jan 2004 13:24:25 -0800 Oracle doesn't but Jonathan Lewis does, in his Tutorials. I found out about that from Scott Gosset in 8i internals class in NYC. Seems still to be true. Gospel of Jonathan should suffice, however. I just discovered that my 10053 trace name errorstack forever, level 12 causes ORA-600 in 9.2. Let me look for a patch and I'll come back later. As for the question being nontrivial and unfair, I agree. On 2004.01.06 14:29, Jonathan Gennick wrote: Tuesday, January 6, 2004, 1:59:26 PM, Mladen Gogala ([EMAIL PROTECTED]) wrote: MG Nope. The answer is b). In the FIRST_ROWS mode, optimizer prefers NL to all other MG methos despite the price. Does Oracle themselves *document* that what you say is the case? I believe you, but I'm not sure that Oracle documents what you just said, so short of looking at the code, I'm not sure how anyone could be expected to really *know* what the answer to the original question was, which makes me wonder if the question is really even a fair question to ask. Do we *know* that B is the case, or have we just always *observed* that it *happens to be* the case? Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to [EMAIL PROTECTED] and include the word subscribe in either the subject or body. MG On 2004.01.06 13:44, Jay Wade wrote: Hello: I was looking through some OCP questions posted on the web and came across the one below. I believe the answer is (D), because the join type would be dependent on the number of rows within the table. Is this correct or does the OPTIMIZER_MODE set to FIRST_ROWS alter this behavior? The cost-based optimizer can choose between a nested loops join and a sort merge join operation. All tables are analyzed and the OPTIMIZER_MODE is set to FIRST_ROWS. Which execution plan will be the result? a. The sort-merge join. b. The nested loops join. c. This depends on some sort parameter values. d. This depends on the number of rows in each table. _ Check your PC for viruses with the FREE McAfee online computer scan. http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Wade INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). MG -- MG Mladen Gogala MG Oracle DBA MG -- MG Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Gennick INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself
