This discussion is archived
10 Replies Latest reply: Feb 8, 2013 12:19 AM by 988324 RSS

how to find where is bottleneck oracle 11.2

988324 Newbie
Currently Being Moderated
I am running oracle 11gr2 on windows server 2008 R2 having ram 20gb.
I am taking statspack report on database and following is it:

i am not getting where is actual bottleneck is and some time there is problem regarding procedure hangup which having business logic fetch by cursor.

i am generate statistics every day using :
begin
dbms_stats.gather_schema_stats(ownname => 'MFG',cascade => TRUE,no_invalidate => FALSE);
end;

and also using:

analyze table abc compute statistics;

stats pack report:
Load Profile Per Second Per Transaction Per Exec Per Call
~~~~~~~~~~~~ ------------------ ----------------- ----------- -----------
DB time(s): 2.0 0.8 0.00 0.01
DB CPU(s): 1.5 0.6 0.00 0.00
Redo size: 68,274.8 28,441.6
Logical reads: 83,672.4 34,855.8
Block changes: 633.0 263.7
Physical reads: 2,763.0 1,151.0
Physical writes: 37.4 15.6
User calls: 379.8 158.2
Parses: 342.6 142.7
Hard parses: 0.3 0.1
W/A MB processed: 9.9 4.1
Logons: 34.0 14.2
Executes: 2,702.3 1,125.7
Rollbacks: 0.0 0.0
Transactions: 2.4

Instance Efficiency Indicators
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 98.06 Optimal W/A Exec %: 99.99
Library Hit %: 100.03 Soft Parse %: 99.90
Execute to Parse %: 87.32 Latch Hit %: 99.97
Parse CPU to Parse Elapsd %: 91.04 % Non-Parse CPU: 99.19

Shared Pool Statistics Begin End
------ ------
Memory Usage %: 68.94 69.12
% SQL with executions>1: 64.57 65.59
% Memory for SQL w/exec>1: 81.76 82.22

Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time
----------------------------------------- ------------ ----------- ------ ------
CPU time 2,194 71.4
db file sequential read 131,781 323 2 10.5
db file scattered read 89,404 206 2 6.7
Disk file operations I/O 91,788 176 2 5.7
direct path read 17,001 89 5 2.9
-------------------------------------------------------------
Host CPU (CPUs: 8 Cores: 4 Sockets: 1)
~~~~~~~~ Load Average
Begin End User System Idle WIO WCPU
------- ------- ------- ------- ------- ------- --------
16.82 5.62 77.56

Instance CPU
~~~~~~~~~~~~ % Time (seconds)
-------- --------------
Host: Total time (s): 15,156.8
Host: Busy CPU time (s): 3,401.3
% of time Host is Busy: 22.4
Instance: Total CPU time (s): 2,827.1
% of Busy CPU used for Instance: 83.1
Instance: Total Database time (s): 3,753.3
%DB time waiting for CPU (Resource Mgr): 0.0

Memory Statistics Begin End
~~~~~~~~~~~~~~~~~ ------------ ------------
Host Mem (MB): 20,468.5 20,468.5
SGA use (MB): 11,022.6 11,022.6
PGA use (MB): 779.3 807.4
% Host Mem used for SGA+PGA: 57.7 57.8
-------------------------------------------------------------

Time Model System Stats DB/Inst: ORACLE/oracle Snaps: 511-512
-> Ordered by % of DB time desc, Statistic name

Statistic Time (s) % DB time
----------------------------------- -------------------- ---------
sql execute elapsed time 2,912.2 78.8
DB CPU 2,825.0 76.4
connection management call elapsed 343.6 9.3
PL/SQL execution elapsed time 56.6 1.5
parse time elapsed 42.8 1.2
hard parse elapsed time 25.1 .7
PL/SQL compilation elapsed time 1.1 .0
repeated bind elapsed time 1.0 .0
inbound PL/SQL rpc elapsed time 0.7 .0
hard parse (sharing criteria) elaps 0.5 .0
sequence load elapsed time 0.1 .0
failed parse elapsed time 0.0 .0
hard parse (bind mismatch) elapsed 0.0 .0
DB time 3,697.2
background elapsed time 56.1
background cpu time 2.1
-------------------------------------------------------------
Foreground Wait Events DB/Inst: ORACLE/oracle Snaps: 511-512
-> 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 sequential read 131,324 0 321 2 28.9 10.5
db file scattered read 89,376 0 206 2 19.6 6.7
Disk file operations I/O 91,705 0 176 2 20.2 5.7
direct path read 16,992 0 89 5 3.7 2.9
log file sync 5,064 0 16 3 1.1 .5
db file parallel read 1,575 0 14 9 0.3 .4
enq: KO - fast object checkp 8 0 5 591 0.0 .2
control file sequential read 4,457 0 2 0 1.0 .1
direct path write temp 1,635 0 2 1 0.4 .1
SQL*Net more data to client 14,776 0 1 0 3.2 .0
SQL*Net message from dblink 603 0 0 1 0.1 .0
ADR block file read 91 0 0 4 0.0 .0
direct path read temp 713 0 0 0 0.2 .0
SQL*Net break/reset to clien 152 0 0 0 0.0 .0
asynch descriptor resize 8,239 100 0 0 1.8 .0
library cache: mutex X 1,238 0 0 0 0.3 .0
SQL*Net more data from dblin 345 0 0 0 0.1 .0
ADR block file write 5 0 0 0 0.0 .0
latch free 66 0 0 0 0.0 .0
direct path write 4 0 0 0 0.0 .0
cursor: pin S 10 0 0 0 0.0 .0
SQL*Net message from client 526,480 0 238,770 454 115.7
jobq slave wait 3,954 100 2,034 514 0.9
wait for unread message on b 1,896 98 1,894 999 0.4
Streams AQ: waiting for mess 379 100 1,892 4993 0.1
SQL*Net more data from clien 11,597 0 4 0 2.5
single-task message 29 0 1 21 0.0
SQL*Net message to client 526,478 0 1 0 115.7
-------------------------------------------------------------

