Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> Oracle StatsPack Query, Disk file operations I/O AND recursive cpu usage
hy.qube
post Jan 17 2017, 11:11 AM
Post #1


Advanced Member
***

Group: Members
Posts: 84
Joined: 17-May 14
From: London
Member No.: 49,627



Hi All,

I have a database that is now taking in some instances 10 times longer to run exactly the same set of scripts that were run in December 2016 to populate tables.

I have created snapshots at various points and generated the logs and noticed the Disk file operations I/O AND recursive cpu usage as the highest top 10 times events.

I have had a quick google and not 100% sure what is the cause of this.

CPU/Memory and Disk wise we dont get any issues from the local host or the SAN. I am therefore stuck and wondering if anyone can provide some guidance on this.

The TOP 10 Wait Events are:

Snapshot 1

STATSPACK report for

Database DB Id Instance Inst Num Startup Time Release RAC
~~~~~~~~ ----------- ------------ -------- --------------- ----------- ---
1687038294 hznmig2 1 17-Jan-17 14:27 12.1.0.2.0 NO

Host Name Platform CPUs Cores Sockets Memory (G)
~~~~ ---------------- ---------------------- ----- ----- ------- ------------
G-QUBEMIGDB2 Microsoft Windows x86 4 4 2 9.0

Snapshot Snap Id Snap Time Sessions Curs/Sess Comment
~~~~~~~~ ---------- ------------------ -------- --------- ------------------
Begin Snap: 23 17-Jan-17 14:38:20 89 2.9
End Snap: 33 17-Jan-17 14:47:01 105 2.9
Elapsed: 8.68 (mins) Av Act Sess: 0.0
DB time: 0.42 (mins) DB CPU: 0.17 (mins)

Cache Sizes Begin End
~~~~~~~~~~~ ---------- ----------
Buffer Cache: 3,024M Std Block Size: 8K
Shared Pool: 672M Log Buffer: 12,888K

Load Profile Per Second Per Transaction Per Exec Per Call
~~~~~~~~~~~~ ------------------ ----------------- ----------- -----------
DB time(s): 0.1 0.0 0.00 0.00
DB CPU(s): 0.0 0.0 0.00 0.00
Redo size: 35,450.0 3,680.6
Logical reads: 216.2 22.4
Block changes: 92.7 9.6
Physical reads: 2.4 0.3
Physical writes: 0.0 0.0
User calls: 13.3 1.4
Parses: 5.6 0.6
Hard parses: 0.3 0.0
W/A MB processed: 0.6 0.1
Logons: 0.1 0.0
Executes: 19.3 2.0
Rollbacks: 0.0 0.0
Transactions: 9.6

Instance Efficiency Indicators
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 98.87 Optimal W/A Exec %: 100.00
Library Hit %: 96.73 Soft Parse %: 94.61
Execute to Parse %: 70.99 Latch Hit %: 99.99
Parse CPU to Parse Elapsd %: 27.38 % Non-Parse CPU: 95.47

Shared Pool Statistics Begin End
------ ------
Memory Usage %: 58.22 59.95
% SQL with executions>1: 53.85 53.66
% Memory for SQL w/exec>1: 53.82 54.31

Top 10 Timed Events
~~~~~~~~~~~~~~~~~~~ Wait
Event Time (s) % TOTAL TIME
-------------------------------------------- ------------ ------------
Disk file operations I/O 17 43.09
recursive cpu usage 7 17.34
db file sequential read 5 12.97
log file sync 5 12.08
cpu other 3 6.62
control file sequential read 1 2.91
db file scattered read 1 2.33
parse time cpu 0 1.14
oracle thread bootstrap 0 .7
control file parallel write 0 .69

Top 10 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time
----------------------------------------- ------------ ----------- ------ ------
Disk file operations I/O 116 17 150 43.1
CPU time 10 25.1
db file sequential read 717 5 7 13.0
log file sync 5,020 5 1 12.1
control file sequential read 3,929 1 0 2.9
db file scattered read 110 1 9 2.3
oracle thread bootstrap 23 0 12 .7
control file parallel write 173 0 2 .7
db file parallel read 2 0 11 .1
SQL*Net message to client 6,806 0 0 .1
-------------------------------------------------------------


Snapshot 2:

STATSPACK report for

Database DB Id Instance Inst Num Startup Time Release RAC
~~~~~~~~ ----------- ------------ -------- --------------- ----------- ---
1687038294 hznmig2 1 17-Jan-17 14:27 12.1.0.2.0 NO

