Jared,
The 20% differential is surprising - and the absence of write information left
a hole that might help to explain it.
Given that the CDB reports fill the gap, a couple of details I would want to
check are:
a) is there a difference in the pattern of writes with different substrates -
e.g. average blocks written per I/O request
b) is the number of redo writes significantly different
c) is there a dramatic difference in redo wastage
I find it hard to believe that a change in code-path could produce such a
difference - somewhere there are likely to be some figures telling us that one
system is doing more work than the other to achieve the same ends - and there
may be something in the CDB stats that give us a clue.
Regards
Jonathan Lewis
________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf
of Jared Still <jkstill@xxxxxxxxx>
Sent: 26 January 2018 17:36:05
To: Oracle-L Freelists
Subject: Re: ASM Filter Driver Performance
Hello all,
Hopefully this will clear up the writes issue a bit.
These tests were all performed on Oracle 12.2 with a single PDB.
Why did I choose to use PDB? Because the legacy configuration is deprecated.
Some of you noticed that there were no writes appearing in the AWR report.
I also noticed it, but failed to call it out explicitly.
As there was much redo appearing in the reports, I chalked it up to an AWR
reporting bug.
The AWR reporting system is much revised for 12.2 so that metrics can be
gathered at the PDB level.
Certainly this is not the first AWR Bug. Some of you may recall that many
(all?) CPU metrics are missing from AWR in 11.2. Bug.
I also confirmed the write activity via iostat. If you are familiar with SLOB
then you may recall that an iostat report is run for the period of the test.
Yes, there were plenty of writes.
I should have explained this before the sharp eyed Oracle-L crowd got hold of
it.
Anyway, I have rerun one of the tests, but this time modified the runit.sh
script a bit to also create CDB level snapshots before and after the SLOB run..
=== PDB Report ===
Host Name Platform CPUs Cores Sockets Memory(GB)
---------------- -------------------------------- ---- ----- ------- ----------
ora12cR2-asm.jks Linux x86 64-bit 2 2 1 7.80
Snap Id Snap Time Sessions Curs/Sess
--------- ------------------- -------- ---------
Begin Snap: 11 26-Jan-18 08:57:20 5 1.6
End Snap: 12 26-Jan-18 09:07:23 1 6.0
Elapsed: 10.06 (mins)
DB Time: 40.04 (mins)
Container DB Id Container Name Open Time
--------------- --------------- ---------------
859891322 P1 24-Jan-18 06:01
Host Name Platform CPUs Cores Sockets Memory(GB)
---------------- -------------------------------- ---- ----- ------- ----------
ora12cR2-asm.jks Linux x86 64-bit 2 2 1 7.80
Snap Id Snap Time Sessions Curs/Sess
--------- ------------------- -------- ---------
Begin Snap: 11 26-Jan-18 08:57:20 5 1.6
End Snap: 12 26-Jan-18 09:07:23 1 6.0
Elapsed: 10.06 (mins)
DB Time: 40.04 (mins)
Load Profile Per Second Per Transaction Per Exec Per Call
~~~~~~~~~~~~~~~ --------------- --------------- --------- ---------
DB Time(s): 4.0 0.0 0.01 42.90
DB CPU(s): 0.7 0.0 0.00 7.76
Background CPU(s): 0.0 0.0 0.00 0.00
Redo size (bytes): 1,194,250.6 8,774.5
Logical read (blocks): 9,903.1 72.8
Block changes: 8,766.3 64.4
Physical read (blocks): 6,800.2 50.0
Physical write (blocks): 0.0 0.0
Read IO requests: 6,799.8 50.0
Write IO requests: 0.0 0.0
Read IO (MB): 53.1 0.4
Write IO (MB): 0.0 0.0
IM scan rows: 0.0 0.0
Session Logical Read IM: 0.0 0.0
User calls: 0.1 0.0
Parses (SQL): 2.2 0.0
Hard parses (SQL): 0.5 0.0
SQL Work Area (MB): 0.2 0.0
Logons: 0.0 0.0
Executes (SQL): 294.1 2.2
Rollbacks: 0.0 0.0
Transactions: 136.1
=== CDB Report ===
Host Name Platform CPUs Cores Sockets Memory(GB)
---------------- -------------------------------- ---- ----- ------- ----------
ora12cR2-asm.jks Linux x86 64-bit 2 2 1 7.80
Snap Id Snap Time Sessions Curs/Sess PDBs
--------- ------------------- -------- --------- -----
Begin Snap: 210 26-Jan-18 08:57:21 57 .7 1
End Snap: 211 26-Jan-18 09:07:25 53 .6 1
Elapsed: 10.06 (mins)
DB Time: 40.11 (mins)
Top ADDM Findings by Average Active Sessions
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Finding Name Avg act Percen Task Name
---------------------------------------- ------- ------ ------------------------
Top SQL Statements 3.98 98.31 ADDM:2577183038_1_211
Top Segments by "User I/O" and "Cluster" 3.98 81.39 ADDM:2577183038_1_211
Load Profile Per Second Per Transaction Per Exec Per Call
~~~~~~~~~~~~~~~ --------------- --------------- --------- ---------
DB Time(s): 4.0 0.0 0.01 2.78
DB CPU(s): 0.7 0.0 0.00 0.50
Background CPU(s): 0.5 0.0 0.00 0.00
Redo size (bytes): 1,280,263.5 9,402.6
Logical read (blocks): 10,080.1 74.0
Block changes: 8,786.3 64.5
Physical read (blocks): 6,806.5 50.0
Physical write (blocks): 3,980.9 29.2
Read IO requests: 6,806.1 50.0
Write IO requests: 3,449.2 25.3
Read IO (MB): 53.2 0.4
Write IO (MB): 31.1 0.2
IM scan rows: 0.0 0.0
Session Logical Read IM: 0.0 0.0
User calls: 1.4 0.0
Parses (SQL): 8.6 0.1
Hard parses (SQL): 1.8 0.0
SQL Work Area (MB): 0.1 0.0
Logons: 0.3 0.0
Executes (SQL): 343.4 2.5
Rollbacks: 0.0 0.0
Transactions: 136.2
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist
Principal Consultant at Pythian
Pythian Blog http://www.pythian.com/blog/author/still/
Github: https://github.com/jkstill
On Thu, Jan 25, 2018 at 12:47 PM, Jared Still
<jkstill@xxxxxxxxx<mailto:jkstill@xxxxxxxxx>> wrote:
Hello,
Some of you may find this interesting.
As much of a pain as it is to setup ASM Filter Driver 12.2, I have now done so
several times just so I could see how it works and compare it to udev and
ASMLib.
What I found was not what I was looking for: 20% better IO performance over
ASMLib.
https://blog.pythian.com/improve-oracle-performance-asm-filter-driver/
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist
Principal Consultant at Pythian
Pythian Blog http://www.pythian.com/blog/author/still/
Github: https://github.com/jkstill
--
//www.freelists.org/webpage/oracle-l
