Hello,
this is my DB:
SQL*Plus: Release 11.2.0.1.0 Production on Di Mai 28 16:59:18 2013
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Verbunden mit:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning and Automatic Storage Management options
When I create an AWR report I get this:
SQL ordered by Elapsed Time DB/Inst: PACSINT/pacsint Snaps: 182-183
-> Resources reported for PL/SQL code includes the resources used by all SQL
statements called by the code.
-> % Total DB Time is the Elapsed Time of the SQL statement divided
into the Total Database Time multiplied by 100
-> %Total - Elapsed Time as a percentage of Total DB time
-> %CPU - CPU Time as a percentage of Elapsed Time
-> %IO - User I/O Time as a percentage of Elapsed Time
-> Captured SQL account for 66.1% of Total DB Time (s): 2,668
-> Captured PL/SQL account for 44.2% of Total DB Time (s): 2,668
Elapsed Elapsed Time
Time (s) Executions per Exec (s) %Total %CPU %IO SQL Id
---------------- -------------- ------------- ------ ------ ------ -------------
659.1 75,454 0.01 24.7 63.8 36.6 fthhmwpwayt0r
Module: JDBC Thin Client
INSERT INTO ...
372.1 1 372.10 13.9 98.5 1.4 6q060rrpjya4m
Module: DBMS_SCHEDULER
DECLARE job BINARY_INTEGER := :job; next_date TIMESTAMP WITH TIME ZONE := :myda
te; broken BOOLEAN := FALSE; job_name VARCHAR2(30) := :job_name; job_subname
VARCHAR2(30) := :job_subname; job_owner VARCHAR2(30) := :job_owner; job_start
TIMESTAMP WITH TIME ZONE := :job_start; job_scheduled_start TIMESTAMP WITH TIME
368.9 75,454 0.00 13.8 100.0 .0 73pq7xw32ksnu
Module: JDBC Thin Client
SELECT ...
335.5 2 167.76 12.6 98.7 1.2 2hmsvxxf30kmb
Module: DBMS_SCHEDULER
DECLARE job BINARY_INTEGER := :job; next_date TIMESTAMP WITH TIME ZONE := :myda
te; broken BOOLEAN := FALSE; job_name VARCHAR2(30) := :job_name; job_subname
VARCHAR2(30) := :job_subname; job_owner VARCHAR2(30) := :job_owner; job_start
TIMESTAMP WITH TIME ZONE := :job_start; job_scheduled_start TIMESTAMP WITH TIME
231.4 1 231.37 8.7 98.8 1.2 300mhcs8n6rgu
Module: DBMS_SCHEDULER
DECLARE job BINARY_INTEGER := :job; next_date TIMESTAMP WITH TIME ZONE := :myda
te; broken BOOLEAN := FALSE; job_name VARCHAR2(30) := :job_name; job_subname
VARCHAR2(30) := :job_subname; job_owner VARCHAR2(30) := :job_owner; job_start
TIMESTAMP WITH TIME ZONE := :job_start; job_scheduled_start TIMESTAMP WITH TIME
162.0 24 6.75 6.1 12.7 88.5 16k7zaajus0vu
Module: JDBC Thin Client
SELECT ...
126.9 1 126.94 4.8 97.8 2.1 702892338gv52
Module: DBMS_SCHEDULER
DECLARE job BINARY_INTEGER := :job; next_date TIMESTAMP WITH TIME ZONE := :myda
te; broken BOOLEAN := FALSE; job_name VARCHAR2(30) := :job_name; job_subname
VARCHAR2(30) := :job_subname; job_owner VARCHAR2(30) := :job_owner; job_start
TIMESTAMP WITH TIME ZONE := :job_start; job_scheduled_start TIMESTAMP WITH TIME
80.0 6 13.33 3.0 93.4 6.6 6u0tn5f88dfxc
Module: JDBC Thin Client
INSERT INTO ...
73.9 589 0.13 2.8 100.0 .0 80c8br3z767zr
Module: JDBC Thin Client
SELECT ..
SQL ordered by Elapsed Time DB/Inst: PACSINT/pacsint Snaps: 182-183
-> Resources reported for PL/SQL code includes the resources used by all SQL
statements called by the code.
-> % Total DB Time is the Elapsed Time of the SQL statement divided
into the Total Database Time multiplied by 100
-> %Total - Elapsed Time as a percentage of Total DB time
-> %CPU - CPU Time as a percentage of Elapsed Time
-> %IO - User I/O Time as a percentage of Elapsed Time
-> Captured SQL account for 66.1% of Total DB Time (s): 2,668
-> Captured PL/SQL account for 44.2% of Total DB Time (s): 2,668
Elapsed Elapsed Time
Time (s) Executions per Exec (s) %Total %CPU %IO SQL Id
---------------- -------------- ------------- ------ ------ ------ -------------
67.4 33,664 0.00 2.5 81.1 19.4 9xp8yaqtjwdc9
Module: JDBC Thin Client
INSERT ...
49.6 1 49.62 1.9 94.3 5.6 6k1qu5v9m2y0u
Module: DBMS_SCHEDULER
DECLARE job BINARY_INTEGER := :job; next_date TIMESTAMP WITH TIME ZONE := :myda
te; broken BOOLEAN := FALSE; job_name VARCHAR2(30) := :job_name; job_subname
VARCHAR2(30) := :job_subname; job_owner VARCHAR2(30) := :job_owner; job_start
TIMESTAMP WITH TIME ZONE := :job_start; job_scheduled_start TIMESTAMP WITH TIME
49.3 1 49.33 1.8 98.3 1.7 0mr4y0rc976rh
Module: DBMS_SCHEDULER
DECLARE job BINARY_INTEGER := :job; next_date TIMESTAMP WITH TIME ZONE := :myda
te; broken BOOLEAN := FALSE; job_name VARCHAR2(30) := :job_name; job_subname
VARCHAR2(30) := :job_subname; job_owner VARCHAR2(30) := :job_owner; job_start
TIMESTAMP WITH TIME ZONE := :job_start; job_scheduled_start TIMESTAMP WITH TIME
42.6 572,302 0.00 1.6 101.0 .0 4m7m0t6fjcs5x
update seq$ ...
30.1 1,883 0.02 1.1 100.0 .0 bfya3g2xf0rpj
Module: JDBC Thin Client
update ...
27.3 1,883 0.01 1.0 100.0 .0 53wcpdaurprbk
Module: JDBC Thin Client
SELECT...
My
Question is: why do I have <tt>DECLARE job</tt>-Statemens in the top 15 entries?
This jobs simply call alle the same procedure in a package (giving different parameters so that they affect diffent data).
If the statement time includes the execution of nested statements: how do I find out the most expensive statements run by the job?
If the statement time does not include he execution of nested statements: how do I find out why the job creation takes so long?
bye
TPD
If the time consume