Dynamic Sampling: some questions about the guts

Mailing List

Home

Forum Home

Oracle List - by freelists.org

Oracle on SUSE Linux - Runing Oracle on SUSE Linux

Oracle database error code ...

www.freelists.org

Subjects

•	ORA 12540: TNS:internal limit restriction exceeded
•	ORA 12838 please : Is possible to append two times to the same table befo
•	ORA 12838 please : Is possible to append two times to the same table before
•	ora 04031
•	ora 12500 on windows
•	ORA 32004: obsolete and/or deprecated parameter(s) specified
•	ORA 01925: maximum of 30 enabled roles exceeded
•	ORA 01925: maximum of 30 enabled roles exceeded
•	ora 12500 on windows
•	ORA 01650, one idea
•	ORA 01650
•	ORA 4030
•	ORA 12838 please : Is possible to append two times to thesametable before do
•	ORA 12838 please : Is possible to append two times to thesame table before d
•	ORA 01536
•	ORA 03113 end of file on communication channel
•	ORA 32004: obsolete and/or deprecated parameter(s) specified
•	ORA 00600:
•	ORA 00020: maximum number of processes (%s) exceeded
•	ORA 01925: maximum of 30 enabled roles exceeded
•	ORA 3113 while creating a cluster database 9201 RAC on Linux with OCFS
•	ora 12500 on windows
•	ora 12500 on windows
•	ora 12500 on windows
•	ORA 01650, one idea
•	ora 12500 on windows
•	ora 12500 on windows
•	ora 12500 on windows
•	ORA 2000 Error Using DBMS STATS GATHER SCHEMA STATS
•	ORA 01650, one idea
•	ORA 01650, one idea
•	ORA 01650, one idea
•	ORA 01650
•	ORA 01650
•	Subject: ora 01031
•	ORA 4030
•	ORA 4030
•	ORA 06502: PL/SQL: numeric or value error: Bulk Bind: Truncated Bind
•	Subject: Re: ORA 01722 invalid number

Dynamic Sampling: some questions about the guts

Dynamic Sampling: some questions about the guts

2007-12-01 - By Rajeev Prabhakar

Back

Reply: 1 2 3 4 5 6

Charles

1) Do you have histograms collected for relevant column(s) ?
2) What is the value of cursor_sharing parameter ?
3) Do you use dbms_stats ? If yes, what parameters/options
do you use ?

-Rajeev

On Nov 30, 2007 3:45 PM, Charles Schultz <sacrophyte@(protected)> wrote:

> In one of our reporting environments (OSEE 10.2.0.2) we have
> optimizer_dynamic_sampling cranked up to 5. We have a case where dynamic
> sampling is pushing the CBO to choose a poor plan. Our situation highlights
> Level 4, specifically "tables that have single-table predicates that
> reference 2 or more columns". (from the documentation<http://download.oracle
.com/docs/cd/B19306_01/server.102/b14211/stats.htm#sthref1491>
> )
>
> Looking at a 10046 trace, I see a OPT_DYN_SAMP query that counts a sample
> of rows that match variations on the predicate. I have found that a 10046
> can always be good grounds for finding more questions than it answers.
> *grin*
>
> To avoid the bandwidth, I will show just the relevant parts of the
> sampling query:
> SELECT /* OPT_DYN_SAMP */
> ...
> FROM
> (SELECT /* ... */
> 1 AS C1,
> CASE
> WHEN "RCRAPP1"."RCRAPP1_AIDY_CODE"=:bind1
> AND "RCRAPP1"."RCRAPP1_INFC_CODE"=:bind2
> AND "RCRAPP1"."RCRAPP1_CURR_REC_IND"=:bind3
> AND "RCRAPP1"."RCRAPP1_VPDI_CODE"=:bind4
> THEN 1
> ELSE 0
> END AS C2,
> CASE
> WHEN "RCRAPP1"."RCRAPP1_AIDY_CODE"=:bind1
> AND "RCRAPP1"."RCRAPP1_VPDI_CODE"=:bind4
> THEN 1
> ELSE 0
> END AS C3,
> CASE
> WHEN "RCRAPP1"."RCRAPP1_AIDY_CODE"=:bind1
> AND "RCRAPP1"."RCRAPP1_VPDI_CODE"=:bind4
> THEN 1
> ELSE 0
> END AS C4
> FROM ...
> SAMPLE BLOCK (.037704 , 1) SEED (1) "RCRAPP1"
> ) SAMPLESUB
> /
>
> First question: Why the predicate variations? I am assuming that sometimes
> C4 is different than C3, so I am going to ignore C4 for now.
> 2) How does the Optimizer use these sample counts to influence the costs
> and subsequent access/join methods?
>
> I believe what is happening in our case is that sampling is "just unlucky"
> and it happens to come up with a inaccurate picture of how many rows match
> (an order of magnitude too many). I can prove this by using this query
> directly with different sample sizes.
>
> I am hoping that by understanding dynamic sampling a little better, I can
> understand how to resolve this issue. I realize that one can add the
> dynamic_sampling query hint, and perhaps that is the "best" solution for
> this. Aside from adjusting dynamic sampling (whether it be at the system
> level or via a hint), is there any other way to address this situation?
>
> TIA,
>
> --
> Charles Schultz

