\n
![\"cloud\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/cloud.png\")
You buy resources on the Cloud, but they are mutualized, thanks to virtualization. Are you sure you have the resources you paid for? When it comes to Oracle databases there are 3 ways to test performances:<\/p>\n.
\nYou buy resources on the Cloud, but they are mutualized, thanks to virtualization. Are you sure you have the resources you paid for? When it comes to Oracle databases there are 3 ways to test performances:<\/p>\n
In order to run SLOB you have to create a database. Let’s do that in the Oracle Cloud Services in order to evaluate the performance.
\nThis exemple was done on the EMEA cloud based in Amterdam.<\/p>\n
In Oracle Database Cloud Services<\/strong> click on Create Service<\/strong> In Oracle Compute Cloud Service<\/strong>, click on storage and Create Storage volume<\/strong><\/p>\n I want to measure IOPS so I choose the ‘optimized for latency’. Here are the different choices: <\/p>\n That’s all from the GUI, I’ll continue in command line. The VM is an OVM accessible with ssh.<\/p>\n Connect as root:<\/p>\n Check disk name:<\/p>\n Partition disk:<\/p>\n Check the partition is name:<\/p>\n Create filesystem:<\/p>\n Mount the filesystem<\/p>\n Here it is<\/p>\n It’s packaged in the image, preinstalled. Just untar it:<\/p>\n Run orainstRoot.sh and root.sh<\/p>\n added 29-AUG-15<\/strong> I remove the tar file because I need some space to create the database (limited space in trial), and don’t need to be root anymore.<\/p>\n I’ve downloaded SLOB from http:\/\/kevinclosson.net\/slob\/<\/a><\/p>\n And change instance parameters required by SLOB (they are in simple.ora)<\/p>\n I change the slob.conf to set SCALE to the maximum size that fit in \/u01<\/p>\n Now I can setup the SLOB tablespace. It’s long so I run it with nohup.<\/p>\n and compile the wait kit<\/p>\n I’ll test physical I\/O latency. and run SLOB<\/p>\n You know I use Orachrome Lighty to look at performance statistics. Here is the workload:<\/p>\n Most of I\/0 are less than 1ms. As I’m in 12c I have more details from wait event histograms in microseconds:<\/p>\n I’ll not comment the result here, that’s for future posts. To test logical reads, I want all blocks in cache, so I increase the buffer cache:<\/p>\n and reduce the SCALE to 500M in slob.conf and set UPDATE to 0%<\/p>\n I check the activity from Lighty to that I’m sure I run only in CPU:<\/p>\n If you wonder what is the brown activity before, it’s log file switch when I’ve forgot to set UPDATE_PCT to 0<\/p>\n SLOB gathers the AWR report where we check that buffer cache hit ratio is 100%, that DB CPU(s) per second is 1:<\/p>\n Again more comments on future blog post, but that looks reasonable for and Intel(R) Xeon(R) CPU E5-2690 v2 @ 3.00GHz From what I tested, a 2 OCPU VM with 128GB storage on low latency storage, the expected performance is there. You should be able to follow the same procedure explained here (setup a VM with SLOB for PIO and LIO test). I think it’s a good idea to do that when you have a new cloud service just to be sure that everything is set up as expected.<\/p>\n Big thanks to my colleague Vincent Matthey<\/a> for the setup of our trial on Cloud Services. His documentation (published soon) helped me a lot. By Franck Pachot . You buy resources on the Cloud, but they are mutualized, thanks to virtualization. Are you sure you have the resources you paid for? When it comes to Oracle databases there are 3 ways to test performances: Run an application, or a simulation of an application, such as the excellent SwingBench from […]<\/p>\n","protected":false},"author":27,"featured_media":5309,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229,59],"tags":[135,612,96,506,624],"type_dbi":[],"class_list":["post-5302","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-database-administration-monitoring","category-oracle","tag-cloud","tag-dbaas","tag-oracle","tag-pass","tag-slob"],"acf":[],"yoast_head":"\n
\n![\"CaptureCreateCloud\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/CaptureCreateCloud.jpg\")
<\/a>
\nChoose Oracle Database Cloud Service – Virtual Image<\/strong> because I’ll create the database myself with dbca.
\nChoose Oracle Database 12c Release 1<\/strong>
\nChoose Enterprise Edition – Extreme Performance<\/strong> because I want all options
\nI’ve chosen 2 OCPU (equivalent to 2 cores with 2 threads)<\/p>\nCreate the Storage Volume<\/h1>\n
![\"CaptureCloudEMEA-0011\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/CaptureCloudEMEA-0011.jpg\")
<\/a><\/p>\n![\"CaptureCloudEMEA-0010\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/CaptureCloudEMEA-0010.jpg\")
<\/a><\/p>\nCreate 100G filesystem<\/h1>\n
\n$ ssh opc@test-perf\nAuthorized uses only. All activity may be monitored and reported.\n-bash-4.1$ id\nuid=500(opc) gid=500(opc) groups=500(opc)\n-bash-4.1$ sudo su\n<\/code><\/pre>\n\n[oracle@test-perf ~]$ lsblk\nNAME MAJ:MIN RM SIZE RO TYPE MOUNTPOINT\nxvdb 202:16 0 21G 0 disk\n\u251c\u2500xvdb1 202:17 0 200M 0 part \/boot\n\u251c\u2500xvdb2 202:18 0 15.8G 0 part \/\n\u2514\u2500xvdb3 202:19 0 4G 0 part [SWAP]\nxvdc 202:32 0 100G 0 disk\n<\/code><\/pre>\n\nbash-4.1# fdisk \/dev\/xvdc\nCommand (m for help): n\nCommand action\n e extended\n p primary partition (1-4)\np\nPartition number (1-4): 1\nFirst cylinder (1-13054, default 1):\nUsing default value 1\nLast cylinder, +cylinders or +size{K,M,G} (1-13054, default 13054):\nUsing default value 13054\nCommand (m for help): w\nThe partition table has been altered!\n<\/code><\/pre>\n\nbash-4.1# lsblk\nNAME MAJ:MIN RM SIZE RO TYPE MOUNTPOINT\nxvdb 202:16 0 21G 0 disk\n\u251c\u2500xvdb1 202:17 0 200M 0 part \/boot\n\u251c\u2500xvdb2 202:18 0 15.8G 0 part \/\n\u2514\u2500xvdb3 202:19 0 4G 0 part [SWAP]\nxvdc 202:32 0 100G 0 disk\n\u2514\u2500xvdc1 202:33 0 100G 0 part\n<\/code><\/pre>\n\nbash-4.1# mkfs -t ext4 \/dev\/xvdc1\nmke2fs 1.41.12 (17-May-2010)\nFilesystem label=\nOS type: Linux\nBlock size=4096 (log=2)\nFragment size=4096 (log=2)\nStride=0 blocks, Stripe width=0 blocks\n...\nWriting inode tables: done\nCreating journal (32768 blocks): done\nWriting superblocks and filesystem accounting information: done\n...\n<\/code><\/pre>\n\nbash-4.1# mkdir \/u02 \nbash-4.1# ls -l \/dev\/disk\/by-uuid | grep xvdc1\nlrwxrwxrwx 1 root root 11 Aug 27 15:13 d8012d1f-be4c-486a-8230-f4a1631910f7 -> ..\/..\/xvdc1\nbash-4.1# echo \"UUID=d8012d1f-be4c-486a-8230-f4a1631910f7 \/u02 ext4 defaults 0 0\" >> \/etc\/fstab\nbash-4.1# mount \/u02 ; chown oracle:oinstall \/u02\n<\/code><\/pre>\n\nbash-4.1# lsblk\nNAME MAJ:MIN RM SIZE RO TYPE MOUNTPOINT\nxvdb 202:16 0 21G 0 disk\n\u251c\u2500xvdb1 202:17 0 200M 0 part \/boot\n\u251c\u2500xvdb2 202:18 0 15.8G 0 part \/\n\u2514\u2500xvdb3 202:19 0 4G 0 part [SWAP]\nxvdc 202:32 0 100G 0 disk\n\u2514\u2500xvdc1 202:33 0 100G 0 part \/u02\n<\/code><\/pre>\nInstall Oracle Software<\/h1>\n
\n\nbash-4.1# mkdir \/u01 ; chown oracle:oinstall \/u01 && cd \/u01\nbash-4.1# tar -xvf \/scratch\/db\/db12102_bits.tar.gz\ndrwxr-xr-x oracle\/oinstall 0 2015-01-16 10:10 app\/\ndrwxr-xr-x oracle\/oinstall 0 2014-07-21 18:41 app\/oracle\/\ndrwxr-xr-x oracle\/oinstall 0 2014-07-21 18:41 app\/oracle\/checkpoints\/\ndrwxr-xr-x oracle\/oinstall 0 2014-09-25 17:08 app\/oracle\/product\/\ndrwxr-xr-x oracle\/oinstall 0 2014-07-21 18:37 app\/oracle\/product\/12.1.0\/\ndrwxr-xr-x oracle\/oinstall 0 2015-04-22 14:41 app\/oracle\/product\/12.1.0\/dbhome_1\/\n...\napp\/oraInventory\/logs\/OPatch2014-09-25_02-19-13-PM.log\napp\/oraInventory\/logs\/OPatch2015-03-20_06-39-10-PM.log\napp\/oraInventory\/logs\/oraInstall2014-07-21_03-37-40PM.out\napp\/oraInventory\/logs\/OPatch2014-10-12_09-21-02-PM.log\napp\/oraInventory\/orainstRoot.sh\n<\/code><\/pre>\n\nbash-4.1# app\/oraInventory\/orainstRoot.sh\nChanging permissions of \/u01\/app\/oraInventory.\nAdding read,write permissions for group.\nRemoving read,write,execute permissions for world.\n \nChanging groupname of \/u01\/app\/oraInventory to oinstall.\nThe execution of the script is complete.\nbash-4.1# app\/oracle\/product\/12.1.0\/dbhome_1\/root.sh\nCheck \/u01\/app\/oracle\/product\/12.1.0\/dbhome_1\/install\/root_test-perf_2015-08-27_15-29-05.log for the output of root script\n<\/code><\/pre>\n
\nBy default sar is not running in this image. As root we have to:<\/p>\n\nbash-4.1# mkdir -p \/var\/log\/sa ; chmod a+r \/var\/log\/sa\n<\/code><\/pre>\n\nbash-4.1# rm \/scratch\/db\/db12102_bits.tar.gz\nbash-4.1# exit\nexit\n-bash-4.1$ exit\nlogout\nConnection to 140.86.4.11 closed.\n<\/code><\/pre>\nInstall SLOB<\/h1>\n
\n$ scp 2015.07.16.slob_2.3.0.3-1.tar.gz oracle@test-perf:\/home\/oracle\n$ ssh oracle@test-perf\nAuthorized uses only. All activity may be monitored and reported.\n[oracle@test-perf ~]$ \n[oracle@test-perf ~]$ tar -zxvf 2015.07.16.slob_2.3.0.3-1.tar.gz\n<\/code><\/pre>\nCreate SLOB database<\/h1>\n
\n[oracle@test-perf ~]$ \/u01\/app\/oracle\/product\/12.1.0\/dbhome_1\/bin\/dbca -createDatabase -templateName General_Purpose.dbc -gdbName SLOB -sid SLOB -sysPassword oracle -systemPassword oracle -storageType FS -listeners LISTENER -sampleSchema false -databaseType MULTIPURPOSE -silent\n<\/code><\/pre>\n\n[oracle@test-perf ~]$ . oraenv <<< SLOB\n[oracle@test-perf ~]$ cd SLOB\n[oracle@test-perf SLOB]$ sqlplus \/ as sysdba\nSQL> alter system reset sga_target;\nSQL> create pfile from spfile;\nSQL> shutdown immediate;\nSQL> host grep -v \/data simple.ora >> $ORACLE_HOME\/dbs\/initSLOB.ora\nSQL> create spfile from pfile;\nSQL> startup\nSQL> create bigfile tablespace IOPS datafile '\/u02\/IOPS.dbf' size 10M autoextend on next 10M maxsize unlimited;\n<\/code><\/pre>\n\n[oracle@test-perf SLOB]$ head slob.conf\nUPDATE_PCT=25\nRUN_TIME=300\nWORK_LOOP=0\nSCALE=95000M\nWORK_UNIT=64\nREDO_STRESS=LITE\nLOAD_PARALLEL_DEGREE=4\n<\/code><\/pre>\n\nnohup sh setup.sh IOPS 1\n<\/code><\/pre>\n\n[oracle@test-perf SLOB]$ ( cd .\/wait_kit ; make ; )\nrm -fr *.o mywait trigger create_sem\ncc -c -o mywait.o mywait.c\ncc -o mywait mywait.o\ncc -c -o trigger.o trigger.c\ncc -o trigger trigger.o\ncc -c -o create_sem.o create_sem.c\ncc -o create_sem create_sem.o\ncp mywait trigger create_sem ..\/\nrm -fr *.o\n<\/code><\/pre>\nRun PIOs test<\/h1>\n
\nI restart the instance to reset counters (especially the V$EVENT_HISTOGRAM_MICRO because it’s not snapshotted by AWR)<\/p>\n\n[oracle@test-perf SLOB]$ sqlplus \/ as sysdba\nSQL> startup force\n<\/code><\/pre>\n\n[oracle@test-perf SLOB]$ sh runit.sh 1\nNOTIFY : 2015.08.28-04:05:33 :\nNOTIFY : 2015.08.28-04:05:33 : Conducting SLOB pre-test checks.\n \nUPDATE_PCT: 25\nRUN_TIME: 300\nWORK_LOOP: 0\nSCALE: 90000M (11520000 blocks)\nWORK_UNIT: 64\nREDO_STRESS: LITE\nHOT_SCHEMA_FREQUENCY: 0\nDO_HOTSPOT: FALSE\nHOTSPOT_MB: 8\nHOTSPOT_OFFSET_MB: 16\nHOTSPOT_FREQUENCY: 3\nTHINK_TM_FREQUENCY: 0\nTHINK_TM_MIN: .1\nTHINK_TM_MAX: .5\n...\nNOTIFY : 2015.08.28-04:11:02 : SLOB test is complete.\n<\/code><\/pre>\n![\"CaptureLighty\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/CaptureLighty.jpg\")
<\/a><\/p>\n\ncolumn event format a30 \nset linesize 120 pagesize 1000\nselect event,wait_time_micro,wait_count,wait_time_format from v$event_histogram_micro where event like 'db file sequential read' order by event,wait_time_micro;\n<\/code><\/pre>\n\nEVENT WAIT_TIME_MICRO WAIT_COUNT WAIT_TIME_FORMAT\n------------------------------ --------------- ---------- ------------------------------\ndb file sequential read 1 0 1 microsecond\ndb file sequential read 2 0 2 microseconds\ndb file sequential read 4 0 4 microseconds\ndb file sequential read 8 0 8 microseconds\ndb file sequential read 16 0 16 microseconds\ndb file sequential read 32 0 32 microseconds\ndb file sequential read 64 0 64 microseconds\ndb file sequential read 128 0 128 microseconds\ndb file sequential read 256 27 256 microseconds\ndb file sequential read 512 390619 512 microseconds\ndb file sequential read 1024 172914 1 millisecond\ndb file sequential read 2048 3387 2 milliseconds\ndb file sequential read 4096 1709 4 milliseconds\ndb file sequential read 8192 1237 8 milliseconds\ndb file sequential read 16384 96 16 milliseconds\ndb file sequential read 32768 56 32 milliseconds\ndb file sequential read 65536 4 65 milliseconds\ndb file sequential read 131072 0 131 milliseconds\ndb file sequential read 262144 1 262 milliseconds\n<\/code><\/pre>\n
\nI can confirm that filesystemio_options=setall because filesystem cache would be below 100us.<\/p>\nRun LIOs test<\/h1>\n
\nSQL> show parameter db_cache_size\n \nNAME TYPE VALUE\n------------------------------------ ----------- ------------------------------\ndb_cache_size big integer 112M\n\n \nSQL> alter system set db_cache_size=2048M scope=spfile;\nSystem altered.\n \nSQL> startup force;\n<\/code><\/pre>\n\n[oracle@test-perf SLOB]$ sh runit.sh 1\nNOTIFY : 2015.08.28-04:24:38 :\nNOTIFY : 2015.08.28-04:24:38 : Conducting SLOB pre-test checks.\n \nUPDATE_PCT: 0\nRUN_TIME: 300\nWORK_LOOP: 0\nSCALE: 500M (64000 blocks)\nWORK_UNIT: 64\nREDO_STRESS: LITE\nHOT_SCHEMA_FREQUENCY: 0\nDO_HOTSPOT: FALSE\nHOTSPOT_MB: 8\nHOTSPOT_OFFSET_MB: 16\nHOTSPOT_FREQUENCY: 3\nTHINK_TM_FREQUENCY: 0\nTHINK_TM_MIN: .1\nTHINK_TM_MAX: .5\n...\nNOTIFY : 2015.08.28-04:36:15 : SLOB test is complete.\n<\/code><\/pre>\n![\"CaptureLighty2\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/CaptureLighty2.jpg\")
<\/a><\/p>\n\nLoad Profile Per Second Per Transaction Per Exec Per Call\n~~~~~~~~~~~~~~~ --------------- --------------- --------- ---------\n DB Time(s): 1.0 13.1 0.00 2.92\n DB CPU(s): 1.0 13.1 0.00 2.92\n Background CPU(s): 0.0 0.0 0.00 0.00\n Redo size (bytes): 17,786.6 233,499.7\n Logical read (blocks): 587,373.4 7,710,960.9\n Block changes: 73.5 965.0\n Physical read (blocks): 0.5 5.9\nPhysical write (blocks): 0.3 3.9\n Read IO requests: 0.5 5.9\n Write IO requests: 0.2 2.1\n Read IO (MB): 0.0 0.1\n Write IO (MB): 0.0 0.0\n IM scan rows: 0.0 0.0\nSession Logical Read IM:\n User calls: 0.3 4.5\n Parses (SQL): 4.9 64.0\n Hard parses (SQL): 0.0 0.3\n SQL Work Area (MB): 0.1 1.9\n Logons: 0.1 0.8\n Executes (SQL): 8,888.3 116,683.9\n Rollbacks: 0.0 0.0\n Transactions: 0.1\n\nInstance Efficiency Percentages (Target 100%)\n~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~\n Buffer Nowait %: 100.00 Redo NoWait %: 100.00\n Buffer Hit %: 100.00 In-memory Sort %: 100.00\n Library Hit %: 100.00 Soft Parse %: 99.52\n Execute to Parse %: 99.95 Latch Hit %: 100.00\nParse CPU to Parse Elapsd %: 80.00 % Non-Parse CPU: 99.99\n Flash Cache Hit %: 0.00\n<\/code><\/pre>\n
\nCompare with physical CPU: https:\/\/twitter.com\/kevinclosson\/status\/636905086338342913<\/a><\/p>\nSo what?<\/h1>\n
\nAnd to @kevinclosson<\/a> @BertrandDrouvot<\/a> @OracleSK<\/a> for their help interpreting the performance results.<\/p>\n","protected":false},"excerpt":{"rendered":"