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> 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
