Oracle Database Discussions

Hi.
We have an application layer with htmldb 2.0 running on 10.2 on HP-UX Itanium.
One of the applications runs a sql joining two tables and using bind variables as in the selection critieria.
The two tables has the following number of rows;
SQL> select count(1) from innsyn_kunde.kunde;
COUNT(1)
----------
96708
SQL> select count(1) from innsyn_kunde.regning;
COUNT(1)
----------
1867136
SQL>
The following intialization parameters are set on instances level:
SQL> show parameter optimiz
NAME TYPE VALUE
------------------------------------ ----------- -----------
optimizer_dynamic_sampling integer 2
optimizer_features_enable string 10.2.0.1
optimizer_index_caching integer 80
optimizer_index_cost_adj integer 10
optimizer_mode string FIRST_ROWS
optimizer_secure_view_merging boolean TRUE
plsql_optimize_level integer 2
SQL>
All tables and indexes used by the application has fresh statistics gathered with dbms_stats.
The following sql is defined in htmldb:
select
regn.regningnr
,regn.appl_kode
,regn.regningtekst1
,regn.regningdato
,regn.kundenr
,kund.kundenavn kunde
from innsyn_kunde.regning regn
, innsyn_kunde.kunde kund
where
regn.appl_kode=kund.appl_kode
and regn.kundenr=kund.kundenr(+)
and regn.appl_kode = :P31_appl_kode
and regn.kundenr like upper(decode(:P31_kundenr,null,'%',:P31_kundenr||'%'))
and regn.regningnr like decode(:P31_regningnr,null,'%',:P31_regningnr||'%')
and kund.kundenavn like upper(decode(:P31_kundenavn,null,'%',:P31_kundenavn||'%'))
order by regn.regningdato desc

1) If we substitute the bind variables with actual values in the actual sql in order to fetch one row the query runs very fast:
********************************************************************************

select
regn.regningnr
,regn.appl_kode
,regn.regningtekst1
,regn.regningdato
,regn.kundenr
,kund.kundenavn kunde
from innsyn_kunde.regning regn
, innsyn_kunde.kunde kund
where
regn.appl_kode=kund.appl_kode
and regn.kundenr=kund.kundenr(+)
and regn.appl_kode = 'SKOL'
and regn.kundenr like '%'
and regn.regningnr like '1150456%'
and kund.kundenavn like '%'
order by regn.regningdato desc

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 5 8 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 5 8 0 1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 95 (INNSYN_KUNDE)

Rows Row Source Operation
------- ---------------------------------------------------
1 SORT ORDER BY (cr=8 pr=5 pw=0 time=343 us)
1 NESTED LOOPS (cr=8 pr=5 pw=0 time=315 us)
1 TABLE ACCESS BY INDEX ROWID REGNING (cr=4 pr=1 pw=0 time=182 us)
1 INDEX RANGE SCAN REGNING_IDX_03 (cr=3 pr=1 pw=0 time=165 us)(object id 68870)
1 TABLE ACCESS BY INDEX ROWID KUNDE (cr=4 pr=4 pw=0 time=129 us)
1 INDEX UNIQUE SCAN PK_KUNDE (cr=3 pr=3 pw=0 time=99 us)(object id 68356)


Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
1 SORT (ORDER BY)
1 NESTED LOOPS
1 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF 'REGNING'
(TABLE)
1 INDEX MODE: ANALYZED (RANGE SCAN) OF 'REGNING_IDX_03'
(INDEX)
1 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF 'KUNDE'
(TABLE)
1 INDEX MODE: ANALYZED (UNIQUE SCAN) OF 'PK_KUNDE' (INDEX
(UNIQUE))

********************************************************************************


2) If we use bind variables to reproduce the problem in htmldb from sqlplus like this we get the following result;
********************************************************************************

select
regn.regningnr
,regn.appl_kode
,regn.regningtekst1
,regn.regningdato
,regn.kundenr
,kund.kundenavn kunde

from innsyn_kunde.regning regn
, innsyn_kunde.kunde kund

where
regn.appl_kode=kund.appl_kode
and regn.kundenr=kund.kundenr(+)
and regn.appl_kode = :P31_appl_kode
and regn.kundenr like upper(decode(:P31_kundenr,null,'%',:P31_kundenr||'%'))
and regn.regningnr like decode(:P31_regningnr,null,'%',:P31_regningnr||'%')
and kund.kundenavn like upper(decode(:P31_kundenavn,null,'%',:P31_kundenavn||'%'))
order by regn.regningdato desc

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 3 0.00 0.00 0 0 0 0
Execute 3 0.00 0.00 0 0 0 0
Fetch 2 30.19 32.64 516728 1406955 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 8 30.19 32.64 516728 1406955 0 1

