Database Reference
In-Depth Information
Execution Plan:
Step Parent Rows Row Source
---- ------ -------- ------------------------------------------------------------
1 0 0 LOAD TABLE CONVENTIONAL (cr=3 pr=4 pw=4 time=0 us)
2 1 1 SEQUENCE CUSTOMER_ID_SEQ (cr=3 pr=1 pw=1 time=0 us)
(object_id=15920)
The SQL identifier in reports allows for easy integration with AWR. It may be used as input
to the AWR SQL report script
awrsqrpt.sql
or
DBMS_XPLAN
.
SQL> SELECT * FROM TABLE (dbms_xplan.display_awr('b85s0yd5dy1z3'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------
SQL_ID b85s0yd5dy1z3
--------------------
INSERT INTO customer(id, name, phone) VALUES (customer_id_seq.nextval,
:name, :phone) RETURNING id INTO :id
Plan hash value: 2690979981
------------------------------------------------------------
| Id | Operation | Name | Cost |
------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 |
PLAN_TABLE_OUTPUT
------------------------------------------------------------
| 1 | LOAD TABLE CONVENTIONAL | | |
| 2 | SEQUENCE | CUSTOMER_ID_SEQ | |
------------------------------------------------------------
Note
----
- cpu costing is off (consider enabling it)
Module and action are extracted from application instrumentation entries. If no such
entries are present in a trace file, both module and action are reported as “undefined”. The
Oracle9
i
APPNAME
entry format is used to extract module and action from Oracle9
i
trace files.
The recursive call depth is taken from the parameter
dep
of
PARSING
IN
CURSOR
entries.
Lessons Learned
The ESQLTRCPROF profiler accepts an extended SQL trace file as input and calculates session
level and statement level resource profiles. To the best of my knowledge, it is the only profiler
that categorizes
SQL*Net message from client
into
think time
and unavoidable network round-
trips between client and database server. The pseudo wait event
think time
is defined as a
WAIT
entry with
nam='SQL*Net message from client'
where the
ela
value exceeds a configurable
threshold. Remember that a DBA does not stand any chance to reduce think time accumulated
