OraFAQ Forum: Performance Tuning » Select statement is causing performance issue

Home » RDBMS Server » Performance Tuning » Select statement is causing performance issue

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

Select statement is causing performance issue [message #379856]

Thu, 08 January 2009 02:53

ind9
Messages: 65
Registered: January 2009

Member

Hi,

I am facing a performance issue and the details are as given below.

CREATE INDEX Scott.IDX_Last ON Scott.Emp
(SUBSTR(Scott.Pkg.Fn_Match(Last_Name,40),1,40));
/

CREATE INDEX Scott.IDX_First ON Scott.Emp
(SUBSTR(Scott.Pkg.Fn_Match(First_Name,40),1,40))
/

I have a function based index for Last_Name and First_Name. Like shown above.

 Select *
  From Emp
   WHere (SUBSTR (Pkg.Fn_Match (Last_Name, 40), 1, 40) Like 'In%'
                  OR 
          SUBSTR(Pkg.Fn_Match (First_Name,40), 1, 40) Like 'In%')
/

no rows selected

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=124)
   1    0   TABLE ACCESS (FULL) OF 'EMP' (Cost=3 Card=1
           Bytes=124)

This query is been using in the production environment. It's causing performance problems.

When i run this query it is going for full table scan and it is taking more time to execute.
Please tell me why it is not using index.
What should i do to make this query to use index? Its very urgent.

Thanks

Report message to a moderator