Misses in library cache during parse: 0
Parsing user id: 64 (INNSYN_WEB) (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
1 SORT ORDER BY (cr=1406955 pr=516728 pw=75 time=32643407 us)
1 TABLE ACCESS BY INDEX ROWID REGNING (cr=1406955 pr=516728 pw=75 time=32643357 us)
1354432 NESTED LOOPS (cr=106950 pr=42778 pw=75 time=17612677 us)
50191 VIEW index$_join$_002 (cr=661 pr=736 pw=75 time=447634 us)
50191 HASH JOIN (cr=661 pr=736 pw=75 time=297306 us)
50191 INDEX RANGE SCAN PK_KUNDE (cr=258 pr=258 pw=0 time=163 us)(object id 68356)
96705 INDEX RANGE SCAN KUNDE_IDX01 (cr=403 pr=403 pw=0 time=97049 us)(object id 68688)
1304240 INDEX RANGE SCAN REGNING_IDX_01 (cr=106289 pr=42042 pw=0 time=3401535 us)(object id 68363)


Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
1 SORT (ORDER BY)
1 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF 'REGNING'
(TABLE)
1354432 NESTED LOOPS
50191 VIEW OF 'index$_join$_002' (VIEW)
50191 HASH JOIN
50191 INDEX MODE: ANALYZED (RANGE SCAN) OF 'PK_KUNDE'
(INDEX (UNIQUE))
96705 INDEX MODE: ANALYZED (RANGE SCAN) OF 'KUNDE_IDX01'
(INDEX)
1304240 INDEX MODE: ANALYZED (RANGE SCAN) OF 'REGNING_IDX_01'
(INDEX)


By using bind-variables it uses 30 seconds to fetch one row that is returned in less than a second with standard litherals

3) And by doing something we probably not should, at least on 10.2 is to hint with RULE based optimization.
Parsing user id: 95 (INNSYN_KUNDE)
********************************************************************************

select /*+ RULE */
regn.regningnr
,regn.appl_kode
,regn.regningtekst1
,regn.regningdato
,regn.kundenr
,kund.kundenavn kunde
from innsyn_kunde.regning regn,
innsyn_kunde.kunde kund
where
regn.appl_kode=kund.appl_kode
and regn.kundenr=kund.kundenr (+)
and regn.appl_kode = :P31_appl_kode
and regn.kundenr like upper(decode(:P31_kundenr,null,'%',:P31_kundenr||'%'))
and regn.regningnr like decode(:P31_regningnr,null,'%',:P31_regningnr||'%')
and kund.kundenavn like upper(decode(:P31_kundenavn,null,'%',:P31_kundenavn||'%'))
order by regn.regningdato desc

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.13 0.12 1109 1122 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.13 0.12 1109 1122 0 1

Misses in library cache during parse: 0
Optimizer mode: RULE
Parsing user id: 95 (INNSYN_KUNDE)

Rows Row Source Operation
------- ---------------------------------------------------
1 SORT ORDER BY (cr=1122 pr=1109 pw=0 time=123795 us)
1 FILTER (cr=1122 pr=1109 pw=0 time=123752 us)
1 MERGE JOIN OUTER (cr=1122 pr=1109 pw=0 time=123735 us)
1 SORT JOIN (cr=4 pr=0 pw=0 time=125 us)
1 TABLE ACCESS BY INDEX ROWID REGNING (cr=4 pr=0 pw=0 time=83 us)
1 INDEX RANGE SCAN REGNING_IDX_03 (cr=3 pr=0 pw=0 time=64 us)(object id 68870)
1 SORT JOIN (cr=1118 pr=1109 pw=0 time=123601 us)
96708 TABLE ACCESS FULL KUNDE (cr=1118 pr=1109 pw=0 time=199 us)


Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: HINT: RULE
1 SORT (ORDER BY)
1 FILTER
1 MERGE JOIN (OUTER)
1 SORT (JOIN)
1 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'REGNING' (TABLE)
1 INDEX MODE: ANALYZED (RANGE SCAN) OF
'REGNING_IDX_03' (INDEX)
1 SORT (JOIN)
96708 TABLE ACCESS MODE: ANALYZED (FULL) OF 'KUNDE' (TABLE)

By using Rule based it uses 0.1 second to fetch one row from the database.
Anyone seen this problems using Cost based with good statistics and bind variables giving such bad performance .
Any help would be apreciated.

rgds
Kjell