This forum is currently locked. You can't register or post questions at this time. (read more)

Performance Issue on Production

All posts relating to Oracle database administration.

Moderator: Tim...

mail.madhankumar
Senior Member
Posts: 87
Joined: Tue May 15, 2012 2:47 pm

Performance Issue on Production

Postby mail.madhankumar » Sat Mar 16, 2013 1:42 pm

Hi Tim,
i am facing performance issue in one of 11g Std Server with Redhat 5.
server configured through VM Ware. i feel it has IO Problem. it shows one query has problem and execution time looks more but when i execute in Production it works fine.. but in Statspack report shows that is the problem.. it shows different Wait events related to IO like db file async I/O submit , log file parallel write , control file parallel write. earlier
it was showing rowlock contention, then i increased db_writter to 4.. Server has 8 CPU. should i increase dbwr_io_slaves parameter for db_writter... dbwr_io_slaves has value 0. please check my Statspack and Advise me... logical read is more.. but that query is not taking more as per my knowledge with explain plan..

Code: Select all

Snapshot       Snap Id     Snap Time      Sessions Curs/Sess Comment
~~~~~~~~    ---------- ------------------ -------- --------- ------------------
Begin Snap:       7743 15-Mar-13 15:20:53       76       2.4
  End Snap:       7746 15-Mar-13 16:50:30      138       3.6
   Elapsed:      89.62 (mins) Av Act Sess:       7.7
   DB time:     690.91 (mins)      DB CPU:     465.86 (mins)

Cache Sizes            Begin        End
~~~~~~~~~~~       ---------- ----------
    Buffer Cache:     6,272M     6,144M   Std Block Size:         8K
     Shared Pool:     1,728M     1,856M       Log Buffer:    20,464K

Load Profile              Per Second    Per Transaction    Per Exec    Per Call
~~~~~~~~~~~~      ------------------  ----------------- ----------- -----------
      DB time(s):                7.7                4.4        0.06        0.03
       DB CPU(s):                5.2                3.0        0.04        0.02
       Redo size:          977,607.7          556,901.8
   Logical reads:          937,122.9          533,839.4
   Block changes:            9,437.2            5,376.0
  Physical reads:              108.6               61.9
 Physical writes:              146.0               83.2
      User calls:              243.5              138.7
          Parses:               84.2               48.0
     Hard parses:                4.4                2.5
W/A MB processed:               16.2                9.2
          Logons:                0.1                0.1
        Executes:              133.6               76.1
       Rollbacks:                0.0                0.0
    Transactions:                1.8

Instance Efficiency Indicators
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
            Buffer Nowait %:  100.00       Redo NoWait %:   99.69
            Buffer  Hit   %:   99.99  Optimal W/A Exec %:  100.00
            Library Hit   %:   93.47        Soft Parse %:   94.82
         Execute to Parse %:   36.95         Latch Hit %:   99.99
Parse CPU to Parse Elapsd %:   86.87     % Non-Parse CPU:   95.30

 Shared Pool Statistics        Begin   End
                               ------  ------
             Memory Usage %:   80.19   88.06
    % SQL with executions>1:   83.33   80.44
  % Memory for SQL w/exec>1:   81.76   79.96

Top 5 Timed Events                                                    Avg %Total
~~~~~~~~~~~~~~~~~~                                                   wait   Call
Event                                            Waits    Time (s)   (ms)   Time
----------------------------------------- ------------ ----------- ------ ------
log file switch (checkpoint incomplete)            384       7,576  19730   38.2
CPU time                                                     4,436          22.3
db file async I/O submit                         1,867       4,105   2199   20.7
log file sync                                    7,955       1,172    147    5.9
log file parallel write                          9,757         777     80    3.9
          -------------------------------------------------------------
Host CPU  (CPUs: 6  Cores: 6  Sockets: 2)
~~~~~~~~              Load Average
                      Begin     End      User  System    Idle     WIO     WCPU
                    ------- -------   ------- ------- ------- ------- --------
                       8.49    6.19     83.78    3.23   12.76    3.31
Time Model System Stats  DB/Inst: FJETRANS/fjetrans  Snaps: 7743-7746
-> Ordered by % of DB time desc, Statistic name

Statistic                                       Time (s) % DB time
----------------------------------- -------------------- ---------
sql execute elapsed time                        39,105.5      94.3
DB CPU                                          27,951.6      67.4
parse time elapsed                                 338.8        .8
hard parse elapsed time                            306.6        .7
sequence load elapsed time                         182.7        .4
connection management call elapsed                 174.8        .4
PL/SQL execution elapsed time                       21.6        .1
hard parse (sharing criteria) elaps                  4.0        .0
PL/SQL compilation elapsed time                      3.2        .0
hard parse (bind mismatch) elapsed                   0.7        .0
repeated bind elapsed time                           0.3        .0
failed parse elapsed time                            0.1        .0
DB time                                         41,454.7
background elapsed time                          5,531.8
background cpu time                                 84.8
          -------------------------------------------------------------
