Home » RDBMS Server » Performance Tuning » Statspack Report - Suggestion (9.2.0.7)
Statspack Report - Suggestion [message #345935] Fri, 05 September 2008 06:20 Go to next message
nazbrian
Messages: 36
Registered: July 2008
Member
Hi,

Following is my Statspack Report.

STATSPACK report for

DB Name         DB Id    Instance     Inst Num Release     Cluster Host
------------ ----------- ------------ -------- ----------- ------- ------------
PROD             1936202 PROD                1 9.2.0.7.0   NO      dcdwdb01

              Snap Id     Snap Time      Sessions Curs/Sess Comment
            --------- ------------------ -------- --------- -------------------
Begin Snap:         6 02-Sep-08 21:00:03       78      39.2
  End Snap:         7 02-Sep-08 22:00:05       78      39.6
   Elapsed:               60.03 (mins)

Cache Sizes (end)
~~~~~~~~~~~~~~~~~
               Buffer Cache:       608M      Std Block Size:          8K
           Shared Pool Size:       288M          Log Buffer:     10,240K

Load Profile
~~~~~~~~~~~~                            Per Second       Per Transaction
                                   ---------------       ---------------
                  Redo size:              6,840.20              5,278.15
              Logical reads:             33,552.59             25,890.41
              Block changes:                 43.40                 33.49
             Physical reads:             32,890.20             25,379.28
            Physical writes:                  0.46                  0.36
                 User calls:                 18.25                 14.08
                     Parses:                  4.44                  3.43
                Hard parses:                  0.00                  0.00
                      Sorts:                  3.56                  2.75
                     Logons:                  0.00                  0.00
                   Executes:                 15.66                 12.08
               Transactions:                  1.30

  % Blocks changed per Read:    0.13    Recursive Call %:     41.99
 Rollback per transaction %:    0.02       Rows per Sort:      1.64

Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
            Buffer Nowait %:  100.00       Redo NoWait %:    100.00
            Buffer  Hit   %:    1.97    In-memory Sort %:    100.00
            Library Hit   %:  100.00        Soft Parse %:    100.00
         Execute to Parse %:   71.62         Latch Hit %:    100.00
Parse CPU to Parse Elapsd %:  100.00     % Non-Parse CPU:     99.98

 Shared Pool Statistics        Begin   End
                               ------  ------
             Memory Usage %:   33.97   33.97
    % SQL with executions>1:   78.53   78.53
  % Memory for SQL w/exec>1:   78.71   78.71

Top 5 Timed Events
~~~~~~~~~~~~~~~~~~                                                     % Total
Event                                               Waits    Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
CPU time                                                        3,343    69.93
db file scattered read                         14,914,700       1,406    29.41
log file parallel write                             7,380          15      .31
log file sync                                       4,644          10      .22
db file sequential read                            13,402           4      .09
          -------------------------------------------------------------
Wait Events for DB: PROD  Instance: PROD  Snaps: 6 -7
-> 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 scattered read         14,914,700          0      1,406      0  3,195.1
log file parallel write             7,380          0         15      2      1.6
log file sync                       4,644          0         10      2      1.0
db file sequential read            13,402          0          4      0      2.9
control file parallel write         1,197          0          1      1      0.3
SQL*Net break/reset to clien        5,352          0          1      0      1.1
process startup                         3          0          0     35      0.0
latch free                            574          0          0      0      0.1
control file sequential read          536          0          0      0      0.1
LGWR wait for redo copy               802          0          0      0      0.2
buffer busy waits                      62          0          0      0      0.0
SQL*Net more data to client            92          0          0      0      0.0
direct path read (lob)                  6          0          0      0      0.0
SQL*Net message from client        72,241          0    151,174   2093     15.5
queue messages                      1,363      1,363     13,041   9568      0.3
pipe get                            2,635      2,635     10,545   4002      0.6
wakeup time manager                   118        118      3,365  28517      0.0
jobq slave wait                        60         60        176   2939      0.0
SQL*Net more data from clien            3          0          0     18      0.0
SQL*Net message to client          72,242          0          0      0     15.5
          -------------------------------------------------------------
Background Wait Events for DB: PROD  Instance: PROD  Snaps: 6 -7
-> ordered by wait time desc, waits desc (idle events last)

                                                                   Avg
                                                     Total Wait   wait    Waits
Event                               Waits   Timeouts   Time (s)   (ms)     /txn
---------------------------- ------------ ---------- ---------- ------ --------
log file parallel write             7,380          0         15      2      1.6
db file sequential read             1,492          0          3      2      0.3
db file scattered read                804          0          2      3      0.2
control file parallel write         1,197          0          1      1      0.3
control file sequential read          476          0          0      0      0.1
LGWR wait for redo copy               802          0          0      0      0.2
latch free                              1          0          0      0      0.0
rdbms ipc message                  10,853      3,563     14,047   1294      2.3
pmon timer                          1,206      1,206      3,516   2916      0.3
smon timer                             12         12      3,333 ######      0.0
          -------------------------------------------------------------
SQL ordered by Gets for DB: PROD  Instance: PROD  Snaps: 6 -7
-> End Buffer Gets Threshold:     10000
-> Note that resources reported for PL/SQL includes the resources used by
   all SQL statements called within the PL/SQL code.  As individual SQL
   statements are also reported, it is possible and valid for the summed
   total % to exceed 100

                                                     CPU      Elapsd
  Buffer Gets    Executions  Gets per Exec  %Total Time (s)  Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
    120,172,833        1,278       94,032.0   99.4  3325.35   3255.41 3848753087
Module: JDBC Thin Client
select user_data from jtf_ih_bulk_qtbl where msgid = :1 for upda
te
....


Initially I had Buffer Cache of 167M, I have incresed SGA_MAX_SIZE to 2G(Memory 20G) and increased Buffer Cache of 608M.

While running application for I get Buffer hit around 98% for first half an hour. After that it continuously reduces.

Is there any suggestion ?

Regards,
Brian.
Re: Statspack Report - Suggestion [message #345939 is a reply to message #345935] Fri, 05 September 2008 06:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
db file scattered read                         14,914,700       1,406    29.41

Investigate why you read (see SQL section) and why there are waits (see datafiles section).

Regards
Michel
Re: Statspack Report - Suggestion [message #345997 is a reply to message #345935] Fri, 05 September 2008 09:08 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Together with

Buffer  Hit   %:    1.97  


- looks like you are missing a lot of indexes.


Michael
Re: Statspack Report - Suggestion [message #345999 is a reply to message #345997] Fri, 05 September 2008 09:12 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Or doing a lot of Full table scans of vig tables,

If you look at the load profile at the start:
                Redo size:              6,840.20              5,278.15
              Logical reads:             33,552.59             25,890.41
              Block changes:                 43.40                 33.49
             Physical reads:             32,890.20             25,379.28
            Physical writes:                  0.46                  0.36
you spend that whole hour doing an average of 32,000 physical reads per second. That's a LOT of I/O

POst the Top SQL by Physical Reads, and also by Block Reads.
Re: Statspack Report - Suggestion [message #347740 is a reply to message #345935] Sat, 13 September 2008 09:23 Go to previous messageGo to next message
Brayan
Messages: 315
Registered: June 2002
Senior Member
Hi JRowbottom ,

Following SQL's by Physiscal Reads and Block Gets,

I understand that
select user_data from jtf_ih_bulk_qtbl where msgid = :1 for update is is creating the problem,

I Analyze the table jtf_ih_bulk_qtbl regularly, Also there is index on column msgid . Its the type RAW.

In the SQL mentioned above, same record will not be accessed second time.

Pls suggest what should be done

SQL ordered by Gets for DB: PROD  Instance: PROD  Snaps: 6 -7
-> End Buffer Gets Threshold:     10000
-> Note that resources reported for PL/SQL includes the resources used by
   all SQL statements called within the PL/SQL code.  As individual SQL
   statements are also reported, it is possible and valid for the summed
   total % to exceed 100

                                                     CPU      Elapsd
  Buffer Gets    Executions  Gets per Exec  %Total Time (s)  Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
    120,172,833        1,278       94,032.0   99.4  3325.35   3255.41 3848753087
Module: JDBC Thin Client
select user_data from jtf_ih_bulk_qtbl where msgid = :1 for update

        316,254          118        2,680.1    0.3     5.35      5.32 2614951860
select t.schema, t.name, t.flags, q.name from system.aq$_queue_t
ables t, sys.aq$_queue_table_affinities aft,      system.aq$_que
ues q where aft.table_objno = t.objno and aft.owner_instance = :
1 and        q.table_objno = t.objno and q.usage = 0 and       b
itand(t.flags, 4+16+32+64+128+256) = 0       and NOT       ( t.n

         93,075          255          365.0    0.1     0.88   2493.53 3120565770
Module: JDBC Thin Client
BEGIN WF_EVENT_OJMSTEXT_QH.DEQUEUE(:1, :2, :3); END;

         58,015        8,260            7.0    0.0     1.00      1.03 3371479671
select t.name,  (select owner_instance from sys.aq$_queue_table_
affinities   where table_objno = t.objno)  from system.aq$_queue
_tables t where t.name = :1 and t.schema = :2 for update skip lo
cked

         32,109        3,567            9.0    0.0     0.65      0.60 3893339647
---------------------------------------------------------

SQL ordered by Reads for DB: PROD  Instance: PROD  Snaps: 6 -7
-> End Disk Reads Threshold:      1000

                                                     CPU      Elapsd
 Physical Reads  Executions  Reads per Exec %Total Time (s)  Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
    118,470,471        1,278       92,699.9  100.0  3325.35   3255.41 3848753087
Module: JDBC Thin Client
select user_data from jtf_ih_bulk_qtbl where msgid = :1 for update

          3,732            1        3,732.0    0.0     0.42      6.07 4115672663
select u.name, o.name, t.spare1, t.pctfree$    from sys.obj$ o,
sys.user$ u, sys.tab$ t    where  (bitand(t.trigflag, 1048576) =
 1048576) and           o.obj#=t.obj# and o.owner# = u.user#

            648            3          216.0    0.0     1.46      2.75  333476733
DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;
broken BOOLEAN := FALSE; BEGIN statspack.snap; :mydate := next_d
ate; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;

             78          110            0.7    0.0     0.08      0.44 2043612021


Thanks in advance,

Brayan.
Re: Statspack Report - Suggestion [message #347742 is a reply to message #345935] Sat, 13 September 2008 09:26 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>I Analyze the table jtf_ih_bulk_qtbl regularly, Also there is index on column msgid .
Are you also analyzing the index on msgid?
Re: Statspack Report - Suggestion [message #347744 is a reply to message #345935] Sat, 13 September 2008 09:35 Go to previous messageGo to next message
Brayan
Messages: 315
Registered: June 2002
Senior Member
Hi Ana,

Yes, I analyze using following code

dbms_stats.GATHER_TABLE_STATS(ownname=>'APPS',tabname=>'JTF_IH_BULK_QTBL', cascade=>TRUE, granularity => 'DEFAULT'); 


Brayan
Re: Statspack Report - Suggestion [message #347766 is a reply to message #345935] Sat, 13 September 2008 15:59 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>select user_data from jtf_ih_bulk_qtbl where msgid = :1 for update
please post EXPLAIN PLAN for SQL above
Re: Statspack Report - Suggestion [message #348220 is a reply to message #345935] Tue, 16 September 2008 01:21 Go to previous message
Brayan
Messages: 315
Registered: June 2002
Senior Member
Hi Ana,

Please find the explain plan.

SQL> explain plan for
  2  select user_data from jtf_ih_bulk_qtbl where msgid = :1 for update ;

Explained.

SQL> @/proddb/proddb/9.2.0/rdbms/admin/utlxpls.sql

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

--------------------------------------------------------------------------
| Id  | Operation            |  Name             | Rows  | Bytes | Cost  |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                   |  4778 |   443K|  9063 |
|   1 |  FOR UPDATE          |                   |       |       |       |
|*  2 |   TABLE ACCESS FULL  | JTF_IH_BULK_QTBL  |  4778 |   443K|  9063 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   2 - filter(RAWTOHEX("JTF_IH_BULK_QTBL"."MSGID")=:Z)

Note: cpu costing is off

15 rows selected.

SQL>


But, If I use
select user_data from jtf_ih_bulk_qtbl where msgid = HEXTORAW(:1) for update ; Then I'll have diferent Plan. Since this is a standard Application from Oracle I can not customize the application.

SQL>  @/proddb/proddb/9.2.0/rdbms/admin/utlxpls.sql

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------
| Id  | Operation                    |  Name             | Rows  | Bytes | Cost  |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                   |     1 |    95 |     3 |
|   1 |  FOR UPDATE                  |                   |       |       |       |
|   2 |   TABLE ACCESS BY INDEX ROWID| JTF_IH_BULK_QTBL  |     1 |    95 |     3 |
|*  3 |    INDEX UNIQUE SCAN         | SYS_C00127277     |     1 |       |     2 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------

   3 - access("JTF_IH_BULK_QTBL"."MSGID"=HEXTORAW(:Z))

Note: cpu costing is off

16 rows selected.




Brian
Previous Topic: Help - To get Row Source Operation in Tkprof
Next Topic: Problem with SQL Plan
Goto Forum:
  


Current Time: Sat Dec 10 22:13:50 CST 2016

Total time taken to generate the page: 0.04745 seconds