Fwd: Re: Optimizer help, get query to run as good as with RULE
Jonathan, Thanks. I am able to get better performance running -- SELECT /*+ CHOOSE */ DISTINCT -- SELECT /*+ RULE */ DISTINCT -- SELECT /*+ index (prcd_instruction, prcd_instruction_pk) */ DISTINCT -- SELECT /*+ index (prcd_instruction_runsheet, prcd_instruction_runsheet_pk) */ DISTINCT -- SELECT /*+ index (part, part_pk) */ DISTINCT -- SELECT /*+ index (stage, stage_pk) */ DISTINCT -- SELECT /*+ use_nl (prcd_instruction) */ DISTINCT -- SELECT /*+ use_nl (part) */ DISTINCT -- SELECT /*+ full(prcd_instruction) parallel(prcd_instruction, 4) */ DISTINCT SELECT /*+ ordered index (part part_pk) use_nl (part ) index (prcd PRCD_IK03) use_nl (prcd) index (PRCD_INSTRUCTION_RUNSHEET PRCD_INSTRUCTION_RUNSHEET_PK) use_nl(PRCD_INSTRUCTION_RUNSHEET) index (STAGE STAGE_PK) use_nl (STAGE) index (RECIPE RECIPE_NDX_1) use_nl (RECIPE) */ DISTINCT PRCD_INSTRUCTION_RUNSHEET.STAGE_NAME, PRCD_INSTRUCTION_RUNSHEET.RECIPE_NAME, PRCD_INSTRUCTION_RUNSHEET.RECIPE_ORDER, PRCD_INSTRUCTION_RUNSHEET.PRCD_ID, PRCD_INSTRUCTION_RUNSHEET.RECIPE_STAGE, PRCD_INSTRUCTION_RUNSHEET.INSTRUCTION_NUMBER, RECIPE.RECIPE_TITLE, PART.PART_NAME, RECIPE.EQP_TYPE, PRCD_INSTRUCTION_RUNSHEET.STAGE_ORDER, STAGE.STAGE_DESC, TO_NUMBER (STAGE.MATCH_ORDER), DECODE (STAGE.MATCH_ORDER, STAGE.STAGE_SORT_ORDER, 'N', 'Y'), PRCD.PRCD_TITLE FROM -- PRCD_INSTRUCTION_RUNSHEET, -- RECIPE, -- PART, -- STAGE, -- PRCD, -- PRCD_INSTRUCTION PRCD_INSTRUCTION, PART, PRCD, PRCD_INSTRUCTION_RUNSHEET, STAGE, RECIPE WHERE ( PART.PART_ID = PRCD_INSTRUCTION.PRCD_ID ) AND ( STAGE.STAGE_ID = PRCD_INSTRUCTION_RUNSHEET.RECIPE_STAGE ) AND ( PRCD.PRCD_ID = PRCD_INSTRUCTION_RUNSHEET.PRCD_ID ) AND ( (RECIPE.RECIPE_NAME = PRCD_INSTRUCTION_RUNSHEET.RECIPE_NAME) AND (RECIPE.RECIPE_ACTIVE_FLAG = 'A') ) AND ( (PRCD.PRCD_ID LIKE PRCD_INSTRUCTION.CALL_PRCD_NAME || '.%') AND (PRCD.PRCD_ACTIVE_FLAG = 'A') ) AND ( ( PRCD_INSTRUCTION_RUNSHEET.HIERARCHICAL = 'Y' ) AND ( PRCD.PRCD_ACTIVE_FLAG = 'A' ) AND ( PART.PART_ACTIVE_FLAG = 'A' ) AND ( PART.OBSELETE_FLAG 'Y' ) AND PRCD_INSTRUCTION_RUNSHEET.PRCD_ID NOT LIKE 'F-%' AND PRCD_INSTRUCTION_RUNSHEET.PRCD_ID NOT LIKE 'L000%' AND PRCD_INSTRUCTION_RUNSHEET.PRCD_ID NOT LIKE 'PCW%' ) / Also, yes, multiple index hints ARE working as SELECT /*+ index (part, part_pk) index (prcd, prcd_ik03) index (prcd_instruction_runsheet prcd_instruction_runsheet_pk) index (stage stage_pk) index (recipe recipe_ndx_1) */ DISTINCT I had encountered an error message when trying multiple index hints earlier and I cannot reproduce it now. I couldn't find examples of multiple hints in the documentation and I came across a Metalink Forum entry posting where Helene Schoone [whose advice I generally respect] had stated You cannot specify multiple tables in the index hint. I didn't catch that she would have meant a single hint but that it did not exclude seperate hints ! Regards Hemant Date: Wed, 19 Feb 2003 13:05:11 -0800 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] X-Comment: Oracle RDBMS Community Forum X-Sender: Jonathan Lewis [EMAIL PROTECTED] Sender: [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] From: Jonathan Lewis [EMAIL PROTECTED] Subject: Re: Optimizer help, get query to run as good as with RULE hint Organization: Fat City Network Services, San Diego, California Can you clarify what you mean by: I find that I cannot specify multiple Index Hints. Just for the sake of checking a point, arrange the tables in the from clause in the order indicated by the RULE path, viz: PRCD_INSTRUCTION PART PRCD PRCD_INSTRUCTION_RUNSHEET STAGE RECIPE (NB Your plan seems to have displayed the odd order switch on table RECIPE due to v9 table prefetching - which is odd because I had heard it was a cost-based thing). Then put in the ORDERED hint, along with a hint for each table to use the index that appears for that table, with a USE_NL hint viz: /*+ ordered index(part PART_PK) use_nl(part) index(PRCD PRCD_IK03) use_nl(prcd) ... etc ... */ This should give you exactly the same access path as the rule path. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon one-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___March 19th USA_(FL)_May 2nd Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) USA_(CA, TX)_August 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] Date: 18 February 2003 04:32 hint Mark, Here's the query in expl_PRCD.sql delete plan_table where statement_id ='PRCD_H'; explain plan set statement_id='PRCD_H' for SELECT /*+ CHOOSE */ DISTINCT -- SELECT /*+ RULE */ DISTINCT -- SELECT /*+ index (prcd_instruction, prcd_instruction_pk) */ DISTINCT -- SELECT /*+ index (prcd_instruction_runsheet, prcd_instruction_runsheet_pk) *T
Re: Optimizer help, get query to run as good as with RULE hint
Can you clarify what you mean by: I find that I cannot specify multiple Index Hints. Just for the sake of checking a point, arrange the tables in the from clause in the order indicated by the RULE path, viz: PRCD_INSTRUCTION PART PRCD PRCD_INSTRUCTION_RUNSHEET STAGE RECIPE (NB Your plan seems to have displayed the odd order switch on table RECIPE due to v9 table prefetching - which is odd because I had heard it was a cost-based thing). Then put in the ORDERED hint, along with a hint for each table to use the index that appears for that table, with a USE_NL hint viz: /*+ ordered index(part PART_PK) use_nl(part) index(PRCD PRCD_IK03) use_nl(prcd) ... etc ... */ This should give you exactly the same access path as the rule path. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon one-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___March 19th USA_(FL)_May 2nd Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) USA_(CA, TX)_August 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] Date: 18 February 2003 04:32 hint Mark, Here's the query in expl_PRCD.sql delete plan_table where statement_id ='PRCD_H'; explain plan set statement_id='PRCD_H' for SELECT /*+ CHOOSE */ DISTINCT -- SELECT /*+ RULE */ DISTINCT -- SELECT /*+ index (prcd_instruction, prcd_instruction_pk) */ DISTINCT -- SELECT /*+ index (prcd_instruction_runsheet, prcd_instruction_runsheet_pk) *T -- SELECT /*+ index (part, part_pk) */ DISTINCT --SELECT /*+ ordered index (part, part_pk) use_nl (part prcd_instruction) */ DIT -- SELECT /*+ index (stage, stage_pk) */ DISTINCT -- SELECT /*+ use_nl (prcd_instruction) */ DISTINCT -- SELECT /*+ use_nl (part) */ DISTINCT -- SELECT /*+ full(prcd_instruction) parallel(prcd_instruction, 4) */ DISTINCT PRCD_INSTRUCTION_RUNSHEET.STAGE_NAME, PRCD_INSTRUCTION_RUNSHEET.RECIPE_NAME, PRCD_INSTRUCTION_RUNSHEET.RECIPE_ORDER, PRCD_INSTRUCTION_RUNSHEET.PRCD_ID, PRCD_INSTRUCTION_RUNSHEET.RECIPE_STAGE, PRCD_INSTRUCTION_RUNSHEET.INSTRUCTION_NUMBER, RECIPE.RECIPE_TITLE, PART.PART_NAME, RECIPE.EQP_TYPE, PRCD_INSTRUCTION_RUNSHEET.STAGE_ORDER, STAGE.STAGE_DESC, TO_NUMBER (STAGE.MATCH_ORDER), DECODE (STAGE.MATCH_ORDER, STAGE.STAGE_SORT_ORDER, 'N', 'Y'), PRCD.PRCD_TITLE FROM PRCD_INSTRUCTION_RUNSHEET, RECIPE, PART, STAGE, PRCD, PRCD_INSTRUCTION WHERE ( PART.PART_ID = PRCD_INSTRUCTION.PRCD_ID ) AND ( STAGE.STAGE_ID = PRCD_INSTRUCTION_RUNSHEET.RECIPE_STAGE ) AND ( PRCD.PRCD_ID = PRCD_INSTRUCTION_RUNSHEET.PRCD_ID ) AND ( (RECIPE.RECIPE_NAME = PRCD_INSTRUCTION_RUNSHEET.RECIPE_NAME) AND (RECI) AND ( (PRCD.PRCD_ID LIKE PRCD_INSTRUCTION.CALL_PRCD_NAME || '.%') AND (PRCD.) AND ( ( PRCD_INSTRUCTION_RUNSHEET.HIERARCHICAL = 'Y' ) AND ( PRCD.PRCD_ACTIVE_FLAG = 'A' ) AND ( PART.PART_ACTIVE_FLAG = 'A' ) AND ( PART.OBSELETE_FLAG 'Y' ) AND PRCD_INSTRUCTION_RUNSHEET.PRCD_ID NOT LIKE 'F-%' AND PRCD_INSTRUCTION_RUNSHEET.PRCD_ID NOT LIKE 'L000%' AND PRCD_INSTRUCTION_RUNSHEET.PRCD_ID NOT LIKE 'PCW%' ) / As you can see, I've even tried Index Hints. The RULE hint gives me the best performance and usage of indexes: Operation OptionsObject ID PID Pos - -- -- SELECT STATEMENT 0 SORT UNIQUE 10 1 TABLE ACCESS BY INDEX R RECIPE21 1 NESTED LOOPS 32 1 NESTED LOOPS 43 1 NESTED LOOPS 54 1 NESTED LOOPS 65 1 NESTED LOOPS 76 1 TABLE ACCESS FULL PRCD_INSTRUCTION 87 1 TABLE ACCESS BY INDEX R PART 97 2 INDEX UNIQUE SCA PART_PK 109 1 TABLE ACCESS BY INDEX R PRCD 116 2 INDEX RANGE SCAN PRCD_IK0312 11 1 TABLE ACCESS BY INDEX R PRCD_INSTRUCTION_RUNSH 135 2 EET INDEX RANGE SCAN PRCD_INSTRUCTION_RUNSH 14 13 1 EET_PK TABLE ACCESS BY INDEX R STAGE154 2 INDEX UNIQUE SCA STAGE_PK 16 15 1 INDEX RANGE SCAN RECIPE_NDX_1
RE: Optimizer help, get query to run as good as with RULE hint
Glenn What is the execution plan _without_ the DISTINCT? I'd be willing to bet that it uses the same indexes as the RBO. If so, try the following: SELECT /*+ NO_MERGE(x) */ DISTINCT * FROM(original query without the distinct) x; Kevin -Original Message- Sent: Friday, February 14, 2003 11:04 AM To: Multiple recipients of list ORACLE-L I have a problem query which will not complete. This query is part of a report run within Oracle Applications. Our versions are: Oracle Server 8.1.7.4, Apps 11.5.3, HP-UX 11.11 Listed below is the query and the explain plan. I ran full statistics on all the tables immediately before executing the query. Using the CBO, it never returns. I cancel the query, but it won't die until I kill the unix process. If I use the /*+ RULE */ hint, the query plan (also listed below) changes dramatically and the query executes in 30 seconds. What could cause the optimizer to behave so differently? We cannot change our instance to RBO, as it would adversely affect everything else and Apps requires CBO anyway. Any suggestions on what else I could do to improve the explain plan withou having to use the hint? (I've tried setting optimizer_index_cost_adj=10, and it changes the plan a little but still does not complete). 1 SELECT COUNT(*) 2 FROM HZ_CUST_ACCT_SITES ACCT_SITE,HZ_PARTY_SITES PARTY_SITE, 3 HZ_LOCATIONS LOC,HZ_LOC_ASSIGNMENTS LOC_ASSIGN 4 WHERE ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID 5 AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID 6 AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID 7 AND NVL(ACCT_SITE.ORG_ID,-99) = NVL(LOC_ASSIGN.ORG_ID,-99) 8* ; Id Par Pos Ins Plan - -- 0 3218SELECT STATEMENT (choose) Cost,rows,bytes (3218,1,31) 10 1 SORT(aggregate) 21 1NESTED LOOPS Cost,rows,bytes (3218,1466,45446) 32 1 HASH JOIN Cost,rows,bytes (3218,617422807,16052992982) 43 1HASH JOIN Cost,rows,bytes (2681,4307,77526) 54 15 TABLE ACCESS (analyzed) AR HZ_CUST_ACCT_SITES_ALL (full) Cost,rows,bytes (1263,4307,34456) 64 22 TABLE ACCESS (analyzed) AR HZ_PARTY_SITES (full) Cost,rows,bytes (1414,493760,4937600) 73 24 TABLE ACCESS (analyzed) AR HZ_LOC_ASSIGNMENTS (full) Cost,rows,bytes (533,430060,3440480) 82 2 INDEX (analyzed) UNIQUE AR HZ_LOCATIONS_U1 (unique scan) Using the RULE hint (completes in 30 seconds): 1 SELECT /*+ RULE */ COUNT(*) 2 FROM HZ_CUST_ACCT_SITES ACCT_SITE,HZ_PARTY_SITES PARTY_SITE, 3 HZ_LOCATIONS LOC,HZ_LOC_ASSIGNMENTS LOC_ASSIGN 4 WHERE ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID 5 AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID 6 AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID 7 AND NVL(ACCT_SITE.ORG_ID,-99) = NVL(LOC_ASSIGN.ORG_ID,-99) 8* ; Id Par Pos Ins Plan - -- 0 SELECT STATEMENT (hint: rule) 10 1 SORT(aggregate) 21 1NESTED LOOPS 32 1 NESTED LOOPS 43 1NESTED LOOPS 54 14 TABLE ACCESS (analyzed) AR HZ_LOC_ASSIGNMENTS (full) 64 2 INDEX (analyzed) UNIQUE AR HZ_LOCATIONS_U1 (unique scan) 73 22 TABLE ACCESS (analyzed) AR HZ_PARTY_SITES (by index rowid) 87 1 INDEX (analyzed) NON-UNIQUE AR HZ_PARTY_SITES_N2 (range scan) 92 25 TABLE ACCESS (analyzed) AR HZ_CUST_ACCT_SITES_ALL (by index rowid) 109 1INDEX (analyzed) NON-UNIQUE AR HZ_CUST_ACCT_SITES_N1 (range scan) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Glenn Travis 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: Toepke, Kevin M INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services
Re: Optimizer help, get query to run as good as with RULE hint
I prefer the analogy of asking if Einstein theory of general relativity was correct before the technology was available to prove it correct ;) Two serious points though: I didn't say untraceable I said 'a lot harder to decide why'. Secondly - if you start with the premise that hints are ignorable, this means you may give up much too soon when trying to use a hint and do something nasty like turning a SQL statement into a great long screed of procedural code. Not that I approve of hints, mind you - but sometimes hinting is the least-worst short-term solution. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon one-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___March 19th USA_(FL)_May 2nd Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) USA_(CA, TX)_August 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] Date: 17 February 2003 03:34 hint Sounds more and more like a if a tree falls in the forest and no-one is there kind of discussion.. We're getting to the stage where there is an indeterminate number of untraceable but deliberate reasons for the optimizer to 'avoid' a hint. Without access to the source code, that's getting pretty close to the statement: the optimizer ignored my hint :-) -- 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: Optimizer help, get query to run as good as with RULE hint
The cartesian merge join in the last two plans is interesting feature. It's the sort of thing you associate with very small tables - not tables which look as if they might be returning large volumes of data. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon one-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___March 19th USA_(FL)_May 2nd Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) USA_(CA, TX)_August 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] Date: 17 February 2003 05:51 hint Here's one bad SQL I am working on right now. Even the execution plan changes with a different SORT_AREA_SIZE ! 9.2.0.2 on Tru64 : Query Plan - -- SELECT STATEMENT Cost = 179625268 SORT UNIQUE HASH JOIN TABLE ACCESS FULL RECIPE HASH JOIN TABLE ACCESS FULL PRCD HASH JOIN TABLE ACCESS FULL PRCD_INSTRUCTION_RUNSHEET MERGE JOIN CARTESIAN HASH JOIN TABLE ACCESS FULL PART TABLE ACCESS FULL PRCD_INSTRUCTION BUFFER SORT TABLE ACCESS FULL STAGE -- 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: Optimizer help, get query to run as good as with RULE hint
Given the limited information I supplied, this is a perfectly reasonable speculation. The following three extracts from the 10053 trace (under 8.1.7.4) may help to clarify my point, though: S_A_S set to 32K: Sort width:2 Area size: 24576 Degree: 1 Blocks to Sort: 13 Row size: 3316 Rows:30 Initial runs: 5 Merge passes:3 Cost / pass: 20 Total sort cost: 37 S_A_S set to 1M Sort width: 29 Area size: 712704 Degree: 1 Blocks to Sort: 13 Row size: 3316 Rows:30 Initial runs: 1 Merge passes:1 Cost / pass: 18 Total sort cost: 16 S_A_S set to 5M Sort width: 144 Area size: 3538944 Degree: 1 Blocks to Sort: 13 Row size: 3316 Rows:30 Initial runs: 1 Merge passes:1 Cost / pass: 35 Total sort cost: 24 As you can see, the total volume to be sorted is about 95K (30 rows x 3,316 bytes). In theory, I would expect any S_A_S over about 128K to cope with this as a single, in-memory, pass with no change in cost. The Cost / pass figure has the slightly more helpful name IO Cost / pass in 9.2 Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon one-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___March 19th USA_(FL)_May 2nd Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) USA_(CA, TX)_August 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] Date: 17 February 2003 04:12 hint Have not had the chance to research this but when I read your message I saw that the numbers might make sense if there is nothing else missing. You are saying the cost / pass 18 when it was 1M. Do not you think that '18' is the cost for one pass of memory sort in 1M of memory? If the answer is yes, then five passes of memory sort/1M each will cost 90 (5 * 18) while one pass of memory sort in 5M of memory will cost 35. What do you think? Regards, Waleed -- 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: Optimizer help, get query to run as good as with RULE hint
Hemant, You are right - the execution plan does change. However it looks as though the cost decreases each time you increase the sort_area_size. Presumably as the sort area grows in size more options become feasible to Oracle so it changes it's approach. This is an indication that the optimisor is actually pretty smart. Is your query really that bad that there are no index candidates when joining 6 tables? Feel free to post the query and a logical explanation of what it's trying to achieve if you'd like some help. The sort unique implies maybe a distinct clause at the top - I hope that isn't there to hide an incorrect join or missing criteria (I've seen that trick a couple of times in the past). Regards, Mark. Hemant K Chitale To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] hkchital@singn cc: et.com.sgSubject: Re: Optimizer help, get query to run as good as with RULE Sent by: hint [EMAIL PROTECTED] m 17/02/2003 16:13 Please respond to ORACLE-L Here's one bad SQL I am working on right now. Even the execution plan changes with a different SORT_AREA_SIZE ! 9.2.0.2 on Tru64 : SQL alter session set sort_area_size=1048576; Session altered. SQL @expl_PRCD 14 rows deleted. Explained. SQL @explain Enter value for statement: PRCD_H Operation OptionsObject ID PID Pos - -- -- SELECT STATEMENT 0 SORT UNIQUE 101 HASH JOIN 211 TABLE ACCESS FULL RECIPE321 HASH JOIN 422 TABLE ACCESS FULL STAGE 541 HASH JOIN 642 TABLE ACCESS FULL PRCD_INSTRUCTION_RUNSH761 EET HASH JOIN 862 TABLE ACCESS FULL PART 981 NESTED LOOPS 1082 TABLE ACCESS FULL PRCD 11 101 TABLE ACCESS FULL PRCD_INSTRUCTION 12 102 13 rows selected. '* Press ENTER for Nested Query Plan **' Query Plan --- SELECT STATEMENT Cost = 376868670 SORT UNIQUE HASH JOIN TABLE ACCESS FULL RECIPE HASH JOIN TABLE ACCESS FULL STAGE HASH JOIN TABLE ACCESS FULL PRCD_INSTRUCTION_RUNSHEET HASH JOIN TABLE ACCESS FULL PART NESTED LOOPS TABLE ACCESS FULL PRCD TABLE ACCESS FULL PRCD_INSTRUCTION 13 rows selected. SQL alter session set sort_area_size=10485760; Session altered. SQL @expl_PRCD 13 rows deleted. Explained. SQL @explain Enter value for statement: PRCD_H Operation OptionsObject ID PID Pos - -- -- SELECT STATEMENT
Re: Optimizer help, get query to run as good as with RULE hint
is actually pretty smart. Is your query really that bad that there are no index candidates when joining 6 tables? Feel free to post the query and a logical explanation of what it's trying to achieve if you'd like some help. The sort unique implies maybe a distinct clause at the top - I hope that isn't there to hide an incorrect join or missing criteria (I've seen that trick a couple of times in the past). Regards, Mark. Hemant K Chitale To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] hkchital@singn cc: et.com.sgSubject: Re: Optimizer help, get query to run as good as with RULE Sent by: hint [EMAIL PROTECTED] m 17/02/2003 16:13 Please respond to ORACLE-L Here's one bad SQL I am working on right now. Even the execution plan changes with a different SORT_AREA_SIZE ! 9.2.0.2 on Tru64 : SQL alter session set sort_area_size=1048576; Session altered. SQL @expl_PRCD 14 rows deleted. Explained. SQL @explain Enter value for statement: PRCD_H Operation OptionsObject ID PID Pos - -- -- SELECT STATEMENT 0 SORT UNIQUE 1 01 HASH JOIN 2 11 TABLE ACCESS FULL RECIPE3 21 HASH JOIN 4 22 TABLE ACCESS FULL STAGE 5 41 HASH JOIN 6 42 TABLE ACCESS FULL PRCD_INSTRUCTION_RUNSH7 61 EET HASH JOIN 8 62 TABLE ACCESS FULL PART 9 81 NESTED LOOPS 10 82 TABLE ACCESS FULL PRCD 11 101 TABLE ACCESS FULL PRCD_INSTRUCTION 12 102 13 rows selected. '* Press ENTER for Nested Query Plan **' Query Plan --- SELECT STATEMENT Cost = 376868670 SORT UNIQUE HASH JOIN TABLE ACCESS FULL RECIPE HASH JOIN TABLE ACCESS FULL STAGE HASH JOIN TABLE ACCESS FULL PRCD_INSTRUCTION_RUNSHEET HASH JOIN TABLE ACCESS FULL PART NESTED LOOPS TABLE ACCESS FULL PRCD TABLE ACCESS FULL PRCD_INSTRUCTION 13 rows selected. SQL alter session set sort_area_size=10485760; Session altered. SQL @expl_PRCD 13 rows deleted. Explained. SQL @explain Enter value for statement: PRCD_H Operation OptionsObject ID PID Pos - -- -- SELECT STATEMENT 0 SORT UNIQUE 1 01 HASH JOIN
Re: Optimizer help, get query to run as good as with RULE hint
Coincidentally, one of the points I mentioned at the Hotsos Symposium was the increasing the sort_area_size could affect execution paths for the worse. (Even when there is no risk of excess memory usage causing swapping). I was going to post a simple example to demonstrate this - and then cane across a really bizarre result in 8.1.7.4 and 9.2.0.2 - Using EXACTLY the same script to generate and report data, and hinting EXACTLY the same execution path, and running the 10053 trace against it, I built an example where the optimizer cost of sorting went UP when I increased the sort_area_size from 1M to 5M for a particular query. The 10053 trace showed: cost / pass 18 when s_a_s was 1M, and cost / pass 35 when s_a_s was 5M - when everything else was exactly the same. BTW - your statistics would suggest to me that I needed to find out what bits of code were doing so much sorting - and see if I could address the problem at source, rather than fiddling with database parameters. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon one-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___March 19th USA_(FL)_May 2nd Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) USA_(CA, TX)_August 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] Date: 14 February 2003 17:54 hint I changed my sort_area_size to 1M (down from 5M) and the query completed in 18 seconds. We had set sort_area_size to 5M at the suggestion of Oracle or other reasons. Looks like it's time to set it back. I ran the disk_sorts query and it returned this: DISK_SORTS AVERAGE_SIZE PEAK_CONCURRENT -- --- 47073 23815K 826 Doesn't this suggest setting sort_area_size larger? -- 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: Optimizer help, get query to run as good as with RULE hint
Did the increase in SORT_AREA_SIZE change the execution plan, regardless of the use of hints (since certain hints can easily be ignored)? Larger SORT_AREA_SIZE might encourage the CBO to choose a SORT-MERGE join, for example... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Sunday, February 16, 2003 1:38 PM Coincidentally, one of the points I mentioned at the Hotsos Symposium was the increasing the sort_area_size could affect execution paths for the worse. (Even when there is no risk of excess memory usage causing swapping). I was going to post a simple example to demonstrate this - and then cane across a really bizarre result in 8.1.7.4 and 9.2.0.2 - Using EXACTLY the same script to generate and report data, and hinting EXACTLY the same execution path, and running the 10053 trace against it, I built an example where the optimizer cost of sorting went UP when I increased the sort_area_size from 1M to 5M for a particular query. The 10053 trace showed: cost / pass 18 when s_a_s was 1M, and cost / pass 35 when s_a_s was 5M - when everything else was exactly the same. BTW - your statistics would suggest to me that I needed to find out what bits of code were doing so much sorting - and see if I could address the problem at source, rather than fiddling with database parameters. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon one-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___March 19th USA_(FL)_May 2nd Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) USA_(CA, TX)_August 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] Date: 14 February 2003 17:54 hint I changed my sort_area_size to 1M (down from 5M) and the query completed in 18 seconds. We had set sort_area_size to 5M at the suggestion of Oracle or other reasons. Looks like it's time to set it back. I ran the disk_sorts query and it returned this: DISK_SORTS AVERAGE_SIZE PEAK_CONCURRENT -- --- 47073 23815K 826 Doesn't this suggest setting sort_area_size larger? -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tim Gorman 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: Optimizer help, get query to run as good as with RULE hint
Absolutely identical plans, and traces - the only change was that the cost of the sort step went up. This was testing on a very simple plan too, trying to trade between sort (order by) and an index driven order by - so nothing as complex as messing with a merge join. If you're interested in the specific example, I'll post you the script offline to recreate the test case: I'll have to pull it from a different machine, though, which is why I can't post it right now. BTW - I still believe quite firmly that hints are never ignored. They may be syntactically incorrect, there may be bugs, they may never become relevant; but I don't think they are ignored. However, I will agree that there are more and more grey areas in 9.2 where the increased scope for (internal) query rewrites is so extensive that it is becoming a lot harder to decide why a hint appears to have been ignored. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon one-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___March 19th USA_(FL)_May 2nd Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) USA_(CA, TX)_August 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] Date: 16 February 2003 23:07 hint Did the increase in SORT_AREA_SIZE change the execution plan, regardless of the use of hints (since certain hints can easily be ignored)? Larger SORT_AREA_SIZE might encourage the CBO to choose a SORT-MERGE join, for example... -- 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: Optimizer help, get query to run as good as with RULE hint
Sounds more and more like a if a tree falls in the forest and no-one is there kind of discussion.. We're getting to the stage where there is an indeterminate number of untraceable but deliberate reasons for the optimizer to 'avoid' a hint. Without access to the source code, that's getting pretty close to the statement: the optimizer ignored my hint :-) --- Jonathan Lewis [EMAIL PROTECTED] wrote: snip BTW - I still believe quite firmly that hints are never ignored. They may be syntactically incorrect, there may be bugs, they may never become relevant; but I don't think they are ignored. However, I will agree that there are more and more grey areas in 9.2 where the increased scope for (internal) query rewrites is so extensive that it is becoming a lot harder to decide why a hint appears to have been ignored. = Connor McDonald web: http://www.oracledba.co.uk web: http://www.oaktable.net email: [EMAIL PROTECTED] GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?iso-8859-1?q?Connor=20McDonald?= 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: Optimizer help, get query to run as good as with RULE hint
Have not had the chance to research this but when I read your message I saw that the numbers might make sense if there is nothing else missing. You are saying the cost / pass 18 when it was 1M. Do not you think that '18' is the cost for one pass of memory sort in 1M of memory? If the answer is yes, then five passes of memory sort/1M each will cost 90 (5 * 18) while one pass of memory sort in 5M of memory will cost 35. What do you think? Regards, Waleed -Original Message- To: Multiple recipients of list ORACLE-L Sent: 2/16/03 3:38 PM Coincidentally, one of the points I mentioned at the Hotsos Symposium was the increasing the sort_area_size could affect execution paths for the worse. (Even when there is no risk of excess memory usage causing swapping). I was going to post a simple example to demonstrate this - and then cane across a really bizarre result in 8.1.7.4 and 9.2.0.2 - Using EXACTLY the same script to generate and report data, and hinting EXACTLY the same execution path, and running the 10053 trace against it, I built an example where the optimizer cost of sorting went UP when I increased the sort_area_size from 1M to 5M for a particular query. The 10053 trace showed: cost / pass 18 when s_a_s was 1M, and cost / pass 35 when s_a_s was 5M - when everything else was exactly the same. BTW - your statistics would suggest to me that I needed to find out what bits of code were doing so much sorting - and see if I could address the problem at source, rather than fiddling with database parameters. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon one-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___March 19th USA_(FL)_May 2nd Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) USA_(CA, TX)_August 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] Date: 14 February 2003 17:54 hint I changed my sort_area_size to 1M (down from 5M) and the query completed in 18 seconds. We had set sort_area_size to 5M at the suggestion of Oracle or other reasons. Looks like it's time to set it back. I ran the disk_sorts query and it returned this: DISK_SORTS AVERAGE_SIZE PEAK_CONCURRENT -- --- 47073 23815K 826 Doesn't this suggest setting sort_area_size larger? -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Khedr, Waleed 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: Optimizer help, get query to run as good as with RULE hint
Here's one bad SQL I am working on right now. Even the execution plan changes with a different SORT_AREA_SIZE ! 9.2.0.2 on Tru64 : SQL alter session set sort_area_size=1048576; Session altered. SQL @expl_PRCD 14 rows deleted. Explained. SQL @explain Enter value for statement: PRCD_H Operation OptionsObject ID PID Pos - -- -- SELECT STATEMENT 0 SORT UNIQUE 101 HASH JOIN 211 TABLE ACCESS FULL RECIPE321 HASH JOIN 422 TABLE ACCESS FULL STAGE 541 HASH JOIN 642 TABLE ACCESS FULL PRCD_INSTRUCTION_RUNSH761 EET HASH JOIN 862 TABLE ACCESS FULL PART 981 NESTED LOOPS 1082 TABLE ACCESS FULL PRCD 11 101 TABLE ACCESS FULL PRCD_INSTRUCTION 12 102 13 rows selected. '* Press ENTER for Nested Query Plan **' Query Plan --- SELECT STATEMENT Cost = 376868670 SORT UNIQUE HASH JOIN TABLE ACCESS FULL RECIPE HASH JOIN TABLE ACCESS FULL STAGE HASH JOIN TABLE ACCESS FULL PRCD_INSTRUCTION_RUNSHEET HASH JOIN TABLE ACCESS FULL PART NESTED LOOPS TABLE ACCESS FULL PRCD TABLE ACCESS FULL PRCD_INSTRUCTION 13 rows selected. SQL alter session set sort_area_size=10485760; Session altered. SQL @expl_PRCD 13 rows deleted. Explained. SQL @explain Enter value for statement: PRCD_H Operation OptionsObject ID PID Pos - -- -- SELECT STATEMENT 0 SORT UNIQUE 101 HASH JOIN 211 TABLE ACCESS FULL RECIPE321 HASH JOIN 422 TABLE ACCESS FULL STAGE 541 HASH JOIN 642 TABLE ACCESS FULL PRCD_INSTRUCTION_RUNSH761 EET HASH JOIN 862 TABLE ACCESS FULL PART 981 NESTED LOOPS 1082 TABLE ACCESS FULL PRCD 11 101 TABLE ACCESS FULL PRCD_INSTRUCTION 12 102 13 rows selected. '* Press ENTER for Nested Query Plan **' Query Plan --- SELECT STATEMENT Cost = 246673321 SORT UNIQUE HASH JOIN TABLE ACCESS FULL RECIPE HASH JOIN TABLE ACCESS FULL STAGE HASH JOIN TABLE ACCESS FULL PRCD_INSTRUCTION_RUNSHEET HASH JOIN TABLE ACCESS FULL PART NESTED LOOPS TABLE ACCESS FULL PRCD TABLE ACCESS FULL PRCD_INSTRUCTION 13 rows selected. SQL alter session set sort_area_size=5120; Session altered. SQL @expl_PRCD 13 rows deleted. Explained. SQL @explain Enter value for statement: PRCD_H Operation OptionsObject ID PID Pos - -- -- SELECT STATEMENT 0 SORT UNIQUE 101 HASH JOIN 211 TABLE ACCESS FULL RECIPE321 HASH JOIN 422 TABLE ACCESS FULL PRCD 541 HASH JOIN 642 TABLE ACCESS FULL PRCD_INSTRUCTION_RUNSH761 EET MERGE JOINCARTESIAN862 HASH JOIN
Re: Optimizer help, get query to run as good as with RULE hint
Did you check swap/paging activity when sort_area_size was at 5m?
Gaining performance on sorts by reducing the amount of
memory used sounds like your box is low on RAM.
Jared
On Friday 14 February 2003 08:44, Glenn Travis wrote:
I changed my sort_area_size to 1M (down from 5M) and the query completed in
18 seconds.
We had set sort_area_size to 5M at the suggestion of Oracle or other
reasons. Looks like it's time to set it back.
I ran the disk_sorts query and it returned this:
DISK_SORTS AVERAGE_SIZE PEAK_CONCURRENT
-- ---
47073 23815K 826
Doesn't this suggest setting sort_area_size larger?
-Original Message-
From: Glenn Travis
Sent: Friday, February 14, 2003 11:04 AM
To: Multiple recipients of list ORACLE-L
Subject: Optimizer help, get query to run as good as with RULE hint
I have a problem query which will not complete. This query
is part of a report run within Oracle Applications.
Our versions are: Oracle Server 8.1.7.4, Apps 11.5.3, HP-UX 11.11
Listed below is the query and the explain plan. I ran full
statistics on all the tables immediately before executing the
query. Using the CBO, it never returns. I cancel the query,
but it won't die until I kill the unix process.
If I use the /*+ RULE */ hint, the query plan (also listed
below) changes dramatically and the query executes in 30 seconds.
What could cause the optimizer to behave so differently? We
cannot change our instance to RBO, as it would adversely
affect everything else and Apps requires CBO anyway. Any
suggestions on what else I could do to improve the explain
plan withou having to use the hint? (I've tried setting
optimizer_index_cost_adj=10, and it changes the plan a little
but still does not complete).
1 SELECT COUNT(*)
2 FROM HZ_CUST_ACCT_SITES ACCT_SITE,HZ_PARTY_SITES PARTY_SITE,
3 HZ_LOCATIONS LOC,HZ_LOC_ASSIGNMENTS LOC_ASSIGN
4 WHERE ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
5 AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
6 AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID
7 AND NVL(ACCT_SITE.ORG_ID,-99) = NVL(LOC_ASSIGN.ORG_ID,-99)
8* ;
Id Par Pos Ins Plan
-
--
0 3218SELECT STATEMENT (choose)
Cost,rows,bytes (3218,1,31)
10 1 SORT(aggregate)
21 1NESTED LOOPS Cost,rows,bytes
(3218,1466,45446)
32 1 HASH JOIN Cost,rows,bytes
(3218,617422807,16052992982)
43 1HASH JOIN Cost,rows,bytes
(2681,4307,77526)
54 15 TABLE ACCESS (analyzed) AR
HZ_CUST_ACCT_SITES_ALL (full) Cost,rows,bytes (1263,4307,34456)
64 22 TABLE ACCESS (analyzed) AR
HZ_PARTY_SITES (full) Cost,rows,bytes (1414,493760,4937600)
73 24 TABLE ACCESS (analyzed) AR
HZ_LOC_ASSIGNMENTS (full) Cost,rows,bytes (533,430060,3440480)
82 2 INDEX (analyzed) UNIQUE AR
HZ_LOCATIONS_U1 (unique scan)
Using the RULE hint (completes in 30 seconds):
1 SELECT /*+ RULE */ COUNT(*)
2 FROM HZ_CUST_ACCT_SITES ACCT_SITE,HZ_PARTY_SITES PARTY_SITE,
3 HZ_LOCATIONS LOC,HZ_LOC_ASSIGNMENTS LOC_ASSIGN
4 WHERE ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
5 AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
6 AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID
7 AND NVL(ACCT_SITE.ORG_ID,-99) = NVL(LOC_ASSIGN.ORG_ID,-99)
8* ;
Id Par Pos Ins Plan
-
--
0 SELECT STATEMENT (hint: rule)
10 1 SORT(aggregate)
21 1NESTED LOOPS
32 1 NESTED LOOPS
43 1NESTED LOOPS
54 14 TABLE ACCESS (analyzed) AR
HZ_LOC_ASSIGNMENTS (full)
64 2 INDEX (analyzed) UNIQUE AR
HZ_LOCATIONS_U1 (unique scan)
73 22 TABLE ACCESS (analyzed) AR
HZ_PARTY_SITES (by index rowid)
87 1 INDEX (analyzed) NON-UNIQUE
AR HZ_PARTY_SITES_N2 (range scan)
92 25 TABLE ACCESS (analyzed) AR
HZ_CUST_ACCT_SITES_ALL (by index rowid)
109 1INDEX (analyzed) NON-UNIQUE AR
HZ_CUST_ACCT_SITES_N1 (range scan)
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Glenn Travis
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting
RE: Optimizer help, get query to run as good as with RULE hint
I've gathered statistics many different ways to get queries against the
trading partner tables (i.e., HZ) to do something better to or equal a
rule hint, and in the end a sort area of 1M or less and leaving
hash_area_size unset (defaults to 2mb or less) works best. With
hash_area_size 2Mb, hash joins on every table in the query fall out of
favor w/ CBO, and in the case of the HZ tables on 11.5.x, NL joins seem
to outperform Hash joins.
Incidentally, in 11.5.7 and higher, many of the trading partner views
seem better optimized. With 11.5.5 and lower, we've had to customize to
get things working well.
-Original Message-
Still
Sent: Saturday, February 15, 2003 1:34 PM
To: Multiple recipients of list ORACLE-L
Did you check swap/paging activity when sort_area_size was at 5m?
Gaining performance on sorts by reducing the amount of
memory used sounds like your box is low on RAM.
Jared
On Friday 14 February 2003 08:44, Glenn Travis wrote:
I changed my sort_area_size to 1M (down from 5M) and the query
completed in
18 seconds.
We had set sort_area_size to 5M at the suggestion of Oracle or other
reasons. Looks like it's time to set it back.
I ran the disk_sorts query and it returned this:
DISK_SORTS AVERAGE_SIZE PEAK_CONCURRENT
-- ---
47073 23815K 826
Doesn't this suggest setting sort_area_size larger?
-Original Message-
From: Glenn Travis
Sent: Friday, February 14, 2003 11:04 AM
To: Multiple recipients of list ORACLE-L
Subject: Optimizer help, get query to run as good as with RULE hint
I have a problem query which will not complete. This query
is part of a report run within Oracle Applications.
Our versions are: Oracle Server 8.1.7.4, Apps 11.5.3, HP-UX 11.11
Listed below is the query and the explain plan. I ran full
statistics on all the tables immediately before executing the
query. Using the CBO, it never returns. I cancel the query,
but it won't die until I kill the unix process.
If I use the /*+ RULE */ hint, the query plan (also listed
below) changes dramatically and the query executes in 30 seconds.
What could cause the optimizer to behave so differently? We
cannot change our instance to RBO, as it would adversely
affect everything else and Apps requires CBO anyway. Any
suggestions on what else I could do to improve the explain
plan withou having to use the hint? (I've tried setting
optimizer_index_cost_adj=10, and it changes the plan a little
but still does not complete).
1 SELECT COUNT(*)
2 FROM HZ_CUST_ACCT_SITES ACCT_SITE,HZ_PARTY_SITES PARTY_SITE,
3 HZ_LOCATIONS LOC,HZ_LOC_ASSIGNMENTS LOC_ASSIGN
4 WHERE ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
5 AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
6 AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID
7 AND NVL(ACCT_SITE.ORG_ID,-99) = NVL(LOC_ASSIGN.ORG_ID,-99)
8* ;
Id Par Pos Ins Plan
-
--
0 3218SELECT STATEMENT (choose)
Cost,rows,bytes (3218,1,31)
10 1 SORT(aggregate)
21 1NESTED LOOPS Cost,rows,bytes
(3218,1466,45446)
32 1 HASH JOIN Cost,rows,bytes
(3218,617422807,16052992982)
43 1HASH JOIN Cost,rows,bytes
(2681,4307,77526)
54 15 TABLE ACCESS (analyzed) AR
HZ_CUST_ACCT_SITES_ALL (full) Cost,rows,bytes (1263,4307,34456)
64 22 TABLE ACCESS (analyzed) AR
HZ_PARTY_SITES (full) Cost,rows,bytes (1414,493760,4937600)
73 24 TABLE ACCESS (analyzed) AR
HZ_LOC_ASSIGNMENTS (full) Cost,rows,bytes (533,430060,3440480)
82 2 INDEX (analyzed) UNIQUE AR
HZ_LOCATIONS_U1 (unique scan)
Using the RULE hint (completes in 30 seconds):
1 SELECT /*+ RULE */ COUNT(*)
2 FROM HZ_CUST_ACCT_SITES ACCT_SITE,HZ_PARTY_SITES PARTY_SITE,
3 HZ_LOCATIONS LOC,HZ_LOC_ASSIGNMENTS LOC_ASSIGN
4 WHERE ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
5 AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
6 AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID
7 AND NVL(ACCT_SITE.ORG_ID,-99) = NVL(LOC_ASSIGN.ORG_ID,-99)
8* ;
Id Par Pos Ins Plan
-
--
0 SELECT STATEMENT (hint: rule)
10 1 SORT(aggregate)
21 1NESTED LOOPS
32 1 NESTED LOOPS
43 1NESTED LOOPS
54 14 TABLE ACCESS (analyzed) AR
HZ_LOC_ASSIGNMENTS (full)
64 2
Optimizer help, get query to run as good as with RULE hint
I have a problem query which will not complete. This query is part of a report run within Oracle Applications. Our versions are: Oracle Server 8.1.7.4, Apps 11.5.3, HP-UX 11.11 Listed below is the query and the explain plan. I ran full statistics on all the tables immediately before executing the query. Using the CBO, it never returns. I cancel the query, but it won't die until I kill the unix process. If I use the /*+ RULE */ hint, the query plan (also listed below) changes dramatically and the query executes in 30 seconds. What could cause the optimizer to behave so differently? We cannot change our instance to RBO, as it would adversely affect everything else and Apps requires CBO anyway. Any suggestions on what else I could do to improve the explain plan withou having to use the hint? (I've tried setting optimizer_index_cost_adj=10, and it changes the plan a little but still does not complete). 1 SELECT COUNT(*) 2 FROM HZ_CUST_ACCT_SITES ACCT_SITE,HZ_PARTY_SITES PARTY_SITE, 3 HZ_LOCATIONS LOC,HZ_LOC_ASSIGNMENTS LOC_ASSIGN 4 WHERE ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID 5 AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID 6 AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID 7 AND NVL(ACCT_SITE.ORG_ID,-99) = NVL(LOC_ASSIGN.ORG_ID,-99) 8* ; Id Par Pos Ins Plan - -- 0 3218SELECT STATEMENT (choose) Cost,rows,bytes (3218,1,31) 10 1 SORT(aggregate) 21 1NESTED LOOPS Cost,rows,bytes (3218,1466,45446) 32 1 HASH JOIN Cost,rows,bytes (3218,617422807,16052992982) 43 1HASH JOIN Cost,rows,bytes (2681,4307,77526) 54 15 TABLE ACCESS (analyzed) AR HZ_CUST_ACCT_SITES_ALL (full) Cost,rows,bytes (1263,4307,34456) 64 22 TABLE ACCESS (analyzed) AR HZ_PARTY_SITES (full) Cost,rows,bytes (1414,493760,4937600) 73 24 TABLE ACCESS (analyzed) AR HZ_LOC_ASSIGNMENTS (full) Cost,rows,bytes (533,430060,3440480) 82 2 INDEX (analyzed) UNIQUE AR HZ_LOCATIONS_U1 (unique scan) Using the RULE hint (completes in 30 seconds): 1 SELECT /*+ RULE */ COUNT(*) 2 FROM HZ_CUST_ACCT_SITES ACCT_SITE,HZ_PARTY_SITES PARTY_SITE, 3 HZ_LOCATIONS LOC,HZ_LOC_ASSIGNMENTS LOC_ASSIGN 4 WHERE ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID 5 AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID 6 AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID 7 AND NVL(ACCT_SITE.ORG_ID,-99) = NVL(LOC_ASSIGN.ORG_ID,-99) 8* ; Id Par Pos Ins Plan - -- 0 SELECT STATEMENT (hint: rule) 10 1 SORT(aggregate) 21 1NESTED LOOPS 32 1 NESTED LOOPS 43 1NESTED LOOPS 54 14 TABLE ACCESS (analyzed) AR HZ_LOC_ASSIGNMENTS (full) 64 2 INDEX (analyzed) UNIQUE AR HZ_LOCATIONS_U1 (unique scan) 73 22 TABLE ACCESS (analyzed) AR HZ_PARTY_SITES (by index rowid) 87 1 INDEX (analyzed) NON-UNIQUE AR HZ_PARTY_SITES_N2 (range scan) 92 25 TABLE ACCESS (analyzed) AR HZ_CUST_ACCT_SITES_ALL (by index rowid) 109 1INDEX (analyzed) NON-UNIQUE AR HZ_CUST_ACCT_SITES_N1 (range scan) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Glenn Travis 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: Optimizer help, get query to run as good as with RULE hint
I changed my sort_area_size to 1M (down from 5M) and the query completed in 18 seconds. We had set sort_area_size to 5M at the suggestion of Oracle or other reasons. Looks like it's time to set it back. I ran the disk_sorts query and it returned this: DISK_SORTS AVERAGE_SIZE PEAK_CONCURRENT -- --- 47073 23815K 826 Doesn't this suggest setting sort_area_size larger? -Original Message- From: Glenn Travis Sent: Friday, February 14, 2003 11:04 AM To: Multiple recipients of list ORACLE-L Subject: Optimizer help, get query to run as good as with RULE hint I have a problem query which will not complete. This query is part of a report run within Oracle Applications. Our versions are: Oracle Server 8.1.7.4, Apps 11.5.3, HP-UX 11.11 Listed below is the query and the explain plan. I ran full statistics on all the tables immediately before executing the query. Using the CBO, it never returns. I cancel the query, but it won't die until I kill the unix process. If I use the /*+ RULE */ hint, the query plan (also listed below) changes dramatically and the query executes in 30 seconds. What could cause the optimizer to behave so differently? We cannot change our instance to RBO, as it would adversely affect everything else and Apps requires CBO anyway. Any suggestions on what else I could do to improve the explain plan withou having to use the hint? (I've tried setting optimizer_index_cost_adj=10, and it changes the plan a little but still does not complete). 1 SELECT COUNT(*) 2 FROM HZ_CUST_ACCT_SITES ACCT_SITE,HZ_PARTY_SITES PARTY_SITE, 3 HZ_LOCATIONS LOC,HZ_LOC_ASSIGNMENTS LOC_ASSIGN 4 WHERE ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID 5 AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID 6 AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID 7 AND NVL(ACCT_SITE.ORG_ID,-99) = NVL(LOC_ASSIGN.ORG_ID,-99) 8* ; Id Par Pos Ins Plan - -- 0 3218SELECT STATEMENT (choose) Cost,rows,bytes (3218,1,31) 10 1 SORT(aggregate) 21 1NESTED LOOPS Cost,rows,bytes (3218,1466,45446) 32 1 HASH JOIN Cost,rows,bytes (3218,617422807,16052992982) 43 1HASH JOIN Cost,rows,bytes (2681,4307,77526) 54 15 TABLE ACCESS (analyzed) AR HZ_CUST_ACCT_SITES_ALL (full) Cost,rows,bytes (1263,4307,34456) 64 22 TABLE ACCESS (analyzed) AR HZ_PARTY_SITES (full) Cost,rows,bytes (1414,493760,4937600) 73 24 TABLE ACCESS (analyzed) AR HZ_LOC_ASSIGNMENTS (full) Cost,rows,bytes (533,430060,3440480) 82 2 INDEX (analyzed) UNIQUE AR HZ_LOCATIONS_U1 (unique scan) Using the RULE hint (completes in 30 seconds): 1 SELECT /*+ RULE */ COUNT(*) 2 FROM HZ_CUST_ACCT_SITES ACCT_SITE,HZ_PARTY_SITES PARTY_SITE, 3 HZ_LOCATIONS LOC,HZ_LOC_ASSIGNMENTS LOC_ASSIGN 4 WHERE ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID 5 AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID 6 AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID 7 AND NVL(ACCT_SITE.ORG_ID,-99) = NVL(LOC_ASSIGN.ORG_ID,-99) 8* ; Id Par Pos Ins Plan - -- 0 SELECT STATEMENT (hint: rule) 10 1 SORT(aggregate) 21 1NESTED LOOPS 32 1 NESTED LOOPS 43 1NESTED LOOPS 54 14 TABLE ACCESS (analyzed) AR HZ_LOC_ASSIGNMENTS (full) 64 2 INDEX (analyzed) UNIQUE AR HZ_LOCATIONS_U1 (unique scan) 73 22 TABLE ACCESS (analyzed) AR HZ_PARTY_SITES (by index rowid) 87 1 INDEX (analyzed) NON-UNIQUE AR HZ_PARTY_SITES_N2 (range scan) 92 25 TABLE ACCESS (analyzed) AR HZ_CUST_ACCT_SITES_ALL (by index rowid) 109 1INDEX (analyzed) NON-UNIQUE AR HZ_CUST_ACCT_SITES_N1 (range scan) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Glenn Travis 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
Help with Query
Title: Message Hi list I need some help with a query. I have 3 tables ORIGINALES, CAMBIOS_ORIGINALES, DATOS_FABRICANTES, I have to be able to query an article for whatever code the user provide, ex: (NUMERO_ORIGINAL, CAMBIO_ORIGINAL, NUMERO_FABRICANTE). The 3 tables have a common column, NUMERO_ORIGINAL. When he type a code, no matter which one, I have to display the NUMERO_ORIGINAL, CAMBIO_ORIGINAL(s), NUMERO_FABRICANTE(s) and the DESCRIPTION of the article. I merged the 3 codes in the CODIGO_ARTICULO column to make the join for that column, but I can't get the DESCRIPTION of the article. Any help would be appreciated, SQL DESC ORIGINALESName ---GRUPO COMPANIA NUMERO_ORIGINAL NRO_DESCRIPCION SQL DESC CAMBIOS_ORIGINALESName GRUPO COMPANIA NUMERO_ORIGINAL CAMBIO_ORIGINAL SERIE SQL DESC DATOS_FABRICANTESName ---GRUPO COMPANIA NUMERO_ORIGINAL NUMERO_FABRICANTE LOCALIDAD *** This is what I came up with CREATE OR REPLACE VIEW V_ARTICULOS AS SELECTO.NUMERO_ORIGINAL CODIGO_ARTICULO, O.NUMERO_ORIGINAL, D.DESCRIPCION, ' ' CAMBIO_ORIGINAL, ' ' NUMERO_FABRICANTE FROM ORIGINALES O, DESCRIPCIONES D WHERE D.NRO_DESCRIPCION = O.NRO_DESCRIPCIONUNIONSELECTC.CAMBIO_ORIGINAL CODIGO_ARTICULO, ' ', ' ', C.CAMBIO_ORIGINAL, ' ' NUMERO_FABRICANTE FROM CAMBIOS_ORIGINALES CUNIONSELECTD.NUMERO_FABRICANTE CODIGO_ARTICULO, ' ', ' ', ' ', D.NUMERO_FABRICANTE FROM DATOS_FABRICANTES D / CODIGO_ARTICULO NUMERO_ORIGINAL DESCRIPCION CAMBIO_ORIGINAL NUMERO_FABRICANTE -- 1 1 DESCRIPCION UNO101-122519C1020 12251-PC1-020 101-122519C1020101-12251PC1000 12251-PC1-020 101-12251PC100010A 10A DESCRIPCION UNO10 10A 1010ABC 10A 10ABC12251-PC1-020 12251-PC1-020 JUNTA DE CULATA12345 12345 DESCRIPCION UNO12345A 12345 12345A12345AA 12345 12345AA1414-TT-1414-BR 3 1414-TT-1414-BR147 147 DESCRIPCION UNO Ramon E. Estevez [EMAIL PROTECTED] 809-565-3121
Re: Help with Query
Ramon E. Estevez wrote: Hi list I need some help with a query. I have 3 tables ORIGINALES, CAMBIOS_ORIGINALES, DATOS_FABRICANTES, I have to be able to query an article for whatever code the user provide, ex: (NUMERO_ORIGINAL, CAMBIO_ORIGINAL, NUMERO_FABRICANTE). The 3 tables have a common column, NUMERO_ORIGINAL. When he type a code, no matter which one, I have to display the NUMERO_ORIGINAL, CAMBIO_ORIGINAL(s), NUMERO_FABRICANTE(s) and the DESCRIPTION of the article. I merged the 3 codes in the CODIGO_ARTICULO column to make the join for that column, but I can't get the DESCRIPTION of the article. Any help would be appreciated, Ramon, Your mistake is not to join (on NUMERO_ORIGINAL) with ORIGINALES in the second and third SELECTs of your union - which by the way would be better defined as UNION ALL to avoid a sort. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult 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).
Help on query
Title: Help on query Dear gurus, I have a table : Tabel Exam STD_NO SUBJECT POINT == === = 001 CHEM A 001 PHYSIC A 001 BIOLOGY A 002 CHEM A 002 PHYSIC B 002 BIOLOGY A 003 CHEM A 003 PHYSIC A 003 BIOLOGY B What is the SQL query to retrieve STD_NO which have CHEM = A and PHYSIC = A? The output should be : STD_NO == 001 003 TIA, Ahmadsyah Alghozi Nugroho Database Engineering Specialist PT Infoglobal AutOptima Jl. Baruk Tengah I/49 Surabaya - Jawa Timur INDONESIA phone : +62 (31) 8708456 ext.113 visit [ORAID!] http://oraid.iwarp.com
Help on query
Title: Help on query Dear gurus, I have a table : Tabel Exam STD_NO SUBJECT POINT == === = 001 CHEM A 001 PHYSIC A 001 BIOLOGY A 002 CHEM A 002 PHYSIC B 002 BIOLOGY A 003 CHEM A 003 PHYSIC A 003 BIOLOGY B What is the SQL query to retrieve STD_NO which have CHEM = A and PHYSIC = A? The output should be : STD_NO == 001 003 TIA, Ahmadsyah Alghozi Nugroho Database Engineering Specialist PT Infoglobal AutOptima Jl. Baruk Tengah I/49 Surabaya - Jawa Timur INDONESIA phone : +62 (31) 8708456 ext.113 visit [ORAID!] http://oraid.iwarp.com
Re: Help on query
SELECT DISTINCT std_no
FROM exam
WHERE POINT IN ('A','B');
At 12:00 AM 5/22/2001 -0800, you wrote:
Dear gurus,
I have a table :
Tabel Exam
STD_NO SUBJECT POINT
== === =
001CHEM A
001PHYSIC A
001BIOLOGY A
002CHEM A
002PHYSIC B
002BIOLOGY A
003CHEM A
003PHYSIC A
003BIOLOGY B
What is the SQL query to retrieve STD_NO which have CHEM = A and PHYSIC = A?
The output should be :
STD_NO
==
001
003
TIA,
Ahmadsyah Alghozi Nugroho
Database Engineering Specialist
PT Infoglobal AutOptima
Jl. Baruk Tengah I/49
Surabaya - Jawa Timur
INDONESIA
phone : +62 (31) 8708456 ext.113
visit [ORAID!] http://oraid.iwarp.comhttp://oraid.iwarp.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Alex B. Cheng
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
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: Help on query
select STD_NO from exam
where SUBJECT in ('CHEM','PHYSIC') and POINT='A';
--- Alex B. Cheng [EMAIL PROTECTED] wrote:
SELECT DISTINCT std_no
FROM exam
WHERE POINT IN ('A','B');
At 12:00 AM 5/22/2001 -0800, you wrote:
Dear gurus,
I have a table :
Tabel Exam
STD_NO SUBJECT POINT
== === =
001CHEM A
001PHYSIC A
001BIOLOGY A
002CHEM A
002PHYSIC B
002BIOLOGY A
003CHEM A
003PHYSIC A
003BIOLOGY B
What is the SQL query to retrieve STD_NO which have
CHEM = A and PHYSIC = A?
The output should be :
STD_NO
==
001
003
TIA,
Ahmadsyah Alghozi Nugroho
Database Engineering Specialist
PT Infoglobal AutOptima
Jl. Baruk Tengah I/49
Surabaya - Jawa Timur
INDONESIA
phone : +62 (31) 8708456 ext.113
visit [ORAID!]
http://oraid.iwarp.comhttp://oraid.iwarp.com
--
Please see the official ORACLE-L FAQ:
http://www.orafaq.com
--
Author: Alex B. Cheng
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX:
(858) 538-5051
San Diego, California-- Public Internet
access / Mailing Lists
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).
__
Do You Yahoo!?
Yahoo! Auctions - buy the things you want at great prices
http://auctions.yahoo.com/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: A. Bardeen
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
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: Help on query
SELECT X.std_no
FROM exam X
,exam Y
WHERE X.std_no = Y.std_no
AND X.subject = 'CHEM' AND X.point = 'A'
AND Y.subject = 'PHYSIC' AND Y.point = 'A'
Alex B. Cheng wrote:
SELECT DISTINCT std_no
FROM exam
WHERE POINT IN ('A','B');
At 12:00 AM 5/22/2001 -0800, you wrote:
Dear gurus,
I have a table :
Tabel Exam
STD_NO SUBJECT POINT
== === =
001CHEM A
001PHYSIC A
001BIOLOGY A
002CHEM A
002PHYSIC B
002BIOLOGY A
003CHEM A
003PHYSIC A
003BIOLOGY B
What is the SQL query to retrieve STD_NO which have CHEM = A and PHYSIC = A?
The output should be :
STD_NO
==
001
003
TIA,
Ahmadsyah Alghozi Nugroho
Database Engineering Specialist
PT Infoglobal AutOptima
Jl. Baruk Tengah I/49
Surabaya - Jawa Timur
INDONESIA
phone : +62 (31) 8708456 ext.113
visit [ORAID!] http://oraid.iwarp.comhttp://oraid.iwarp.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Alex B. Cheng
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
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.com
--
Author: priya ramakrishnan
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
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: Help on query
That was too funny
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Tuesday, May 22, 2001 12:46 PM
SELECT DISTINCT std_no
FROM exam
WHERE POINT IN ('A','B');
At 12:00 AM 5/22/2001 -0800, you wrote:
Dear gurus,
I have a table :
Tabel Exam
STD_NO SUBJECT POINT
== === =
001CHEM A
001PHYSIC A
001BIOLOGY A
002CHEM A
002PHYSIC B
002BIOLOGY A
003CHEM A
003PHYSIC A
003BIOLOGY B
What is the SQL query to retrieve STD_NO which have CHEM = A and PHYSIC =
A?
The output should be :
STD_NO
==
001
003
TIA,
Ahmadsyah Alghozi Nugroho
Database Engineering Specialist
PT Infoglobal AutOptima
Jl. Baruk Tengah I/49
Surabaya - Jawa Timur
INDONESIA
phone : +62 (31) 8708456 ext.113
visit [ORAID!] http://oraid.iwarp.comhttp://oraid.iwarp.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Alex B. Cheng
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
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.com
--
Author: Sam Roberts
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
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).