<div>Charles</div>
<div> </div>
<div>1) Do you have histograms collected for relevant column(s) ?</div>
<div>2) What is the value of cursor_sharing parameter ?</div>
<div>3) Do you use dbms_stats ? If yes, what parameters/options </div>
<div>    do you use ?</div>
<div> </div>
<div>-Rajeev </div>
<div>  </div>
<div class="gmail_quote">On Nov 30, 2007 3:45 PM, Charles Schultz <<a href=
"mailto:sacrophyte@(protected)">sacrophyte@(protected)</a>> wrote: 
<blockquote class="gmail_quote" style="PADDING-LEFT: 1ex; MARGIN: 0px 0px 0px 0
.8ex; BORDER-LEFT: #ccc 1px solid">In one of our reporting environments (OSEE <a
href="http://10.2.0.2/" target="_blank">10.2.0.2</a>) we have optimizer_dynamic
_sampling cranked up to 5. We have a case where dynamic sampling is pushing the
CBO to choose a poor plan. Our situation highlights Level 4, specifically &quot
;tables that have single-table predicates that reference 2 or more columns"
. (from the
<a href="http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/stats
.htm#sthref1491" target="_blank">documentation</a>) Looking at a 10046
trace, I see a OPT_DYN_SAMP query that counts a sample of rows that match
variations on the predicate. I have found that a 10046 can always be good
grounds for finding more questions than it answers. *grin*
 To avoid the bandwidth, I will show just the relevant parts of the
sampling query: SELECT /* OPT_DYN_SAMP */ 
 .
.. 

FROM  
(SELECT /* ... */ 
 
  1 AS C1, 
   CASE     WHEN     "RCRAPP1".
"RCRAPP1_AIDY_CODE"=:bind1
 
 
        AND "RCRAPP1"."RCRAPP1
_INFC_CODE"=:bind2 
   
       AND "RCRAPP1"."RCRAPP1_CURR
_REC_IND"=:bind3 
   
       AND "RCRAPP1"."RCRAPP1_VPDI
_CODE"=:bind4 
 
   THEN 1 
    ELSE 0    END AS C2, 
 
  CASE 
    WHEN     "RCRAPP1".&quot
;RCRAPP1_AIDY_CODE"=:bind1          AND
"RCRAPP1"."RCRAPP1_VPDI_CODE"=:bind4
 
 
   THEN 1 
 
   ELSE 0 
   END AS C3,    CASE 
 
   WHEN     "RCRAPP1"."RCRAPP1_AIDY
_CODE"=:bind1 
         AND "RCRAPP1".&quot
;RCRAPP1_VPDI_CODE"=:bind4     THEN 1
 
 
   ELSE 0 
 
  END AS C4 
 FROM ...  SAMPLE BLOCK (.037704 , 1) SEED (1) "RCRAPP1"
 ) SAMPLESUB 
/ First question: Why the predicate variations? I am assuming that
sometimes C4 is different than C3, so I am going to ignore C4 for now.
 2) How does the Optimizer use these sample counts to influence the costs
and subsequent access/join methods? I believe what is happening in our
case is that sampling is "just unlucky" and it happens to come up
with a inaccurate picture of how many rows match (an order of magnitude too
many). I can prove this by using this query directly with different sample
sizes.
 I am hoping that by understanding dynamic sampling a little better, I
can understand how to resolve this issue. I realize that one can add the
dynamic_sampling query hint, and perhaps that is the "best" solution
for this. Aside from adjusting dynamic sampling (whether it be at the system
level or via a hint), is there any other way to address this situation?
 TIA, -- Charles Schultz <
/blockquote></div>