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).
