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 David Aldridge

Back

Reply: 1 2 3 4 5 6

I wouold guess that this might be to do with the optimizer looking at different
access paths to the table. For example, it might be not be much more selective
to access the table via

"RCRAPP1"."RCRAPP1_AIDY_CODE"=:bind1
AND "RCRAPP1"."RCRAPP1_INFC_CODE"=:bind2
AND "RCRAPP1"."RCRAPP1_CURR_REC_IND"=:bind3
AND "RCRAPP1"."RCRAPP1_VPDI_CODE"=:bind4

than it is to access it with:

"RCRAPP1"."RCRAPP1_AIDY_CODE"=:bind1
AND "RCRAPP1"."RCRAPP1_VPDI_CODE"=:bind4

If there is one index on all four columns and one index on just the two columns
then accessing via the two column index might be more efficient than using the
four-column one.

Just speculation though.

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)

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>I wouold guess that this might be to do with the optimizer looking at
different access paths to the table. For example, it might be not be much more
selective to access the table via </div> <div> </div> <div>"RCRAPP1"."RCRAPP1_AIDY_CODE"=:bind1  AND "RCRAPP1".
"RCRAPP1_INFC_CODE"=:bind2  AND "RCRAPP1"."RCRAPP1_CURR_REC_IND"=:bind3
  AND "RCRAPP1"."RCRAPP1_VPDI_CODE"=:bind4 </div>
<div>than it is to access it with:</div> <div> </div> <div>"RCRAPP1"."RCRAPP1_AIDY_CODE"=:bind1 AND "RCRAPP1"."RCRAPP1_VPDI
_CODE"=:bind4 If there is one index on all four columns and one
index on just the two columns then accessing via the two column index might be
more efficient than using the four-column one.</div> <div> </div> <div
>Just speculation though. </div> <BLOCKQUOTE class=replbq
style="PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #1010ff 2px solid">In
one of our reporting environments (OSEE <A href="http://10.2.0.2/">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 "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">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"."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 </BLOCKQUOTE>