OraFAQ Forum: Performance Tuning » how to get the explain plan of a insert statement

Home » RDBMS Server » Performance Tuning » how to get the explain plan of a insert statement

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

how to get the explain plan of a insert statement [message #128405]

Mon, 18 July 2005 06:51

d.dineshkumar
Messages: 211
Registered: April 2005
Location: Kolkatta

Senior Member

hi bosses,
how to get the explain plan of a insert statement. I know how to do it for select but for insert.
suppose this is the statement:

INSERT INTO EMP(EMPNO,ENAME,SAL) VALUES(p_empno,p_enm,12000);

here p_empno and p_enm are variables.

Thanks 4 ur reply.
Dinesh

Report message to a moderator

Re: how to get the explain plan of a insert statement [message #128411 is a reply to message #128405]

Mon, 18 July 2005 07:16

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

Same method.

scott@9i > set autotrace on
scott@9i > insert into dept values ('8','a','d');

1 row created.


Execution Plan
----------------------------------------------------------
   0      INSERT STATEMENT Optimizer=CHOOSE




Statistics
----------------------------------------------------------
          0  recursive calls
          4  db block gets
          1  consistent gets
          0  physical reads
        368  redo size
       1014  bytes sent via SQL*Net to client
       1050  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

Report message to a moderator

Re: how to get the explain plan of a insert statement [message #128414 is a reply to message #128411]

Mon, 18 July 2005 07:42

d.dineshkumar
Messages: 211
Registered: April 2005
Location: Kolkatta

Senior Member

Thanks mahesh,
as it is an insert stement ,so it is having consistent gets.Am i correct.if it is a select statement then??
Actulayy mahesh i have been told to find the cost of each query so how to start this procedure.What should i look in this set autotrace output.

Thanks
Dinesh

Report message to a moderator

Re: how to get the explain plan of a insert statement [message #128417 is a reply to message #128414]

Mon, 18 July 2005 07:45

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

i dont understand your question.
Please reframe it.

>>Actulayy mahesh i have been told to find the cost of each query
COST cannot be considered as a measure for performance.
It is just a derived number.

Report message to a moderator

Re: how to get the explain plan of a insert statement [message #128418 is a reply to message #128405]

Mon, 18 July 2005 07:48

smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida

Senior Member

http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10752/ex_plan.htm#19259

Report message to a moderator

Re: how to get the explain plan of a insert statement [message #128422 is a reply to message #128417]

Mon, 18 July 2005 08:06

d.dineshkumar
Messages: 211
Registered: April 2005
Location: Kolkatta

Senior Member

Mahesh,
actually there is an existing package. and it is showing 100%cpu usage while on testing.
So we have been told to find the cost of all the select,insert,update used in that package.
I know one thing that the cost shown in explain plan is an arbitary value.
but how to get the query's performance cost.

I hope u have understand the problem.

Thanks
Dinesh

Report message to a moderator

Re: how to get the explain plan of a insert statement [message #128425 is a reply to message #128422]

Mon, 18 July 2005 08:19

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

'100% cpu' can be cause by many many factors.
Explain plan is for different purpose.

YOu have not posted any information on oracle version,OS,your storage/extent management etc, so anyone who's going to respond to your question would ask a zillion questions!.

first identify which process is taking the cpu (use OS tools).
If it is oracle process, is it an user process or background process?
In Most of cases ( in older releases) the reason was dictionary managed tablespace with non-zero pctincrease and INITIAL not equal to next and SMON process.

So breakdown the package and identify the process first.
Did you look into your statspack / trace files?

Report message to a moderator

Re: how to get the explain plan of a insert statement [message #128426 is a reply to message #128425]

Mon, 18 July 2005 08:26

d.dineshkumar
Messages: 211
Registered: April 2005
Location: Kolkatta

Senior Member

ok.
i will do like that.
But suppose i want to have an autotrace of an insert statement which is using some variables then how to do it in sql plus.
as current if i am writing as below:-

SET AUTOTRACE ON

INSERT INTO TABLE_A (A1,A2,A3) VALUES( Mad

,:Y,:Z);

Then it is not recognising x ,y and z.Actually how can i replce the variable of an insert statement in my pl/sql procedure,so that i can have the autotrace out put for these insert statement.

don't mind..i may be wrong in this question.

We r using 9.2 and os is win 2000.

Thanks
Dinesh

Report message to a moderator

Re: how to get the explain plan of a insert statement [message #128431 is a reply to message #128426]

Mon, 18 July 2005 08:43

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

Please go through link SMartin has provied.
ALso read documentation regarding tracefiles and tkprof.

scott@9i > alter session set timed_statistics=true
  2  ;

Session altered.

scott@9i > alter session set max_dump_file_size=unlimited;

Session altered.

scott@9i > alter session set tracefile_identifier='DINESH';

Session altered.

scott@9i > alter session set events '10046 trace name context forever, level 8';

Session altered.

scott@9i > insert into dept values ('8','a','d');

1 row created.

scott@9i > commit;

Commit complete.

scott@9i > alter session set events '10046 trace name context off';

Session altered.

scott@9i > !ls -lrt $ORACLE_BASE/admin/mutation/udump/*DINESH*
-rw-r-----   1 oracle   dba         2967 Jul 18 09:34 /u01/app/oracle/admin/mutation/udump/mutation_ora_18383_DINESH.trc


oracle#tkprof /u01/app/oracle/admin/mutation/udump/mutation_ora_18383_DINESH.trc DINESH1.Txt


lookinto DINESH1.Txt.

THe relevant part clearly says, the CPU used by this particular Insert statment.



  ********************************************************************************
    41
    42  insert into dept
    43  values
    44   ('8','a','d')
    45
    46
    47  call     count       cpu    elapsed       disk      query    current        rows
    48  ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    49  Parse        1      0.00       0.00          0          0          0           0
    50  Execute      1      0.00       0.00          0          1          4           1
    51  Fetch        0      0.00       0.00          0          0          0           0
    52  ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    53  total        2      0.00       0.00          0          1          4           1
    54
    55  Misses in library cache during parse: 1
    56  Optimizer goal: CHOOSE
    57  Parsing user id: 32
    58
    59  Elapsed times include waiting on following events:
    60    Event waited on                             Times   Max. Wait  Total Waited
    61    ----------------------------------------   Waited  ----------  ------------
    62    SQL*Net message to client                       1        0.00          0.00
    63    SQL*Net message from client                     1        0.00          0.00
    64  ********************************************************************************

Report message to a moderator

Re: how to get the explain plan of a insert statement [message #128433 is a reply to message #128431]

Mon, 18 July 2005 08:54

d.dineshkumar
Messages: 211
Registered: April 2005
Location: Kolkatta

Senior Member

Thanks mahesh,
i will come back to you after doing these things only.
I know i have to read a lot regarding this.

Thanks
Dinesh

Report message to a moderator

Re: how to get the explain plan of a insert statement [message #128441 is a reply to message #128433]

Mon, 18 July 2005 09:50

d.dineshkumar
Messages: 211
Registered: April 2005
Location: Kolkatta

Senior Member

mahesh,
how can i get the execution plan of the insert statement that i have used in my procedure,where i am not giving original values but variables of my procedure.

For ex:-
INSERT INTO TABLEA(A1,A2,A3) VALUES(p_a1,p_a2,p_a3);

Here p_a1/a2/a3 are variables.

Thanks
Dinesh

Report message to a moderator

Re: how to get the explain plan of a insert statement [message #128447 is a reply to message #128441]

Mon, 18 July 2005 11:15

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

Obviously you are not reading the docs.
In the explain plan

INSERT STATEMENT GOAL: CHOOSE
is the plan.

A level 12 tracing will include your Bind variables.

--
--
--
scott@9i > alter session set timed_statistics=true;

Session altered.

scott@9i > alter session set max_dump_file_size=unlimited;

Session altered.

scott@9i > alter session set tracefile_identifier='DINESH';

Session altered.

scott@9i > alter session set events '10046 trace name context forever, level 12';

Session altered.

scott@9i > variable dno number;
scott@9i > variable loc varchar2(10);
scott@9i > variable dname varchar2(10);

scott@9i > exec :dno :=9

PL/SQL procedure successfully completed.

scott@9i > exec :loc :='b'

PL/SQL procedure successfully completed.

scott@9i > exec :dname :='b';

PL/SQL procedure successfully completed.

scott@9i > insert into dept (deptno,dname,loc) values (:dno,:dname,:loc);

1 row created.

scott@9i > commit;

Commit complete.

scott@9i > alter session set events '10046 trace name context off'
  2  ;

Session altered.

oracle@mutation#tkprof  mutation_ora_18504_DINESH.trc Dinesh2.txt explain=scott/tiger


   139  insert into dept (deptno,dname,loc)
   140  values
   141   (:dno,:dname,:loc)
   142
   143
   144  call     count       cpu    elapsed       disk      query    current        rows
   145  ------- ------  -------- ---------- ---------- ---------- ----------  ----------
   146  Parse        1      0.00       0.00          0          0          0           0
   147  Execute      1      0.00       0.00          0          1          4           1
   148  Fetch        0      0.00       0.00          0          0          0           0
   149  ------- ------  -------- ---------- ---------- ---------- ----------  ----------
   150  total        2      0.00       0.00          0          1          4           1
   151
   152  Misses in library cache during parse: 1
   153  Optimizer goal: CHOOSE
   154  Parsing user id: 32  (SCOTT)
   155
   156  Rows     Execution Plan
   157  -------  ---------------------------------------------------
   158        0  INSERT STATEMENT   GOAL: CHOOSE
   159
   160

Report message to a moderator

Re: how to get the explain plan of a insert statement [message #128489 is a reply to message #128447]

Mon, 18 July 2005 23:41

d.dineshkumar
Messages: 211
Registered: April 2005
Location: Kolkatta

Senior Member

thanks again,
i am going on,but still not completed the docs.

Thanks Bye
dinesh

Report message to a moderator

Previous Topic:	increasing buffer cache....!
Next Topic:	tuning insert

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Nov 23 16:41:24 CST 2024