Home » RDBMS Server » Performance Tuning » performance problems when using ...like string||'%'; (oracle 11g)
performance problems when using ...like string||'%'; [message #360270] Thu, 20 November 2008 04:28 Go to next message
jsulc
Messages: 19
Registered: October 2005
Junior Member
Hello,
I have two tables: "storage_medium" and "port"

SQL> select count(*) from storage_medium; 
  COUNT(*)
----------
  13892957
 
Executed in 5,813 seconds
 

SQL> select count(*) from port; 
  COUNT(*)
----------
  13876120
 
Executed in 3,75 seconds



Running simple select statement is OK:

SQL> select p.hlcode
  2    from storage_medium sm
  3        ,port p
  4   where sm.sm_id = p.sm_id
  5     and sm.sm_serialnum like '894202032250045747'||'%';
 
                                 HLCODE
---------------------------------------
                                     90
 
Executed in 0,031 seconds


But using the select in pl/sql block will result in catastrophical performance, because of "like" condition. (Even if it is implemented the "same" way as above.)

SQL> declare
  2    sim varchar2(50) := '894202032250045747';
  3    hlcode integer;
  4  begin
  5              select p.hlcode
  6                     into hlcode
  7                from storage_medium sm
  8                    ,port p
  9               where sm.sm_id = p.sm_id
 10                 and sm.sm_serialnum like sim||'%';
 11  
 12  dbms_output.put_line(hlcode);
 13  end;
 14  /
 
PL/SQL procedure successfully completed
 
Executed in 102,844 seconds



When I put the select into pl/sql block, performance is dramatically worse - 102 seconds!!!
Can you explain WHY?
How should I write the pl/sql block to achieve the same speed like "pure select statement"?
Thanks for any comments or help.
Jan
PS: unfortunately, I don't have access to selects like
select * from v$sql_plan
select * from v_$sql_plan
So i can't check what really happens.
Or is there any other place apart from v$sql_plan, where i could find the used explain plan?
Thanks.



[Updated on: Thu, 20 November 2008 05:03]

Report message to a moderator

Re: performance problems when using ...like string||'%'; [message #360409 is a reply to message #360270] Thu, 20 November 2008 21:38 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Last I recall, PL/SQL enforces its own default optimizer settings on all sql it executes and this is usually ALL ROWS. This basically means that oracle can run a sql statement from sqlplus with one execution plan, but when that sql statement makes its way inside a plsql block, it might be optimized to a different execution plan because pl/sql is not using the optimizer goal set for your session.

This may no longer be true (heck I may be remembering it wrong altogether) but I believe it is still true. Maybe someone else can comment with more info.

I suggest you put a first_rows hint in your sql code and see if that works.

Good luck, Kevin
Re: performance problems when using ...like string||'%'; [message #360457 is a reply to message #360409] Fri, 21 November 2008 01:34 Go to previous messageGo to next message
jsulc
Messages: 19
Registered: October 2005
Junior Member
Thanks very much, you are RIGHT!
With /*+ FIRST_ROWS */ hint it runs as quickly as in the simple select statement.

Just one more remark.
There is also another way to make the pl/sql block running fast.
If I remove the "like sim||'%';" part, and if I make this "concatenation" BEFORE the select, it goes quick as well:

 declare
    sim varchar2(50) := '894202032250045747';
    sim2 varchar2(50);
    hlcode integer;
  begin
  sim2 := sim||'%';
  
              select p.hlcode
                     into hlcode
                from storage_medium sm
                    ,port p
               where sm.sm_id = p.sm_id
                 and sm.sm_serialnum like sim2;
  
  dbms_output.put_line(hlcode);
 end;



I understand, why your hint works, it sounds logical. But why my "solution" works, is mysterious for me.
What has concatenating strings to do with execution plan?

Anyway thanks a lot.
jan


Re: performance problems when using ...like string||'%'; [message #360505 is a reply to message #360457] Fri, 21 November 2008 03:49 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I suspect that the concatenation works beause you've now got the results cached from the previous run.
Try running your new version again, followed by the original version.

Re: performance problems when using ...like string||'%'; [message #360508 is a reply to message #360505] Fri, 21 November 2008 04:01 Go to previous messageGo to next message
jsulc
Messages: 19
Registered: October 2005
Junior Member
No no, this is not the case, I tried it.

Somehow, the concatenation inside pl/sql block is processed DIFFERENTLY than when it is in a simple select.
This was what made me so surprised and why I posted this topic...

Jan
Re: performance problems when using ...like string||'%'; [message #360517 is a reply to message #360508] Fri, 21 November 2008 04:33 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Trace the sessions and post Tkpros showing the explain plans.
Or, get the plans back from v$sql_plan and post them here.
Re: performance problems when using ...like string||'%'; [message #360615 is a reply to message #360517] Fri, 21 November 2008 11:09 Go to previous message
jsulc
Messages: 19
Registered: October 2005
Junior Member
Unfortunately I can't do any of what you said.
I work for external company with very limited access rights.

1. I can see sessions id, sql hash value etc., but I can't select from v$sql_plan.

2. I tried do autotrace, but no success:
SQL> SET AUTOTRACE ON
Cannot SET AUTOTRACE

I suppose problems of access rights?

3. I can do "alter session set sql_trace=true;", but I can't get to the server to fetch the tracefile...

All in all, quite sad situation:-)
Well, thank you anyway.
Jan
Previous Topic: The difference between sql_text in v$sqlarea and v$sqltext
Next Topic: How to make use of indexes?
Goto Forum:
  


Current Time: Mon Dec 05 12:53:45 CST 2016

Total time taken to generate the page: 0.08345 seconds