Home » RDBMS Server » Performance Tuning » Statspack snap shot at level 7 is slow (11.2.0.4. windows 2012 r2 Datacentre)
Statspack snap shot at level 7 is slow [message #665945] Wed, 04 October 2017 05:35 Go to next message
juniordbanewbie
Messages: 169
Registered: April 2014
Senior Member
Dear Sir/Mdm,


https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=366833143253174&parent=WIDGET_RECENT_DB&id=2182680.1&_afr WindowMode=0&_adf.ctrl-state=r3hwnx5bm_75

Statspack Running Slow on 11g Databases Due To Slow Statement 'Insert Into Stat$Sql_Plan...' (Doc ID 2182680.1) To BottomTo Bottom

currently I've already recompile statspack,
but after recompilation, whenever I take the snapshot at level 6 or 7, it hangs.

anyone has any experience on how to resolve statspack snap procedure at level 6 or 7 taking snapshot slowly?

thanks a lot!
Re: Statspack snap shot at level 7 is slow [message #665948 is a reply to message #665945] Wed, 04 October 2017 06:20 Go to previous messageGo to next message
John Watson
Messages: 7102
Registered: January 2010
Location: Global Village
Senior Member
I have not hit that problem, in release 11.x or 12.x. Have you completely removed it with spdrop and reinstalled with that new script? What wait event is it hanging on when you take a snapshot?

update: also, be sure to gather stats for the perfstat schema. I would raise dynamic sampling to at least 4 too.

[Updated on: Wed, 04 October 2017 06:24]

Report message to a moderator

Re: Statspack snap shot at level 7 is slow [message #665955 is a reply to message #665948] Wed, 04 October 2017 09:08 Go to previous messageGo to next message
juniordbanewbie
Messages: 169
Registered: April 2014
Senior Member
Dear John,

I did a

ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';

here's the trace log I've to kill the session while the session is taking level 7 snapshot.

INSERT INTO STATS$SQL_PLAN ( PLAN_HASH_VALUE , ID , OPERATION , OPTIONS , OBJECT_NODE , OBJECT# , OBJECT_OWNER , OBJECT_NAME , OBJECT_ALIAS , OBJECT_TYPE , OPTIMIZER , PARENT_ID , DEPTH , POSITION , SEARCH_COLUMNS , COST , CARDINALITY , BYTES , OTHER_TAG , PARTITION_START , PARTITION_STOP , PARTITION_ID , OTHER , DISTRIBUTION , CPU_COST , IO_COST , TEMP_SPACE , ACCESS_PREDICATES , FILTER_PREDICATES , PROJECTION , TIME , QBLOCK_NAME , REMARKS , SNAP_ID ) SELECT /*+ no_merge(new_plan) leading(new_plan s sp) use_nl(s) use_nl(sp) */ NEW_PLAN.PLAN_HASH_VALUE , SP.ID , MAX(SP.OPERATION) , MAX(SP.OPTIONS) , MAX(SP.OBJECT_NODE) , MAX(SP.OBJECT#) , MAX(SP.OBJECT_OWNER) , MAX(SP.OBJECT_NAME) , MAX(SP.OBJECT_ALIAS) , MAX(SP.OBJECT_TYPE) , MAX(SP.OPTIMIZER) , MAX(SP.PARENT_ID) , MAX(SP.DEPTH) , MAX(SP.POSITION) , MAX(SP.SEARCH_COLUMNS) , MAX(SP.COST) , MAX(SP.CARDINALITY) , MAX(SP.BYTES) , MAX(SP.OTHER_TAG) , MAX(SP.PARTITION_START) , MAX(SP.PARTITION_STOP) , MAX(SP.PARTITION_ID) , MAX(SP.OTHER) , MAX(SP.DISTRIBUTION) , MAX(SP.CPU_COST) , MAX(SP.IO_COST) , MAX(SP.TEMP_SPACE) , 0 , 0 , MAX(SP.PROJECTION) , MAX(SP.TIME) , MAX(SP.QBLOCK_NAME) , MAX(SP.REMARKS) , MAX(NEW_PLAN.SNAP_ID) FROM (SELECT /*+ index(spu) */ SPU.PLAN_HASH_VALUE , SPU.HASH_VALUE HASH_VALUE , SPU.ADDRESS ADDRESS , SPU.TEXT_SUBSET TEXT_SUBSET , SPU.SNAP_ID SNAP_ID FROM STATS$SQL_PLAN_USAGE SPU WHERE SPU.SNAP_ID = :B3 AND SPU.DBID = :B2 AND SPU.INSTANCE_NUMBER = :B1 AND NOT EXISTS (SELECT /*+ nl_aj */ * FROM STATS$SQL_PLAN SSP WHERE SSP.PLAN_HASH_VALUE = SPU.PLAN_HASH_VALUE ) ) NEW_PLAN , V$SQL S , V$SQL_PLAN SP WHERE S.ADDRESS = NEW_PLAN.ADDRESS AND S.PLAN_HASH_VALUE = NEW_PLAN.PLAN_HASH_VALUE AND S.HASH_VALUE = NEW_PLAN.HASH_VALUE AND SP.HASH_VALUE = NEW_PLAN.HASH_VALUE AND SP.ADDRESS = NEW_PLAN.ADDRESS AND SP.HASH_VALUE = S.HASH_VALUE AND SP.ADDRESS = S.ADDRESS AND SP.CHILD_NUMBER = S.CHILD_NUMBER GROUP BY NEW_PLAN.PLAN_HASH_VALUE, SP.ID ORDER BY NEW_PLAN.PLAN_HASH_VALUE, SP.ID
END OF STMT
PARSE #1307790776:c=0,e=2573,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,plh=0,tim=6209409700772
PARSE #1307780728:c=0,e=43,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,plh=3684871272,tim=6209409701524
EXEC #1307780728:c=0,e=49,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,plh=3684871272,tim=6209409701697
FETCH #1307780728:c=0,e=27,p=0,cr=2,cu=0,mis=0,r=1,dep=2,og=4,plh=3684871272,tim=6209409701975
CLOSE #1307780728:c=0,e=24,dep=2,type=3,tim=6209409702035
PARSE #1307780728:c=0,e=25,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,plh=3684871272,tim=6209409703617
EXEC #1307780728:c=0,e=29,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,plh=3684871272,tim=6209409703707
FETCH #1307780728:c=0,e=17,p=0,cr=2,cu=0,mis=0,r=1,dep=2,og=4,plh=3684871272,tim=6209409703759
CLOSE #1307780728:c=0,e=15,dep=2,type=3,tim=6209409703806

*** 2017-10-04 16:20:13.615
WAIT #1307790776: nam='library cache: mutex X' ela= 5673 idn=1814544732 value=2821793513472 where=125 obj#=-1 tim=6209824218315

*** 2017-10-04 16:20:15.505
WAIT #1307790776: nam='library cache: mutex X' ela= 4827 idn=1814544732 value=2821793513472 where=125 obj#=-1 tim=6209826108892

*** 2017-10-04 16:27:59.828
WAIT #1307790776: nam='library cache: mutex X' ela= 24809 idn=1814544732 value=1163936137216 where=132 obj#=-1 tim=6210290422318
WAIT #1307790776: nam='library cache: mutex X' ela= 7952 idn=1814544732 value=1163936137216 where=132 obj#=-1 tim=6210401985545

*** 2017-10-04 16:32:28.146
EXEC #1307790776:c=1090843750,e=1149039509,p=0,cr=1771,cu=0,mis=1,r=0,dep=1,og=1,plh=3104316024,tim=6210558740547
ERROR #1307790776:err=28 tim=6210558740631
STAT #1307790776 id=1 cnt=0 pid=0 pos=1 obj=0 op='LOAD TABLE CONVENTIONAL  STATS$SQL_PLAN (cr=0 pr=0 pw=0 time=45 us)'
STAT #1307790776 id=2 cnt=0 pid=1 pos=1 obj=0 op='SORT GROUP BY (cr=0 pr=0 pw=0 time=38 us cost=3 size=953 card=1)'
STAT #1307790776 id=3 cnt=136347 pid=2 pos=1 obj=0 op='NESTED LOOPS  (cr=1767 pr=0 pw=0 time=1033025234 us cost=2 size=953 card=1)'
STAT #1307790776 id=4 cnt=19310 pid=3 pos=1 obj=0 op='NESTED LOOPS  (cr=1767 pr=0 pw=0 time=377720 us cost=2 size=103 card=1)'
STAT #1307790776 id=5 cnt=1318 pid=4 pos=1 obj=0 op='VIEW  (cr=1767 pr=0 pw=0 time=30228 us cost=2 size=45 card=1)'
STAT #1307790776 id=6 cnt=1318 pid=5 pos=1 obj=0 op='NESTED LOOPS ANTI (cr=1767 pr=0 pw=0 time=29149 us cost=2 size=84 card=1)'
STAT #1307790776 id=7 cnt=1318 pid=6 pos=1 obj=217299 op='TABLE ACCESS BY INDEX ROWID STATS$SQL_PLAN_USAGE (cr=1146 pr=0 pw=0 time=14637 us cost=1 size=71 card=1)'
STAT #1307790776 id=8 cnt=1318 pid=7 pos=1 obj=217300 op='INDEX RANGE SCAN STATS$SQL_PLAN_USAGE_PK (cr=19 pr=0 pw=0 time=2297 us cost=1 size=0 card=1)'
STAT #1307790776 id=9 cnt=0 pid=6 pos=2 obj=217304 op='INDEX RANGE SCAN STATS$SQL_PLAN_PK (cr=621 pr=0 pw=0 time=8024 us cost=1 size=13 card=1)'
STAT #1307790776 id=10 cnt=19310 pid=4 pos=2 obj=0 op='FIXED TABLE FIXED INDEX X$KGLCURSOR_CHILD (ind:1) (cr=0 pr=0 pw=0 time=330931 us cost=0 size=58 card=1)'
STAT #1307790776 id=11 cnt=136347 pid=3 pos=2 obj=0 op='FIXED TABLE FIXED INDEX X$KQLFXPL (ind:3) (cr=0 pr=0 pw=0 time=1096336392 us cost=0 size=850 card=1)'
CLOSE #1307790776:c=0,e=154,dep=1,type=0,tim=6210558747617
EXEC #1307444904:c=1096656250,e=1158145582,p=168,cr=10398,cu=18727,mis=0,r=0,dep=0,og=1,plh=0,tim=6210558747951
ERROR #1307444904:err=28 tim=6210558747994
WAIT #1307444904: nam='db file sequential read' ela= 7372 file#=36 block#=9216 blocks=1 obj#=-1 tim=6210558790481
WAIT #1307444904: nam='db file sequential read' ela= 11759 file#=36 block#=15806 blocks=1 obj#=-1 tim=6210558857373
WAIT #1307444904: nam='db file sequential read' ela= 8383 file#=36 block#=17615 blocks=1 obj#=-1 tim=6210558866167
WAIT #1307444904: nam='db file sequential read' ela= 5643 file#=36 block#=3646 blocks=1 obj#=-1 tim=6210558886080
WAIT #1307444904: nam='db file sequential read' ela= 10050 file#=36 block#=2878 blocks=1 obj#=-1 tim=6210558898658
WAIT #1307444904: nam='log file sync' ela= 2845 buffer#=15827 sync scn=3387215352 p3=0 obj#=-1 tim=6210558903384


I believe there's a wait in file 36 right?

thanks a lot!
Re: Statspack snap shot at level 7 is slow [message #665956 is a reply to message #665955] Wed, 04 October 2017 09:14 Go to previous messageGo to next message
BlackSwan
Messages: 25673
Registered: January 2009
Location: SoCal
Senior Member
What is with ERRORs below?

ERROR #1307790776:err=28 tim=6210558740631
STAT #1307790776 id=1 cnt=0 pid=0 pos=1 obj=0 op='LOAD TABLE CONVENTIONAL STATS$SQL_PLAN (cr=0 pr=0 pw=0 time=45 us)'
STAT #1307790776 id=2 cnt=0 pid=1 pos=1 obj=0 op='SORT GROUP BY (cr=0 pr=0 pw=0 time=38 us cost=3 size=953 card=1)'
STAT #1307790776 id=3 cnt=136347 pid=2 pos=1 obj=0 op='NESTED LOOPS (cr=1767 pr=0 pw=0 time=1033025234 us cost=2 size=953 card=1)'
STAT #1307790776 id=4 cnt=19310 pid=3 pos=1 obj=0 op='NESTED LOOPS (cr=1767 pr=0 pw=0 time=377720 us cost=2 size=103 card=1)'
STAT #1307790776 id=5 cnt=1318 pid=4 pos=1 obj=0 op='VIEW (cr=1767 pr=0 pw=0 time=30228 us cost=2 size=45 card=1)'
STAT #1307790776 id=6 cnt=1318 pid=5 pos=1 obj=0 op='NESTED LOOPS ANTI (cr=1767 pr=0 pw=0 time=29149 us cost=2 size=84 card=1)'
STAT #1307790776 id=7 cnt=1318 pid=6 pos=1 obj=217299 op='TABLE ACCESS BY INDEX ROWID STATS$SQL_PLAN_USAGE (cr=1146 pr=0 pw=0 time=14637 us cost=1 size=71 card=1)'
STAT #1307790776 id=8 cnt=1318 pid=7 pos=1 obj=217300 op='INDEX RANGE SCAN STATS$SQL_PLAN_USAGE_PK (cr=19 pr=0 pw=0 time=2297 us cost=1 size=0 card=1)'
STAT #1307790776 id=9 cnt=0 pid=6 pos=2 obj=217304 op='INDEX RANGE SCAN STATS$SQL_PLAN_PK (cr=621 pr=0 pw=0 time=8024 us cost=1 size=13 card=1)'
STAT #1307790776 id=10 cnt=19310 pid=4 pos=2 obj=0 op='FIXED TABLE FIXED INDEX X$KGLCURSOR_CHILD (ind:1) (cr=0 pr=0 pw=0 time=330931 us cost=0 size=58 card=1)'
STAT #1307790776 id=11 cnt=136347 pid=3 pos=2 obj=0 op='FIXED TABLE FIXED INDEX X$KQLFXPL (ind:3) (cr=0 pr=0 pw=0 time=1096336392 us cost=0 size=850 card=1)'
CLOSE #1307790776:c=0,e=154,dep=1,type=0,tim=6210558747617
EXEC #1307444904:c=1096656250,e=1158145582,p=168,cr=10398,cu=18727,mis=0,r=0,dep=0,og=1,plh=0,tim=6210558747951
ERROR #1307444904:err=28 tim=6210558747994
Re: Statspack snap shot at level 7 is slow [message #665957 is a reply to message #665956] Wed, 04 October 2017 09:37 Go to previous messageGo to next message
juniordbanewbie
Messages: 169
Registered: April 2014
Senior Member
Dear Black Swan,

I do not understand your question:

What is with ERRORs below?

As I said before, I trace the procedure

BEGIN perfstat.statspack.snap(i_snap_level=>7); END;
,

since it is taking too long, I simply open another terminal and issue ALTER SYSTEM kill session command to kill the session.

that's why the trace log just simply stop when it is killed.
Re: Statspack snap shot at level 7 is slow [message #665976 is a reply to message #665957] Thu, 05 October 2017 04:59 Go to previous messageGo to next message
Flyby
Messages: 181
Registered: March 2011
Location: Belgium
Senior Member
Did you gather system statistics?
Re: Statspack snap shot at level 7 is slow [message #665977 is a reply to message #665976] Thu, 05 October 2017 05:19 Go to previous messageGo to next message
juniordbanewbie
Messages: 169
Registered: April 2014
Senior Member
No,

my manager told me to stop the daily stats collection.

i maybe a dba in the company, I'm powerless. but when thing don't work, then they start to listen.

[Updated on: Thu, 05 October 2017 06:25]

Report message to a moderator

Re: Statspack snap shot at level 7 is slow [message #665979 is a reply to message #665977] Thu, 05 October 2017 06:33 Go to previous messageGo to next message
John Watson
Messages: 7102
Registered: January 2010
Location: Global Village
Senior Member
Did you actually read my previous reply?

Do reiterate: have you completely removed and replaced statspack? Have you analyzed the perfstat schema?

You will also need dictionary and fixed objects stats, and (as FlyBy said) system stats. None of these are gathered by the daily autotask, so that is of no relevance.
Re: Statspack snap shot at level 7 is slow [message #665980 is a reply to message #665979] Thu, 05 October 2017 06:39 Go to previous messageGo to next message
Roachcoach
Messages: 1495
Registered: May 2010
Location: UK
Senior Member
Remember fixed object statistics need to be gathered under load, otherwise it is pointless, so don't do it "when it is quiet"
Re: Statspack snap shot at level 7 is slow [message #665991 is a reply to message #665979] Fri, 06 October 2017 01:20 Go to previous messageGo to next message
juniordbanewbie
Messages: 169
Registered: April 2014
Senior Member
Dear John,

I've execute spdrop, recreate perfstat again, but still it has problems inserting into the table. I did a snapshot level 5.


Top 5 Timed Events                                                    Avg %Total
~~~~~~~~~~~~~~~~~~                                                   wait   Call
Event                                            Waits    Time (s)   (ms)   Time
----------------------------------------- ------------ ----------- ------ ------
log file sync                                  325,953       1,733      5   58.2
log file parallel write                        225,463         578      3   19.4
CPU time                                                       576          19.3
direct path read                                38,126          64      2    2.2
db file sequential read                          1,202           8      7     .3

from https://blog.pythian.com/tuning-log-file-sync-event-waits/
how do I actually know

how do I know if it's a

Disk I/O
CPU exhaustion
OR memory issue?

at the same time I get

[code]
Session 168 is blocking 12 other sessio
[/oode]

will increasing the number of redo group help to resolve the issue?
thanks a lot!

Dear Roachcoach,

what do you mean by done under load,

should we schedule a job to run gather dictionary and fixed objects stats?

thanks a lot!
Re: Statspack snap shot at level 7 is slow [message #665994 is a reply to message #665991] Fri, 06 October 2017 02:21 Go to previous messageGo to next message
John Watson
Messages: 7102
Registered: January 2010
Location: Global Village
Senior Member
No-one can have any opinion from five lines of a statspack report. If you want advice based on that you need to post the whole thing.

Have you gathered statistics for the perfstat schema, the data dictionary, the fixed objects, and the system yet?
Re: Statspack snap shot at level 7 is slow [message #666011 is a reply to message #665994] Fri, 06 October 2017 10:08 Go to previous messageGo to next message
juniordbanewbie
Messages: 169
Registered: April 2014
Senior Member
Dear all,

best practices about gather stats

dictionary=>when to gather
can't find any in the documentation that said when to take it
http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_stats.htm#ARPLS68571


DBMS_STATS.GATHER_DICTIONARY_STATS


fixed objects stats=>done when load
=> how do we define by load
=>when there's a lot of upload?
=>a lot of transactions

DBMS_STATS.GATHER_FIXED_OBJECTS_STATS


https://docs.oracle.com/cd/E11882_01/server.112/e41573/stats.htm#PFGRF94740


The simplest way to gather statistics in these environments is to use a script or job scheduling tool to regularly run the GATHER_SCHEMA_STATS and GATHER_DATABASE_STATS procedures. The frequency of collection intervals should balance the task of providing accurate statistics for the optimizer against the processing overhead incurred by the statistics collection process.

If I run GATHER_DATABASE_STATS, do I still need to run GATHER_SCHEMA_STATS?
I guest if we run gather_schema_stats we can run in different session for different schema in parallel? That may make it faster instead of just running GATHER_DATABASE_STATS

Yes I've gather stats except DBMS_STATS.GATHER_FIXED_OBJECTS_STATS. I need to know when to gather this.

thanks in advance for your patience.

here's the last portion statspack report



STATSPACK report for

Database    DB Id    Instance     Inst Num  Startup Time   Release     RAC
~~~~~~~~ ----------- ------------ -------- --------------- ----------- ---
          4009822285 nowatzki               1 24-Jul-17 19:21 11.2.0.4.0  NO

Host Name             Platform                CPUs Cores Sockets   Memory (G)
~~~~ ---------------- ---------------------- ----- ----- ------- ------------
     shanghai        Microsoft Windows x86      8     8       2         16.0

Snapshot       Snap Id     Snap Time      Sessions Curs/Sess Comment
~~~~~~~~    ---------- ------------------ -------- --------- ------------------
Begin Snap:          303 06-Oct-17 12:35:27      202       4.0
  End Snap:          304 06-Oct-17 12:50:27      196       4.0
   Elapsed:      15.00 (mins) Av Act Sess:       2.8
   DB time:      42.72 (mins)      DB CPU:      12.92 (mins)

Cache Sizes            Begin        End
~~~~~~~~~~~       ---------- ----------
    Buffer Cache:     6,368M              Std Block Size:         8K
     Shared Pool:     3,296M                  Log Buffer:    24,952K

Load Profile              Per Second    Per Transaction    Per Exec    Per Call
~~~~~~~~~~~~      ------------------  ----------------- ----------- -----------
      DB time(s):                2.9                0.0        0.00        0.00
       DB CPU(s):                0.9                0.0        0.00        0.00
       Redo size:        1,220,436.6            1,163.4
   Logical reads:           54,944.9               52.4
   Block changes:            5,298.9                5.1
  Physical reads:               58.4                0.1
 Physical writes:               73.3                0.1
      User calls:            4,359.3                4.2
          Parses:            1,608.3                1.5
     Hard parses:                0.4                0.0
W/A MB processed:                2.7                0.0
          Logons:                0.1                0.0
        Executes:            1,658.7                1.6
       Rollbacks:              703.8                0.7
    Transactions:            1,049.0

Instance Efficiency Indicators
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
            Buffer Nowait %:   99.97       Redo NoWait %:  100.00
            Buffer  Hit   %:  100.00  Optimal W/A Exec %:  100.00
            Library Hit   %:   99.96        Soft Parse %:   99.97
         Execute to Parse %:    3.04         Latch Hit %:   99.97
Parse CPU to Parse Elapsd %:   88.12     % Non-Parse CPU:   96.06

 Shared Pool Statistics        Begin   End
                               ------  ------
             Memory Usage %:   79.97   79.97
    % SQL with executions>1:   71.98   71.94
  % Memory for SQL w/exec>1:   86.30   86.23

Top 5 Timed Events                                                    Avg %Total
~~~~~~~~~~~~~~~~~~                                                   wait   Call
Event                                            Waits    Time (s)   (ms)   Time
----------------------------------------- ------------ ----------- ------ ------
log file sync                                  325,953       1,733      5   58.2
log file parallel write                        225,463         578      3   19.4
CPU time                                                       576          19.3
direct path read                                38,126          64      2    2.2
db file sequential read                          1,202           8      7     .3
          -------------------------------------------------------------
Host CPU  (CPUs: 8  Cores: 8  Sockets: 2)
~~~~~~~~              Load Average
                      Begin     End      User  System    Idle     WIO     WCPU
                    ------- -------   ------- ------- ------- ------- --------
                                         9.98    3.38   86.64

Instance CPU
~~~~~~~~~~~~                                       % Time (seconds)
                                            -------- --------------
                     Host: Total time (s):                  7,193.9
                  Host: Busy CPU time (s):                    961.4
                   % of time Host is Busy:      13.4
             Instance: Total CPU time (s):                    811.3
          % of Busy CPU used for Instance:      84.4
        Instance: Total Database time (s):                  3,236.2
  %DB time waiting for CPU (Resource Mgr):       0.0

Memory Statistics                       Begin          End
~~~~~~~~~~~~~~~~~                ------------ ------------
                  Host Mem (MB):     16,383.5     16,383.5
                   SGA use (MB):     15,291.4     15,291.4
                   PGA use (MB):        661.2        655.1
    % Host Mem used for SGA+PGA:         97.4         97.3
          -------------------------------------------------------------

Time Model System Stats  DB/Inst: nowatzki/nowatzki  Snaps: 3-4
-> Ordered by % of DB time desc, Statistic name

Statistic                                       Time (s) % DB time
----------------------------------- -------------------- ---------
DB CPU                                             775.4      30.3
sql execute elapsed time                           451.8      17.6
parse time elapsed                                  31.0       1.2
sequence load elapsed time                           6.8        .3
PL/SQL execution elapsed time                        3.3        .1
hard parse elapsed time                              0.5        .0
connection management call elapsed                   0.4        .0
hard parse (sharing criteria) elaps                  0.3        .0
repeated bind elapsed time                           0.1        .0
failed parse elapsed time                            0.1        .0
PL/SQL compilation elapsed time                      0.0        .0
DB time                                          2,563.2
background elapsed time                            673.0
background cpu time                                 35.9
          -------------------------------------------------------------
Foreground Wait Events  DB/Inst: nowatzki/nowatzki  Snaps: 3-4
-> Only events with Total Wait Time (s) >= .001 are shown
-> ordered by Total Wait Time desc, Waits desc (idle events last)

                                                             Avg          %Total
                                          %Tim Total Wait   wait    Waits   Call
Event                               Waits  out   Time (s)   (ms)     /txn   Time
---------------------------- ------------ ---- ---------- ------ -------- ------
log file sync                     325,953    0      1,733      5      0.3   58.2
direct path read                   38,112    0         64      2      0.0    2.2
db file sequential read             1,201    0          8      7      0.0     .3
control file sequential read        1,022    0          2      2      0.0     .1
enq: SQ - contention                5,343    0          2      0      0.0     .1
buffer busy waits                  13,792    0          1      0      0.0     .0
SQL*Net more data to client        42,100    0          1      0      0.0     .0
library cache: mutex X              3,263    0          1      0      0.0     .0
enq: TX - index contention          2,947    0          1      0      0.0     .0
SQL*Net break/reset to clien        1,766    0          0      0      0.0     .0
log file switch completion              6    0          0     51      0.0     .0
undo segment extension                 22   95          0     13      0.0     .0
cursor: pin S                          80    0          0      3      0.0     .0
Disk file operations I/O               98    0          0      1      0.0     .0
db file parallel read                   9    0          0      7      0.0     .0
ADR block file read                    17    0          0      3      0.0     .0
latch: redo allocation                137    0          0      0      0.0     .0
latch free                            213    0          0      0      0.0     .0
direct path write                      18    0          0      1      0.0     .0
latch: cache buffers chains            96    0          0      0      0.0     .0
buffer deadlock                       557  100          0      0      0.0     .0
latch: shared pool                      2    0          0      5      0.0     .0
ADR block file write                    5    0          0      1      0.0     .0
latch: messages                         7    0          0      0      0.0     .0
latch: enqueue hash chains             24    0          0      0      0.0     .0
enq: HW - contention                    4    0          0      0      0.0     .0
SQL*Net message from client     2,867,843    0    148,598     52      3.0
Streams AQ: waiting for mess          371  100      1,801   4853      0.0
jobq slave wait                     2,101   99      1,063    506      0.0
wait for unread message on b          903   99        900    996      0.0
SQL*Net message to client       2,867,779    0          4      0      3.0
SQL*Net more data from clien       24,918    0          1      0      0.0
          -------------------------------------------------------------

Background Wait Events  DB/Inst: nowatzki/nowatzki  Snaps: 3-4
-> Only events with Total Wait Time (s) >= .001 are shown
-> ordered by Total Wait Time desc, Waits desc (idle events last)

                                                             Avg          %Total
                                          %Tim Total Wait   wait    Waits   Call
Event                               Waits  out   Time (s)   (ms)     /txn   Time
---------------------------- ------------ ---- ---------- ------ -------- ------
log file parallel write           225,463    0        578      3      0.2   19.4
db file parallel write              3,327    0          4      1      0.0     .1
enq: CR - block range reuse           299    0          3      9      0.0     .1
log file sequential read              246    0          2      8      0.0     .1
control file sequential read        1,115    0          1      1      0.0     .0
control file parallel write           352    0          0      1      0.0     .0
os thread startup                      32    0          0      9      0.0     .0
LGWR wait for redo copy             4,199    0          0      0      0.0     .0
db file async I/O submit              600    0          0      0      0.0     .0
Disk file operations I/O              134    0          0      1      0.0     .0
reliable message                      417    0          0      0      0.0     .0
direct path read                       14    0          0      4      0.0     .0
db file sequential read                 1    0          0      6      0.0     .0
latch: redo allocation                 17    0          0      0      0.0     .0
direct path write                       2    0          0      1      0.0     .0
log file single write                   2    0          0      1      0.0     .0
rdbms ipc message                 188,008    2     12,807     68      0.2
DIAG idle wait                      1,788  100      1,799   1006      0.0
Space Manager: slave idle wa          351   98      1,743   4966      0.0
pmon timer                            300  100        901   3005      0.0
shared server idle wait                30  100        900  30016      0.0
dispatcher timer                       15  100        900  60016      0.0
Streams AQ: deallocate messa          448  100        899   2006      0.0
Streams AQ: qmn slave idle w           32    0        896  28016      0.0
Streams AQ: qmn coordinator            65   49        896  13792      0.0
smon timer                             30    3        738  24603      0.0
SQL*Net message from client            87    0          0      2      0.0
          -------------------------------------------------------------

Wait Events (fg and bg) DB/Inst: nowatzki/nowatzki  Snaps: 3-4
-> s - second, cs - centisecond,  ms - millisecond, us - microsecond
-> %Timeouts:  value of 0 indicates value was < .5%.  Value of null is truly 0
-> Only events with Total Wait Time (s) >= .001 are shown
-> ordered by Total Wait Time desc, Waits desc (idle events last)

                                                             Avg          %Total
                                          %Tim Total Wait   wait    Waits   Call
Event                               Waits  out   Time (s)   (ms)     /txn   Time
---------------------------- ------------ ---- ---------- ------ -------- ------
log file sync                     325,953    0      1,733      5      0.3   58.2
log file parallel write           225,463    0        578      3      0.2   19.4
direct path read                   38,126    0         64      2      0.0    2.2
db file sequential read             1,202    0          8      7      0.0     .3
db file parallel write              3,327    0          4      1      0.0     .1
control file sequential read        2,137    0          3      1      0.0     .1
enq: CR - block range reuse           299    0          3      9      0.0     .1
log file sequential read              246    0          2      8      0.0     .1
enq: SQ - contention                5,343    0          2      0      0.0     .1
buffer busy waits                  13,799    0          1      0      0.0     .0
SQL*Net more data to client        42,100    0          1      0      0.0     .0
library cache: mutex X              3,263    0          1      0      0.0     .0
enq: TX - index contention          2,947    0          1      0      0.0     .0
control file parallel write           352    0          0      1      0.0     .0
SQL*Net break/reset to clien        1,766    0          0      0      0.0     .0
log file switch completion              6    0          0     51      0.0     .0
undo segment extension                 22   95          0     13      0.0     .0
os thread startup                      32    0          0      9      0.0     .0
Disk file operations I/O              232    0          0      1      0.0     .0
LGWR wait for redo copy             4,199    0          0      0      0.0     .0
cursor: pin S                          80    0          0      3      0.0     .0
db file async I/O submit              600    0          0      0      0.0     .0
reliable message                      417    0          0      0      0.0     .0
db file parallel read                   9    0          0      7      0.0     .0
ADR block file read                    17    0          0      3      0.0     .0
latch: redo allocation                154    0          0      0      0.0     .0
latch free                            216    0          0      0      0.0     .0
direct path write                      20    0          0      1      0.0     .0
latch: cache buffers chains            97    0          0      0      0.0     .0
buffer deadlock                       557  100          0      0      0.0     .0
latch: shared pool                      2    0          0      5      0.0     .0
ADR block file write                    5    0          0      1      0.0     .0
latch: messages                         8    0          0      0      0.0     .0
latch: enqueue hash chains             24    0          0      0      0.0     .0
enq: HW - contention                    4    0          0      0      0.0     .0
log file single write                   2    0          0      1      0.0     .0
SQL*Net message from client     2,867,930    0    148,598     52      3.0
rdbms ipc message                 188,008    2     12,807     68      0.2
Streams AQ: waiting for mess          371  100      1,801   4853      0.0
DIAG idle wait                      1,788  100      1,799   1006      0.0
Space Manager: slave idle wa          351   98      1,743   4966      0.0
jobq slave wait                     2,101   99      1,063    506      0.0
pmon timer                            300  100        901   3005      0.0
shared server idle wait                30  100        900  30016      0.0
dispatcher timer                       15  100        900  60016      0.0
wait for unread message on b          903   99        900    996      0.0
Streams AQ: deallocate messa          448  100        899   2006      0.0
Streams AQ: qmn slave idle w           32    0        896  28016      0.0
Streams AQ: qmn coordinator            65   49        896  13792      0.0

Wait Events (fg and bg) DB/Inst: nowatzki/nowatzki  Snaps: 3-4
-> s - second, cs - centisecond,  ms - millisecond, us - microsecond
-> %Timeouts:  value of 0 indicates value was < .5%.  Value of null is truly 0
-> Only events with Total Wait Time (s) >= .001 are shown
-> ordered by Total Wait Time desc, Waits desc (idle events last)

                                                             Avg          %Total
                                          %Tim Total Wait   wait    Waits   Call
Event                               Waits  out   Time (s)   (ms)     /txn   Time
---------------------------- ------------ ---- ---------- ------ -------- ------
smon timer                             30    3        738  24603      0.0
SQL*Net message to client       2,867,846    0          4      0      3.0
SQL*Net more data from clien       24,918    0          1      0      0.0
          -------------------------------------------------------------
Wait Event Histogram  DB/Inst: nowatzki/nowatzki  Snaps: 3-4
-> Total Waits - units: K is 1000, M is 1000000, G is 1000000000
-> % of Waits - column heading: <=1s is truly <1024ms, >1s is truly >=1024ms
-> % of Waits - value: .0 indicates value was <.05%, null is truly 0
-> Ordered by Event (idle events last)

                           Total ----------------- % of Waits ------------------
Event                      Waits  <1ms  <2ms  <4ms  <8ms <16ms <32ms  <=1s   >1s
-------------------------- ----- ----- ----- ----- ----- ----- ----- ----- -----
ADR block file read          17   41.2   5.9  11.8  41.2
ADR block file write          5  100.0
ADR file lock                 6  100.0
ARCH wait for archivelog l    1  100.0
Disk file operations I/O    232   92.7   3.0         2.6    .9    .4    .4
LGWR wait for redo copy    4199  100.0
Log archive I/O               1  100.0
SQL*Net break/reset to cli 1766   98.4   1.0    .5          .1
SQL*Net more data to clien   42K 100.0    .0
asynch descriptor resize     52  100.0
buffer busy waits            13K 100.0    .0          .0    .0
buffer deadlock             557   99.8    .2
control file parallel writ  352   58.2  38.1   2.3    .9    .6
control file sequential re 2137   87.8   3.3   1.2   5.1   2.0    .6    .1
cursor: pin S                80   62.5   2.5   6.3  12.5  16.3
db file async I/O submit    600   93.0   5.8   1.0          .2
db file parallel read         9                     77.8  22.2
db file parallel write     3327   79.2   7.5   4.7   7.8    .6    .1    .1
db file sequential read    1202    4.6    .9  14.1  68.2  10.6    .8    .7
db file single write          1  100.0
direct path read             38K  78.7   9.7   5.3   4.7   1.2    .1    .4
direct path write            20   85.0  15.0
enq: CR - block range reus  299    1.7   2.0    .7  41.1  53.2   1.0    .3
enq: HW - contention          4  100.0
enq: SQ - contention       5343   99.9    .0                            .0
enq: TX - index contention 2947   99.4    .3          .1          .2
latch free                  216  100.0
latch: cache buffers chain   97   99.0               1.0
latch: enqueue hash chains   24  100.0
latch: messages               8   75.0  25.0
latch: redo allocation      154   96.8   1.9   1.3
latch: redo writing           1  100.0
latch: session allocation     6  100.0
latch: shared pool            2               50.0  50.0
library cache: mutex X     3263   96.4    .3    .5   1.0   1.7
log file parallel write     225K  78.7   7.2   1.8    .4   7.8   4.1    .0
log file sequential read    246   19.9  12.6  23.6  22.4  15.4   4.5   1.6
log file single write         2  100.0
log file switch completion    6                                      100.0
log file sync               325K  47.0  18.5   2.9   1.8  20.0   9.6    .2
os thread startup            32                     40.6  59.4
reliable message            417   99.0    .5    .5
undo segment extension       22   54.5               4.5   9.1  18.2  13.6
DIAG idle wait             1788                                      100.0
SQL*Net message from clien 2867K  88.6   6.0   2.4    .8    .5    .3   1.3    .2
SQL*Net message to client  2867K 100.0
SQL*Net more data from cli   24K  99.8    .1    .1    .0
Space Manager: slave idle   351                                            100.0
Streams AQ: deallocate mes  448                                            100.0
Streams AQ: qmn coordinato   65   49.2   1.5                                49.2
Streams AQ: qmn slave idle   32                                            100.0
Wait Event Histogram  DB/Inst: nowatzki/nowatzki  Snaps: 3-4
-> Total Waits - units: K is 1000, M is 1000000, G is 1000000000
-> % of Waits - column heading: <=1s is truly <1024ms, >1s is truly >=1024ms
-> % of Waits - value: .0 indicates value was <.05%, null is truly 0
-> Ordered by Event (idle events last)

                           Total ----------------- % of Waits ------------------
Event                      Waits  <1ms  <2ms  <4ms  <8ms <16ms <32ms  <=1s   >1s
-------------------------- ----- ----- ----- ----- ----- ----- ----- ----- -----
Streams AQ: waiting for me  371                            3.0              97.0
class slave wait              4  100.0
dispatcher timer             15                                            100.0
jobq slave wait            2101                                   .1  99.9
pmon timer                  300                                            100.0
rdbms ipc message           188K  50.6  16.8  24.9   4.0   1.2    .4    .7   1.4
shared server idle wait      30                                            100.0
smon timer                   30                                       33.3  66.7
wait for unread message on  903                             .7    .1  99.2
          -------------------------------------------------------------

SQL ordered by CPU  DB/Inst: nowatzki/nowatzki  Snaps: 3-4
-> Total DB CPU (s):             775
-> Captured SQL accounts for   62.8% of Total DB CPU
-> SQL reported below exceeded  1.0% of Total DB CPU

    CPU                  CPU per             Elapsd                     Old
  Time (s)   Executions  Exec (s)  %Total   Time (s)    Buffer Gets  Hash Value
---------- ------------ ---------- ------ ---------- --------------- ----------
    116.77        1,329       0.09   15.1     120.03      32,168,016 3636508337
Module: ghost.exe
SELECT * FROM TABLE(space_online.select_ckc_candidates())

     94.39           89       1.06   12.2     452.83      11,055,209 2353567088
Module: DBMS_SCHEDULER
DELETE FROM STATS$SNAPSHOT WHERE DBID = :B3 AND INSTANCE_NUMBER
= :B2 AND BASELINE IS NULL AND SNAP_TIME < :B1 RETURNING COUNT(1
) INTO :O0

     79.09      290,815       0.00   10.2      85.39       4,957,059 3329658333
Module: JDBC Thin Client
INSERT INTO T_MEAS_DATA (SEQ, MEAS_ID, MEAS_CONTEXT, VALUE, CREA
TE_TIME, DELETED, RAW_EXVAL_00, RAW_EXVAL_01, RAW_EXVAL_02, RAW_
EXVAL_03, RAW_EXVAL_04, RAW_EXVAL_05, RAW_EXVAL_06, RAW_EXVAL_07
, RAW_EXVAL_08, RAW_EXVAL_09, RAW_EXVAL_10, RAW_EXVAL_11, RAW_EX

     56.22          577       0.10    7.2      58.06      15,692,097 3777041917
Module: ghost.exe
SELECT /*+NO_MERGE(compact)*/ ch_id, ckc_id, id1 param_idx, chan
nel_type FROM t_id_pairs, (SELECT r.ch_id, r.ckc_id, e.id2 max_i
dx, r.channel_type, r.parameter_name FROM check_runtime r, t_cha
nnel_def, (SELECT DISTINCT parameter_name, exval_00, exval_01, e

     52.92          576       0.09    6.8      53.97      15,633,648 1207080377
Module: ghost.exe
SELECT /*+NO_MERGE(compact)*/ ch_id, ckc_id, id1 param_idx, chan
nel_type FROM t_id_pairs, (SELECT r.ch_id, r.ckc_id, e.id2 max_i
dx, r.channel_type, r.parameter_name FROM check_runtime r, t_cha
nnel_def, (SELECT DISTINCT parameter_name, exval_00, exval_01, e

     22.38      328,434       0.00    2.9      25.40         656,666 1333943659
Module: JDBC Thin Client
select * from dual

     15.81      242,271       0.00    2.0      17.74         756,896  582192567
Module: JDBC Thin Client
select * from t_a_web_key_choice where group_id=:1  and key_no=:
2  and parameter_name=:3  order by choice_no

     14.91      242,408       0.00    1.9      16.68         969,994  889279068
Module: JDBC Thin Client
select * from t_a_web_key_param where group_id=:1  and key_no=:2


     14.77       11,844       0.00    1.9      15.25       1,724,168  297059384
Module: ghost.exe
UPDATE check_runtime                  SET ckc_blob =  :BP_00002_
v:BP_00002_i, first_sample_date =  :BP_00003_v:BP_00003_i, last_
sample_date =  :BP_00004_v:BP_00004_i, sample_num =  :BP_00005_v
:BP_00005_i, violation_num =  :BP_00006_v:BP_00006_i, to_be_comm

SQL ordered by CPU  DB/Inst: nowatzki/nowatzki  Snaps: 3-4
-> Total DB CPU (s):             775
-> Captured SQL accounts for   62.8% of Total DB CPU
-> SQL reported below exceeded  1.0% of Total DB CPU

    CPU                  CPU per             Elapsd                     Old
  Time (s)   Executions  Exec (s)  %Total   Time (s)    Buffer Gets  Hash Value
---------- ------------ ---------- ------ ---------- --------------- ----------
     10.55       10,788       0.00    1.4      10.98         499,846 1983055036
Module: JDBC Thin Client
DELETE FROM T_MEAS_DATA WHERE MEAS_CONTEXT = :1

      9.48           12       0.79    1.2      18.62         473,460  256387292
Module: DBMS_SCHEDULER
DELETE FROM STATS$SQLTEXT ST1 WHERE (ST1.OLD_HASH_VALUE, ST1.TEX
T_SUBSET) IN (SELECT /*+ index_ffs(st) */ ST.OLD_HASH_VALUE, ST.
TEXT_SUBSET FROM STATS$SQLTEXT ST WHERE (OLD_HASH_VALUE, TEXT_SU
BSET) NOT IN (SELECT /*+ hash_aj index_ffs(ss) */ DISTINCT OLD_H

      8.36            3       2.79    1.1       8.59         551,643 4166403641
Module: ghost.exe
SELECT v_ckc_prerun.ch_id, v_ckc_prerun.ckc_id, v_ckc_prerun.pre
run_length, v_ckc_prerun.prerun_start, v_ckc_prerun.send_prerun_
mail, v_ckc_prerun.chart_design_id, v_ckc_prerun.mail_addr, v_ck
c_prerun.nav_path2, v_ckc_prerun.limit_enable, v_ckc_prerun.prer

          -------------------------------------------------------------
SQL ordered by Elapsed time for DB: nowatzki  Instance: nowatzki  Snaps: 3 -4
-> Total DB Time (s):           2,563
-> Captured SQL accounts for   21.9% of Total DB Time
-> SQL reported below exceeded  1.0% of Total DB Time

  Elapsed                Elap per            CPU                        Old
  Time (s)   Executions  Exec (s)  %Total   Time (s)  Physical Reads Hash Value
---------- ------------ ---------- ------ ---------- --------------- ----------
    452.83           89       5.09   17.7      94.39          53,080 2353567088
Module: DBMS_SCHEDULER
DELETE FROM STATS$SNAPSHOT WHERE DBID = :B3 AND INSTANCE_NUMBER
= :B2 AND BASELINE IS NULL AND SNAP_TIME < :B1 RETURNING COUNT(1
) INTO :O0

    120.03        1,329       0.09    4.7     116.77               1 3636508337
Module: ghost.exe
SELECT * FROM TABLE(space_online.select_ckc_candidates())

     85.39      290,815       0.00    3.3      79.09               4 3329658333
Module: JDBC Thin Client
INSERT INTO T_MEAS_DATA (SEQ, MEAS_ID, MEAS_CONTEXT, VALUE, CREA
TE_TIME, DELETED, RAW_EXVAL_00, RAW_EXVAL_01, RAW_EXVAL_02, RAW_
EXVAL_03, RAW_EXVAL_04, RAW_EXVAL_05, RAW_EXVAL_06, RAW_EXVAL_07
, RAW_EXVAL_08, RAW_EXVAL_09, RAW_EXVAL_10, RAW_EXVAL_11, RAW_EX

     58.06          577       0.10    2.3      56.22               0 3777041917
Module: ghost.exe
SELECT /*+NO_MERGE(compact)*/ ch_id, ckc_id, id1 param_idx, chan
nel_type FROM t_id_pairs, (SELECT r.ch_id, r.ckc_id, e.id2 max_i
dx, r.channel_type, r.parameter_name FROM check_runtime r, t_cha
nnel_def, (SELECT DISTINCT parameter_name, exval_00, exval_01, e

     53.97          576       0.09    2.1      52.92               0 1207080377
Module: ghost.exe
SELECT /*+NO_MERGE(compact)*/ ch_id, ckc_id, id1 param_idx, chan
nel_type FROM t_id_pairs, (SELECT r.ch_id, r.ckc_id, e.id2 max_i
dx, r.channel_type, r.parameter_name FROM check_runtime r, t_cha
nnel_def, (SELECT DISTINCT parameter_name, exval_00, exval_01, e

     38.13       25,380       0.00    1.5       7.75          25,380  457813625
Module: JDBC Thin Client
SELECT chart_design_id, ss.chart_design_name, ss.chart_design_de
scr, ss.min_sample_size, ss.normal_sample_size, ss.max_sample_si
ze, ss.ewma_factor, ss.ewma_mv_center_est, ss.ewma_mv_alpha, ss.
ewma_s_factor, ss.ewma_s_center_est, ss.ewma_s_k, ss.ewma_r_fact

          -------------------------------------------------------------
SQL ordered by Gets  DB/Inst: nowatzki/nowatzki  Snaps: 3-4
-> End Buffer Gets Threshold:     10000 Total Buffer Gets:      49,450,367
-> Captured SQL accounts for  163.0% of Total Buffer Gets
-> SQL reported below exceeded  1.0% of Total Buffer Gets

                                                     CPU      Elapsd     Old
  Buffer Gets    Executions  Gets per Exec  %Total Time (s)  Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
     32,168,016        1,329       24,204.7   65.1   116.77    120.03 3636508337
Module: ghost.exe
SELECT * FROM TABLE(space_online.select_ckc_candidates())

     15,692,097          577       27,196.0   31.8    56.22     58.06 3777041917
Module: ghost.exe
SELECT /*+NO_MERGE(compact)*/ ch_id, ckc_id, id1 param_idx, chan
nel_type FROM t_id_pairs, (SELECT r.ch_id, r.ckc_id, e.id2 max_i
dx, r.channel_type, r.parameter_name FROM check_runtime r, t_cha
nnel_def, (SELECT DISTINCT parameter_name, exval_00, exval_01, e

     15,633,648          576       27,141.8   31.6    52.92     53.97 1207080377
Module: ghost.exe
SELECT /*+NO_MERGE(compact)*/ ch_id, ckc_id, id1 param_idx, chan
nel_type FROM t_id_pairs, (SELECT r.ch_id, r.ckc_id, e.id2 max_i
dx, r.channel_type, r.parameter_name FROM check_runtime r, t_cha
nnel_def, (SELECT DISTINCT parameter_name, exval_00, exval_01, e

     11,055,209           89      124,215.8   22.4    94.39    452.83 2353567088
Module: DBMS_SCHEDULER
DELETE FROM STATS$SNAPSHOT WHERE DBID = :B3 AND INSTANCE_NUMBER
= :B2 AND BASELINE IS NULL AND SNAP_TIME < :B1 RETURNING COUNT(1
) INTO :O0

      4,957,059      290,815           17.0   10.0    79.09     85.39 3329658333
Module: JDBC Thin Client
INSERT INTO T_MEAS_DATA (SEQ, MEAS_ID, MEAS_CONTEXT, VALUE, CREA
TE_TIME, DELETED, RAW_EXVAL_00, RAW_EXVAL_01, RAW_EXVAL_02, RAW_
EXVAL_03, RAW_EXVAL_04, RAW_EXVAL_05, RAW_EXVAL_06, RAW_EXVAL_07
, RAW_EXVAL_08, RAW_EXVAL_09, RAW_EXVAL_10, RAW_EXVAL_11, RAW_EX

      1,724,168       11,844          145.6    3.5    14.77     15.25  297059384
Module: ghost.exe
UPDATE check_runtime                  SET ckc_blob =  :BP_00002_
v:BP_00002_i, first_sample_date =  :BP_00003_v:BP_00003_i, last_
sample_date =  :BP_00004_v:BP_00004_i, sample_num =  :BP_00005_v
:BP_00005_i, violation_num =  :BP_00006_v:BP_00006_i, to_be_comm

        969,994      242,408            4.0    2.0    14.91     16.68  889279068
Module: JDBC Thin Client
select * from t_a_web_key_param where group_id=:1  and key_no=:2


        866,450          215        4,030.0    1.8     6.34      6.49 1722646929
Module: JDBC Thin Client
select BATCH_ID, EXKEY_3, EXKEY_5, EXKEY_9, EXKEY_10, EXKEY_13,
EXKEY_14, EXKEY_15, EXKEY_16, EXKEY_17, EXKEY_18, EXKEY_19,
   DATKEY_0, DATKEY_1, DATKEY_2, DATKEY_3, DATKEY_4, DATKEY_5, D
ATKEY_6, DATKEY_7, DATKEY_8, DATKEY_9, DATKEY_10,    DATKEY_11,

        756,896      242,271            3.1    1.5    15.81     17.74  582192567
Module: JDBC Thin Client
SQL ordered by Gets  DB/Inst: nowatzki/nowatzki  Snaps: 3-4
-> End Buffer Gets Threshold:     10000 Total Buffer Gets:      49,450,367
-> Captured SQL accounts for  163.0% of Total Buffer Gets
-> SQL reported below exceeded  1.0% of Total Buffer Gets

                                                     CPU      Elapsd     Old
  Buffer Gets    Executions  Gets per Exec  %Total Time (s)  Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
select * from t_a_web_key_choice where group_id=:1  and key_no=:
2  and parameter_name=:3  order by choice_no

        656,666      328,434            2.0    1.3    22.38     25.40 1333943659
Module: JDBC Thin Client
select * from dual

        551,643            3      183,881.0    1.1     8.36      8.59 4166403641
Module: ghost.exe
SELECT v_ckc_prerun.ch_id, v_ckc_prerun.ckc_id, v_ckc_prerun.pre
run_length, v_ckc_prerun.prerun_start, v_ckc_prerun.send_prerun_
mail, v_ckc_prerun.chart_design_id, v_ckc_prerun.mail_addr, v_ck
c_prerun.nav_path2, v_ckc_prerun.limit_enable, v_ckc_prerun.prer

        499,846       10,788           46.3    1.0    10.55     10.98 1983055036
Module: JDBC Thin Client
DELETE FROM T_MEAS_DATA WHERE MEAS_CONTEXT = :1

          -------------------------------------------------------------
SQL ordered by Reads  DB/Inst: nowatzki/nowatzki  Snaps: 3-4
-> End Disk Reads Threshold:      1000  Total Disk Reads:          52,562
-> Captured SQL accounts for   57.4% of Total Disk Reads
-> SQL reported below exceeded  1.0% of Total Disk Reads

                                                     CPU      Elapsd
  Physical Rds   Executions  Rds per Exec   %Total Time (s)  Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
         53,080           89          596.4  101.0    94.39    452.83 2353567088
Module: DBMS_SCHEDULER
DELETE FROM STATS$SNAPSHOT WHERE DBID = :B3 AND INSTANCE_NUMBER
= :B2 AND BASELINE IS NULL AND SNAP_TIME < :B1 RETURNING COUNT(1
) INTO :O0

         25,380       25,380            1.0   48.3     7.75     38.13  457813625
Module: JDBC Thin Client
SELECT chart_design_id, ss.chart_design_name, ss.chart_design_de
scr, ss.min_sample_size, ss.normal_sample_size, ss.max_sample_si
ze, ss.ewma_factor, ss.ewma_mv_center_est, ss.ewma_mv_alpha, ss.
ewma_s_factor, ss.ewma_s_center_est, ss.ewma_s_k, ss.ewma_r_fact

          3,672        3,672            1.0    7.0     2.08      8.68 3717167260
Module: JDBC Thin Client
SELECT * FROM v_stargate_chart WHERE ch_id = :1  AND ckc_id = :2


          1,607           12          133.9    3.1     9.48     18.62  256387292
Module: DBMS_SCHEDULER
DELETE FROM STATS$SQLTEXT ST1 WHERE (ST1.OLD_HASH_VALUE, ST1.TEX
T_SUBSET) IN (SELECT /*+ index_ffs(st) */ ST.OLD_HASH_VALUE, ST.
TEXT_SUBSET FROM STATS$SQLTEXT ST WHERE (OLD_HASH_VALUE, TEXT_SU
BSET) NOT IN (SELECT /*+ hash_aj index_ffs(ss) */ DISTINCT OLD_H

          -------------------------------------------------------------
SQL ordered by Executions  DB/Inst: nowatzki/nowatzki  Snaps: 3-4
-> End Executions Threshold:       100  Total Executions:       1,492,798
-> Captured SQL accounts for   99.8% of Total Executions
-> SQL reported below exceeded  1.0% of Total Executions

                                                CPU per    Elap per     Old
 Executions   Rows Processed   Rows per Exec    Exec (s)   Exec (s)  Hash Value
------------ --------------- ---------------- ----------- ---------- ----------
     328,434         328,377              1.0       0.00        0.00  1333943659
Module: JDBC Thin Client
select * from dual

     290,815         290,796              1.0       0.00        0.00  3329658333
Module: JDBC Thin Client
INSERT INTO T_MEAS_DATA (SEQ, MEAS_ID, MEAS_CONTEXT, VALUE, CREA
TE_TIME, DELETED, RAW_EXVAL_00, RAW_EXVAL_01, RAW_EXVAL_02, RAW_
EXVAL_03, RAW_EXVAL_04, RAW_EXVAL_05, RAW_EXVAL_06, RAW_EXVAL_07
, RAW_EXVAL_08, RAW_EXVAL_09, RAW_EXVAL_10, RAW_EXVAL_11, RAW_EX

     242,408         242,407              1.0       0.00        0.00   889279068
Module: JDBC Thin Client
select * from t_a_web_key_param where group_id=:1  and key_no=:2


     242,271          41,761              0.2       0.00        0.00   582192567
Module: JDBC Thin Client
select * from t_a_web_key_choice where group_id=:1  and key_no=:
2  and parameter_name=:3  order by choice_no

      29,268          48,684              1.7       0.00        0.00  2107068777
Module: JDBC Thin Client
select change_id from t_changes where object_type = 1 and object
_id = :1  and sub_object_id = :2  and revision_id = :3

      25,380          25,380              1.0       0.00        0.00   457813625
Module: JDBC Thin Client
SELECT chart_design_id, ss.chart_design_name, ss.chart_design_de
scr, ss.min_sample_size, ss.normal_sample_size, ss.max_sample_si
ze, ss.ewma_factor, ss.ewma_mv_center_est, ss.ewma_mv_alpha, ss.
ewma_s_factor, ss.ewma_s_center_est, ss.ewma_s_k, ss.ewma_r_fact

      24,480             438              0.0       0.00        0.00  3309130212
Module: JDBC Thin Client
select ckc_id, val_type_id, viol_comment_flag, prio_violation, v
iol_comment from t_ext_samples_viol where sample_id = :1  and ch
_id = :2  and ckc_id = :3

      24,396             450              0.0       0.00        0.00  3843924863
Module: JDBC Thin Client
select ckc_id, val_type_id, viol_comment_flag, prio_violation, v
iol_comment from t_ext_samples_viol where sample_id = :1  and ch
_id = :2

          -------------------------------------------------------------
SQL ordered by Parse Calls  DB/Inst: nowatzki/nowatzki  Snaps: 3-4
-> End Parse Calls Threshold:      1000 Total Parse Calls:       1,447,451
-> Captured SQL accounts for   99.8% of Total Parse Calls
-> SQL reported below exceeded  1.0% of Total Parse Calls

                           % Total    Old
 Parse Calls   Executions   Parses Hash Value
------------ ------------ -------- ----------
     328,418      328,434    22.69 1333943659
Module: JDBC Thin Client
select * from dual

     290,796      290,815    20.09 3329658333
Module: JDBC Thin Client
INSERT INTO T_MEAS_DATA (SEQ, MEAS_ID, MEAS_CONTEXT, VALUE, CREA
TE_TIME, DELETED, RAW_EXVAL_00, RAW_EXVAL_01, RAW_EXVAL_02, RAW_
EXVAL_03, RAW_EXVAL_04, RAW_EXVAL_05, RAW_EXVAL_06, RAW_EXVAL_07
, RAW_EXVAL_08, RAW_EXVAL_09, RAW_EXVAL_10, RAW_EXVAL_11, RAW_EX

     242,403      242,408    16.75  889279068
Module: JDBC Thin Client
select * from t_a_web_key_param where group_id=:1  and key_no=:2


     242,267      242,271    16.74  582192567
Module: JDBC Thin Client
select * from t_a_web_key_choice where group_id=:1  and key_no=:
2  and parameter_name=:3  order by choice_no

      29,268       29,268     2.02 2107068777
Module: JDBC Thin Client
select change_id from t_changes where object_type = 1 and object
_id = :1  and sub_object_id = :2  and revision_id = :3

      25,380       25,380     1.75  457813625
Module: JDBC Thin Client
SELECT chart_design_id, ss.chart_design_name, ss.chart_design_de
scr, ss.min_sample_size, ss.normal_sample_size, ss.max_sample_si
ze, ss.ewma_factor, ss.ewma_mv_center_est, ss.ewma_mv_alpha, ss.
ewma_s_factor, ss.ewma_s_center_est, ss.ewma_s_k, ss.ewma_r_fact

      24,480       24,480     1.69 3309130212
Module: JDBC Thin Client
select ckc_id, val_type_id, viol_comment_flag, prio_violation, v
iol_comment from t_ext_samples_viol where sample_id = :1  and ch
_id = :2  and ckc_id = :3

      24,396       24,396     1.69 3843924863
Module: JDBC Thin Client
select ckc_id, val_type_id, viol_comment_flag, prio_violation, v
iol_comment from t_ext_samples_viol where sample_id = :1  and ch
_id = :2

          -------------------------------------------------------------
SQL ordered by Sharable Memory  DB/Inst: nowatzki/nowatzki  Snaps: 3-4
-> End Sharable Memory Threshold(KB):      1024

    Max         End
 Sharable    Sharable                                        Old
Memory (KB) Memory (KB)  Parse Calls  Executions  % Total Hash Value
----------- ----------- ------------ ------------ ------- ----------
    845,734     845,734            0            0    25.1 1284510864
Module: ghost.exe
SELECT v_ext_samples_v.sample_id, v_ext_samples_v.sample_date, v
_ext_samples_v.created, v_ext_samples_v.viol_count, v_ext_sample
s_v.spc_com_extern, v_ext_samples_v.spc_flag_extern, v_ext_sampl
es_v.spc_com_intern, v_ext_samples_v.spc_flag_intern, v_ext_samp

    131,219     131,219            0            0     3.9 1132933973
Module: ghost.exe
SELECT sample_id, sample_date, nvl(val_type_id, 0), nvl(ca_id_se
lected, 0), nvl(viol_comment_flag, 'N'), nvl(viol_comment, ''),
nvl(viol_comment_date, tmp_date) FROM t_id_tmp2, (SELECT sample_
id, sample_date, val_type_id, ca_id_selected, viol_comment_flag,

     36,937      36,937            9            9     1.1 1315002880
Module: ghost.exe
SELECT sample_id, sample_date, limit_enable, ext_mv_lcl, mv_lal,
 ext_mv_center, mv_ual, ext_mv_ucl, ext_raw_lcl, raw_lal, ext_ra
w_center, raw_ual, ext_raw_ucl, ext_sigma_lcl, sigma_lal, ext_si
gma_center, sigma_ual, ext_sigma_ucl, ext_range_lcl, range_lal,

     24,119      24,119           10           16     0.7  382751968
Module: OEM.BoundedPool
SELECT LOCKID FROM DBMS_LOCK_ALLOCATED WHERE NAME = :B1 FOR UPDA
TE

     23,379      23,379            0            0     0.7 1883164605
Module: Data Pump Worker
SELECT /*+all_rows*/ SYS_XMLGEN(VALUE(KU$), XMLFORMAT.createForm
at2('TABLE_T', '7')), KU$.OBJ_NUM ,KU$.ANC_OBJ.NAME ,KU$.ANC_OBJ
.OWNER_NAME ,KU$.ANC_OBJ.TYPE_NAME ,KU$.BASE_OBJ.NAME ,KU$.BASE_
OBJ.OWNER_NAME ,KU$.BASE_OBJ.TYPE_NAME ,KU$.SPARE1 ,KU$.TSTZ_COL

     17,197      17,197            6           12     0.5  326203898
Module: OEM.CacheModeWaitPool
SELECT TARGET_GUID FROM MGMT_TARGETS WHERE TARGET_NAME=:B2 AND T
ARGET_TYPE=:B1

     15,932      15,932            5          352     0.5 2669392844
Module: OEM.SystemPool
UPDATE MGMT_CURRENT_METRICS SET COLLECTION_TIMESTAMP = :B1 , VAL
UE = :B6 , STRING_VALUE = :B5 WHERE TARGET_GUID = :B4 AND METRIC
_GUID = :B3 AND KEY_VALUE = :B2 AND COLLECTION_TIMESTAMP < :B1

     15,771      15,771            6           12     0.5 3011516722
Module: OEM.CacheModeWaitPool
SELECT SET_USAGE, SET_CONTEXT_TYPE FROM MGMT_CREDENTIAL_SETS CS,
 MGMT_TARGETS T WHERE CS.TARGET_TYPE=:B3 AND CS.SET_NAME=:B2 AND
 CS.TARGET_TYPE_META_VER=T.TYPE_META_VER AND T.TARGET_GUID=:B1

     14,868      14,868            0            0     0.4    9861608
Module: MMON_SLAVE
SELECT T.CLIENT_ID,         T.OPERATION_ID,         T.TARGET_TYP
SQL ordered by Sharable Memory  DB/Inst: nowatzki/nowatzki  Snaps: 3-4
-> End Sharable Memory Threshold(KB):      1024

    Max         End
 Sharable    Sharable                                        Old
Memory (KB) Memory (KB)  Parse Calls  Executions  % Total Hash Value
----------- ----------- ------------ ------------ ------- ----------
E,         T.TARGET_NAME,         T.CURR_JOB_NAME,         TO_NU
MBER(BITAND(T.ATTRIBUTES, 131072)),         CASE NVL((SELECT J.S
TATE                     FROM DBA_SCHEDULER_JOBS J

     14,643      14,643            0            0     0.4 1440126209
Module: DBMS_SCHEDULER
MERGE /*+ dynamic_sampling(ST 4) dynamic_sampling_est_cdn(ST) */
 INTO STATS_TARGET$ ST USING (SELECT STALENESS, OSIZE, OBJ#, TYP
E#, AFLAGS, STATUS, SID, SERIAL#, PART#, BO# FROM ( SELECT /*+ n
o_expand dynamic_sampling(4) dynamic_sampling_est_cdn */ DECODE(

     14,104      14,104            9           12     0.4 3340678517
Module: OEM.SystemPool
SELECT /*+ ORDERED PUSH_SUBQ */ DISTINCT D.PROFILE_NAME, D.DEVIC
E_NAME, D.TYPE, D.STATUS, D.PROGRAM, CFG.OWNER, CFG.RULE_NAME FR
OM (SELECT RULE_NAME, OWNER FROM MGMT_NOTIFY_JOB_RULE_CONFIGS C
WHERE (C.TARGET_NAME||';'||C.TARGET_TYPE IN (SELECT * FROM TABLE

     13,987      13,987            1           31     0.4 3644905437
Module: OEM.BoundedPool
SELECT DEVICE_NAME, PROFILE_NAME, TYPE, PROGRAM FROM MGMT_NOTIFY
_DEVICES D WHERE D.STATUS = :B1 AND EXISTS (SELECT 1 FROM MGMT_N
OTIFY_REQUEUE R WHERE R.DEVICE_NAME = D.DEVICE_NAME AND R.DEVICE
_OWNER = D.PROFILE_NAME AND (R.NOTIFICATION_TYPE = :B2 AND R.LAS

     13,464      13,464            6            6     0.4  334677012
Module: OEM.CacheModeWaitPool
SELECT STEP_ID FROM MGMT_JOB_EXECUTION WHERE JOB_ID=:B6 AND EXEC
UTION_ID=:B5 AND STEP_NAME=:B4 AND STEP_TYPE IN (:B3 , :B2 , :B1
 )

     11,517      11,517            2            2     0.3 3529836921
Module: JDBC Thin Client
INSERT INTO T_ID_TMP2   SELECT T.CH_ID, T.CKC_ID        FROM T_D
B_METRICS T, V_CHANNEL_INH V, check_runtime R, V_CKC_INH VC
   WHERE T.CH_ID = V.CH_ID AND V.CKC_ID = 0 AND              R.C
H_ID = T.CH_ID AND R.CKC_ID = T.CKC_ID AND              T.CH_ID

      9,943       9,943            0            0     0.3 3694268570
select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags,i
.property,i.pctfree$,i.initrans,i.maxtrans,i.blevel,i.leafcnt,i.
distkey,i.lblkkey,i.dblkkey,i.clufac,i.cols,i.analyzetime,i.samp
lesize,i.dataobj#,nvl(i.degree,1),nvl(i.instances,1),i.rowcnt,mo

      9,578       9,578           22           22     0.3 1057335806
Module: JDBC Thin Client
insert into nfm.T_BATCHID_SESSIONS_LOG (address, batch_id, end_t
ime, reason, retry, send_as_type, start_time, status, type, id,
message_body) values (:1 , :2 , :3 , :4 , :5 , :6 , :7 , :8 , :9
 , :10 , :11 )

      9,184       9,184            2            2     0.3 2419146987
Module: JDBC Thin Client
SQL ordered by Sharable Memory  DB/Inst: nowatzki/nowatzki  Snaps: 3-4
-> End Sharable Memory Threshold(KB):      1024

    Max         End
 Sharable    Sharable                                        Old
Memory (KB) Memory (KB)  Parse Calls  Executions  % Total Hash Value
----------- ----------- ------------ ------------ ------- ----------
MERGE INTO T_DB_METRICS T USING (SELECT * from T_DB_METRICS_TMP
WHERE UPDATE_CASE =1) TMP ON ( T.CH_ID = TMP.CH_ID AND T.CKC_ID
= TMP.CKC_ID )  WHEN MATCHED THEN UPDATE SET T.UPDATED = TMP.UPD
ATED, T.LDS_ID = TMP.LDS_ID, T.PARAMETER_NAME = TMP.PARAMETER_NA

      8,763       8,763            1            1     0.3   67733282
Module: OEM.BoundedPool
SELECT /*+ ORDERED PUSH_SUBQ */ DISTINCT D.PROFILE_NAME, D.DEVIC
E_NAME, D.TYPE, D.STATUS, D.PROGRAM, CFG.OWNER, CFG.RULE_NAME, D
ECODE(D.TYPE, :B29 ,D.PROFILE_NAME, :B28 ,D.PROFILE_NAME, CFG.OW
NER) CHECKUSER FROM (SELECT RULE_NAME, OWNER FROM MGMT_NOTIFY_RU

      8,475       8,475            0            0     0.3  166515280
                                       merge /*+ dynamic_samplin
g(mm 4) dynamic_sampling_est_cdn(mm) */                into mon_
mods_all$ mm
     using

      7,957       7,957            0            0     0.2 3067124474
Module: Data Pump Worker
SELECT /*+rule*/ SYS_XMLGEN(VALUE(KU$), XMLFORMAT.createFormat2(
'TABLE_DATA_T', '7')), 0 ,KU$.BASE_OBJ.NAME ,KU$.BASE_OBJ.OWNER_
NAME ,'TABLE' ,to_char(KU$.BYTES_ALLOC) ,KU$.SCHEMA_OBJ.CTIME ,K
U$.DATAOBJ_NUM ,to_char(KU$.ET_PARALLEL) ,KU$.FGAC ,KU$.LONGCOL

      7,875       7,875           21           20     0.2  669053422
Module: JDBC Thin Client
SELECT NULL AS table_cat,        o.owner AS table_schem,
o.object_name AS table_name,        o.object_type AS table_type,
        NULL AS remarks   FROM all_objects o   WHERE o.owner LIK
E :1 ESCAPE '/'     AND o.object_name LIKE :2 ESCAPE '/'     AND

      7,802       7,802            0            0     0.2  602297095
Module: OEM.BoundedPool
SELECT M.METRIC_GUID, M.METRIC_TYPE, M.METRIC_NAME, M.NUM_KEYS,
M.IS_REPOSITORY, M.IS_TRANSPOSED, M.SOURCE_TYPE, M.KEY_ORDER, M.
METRIC_COLUMN,M.KEYS_FROM_MULT_COLLS FROM MGMT_METRICS M, MGMT_T
ARGETS T WHERE M.METRIC_NAME = (SELECT DISTINCT(METRIC_NAME) FRO

      7,781       7,781            1           11     0.2 3622687358
Module: OEM.BoundedPool
SELECT T2.TARGET_GUID FROM MGMT_TARGETS T1, MGMT_TARGETS T2 WHER
E T1.TARGET_GUID=:B2 AND T1.EMD_URL=T2.EMD_URL AND T2.TARGET_TYP
E=:B1

      7,414       7,414            0            0     0.2 4140321063
Module: emagent_SQL_oracle_database
/* OracleOEM */  SELECT /*+ INDEX(ts) */    o.object_type, u.nam
e, o.object_name, o.partition, o.lob_column, o.seg_type, ts.name
, 4 FROM (   SELECT 1 "OBJECT_TYPE", o.owner# "USER_NAME#", o.na
me "OBJECT_NAME", ' ' "PARTITION", ' ' "LOB_COLUMN", 1 "SEG_TYPE

      6,946       6,946            0            0     0.2 2502530145
SQL ordered by Sharable Memory  DB/Inst: nowatzki/nowatzki  Snaps: 3-4
-> End Sharable Memory Threshold(KB):      1024

    Max         End
 Sharable    Sharable                                        Old
Memory (KB) Memory (KB)  Parse Calls  Executions  % Total Hash Value
----------- ----------- ------------ ------------ ------- ----------
Module: Data Pump Worker
SELECT /*+rule*/ SYS_XMLGEN(VALUE(KU$), XMLFORMAT.createFormat2(
'VIEW_T', '7')), KU$.OBJ_NUM ,KU$.SCHEMA_OBJ.NAME ,KU$.SCHEMA_OB
J.NAME ,'VIEW' ,KU$.SCHEMA_OBJ.OWNER_NAME FROM SYS.KU$_VIEW_VIEW
 KU$ WHERE  KU$.OBJ_NUM IN (SELECT * FROM TABLE(DBMS_METADATA.FE

      6,876       6,876            0            0     0.2 4094477166
Module: JDBC Thin Client
select TD.CH_ID, TD.CKC_ID, OBJECT_NAME, OBJECT_DESCRIPTION, VC.
PARAMETER_NAME, nvl2(OBJECT_STATE, OBJECT_STATE, TD2.CH_STATE) O
BJECT_STATE, nvl2(TD.CH_STATE, 'N', 'Y') OBJECT_STATE_INH, VC.FO
LDER_ID, FOLDER_NAME, OBJECT_OWNER, OBJECT_DIVISION, VC.CREATED,

      6,844       6,844            0            0     0.2 1821591710
Module: OEM.BoundedPool
SELECT COLL_NAME, KEY_VALUE, KEY_OPERATOR, IS_EXCEPTION FROM MGM
T_POLICY_ASSOC_CFG WHERE OBJECT_GUID = :B3 AND POLICY_GUID = :B2
 AND COLL_NAME = NVL(:B1 , COLL_NAME) ORDER BY COLL_NAME, EVAL_O
RDER

      6,833       6,833            0            0     0.2 2468153467
Module: OEM.BoundedPool
SELECT CRIT_ACTION_JOB_ID, WARN_ACTION_JOB_ID, INFO_ACTION_JOB_I
D, SIMULTANEOUS_ACTIONS FROM MGMT_POLICY_ASSOC_CFG WHERE OBJECT_
GUID = :B5 AND POLICY_GUID = :B4 AND COLL_NAME = :B3 AND KEY_VAL
UE = :B2 AND KEY_OPERATOR = :B1

      6,815       6,815            2            2     0.2 1558861730
Module: JDBC Thin Client
MERGE INTO T_DB_METRICS T USING (SELECT * from T_DB_METRICS_TMP
WHERE UPDATE_CASE IN (2,3)) TMP ON ( T.CH_ID = TMP.CH_ID AND T.C
KC_ID = TMP.CKC_ID ) WHEN MATCHED THEN UPDATE SET T.COLL_FREQ_CA
T = TMP.COLL_FREQ_CAT, T.NEWEST_DATE  = TMP.NEWEST_DATE, T.COLLE

      6,809       6,809            6            6     0.2  940723418
Module: JDBC Thin Client
insert into ua.T_CSM_ATTRIBUTE (SESSION_ID, ATTRIBUTE_ID, ATTRIB
UTE_VALUE) values (:1 , :2 , :3 )

      6,772       6,772           22           22     0.2  128656321
Module: JDBC Thin Client
update nfm.T_BATCHID_SESSIONS set address=:1 , batch_id=:2 , end
_time=:3 , locked=:4 , retry=:5 , send_as_type=:6 , start_time=:
7 , template_id=:8 , type=:9 , message_body=:10  where id=:11

      6,696       6,696            0            0     0.2 4144012469
Module: ghost.exe
SELECT check_runtime.channel_type FROM check_runtime  WHERE  ch_
id  =  :BP_00000_v:BP_00000_i AND  ckc_id  =  :BP_00001_v:BP_000
01_i  FOR UPDATE OF channel_type

      6,660       6,660            2            2     0.2 1283368828
Module: ghost.exe
SQL ordered by Sharable Memory  DB/Inst: nowatzki/nowatzki  Snaps: 3-4
-> End Sharable Memory Threshold(KB):      1024

    Max         End
 Sharable    Sharable                                        Old
Memory (KB) Memory (KB)  Parse Calls  Executions  % Total Hash Value
----------- ----------- ------------ ------------ ------- ----------
SELECT check_runtime.first_sample_date FROM check_runtime  WHERE
  ch_id  =  :BP_00000_v:BP_00000_i AND  ckc_id  =  :BP_00001_v:B
P_00001_i

      6,650       6,650            0            6     0.2 1461369474
Module: OEM.SystemPool
UPDATE MGMT_JOB_EXECUTION SET STEP_STATUS = :B4 , DISPATCHER_ID=
:B3 , OMS_NAME=:B2 WHERE STEP_ID = :B1

      6,623       6,623            3            3     0.2 3026054240
Module: OEM.BoundedPool
SELECT STEPSET_STATUS FROM MGMT_JOB_EXECPLAN WHERE STEP_NAME=(SE
LECT STEP_NAME FROM MGMT_JOB_EXECUTION WHERE STEP_ID=:B3 ) AND S
TEP_TYPE=:B2 AND JOB_TYPE_ID=:B1

      6,540       6,540            0            0     0.2 3329738953
                                    merge /*+ dynamic_sampling(m
m 4) dynamic_sampling_est_cdn(mm) */               into sys.mon_
mods_all$ mm
 using

      6,419       6,419            9            9     0.2 3809403205
Module: ghost.exe
SELECT c.ch_id, c.parameter_name, ch_name, ch_descr, c.channel_t
ype, c.active, parameter_unit, created, updated, updated_by, aut
hor, owner, perm_domain_id, d.ch_state, prerun_start, autostart_
prerun, sample_num, violation_num, to_be_commented, first_sample

      6,410       6,410            6            6     0.2   31599234
Module: perl.exe
select propagation_name, 'BUFFERED', num_msgs ready, 0  from gv$
buffered_subscribers b,dba_propagation p, dba_queues q, dba_queu
e_tables t where b.subscriber_name = p.propagation_name and b.su
bscriber_address  = p.destination_dblink and b.queue_schema = p.

      6,279       6,279            0            0     0.2  286044095
Module: ghost.exe
SELECT ckc_id, ckc_blob FROM check_runtime WHERE ch_id =  :BP_00
000_v:BP_00000_i AND ckc_id != 0 AND exval_06 =  :BP_00001_v:BP_
00001_i AND exval_07 =  :BP_00002_v:BP_00002_i AND exval_08 =  :
BP_00003_v:BP_00003_i

      6,262       6,262            3            3     0.2 3452405764
Module: emagent_SQL_oracle_database
/* OracleOEM */  SELECT SUM(broken), SUM(failed)    FROM (SELECT
 DECODE(STATE, 'BROKEN', 1, 0) broken,                DECODE(STA
TE,'FAILED',1,0) failed                   FROM DBA_SCHEDULER_JOB
S            )

      6,243       6,243           15           15     0.2 2201096425
Module: event.exe
SELECT t_event_consumers.ior, t_event_consumers.event_id, t_even
t_consumers.operation_name, t_event_consumers.tcp_address FROM t
SQL ordered by Sharable Memory  DB/Inst: nowatzki/nowatzki  Snaps: 3-4
-> End Sharable Memory Threshold(KB):      1024

    Max         End
 Sharable    Sharable                                        Old
Memory (KB) Memory (KB)  Parse Calls  Executions  % Total Hash Value
----------- ----------- ------------ ------------ ------- ----------
_event_consumers  WHERE  event_id  =  :BP_00004_v:BP_00004_i AND
 tcp_address IN ( :BP_00000_v:BP_00000_i,  :BP_00001_v:BP_00001_

      6,234       6,234            0            0     0.2 1671000993
Module: ghost.exe
SELECT check_runtime.ch_id FROM check_runtime  WHERE  ch_id  =
:BP_00000_v:BP_00000_i AND  ckc_id  <>  :BP_00001_v:BP_00001_i A
ND  exval_06  =  :BP_00002_v:BP_00002_i AND  exval_07  =  :BP_00
003_v:BP_00003_i AND  exval_08  =  :BP_00004_v:BP_00004_i

      6,167       6,167       12,120       12,120     0.2 2656347295
Module: ghost.exe
SELECT /*+ NOPARALLEL (check_runtime)*/ r.ch_id, r.ckc_id, r.fol
der_id, r.fxs_available, r.min_sample_size, r.max_sample_size, r
.ckc_blob, t.reset_date, nvl2(t.ch_state, t.ch_state, t2.ch_stat
e) FROM check_runtime r, t_ckc_spc_data t, t_ckc_spc_data t2 WHE

      6,132       6,132            2            2     0.2 3575815453
Module: emagent_SQL_oracle_database
SELECT OWNER,JOB_NAME,COMMENTS FROM DBA_SCHEDULER_JOBS WHERE JOB
_NAME LIKE 'EM_IDX_STAT_JOB%' AND UPPER(OWNER) = 'DBSNMP'

      6,058       6,058            9            9     0.2 1704291494
Module: ghost.exe
SELECT check_runtime.ckc_blob, check_runtime.sample_num, t_ckc_r
untime.first_sample_date, check_runtime.blob_version FROM t_ckc_
runtime  WHERE  ch_id  =  :BP_00000_v:BP_00000_i AND  ckc_id  =
 :BP_00001_v:BP_00001_i  FOR UPDATE OF ckc_blob

      5,871       5,871            1            1     0.2  954141548
select OBJOID,  CLSOID, RUNTIME, PRI, JOBTYPE,  SCHLIM,  WT, INS
T,  RUNNOW, ENQ_SCHLIM from ( select a.obj# OBJOID, a.class_oid
CLSOID,    decode(bitand(a.flags, 16384), 0, a.next_run_date,
        a.last_enabled_time) RUNTIME,    (2*a.priority +     dec

      5,856       5,856        1,203        1,203     0.2  763934088
Module: event.exe
SELECT t_event_consumers.ior, t_event_consumers.event_id, t_even
t_consumers.operation_name, t_event_consumers.tcp_address FROM t
_event_consumers  WHERE  event_id  =  :BP_00002_v:BP_00002_i AND
 tcp_address IN ( :BP_00000_v:BP_00000_i,  :BP_00001_v:BP_00001_

      5,756       5,756          632          632     0.2   48855250
Module: ghost.exe
SELECT check_runtime.ckc_blob, check_runtime.sample_num, t_ckc_r
untime.first_sample_date, check_runtime.blob_version FROM t_ckc_
runtime  WHERE  ch_id  =  :BP_00000_v:BP_00000_i AND  ckc_id  =
 :BP_00001_v:BP_00001_i

      5,684       5,684            3            3     0.2 3516263011
Module: emagent_SQL_oracle_database
select a.capture_name streams_process_name, a.status streams_pro
cess_status, 'CAPTURE' streams_process_type, COUNT(a.error_messa
SQL ordered by Sharable Memory  DB/Inst: nowatzki/nowatzki  Snaps: 3-4
-> End Sharable Memory Threshold(KB):      1024

    Max         End
 Sharable    Sharable                                        Old
Memory (KB) Memory (KB)  Parse Calls  Executions  % Total Hash Value
----------- ----------- ------------ ------------ ------- ----------
ge) from dba_capture a group by a.capture_name, a.status union a
ll select a.propagation_name streams_process_name, a.status stre

      5,589       5,589           22           22     0.2 1592125054
Module: JDBC Thin Client
insert into nfm.T_BATCHID_SESSIONS (address, batch_id, end_time,
 locked, retry, send_as_type, start_time, template_id, type, id,
 message_body) values (:1 , :2 , :3 , :4 , :5 , :6 , :7 , :8 , :
9 , :10 , :11 )

      5,530       5,530            0            0     0.2 1559200429
Module: Data Pump Worker
SELECT /*+all_rows*/ SYS_XMLGEN(VALUE(KU$), XMLFORMAT.createForm
at2('TABLE_T', '7')), KU$.OBJ_NUM ,KU$.ANC_OBJ.NAME ,KU$.ANC_OBJ
.OWNER_NAME ,KU$.ANC_OBJ.TYPE_NAME ,KU$.BASE_OBJ.NAME ,KU$.BASE_
OBJ.OWNER_NAME ,KU$.BASE_OBJ.TYPE_NAME ,KU$.SPARE1 ,KU$.TSTZ_COL

      5,311       5,311            3            3     0.2  911058769
Module: perl.exe
select propagation_name streams_name,'PROPAGATION' streams_type,
'"'||destination_queue_owner||'"."'||destination_queue_name||'"@
'||destination_dblink address,queue_table,owner,source_queue_nam
e from dba_queues, dba_propagation where owner=SOURCE_QUEUE_OWNE

      5,273       5,273        6,082        6,080     0.2  225647200
Module: JDBC Thin Client
select group_id from T_A_WEB_GROUP_ID_MAP where LDS=:1  and grou
p1=:2  and group2=:3  and group3=:4  and group5=:5

      5,101       5,101            0            0     0.2 1231100300
Module: event.exe
SELECT t_event_consumers.ior, t_event_consumers.event_id, t_even
t_consumers.operation_name, t_event_consumers.tcp_address FROM t
_event_consumers  WHERE  event_id  =  :BP_00001_v:BP_00001_i AND
 tcp_address IN ( :BP_00000_v:BP_00000_i) ORDER BY  tcp_address

      5,004       5,004            0            0     0.1 3524300188
SELECT SUM(USED), SUM(TOTAL) FROM (SELECT /*+ ORDERED */ SUM(D.B
YTES)/(1024*1024)-MAX(S.BYTES) USED, SUM(D.BYTES)/(1024*1024) TO
TAL FROM (SELECT TABLESPACE_NAME, SUM(BYTES)/(1024*1024) BYTES F
ROM (SELECT /*+ ORDERED USE_NL(obj tab) */ DISTINCT TS.NAME FROM

      4,974       4,974            0            0     0.1 1497539496
 SELECT mgmt_metric_value_obj.new(target_guid,null,null,
                                         mgmt_namevalue_array(mg
mt_namevalue_obj.new('GROUP_TARGET_GUID',GROUP_TARGET_GUID),mgmt
_namevalue_obj.new('MEMBER_TARGET_TYPE',MEMBER_TARGET_TYPE),mgmt

      4,974       4,974            0            0     0.1 2477663428
Module: ghost.exe
SELECT check_runtime.ch_id FROM check_runtime  WHERE  lds_id  =
 :BP_00000_v:BP_00000_i AND  parameter_name  =  :BP_00001_v:BP_0
0001_i AND  channel_type  =  :BP_00002_v:BP_00002_i AND  exval_0
SQL ordered by Sharable Memory  DB/Inst: nowatzki/nowatzki  Snaps: 3-4
-> End Sharable Memory Threshold(KB):      1024

    Max         End
 Sharable    Sharable                                        Old
Memory (KB) Memory (KB)  Parse Calls  Executions  % Total Hash Value
----------- ----------- ------------ ------------ ------- ----------
0  =  :BP_00003_v:BP_00003_i AND  exval_01  =  :BP_00004_v:BP_00

      4,908       4,908            3            3     0.1 1691239368
Module: emagent_SQL_oracle_database
SELECT COUNT(*) FROM SYS.DBA_PROPAGATION WHERE ERROR_MESSAGE IS
NOT NULL

      4,897       4,897           15           48     0.1 4203089485
SELECT TARGET_GUID FROM MGMT_TARGETS WHERE TARGET_NAME = :B2 AND
 TARGET_TYPE = :B1

      4,877       4,877            2            2     0.1 1420902353
Module: JDBC Thin Client
MERGE INTO T_DB_VIOLATIONS T USING T_DB_VIOLATIONS_TMP TMP ON (
T.CH_ID = TMP.CH_ID AND T.CKC_ID = TMP.CKC_ID AND T.SAMPLE_ID =
TMP.SAMPLE_ID AND T.VAL_TYPE_ID = TMP.VAL_TYPE_ID ) WHEN MATCHED
 THEN UPDATE SET T.PRIO_VIOLATION = TMP.PRIO_VIOLATION, T.SAMPLE

      4,861       4,861            0            0     0.1 3793998726
Module: emagent_SQL_oracle_database
/* OracleOEM */  select 'insert_failure', obj.owner || '.' || ob
j.object_name , 'NA' from dba_objects obj, DBA_OBJ_AUDIT_OPTS au
d  where aud.object_name=obj.object_name and    aud.owner = obj.
owner and    obj.OBJECT_TYPE in ('VIEW', 'TABLE', 'MATERIALIZED

      4,783       4,783            3            3     0.1 3587501735
Module: emagent_SQL_oracle_database
/* OracleOEM */    SELECT PROPAGATION_NAME,MESSAGE_DELIVERY_MODE
, TOTAL_NUMBER, TOTAL_BYTES/1024 KBYTES FROM DBA_PROPAGATION P,
DBA_QUEUE_SCHEDULES Q   WHERE  P.SOURCE_QUEUE_NAME = Q.QNAME AND
 P.SOURCE_QUEUE_OWNER = Q.SCHEMA AND MESSAGE_DELIVERY_MODE='BUFF

      4,780       4,780            1            1     0.1 2089211544
Module: OEM.BoundedPool
DELETE FROM MGMT_CURRENT_VIOLATION WHERE TARGET_GUID = :B3 AND P
OLICY_GUID = :B2 AND KEY_VALUE = :B1

      4,726       4,726           17           17     0.1 3568146125
Module: ghost.exe
SELECT * FROM (SELECT d.ch_id, k.ckc_id, ckc_name, ckc_descripti
on, created, updated, updated_by, author, NVL(deleted, 'N') dele
ted, del_date ,prerun_start, autostart_prerun, d.ch_state, sampl
e_num, violation_num, to_be_commented, first_sample_date, last_s

      4,568       4,568            0            0     0.1 2485711662
Module: JDBC Thin Client
select /*+ RULE */ TD.CH_ID, TD.CKC_ID, OBJECT_NAME, OBJECT_DESC
RIPTION, VC.PARAMETER_NAME, nvl2(OBJECT_STATE, OBJECT_STATE, TD2
.CH
		
		
		
Re: Statspack snap shot at level 7 is slow [message #666012 is a reply to message #666011] Fri, 06 October 2017 10:22 Go to previous messageGo to next message
BlackSwan
Messages: 25673
Registered: January 2009
Location: SoCal
Senior Member
If data is NOT changing, then no need to collect new statistics.
If data changes only a little, then no need to collect new statistics.
The rule of thumb is "a little" means less than 10%.
The term "data changes" is for INSERT or DELETE which impacts row counts.

Since V10 release default Oracle database comes with DBMS_SCHEDULER job that "automatically" collects new statistics.
You can & should decide if this is adequate for your database. If not, then you need to implement the desired changes to the statistic update schedule.
Re: Statspack snap shot at level 7 is slow [message #666013 is a reply to message #666012] Fri, 06 October 2017 10:31 Go to previous messageGo to next message
juniordbanewbie
Messages: 169
Registered: April 2014
Senior Member
But I guest we still need to gather system stats, i.e GATHER_SYSTEM_STATISTICS

since server os condition is constantly changing?

right?

thanks
Re: Statspack snap shot at level 7 is slow [message #666014 is a reply to message #666013] Fri, 06 October 2017 10:43 Go to previous messageGo to next message
BlackSwan
Messages: 25673
Registered: January 2009
Location: SoCal
Senior Member
>since server os condition is constantly changing?

post SQL & results that show above is correct?

How frequently do you need to invoke GATHER_SYSTEM_STATISTICS?

I'll stipulate that the database does INSERT to "performance capture" tables,
so show how this action have negative performance implication to your application.

It appears that you suffer from Compulsive Tuning Disorder.
Re: Statspack snap shot at level 7 is slow [message #666015 is a reply to message #666011] Fri, 06 October 2017 15:08 Go to previous message
John Watson
Messages: 7102
Registered: January 2010
Location: Global Village
Senior Member
What happened to the rest of the statspack report?

You seem to be acting deliberately stupid. I shall not get angry, which I assume is what you are trying to provoket. I shall just say "good bye".
Previous Topic: How to partition unpartitioned existing table
Next Topic: using sysdate from dual takes longer time
Goto Forum:
  


Current Time: Mon Oct 23 01:04:09 CDT 2017

Total time taken to generate the page: 0.18196 seconds