Home » RDBMS Server » Performance Tuning » Select statement is causing performance issue
Select statement is causing performance issue [message #379856] Thu, 08 January 2009 02:53 Go to next message
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
Re: Select statement is causing performance issue [message #379858 is a reply to message #379856] Thu, 08 January 2009 03:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Select statement is causing performance issue [message #379859 is a reply to message #379858] Thu, 08 January 2009 03:06 Go to previous messageGo to next message
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
Re: Select statement is causing performance issue [message #379861 is a reply to message #379859] Thu, 08 January 2009 03:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First, you have to tell us what is your REAL query.

Regards
Michel
Re: Select statement is causing performance issue [message #379863 is a reply to message #379861] Thu, 08 January 2009 03:16 Go to previous messageGo to next message
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
Re: Select statement is causing performance issue [message #379869 is a reply to message #379863] Thu, 08 January 2009 03:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Select statement is causing performance issue [message #379876 is a reply to message #379869] Thu, 08 January 2009 03:59 Go to previous messageGo to next message
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.
Re: Select statement is causing performance issue [message #379882 is a reply to message #379876] Thu, 08 January 2009 04:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
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
Re: Select statement is causing performance issue [message #379912 is a reply to message #379909] Thu, 08 January 2009 06:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Select statement is causing performance issue [message #379926 is a reply to message #379856] Thu, 08 January 2009 08:09 Go to previous messageGo to next message
cookiemonster
Messages: 12422
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.
Re: Select statement is causing performance issue [message #380011 is a reply to message #379926] Thu, 08 January 2009 19:53 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Try adding an /*+INDEX(Emp)*/ hint.

Ross Leishman
Re: Select statement is causing performance issue [message #380019 is a reply to message #379856] Thu, 08 January 2009 20:43 Go to previous messageGo to next message
BlackSwan
Messages: 25046
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.
Re: Select statement is causing performance issue [message #380020 is a reply to message #379926] Thu, 08 January 2009 20:43 Go to previous messageGo to next message
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
Re: Select statement is causing performance issue [message #380041 is a reply to message #380019] Thu, 08 January 2009 21:33 Go to previous messageGo to next message
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
Re: Select statement is causing performance issue [message #380048 is a reply to message #379856] Thu, 08 January 2009 22:34 Go to previous messageGo to next message
BlackSwan
Messages: 25046
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.
Re: Select statement is causing performance issue [message #380104 is a reply to message #380020] Fri, 09 January 2009 03:10 Go to previous message
cookiemonster
Messages: 12422
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.
Previous Topic: cpu usage during backup
Next Topic: tuning sql query
Goto Forum:
  


Current Time: Fri Dec 09 07:46:27 CST 2016

Total time taken to generate the page: 0.13492 seconds