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 Go to next message
trantuananh24hg
Messages: 664
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!
Re: Very high executions (Parse=Execute) [message #432871 is a reply to message #432870] Thu, 26 November 2009 23:02 Go to previous messageGo to next message
BlackSwan
Messages: 24915
Registered: January 2009
Senior Member
> EXECUTE IMMEDIATE ssql_;
Requires HARD PARSE!
If SQL is static except for bind variables, then EXECUTE IMMEDIATE is not needed & should be not done.
Oracle is only doing EXACTLY what you directed it to do.
Re: Very high executions (Parse=Execute) [message #432880 is a reply to message #432870] Fri, 27 November 2009 00:37 Go to previous message
trantuananh24hg
Messages: 664
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Thank you, Swan, I'll feedback as soon as possible!
Previous Topic: Performance of a Query
Next Topic: After Version Enabled (Workspace Manager)
Goto Forum:
  


Current Time: Fri Sep 30 22:45:28 CDT 2016

Total time taken to generate the page: 0.09108 seconds