Home » RDBMS Server » Performance Tuning » Very high executions (Parse=Execute) (Oracle 10g, Solaris 10)
| Very high executions (Parse=Execute) [message #432870] |
Thu, 26 November 2009 22:56  |
trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam
|
Senior Member |
|
|
Hi all!
I have a procedure:
/* Formatted on 2009/11/27 11:49 (Formatter Plus v4.8.7) */
DECLARE
ssql_ VARCHAR2 (32000);
ref_ sys_refcursor;
a VARCHAR2 (100);
n NUMBER := 0;
itemp NUMBER := 0;
smatb VARCHAR2 (32000);
ngaysinh DATE;
BEGIN
FOR j IN (SELECT a.short_name, a.SCHEMA
FROM ccs_common.AGENT a
WHERE a.SCHEMA IS NOT NULL AND a.short_name != 'GPC')
LOOP
BEGIN
SELECT COUNT (1)
INTO n
FROM all_tables
WHERE owner =
''
|| UPPER (SUBSTR ('CCS_COMMON.',
1,
LENGTH ('CCS_COMMON.') - 1
)
)
|| ''
AND table_name =
'BIRTHDAY_VNP_' || REPLACE (TRUNC (SYSDATE), '/', '');
IF n > 0
THEN
ssql_ :=
'Delete from ccs_common.birthday_vnp_'
|| REPLACE (TRUNC (SYSDATE), '/', '')
|| '';
EXECUTE IMMEDIATE ssql_;
COMMIT;
ELSE
ssql_ :=
' Create table ccs_common.birthday_vnp_'
|| REPLACE (TRUNC (SYSDATE), '/', '')
|| '
(
MA_TB varchar2(20),
NGAYSINH DATE
)';
EXECUTE IMMEDIATE ssql_;
END IF;
ssql_ :=
' Insert into ccs_common.birthday_vnp_'
|| REPLACE (TRUNC (SYSDATE), '/', '')
|| '
Select a.somay,a.ngaysinh from '
|| j.SCHEMA
|| 'danhba_dds_pttb a
where a.ngaysinh is not null
and a.trangthai_id in (1,2,3)';
EXECUTE IMMEDIATE ssql_;
COMMIT;
a := REPLACE (TRUNC (SYSDATE), '/', '');
ssql_ :=
'select ma_tb,ngaysinh from ccs_common.birthday_vnp_' || a || '';
OPEN ref_ FOR ssql_;
LOOP
FETCH ref_
INTO smatb, ngaysinh;
EXIT WHEN ref_%NOTFOUND;
SELECT COUNT (1)
INTO itemp
FROM ccs_common.birthday_vnp a
WHERE a.ma_tb = smatb;
IF (itemp > 0)
THEN
ssql_ :=
'update ccs_common.birthday_vnp set(ngaysinh,ten_tt,diachi_tt)=
( SELECT a.ten_tt, a.diachi_tt,b.ngaysinh
FROM '
|| j.SCHEMA
|| 'khachhangs_pttb a, '
|| j.SCHEMA
|| 'danhba_dds_pttb b
WHERE a.ma_kh = b.ma_kh
AND b.somay= '''
|| smatb
|| '''
) where ma_tb='''
|| smatb
|| '''';
EXECUTE IMMEDIATE ssql_;
COMMIT;
ELSE
ssql_ :=
'insert into ccs_common.birthday_vnp SELECT a.ma_kh,b.somay ma_tb, a.ten_tt, a.diachi_tt,b.ngaysinh
, a.ms_thue, a.taikhoan,'''
|| j.short_name
|| ''' matinh , b.ngay_cn ngay_cn,a.so_gt
FROM '
|| j.SCHEMA
|| 'khachhangs_pttb a, '
|| j.SCHEMA
|| 'danhba_dds_pttb b
WHERE a.ma_kh = b.ma_kh
AND b.somay= '''
|| smatb
|| ''' ';
EXECUTE IMMEDIATE ssql_;
COMMIT;
END IF;
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
BEGIN
DBMS_OUTPUT.put_line ('');
END;
END;
ssql_ :=
'drop table ccs_common.birthday_vnp_'
|| REPLACE (TRUNC (SYSDATE), '/', '')
|| '';
EXECUTE IMMEDIATE ssql_;
END LOOP;
COMMIT;
END;
And when it came to insert into, the statistics show very high executions, although the query used index
insert into ccs_common.birthday_vnp SELECT a.ma_kh,b.somay ma_tb, a.ten_tt, a.diachi_tt,b.ngaysinh
, a.ms_thue, a.taikhoan,:"SYS_B_0" matinh , b.ngay_cn ngay_cn,a.so_gt
FROM CCS_AGG.khachhangs_pttb a, CCS_AGG.danhba_dds_pttb b
WHERE a.ma_kh = b.ma_kh
AND b.somay= :"SYS_B_1"
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 14479 46.19 47.87 0 0 0 0
Execute 14479 83.54 85.59 162 101473 104102 14479
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 28958 129.73 133.46 162 101473 104102 14479
Misses in library cache during parse: 14479
Misses in library cache during execute: 14479
Optimizer mode: ALL_ROWS
Parsing user id: 534 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 NESTED LOOPS (cr=6 pr=0 pw=0 time=58 us)
1 TABLE ACCESS BY INDEX ROWID DANHBA_DDS_PTTB (cr=3 pr=0 pw=0 time=35 us)
1 INDEX UNIQUE SCAN DANHBA_DDS_PTTB_PK (cr=2 pr=0 pw=0 time=21 us)(object id 320119)
1 TABLE ACCESS BY INDEX ROWID KHACHHANGS_PTTB (cr=3 pr=0 pw=0 time=21 us)
1 INDEX UNIQUE SCAN PK_MA_KH_KHACHHANG_PTTB (cr=2 pr=0 pw=0 time=13 us)(object id 320237)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 162 0.00 0.01
latch: library cache 247 0.39 1.70
latch: shared pool 64 0.08 0.67
latch: row cache objects 4 0.05 0.07
buffer exterminate 2 0.27 0.27
********************************************************************************
The table description
logvnp@VNP> select owner, table_name, index_name, index_type, num_rows
2 from dba_indexes
3 where owner='CCS_COMMON'
4 and table_name='BIRTHDAY_VNP';
OWNER TABLE_NAME INDEX_NAME INDEX_TYPE NUM_ROWS
---------- ------------ ---------- --------------------------- ----------
CCS_COMMON BIRTHDAY_VNP IDEX_MATB NORMAL
1 row selected.
logvnp@VNP> desc ccs_common.birthday_vnp
Name Null? Type
----------------------------------------------------- -------- ---------------
--------------------
MA_KH NOT NULL VARCHAR2(30)
MA_TB NOT NULL VARCHAR2(100)
TEN_TT NOT NULL VARCHAR2(200)
DIACHI_TT NOT NULL VARCHAR2(500)
NGAYSINH DATE
MS_THUE VARCHAR2(90)
TAIKHOAN VARCHAR2(60)
MATINH CHAR(3)
NGAY_CN DATE
SO_GT VARCHAR2(20)
The question:
How do I reduce Parse when using procedure? How do I use bind variable for the query?
The value smatb is not a consultant, it always change
logvnp@VNP> select ma_tb
2 from ccs_common.birthday_vnp
3 where rownum<=10;
MA_TB
--------------
84915670005
84915670012
84915670015
84915670016
84915670017
84915670021
84915670023
84915670031
84915670032
84915670034
10 rows selected.
logvnp@VNP>
Thank you for your suggestion!
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Nov 22 12:38:46 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