Re: Select statement is causing performance issue [message #379858 is a reply to message #379856]

Thu, 08 January 2009 03:00

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

First:
SUBSTR (Pkg.Fn_Match (Last_Name, 40), 1, 40) Like 'In%'
is equivalent to
Pkg.Fn_Match (Last_Name, 40) Like 'In%'
You don't need to substr.

Then did you gather statistics?
Anyway this kind of queries is always slow as package must be called twice per row.

Regards
Michel

Report message to a moderator

Re: Select statement is causing performance issue [message #379859 is a reply to message #379858]

Thu, 08 January 2009 03:06

ind9
Messages: 65
Registered: January 2009

Member

Hi Michel,

Thanks for your response.

SUBSTR (Pkg.Fn_Match (Last_Name, 40), 1, 40) Like '&Last_Nm'

Input of the user might exceed 40 characters also that is why we used substr

And we have gather statistics. Is there any way can i make the above query to use index instead of going for full table scan.

Thanks

Report message to a moderator

Re: Select statement is causing performance issue [message #379861 is a reply to message #379859]

Thu, 08 January 2009 03:12

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

First, you have to tell us what is your REAL query.

Regards
Michel

Report message to a moderator

Re: Select statement is causing performance issue [message #379863 is a reply to message #379861]

Thu, 08 January 2009 03:16

ind9
Messages: 65
Registered: January 2009

Member

Hi,

Query is this one.

Select *
  From Emp
   WHere (SUBSTR (Pkg.Fn_Match (Last_Name, 40), 1, 40) Like '&Last_Nm'
                  OR 
          SUBSTR(Pkg.Fn_Match (First_Name,40), 1, 40) Like '&Last_Nm')
/

Thanks

Report message to a moderator

Re: Select statement is causing performance issue [message #379869 is a reply to message #379863]

Thu, 08 January 2009 03:43

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

1/ You should bind variable
2/ Oracle receives the query after substitution of variable by its value and plan depends on this value

Regards
Michel

Report message to a moderator

Re: Select statement is causing performance issue [message #379876 is a reply to message #379869]

Thu, 08 January 2009 03:59

ind9
Messages: 65
Registered: January 2009

Member

Sorry i am not getting any directions to solve this issue.

My concern is the above specified query has to use function based index which is created.

It is not using index and its going for full table scan.

I hope i am clear with the problem.

Thanks.

Report message to a moderator

Re: Select statement is causing performance issue [message #379882 is a reply to message #379876]

Thu, 08 January 2009 04:23

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

You are clear with the problem.
You don't understand what I mean.
Plan depends on value.
Each time you change the value plan may change.
There is no reason Oracle will choose an index if it thinks it is faster with a full scan.
Why doyou think it should use one (or both) of the indexes?

Regards
Michel

[Updated on: Thu, 08 January 2009 04:24]

Report message to a moderator

Re: Select statement is causing performance issue [message #379909 is a reply to message #379882]

Thu, 08 January 2009 06:10

ind9
Messages: 65
Registered: January 2009

Member

Hi,

Is there any way can i re-write this query.

 Select *
  From Emp
   WHere (SUBSTR (Pkg.Fn_Match (Last_Name, 40), 1, 40) Like '&input'
                  OR 
          SUBSTR(Pkg.Fn_Match (First_Name,40), 1, 40) Like '&input')
/

no rows selected

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=124)
   1    0   TABLE ACCESS (FULL) OF 'EMP' (Cost=3 Card=1
           Bytes=124)

This query is been using in the production environment and if we execute this it is taking more than half hour.

The requirement is if we give the input value as say 'PO%' then
the above query has to retrieve all the last name starting with 'PO' and first_name starting with 'PO'.

Is there any way where i can rewrite this query. Kindly help

Thanks

Report message to a moderator

Re: Select statement is causing performance issue [message #379912 is a reply to message #379909]

Thu, 08 January 2009 06:13

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

the last name starting with 'PO' and first_name starting with 'PO'

not and, OR

No, there is no other way to say that.

Regards
Michel

Report message to a moderator

Re: Select statement is causing performance issue [message #379926 is a reply to message #379856]

Thu, 08 January 2009 08:09

cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester

Senior Member

Is that execution plan from your production environment?
Because it suggests that query should be as fast as lightning.

Report message to a moderator

Re: Select statement is causing performance issue [message #380011 is a reply to message #379926]

Thu, 08 January 2009 19:53

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

Senior Member

Try adding an /*+INDEX(Emp)*/ hint.

Ross Leishman

Report message to a moderator

Re: Select statement is causing performance issue [message #380019 is a reply to message #379856]

Thu, 08 January 2009 20:43

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Input of the user might exceed 40 characters also that is why we used substr
HUH?????
If the two leftmost character match, it does not matter how many characters (0-32766) come to the right of the 1st 2 characters.
I fail to understand how the results would change by eliminating the SUBSTR functions from the WHERE clause.

The (ab)use of a function [like SUBSRT, LTRIM, etc.] precludes the use of any index which may exist on the column operated on by the function.

Report message to a moderator

Re: Select statement is causing performance issue [message #380020 is a reply to message #379926]

Thu, 08 January 2009 20:43

ind9
Messages: 65
Registered: January 2009

Member

Unfortunately we wont get execution plans from production environment.

This execution plan is related to development environment. But query is same.

Thanks

Report message to a moderator

Re: Select statement is causing performance issue [message #380041 is a reply to message #380019]

Thu, 08 January 2009 21:33

ind9
Messages: 65
Registered: January 2009

Member

CREATE INDEX Scott.IDX_Last ON Scott.Emp
(SUBSTR(Scott.Pkg.Fn_Match(Last_Name,40),1,40));

CREATE INDEX Scott.IDX_First ON Scott.Emp
(SUBSTR(Scott.Pkg.Fn_Match(First_Name,40),1,40))
/

The reason is indexes were created by using substr. Thats why even in the queries we are using substr

Report message to a moderator

Re: Select statement is causing performance issue [message #380048 is a reply to message #379856]

Thu, 08 January 2009 22:34

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>The reason is indexes were created by using substr. Thats why even in the queries we are using substr

I suggest, just as a test, eliminate ALL references to SUBSTR to see if Oracle will then use indexes & provide faster response time.

If so, then you get to decide how important the use of SUBSTR is.

Report message to a moderator

Re: Select statement is causing performance issue [message #380104 is a reply to message #380020]

Fri, 09 January 2009 03:10

cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester

Senior Member

ind9 wrote on Fri, 09 January 2009 02:43

Unfortunately we wont get execution plans from production environment.

This execution plan is related to development environment. But query is same.

Thanks

That's what I thought.
How many rows have you got in emp on your test machine and how many have you got in prod?
I'm guessing there is a very large difference between the two counts.
It helps, when doing performance tuning, to have a dev/test machine with similar data volumes to production.
I very much doubt there is anything you can do to make oracle use the indexes in your dev environment because you don't have enough data to make indexes worth it's while.

Report message to a moderator

Previous Topic:	cpu usage during backup
Next Topic:	tuning sql query

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Nov 26 01:26:58 CST 2024