Host Name Platform CPUs Cores Sockets Memory (G)
~~~~ ---------------- ---------------------- ----- ----- ------- ------------
G-QUBEMIGDB2 Microsoft Windows x86 4 4 2 9.0

Snapshot Snap Id Snap Time Sessions Curs/Sess Comment
~~~~~~~~ ---------- ------------------ -------- --------- ------------------
Begin Snap: 42 17-Jan-17 15:00:01 110 3.0
End Snap: 59 17-Jan-17 15:51:00 113 3.1
Elapsed: 50.98 (mins) Av Act Sess: 0.0
DB time: 2.18 (mins) DB CPU: 1.24 (mins)

Cache Sizes Begin End
~~~~~~~~~~~ ---------- ----------
Buffer Cache: 3,024M Std Block Size: 8K
Shared Pool: 672M Log Buffer: 12,888K

Load Profile Per Second Per Transaction Per Exec Per Call
~~~~~~~~~~~~ ------------------ ----------------- ----------- -----------
DB time(s): 0.0 0.0 0.00 0.00
DB CPU(s): 0.0 0.0 0.00 0.00
Redo size: 35,260.6 5,612.6
Logical reads: 1,084.7 172.7
Block changes: 190.5 30.3
Physical reads: 4.7 0.7
Physical writes: 3.9 0.6
User calls: 19.7 3.1
Parses: 6.9 1.1
Hard parses: 0.5 0.1
W/A MB processed: 0.3 0.1
Logons: 0.1 0.0
Executes: 44.9 7.2
Rollbacks: 0.0 0.0
Transactions: 6.3

Instance Efficiency Indicators
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 99.58 Optimal W/A Exec %: 100.00
Library Hit %: 97.87 Soft Parse %: 92.16
Execute to Parse %: 84.73 Latch Hit %: 100.00
Parse CPU to Parse Elapsd %: 73.58 % Non-Parse CPU: 87.47

Shared Pool Statistics Begin End
------ ------
Memory Usage %: 60.39 69.88
% SQL with executions>1: 61.21 65.46
% Memory for SQL w/exec>1: 70.15 72.51

Top 10 Timed Events
~~~~~~~~~~~~~~~~~~~ Wait
Event Time (s) % TOTAL TIME
-------------------------------------------- ------------ ------------
Disk file operations I/O 64 33.95
recursive cpu usage 48 25.29
log file sync 19 10.18
db file sequential read 18 9.67
cpu other 14 7.36
parse time cpu 9 4.68
control file sequential read 5 2.89
db file scattered read 4 2.17
control file parallel write 2 1.09
oracle thread bootstrap 1 .72

Top 10 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time
----------------------------------------- ------------ ----------- ------ ------
CPU time 70 37.0
Disk file operations I/O 494 64 129 33.7
log file sync 15,667 19 1 10.1
db file sequential read 2,853 18 6 9.6
control file sequential read 16,840 5 0 2.9
db file scattered read 642 4 6 2.2
control file parallel write 2,600 2 1 1.1
oracle thread bootstrap 155 1 9 .7
LGWR any worker group 3 1 318 .5
LGWR worker group ordering 3 1 267 .4
-------------------------------------------------------------


I have attached the logs to the call too. I tried passing this into the online StatsPack transalter but I get errors so assume it's not 12c supported yet?

http://burleson-dba.com/SP/

Thanks and hope everyone had a good Christmas and New Year.

Hakan
Attached File(s)
Attached File  hakan_2.zip ( 64.69K ) Number of downloads: 1
 
Go to the top of the page
 
+Quote Post
burleson
post Jan 19 2017, 10:38 AM
Post #2


Advanced Member
***

Group: Members
Posts: 13,476
Joined: 26-January 04
Member No.: 13



Hi Hakan,

>> I have a database that is now taking in some instances 10 times longer to run exactly the same set of scripts that were run in December 2016 to populate tables.


See my notes here on tuning inserts:

http://www.dba-oracle.com/t_insert_tuning.htm

Make sure to drop/disable all constraints and indexes before the load . . .

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

>> statspack analyzer

Yeah, I have not updated this for 12c, but you can still read it manually.

There is a whole chapter on this in the tuning book I sent you.

Good Luck!









--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
hy.qube
post Feb 23 2017, 01:38 AM
Post #3


Advanced Member
***

Group: Members
Posts: 84
Joined: 17-May 14
From: London
Member No.: 49,627



Thanks Don,

I have managed to resolve this by looking through some online sources and the book you sent.

Sorry for the late reply.

Thanks
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 25th July 2017 - 07:45 AM