Re: Index Constraint
[EMAIL PROTECTED] wrote: An example on my understanding of Reverse Key Indices: Say you have a table EMP with a column EMPNO, and an index on this field.This number is incremented sequentially for every new employee that joins in. And as employees retire, say, the records are deleted. This would generally mean deletion of records, with lower employee numbers. And subsequent deletion of indices. As such, deletions from the index are likely to be concentrated on a small set of leaf blocks towards the beginning of the index. A reverse key index, which reverses the bytes for the column value, could help in uniformly dividing the deletions across various branches of the index, and avoid a skewed index. Raj Raj, I would rather tend to see the benefits in terms of contention avoidance when inserting (typically when the PK is sequence-generated), but by and large I share your view. -- 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 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: Horrendous Execution Plan from CBO
Stahlke, Mark wrote: Greetings, One of our developers came to me with a fairly simple query that runs much faster when she uses the RBO. I looked at the execution plans generated by both the RBO and CBO and the CBO's plan is horrible. I was able to get a reasonable plan from the CBO using a USE_NL hint. Do any of you SQL tuning gurus have any suggestions? I've listed all the gory details below. Thanks, Mark Stahlke Oracle DuhBA Denver Newspaper Agency Mark, You have by now been pointed to the hash join hint, but what I'd like to underline is that the beauty or ugliness of a plan is not exactly where you should start from. Look at your stats: Without hints : 12740 db block gets 53167 consistent gets so about 66,000 logical reads (but 2 disk sorts, which probably hurt) With /*+ RULE */ : 4 db block gets 2828280 consistent gets that's 2,830,000 logical reads (but no sort) With /*+ USE_NL(c p) */ 4 db block gets 3062526 consistent gets or 3,060,000 logical reads (no sort) With the hash hint : 8 db block gets 58649 consistent gets which is 58,650 or about - once again, no sort. In other words, even in its primitive 7.3.4 incarnation, the CBO didn't, in fact, totally botch up the job. Actually, as I tend to think that real beauty lies in logical reads much more than in the plan, for those unfortunate disk sorts the 'no hint' version still is not far beyond the hash join version. Perhaps that simply altering SORT_AREA_SIZE could have done much to help, in terms of elapsed time. For this type of query (join with no other condition than the join condition and similar-sized tables) there is nothing better than the plain old full scan (especially when parallelism kicks in), as your nested loops attempt proves. If I were you, and if creating another index is a bearable nuisance, I would create a concatenated index on the four columns from CNR in your query. If CNR has much more many columns, this would allow Oracle to do a fast full scan of the index (rather than a full scan of the table) - which is likely to mean much fewer blocks to wade through. -- 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 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: SQL Question (DISREGARD 1ST MESSAGE, SORRY)
Viktor wrote: Hello All, It looks as if I've hit a brick wall and I'd very much appreciate if you can help. desc Names FIRST_INIT NOT NULL CHAR(4) SECOND_INIT NOT NULL CHAR(1) INIT_SEQUENCE NOT NULL NUMBER LAST_NAMEVARCHAR2(30) FIRST_NAME VARCHAR2(20) FLAGNOT NULL NUMBER desc MEMBER MEM_FIRST_INIT CHAR(4) MEM_SECOND_INIT CHAR(1) MEM_INIT_SEQUENCENUMBER Member table references Names table on FIRST_INIT, SECOND_INIT, INIT_SEQUENCE (FOREIGN KEYS). Names table has NOT NULL column flag. It can only be 0 or 1 -- means Name is a member. But, not all NAME records with FLAG 1 are in MEMBER. In other words, records in MEMBER usually represent other some other types of memership. But, in this case, I need to get those NAME(parent) records that have FLAG = 1, and those MEMBER(child) records that reference NAMES via foreign keys. Sometimes NAME record with FLAG = 1, also has a MEMBER record, and it could be that record with FLAG = 1 does not have a record in MEMBER. I need all those with FLAG = 1 in NAMES + all records that are in MEMBER. Is there another way besides the UNION: SELECT a.first_init||a.second_init||a.init_sequence INITIALS, a.last_name LAST_NAME, a.first_name FIRST_NAME, a.flag MEMBER_FLAG, FROM names a WHERE a.advisor_flag = 1 UNION SELECT a.first_init||a.second_init||a.init_sequence INITIALS, a.last_name LAST_NAME, a.first_name FIRST_NAME, a.flag MEMBER_FLAG FROM names a, members m WHERE a.first_init = m.mem_first_init and a.second_init = m.mem_second_init and a.init_sequence = m.mem_init_sequence Thanks a lot in advance! Regards, Viktor The UNION is perfect. You could possibly do something with external joins and an 'OR', but it would not be more legible. -- 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 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: Horrendous Execution Plan from CBO
Mark, What were the elapsed times for each run? You show the AUTOTRACE, but did you have SET TIMING ON? Some notes: The CBO plan isn't so horrendous (although the elapsed times would be useful to either validate or negate that statement). The total logical reads were about 66,000 and the physical reads were 96,000. In comparison, the RBO plan used 2.8m logical reads and 69,000 physical reads and the USE_NL plan used 3.0 logical reads and 69,000 physical reads. So, the logical reads are roughly 2% for the CBO plan as the others, while the physical reads are roughly 50% greater... Since all hints activate the CBO, the USE_NL plan was actually using the CBO, not the RBO, so it figured out that the "CNR" tables was larger than "PUB" and performed the FULL table scan against that table, which was slightly better. The RBO chose to lead with a full table scan on "PUB" because that table was the "right-most" in the FROM clause list (i.e. RBO reads right-to-left)... Since "CNR" and "PUB" are both "large" and both roughly the same size, then a SORT-MERGE join was a pretty good choice. As you can see, a NESTED LOOPS join is very expensive in terms of logical reads and a HASH join always works best when one table is close in size to HASH_AREA_SIZE. If SORT_AREA_SIZE is set very large for this database, then the CBO would obviously look favorably upon SORT-MERGE joins when deciding which join method to use; what is the value of SORT_AREA_SIZE? Also, what is the value of HASH_AREA_SIZE? By the default settings of OPTIMIZER_INDEX_CACHING and OPTIMIZER_INDEX_COST_ADJ, the CBO has a rather dim view of the capabilities of indexes. I usually like to set OPTIMIZER_INDEX_CACHING to "90" to better reflect the real behavior of index blocks with respect to the Buffer Cache. What are the settings of these? The tables and indexes involved appear to be analyzed, because the AUTOTRACE output shows what looks like valid stats. Thanks! -Tim - Original Message - From: "Stahlke, Mark" [EMAIL PROTECTED] To: "Multiple recipients of list ORACLE-L" [EMAIL PROTECTED] Sent: Friday, June 07, 2002 3:51 PM Subject: Horrendous Execution Plan from CBO Greetings, One of our developers came to me with a fairly simple query that runs much faster when she uses the RBO. I looked at the execution plans generated by both the RBO and CBO and the CBO's plan is horrible. I was able to get a reasonable plan from the CBO using a USE_NL hint. Do any of you SQL tuning gurus have any suggestions? I've listed all the gory details below. Thanks, Mark Stahlke Oracle DuhBA Denver Newspaper Agency The Gory Details: Background: Oracle 7.3.4 (I know, I know). PUB has 531324 rows. CNR has 601007 rows. Both tables analyzed. The Query: 1 SELECT 2 c.acct_key, 3 c.pub, 4 c.ref_nbr, 5 c.sls_nbr_1 cnr_sls_nbr, 6 p.sls_eff_iss_1, 7 p.sls_nbr_1_1, 8 p.sls_nbr_1_2, 9 p.sls_eff_iss_2, 10 p.sls_nbr_2_1, 11 p.sls_nbr_2_2, 12 p.sls_eff_iss_3, 13 p.sls_nbr_3_1, 14 p.sls_nbr_3_2 15 FROM cnr c, pub p 16 WHERE c.acct_key = p.acct_key 17* AND c.pub = p.pub Plan and stats without hints: Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=28838 Card=597847 By tes=100438296) 1 0 MERGE JOIN (Cost=28838 Card=597847 Bytes=100438296) 2 1 SORT (JOIN) 3 2 TABLE ACCESS (FULL) OF 'PUB' (Cost=841 Card=529489 Byt es=55066856) 4 1 SORT (JOIN) 5 4 TABLE ACCESS (FULL) OF 'CNR' (Cost=2195 Card=598749 By tes=38319936) Statistics -- 365 recursive calls 12740 db block gets 53167 consistent gets 96684 physical reads 4956 redo size 45285104 bytes sent via SQL*Net to client 441377 bytes received via SQL*Net from client 40070 SQL*Net roundtrips to/from client 0 sorts (memory) 2 sorts (disk) 601007 rows processed Plan and stats with /*+ RULE */ Execution Plan -- 0 SELECT STATEMENT Optimizer=HINT: RULE 1 0 NESTED LOOPS 2 1 TABLE ACCESS (FULL) OF 'PUB' 3 1 TABLE ACCESS (BY ROWID) OF 'CNR' 4 3 INDEX (RANGE SCAN) OF 'PK_CNR' (UNIQUE) Statistics -- 0 recursive calls 4 db block gets 2828280 consistent gets 69635 physical reads 0 redo size 45285104 bytes sent via SQL*Net to client 441389 bytes received via SQL*Net from client 40070 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 601007 rows processed Plan and stats with /*+ USE_NL(c p) */ Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1204217 Card=601007 Bytes=100969176) 1 0 NESTED LOOPS (Cost=1204217 Card=601007 Bytes=100969176) 2 1 TABLE ACCESS (FULL) OF 'CNR' (Cost=2203 Card=601007 Byte s=38464448) 3 1 TABLE ACCESS
Re: connect as SYSDBA using ADO
From my experience there is no way that you can use a parameter to connect as SYSDBA. I was reading that you are to connect as INTERNAL if you want to connect as SYSDBA. But we all know that is a bad idea and impossible in 9i anyway. If you find away could you let me know. Thanks Jay - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, June 07, 2002 3:47 PM I am trying to find out how to connect as SYSDBA using ADO in Visual Basic. Did not see any mention of sysdba in the doc. Alex Hillman -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Alex Hillman 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: Jay 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).
Why is Parallel Query Running after upgrading to 8.1.7.2
I have just upgraded my 8.0.4 database to 8.1.7.2. Previously, parallel query would only run on objects that a DEGREE set 1. I just checked and all of my tables and indexes have DEGREE set to 1. So are most of my queries now running in parallel? I just checked my init.ora file and I haven't added any new parameters or changed any existing ones. I'm going to go research some more on Metalink and in the on-line manuals. However, if anyone could give me a quick run-down in what's changed in parallel query in 8.1.7.2, I'd appreciate it. We are making the initial decision whether or not to go with this upgrade in a couple of hours, so I'd like to figure this out by then. Thanks, Cherie Machler Oracle DBA Gelco Information Network -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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: Why is Parallel Query Running after upgrading to 8.1.7.2
Here's my thinking -- it could be way off the mark... As observed in the output from the 10053 trace, the CBO always considers not only serial full table scans but also parallel full table scans. The big question is exactly what degree of parallelism is it using in its cogitations; I haven't had a chance to research that (at least I don't remember). Logically, with no PARALLEL hint in the SQL statement and with the table DEGREE set to 1, then the degree of parallelism considered by the CBO should be 1. But apparently not. Are you sure that it is the value 1 and not DEFAULT in the DEGREE column? --- Anyway, somehow the CBO is coming to the conclusion that the PQ-based full table scan is cheapest, which it would only do if it considered a full table scan to be attractive in the first place. A major reason that the CBO has historically considered full table scans more attractive than one would expect is the default setting of OPTIMIZER_INDEX_CACHING (i.e. 0), which indicates that logical reads equal physical reads. Since execution plans involving indexes frequently produce far more logical reads than full table scan, this assumption of logical reads being 1:1 with physical reads (i.e. the CBO largely considers a physical read as one unit of cost) can make indexed scans seem rather unattractive. What is your setting for OPTIMIZER_INDEX_CACHING? I usually recommend setting OPTIMIZER_INDEX_CACHING = 90, which indicates asks the CBO to discount 90% of logical reads of index blocks as able to be found in the Buffer Cache, thus reducing the final calculation of cost (i.e. physical reads) to 10% of the former. Now, we all know that the actual buffering effect is probably higher than 90%, but I've found truly disturbing behavior when you set the parameter to 99 or so, so I try to give that a wide berth. Plus, there are many situations where a full table scan is truly superior to an indexed scan, so I don't want to go overboard on this... If your setting is still at the default, try executing ALTER SESSION SET OPTIMIZER_INDEX_CACHING = 90 before re-running the statement; see if it doesn't lose the PQ stuff... Hope this helps... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Saturday, June 08, 2002 1:53 PM I have just upgraded my 8.0.4 database to 8.1.7.2. Previously, parallel query would only run on objects that a DEGREE set 1. I just checked and all of my tables and indexes have DEGREE set to 1. So are most of my queries now running in parallel? I just checked my init.ora file and I haven't added any new parameters or changed any existing ones. I'm going to go research some more on Metalink and in the on-line manuals. However, if anyone could give me a quick run-down in what's changed in parallel query in 8.1.7.2, I'd appreciate it. We are making the initial decision whether or not to go with this upgrade in a couple of hours, so I'd like to figure this out by then. Thanks, Cherie Machler Oracle DBA Gelco Information Network -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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: Tim Gorman 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).
Install: 9i on 2000, TNS Listener service not installed
Starting to play around with 9i. Downloaded and installed on Windows 2000 Workstation. So far so good except no TNS Listener service lsnrctl start from the command line creates a listener. This may be a side effect of installing on Workstation instead of Server. Anyone have any insight? Anyone know how to create the listener service after the install? TIA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Robert Monical 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: Why is Parallel Query Running after upgrading to 8.1.7.2
Tim, Thanks for replying on a weekend. After doing some reading, I discovered that parallel_threads_per_cpu is set to the default of 2 on our database. With 8 CPUs, we are seeing 16 parallel threads on these queries, when they do go to parallel (a few of them do not). I experimented with setting parallel_threads_per_cpu to 1 and then we saw 8 parallel threads per statement. Then I set parallel_threads_per_cpu to 0 and now it is not going to parallel at all. That seems to be what our application owners want and we are testing with that right now. Seems to give us better timings. optimizer_index_caching is set to 0 currently. I don't believe that we have ever set it. We are just upgrading from 8.0.4 to 8.1.7.2 today so we are trying to minimize any parameter changes unless they are truly needed so that we can minimize unknown variables when something goes south. However, if we truly need to do this now that we are at 8.1.7.2, maybe we should bite the bullet and do it now. Isn't there another parameter that is used in association with optimizer_index_caching that should also be reset. I remember seeing it several times on the list but the parameter name escapes me now. Thanks again, Cherie Tim Gorman Tim@SageLogix To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] .comcc: Sent by: Subject: Re: Why is Parallel Query Running after upgrading to [EMAIL PROTECTED]8.1.7.2 om 06/08/02 04:53 PM Please respond to ORACLE-L Here's my thinking -- it could be way off the mark... As observed in the output from the 10053 trace, the CBO always considers not only serial full table scans but also parallel full table scans. The big question is exactly what degree of parallelism is it using in its cogitations; I haven't had a chance to research that (at least I don't remember). Logically, with no PARALLEL hint in the SQL statement and with the table DEGREE set to 1, then the degree of parallelism considered by the CBO should be 1. But apparently not. Are you sure that it is the value 1 and not DEFAULT in the DEGREE column? --- Anyway, somehow the CBO is coming to the conclusion that the PQ-based full table scan is cheapest, which it would only do if it considered a full table scan to be attractive in the first place. A major reason that the CBO has historically considered full table scans more attractive than one would expect is the default setting of OPTIMIZER_INDEX_CACHING (i.e. 0), which indicates that logical reads equal physical reads. Since execution plans involving indexes frequently produce far more logical reads than full table scan, this assumption of logical reads being 1:1 with physical reads (i.e. the CBO largely considers a physical read as one unit of cost) can make indexed scans seem rather unattractive. What is your setting for OPTIMIZER_INDEX_CACHING? I usually recommend setting OPTIMIZER_INDEX_CACHING = 90, which indicates asks the CBO to discount 90% of logical reads of index blocks as able to be found in the Buffer Cache, thus reducing the final calculation of cost (i.e. physical reads) to 10% of the former. Now, we all know that the actual buffering effect is probably higher than 90%, but I've found truly disturbing behavior when you set the parameter to 99 or so, so I try to give that a wide berth. Plus, there are many situations where a full table scan is truly superior to an indexed scan, so I don't want to go overboard on this... If your setting is still at the default, try executing ALTER
RE: Why is Parallel Query Running after upgrading to 8.1.7.2
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Tim Gorman Sent: Saturday, June 08, 2002 4:53 PM To: Multiple recipients of list ORACLE-L Subject: Re: Why is Parallel Query Running after upgrading to 8.1.7.2 Here's my thinking -- it could be way off the mark... As observed in the output from the 10053 trace, the CBO always considers not only serial full table scans but also parallel full table scans. The big question is exactly what degree of parallelism is it using in its cogitations; I haven't had a chance to research that (at least I don't remember). Logically, with no PARALLEL hint in the SQL statement and with the table DEGREE set to 1, then the degree of parallelism considered by the CBO should be 1. But apparently not. I don't know, your original thoughts make sense. A quick 10053 trace indicates with a value of 1, the serial and parallel costs are the same. Here is the section of a 10053 trace showing the tablescan (tsc) costs for serial (Resc) and parallel (Resp) with a degree of 1 on the table: Access path: tsc Resc: 234 Resp: 234 snip BEST_CST: 234.00 PATH: 2 Degree: 1 Changed the degree to 4, and here is how the section changes, note the drop for Resp and the reported value for Degree: Access path: tsc Resc: 234 Resp: 59 snip BEST_CST: 234.00 PATH: 2 Degree: 4 Yeah I know that *you* know what those abbreviations mean, but I included the explanation for those not familiar with 10053 traces. So yeah, a 10053 shows the comparison between serial and parallel, but with a degree of 1, one would *think* they would always be the same. I, like you, wouldn't expect parallel to jump into the mix, or at least I've never run across such a situation (except as noted below). Are you sure that it is the value 1 and not DEFAULT in the DEGREE column? And along the default train of thought, and a wild stab in the dark. If someone or something had issued an alter session force parallel query, and the degree on the tables/indexes were 1, and, no hints specifying parallel were used, it would still use a default value which is based on the number of CPU's. For example, I just bounced my single CPU box here at home setting the CPU Count to 4. After issuing the alter session force parallel query and doing a select on a table with a degree of 1, the 10053 trace indicated a degree of 8: Access path: tsc Resc: 234 Resp: 30 snip BEST_CST: 234.00 PATH: 2 Degree: 8 Something like that *could* have happened. But it is a wild stab in the dark. Cherie, have you come up with anything from the Metalink research you were going to do? Larry G. Elkins [EMAIL PROTECTED] 214.954.1781 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Larry Elkins 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: Why is Parallel Query Running after upgrading to 8.1.7.2
PQ will not be used except degree was defined to a value 1 or set to default. Hints also can trigger that. I would simply get the execution plan for simple sql that access individual tables and the check the plan table if it's serial or using PQO . Waleed -Original Message- To: Multiple recipients of list ORACLE-L Sent: 6/8/02 6:38 PM Tim, Thanks for replying on a weekend. After doing some reading, I discovered that parallel_threads_per_cpu is set to the default of 2 on our database. With 8 CPUs, we are seeing 16 parallel threads on these queries, when they do go to parallel (a few of them do not). I experimented with setting parallel_threads_per_cpu to 1 and then we saw 8 parallel threads per statement. Then I set parallel_threads_per_cpu to 0 and now it is not going to parallel at all. That seems to be what our application owners want and we are testing with that right now. Seems to give us better timings. optimizer_index_caching is set to 0 currently. I don't believe that we have ever set it. We are just upgrading from 8.0.4 to 8.1.7.2 today so we are trying to minimize any parameter changes unless they are truly needed so that we can minimize unknown variables when something goes south. However, if we truly need to do this now that we are at 8.1.7.2, maybe we should bite the bullet and do it now. Isn't there another parameter that is used in association with optimizer_index_caching that should also be reset. I remember seeing it several times on the list but the parameter name escapes me now. Thanks again, Cherie Tim Gorman Tim@SageLogix To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] .comcc: Sent by: Subject: Re: Why is Parallel Query Running after upgrading to [EMAIL PROTECTED]8.1.7.2 om 06/08/02 04:53 PM Please respond to ORACLE-L Here's my thinking -- it could be way off the mark... As observed in the output from the 10053 trace, the CBO always considers not only serial full table scans but also parallel full table scans. The big question is exactly what degree of parallelism is it using in its cogitations; I haven't had a chance to research that (at least I don't remember). Logically, with no PARALLEL hint in the SQL statement and with the table DEGREE set to 1, then the degree of parallelism considered by the CBO should be 1. But apparently not. Are you sure that it is the value 1 and not DEFAULT in the DEGREE column? --- Anyway, somehow the CBO is coming to the conclusion that the PQ-based full table scan is cheapest, which it would only do if it considered a full table scan to be attractive in the first place. A major reason that the CBO has historically considered full table scans more attractive than one would expect is the default setting of OPTIMIZER_INDEX_CACHING (i.e. 0), which indicates that logical reads equal physical reads. Since execution plans involving indexes frequently produce far more logical reads than full table scan, this assumption of logical reads being 1:1 with physical reads (i.e. the CBO largely considers a physical read as one unit of cost) can make indexed scans seem rather unattractive. What is your setting for OPTIMIZER_INDEX_CACHING? I usually recommend setting OPTIMIZER_INDEX_CACHING = 90, which indicates asks the CBO to discount 90% of logical reads of index blocks as able to be found in the Buffer Cache, thus reducing the final calculation of cost (i.e. physical reads) to 10% of the former. Now, we all know that the actual buffering effect is probably higher than 90%, but I've found truly disturbing behavior when you set the parameter to 99 or so, so I try to give that a wide berth. Plus, there are many situations where a full table scan is truly superior to an indexed scan, so I don't want to go overboard on this... If your setting is still at the default, try executing ALTER SESSION SET OPTIMIZER_INDEX_CACHING = 90 before re-running the statement; see if it doesn't lose the PQ stuff... Hope this helps... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Saturday, June 08, 2002 1:53 PM I have just upgraded my 8.0.4 database to 8.1.7.2. Previously, parallel query would only run on objects that a DEGREE set 1. I just checked and all of my tables and indexes have DEGREE set to 1. So are most of my queries now running in parallel? I just checked my init.ora file and I haven't added any new parameters or changed any existing ones. I'm going to go research some more on Metalink and in the on-line manuals. However, if anyone could give me a quick run-down in what's changed in parallel query in 8.1.7.2, I'd appreciate it. We are making the initial
Wrong Results Bug in Oracle 8.1.7.1
SQL SELECT VOUCHER_ID FROM PS_VCHR_MM_VW WHERE ROWNUM 10;
VOUCHER_
3394
3395
3396
3397
3398
3399
3400
3401
3402
set feedback on
1 SELECT VOUCHER_ID FROM PS_VCHR_MM_VW
2* WHERE VOUCHER_ID = '3394'
SQL /
VOUCHER_
1 row selected.
--
Zounds !!! Select dump(voucher_id) shows that Oracle is returning a null here.
Here a function is used to force the query to do full tablescans
1 SELECT VOUCHER_ID FROM PS_VCHR_MM_VW
2* WHERE CONCAT(VOUCHER_ID,'A') = CONCAT('3394', 'A')
SQL /
VOUCHER_
3394
-
I get the expected results if I force full table scans.
I looked at the explain plan for the failing query and rebuilt the ps_voucher index.
This
did not change its erroneous results. I rebuilt the view itself again to no avail. A
check on Metalink revealed bug 1852163. Although this bug's conditions were different
from the one above, their were some similarities. One workaround for bug 1852163
alter session set _complex_view_merging = true;
I tried this and the original query still gave improper results.
---
All queries against the component tables of the view work fine.
--
The view text is
CREATE VIEW SYSADM.PS_VCHR_MM_VW
AS
SELECT DISTINCT A.BUSINESS_UNIT, A.VOUCHER_ID, A.INVOICE_ID,
A.INVOICE_DT, A.PROCESS_INSTANCE, A.ENTRY_STATUS, A.POST_STATUS_AP,
A.MATCH_ACTION, C.PPV_POST_FLG, C.ERV_POST_FLG, A.ORIGIN FROM
SYSADM.PS_VOUCHER A, SYSADM.PS_PO_LINE_MATCHED C WHERE A.BUSINESS_UNIT =
C.BUSINESS_UNIT_AP AND A.VOUCHER_ID = C.VOUCHER_ID AND
A.MATCH_ACTION IN ('Y', 'E')
-
If I run the select statement outside of the view and tack on the 'voucher_id = '
clause
SELECT DISTINCT A.BUSINESS_UNIT, A.VOUCHER_ID, A.INVOICE_ID,
A.INVOICE_DT, A.PROCESS_INSTANCE, A.ENTRY_STATUS, A.POST_STATUS_AP,
A.MATCH_ACTION, C.PPV_POST_FLG, C.ERV_POST_FLG, A.ORIGIN FROM
SYSADM.PS_VOUCHER A, SYSADM.PS_PO_LINE_MATCHED C WHERE A.BUSINESS_UNIT =
C.BUSINESS_UNIT_AP AND A.VOUCHER_ID = C.VOUCHER_ID AND
A.MATCH_ACTION IN ('Y', 'E')
and a.voucher_id = '3394'
/
I get the expected results. The query plan matches the one for the failing statement.
--
If I select more than voucher_id from the view with the 'voucher_id = ' predicate
the other fields are projected correctly, but returns voucher_id as null.
===
Ian MacGregor
Stanford Linear Accelerator Center
[EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: MacGregor, Ian A.
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).
A bit Off Topic - But need Help
Hello all, Sorry I am being a bit off topic, but I am helpless. What I need to know is the details (how, which file to add to, what is the name of the kernel file) of adding storage parameters in kernel. I need for 3 systems, that is Solaris 8 or any version, AIX 4.2.2 or above, and HP-uX 10 or above. I know eg that in case of Soalris, the file name is sdconf, but I have no idea what the contents of the file are, all I know since sdconf is "system disk configuration file", it would contain the names of the system disks, but if some one could send me the output of such a file that would be great. I do not have any of these 3 platforms now, (you may wonder why I need it then), but I need to know. Thanks and please help, who can. Thanks in advance. Rgds, Viraj
