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_at_SageLogix       To:     Multiple recipients of
list ORACLE-L <ORACLE-L_at_fatcity.com>     
                    .com>                cc:

                    Sent by:             Subject:     Re: Why is
Parallel Query Running after upgrading to       
                    root_at_fatcity.c        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" <ORACLE-L_at_fatcity.com>
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: Cherie_Machler_at_gelco.com
>
> 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: ListGuru_at_fatcity.com (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: Tim_at_SageLogix.com

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: ListGuru_at_fatcity.com (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: 
  INET: Cherie_Machler_at_gelco.com

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: ListGuru_at_fatcity.com (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: Khedr, Waleed
  INET: Waleed.Khedr_at_FMR.COM

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: ListGuru_at_fatcity.com (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).