Jonathan,
I would love to be able to supply answers to all the questions that have
come up here.
A major performance comparisons was not really the goal of my testing; it
was a bit surprising to me to see that large IO improvements with ASM
Filter driver.
The test shown here were from runs representing reasonable loads.
There were also subsequent runs with unreasonable loads there much less
work was done due to extreme resource contention.
The performance different held true regardless of load.
If I did not have an obligation to prepare quite a bit of other information
for an upcoming presentation I would continue delving into the performance
aspect.
Until other work is done however, I will have to leave this bit as is.
Jared
On Sat, Jan 27, 2018 at 5:31 AM, Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx
wrote:
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
