optimizer behavior
- From: walid alkaakati <walid_alkaakati@xxxxxxxxx>
- To: Oracle-L@xxxxxxxxxxxxx
- Date: Tue, 26 Oct 2010 13:11:00 +0100 (BST)
hi list,
Below is a query in which the optimizer is estimating 456 rows and hence
genrating a wrong access plan by which iam reading 66900 rows by the index and
the another 66900 rows for the table while i need only one row from table
XSVPATIENTRECORD,
SELECT /*+ gather_plan_statistics */ ....FROM XsvRecPerInfoV t0 WHERE
((((t0.AgnExtID = '1393621382') AND (t0.PrtID IN (72, 165))) AND (t0.RpxType IN
(1, 2, 6, 3, 5,6) and (t0.RstState IN (202, 208, 210, 203, 206)))
--------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name |
E-Rows | A-Rows | A-Time |
--------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | 1
| | 1 |00:00:00.36 |
| 1 | NESTED LOOPS
| 1 | 1 | 1 |00:00:00.36 |
| 2 | NESTED LOOPS
| 1 | 1 | 1 |00:00:15.38 |
|* 3 | HASH
JOIN | 1
| 1 | 1 |00:00:15.38 |
| 4 | TABLE ACCESS BY INDEX ROWID | PERPERSON | 1
| 1 |
|* 5 | INDEX RANGE SCAN | PERIDXAGNEXTID | 1
| 1 | 1 |00:00:00.01 |
|* 6 | TABLE ACCESS BY INDEX ROWID | XSVRECPERXREF | 1 | 2
| 23623 |00:00:26.65
| 7 | NESTED LOOPS | 1 |
431 | 33893 |00:00:15.56 |
|* 8 | HASH JOIN | 1
| 268 | 5139 |00:00:05.87 |
|* 9 | TABLE ACCESS FULL | XMFREPORTSTATE | 1
| 5 | 5 |00:00:00.01 |
| 10 | TABLE ACCESS BY INDEX ROWID | XSVPATIENTRECORD | 1 | 456 |
66900 00:00:13.31
| 11 | NESTED LOOPS | | 1
| 913 | 66903 |00:00:00.27
| 12 | INLIST ITERATOR | | 1
| | 2 |00:00:00.01 |
| 13 | TABLE ACCESS BY INDEX ROWID| XMFPATIENTRECORDTYPE | 2
| 2 |00:00:00.01 |
|* 14 | INDEX UNIQUE SCAN | PKPRT 2 | 2
|00:00:00.01 | 2 | 1 |
|* 15 | INDEX RANGE SCAN | RECIDXPRTID 456 | 66900
|00:00:00.07 |
|* 16 | INDEX RANGE SCAN | RPXIDXRECID | 5139
| 2 | 28753 |00:00:00.76 |
| 17 | TABLE ACCESS BY INDEX ROWID | ADTCASE 1 | 1
|00:00:00.01 |
|* 18 | INDEX UNIQUE SCAN | PK_ADTCASE 1
| 1 |00:00:00.01 |
| 19 | TABLE ACCESS BY INDEX ROWID | ADTPATIENT 1 | 1
| 1 |00:00:00.01 |
|* 20 | INDEX UNIQUE SCAN | PK_ADTPATIENT 1 |
1 |00:00:00.01 |
-------------------------------------------------------------------------------------------------------------------------------------
3 - access("PER"."PERID"="X"."PERID")
5 - access("PER"."AGNEXTID"='1393621382')
6 - filter(("X"."RPXTYPE"=1 OR "X"."RPXTYPE"=2 OR "X"."RPXTYPE"=3 OR
"X"."RPXTYPE"=5 OR "X"."RPXTYPE"=6))
8 - access("D"."RSTID"="R"."RSTID")
9 - filter(("R"."RSTSTATE"=202 OR "R"."RSTSTATE"=203 OR "R"."RSTSTATE"=206
OR "R"."RSTSTATE"=208 OR "R"."RSTSTATE"=210))
14 - access(("PRT"."PRTID"=72 OR "PRT"."PRTID"=165))
15 - access("D"."PRTID"="PRT"."PRTID")
filter(("D"."PRTID"=72 OR "D"."PRTID"=165))
16 - access("X"."RECID"="D"."RECID")
18 - access("D"."CASID"="C"."CASID")
20 - access("C"."PATID"="P"."PATID")
and the view XsvRecPerInfoV is :
select ... FROM PerPerson per,XsvRecPerXRef x ,XsvPatientRecord
d,XmfReportState r,XmfPatientRecordType prt , AdtCase c, AdtPatient p
WHERE per.PerID= x.PerID AND x.RecID = d.RecID aND d.RstID = r.RstID
AND d.PrtID = prt.PrtID AND d.CasID = c.CasID AND c.PatID = p.PatID
i want the tables to be accessed as in the view but i do not want to use a
hint /*ordered*/ also i have histograms on column PrtID and all statistics are
updated,and this
happens for specific values of prtid column.
oracle 10.2.0.5.0 on windows 2008 64,XSVPATIENTRECORD is 9 million rows.
This is a bug or the histogams are not accurate ?
best wishes
Other related posts:
- » optimizer behavior - walid alkaakati
