|
-
thanks Davey23Uk,
below is the stats on DEV.
Code:
Cache Sizes Begin End
~~~~~~~~~~~ ---------- ----------
Buffer Cache: 100M Std Block Size: 8K
Shared Pool Size: 152M Log Buffer: 2,048K
Load Profile Per Second Per Transaction
~~~~~~~~~~~~ --------------- ---------------
Redo size: 7,779.55 5,686.05
Logical reads: 3,752.07 2,742.38
Block changes: 46.36 33.89
Physical reads: 5.17 3.78
Physical writes: 1.24 0.90
User calls: 51.22 37.44
Parses: 22.98 16.80
Hard parses: 2.18 1.59
Sorts: 10.47 7.65
Logons: 0.01 0.00
Executes: 36.72 26.84
Transactions: 1.37
% Blocks changed per Read: 1.24 Recursive Call %: 87.89
Rollback per transaction %: 5.11 Rows per Sort: 176.25
Instance Efficiency Percentages
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 99.86 In-memory Sort %: 100.00
Library Hit %: 92.18 Soft Parse %: 90.53
Execute to Parse %: 37.42 Latch Hit %: 99.98
Parse CPU to Parse Elapsd %: 99.08 % Non-Parse CPU: 54.98
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 88.84 92.32
% SQL with executions>1: 49.20 54.71
% Memory for SQL w/exec>1: 30.05 28.42
Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time
----------------------------------------- ------------ ----------- ------ ------
CPU time 257 93.6
db file sequential read 3,342 6 2 2.2
log file parallel write 3,110 5 2 1.9
log file sync 2,412 5 2 1.8
db file parallel write 918 0 1 .2
-------------------------------------------------------------
Host CPU (CPUs: 8)
~~~~~~~~ Load Average
Begin End User System Idle WIO WCPU
------- ------- ------- ------- ------- ------- --------
3.27 2.82 20.56 14.95 64.49 0.68 0.11
Instance CPU
~~~~~~~~~~~~
% of total CPU for Instance: 1.83
% of busy CPU for Instance: 5.14
%DB time waiting for CPU - Resource Mgr:
Memory Statistics Begin End
~~~~~~~~~~~~~~~~~ ------------ ------------
Host Mem (MB): 32,014.8 32,014.8
SGA use (MB): 400.0 400.0
PGA use (MB): 149.8 146.3
% Host Mem used for SGA+PGA: 1.7 1.7
-------------------------------------------------------------
Time Model System Stats DB/Inst: DEV/DEV Snaps: 21-31
-> Ordered by % of DB time desc, Statistic name
Statistic Time (s) % of DB time
----------------------------------- -------------------- ------------
DB CPU 250.1 95.6
parse time elapsed 165.3 63.2
hard parse elapsed time 162.6 62.2
sql execute elapsed time 128.7 49.2
hard parse (sharing criteria) elaps 25.0 9.6
hard parse (bind mismatch) elapsed 25.0 9.6
PL/SQL compilation elapsed time 4.1 1.6
sequence load elapsed time 0.2 .1
PL/SQL execution elapsed time 0.2 .1
connection management call elapsed 0.2 .1
repeated bind elapsed time 0.1 .0
DB time 261.5
background elapsed time 12.0
background cpu time 7.0
-------------------------------------------------------------
Wait Events DB/Inst: DEV/DEV Snaps: 21-31
-> s - second, cs - centisecond, ms - millisecond, us - microsecond
-> %Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0
-> Only events with Total Wait Time (s) >= .001 are shown
-> ordered by Total Wait Time desc, Waits desc (idle events last)
Avg
%Time Total Wait wait Waits
Event Waits -outs Time (s) (ms) /txn
--------------------------------- ------------ ------ ---------- ------ --------
db file sequential read 3,342 0 6 2 1.4
log file parallel write 3,110 0 5 2 1.3
log file sync 2,412 0 5 2 1.0
db file parallel write 918 0 0 1 0.4
control file parallel write 597 0 0 1 0.2
db file scattered read 880 0 0 0 0.4
control file sequential read 544 0 0 0 0.2
latch: shared pool 69 0 0 0 0.0
SQL*Net more data to client 795 0 0 0 0.3
Data file init write 9 0 0 3 0.0
latch: library cache 2 0 0 5 0.0
LGWR wait for redo copy 129 0 0 0 0.1
rdbms ipc reply 15 0 0 0 0.0
db file single write 4 0 0 0 0.0
SQL*Net message from client 88,521 0 46,015 520 36.8
Streams AQ: waiting for time mana 1 100 3,117 ###### 0.0
Streams AQ: qmn slave idle wait 62 0 1,713 27622 0.0
Streams AQ: qmn coordinator idle 131 53 1,713 13073 0.1
SQL*Net more data from client 1,700 0 1 0 0.7
SQL*Net message to client 88,519 0 0 0 36.8
-
i meant the query statistics
-
Mathew,
Did not get your problem exactly. Why the user complains abt performance if the response time is 1.5 sec. Is this query sometimes take more times to execute? First need to know what's the problem, after that onky u can proceed. Otherwise it's pure waist of time.
-
Davery23UK,
below is the query in DEV
Code:
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.03 0.02 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.07 0.06 0 835 0 5
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.10 0.09 0 835 0 5
Misses in library cache during parse: 1
Optimizer mode: FIRST_ROWS
Parsing user id: 5
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 3 0.03 0.02 0 0 0 0
Execute 3 0.00 0.00 0 0 0 2
Fetch 1 0.07 0.06 0 835 0 5
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7 0.10 0.09 0 835 0 7
Misses in library cache during parse: 1
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 6 0.02 0.01 0 0 0 0
Execute 98 0.06 0.08 0 0 0 0
Fetch 108 0.01 0.11 15 297 0 542
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 212 0.09 0.20 15 297 0 542
Misses in library cache during parse: 6
Misses in library cache during execute: 6
3 user SQL statements in session.
98 internal SQL statements in session.
101 SQL statements in session.
********************************************************************************
Trace file: qptmd_ora_4361.trc
Trace file compatibility: 10.01.00
Sort options: default
1 session in tracefile.
3 user SQL statements in trace file.
98 internal SQL statements in trace file.
101 SQL statements in trace file.
8 unique SQL statements in trace file.
2258 lines in trace file.
0 elapsed seconds in trace file.
question: what make the total query so high compare between DEV and PRD?
thanks
-
wheres the exection plan?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|
Reply With Quote