You Asked

Hi

We have a PL/SQL application which is inserting into database tables. These tables have materialized view logs.
Noticed when looking into v$sql, that number of executions of these insert statements is much greater then number of parsing operations but
insert statements for mlog$ -tables seem to have same number of parsings as executions.
Why these insertions into mlog -tables are parsed each time they are executed ?

lh

and Connor said...

I need to see your test case. Here's mine


SQL> create table t as select * from dba_objects
  2  where rownum <= 100 and object_id is not null;

Table created.

SQL>
SQL> alter table t add primary key ( object_id );

Table altered.

SQL>
SQL> create materialized view log on t;

Materialized view log created.

SQL>
SQL> exec dbms_monitor.session_trace_enable;

PL/SQL procedure successfully completed.

SQL>
SQL> insert into t ( object_id)  select -object_id from t where rownum <= 10;

10 rows created.

SQL> commit;

Commit complete.

SQL> update t set owner = lower(owner) where owner = 'SYS' and rownum <= 10;

10 rows updated.

SQL> commit;

Commit complete.

SQL> update t set owner = lower(owner) where owner = 'SYSTEM' and rownum <= 10;

0 rows updated.

SQL> commit;

Commit complete.

SQL>
SQL> exec dbms_monitor.session_trace_disable;

PL/SQL procedure successfully completed.

SQL> @tk

VALUE
----------------------------------------------------------------------------------------------------------------------------------
VALUE1
----------------------------------------------------------------------------------------------------------------------------------
C:\ORACLE\diag\rdbms\db122\db122\trace\db122_ora_15304.trc
C:\ORACLE\diag\rdbms\db122\db122\trace\db122_ora_15304.prf


1 row selected.


TKPROF: Release 12.2.0.1.0 - Development on Tue Aug 22 11:03:56 2017

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.



SQL>

INSERT /*+ NO_DST_UPGRADE_INSERT_CONV IDX(0) */ INTO "MCDONAC"."MLOG$_T" 
  (dmltype$$,old_new$$,snaptime$$,change_vector$$,xid$$,"OBJECT_ID") 
VALUES
 (:d,:o,to_date('4000-01-01:00:00:00','YYYY-MM-DD:HH24:MI:SS'),:c,:x,:1)


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute     10      0.00       0.00          0          9        128          10
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       11      0.00       0.00          0          9        128          10

Rating

(2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

missed

A reader, August 22, 2017 - 7:18 am UTC

Do you think in client production env we are able to do such silly test? Requester is pointing to v $sql you are not. The question is not to prove what parsed vs executed. But to explain such situation for mlog. The requester is not asking for some behavior case but pointing to an observed amount of indicators. So maybe such behavior is normal maybe not so what could be the reasons upon your expertise ? I suggest to requesting to paste his s3lect from v $sql.

August 23, 2017 - 2:21 am UTC

Do you think in client production env we are able to do such silly test?

Well...actually NO test case was provided. None! Zero! Zilch!

Given that

1) the Guidelines for asking a question state:

"make sure that you provide the smallest, self-contained and correct (executes without error or reproduces the error in question) script"

and

2) the person who asked the question actually TICKS THE BOX that says "Yes, I've read the guidelines"

Then you say

"I suggest to requesting to paste his s3lect from v $sql. "

What SELECT ? There wasn't one because there was no test case.

Chuckling to myself here.

A reader, August 22, 2017 - 1:55 pm UTC

SQL> create table lh_t (
t_id number(10) not null,
t_dummy varchar2(100 char)
)
Table created.
SQL> alter table lh_t add primary key ( t_id)
Table altered.
SQL> create materialized view log on lh_t
Materialized View log created.
SQL> begin
for i in 1..100 loop
       insert into lh_t(t_id,t_dummy) values (i, 'Hi');
end loop;
commit;
end;
 PL/SQL procedure successfully completed.
SQL> select  EXECUTIONS, PARSE_CALLS,ROWS_PROCESSED, SQL_TEXT  FROM  v$sql V where upper(sql_text) like 'INSERT%LH_T%'

