| performance problems when using ...like string||'%'; [message #360270] |
Thu, 20 November 2008 04:28  |
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 #360457 is a reply to message #360409] |
Fri, 21 November 2008 01:34  |
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 #360615 is a reply to message #360517] |
Fri, 21 November 2008 11:09 |
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
