Home » RDBMS Server » Performance Tuning » Parse once - Execute many (Oracle 9,10, cross platform)
Parse once - Execute many [message #487409] |
Sun, 26 December 2010 21:04 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
trantuananh24hg
Messages: 744 Registered: January 2007 Location: Ha Noi, Viet Nam
|
Senior Member |
|
|
Hi all!
I'm not developer, so, I'm not well in coding. OK, I must post here to ask to you for your idea.
The parse call devices to hard/soft/execute, yeap, the best appoarch in performance tuning is Parse once/Execute many. But, how? Bind variable? Yes, of course. However, in the packg, the appoarch is decrease sub-string, do that to avoid latch in shared pool. Mmm, I do not know much to do it
Please see:
1- Capture performance:
![http://i95.photobucket.com/albums/l130/trantuananh24hg/Oravn/parse_3.jpg](http://i95.photobucket.com/albums/l130/trantuananh24hg/Oravn/parse_3.jpg)
2- Detect SQL
![http://i95.photobucket.com/albums/l130/trantuananh24hg/Oravn/parse_1.jpg](http://i95.photobucket.com/albums/l130/trantuananh24hg/Oravn/parse_1.jpg)
3- How are SGA, PGA, cursor value?
![http://i95.photobucket.com/albums/l130/trantuananh24hg/Oravn/parse_2.jpg](http://i95.photobucket.com/albums/l130/trantuananh24hg/Oravn/parse_2.jpg)
4- The code in pckg
function layth_thuebao(psso_tb varchar2, pskieuso_id varchar2, psschema varchar2, pspageNum varchar2, psPageRec varchar2, psuserip varchar2)
return sys_refcursor
is
ref_ sys_refcursor;
s varchar2(1000);
c_ number;
pages number;
tb_ varchar2(100):=psso_tb;
begin
dms_admin.admin_log_access('layth_thuebao','{so_tb|'||psso_tb||'}{kieuso_id|'||pskieuso_id||'}{schema|'||psschema||'}{page_num|'||pspageNum||'}{page_rec|'||psPageRec||'}',psuserip);
if substr(psso_tb,1,1)='0' then
tb_:='84'||substr(psso_tb,2);
end if;
s:='select count(1) from '||psschema||'khosos a, '||psschema||'kieusos b where a.status in (0,1) and a.kieuso_id=b.kieuso_id and a.kieuso_id is not null';
if pskieuso_id is not null then
s:=s||' and a.kieuso_id in ('||pskieuso_id||')';
end if;
if psso_tb is not null then
s:=s||' and a.so_tb like '''||replace(tb_,'*','%')||'''';
end if;
--s:=s||' order by id desc';
DBMS_OUTPUT.Put_Line( s );
EXECUTE IMMEDIATE s into c_;
pages:=(case when trunc(c_/psPageRec)<c_/psPageRec then trunc(c_/psPageRec)+2 else trunc(c_/psPageRec)+1 end);
if c_ < pspagerec then
open ref_ for
'select :c_ page,''0'' page_num,''0'' page_rec from dual' using c_;
return ref_;
end if;
if pages<=10 then
open ref_ for
'select :c_ page,''0'' page_num,''0'' page_rec,'''' bg_color from dual union all
select rownum,:1,:2,decode(rownum,:pagenum,''style=''''background-color:#ffffff'''''','''') bg_color from '||psschema||'khosos where rownum<='||pages
using c_, pspageNum, psPageRec, pspageNum;
else
open ref_ for
'select :c_ page,''0'' page_num,''0'' page_rec,'''' bg_color from dual union all
select a, :pspageNum, :psPageRec, decode(a,:pagenum,''style=''''background-color:#ffffff'''''','''') bg_color from (
select rownum a from '||psschema||'khosos where rownum<='||pages||'
) where a>=:1 and rownum<=10'
using c_, pspageNum, psPageRec, pspageNum, (case when pspageNum+10>pages then pages-10 else pspageNum-1 end);
end if;
return ref_;
exception
when others then
DECLARE
err_ varchar2(10000);
begin
err_:='Loi thuc hien: '||to_char(sqlcode);
insert into numstore.log_sql_err (content,ngayth) values ('layth_thuebao.'||err_||'|'||psschema||'|'||psuserip,sysdate);
commit;
open ref_ for 'select :1 ten_kieuso from dual' using err_;
return ref_;
end;
end;
This is the function, so, I think about ref_ variable which contains many substr, am I wrong?
Thank you for your reply, any idea!
|
|
|
|
Re: Parse once - Execute many [message #487427 is a reply to message #487421] |
Mon, 27 December 2010 01:00 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
trantuananh24hg
Messages: 744 Registered: January 2007 Location: Ha Noi, Viet Nam
|
Senior Member |
|
|
I'm sorry, I posted wrong, this is the function:
function layds_thuebao(psso_tb varchar2, pskieuso_id varchar2, psschema varchar2, pspageNum varchar2, psPageRec varchar2, psuserip varchar2)
return sys_refcursor
is
ref_ sys_refcursor;
s varchar2(10000);
dk_ varchar2(100);
tb_ varchar2(100):=psso_tb;
begin
dms_admin.admin_log_access('layds_thuebao','{so_tb|'||psso_tb||'}{kieuso_id|'||pskieuso_id||'}{schema|'||psschema||'}{page_num|'||pspageNum||'}{page_rec|'||psPageRec||'}',psuserip);
if substr(psso_tb,1,1)='0' then
tb_:='84'||substr(psso_tb,2);
end if;
select REGEXP_REPLACE(psso_tb,'[0-9*]+') into dk_ from dual;
if dk_ is not null then
open ref_ for 'select :1 ten_kieuso from dual' using 'Chi nhap chu so va * trong o Tim kiem';
return ref_;
end if;
/*
s:='select rownum r_id, ''0''||substr(a.so_tb,3) so_tb,replace(ten_kieuso,''<br>'','''') ten_kieuso,
decode(a.status,0,''Trong kho'',1,''Đã ĐK'',2,''Đợi thu hồi'') status,
to_char(b.cuocthang_toithieu,'''|| store.fomart_number ||''')||''VNĐ'' cuocthang_toithieu,
to_char(b.tra_truoc/thoigian_sudung,'''|| store.fomart_number ||''')||''VNĐ/tháng'' datcoc,
b.thoigian_sudung||'' Tháng'' thoigian_sudung
from '||psschema||'khosos a, '||psschema||'kieusos b where a.status in (0,1) and a.kieuso_id=b.kieuso_id and a.kieuso_id is not null';
*/
s:='select rownum r_id, ''0''||substr(a.so_tb,3) so_tb,replace(ten_kieuso,''<br>'','''') ten_kieuso,
decode(a.status,0,''Trong kho'',1,''Đã ĐK'',2,''Đợi thu hồi'') status,
to_char(b.cuocthang_toithieu,'''|| store.fomart_number ||''')||'' VNĐ'' cuocthang_toithieu,
decode(thoigian_sudung,''0'',''0'',to_char(b.tra_truoc/thoigian_sudung,'''|| store.fomart_number ||'''))||'' VNĐ/tháng'' datcoc,
b.thoigian_sudung||'' Tháng'' thoigian_sudung,
decode(a.khoitao_tt,1,''Có th¿ hòa m¿ng tr¿ tru¿c ho¿c tr¿ sau'',
''Ch¿ du¿c hòa m¿ng tr¿ sau'') hinhthuc_hm
from '||psschema||'khosos a, '||psschema||'kieusos b where a.status in (0,1) and a.kieuso_id=b.kieuso_id and a.kieuso_id is not null';
if pskieuso_id is not null then
s:=s||' and a.kieuso_id in ('||pskieuso_id||')';
end if;
if psso_tb is not null then
s:=s||' and a.so_tb like '''||replace(tb_,'*','%')||'''';
end if;
s:=s||' order by a.status';
s:= dms_admin.util.xuly_phantrang(s, pspageNum, psPageRec);
s:='select decode(mod(rownum,2),1,''#ffffff'',''#f9f6f3'') bg_color,a.* from ('||s||') a';
--insert into dms_admin.testsql values (s);
open ref_ for s;
return ref_;
exception
when others then
DECLARE
err_ varchar2(1000);
begin
rollback;
err_:='Loi thuc hien: '||to_char(sqlcode);
insert into numstore.log_sql_err (content,ngayth) values ('layds_thuebao.'||err_||'|'||psschema||'|'||psuserip,sysdate);
commit;
open ref_ for 'select :1 ten_kieuso from dual' using err_;
return ref_;
end;
end;
|
|
|
|
Re: Parse once - Execute many [message #487969 is a reply to message #487429] |
Mon, 03 January 2011 22:04 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
trantuananh24hg
Messages: 744 Registered: January 2007 Location: Ha Noi, Viet Nam
|
Senior Member |
|
|
Dear!
I would like not to open new topic, so, may I post here?
Think the question is related to my topic.
I'd like to search, find and think about the parse. It's difficult to make ideas into the true, how?
I'd like to save the parse (any parsing - hard/soft/execution and parse tree) of any SQL. I'v read [[Parsing| Parsing]], so, may you introduce someway to save or go to closer to my idea?
How does Oracle build parse tree?
How does Oracle store parse tree in shared_pool?
In which, can I do restrict something from fix_table to build for myself? For example:
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
----------
SQL_ID 9mz65qwxmwr7m
--------------------
SELECT COUNT(*) FROM "SUBADMIN"."LICH_SU_TB" "A1" WHERE "A1"."SO_TB"=:"SYS_B_0"
AND
"A1"."NGAY_THANG">ADD_MONTHS(SYSDATE@!,(-:"SYS_B_1")) AND ("A1"."MA_DV"=:"SYS_B_
2" OR "A1"."MA_DV"=:"SYS_B_3")
Plan hash value: 618329699
--------------------------------------------------------------------------------
--------------------------------------
| Id | Operation | Name | Rows | Bytes | C
ost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------
--------------------------------------
| 0 | SELECT STATEMENT | | | |
290 (100)| | | |
| 1 | SORT AGGREGATE | | 1 | 26 |
| | | |
| 2 | PARTITION RANGE ITERATOR | | 1 | 26 |
290 (0)| 00:00:04 | KEY | 98 |
| 3 | PARTITION HASH SINGLE | | 1 | 26 |
290 (0)| 00:00:04 | KEY | KEY |
| 4 | TABLE ACCESS BY LOCAL INDEX ROWID| LICH_SU_TB | 1 | 26 |
290 (0)| 00:00:04 | | |
| 5 | INDEX RANGE SCAN | LSTB_SOTB_IDX | 114 | |
197 (0)| 00:00:03 | | |
--------------------------------------------------------------------------------
--------------------------------------
SQL_ID 9mz65qwxmwr7m
--------------------
SELECT COUNT(*) FROM "SUBADMIN"."LICH_SU_TB" "A1" WHERE "A1"."SO_TB"=:"SYS_B_0"
AND
"A1"."NGAY_THANG">ADD_MONTHS(SYSDATE@!,(-:"SYS_B_1")) AND ("A1"."MA_DV"=:"SYS_B_
2" OR "A1"."MA_DV"=:"SYS_B_3")
Plan hash value: 1194074997
--------------------------------------------------------------------------------
-------------------------------------------
| Id | Operation | Name | Rows | Byte
s | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------
-------------------------------------------
| 0 | SELECT STATEMENT | | |
| 212 (100)| | | |
| 1 | SORT AGGREGATE | | 1 | 2
6 | | | | |
| 2 | PARTITION RANGE ITERATOR | | 1 | 2
6 | 212 (0)| 00:00:03 | KEY | 98 |
| 3 | PARTITION HASH SINGLE | | 1 | 2
6 | 212 (0)| 00:00:03 | KEY | KEY |
| 4 | TABLE ACCESS BY LOCAL INDEX ROWID| LICH_SU_TB | 1 | 2
6 | 212 (0)| 00:00:03 | | |
| 5 | INDEX RANGE SCAN | LSTB_NGAYTHANG_IDX | 29 |
| 197 (0)| 00:00:03 | | |
--------------------------------------------------------------------------------
-------------------------------------------
The table is about ~1 billion rows. One statement from App was ~0.3s, however, if about 10.000 concurrent session calling, the performance is rise, exactly I/O performance.
So, I just want to build my table to store parse tree and decide to re-write the statement or make an idea to change the table architecture, or to do something, if the Oracle optimizer will not change for any tuning-instance-sql way.
Thank you!
[Updated on: Mon, 03 January 2011 22:06] Report message to a moderator
|
|
|
Goto Forum:
Current Time: Mon Sep 09 09:58:18 CDT 2024
|