Foreground Wait Events  DB/Inst: FJETRANS/fjetrans  Snaps: 7743-7746
-> 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 switch (checkpoint           366    0      7,247  19799      0.0   36.5
log file sync                       7,953    0      1,172    147      0.8    5.9
db file sequential read           117,133    0        414      4     12.4    2.1
enq: TX - row lock contentio           16    0        275  17172      0.0    1.4
latch: cache buffers chains         3,394    0        176     52      0.4     .9
direct path write temp              8,308    0        169     20      0.9     .9
log buffer space                      184    0        130    709      0.0     .7
buffer busy waits                      36    0         96   2663      0.0     .5
log file switch completion             65    0         62    959      0.0     .3
db file scattered read             22,936    0         56      2      2.4     .3
write complete waits                    3    0         51  17037      0.0     .3
latch free                            757    0         26     34      0.1     .1
read by other session               4,910    0         19      4      0.5     .1
SQL*Net message from dblink         1,923    0         18      9      0.2     .1
log file switch (private str            9    0         18   1966      0.0     .1
enq: TX - contention                   30   20         14    472      0.0     .1
db file parallel read                 563    0         12     21      0.1     .1
SQL*Net more data to client       401,326    0          7      0     42.5     .0
direct path sync                      113    0          5     43      0.0     .0
Disk file operations I/O              838    0          4      4      0.1     .0
library cache: mutex X                 44    0          2     46      0.0     .0
reliable message                      141    0          2     11      0.0     .0
rdbms ipc reply                       134    0          1     11      0.0     .0
latch: cache buffers lru cha          114    0          1     11      0.0     .0
direct path read temp               6,816    0          1      0      0.7     .0
latch: row cache objects               61    0          1     11      0.0     .0
latch: redo allocation                 63    0          1     10      0.0     .0
latch: shared pool                    126    0          1      5      0.0     .0
local write wait                        9    0          1     66      0.0     .0
cursor: pin S wait on X                11    0          0     39      0.0     .0
undo segment extension                 11  100          0     23      0.0     .0
enq: RO - fast object reuse             5    0          0     47      0.0     .0
asynch descriptor resize           80,378  100          0      0      8.5     .0
direct path read                      598    0          0      0      0.1     .0
cursor: pin S                           1    0          0     92      0.0     .0
enq: TM - contention                    1    0          0     63      0.0     .0
library cache load lock                 1    0          0     34      0.0     .0
SQL*Net break/reset to clien           50    0          0      1      0.0     .0
control file sequential read        2,724    0          0      0      0.3     .0
direct path write                     186    0          0      0      0.0     .0
SQL*Net message to dblink           1,861    0          0      0      0.2     .0
latch: enqueue hash chains              1    0          0      4      0.0     .0
latch: cache buffer handles            10    0          0      0      0.0     .0
SQL*Net message from client     1,049,448    0    450,610    429    111.2
jobq slave wait                    12,245  100      6,138    501      1.3
single-task message                    62    0          2     36      0.0
SQL*Net message to client       1,049,521    0          2      0    111.2
SQL*Net more data from clien       18,324    0          2      0      1.9
          -------------------------------------------------------------

Background Wait Events  DB/Inst: FJETRANS/fjetrans  Snaps: 7743-7746
-> 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
---------------------------- ------------ ---- ---------- ------ -------- ------
db file async I/O submit            1,867    0      4,105   2199      0.2   20.7
log file parallel write             9,757    0        777     80      1.0    3.9
log file switch (checkpoint            18    0        330  18322      0.0    1.7
control file parallel write         3,042    0        153     50      0.3     .8
Disk file operations I/O              861    0         16     19      0.1     .1
buffer busy waits                       6    0         13   2227      0.0     .1
Log archive I/O                     5,332    0         10      2      0.6     .1
db file single write                  630    0          7     12      0.1     .0
os thread startup                     212    0          7     33      0.0     .0
enq: CF - contention                   38    0          6    170      0.0     .0
control file sequential read      255,598    0          4      0     27.1     .0
log file sequential read            5,914    0          3      1      0.6     .0
log file single write                 232    0          3     11      0.0     .0
db file sequential read             2,186    0          2      1      0.2     .0
log file switch completion              1    0          1    957      0.0     .0
LGWR wait for redo copy                57    0          0      9      0.0     .0
latch: cache buffers chains             6    0          0     66      0.0     .0
latch: redo allocation                 15    0          0     23      0.0     .0
Parameter File I/O                     32    0          0     10      0.0     .0
log buffer space                        2    0          0    115      0.0     .0
latch free                             29    0          0      7      0.0     .0
log file sync                           2    0          0     84      0.0     .0
latch: enqueue hash chains              3    0          0     42      0.0     .0
rdbms ipc reply                       345    0          0      0      0.0     .0
reliable message                      343    0          0      0      0.0     .0
ADR block file read                    16    0          0      3      0.0     .0
db file scattered read                602    0          0      0      0.1     .0
ADR block file write                    5    0          0      6      0.0     .0
latch: shared pool                      5    0          0      5      0.0     .0
SQL*Net break/reset to clien            2    0          0      6      0.0     .0
Data file init write                   10    0          0      0      0.0     .0
asynch descriptor resize              450  100          0      0      0.0     .0
rdbms ipc message                  71,297   37     96,716   1357      7.6
DIAG idle wait                     10,721  100     10,738   1002      1.1
Space Manager: slave idle wa        1,905   97      9,386   4927      0.2
Streams AQ: qmn slave idle w          194    0      5,377  27717      0.0
Streams AQ: qmn coordinator           384   50      5,377  14003      0.0
smon timer                             33   30      5,377 ######      0.0
shared server idle wait               179  100      5,371  30003      0.0
pmon timer                          2,978   58      5,370   1803      0.3
dispatcher timer                       89  100      5,340  60005      0.0
SGA: MMAN sleep for componen        2,591   99         29     11      0.3
SQL*Net message from client           895    0         22     25      0.1
class slave wait                       25    0          0      1      0.0
SQL*Net message to client             671    0          0      0      0.1
          -------------------------------------------------------------

SQL ordered by CPU  DB/Inst: FJETRANS/fjetrans  Snaps: 7743-7746
-> Total DB CPU (s):          27,952
-> Captured SQL accounts for  105.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
---------- ------------ ---------- ------ ---------- --------------- ----------
  23196.85          101     229.67   83.0   26143.61   5,876,670,061 3560621806
Module: i-net Crystal-Clear
SELECT INVOICEID, INVOICENUMBER, INVOICEDATE, INVOICEBRANCH, INV
OICEDEPT, INVOICEPARTY, INVOICEPARTYADDRESS, INVOICECURRENCY, LO
CALCURRENCY, INVOICEEXCHANGERATE, INVOICETYPE, INVOICECLASS, INV
OICE_ROUND_OFF_AMOUNT, INVOICE_GROSS_AMOUNT, INVOICEREMARKS, INV

   1577.53        2,395       0.66    5.6    1905.86      31,767,241 3579024815
SELECT POSITION FROM (SELECT SUBSTR(:B1 , 1, ROWNUM) S, ROWNUM P
OSITION FROM ALL_TAB_COLUMNS WHERE ROWNUM <= LENGTH(:B1 )) A WHE
RE SUBSTR(A.S, LENGTH(A.S), 1) = '1' ORDER BY POSITION

   1064.69          121       8.80    3.8    1471.37      21,424,024 2393948026
INSERT INTO FS_AC_TRANSACTION_TAX  (COMPANY_ID,   TRANSACTION_ID
,   TAX_TYPE_INDICATOR,   TAX_CODE,   TAX_RATE,   NET_INVC_AMT_T
XN_CURR,   TAX_AMOUNT_TRANSACTION_CURR,   NET_INVOICE_AMOUNT_LOC
AL_CURR,   TAX_AMOUNT_LOCAL_CURR,   NET_INVOICE_AMOUNT_BASE_CURR

    606.54            4     151.64    2.2    1582.17      93,423,328 2359354138
Module: i-net Crystal-Clear
begin FLYJACETRANS26PROD210312.GETJOBPROFITREPORT(:1,:2,:3,:4,:5
,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17); end;

    410.20           16      25.64    1.5     748.63       8,253,234 2827479155
BEGIN master_cost_apportionment.performApportionment(:1,:2); END
;

          -------------------------------------------------------------
SQL ordered by Elapsed time for DB: FJETRANS  Instance: fjetrans  Snaps: 7743 -7
-> Total DB Time (s):          41,455
-> Captured SQL accounts for  103.2% 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
---------- ------------ ---------- ------ ---------- --------------- ----------
  26143.61          101     258.85   63.1   23196.85           2,019 3560621806
Module: i-net Crystal-Clear
SELECT INVOICEID, INVOICENUMBER, INVOICEDATE, INVOICEBRANCH, INV
OICEDEPT, INVOICEPARTY, INVOICEPARTYADDRESS, INVOICECURRENCY, LO
CALCURRENCY, INVOICEEXCHANGERATE, INVOICETYPE, INVOICECLASS, INV
OICE_ROUND_OFF_AMOUNT, INVOICE_GROSS_AMOUNT, INVOICEREMARKS, INV

   1905.86        2,395       0.80    4.6    1577.53               7 3579024815
SELECT POSITION FROM (SELECT SUBSTR(:B1 , 1, ROWNUM) S, ROWNUM P
OSITION FROM ALL_TAB_COLUMNS WHERE ROWNUM <= LENGTH(:B1 )) A WHE
RE SUBSTR(A.S, LENGTH(A.S), 1) = '1' ORDER BY POSITION

   1582.17            4     395.54    3.8     606.54          73,330 2359354138
Module: i-net Crystal-Clear
begin FLYJACETRANS26PROD210312.GETJOBPROFITREPORT(:1,:2,:3,:4,:5
,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17); end;

   1471.37          121      12.16    3.5    1064.69             150 2393948026
INSERT INTO FS_AC_TRANSACTION_TAX  (COMPANY_ID,   TRANSACTION_ID
,   TAX_TYPE_INDICATOR,   TAX_CODE,   TAX_RATE,   NET_INVC_AMT_T
XN_CURR,   TAX_AMOUNT_TRANSACTION_CURR,   NET_INVOICE_AMOUNT_LOC
AL_CURR,   TAX_AMOUNT_LOCAL_CURR,   NET_INVOICE_AMOUNT_BASE_CURR

    748.63           16      46.79    1.8     410.20             123 2827479155
BEGIN master_cost_apportionment.performApportionment(:1,:2); END
;

    546.41           55       9.93    1.3     226.50          30,267 1659310425
Module: i-net Crystal-Clear
begin FLYJACETRANS26PROD210312.IWA_EACCOUNTS_REPORTS(:1,:2,:3,:4
,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,
:22,:23,:24); end;

    443.02          632       0.70    1.1       0.42             662 4196636411
INSERT INTO FS_FR_JOB_CHARGE(JOB_ID,HEADER_SEQ,DETAIL_SEQ ,CHARG
E_CODE,CHARGE_DATE ,YEAR ,PERIOD ,SALES_OR_COST,PREPAID_OR_COLLE
CT ,PARTY_ID ,ACCOUNT_ID ,CHARGE_REFERENCE ,ACTUAL_TRANSACTION_A
MOUNT ,RESERVE_TRANSACTION_AMOUNT ,TRANSACTION_CURRENCY_CODE ,AC

    417.10            7      59.59    1.0     131.04         280,633 1701662182
Module: i-net Crystal-Clear
SELECT JOBID, COMPANY, BRANCH, DEPARTMENT, CHARGE_CODE, CHARGE_C
ODE_DESCRIPTION, SALES_OR_COST, HEADERSEQ, TO_CHAR(CHARGEDATE, :
B1 ) CHARGEDATE, TRANSACTION_ID, PRICECURRENCY, PRICEEXCHGRATE,
PRICEAMOUNT, TRANSACTION_CURRENCY_CODE, ACTUAL_TRANSACTION_AMOUN

    415.69          228       1.82    1.0      76.78             580 4234089670
BEGIN SHIPMENT_REGISTERS_MIS.CREATESHIPMENTREGISTER(:1,:2,:3); E
ND;


Instance Activity Stats  DB/Inst: FJETRANS/fjetrans  Snaps: 7743-7746
-> Statistics identified by '(derived)' come from sources other than SYSSTAT

Statistic                                      Total  per Hour
--------------------------------- ------------------ ---------
log switches (derived)                            58     38.83
          -------------------------------------------------------------
IO Stat by Function - summary  DB/Inst: FJETRANS/fjetrans  Snaps: 7743-7746
->Data Volume values suffixed with   M,G,T,P are in multiples of 1024,
  other values suffixed with       K,M,G,T,P are in multiples of 1000
->ordered by Data Volume (Read+Write) desc

               ---------- Read --------- --------- Write -------- --- Wait ----
                 Data  Requests    Data   Data  Requests    Data          Avg
Function        Volume     /sec  Vol/sec Volume     /sec  Vol/sec  Count Tm(ms)
--------------- ------ -------- -------- ------ -------- -------- ------ ------
LGWR             3854M    45.9       .7M    10G     7.3      2.0M   268K    0.0
Others           5613M     3.4      1.0M  5428M     2.0      1.0M    29K    0.0
DBWR                1M      .0       .0M  4606M    64.7       .9M  1886     0.0
Buffer Cache Re  3149M    27.3       .6M                            143K    0.0
Direct Writes      19M      .0       .0M  1556M     1.6       .3M  8496     0.0
Direct Reads     1393M     1.3       .3M     3M      .0       .0M  7415     0.0
Streams AQ                  .0                                        6     0.0
          -------------------------------------------------------------

IO Stat by Function - detail  DB/Inst: FJETRANS/fjetrans  Snaps: 7743-7746
->ordered by Data Volume (Read+Write) desc

                   ----------- Read ---------- ----------- Write ---------
                    Small  Large  Small  Large  Small  Large  Small  Large
                     Read   Read   Data   Data  Write  Write   Data   Data
Function             Reqs   Reqs   Read   Read   Reqs   Reqs  Writn  Writn
------------------ ------ ------ ------ ------ ------ ------ ------ ------
LGWR                 247K         3854M           24K    15K   195M    10G
Others                13K  5475    189M  5424M  5601   5363     83M  5345M
DBWR                  15             1M          341K  6514   3792M   814M
Buffer Cache Reads   144K  2428   1640M  1509M
Direct Writes        167            19M         2842   5634    193M  1363M
Direct Reads        2726   4522    179M  1214M    20             3M
Streams AQ             6
          -------------------------------------------------------------
Tablespace IO Stats  DB/Inst: FJETRANS/fjetrans  Snaps: 7743-7746
->ordered by IOs (Reads + Writes) desc

Tablespace
------------------------------
                 Av      Av     Av                    Av        Buffer Av Buf
         Reads Reads/s Rd(ms) Blks/Rd       Writes Writes/s      Waits Wt(ms)
-------------- ------- ------ ------- ------------ -------- ---------- ------
UNDOTBS1
           576       0    0.0     1.0      252,133       47         61  635.4
TEMP
        27,915       5    0.0     8.0       86,781       16          0    0.0
INDEX_TS
        63,806      12    2.5     1.4        9,254        2      5,039    1.2
USERS
        48,014       9    5.5     5.1        5,317        1        563  148.2
PERFSTAT
         8,646       2    3.1     1.0        1,713        0          0    0.0
SYSTEM
         4,269       1    4.7     2.7          188        0          5   34.0
SYSAUX
           871       0    4.9     1.9          512        0          0    0.0
ETRANS_DATA
             7       0    0.0     1.0            0        0          0    0.0
ETRANS_IDX
             6       0    0.0     1.0            0        0          0    0.0
          -------------------------------------------------------------
File IO Stats  DB/Inst: FJETRANS/fjetrans  Snaps: 7743-7746
->Mx Rd Bkt: Max bucket time for single block read
->ordered by Tablespace, File

Tablespace               Filename
------------------------ ----------------------------------------------------
                        Av   Mx                                             Av
                 Av     Rd   Rd    Av                    Av        Buffer BufWt
         Reads Reads/s (ms)  Bkt Blks/Rd       Writes Writes/s      Waits  (ms)
-------------- ------- ----- --- ------- ------------ -------- ---------- ------
ETRANS_DATA              /data1/oradata/fjetrans/etrans_data01_01.dbf
             4       0   0.0   1     1.0            0        0          0
                         /data2/oradata/fjetrans/etrans_data01_02.dbf
             3       0   0.0   1     1.0            0        0          0

ETRANS_IDX               /data1/oradata/fjetrans/etrans_idx01_01.dbf
             3       0   0.0   1     1.0            0        0          0
                         /data2/oradata/fjetrans/etrans_idx01_02.dbf
             3       0   0.0   1     1.0            0        0          0

INDEX_TS                 /data2/oradata/fjetrans/index_ts01.dbf
        63,806      12   2.5 ###     1.4        9,254        2      5,039    1.2

PERFSTAT                 /data2/oradata/fjetrans/perfstat.dbf
         8,646       2   3.1 ###     1.0        1,713        0          0

SYSAUX                   /data1/oradata/fjetrans/sysaux01.dbf
           871       0   4.9 ###     1.9          512        0          0

SYSTEM                   /data1/oradata/fjetrans/system01.dbf
         4,269       1   4.7 ###     2.7          188        0          5   34.0

TEMP                     /data1/oradata/fjetrans/temp01.dbf
        27,915       5   0.0         8.0       86,781       16          0

UNDOTBS1                 /data1/oradata/fjetrans/undotbs01.dbf
           576       0   0.0   4     1.0      252,133       47         61  635.4

USERS                    /data1/oradata/fjetrans/users01.dbf
        41,136       8   5.2 ###     5.5        3,659        1        516  160.4
                         /data2/oradata/fjetrans/users02.dbf
         6,878       1   6.9 ###     2.8        1,658        0         47   13.6

          -------------------------------------------------------------
                                                                  End value
Parameter Name                Begin value                       (if different)
----------------------------- --------------------------------- --------------
audit_file_dest               /home/oracle/admin/fjetrans/adump
audit_trail                   DB
compatible                    11.2.0.0.0
control_files                 /data1/oradata/fjetrans/control01
                              .ctl, /backup/fast_recovery_area/
                              fjetrans/control02.ctl
db_block_size                 8192
db_domain
db_name                       fjetrans
db_recovery_file_dest         /backup/fast_recovery_area
db_recovery_file_dest_size    64424509440
db_writer_processes           4
diagnostic_dest               /home/oracle
dispatchers                   (PROTOCOL=TCP) (SERVICE=fjetransX
                              DB)
log_archive_dest_1            LOCATION=/backup/archive
log_archive_format            %t_%s_%r.dbf
open_cursors                  1000
pga_aggregate_target          2147483648
processes                     1100
remote_login_passwordfile     EXCLUSIVE
sessions                      1674
sga_max_size                  17179869184
sga_target                    8589934592
undo_tablespace               UNDOTBS1
utl_file_dir                  /backup/utl_dir
          -------------------------------------------------------------

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Performance Issue on Production

Postby Tim... » Sat Mar 16, 2013 2:38 pm

Hi.

Are you serious? You have one SQL that you think is a problem, so you started changing instance parameters? That is the polar opposite of what you are meant to do. You are potentially affecting everything else in the system in an attempt to remedy one SQL statement that you think may be a problem. What's more, you say it appears to be working fine when you run it. That makes system-wide changes even more crazy.

The first thing I would suggest is to revert any changes you made to instance, unless the previous values were causing problems on a wider scale.

The next thing I would suggest is that you ignore statspack when trying to diagnose a single statement. Statspack casts it's net too wide to use as a tool to investigate a single statement. Instead, once you've used statspack to identify potentially problematic statements, you should then focus on those statements directly. You are using SE, so probably the best tool is SQL Trace.

http://www.oracle-base.com/articles/mis ... tkprof.php

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

mail.madhankumar
Senior Member
Posts: 87
Joined: Tue May 15, 2012 2:47 pm

Re: Performance Issue on Production

Postby mail.madhankumar » Sat Mar 16, 2013 2:56 pm

Hi Tim,
last we were getting Free buffers wait more in top 5 event.. that's why we though that Server not having IO Capacity for write operation.. that query returns all rows from temp table with order by clause that's it. not even complex.. i will trace it and let u know.. earlier i faced problem with other server like, when it return all rows from temporary table with order by then it was takes more time , due to order by.. so i modified that steps like, created new table same like main temp table.. inserted into new table with order by then i used new temp table for main select statement, this steps works fine.. but this prod issue i tried like that but no use.. can we use this method.. why it takes more time for retrieving rows from temptable with order by.. i could try to apply for this issue also..


How can i find out that server has Enough IO Capacity.. how can we say we need This much IO Speed Required..

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Performance Issue on Production

Postby Tim... » Sat Mar 16, 2013 3:10 pm

Hi.

The load profile of the AWR or statspack reports give you an idea of the maximum load your system is taking up. As far as finding out what the system is capable of, then you are better using one of the test harnesses for measuring storage performance. This article lists some ways to test the storage performance.

http://www.oracle-base.com/articles/mis ... ystems.php

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

mail.madhankumar
Senior Member
Posts: 87
Joined: Tue May 15, 2012 2:47 pm

Re: Performance Issue on Production

Postby mail.madhankumar » Sat Mar 16, 2013 3:18 pm

Hi,
Why it takes more time to return all rows from temp table with order by clause.. if remove order by it works fine..... so i did creating new temp table and inserted all rows with order by clause into new table. new temp table used in Main select statement... can i use like this...

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Performance Issue on Production

Postby Tim... » Sat Mar 16, 2013 3:30 pm

Hi.

If you trace the statement going against the temp table, you may well get an idea about why it is taking so long.

Temp segments are used for ordering data when the operation can't be done in memory. It is possible, allocating more memory to the PGA will allow you to do the ordering without needing extra temp segments. Of course, this is all speculation. The only way you will know for sure it to trace. :)

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

mail.madhankumar
Senior Member
Posts: 87
Joined: Tue May 15, 2012 2:47 pm

Re: Performance Issue on Production

Postby mail.madhankumar » Sat Mar 16, 2013 3:42 pm

Hi Tim,
Thanks for Valuable information.. Production runs on Weekdays. i will analyze it on monday and come back..

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Performance Issue on Production

Postby Tim... » Sat Mar 16, 2013 3:51 pm

OK
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

mail.madhankumar
Senior Member
Posts: 87
Joined: Tue May 15, 2012 2:47 pm

Re: Performance Issue on Production

Postby mail.madhankumar » Mon Mar 18, 2013 3:09 pm

Hi Tim,
i analyzed using trace.. but it not shows anything like problem.. below query has executed more number of times...
i think that query related to dynamic sampling.. it should show when statistics not upto date...why it has executed more number of times..

Code: Select all

SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE
  NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false')
  NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),:"SYS_B_0"),
  NVL(SUM(C2),:"SYS_B_1")
FROM
 (SELECT /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL("SYS_ALIAS_1")
  FULL("SYS_ALIAS_1") NO_PARALLEL_INDEX("SYS_ALIAS_1") */ :"SYS_B_2" AS C1,
  CASE WHEN "SYS_ALIAS_1"."TRANSACTION_ID"=:"SYS_B_3" THEN :"SYS_B_4" ELSE
  :"SYS_B_5" END AS C2 FROM "FLYJACETRANS26PROD210312"."GT_TRANSACTION_TAX"
  "SYS_ALIAS_1") SAMPLESUB



my query also parsed 55 times.. i think it has parsed 55 times for this session itself...

Code: Select all


SQL ID: dua6xqv71qa0h Plan Hash: 2528763625

SELECT INVOICEID, INVOICENUMBER, INVOICEDATE, INVOICEBRANCH, INVOICEDEPT,
  INVOICEPARTY, INVOICEPARTYADDRESS, INVOICECURRENCY, LOCALCURRENCY,
  INVOICEEXCHANGERATE, INVOICETYPE, INVOICECLASS, INVOICE_ROUND_OFF_AMOUNT,
  INVOICE_GROSS_AMOUNT, INVOICEREMARKS, INVOICE_REFERENCE_TYPE,
  INVOICE_REFERENCE_NO, GSIP.EXPORT_IMPORT_INDICATOR, COMPANY_PANNO,
  COMPANY_TANNO, TERMINAL_CHANO, TERMINAL_SERVICE_TAXNO, GSIP.CUSTOMERID,
  GSIP.SHIPPERID, SHIPPERADDRESS, CONSIGNEE, CONSIGNEEADDRESS, HAWB_HBL_NO,
  HAWB_HBL_JOBNO, HAWB_HBL_DATE, MAWB_CONSOLE_NO, MAWB_CONSOLE_JOBNO,
  MAWB_CONSOLE_DATE, CONSOLETYPE, OBLID, OBLDATE, IS_DIRECT, GSIP.GROSSWEIGHT,
   DECODE(GSIP.SHIPMENTMODE, 1, CHARGEABLEWEIGHT, GSIP.GROSSWEIGHT)
  CHARGEABLEWEIGHT, NOOFPACKAGES, GSIP.VOLUME, GSIP.ORIGINTERMINAL,
  DESTINATIONTERMINAL, POL, POD, FLIGHTDATE, FLIGHT_VOYAGE_NO,
  CARRIER_VESSEL_NAME, ETD, GSIP.ETA, INCOTERMS, USER_EMAIL_ID,
  GSIP.SHIPMENTMODE, GSIP.INVOICEBRANCHADDRESS, CUSTOMERNAME,
  GSIP.GOODSDESCRIPTION, PURCHASEORDERNO, COMMERCIALINVNO,
  INVOICEAMOUNTINWORDS, INVOICENAME, GSIP.UOW, GSIP.UOV, GSIP.IGMNO,
  GSIP.IGMDATE, GSIP.NATUREOFGOODS, GSIP.PACKAGETYPE, GSIP.DESCRIP,
  GSIP.CHARGEREF, GSIP.PRICECURR, GSIP.PRICE_EXCG_RATE, GSIP.PRICEAMT,
  GSIP.TRANSAMT, GSIP.INV_ADV_AMOUNT ADVANCEAMT, GSIP.INV_BALANCE_AMOUNT,
  GSIP.CHARGE_SERIAL_NO, GSIP.JOB_ID, (SELECT COUNT(1)
FROM
 GT_SALES_INVOICE_PRINT GSIP1 WHERE GSIP1.INVOICEID = GSIP.INVOICEID GROUP BY
  GSIP1.INVOICEID) CHGCOUNT, GSIP.SHIPPER_INVOICE_NO, GSIP.CONTAINER_TYPE,
  GSIP.CONTAINER_NO, GSIP.SHIPPERNAME, GSIP.CONSIGNEENAME, (SELECT
  SUM(TA1.TAX_AMOUNT_TRANSACTION_CURR) FROM GT_TRANSACTION_TAX TA1 WHERE
  TA1.TRANSACTION_ID = GSIP.INVOICEID GROUP BY TA1.TRANSACTION_ID) TAXAMOUNT,
  GSIP.TAX_PERCENT, GSIP.NOTIFYNAME, HDR.TOORDER TO_ORDER FROM
  GT_SALES_INVOICE_PRINT GSIP LEFT JOIN FS_FR_HOUSEDOCHDR HDR ON
  HDR.JOBFILENO = GSIP.HAWB_HBL_JOBNO AND HDR.HOUSEDOCNO = GSIP.HAWB_HBL_NO
  ORDER BY GSIP.CHARGE_SERIAL_NO


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       55      0.01       0.01          0          6          0           0
Execute     55      0.00       0.00          0          0          0           0
Fetch       55      0.04       0.08         11       1251          0         351
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      165      0.05       0.10         11       1257          0         351

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 88
Number of plan statistics captured: 55

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          4  SORT GROUP BY NOSORT (cr=4 pr=0 pw=0 time=51 us cost=2 size=14 card=1)
         8          6         27   TABLE ACCESS FULL GT_SALES_INVOICE_PRINT (cr=4 pr=0 pw=0 time=29 us cost=2 size=14 card=1)
         1          1          1  SORT GROUP BY NOSORT (cr=3 pr=0 pw=0 time=36 us cost=2 size=27 card=1)
         6          9         27   TABLE ACCESS FULL GT_TRANSACTION_TAX (cr=3 pr=0 pw=0 time=26 us cost=2 size=27 card=1)
         8          6         27  SORT ORDER BY (cr=23 pr=0 pw=0 time=1288 us cost=19 size=150608 card=8)
         8          6         27   NESTED LOOPS OUTER (cr=16 pr=0 pw=0 time=1131 us cost=18 size=150608 card=8)
         8          6         27    TABLE ACCESS FULL GT_SALES_INVOICE_PRINT (cr=3 pr=0 pw=0 time=103 us cost=2 size=150384 card=8)
         8          5         27    TABLE ACCESS BY INDEX ROWID FS_FR_HOUSEDOCHDR (cr=12 pr=0 pw=0 time=955 us cost=2 size=28 card=1)
         8          5         27     INDEX RANGE SCAN FS_FR_HOUSEDOCHDR_HDOCNO (cr=6 pr=0 pw=0 time=906 us cost=1 size=0 card=1)(object id 90215)

********************************************************************************

SQL ID: gxt8gy80v4w03 Plan Hash: 1395081586

SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE
  NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false')
  NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),:"SYS_B_0"),
  NVL(SUM(C2),:"SYS_B_1")
FROM
 (SELECT /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL("SYS_ALIAS_1")
  FULL("SYS_ALIAS_1") NO_PARALLEL_INDEX("SYS_ALIAS_1") */ :"SYS_B_2" AS C1,
  CASE WHEN "SYS_ALIAS_1"."TRANSACTION_ID"=:"SYS_B_3" THEN :"SYS_B_4" ELSE
  :"SYS_B_5" END AS C2 FROM "FLYJACETRANS26PROD210312"."GT_TRANSACTION_TAX"
  "SYS_ALIAS_1") SAMPLESUB


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse      102      0.02       0.02          0          0          0           0
Execute    102      0.03       0.04          0          0          0           0
Fetch      102      0.00       0.00          0        353          0         102
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      306      0.07       0.07          0        353          0         102

Misses in library cache during parse: 50
Misses in library cache during execute: 50
Optimizer mode: ALL_ROWS
Parsing user id: 88     (recursive depth: 1)
Number of plan statistics captured: 101

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  SORT AGGREGATE (cr=3 pr=0 pw=0 time=59 us)
         6         10         27   TABLE ACCESS FULL GT_TRANSACTION_TAX (cr=3 pr=0 pw=0 time=49 us cost=29 size=114352 card=8168)

********************************************************************************



Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Performance Issue on Production

Postby Tim... » Mon Mar 18, 2013 3:47 pm

Hi.

OK. Look at the elapsed times you are dealing with here. They are tiny. I don't really think you will be able to see any net gain my doing anything with these statement. 55 executions, including hard parses, of your query were done in 1/10 of a second. I hardly think this is a killer statement. :)

If you are using dynamic sampling, it either means your database statistics are out of date or you are specifically requesting it. By getting decent stats on your tables you can probably remove all the dynamic sampling stuff straight away.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

mail.madhankumar
Senior Member
Posts: 87
Joined: Tue May 15, 2012 2:47 pm

Re: Performance Issue on Production

Postby mail.madhankumar » Mon Mar 18, 2013 4:00 pm

Hi,
We are not using Dynamic Sampling.. not even manually..also statistics are upto date.. this number of parse for only this session rite... how can i avoid this number of parse.. We are using Cursor_sharing as Exact only...

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Performance Issue on Production

Postby Tim... » Mon Mar 18, 2013 4:06 pm

Hi.

You can see that there is dynamic sampling going on, so I would want to establish why this is happening for a start.

What is your OPTIMIZER_DYNAMIC_SAMPLING value for the system and the session?

As for the parsing, this will be done if the statement is aged out of the system between runs, if the shared pool is flushed, or if the stats are changed, including through dynamic sampling.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

mail.madhankumar
Senior Member
Posts: 87
Joined: Tue May 15, 2012 2:47 pm

Re: Performance Issue on Production

Postby mail.madhankumar » Mon Mar 18, 2013 4:19 pm

HI,
optimizer_dynamic_sampling value is 2.. how will it get aged out from shared pool.. due to more parse..?

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Performance Issue on Production

Postby Tim... » Mon Mar 18, 2013 4:37 pm

Hi.

Changes in stats age stuff out of the shared pool. If new statements are parsed, they push existing statements out if the room it needed.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

mail.madhankumar
Senior Member
Posts: 87
Joined: Tue May 15, 2012 2:47 pm

Re: Performance Issue on Production

Postby mail.madhankumar » Mon Mar 18, 2013 4:53 pm

Hi,
Sga_target is 8G , i feel it's enough memory.. anything can i do here... shared pool also had adjusted to 2,400M at that duration.. automatically..


Return to “Oracle Database Administration”

Who is online

Users browsing this forum: No registered users and 4 guests

cron