OraFAQ Forum: Performance Tuning

Home » RDBMS Server » Performance Tuning » Slow Sql query (Oracle 10.2.0.3)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Slow Sql query [message #495157]

Thu, 17 February 2011 14:52

Its_me_ved
Messages: 979
Registered: October 2009
Location: India

Senior Member

Hi,

I need your advise to tune sql attached herewith.

Instead of using NVL wrote:((column=param and param is not null) or param is null).
Is there any index or hint to be used for optimum access path?

SELECT STATEMENT, GOAL = CHOOSE   Cost=6244 Cardinality=1 Bytes=234 IO cost=6228
 SORT GROUP BY    Cardinality=1 Bytes=234 
  CONCATENATION      
   FILTER      
    TABLE ACCESS BY INDEX ROWID Object owner=VDX01 Object name=lic_policy_group Cost=0 Cardinality=1 Bytes=35 IO cost=0
     NESTED LOOPS   Cost=3122 Cardinality=1 Bytes=234 IO cost=3114
      NESTED LOOPS   Cost=3122 Cardinality=1 Bytes=199 IO cost=3114
       NESTED LOOPS   Cost=3122 Cardinality=1 Bytes=174 IO cost=3114
        NESTED LOOPS   Cost=3119 Cardinality=1 Bytes=139 IO cost=3111
         NESTED LOOPS   Cost=3117 Cardinality=1 Bytes=117 IO cost=3109
          TABLE ACCESS FULL Object owner=VDX01 Object name=lic_policiy Cost=3113 Cardinality=1 Bytes=68 IO cost=3105
          TABLE ACCESS BY INDEX ROWID Object owner=VDX01 Object name=lic_policy_pro Cost=4 Cardinality=1 Bytes=49 IO cost=4
           INDEX RANGE SCAN Object owner=VDX01 Object name=UK02_LPP Cost=3 Cardinality=1  IO cost=3
         TABLE ACCESS BY INDEX ROWID Object owner=VDX01 Object name=lic_policy_item Cost=2 Cardinality=1 Bytes=22 IO cost=2
          INDEX UNIQUE SCAN Object owner=VDX01 Object name=PK_LPI Cost=1 Cardinality=1  IO cost=1
        TABLE ACCESS BY INDEX ROWID Object owner=VDX01 Object name=lic_policy_group Cost=3 Cardinality=1 Bytes=35 IO cost=3
         INDEX RANGE SCAN Object owner=VDX01 Object name=UK02_LPG Cost=2 Cardinality=1  IO cost=2
       TABLE ACCESS BY INDEX ROWID Object owner=VDX01 Object name=lic_policy_group Cost=0 Cardinality=1 Bytes=25 IO cost=0
        INDEX RANGE SCAN Object owner=VDX01 Object name=UK02_LPG Cost=2 Cardinality=1  IO cost=2
      INDEX RANGE SCAN Object owner=VDX01 Object name=UK02_LPG Cost=2 Cardinality=1  IO cost=2
   FILTER      
    TABLE ACCESS BY INDEX ROWID Object owner=VDX01 Object name=lic_policy_group Cost=0 Cardinality=1 Bytes=35 IO cost=0
     NESTED LOOPS   Cost=3122 Cardinality=1 Bytes=234 IO cost=3114
      NESTED LOOPS   Cost=3122 Cardinality=1 Bytes=199 IO cost=3114
       NESTED LOOPS   Cost=3122 Cardinality=1 Bytes=164 IO cost=3114
        NESTED LOOPS   Cost=3119 Cardinality=1 Bytes=139 IO cost=3111
         NESTED LOOPS   Cost=3117 Cardinality=1 Bytes=117 IO cost=3109
          TABLE ACCESS FULL Object owner=VDX01 Object name=lic_policiy Cost=3113 Cardinality=1 Bytes=68 IO cost=3105
          TABLE ACCESS BY INDEX ROWID Object owner=VDX01 Object name=lic_policy_pro Cost=4 Cardinality=1 Bytes=49 IO cost=4
           INDEX RANGE SCAN Object owner=VDX01 Object name=UK02_LPP Cost=3 Cardinality=1  IO cost=3
         TABLE ACCESS BY INDEX ROWID Object owner=VDX01 Object name=lic_policy_item Cost=2 Cardinality=1 Bytes=22 IO cost=2
          INDEX UNIQUE SCAN Object owner=VDX01 Object name=PK_LPI Cost=1 Cardinality=1  IO cost=1
        TABLE ACCESS BY INDEX ROWID Object owner=VDX01 Object name=lic_policy_group Cost=3 Cardinality=1 Bytes=25 IO cost=3
         INDEX RANGE SCAN Object owner=VDX01 Object name=UK02_LPG Cost=2 Cardinality=1  IO cost=2
       TABLE ACCESS BY INDEX ROWID Object owner=VDX01 Object name=lic_policy_group Cost=0 Cardinality=1 Bytes=35 IO cost=0
        INDEX RANGE SCAN Object owner=VDX01 Object name=UK02_LPG Cost=2 Cardinality=1  IO cost=2
      INDEX RANGE SCAN Object owner=VDX01 Object name=UK02_LPG Cost=2 Cardinality=1  IO cost=2

Index list: Attached in the sql file
Count: Attached in the sql file

Additional info:
SYS_NC00029$ = nvl(lpp.lpp_val_cde, lpp.lpp_val_flag);

A few things were observed :
1. The optimizer mode is CHOOOSE (If I am not wrong for CBO it should be ALL_ROWS not CHOOSE- based on principle,
use RBO if statistics are not gathered)
2. The sample size for the tables are comparatively low while compared to num_rows
eg, for table lic_policy_pro the sample size is 56000
3. query takes long time to get output.The predicates may be different ( here it is replaced as null)

Where situation are like this where LIKE is used in so many predicates,I think oracle optimizer is still not smart
enought to estimate cost.How to make the optimizer to choose the optimum access path.

Thanks for your advise!

Regards
Ved

Attachment: sql18022011.sql
(Size: 5.22KB, Downloaded 1192 times)

Report message to a moderator

Re: Slow Sql query [message #495413 is a reply to message #495157]

Fri, 18 February 2011 18:27

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

You haven't prefixed the column names on these predicates:

WHERE prod_cde LIKE 'PAMH0D%'
AND policy_allocate_nbr LIKE nvl( null , policy_allocate_nbr)
AND nvl(policy_nbr, '0' ) LIKE nvl( null , nvl(policy_nbr, '0' ))
AND status LIKE nvl( null , status)
AND policy_start_dt >= nvl( null , policy_start_dt)
AND policy_start_dt <= nvl( null , policy_start_dt)

So it's hard to tell which tables they reference.

Between these and the predicates on lpg3 at the bottom of the query, which are the most selective predicates. i.e. which one(s) filter out the most rows. Whichever it is, that's the table you want as the driving table.

Also, I'm having trouble believing that this is the plan for the attached query. In the plan I see a CONCATENATION, which implies that there is an OR predicate somewhere in your SQL, but I don't see an OR predicate.

It would really help if you ran a SQL trace and posted the TKPROF output. Then we could see where the trouble is.

Ross Leishman

Report message to a moderator

Re: Slow Sql query [message #495434 is a reply to message #495413]

Sat, 19 February 2011 02:05

Its_me_ved
Messages: 979
Registered: October 2009
Location: India

Senior Member

Thanks you very much for the response.

WHERE prod_cde LIKE 'PAMH0D%'
AND policy_allocate_nbr LIKE nvl( null , policy_allocate_nbr)
AND nvl(policy_nbr, '0' ) LIKE nvl( null , nvl(policy_nbr, '0' ))
AND status LIKE nvl( null , status)
AND policy_start_dt >= nvl( null , policy_start_dt)
AND policy_start_dt <= nvl( null , policy_start_dt)

all the column in above statement reference lic_policy table

prod_cde LIKE 'PAMH0D% has 90% of rows in table.

AND lpg2.lpg_eff_end_dt IS NULL and lpp.lpp_eff_end_dt IS NULL picks almost 90% rows--but i cant skip this criteria.
lpp.lpp_eff_end_dt IS NULL --more than 90% of rows..

Quote:

In the plan I see a CONCATENATION, which implies that there is an OR predicate somewhere in your SQL, but I don't see an OR predicate.

may be for condition used like col like nvl(:a,'abc') ?

I tested in prod environment(where statistics were gathered properly) also but the there was no improvement.
Need some advise to look at the area where I need to look at for performance improvement.

Regards
Ved

Attachment: sql18022011-aliasadded.sql
(Size: 1.40KB, Downloaded 1207 times)

[Updated on: Sat, 19 February 2011 02:37]

Report message to a moderator

Re: Slow Sql query [message #495440 is a reply to message #495434]

Sat, 19 February 2011 05:38

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

If all of those conditions are non-selective, then you are returning a large proportion of lic_policy and its related rows. Right? If you are returning a small proportion, then there's something you're not telling me.

With such a large proportion of the table returned, those Nested Loops joins are the LAST thing you want. All of those filter criteria are tricking Oracle into thinking the statement is selective when it is not.

Try adding a gentle hint like:

SELECT COUNT (distinct(sq.policy_id))
FROM
(SELECT /*+ CARDINALITY(l 10000000)*/ *
FROM lic_policiy l, 
...

If that doesn't encourage some hash joins, use explicit ORDERED, USE_HASH, and FULL hints to avoid index usage.

Ross Leishman

Report message to a moderator

Re: Slow Sql query [message #495445 is a reply to message #495440]

Sat, 19 February 2011 07:45

Its_me_ved
Messages: 979
Registered: October 2009
Location: India

Senior Member

Thanks once again for the reply.

If I pass below condition:
case1:

prod_cde LIKE 'PAMH0D%'
status='109'
AND lpp.LPP_P_VAL = 'Deduction'
AND nvl(lpp.lpp_val_cde, lpp.lpp_val_flag) = 'Y'

it gives me a count of around 250

case2:

prod_cde LIKE 'PAMH0D%'
status='109'
AND lpp.LPP_P_VAL = 'Deduction'
AND nvl(lpp.lpp_val_cde, lpp.lpp_val_flag) = 'N'

it gives me a count of around 14500

Need some advise.

Actually the input are optional except the product.
Do I also give a thought to rebuild /create a new index as well?

Regards
Ved

[Updated on: Sat, 19 February 2011 07:46]

Report message to a moderator

Re: Slow Sql query [message #495471 is a reply to message #495445]

Sat, 19 February 2011 18:43

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

Then there is something you're not telling me. You have 100K rows in lic_policy. You tell me that each of the selection criteria is non-selective, but that under some values it filters out all but 250 rows (~0.25%) and under others it filters out all but 14500 rows (14.5%).

So what is filtering all those rows?

Maybe you should - just for an exercise - remove all of the other tables and just run some SELECTs over lic_policy to show exactly what is happening under various criteria.

If you are really serious about getting a result here, you should also:
- Post TKPROF output
- Run a proper DBMS_XPLAN Explain Plan that shows the Access and Filter predicates

Ross Leishman

Report message to a moderator

Re: Slow Sql query [message #495504 is a reply to message #495471]

Sun, 20 February 2011 01:59

Its_me_ved
Messages: 979
Registered: October 2009
Location: India

Senior Member

Thanks Ross for the advise. Currently I have no access to the system to provide trace file output.

AND lpp.LPP_P_VAL = 'Rule 214' basically this is the predicate that filters the rows and those that does not using NVL should always have some value.

When the predicate is changed to N the record count increases as
there are more records with value 'N'

AND lpp.LPP_P_VAL = 'Deduction'
AND nvl(lpp.lpp_val_cde, lpp.lpp_val_flag) = 'N'

The below criteria also filters the rows:

,lic_policy_group lpg3
WHERE lpg3.pg_policy_id = sq.policy_id
AND lpg3.lpg_stake_holder_id = 'XXXXX'
AND lpg3.lpg_grp_cd = 'CK001'

I do appreciate the time and effort you give to help me out on this.

Regards
Ved

[Updated on: Sun, 20 February 2011 01:59]

Report message to a moderator

Re: Slow Sql query [message #495515 is a reply to message #495504]

Sun, 20 February 2011 04:18

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

In that case, you need to drive from the table that does all of the filtering:

SELECT COUNT (distinct(sq.policy_id))
FROM
(SELECT /*+ LEADING(lpp)*/ *
FROM lic_policiy, 
lic_policy_item lpi, 
lic_policy_group lpg1, 
lic_policy_group lpg2,
lic_policy_pro lpp 
WHERE prod_cde LIKE 'PAMH0D%'
AND policy_allocate_nbr LIKE nvl( null , policy_allocate_nbr)
AND nvl(policy_nbr, '0' ) LIKE nvl( null , nvl(policy_nbr, '0' ))
AND status LIKE nvl( null , status)
AND policy_start_dt >= nvl( null , policy_start_dt)
AND policy_start_dt <= nvl( null , policy_start_dt)
AND lpi_policy_id = policy_id
AND lpi_policy_item_id = lp_map_policy_item_id
AND lpi_policy_end_dt IS NULL
AND lpg1.pg_policy_id = policy_id
AND lpg1.lpg_p_grp_cde = nvl( null , lpg1.lpg_p_grp_cde)
AND lpg1.lpg_stake_holder_id LIKE nvl(:B2, 'SH' )
AND lpg1.lpg_stake_holder_id = 'SH'
AND lpg1.lpg_grp_cd LIKE nvl( null , '%' )
AND lpg1.lpg_eff_end_dt IS NULL
AND lpg2.pg_policy_id = policy_id
AND lpg2.lpg_p_grp_cde = nvl( null , lpg1.lpg_p_grp_cde)
AND lpg2.lpg_stake_holder_id LIKE nvl( null , 'SH' )
AND lpg2.lpg_grp_cd LIKE nvl( null , '%' )
AND lpg2.lpg_eff_end_dt IS NULL
AND lpp_policy_id = policy_id
AND lpp.LPP_P_VAL = 'Rule 214'
AND nvl(lpp.lpp_val_cde, lpp.lpp_val_flag) = 'Y'
AND lpp.lpp_eff_end_dt IS NULL ) sq
,lic_policy_group lpg3
WHERE lpg3.pg_policy_id = sq.policy_id
AND lpg3.lpg_stake_holder_id = 'XXXXX'
AND lpg3.lpg_grp_cd = 'CK001'
AND lpg3.lpg_eff_end_dt IS NULL

If this doesn't change the join order, you will need to rearange the FROM clause and use an ORDERED hint.

It would help if LPP_P_VAL was indexed.

Ross Leishman

Report message to a moderator

Re: Slow Sql query [message #495516 is a reply to message #495515]

Sun, 20 February 2011 06:00

Its_me_ved
Messages: 979
Registered: October 2009
Location: India

Senior Member

Thank you!

I will take a look when I have access to the application.
The index has already been re created like below

(LPP_P_VAL, nvl(lpp.lpp_val_cde, lpp.lpp_val_flag))

Regards
Ved

Report message to a moderator

Previous Topic:	full table scan
Next Topic:	Identify blocking sessions

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 18 03:38:50 CDT 2024