Background Wait Events DB/Inst: ORACLE/oracle Snaps: 511-512
-> 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 7,020 0 16 2 1.5 .5
db file parallel write 5,529 0 14 3 1.2 .5
control file sequential read 5,966 0 5 1 1.3 .2
control file parallel write 1,618 0 3 2 0.4 .1
log file sequential read 66 0 3 50 0.0 .1
SQL*Net more data to client 64,218 0 2 0 14.1 .1
db file sequential read 457 0 2 4 0.1 .1
os thread startup 66 0 1 8 0.0 .0
Disk file operations I/O 83 0 0 1 0.0 .0
asynch descriptor resize 64,343 100 0 0 14.1 .0
direct path read 9 0 0 6 0.0 .0
db file scattered read 28 0 0 2 0.0 .0
rdbms ipc reply 8 0 0 0 0.0 .0
LGWR wait for redo copy 57 0 0 0 0.0 .0
log file single write 8 0 0 0 0.0 .0
db file single write 1 0 0 2 0.0 .0
rdbms ipc message 14,376 51 31,809 2213 3.2
DIAG idle wait 3,738 100 3,788 1013 0.8
smon timer 12 33 2,077 ###### 0.0
dispatcher timer 32 100 1,920 60012 0.0
Streams AQ: qmn coordinator 136 50 1,905 14009 0.0
Streams AQ: qmn slave idle w 68 0 1,905 28017 0.0
pmon timer 2,099 30 1,896 903 0.5
Space Manager: slave idle wa 381 98 1,894 4970 0.1
shared server idle wait 63 100 1,891 30014 0.0
SQL*Net message from client 257,103 0 1,741 7 56.5
SQL*Net more data from clien 64,218 0 88 1 14.1
SQL*Net message to client 192,827 0 0 0 42.4
-------------------------------------------------------------
  • 1. Re: how to find where is bottleneck oracle 11.2
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    Please edit your thread and use
     ....                                                                                                                                                                                                                        
  • 2. Re: how to find where is bottleneck oracle 11.2
    Rob_J Journeyer
    Currently Being Moderated
    Details of how to use the code tag are in the [url https://wikis.oracle.com/display/Forums/Forums+FAQ]FAQ

    Put them before and after the information from the AWR report, otherwise it's very difficult for people to read and they won't want to respond to your question.
  • 3. Re: how to find where is bottleneck oracle 11.2
    Nikolay Savvinov Guru
    Currently Being Moderated
    Hi,

    welcome to the forum.

    When a doctor examines a patient, he should be looking at this patient's symptoms, and not at nationwide health statistics. Similarly, if you know what part of the code is not performing well, it doesn't make sense to go to the system-wide stats, you should focus on that particular piece of code. Trace it (or obtain performance stats otherwise, e.g. via SQL Real-time Monitor etc., if you have the diagnostic pack license) and see where the time goes. Post the results here if having trouble interpreting trace results.

    Best regards,
    Nikolay
  • 4. Re: how to find where is bottleneck oracle 11.2
    988324 Newbie
    Currently Being Moderated
    My problem is:

    In load time any procedure hangup and does not give any output till 5 to 7 min.

    but many time after generate statistics this same procedure running ok. but this process does not work every time.

    all procedures contains business logic and data fetched using cursor/sysrefcursor.

    also this procedure run good in idle time but same procedure taking 7-10 min in load time.

    so give me the way where is actual bottleneck in database.
  • 5. Re: how to find where is bottleneck oracle 11.2
    sb92075 Guru
    Currently Being Moderated
    985321 wrote:
    My problem is:

    In load time any procedure hangup and does not give any output till 5 to 7 min.

    but many time after generate statistics this same procedure running ok. but this process does not work every time.

    all procedures contains business logic and data fetched using cursor/sysrefcursor.

    also this procedure run good in idle time but same procedure taking 7-10 min in load time.

    so give me the way where is actual bottleneck in database.
    ALTER SESSION SET SQL_TRACE=TRUE;

    the content of the resultant trace file will show where time is actually being spent when it runs both fast & slow.

    Does PL/SQL procedures issue SQL that use bind variables?
  • 6. Re: how to find where is bottleneck oracle 11.2
    988324 Newbie
    Currently Being Moderated
    hello,
    as you said:
    Does PL/SQL procedures issue SQL that use bind variables?

    in oracle pl/sql procedure call with bind variable by default as below:
    get_laser_prod(for_comp => :p_for_comp,
    from_date => :p_from_date,
    to_date => :p_to_date,
    for_kapan => :p_for_kapan,
    vrec => :vrec);

    Now i had searched how to pass bind variable in pl/sql procedure sql but all blog said only the above way we can passed bind variable.

    If any thing wrong then plz told me.


    Now as you ask about sql_trace i'll attached one file having result of trace of one of previous hangup procedure.

    ALTER SESSION SET SQL_TRACE=TRUE;
  • 7. Re: how to find where is bottleneck oracle 11.2
    988324 Newbie
    Currently Being Moderated
    the trace file output after converting in readable file using TKPROF:


    TKPROF: Release 11.2.0.1.0 - Development on Mon Feb 4 16:27:50 2013

    Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

    Trace file: C:\Users\LENOVO\Desktop\oracle_ora_836772.trc
    Sort options: default

    ********************************************************************************
    count = number of times OCI procedure was executed
    cpu = cpu time in seconds executing
    elapsed = elapsed time in seconds executing
    disk = number of physical reads of buffers from disk
    query = number of buffers gotten for consistent read
    current = number of buffers gotten in current mode (usually for update)
    rows = number of rows processed by the fetch or execute call
    ********************************************************************************

    SQL ID: 123fu0zkfuu31
    Plan Hash: 0
    alter session set sql_trace=true


    call count cpu elapsed disk query current rows
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    Parse 0 0.00 0.00 0 0 0 0
    Execute 1 0.00 0.00 0 0 0 0
    Fetch 0 0.00 0.00 0 0 0 0
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    total 1 0.00 0.00 0 0 0 0

    Misses in library cache during parse: 0
    Misses in library cache during execute: 1
    Optimizer mode: ALL_ROWS
    Parsing user id: 91
    ********************************************************************************

    SQL ID: 9m7787camwh4m
    Plan Hash: 0
    begin :id := sys.dbms_transaction.local_transaction_id; end;


    call count cpu elapsed disk query current rows
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    Parse 2 0.00 0.00 0 0 0 0
    Execute 2 0.00 0.00 0 0 0 2
    Fetch 0 0.00 0.00 0 0 0 0
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    total 4 0.00 0.00 0 0 0 2

    Misses in library cache during parse: 0
    Optimizer mode: ALL_ROWS
    Parsing user id: 91
    ********************************************************************************

    begin
    sys.dbms_output.get_line(line => :line, status => :status);
    end;

    call count cpu elapsed disk query current rows
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    Parse 1 0.00 0.00 0 0 0 0
    Execute 1 0.00 0.00 0 0 0 1
    Fetch 0 0.00 0.00 0 0 0 0
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    total 2 0.00 0.00 0 0 0 1

    Misses in library cache during parse: 0
    Optimizer mode: ALL_ROWS
    Parsing user id: 91
    ********************************************************************************

    SQL ID: 3bapu5fnw8h07
    Plan Hash: 1388734953
    select :"SYS_B_0"
    from
    dual


    call count cpu elapsed disk query current rows
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    Parse 1 0.00 0.00 0 0 0 0
    Execute 1 0.00 0.00 0 0 0 0
    Fetch 1 0.00 0.00 0 0 0 1
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    total 3 0.00 0.00 0 0 0 1

    Misses in library cache during parse: 0
    Optimizer mode: ALL_ROWS
    Parsing user id: 91

    Rows Row Source Operation
    ------- ---------------------------------------------------
    1 FAST DUAL (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)




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

    OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

    call count cpu elapsed disk query current rows
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    Parse 4 0.00 0.00 0 0 0 0
    Execute 5 0.00 0.00 0 0 0 3
    Fetch 1 0.00 0.00 0 0 0 1
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    total 10 0.00 0.00 0 0 0 4

    Misses in library cache during parse: 0
    Misses in library cache during execute: 1


    OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

    call count cpu elapsed disk query current rows
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    Parse 0 0.00 0.00 0 0 0 0
    Execute 0 0.00 0.00 0 0 0 0
    Fetch 0 0.00 0.00 0 0 0 0
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    total 0 0.00 0.00 0 0 0 0

    Misses in library cache during parse: 0

    5 user SQL statements in session.
    0 internal SQL statements in session.
    5 SQL statements in session.
    ********************************************************************************
    Trace file: C:\Users\LENOVO\Desktop\oracle_ora_836772.trc
    Trace file compatibility: 11.1.0.7
    Sort options: default

    1 session in tracefile.
    5 user SQL statements in trace file.
    0 internal SQL statements in trace file.
    5 SQL statements in trace file.
    4 unique SQL statements in trace file.
    62 lines in trace file.
    202 elapsed seconds in trace file.
  • 8. Re: how to find where is bottleneck oracle 11.2
    Rob_J Journeyer
    Currently Being Moderated
    Why don't you put your code inside CODE tags!?

    No one want to work through that mess to get to the values of interest. I already pointed you towards the link of how to do it.
  • 9. Re: how to find where is bottleneck oracle 11.2
    Richard Harrison . Expert
    Currently Being Moderated
    Hi,
    For a start stop doing the analyze statement completely. And you probably don't need dbms_stats every day - just let the default stats job do its thing.

    The actual data doesn't give us much to go on.

    Is there some top SQL listed from the AWR report to - can you paste that too?

    Cheers,
    Harry
  • 10. Re: how to find where is bottleneck oracle 11.2
    988324 Newbie
    Currently Being Moderated
    Now , I am perform only analyzed method for statics.


    but even though dbms_stats is new method and cover all limit of analyzed method why should you not prefer it?..

    second thing we don't have licence for performance tuning so,AWR does not work . I am using stats pack utility and some query to find top sql on load time which are as below:

    high memory consumed:
    110125     19483     5.65     
    begin CHECK_EMP_ISSUE(P_FOR_COMP=>:P_FOR_COMP, P_FOR_TRANS_DATE=>:P_FOR_TRANS_DATE, P_FOR_EMP_CODE=>:P_FOR_EMP_CODE, P_FOR_DEPT_CODE=>:P_FOR_DEPT_CODE, P_FOR_PROCESS=>:P_FOR_PROCESS, P_FOR_KAPAN_NO=>:P_FOR_KAPAN_NO, P_FOR_PACKET_NO=>:P_FOR_PACKET_NO, P_FOR_PACKET_ID=>:P_FOR_PACKET_ID, P_FOR_ISSUE_TYPE=>:P_FOR_ISSUE_TYPE, P_FOR_SHIFT_NO=>:P_FOR_SHIFT_NO, P_FOR_MC_CODE=>:P_FOR_MC_CODE, P_FOR_CHECK_PREF_EMP=>:P_FOR_CHECK_PREF_EMP, P_FOR_DUMMY=>:P_FOR_DUMMY, P_FOR_MSG=>:P_FOR_MSG, P_FOR_WARNING=>:P_FOR_WARNING, P_FOR_MSG_VALUE=>:P_FOR_MSG_VALUE, VREC=>:VREC); end;                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
    high i/o consumed:
    EVENT                         WAIT_CLASS       USER_IO_WAIT_TIME          SQL_TEXT                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
    asynch descriptor resize     Other            32454263             SELECT /*+ result_cache */ COMP_CODE,KAPAN_GROUP,VKAPAN_GROUP,SEQ_NO,PACKET_ID,CHILD_ID,INW_DATE, VKAPAN_NO,KAPAN_NO,VPACKET_NO,PACKET_NO,SUB_ID,STONE_TYPE,PCS,WGT,CUR_WGT,P_SEQ_NO,L_SEQ_NO, STAGE,STATUS,PACKET_TYPE,:B1 DEPT_CODE,EMP_CODE,PROCESS,CLV_END_DATE,MFG_END_DATE,TRUNC(SYSDATE)-TRUNC(INW_DATE)VDAYS FROM ( SELECT /*+ leading(a) use_hash(b)*/ :B3 COMP_CODE,A.KAPAN_GROUP,A.VKAPAN_GROUP,:B1 DEPT_CODE,A.SEQ_NO,A.PACKET_ID,A.CHILD_ID,A.INW_DATE, A.VKAPAN_NO,A.KAPAN_NO,A.VPACKET_NO,A.PACKET_NO,A.SUB_ID,A.STONE_TYPE,A.PCS PCS,A.WGT,A.CUR_WGT,A.P_SEQ_NO,A.L_SEQ_NO, 

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points