EXECUTIONS PARSE_CALLS ROWS_PROCESSED
---------- ----------- --------------
SQL_TEXT                                                                        
--------------------------------------------------------------------------------
       100         100            100
INSERT /*+ NO_DST_UPGRADE_INSERT_CONV IDX(0) */ INTO MLOG$_LH_T" 
(dmltype$$,old_new$$,snaptime$$,change_vector$$,xid$$,"T_ID") VALUES (:d,:o,to_d
ate('4000-01-01:00:00:00','YYYY-MM-DD:HH24:MI:SS'),:c,:x,:1)                    
                                                                                
       100           0            100
INSERT INTO LH_T(T_ID,T_DUMMY) VALUES (:B1 , 'Hi')                              
                                                                                

2 rows selected.

Hi

Sorry that I didn't include at first an example.

I am wondering why there is 100 as PARSE_CALLS for insert statement into materialized view log.

lh

August 28, 2017 - 2:43 am UTC

OK, I see what you mean.

I suspect it's just one of things that we get better at with each version of Oracle

11.2.0.4
========

SQL> create table lh_t (
  2  t_id number(10) not null,
  3  t_dummy varchar2(100 char)
  4  );

Table created.

SQL>
SQL> alter table lh_t add primary key ( t_id);

Table altered.

SQL>
SQL> create materialized view log on lh_t;

Materialized view log created.

SQL>
SQL> begin
  2  for i in 1..100 loop
  3         insert into lh_t(t_id,t_dummy) values (i, 'Hi');
  4  end loop;
  5  commit;
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL>
SQL> select  EXECUTIONS, PARSE_CALLS,ROWS_PROCESSED, SQL_TEXT  FROM  v$sql V where upper(sql_text) like 'INSERT%LH_T%';

EXECUTIONS PARSE_CALLS ROWS_PROCESSED SQL_TEXT
---------- ----------- -------------- ----------------------------------------------------------------
       100         100            100 INSERT /*+ IDX(0) */ INTO "MCDONAC"."MLOG$_LH_T" (dmltype$$,old_
                                      new$$,snaptime$$,change_vector$$,xid$$,"T_ID") VALUES (:d,:o,to_
                                      date('4000-01-01:00:00:00','YYYY-MM-DD:HH24:MI:SS'),:c,:x,:1)

       100           0            100 INSERT INTO LH_T(T_ID,T_DUMMY) VALUES (:B1 , 'Hi')

12.2.0.1
=========

SQL> create table lh_t (
  2  t_id number(10) not null,
  3  t_dummy varchar2(100 char)
  4  );

Table created.

SQL>
SQL> alter table lh_t add primary key ( t_id);

Table altered.

SQL>
SQL> create materialized view log on lh_t;

Materialized view log created.

SQL>
SQL> begin
  2  for i in 1..100 loop
  3         insert into lh_t(t_id,t_dummy) values (i, 'Hi');
  4  end loop;
  5  commit;
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL>
SQL> select  EXECUTIONS, PARSE_CALLS,ROWS_PROCESSED, SQL_TEXT  FROM  v$sql V where upper(sql_text) like 'INSERT%LH_T%';

EXECUTIONS PARSE_CALLS ROWS_PROCESSED SQL_TEXT
---------- ----------- -------------- ----------------------------------------------------------------
       100           0            100 INSERT /*+ NO_DST_UPGRADE_INSERT_CONV IDX(0) */ INTO "MCDONAC"."
                                      MLOG$_LH_T" (dmltype$$,old_new$$,snaptime$$,change_vector$$,xid$
                                      $,"T_ID") VALUES (:d,:o,to_date('4000-01-01:00:00:00','YYYY-MM-D
                                      D:HH24:MI:SS'),:c,:x,:1)

       100           0            100 INSERT INTO LH_T(T_ID,T_DUMMY) VALUES (:B1 , 'Hi')

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

missed

More to Explore

Design

Inserts into materialized view tables are parsed each time.

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

missed

More to Explore

Design