statspack wait events

i Guy,
My database is 10g and and my database is running in auto tuneing,i.e I kept in sga_target,
and my application is hanging ,can any body help me in finding why cpu is high and
what is parse cpu to parsed in statspack.

Code:

---

STATSPACK report for

DB Name        DB Id    Instance    Inst Num Release    RAC Host
------------ ----------- ------------ -------- ----------- --- ----------------
q        xxxxxx q              1 10.1.0.4.0  NO  q

              Snap Id    Snap Time      Sessions Curs/Sess Comment
            --------- ------------------ -------- --------- -------------------
Begin Snap:      1580 20-Jun-06 17:42:03      109    384.0
  End Snap:      1582 20-Jun-06 18:02:05      97    436.7
  Elapsed:              20.03 (mins)

Cache Sizes (end)
~~~~~~~~~~~~~~~~~
              Buffer Cache:    1,472M      Std Block Size:        8K
          Shared Pool Size:      416M          Log Buffer:      256K

Load Profile
~~~~~~~~~~~~                            Per Second      Per Transaction
                                  ---------------      ---------------
                  Redo size:              4,074.00            15,160.82
              Logical reads:                74.22                276.19
              Block changes:                13.37                49.74
            Physical reads:                  0.68                  2.53
            Physical writes:                  0.97                  3.61
                User calls:                39.07                145.41
                    Parses:                  9.41                35.01
                Hard parses:                  0.04                  0.17
                      Sorts:                  1.81                  6.73
                    Logons:                  0.12                  0.43
                  Executes:                14.69                54.67
              Transactions:                  0.27

  % Blocks changed per Read:  18.01    Recursive Call %:    31.96
 Rollback per transaction %:  36.53      Rows per Sort:    62.95

Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
            Buffer Nowait %:  100.00      Redo NoWait %:  100.00
            Buffer  Hit  %:  99.08    In-memory Sort %:  100.00
            Library Hit  %:  99.68        Soft Parse %:  99.52
        Execute to Parse %:  35.96        Latch Hit %:  100.00
Parse CPU to Parse Elapsd %:  86.49    % Non-Parse CPU:  97.00

 Shared Pool Statistics        Begin  End
                              ------  ------
            Memory Usage %:  93.61  93.66
    % SQL with executions>1:  35.62  35.76
  % Memory for SQL w/exec>1:  61.91  61.94

Top 5 Timed Events
~~~~~~~~~~~~~~~~~~                                                      % Total
Event                                              Waits    Time (s) Call Time
-------------------------------------------- ------------ ----------- ---------
CPU time                                                          21    83.14
db file sequential read                              724          2      9.42
process startup                                        12          1      1.95
log file parallel write                              336          0      1.82
control file parallel write                          400          0      1.32
          -------------------------------------------------------------
Wait Events  DB/Inst: q/q  Snaps: 1580-1582
-> s  - second
-> cs - centisecond -    100th of a second
-> ms - millisecond -    1000th of a second
-> us - microsecond - 1000000th of a second
-> ordered by wait time desc, waits desc (idle events last)

                                                                  Avg
                                                    Total Wait  wait    Waits
Event                              Waits  Timeouts  Time (s)  (ms)    /txn
---------------------------- ------------ ---------- ---------- ------ --------
db file sequential read              724          0          2      3      2.2
process startup                        12          0          1    42      0.0
log file parallel write              336          0          0      1      1.0
control file parallel write          400          0          0      1      1.2
SQL*Net more data to client        1,635          0          0      0      5.1
log buffer space                        2          0          0    62      0.0
log file sync                        115          0          0      1      0.4
db file parallel read                  11          0          0      8      0.0
change tracking file synchro          19          0          0      1      0.1
control file sequential read        2,020          0          0      0      6.3
latch: library cache                  68          0          0      0      0.2
SQL*Net break/reset to clien            6          0          0      0      0.0
rdbms ipc reply                        4          0          0      1      0.0
db file parallel write                712          0          0      0      2.2
change tracking file synchro          19          0          0      0      0.1
buffer busy waits                      1          0          0      0      0.0
db file scattered read                  1          0          0      0      0.0
latch: shared pool                      2          0          0      0      0.0
latch: redo allocation                  1          0          0      0      0.0
virtual circuit status            47,614        37      2,292    48    147.4
SQL*Net message from client        49,039          0      1,584    32    151.8
Queue Monitor Wait                    40        40      1,149  28726      0.1
jobq slave wait                      375        368      1,087  2898      1.2
wakeup time manager                    4          0        915 ######      0.0
SQL*Net message to client          49,039          0          0      0    151.8
SQL*Net more data from clien          22          0          0      0      0.1
          -------------------------------------------------------------
Background Wait Events  DB/Inst: q/q  Snaps: 1580-1582

---

Have you ever heard of bind variables?

Short of rewriting app to utilize bind variables: (Bind variables would be the best)

Review the following parameters:
Cursor_sharing
Cursor_space_for_time

Those are bandaids to the symtoms. Fix the problem.

Indeed... Short term bandaid. Long term solution.

I don't know if I'm being stupid here, but have you looked at the STATSPACK report that the poster attached? All the SQL seems to use bind variables and % Non-Parse CPU = 99.88. I don't think bind variables are the issue here, although I could be wrong?

First the report in the .txt file spans 2 hours - you should try to target a bad period of 10-15 mins to simplify things.

Secondly what is happening here?

Code:

---

Instance Activity Stats  DB/Inst: KTPRO/ktpro  Snaps: 1552-1564

Statistic                                      Total    per Second    per Trans
--------------------------------- ------------------ -------------- ------------

bytes sent via SQL*Net to client    292,477,608,057  40,599,334.8 ############

---

292Gb in 2 hours! wow, 40Mb a second

. . . also your two reports are very different: the "in-line" one shows 21 seconds CPU in 20 minutes, the .txt file shows 8'798 second in 120 minutes and only one CPU ! ! ! (probably an effect of a long transaction finishing in the time slot).

Time Model System Stats DB/Inst: KTPRO/ktpro Snaps: 1552-1564
-> Total Time in Database calls 9967.6s (or 9967640228us)

Statistic Time (s) % of DB Time
----------------------------------- -------------------- ------------
DB CPU 6,858.6 68.8
DB time 9,967.6
PL/SQL compilation elapsed time 4.9 .0
PL/SQL execution elapsed time 9,218.9 92.5
background cpu time 15.0 .2
background elapsed time 126.6 1.3
connection management call elapsed 11.4 .1
failed parse elapsed time 0.2 .0
hard parse (bind mismatch) elapsed 0.0 .0
hard parse (sharing criteria) elaps 0.1 .0
hard parse elapsed time 3.8 .0
inbound PL/SQL rpc elapsed time 9,244.5 92.7
parse time elapsed 17.4 .2
sequence load elapsed time 0.3 .0
sql execute elapsed time 1,796.7 18.0
-------------------------------------------------------------


From the posted file 2 hour window. It looks like your app is transmitting most/ if not all of the PL/SQL to be executed into the server. Hence the high sql net activity.

What type of system and How many CPU's?

Can you modify the application to utilize named procedures/packages instead of anonymous procedures? Then use compiled PL/SQL and ping the packages.

BAD SQL:
SELECT ABS(:b1),ABS(:b2) FROM DUAL ----> Rewrite this, you don't need the DB to do this math work.
There seems to be a lot of that type thing. Each one by itself is not a lot, but it adds up. If you don't have to select the data, don't!
Instead:

var2 = abs(var1) --- If this is embedded in the app. Then no round trip to the DB is required... 100 x faster.

or if in PL/SQL
var2 := abs(:var1) ; (this is a function call and requires less overhead)

nice of him to come back and answet the questions put to him

Quote:

---

Originally Posted by davey23uk

nice of him to come back and answet the questions put to him

---

Happens all the time.