Search

OakieTags

Who's online

There are currently 0 users and 40 guests online.

Recent comments

Oakies Blog Aggregator

9th Circuit Court Ruling 3-0

Little did I know this building that captured my visual attention and imagination so many times walking to work over the last 6 months would play a historic roll in the current political climate.

Here is a picture of the US District Court House from recent articles

Screen Shot 2017-02-11 at 9.19.43 PM

 

And here are some of my iPhone shots over the last few months with some Instagram filtering mixed in :)

Screen Shot 2017-02-10 at 8.17.09 AM Screen Shot 2017-02-10 at 8.15.09 AM

Screen Shot 2017-02-10 at 8.15.01 AMScreen Shot 2017-02-10 at 8.16.58 AM Screen Shot 2017-02-10 at 8.16.52 AM Screen Shot 2017-02-10 at 8.16.21 AM Screen Shot 2017-02-10 at 8.15.54 AM Screen Shot 2017-02-10 at 8.14.32 AM Screen Shot 2017-02-10 at 8.14.24 AM Screen Shot 2017-02-10 at 8.14.17 AM Screen Shot 2017-02-10 at 8.14.09 AM Screen Shot 2017-02-10 at 8.13.59 AM Screen Shot 2017-02-10 at 8.13.41 AM Screen Shot 2017-02-10 at 8.13.32 AM Screen Shot 2017-02-10 at 8.13.18 AM

Provisioning a VFile App to Connect to a VDB

I ran across an article from 2013 from Straight Talk on Agile Development by Alex Kuznetsov and it reminded me how long we’ve been battling for easier ways of doing agile in a RDBMS environments.

Getting comfortable with a virtualized environment can be an odd experience for most DBAs, but as soon as you recognize how similar it is to a standard environment, we stop over-thinking it and it makes it quite simple to then implement agile with even petabytes of data in an relational environment without using slow and archaic processes.

The second effect of this is to realize that we may start to acquire secondary responsibilities and take on ensuring that all tiers of the existing environment are consistently developed and tested, not just the database.

A Virtual Can Be Virtualized

Don’t worry-  I’m going to show you that its not that difficult and virtualization makes it really easy to do all of this, especially when you have products like Delphix to support your IT environment. For our example, we’re going to use our trusty AWS Trial environment and we have already provisioned a virtual QA database.  We want to create a copy of our development virtualized web application to test some changes we’ve made and connect it to this new QA VDB.

From the Delphix Admin Console, go to Dev Copies and expand to view those available.  Click on Employee Web Application, Dev VFiles Running.  Under TimeFlow, you will see a number of snapshots that have been taken on a regular interval.  Click on one and click on Provision.

https://i2.wp.com/dbakevlar.com/wp-content/uploads/2017/02/Screen-Shot-2... 300w, https://i2.wp.com/dbakevlar.com/wp-content/uploads/2017/02/Screen-Shot-2... 768w, https://i2.wp.com/dbakevlar.com/wp-content/uploads/2017/02/Screen-Shot-2... 1200w, https://i2.wp.com/dbakevlar.com/wp-content/uploads/2017/02/Screen-Shot-2... 1800w" sizes="(max-width: 600px) 100vw, 600px" data-recalc-dims="1" />

Now this is where you need the information about your virtual database that you wish to connect to:

  1.  You will want to switch from provisioning to the source to the Linux Target.

Currently the default is to connect to the existing development database, but we want to connect to the new QA we wish to test on.  You can ssh as delphix@ to connect to and gather this information.

2.  Gathering Information When You Didn’t Beforehand

I’ve created a new VDB to test against, with the idea, that I wouldn’t want to confiscate an existing VDB from any of my developers or testers.  The new VDB is called EmpQAV1.  Now, if you’re like me, you’re not going to have remembered to grab the info about this new database before you went into the wizard to begin the provisioning.  No big deal, we’ll just log into the target and get it:

[delphix@linuxtarget ~]$ . 11g.env
[delphix@linuxtarget ~]$ echo $ORACLE_HOME
#ff0000;">/u01/app/oracle/product/11.2.0.4/db_1
[delphix@linuxtarget ~]$ ps -ef | grep pmon
delphix  14816     1  0 17:23 ?        00:00:00 ora_pmon_devdb
delphix  14817     1  0 17:23 ?        00:00:00 ora_pmon_qadb
delphix  17832     1  0 17:32 ?        00:00:00 #ff0000;">ora_pmon_EmpQAV1
delphix  19935 19888  0 18:02 pts/0    00:00:00 grep pmon

I can now set my ORACLE_SID:

[delphix@linuxtarget ~]$ export ORACLE_SID=EmpQAV1

Now, let’s gather the rest of the information we’ll need to connect to the new database by connecting to the database and gathering what we need.

[delphix@linuxtarget ~]$ lsnrctl services
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 10-FEB-2017 18:13:06
Copyright (c) 1991, 2013, Oracle.  All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=#ff0000;">1521))
Services Summary...
Service "#ff0000;">EmpQAV1" has 1 instance(s).
  Instance "EmpQAV1", status READY, has 1 handler(s) for this service...

Provision Your VFile

Fill in all the values required in the next section of the provisioning setup:

https://i0.wp.com/dbakevlar.com/wp-content/uploads/2017/02/Screen-Shot-2... 300w, https://i0.wp.com/dbakevlar.com/wp-content/uploads/2017/02/Screen-Shot-2... 768w, https://i0.wp.com/dbakevlar.com/wp-content/uploads/2017/02/Screen-Shot-2... 1482w, https://i0.wp.com/dbakevlar.com/wp-content/uploads/2017/02/Screen-Shot-2... 1200w" sizes="(max-width: 600px) 100vw, 600px" data-recalc-dims="1" />

Click Next and add any requirements to match your vfile configuration that you had for the existing environment.  For this one, there aren’t any, (additional NFS Mount points, etc.)  Then click Next and Finish.

The VFile creation should take a couple minutes max and you should now see an environment that looks similar to the following:

https://i1.wp.com/dbakevlar.com/wp-content/uploads/2017/02/Screen-Shot-2... 300w, https://i1.wp.com/dbakevlar.com/wp-content/uploads/2017/02/Screen-Shot-2... 768w, https://i1.wp.com/dbakevlar.com/wp-content/uploads/2017/02/Screen-Shot-2... 1200w, https://i1.wp.com/dbakevlar.com/wp-content/uploads/2017/02/Screen-Shot-2... 1800w" sizes="(max-width: 600px) 100vw, 600px" data-recalc-dims="1" />

This is a fully functional copy of your web application, created from another virtual copy that can test against a virtual database, ensuring that all aspects of a development project are tested thoroughly before releasing to production!

Why would you choose to do anything less?

 

 

 



Tags:  ,


Del.icio.us



Facebook

TweetThis

Digg

StumbleUpon




Copyright © DBA Kevlar [Provisioning a VFile App to Connect to a VDB], All Right Reserved. 2017.

The post Provisioning a VFile App to Connect to a VDB appeared first on DBA Kevlar.

TCP WMEM Fault in Delphix AWS Target

So you thought you were finished configuring your AWS target, eh?  I already posted a previous time on how to address a fault with the RMEM, but now we’re onto the WMEM.  Wait, WM-what?

No, I fear a DBAs work is never over and when it comes to the cloud, our skill set has just expanded from what it was when we worked on-premise!

Our trusty Delphix Admin Console keeps track of settings on all our sources and targets, informing us when the settings aren’t set to what is recommended, so that we’ll be aware of any less than optimal parameters that could effect performance.

https://i2.wp.com/dbakevlar.com/wp-content/uploads/2017/01/Screen-Shot-2... 300w" sizes="(max-width: 312px) 100vw, 312px" data-recalc-dims="1" />

As we address latency in cloud environments, network settings become more important.

How WMEM Differs from RMEM

RMEM= receive

WMEM=send

Where RMEM is quite easy to remember as receive settings, we get to thank

 

As root, we’ll add another line to the sysctl.conf file to reflect values other than defaults:

$ echo 'net.ipv4.tcp_wmem= 102404194304 12582912' >> /etc/sysctl.conf

Reload the values into the system:

$ sysctl -p /etc/sysctl.conf

Verify the settings are now active:

$ sysctl -a | grep net.ipv4.tcp_wmem

net.ipv4.tcp_wmem = 10240 4194304 12582912

That’s all there is to it.  Now you can mark the fault as resolved in the Delphix Admin Console.



Tags:  , ,


Del.icio.us



Facebook

TweetThis

Digg

StumbleUpon




Copyright © DBA Kevlar [TCP WMEM Fault in Delphix AWS Target], All Right Reserved. 2017.

The post TCP WMEM Fault in Delphix AWS Target appeared first on DBA Kevlar.

SLOB Use Cases By Industry Vendors. Learn SLOB, Speak The Experts’ Language.

For general SLOB information, please visit: https://kevinclosson.net/slob.

Introduction

This is just a quick blog entry to showcase a few of the publications from IT vendors showcasing SLOB. SLOB allows performance engineers to speak in short sentences. As I’ve pointed out before, SLOB is not used to test how well Oracle handles transaction. If you are worried that Oracle cannot handle transactions then you have bigger problems than what can be tested with SLOB. SLOB is how you test whether–or how well–a platform can satisfy SQL-driven database physical I/O.

SLOB testing is not at all like using a transactional test kit (e.g., TPC-C). Transactional test kits are, first and foremost, Oracle intrinsic code testing kits (the code of the server itself). Here again I say if you are questioning (testing) Oracle code then something is really wrong. Sure, transactional kits can involve physical I/O but the ratio of CPU utilization to physical I/O is generally not conducive to testing even mid-range modern storage without massive compute capability.

Recent SLOB testing on top-bin Broadwell Xeons (E5-2699v4) show that each core is able to drive over 50,000 physical read IOPS (db file sequential read).  On the contrary 50,000 IOPS is about what one would expect from over a dozen of such cores with a transactional test kit because the CPU is being used to execute Oracle intrinsic transaction code paths and, indeed, some sundry I/O.

SLOB Use Cases By IT Vendors

The following are links and screenshots from the likes of DellEMC, HPE, Nutanix, NetApp, Pure Storage, IBM and Nimble Storage showing some of their SLOB use cases. Generally speaking, if you are shopping for modern storage–optimized for Oracle Database–you should expect to see SLOB results.

The first case is VMware showcasing VSAN with Oracle using SLOB at: https://blogs.vmware.com/apps/2016/08/oracle-12c-oltp-dss-workloads-flash-virtual-san-6-2.html.

#000000;" src="https://kevinclosson.files.wordpress.com/2017/02/screen-shot-2017-02-10-at-11-48-06-am.png?w=500&h=287" alt="screen-shot-2017-02-10-at-11-48-06-am" width="500" height="287" srcset="https://kevinclosson.files.wordpress.com/2017/02/screen-shot-2017-02-10-at-11-48-06-am.png?w=500&h=287 500w, https://kevinclosson.files.wordpress.com/2017/02/screen-shot-2017-02-10-... 150w, https://kevinclosson.files.wordpress.com/2017/02/screen-shot-2017-02-10-... 300w, https://kevinclosson.files.wordpress.com/2017/02/screen-shot-2017-02-10-... 768w, https://kevinclosson.files.wordpress.com/2017/02/screen-shot-2017-02-10-... 988w" sizes="(max-width: 500px) 100vw, 500px" />

VMware Using SLOB to Assess VSAN Suitability for Oracle Database

VMware has an additional publication showing SLOB results at the following URL: https://blogs.vmware.com/virtualblocks/2016/08/22/oracle-12c-oltp-dss-workloads-flash-virtual-san-6-2/

The VCE Solution guide for consolidating databases includes proof points based on SLOB testing at the following link: http://www.vce.com/asset/documents/oracle-sap-sql-on-vblock-540-solutions-guide.pdf.

#000000;" src="https://kevinclosson.files.wordpress.com/2017/02/screen-shot-2017-02-10-at-4-57-10-pm.png?w=500&h=161" alt="screen-shot-2017-02-10-at-4-57-10-pm" width="500" height="161" srcset="https://kevinclosson.files.wordpress.com/2017/02/screen-shot-2017-02-10-at-4-57-10-pm.png?w=500&h=161 500w, https://kevinclosson.files.wordpress.com/2017/02/screen-shot-2017-02-10-... 1000w, https://kevinclosson.files.wordpress.com/2017/02/screen-shot-2017-02-10-... 150w, https://kevinclosson.files.wordpress.com/2017/02/screen-shot-2017-02-10-... 300w, https://kevinclosson.files.wordpress.com/2017/02/screen-shot-2017-02-10-... 768w" sizes="(max-width: 500px) 100vw, 500px" />

VCE Solution Guide Using SLOB Proof Points

 

Next is Nutanix with this publication: https://next.nutanix.com/t5/Server-Virtualization/Oracle-SLOB-Performance-on-Nutanix-All-Flash-Cluster/m-p/12997

#000000;" src="https://kevinclosson.files.wordpress.com/2017/02/screen-shot-2017-02-10-at-11-50-33-am.png?w=500&h=327" alt="Figure 2: Nutanix Using SLOB for Platform Suitability Testing" width="500" height="327" srcset="https://kevinclosson.files.wordpress.com/2017/02/screen-shot-2017-02-10-at-11-50-33-am.png?w=500&h=327 500w, https://kevinclosson.files.wordpress.com/2017/02/screen-shot-2017-02-10-... 150w, https://kevinclosson.files.wordpress.com/2017/02/screen-shot-2017-02-10-... 300w, https://kevinclosson.files.wordpress.com/2017/02/screen-shot-2017-02-10-... 768w, https://kevinclosson.files.wordpress.com/2017/02/screen-shot-2017-02-10-... 996w" sizes="(max-width: 500px) 100vw, 500px" />

Nutanix Using SLOB for Platform Suitability Testing

NetApp has a lot of articles showcasing SLOB results. The first is at the following link: https://www.netapp.com/us/media/nva-0012-design.pdf.

#000000;" src="https://kevinclosson.files.wordpress.com/2017/02/screen-shot-2017-02-10-at-12-08-22-pm.png?w=500&h=235" alt="screen-shot-2017-02-10-at-12-08-22-pm" width="500" height="235" srcset="https://kevinclosson.files.wordpress.com/2017/02/screen-shot-2017-02-10-at-12-08-22-pm.png?w=500&h=235 500w, https://kevinclosson.files.wordpress.com/2017/02/screen-shot-2017-02-10-... 1000w, https://kevinclosson.files.wordpress.com/2017/02/screen-shot-2017-02-10-... 150w, https://kevinclosson.files.wordpress.com/2017/02/screen-shot-2017-02-10-... 300w, https://kevinclosson.files.wordpress.com/2017/02/screen-shot-2017-02-10-... 768w" sizes="(max-width: 500px) 100vw, 500px" />

NetApp Testing FlexPod Select for High-Performance Oracle RAC with SLOB

The next NetApp article entitled NetApp AFF8080 EX Performance and Server Consolidation with Oracle Database also features SLOB results and can be found here: https://www.netapp.com/us/media/tr-4415.pdf.

#000000;" src="https://kevinclosson.files.wordpress.com/2017/02/screen-shot-2017-02-10-at-12-01-52-pm.png?w=500&h=206" alt="Figure 4: NetApp Testing the AFF8080 with SLOB" width="500" height="206" srcset="https://kevinclosson.files.wordpress.com/2017/02/screen-shot-2017-02-10-at-12-01-52-pm.png?w=500&h=206 500w, https://kevinclosson.files.wordpress.com/2017/02/screen-shot-2017-02-10-... 150w, https://kevinclosson.files.wordpress.com/2017/02/screen-shot-2017-02-10-... 300w, https://kevinclosson.files.wordpress.com/2017/02/screen-shot-2017-02-10-... 768w, https://kevinclosson.files.wordpress.com/2017/02/screen-shot-2017-02-10-... 998w" sizes="(max-width: 500px) 100vw, 500px" />

NetApp Testing the AFF8080 with SLOB

Yet another SLOB-related NetApp article entitled Oracle Performance Using NetApp Private Storage for SoftLayer can be found here:  http://www.netapp.com/us/media/tr-4373.pdf.

#000000;" src="https://kevinclosson.files.wordpress.com/2017/02/screen-shot-2017-02-10-at-12-04-50-pm.png?w=500&h=213" alt="Figure 5: NetApp Testing NetApp Private Storage for SoftLayer with SLOB" width="500" height="213" srcset="https://kevinclosson.files.wordpress.com/2017/02/screen-shot-2017-02-10-at-12-04-50-pm.png?w=500&h=213 500w, https://kevinclosson.files.wordpress.com/2017/02/screen-shot-2017-02-10-... 1000w, https://kevinclosson.files.wordpress.com/2017/02/screen-shot-2017-02-10-... 150w, https://kevinclosson.files.wordpress.com/2017/02/screen-shot-2017-02-10-... 300w, https://kevinclosson.files.wordpress.com/2017/02/screen-shot-2017-02-10-... 768w" sizes="(max-width: 500px) 100vw, 500px" />

NetApp Testing NetApp Private Storage for SoftLayer with SLOB

When searching the NetApp main webpage I find 11 articles that offer SLOB testing results:

#000000;" src="https://kevinclosson.files.wordpress.com/2017/02/netappslob.png?w=500&h=271" alt="netappslob" width="500" height="271" srcset="https://kevinclosson.files.wordpress.com/2017/02/netappslob.png?w=500&h=271 500w, https://kevinclosson.files.wordpress.com/2017/02/netappslob.png?w=150&h=81 150w, https://kevinclosson.files.wordpress.com/2017/02/netappslob.png?w=300&h=162 300w, https://kevinclosson.files.wordpress.com/2017/02/netappslob.png?w=768&h=416 768w, https://kevinclosson.files.wordpress.com/2017/02/netappslob.png 975w" sizes="(max-width: 500px) 100vw, 500px" />

Searching NetApp Website shows 11 SLOB-Related Articles

Hewlett-Packard Enterprise offers an article entitled HPE 3PAR All-Flash Acceleration for Oracle ASM Preferred Reads which models performance using SLOB. The article can be found here: http://h20195.www2.hpe.com/V2/getpdf.aspx/4AA6-3375ENW.pdf?ver=1.0

#000000;" src="https://kevinclosson.files.wordpress.com/2017/02/screen-shot-2017-02-10-at-12-13-56-pm.png?w=500&h=154" alt="screen-shot-2017-02-10-at-12-13-56-pm" width="500" height="154" srcset="https://kevinclosson.files.wordpress.com/2017/02/screen-shot-2017-02-10-at-12-13-56-pm.png?w=500&h=154 500w, https://kevinclosson.files.wordpress.com/2017/02/screen-shot-2017-02-10-... 1000w, https://kevinclosson.files.wordpress.com/2017/02/screen-shot-2017-02-10-... 150w, https://kevinclosson.files.wordpress.com/2017/02/screen-shot-2017-02-10-... 300w, https://kevinclosson.files.wordpress.com/2017/02/screen-shot-2017-02-10-... 768w" sizes="(max-width: 500px) 100vw, 500px" />

HPE Using SLOB For Performance Assessment of 3PAR Storage

In the Pure Storage article called Pure Storage Reference Architecture for Oracle Databases, the authors also show SLOB results. The article can be found here:

https://support.purestorage.com/@api/deki/files/1732/=Pure_Storage_Oracle_DB_Reference_Architecture.pdf?revision=2.

#000000;" src="https://kevinclosson.files.wordpress.com/2017/02/screen-shot-2017-02-10-at-12-22-24-pm.png?w=500&h=157" alt="screen-shot-2017-02-10-at-12-22-24-pm" width="500" height="157" srcset="https://kevinclosson.files.wordpress.com/2017/02/screen-shot-2017-02-10-at-12-22-24-pm.png?w=500&h=157 500w, https://kevinclosson.files.wordpress.com/2017/02/screen-shot-2017-02-10-... 998w, https://kevinclosson.files.wordpress.com/2017/02/screen-shot-2017-02-10-... 150w, https://kevinclosson.files.wordpress.com/2017/02/screen-shot-2017-02-10-... 300w, https://kevinclosson.files.wordpress.com/2017/02/screen-shot-2017-02-10-... 768w" sizes="(max-width: 500px) 100vw, 500px" />

Pure Storage Featuring SLOB Results in Reference Architecture

Nimble Storage offers the following blog post with SLOB testing results: https://connect.nimblestorage.com/people/tdau/blog/2013/08/14.

#000000;" src="https://kevinclosson.files.wordpress.com/2017/02/screen-shot-2017-02-10-at-12-25-03-pm.png?w=500&h=197" alt="Figure 9: Nimble Storage Blogging About Testing Their Array with SLOB" width="500" height="197" srcset="https://kevinclosson.files.wordpress.com/2017/02/screen-shot-2017-02-10-at-12-25-03-pm.png?w=500&h=197 500w, https://kevinclosson.files.wordpress.com/2017/02/screen-shot-2017-02-10-... 1000w, https://kevinclosson.files.wordpress.com/2017/02/screen-shot-2017-02-10-... 150w, https://kevinclosson.files.wordpress.com/2017/02/screen-shot-2017-02-10-... 300w, https://kevinclosson.files.wordpress.com/2017/02/screen-shot-2017-02-10-... 768w" sizes="(max-width: 500px) 100vw, 500px" />

Nimble Storage Blogging About Testing Their Array with SLOB

There is an IBM 8-bar logo presentation showing SLOB results here:  http://coug.ab.ca/wp-content/uploads/2014/02/Accelerating-Applications-with-IBM-FlashJAN14-v2.pdf.

#000000;" src="https://kevinclosson.files.wordpress.com/2017/02/screen-shot-2017-02-10-at-12-28-45-pm.png?w=500&h=298" alt="screen-shot-2017-02-10-at-12-28-45-pm" width="500" height="298" srcset="https://kevinclosson.files.wordpress.com/2017/02/screen-shot-2017-02-10-at-12-28-45-pm.png?w=500&h=298 500w, https://kevinclosson.files.wordpress.com/2017/02/screen-shot-2017-02-10-... 1000w, https://kevinclosson.files.wordpress.com/2017/02/screen-shot-2017-02-10-... 150w, https://kevinclosson.files.wordpress.com/2017/02/screen-shot-2017-02-10-... 300w, https://kevinclosson.files.wordpress.com/2017/02/screen-shot-2017-02-10-... 768w" sizes="(max-width: 500px) 100vw, 500px" />

IBM Material Showing SLOB Testing

I also find it interesting that folks contributing code to the Linux Kernel include SLOB results showing value of their contributions such as here: http://lkml.iu.edu/hypermail/linux/kernel/1302.2/01524.html.

#000000;" src="https://kevinclosson.files.wordpress.com/2017/02/screen-shot-2017-02-10-at-12-32-27-pm.png?w=500&h=126" alt="screen-shot-2017-02-10-at-12-32-27-pm" width="500" height="126" srcset="https://kevinclosson.files.wordpress.com/2017/02/screen-shot-2017-02-10-at-12-32-27-pm.png?w=500&h=126 500w, https://kevinclosson.files.wordpress.com/2017/02/screen-shot-2017-02-10-... 150w, https://kevinclosson.files.wordpress.com/2017/02/screen-shot-2017-02-10-... 300w, https://kevinclosson.files.wordpress.com/2017/02/screen-shot-2017-02-10-... 641w" sizes="(max-width: 500px) 100vw, 500px" />

Linux Kernel Contributors Use SLOB Testing of Their Submissions

Next we see Red Hat disclosing Live Migration capabilities that involve SLOB workloads: https://www.linux-kvm.org/images/6/66/2012-forum-live-migration.pdf.

#000000;" src="https://kevinclosson.files.wordpress.com/2017/02/screen-shot-2017-02-10-at-12-35-23-pm.png?w=500&h=301" alt="screen-shot-2017-02-10-at-12-35-23-pm" width="500" height="301" srcset="https://kevinclosson.files.wordpress.com/2017/02/screen-shot-2017-02-10-at-12-35-23-pm.png?w=500&h=301 500w, https://kevinclosson.files.wordpress.com/2017/02/screen-shot-2017-02-10-... 1000w, https://kevinclosson.files.wordpress.com/2017/02/screen-shot-2017-02-10-... 150w, https://kevinclosson.files.wordpress.com/2017/02/screen-shot-2017-02-10-... 300w, https://kevinclosson.files.wordpress.com/2017/02/screen-shot-2017-02-10-... 768w" sizes="(max-width: 500px) 100vw, 500px" />

Red Hat Showcasing Live Migration with SLOB Workload

DellEMC has many publications showcasing SLOB results. This reference, however, merely suggests the best-practice of involving SLOB testing before going into production:

http://en.community.dell.com/techcenter/extras/m/white_papers/20438214.

#000000;" src="https://kevinclosson.files.wordpress.com/2017/02/screen-shot-2017-02-10-at-12-39-47-pm.png?w=500&h=391" alt="screen-shot-2017-02-10-at-12-39-47-pm" width="500" height="391" srcset="https://kevinclosson.files.wordpress.com/2017/02/screen-shot-2017-02-10-at-12-39-47-pm.png?w=500&h=391 500w, https://kevinclosson.files.wordpress.com/2017/02/screen-shot-2017-02-10-... 1000w, https://kevinclosson.files.wordpress.com/2017/02/screen-shot-2017-02-10-... 150w, https://kevinclosson.files.wordpress.com/2017/02/screen-shot-2017-02-10-... 300w, https://kevinclosson.files.wordpress.com/2017/02/screen-shot-2017-02-10-... 768w" sizes="(max-width: 500px) 100vw, 500px" />

DellEMC Advocate Pre-Production Testing with SLOB

An example of a detailed DellEMC publication showing SLOB results is the article entitled VMAX ALL FLASH AND VMAX3 ISCSI DEPLOYMENT GUIDE FOR ORACLE DATABASES which can be found here:

https://www.emc.com/collateral/white-papers/h15132-vmax-all-flash-vmax3-iscsi-deploy-guide-oracle-wp.pdf.

 

#000000;" src="https://kevinclosson.files.wordpress.com/2017/02/screen-shot-2017-02-10-at-12-47-19-pm.png?w=500&h=165" alt="screen-shot-2017-02-10-at-12-47-19-pm" width="500" height="165" srcset="https://kevinclosson.files.wordpress.com/2017/02/screen-shot-2017-02-10-at-12-47-19-pm.png?w=500&h=165 500w, https://kevinclosson.files.wordpress.com/2017/02/screen-shot-2017-02-10-... 1000w, https://kevinclosson.files.wordpress.com/2017/02/screen-shot-2017-02-10-... 150w, https://kevinclosson.files.wordpress.com/2017/02/screen-shot-2017-02-10-... 300w, https://kevinclosson.files.wordpress.com/2017/02/screen-shot-2017-02-10-... 768w" sizes="(max-width: 500px) 100vw, 500px" />

EMC Testing VMAX All-FLASH with SLOB

I took a moment to search the main DellEMC website for articles containing the word SLOB and found 76 such articles!

#000000;" src="https://kevinclosson.files.wordpress.com/2017/02/screen-shot-2017-02-10-at-12-49-23-pm.png?w=500&h=363" alt="screen-shot-2017-02-10-at-12-49-23-pm" width="500" height="363" srcset="https://kevinclosson.files.wordpress.com/2017/02/screen-shot-2017-02-10-at-12-49-23-pm.png?w=500&h=363 500w, https://kevinclosson.files.wordpress.com/2017/02/screen-shot-2017-02-10-... 1000w, https://kevinclosson.files.wordpress.com/2017/02/screen-shot-2017-02-10-... 150w, https://kevinclosson.files.wordpress.com/2017/02/screen-shot-2017-02-10-... 300w, https://kevinclosson.files.wordpress.com/2017/02/screen-shot-2017-02-10-... 768w" sizes="(max-width: 500px) 100vw, 500px" />

Search for SLOB Material on DellEMC Main Web Page

Red Stack Tech offer DBaaS and even showcase the ability to test the platform for I/O suitability with SLOB:

http://www.redstk.com/services/cloud-technology-poc/

#000000;" src="https://kevinclosson.files.wordpress.com/2017/02/screen-shot-2017-02-14-at-8-03-49-am.png?w=500&h=263" alt="screen-shot-2017-02-14-at-8-03-49-am" width="500" height="263" srcset="https://kevinclosson.files.wordpress.com/2017/02/screen-shot-2017-02-14-at-8-03-49-am.png?w=500&h=263 500w, https://kevinclosson.files.wordpress.com/2017/02/screen-shot-2017-02-14-... 1000w, https://kevinclosson.files.wordpress.com/2017/02/screen-shot-2017-02-14-... 150w, https://kevinclosson.files.wordpress.com/2017/02/screen-shot-2017-02-14-... 300w, https://kevinclosson.files.wordpress.com/2017/02/screen-shot-2017-02-14-... 768w" sizes="(max-width: 500px) 100vw, 500px" />

Red Stack Tech Offering SLOB Testing as Proof of Concept

Non-Vendor References

Although not a vendor, it deserves mention that Greg Shultz of Server StorageIO and UnlimitedIO LLC lists SLOB alongside other platform and I/O testing toolkits. Greg’s exhaustive list can be found here: http://storageioblog.com/server-and-storage-io-benchmarking-resources/.

#000000;" src="https://kevinclosson.files.wordpress.com/2017/02/storageio.jpg?w=500&h=267" alt="storageio" width="500" height="267" srcset="https://kevinclosson.files.wordpress.com/2017/02/storageio.jpg?w=500&h=267 500w, https://kevinclosson.files.wordpress.com/2017/02/storageio.jpg?w=1000&h=534 1000w, https://kevinclosson.files.wordpress.com/2017/02/storageio.jpg?w=150&h=80 150w, https://kevinclosson.files.wordpress.com/2017/02/storageio.jpg?w=300&h=160 300w, https://kevinclosson.files.wordpress.com/2017/02/storageio.jpg?w=768&h=410 768w" sizes="(max-width: 500px) 100vw, 500px" />

 

 

Summary

More and more people are using SLOB. If you are into Oracle Database platform performance I think you should join the club! Maybe you’ll even take interest in joining the Twitter SLOB list: https://twitter.com/kevinclosson/lists/slob-community.

Get SLOB, use SLOB!

 

 

 

 

Filed under: oracle

Index bouncy scan

There’s a thread running on OTN at present about deleting huge volumes of duplicated data from a table (to reduce it from 1.1 billion to about 22 million rows). The thread isn’t what I’m going to talk about, though, other than quoting some numbers from it to explain what this post is about.

An overview of the requirement suggests that a file of about 2.2 million rows is loaded into the table every week with (historically) no attempt to delete duplicates. As a file is loaded into the table every row gets the same timestamp, which is the sysdate at load time. I thought it would be useful to know how many different timestamps there were in the whole table.  (From an averaging viewpoint, 1.1 billion rows at 2.2 million rows per week suggests about 500 dates/files/weeks – or about 9.5 years – but since the table relates to “customer accounts” it seems likely that the file was originally smaller and has grown over time, which means the hiostory may be rather longer than that.)

Conveniently there is an index on the “input_user_date” column in the table so we might feel happy running a query that simply does:


select
        distinct input_user_date
from
        customer_account
order by
        input_user_date
;

We might then refine the query to do a count(*) aggregate, or do some analytics to find any strange gaps in the timing of the weekly loads. However, all I’m really interested in is the number of dates because I’ve suggested we could de-duplicate the data by running a PL/SQL process that does a simple job for each date in turn, and I want to get an idea of how many times that job will run so that I can estimate how long the entire process might take.

The trouble with the basic query is that the table is (as you probably noticed) rather large, and so is the index. If we assume 8 bytes (which includes the length byte) for a date, 7 bytes for the rowid, 4 bytes overhead, and 100% packing we get about 420 index entries per leaf blocks, so with 1.1 billion entries the index is about 2.6 million leaf blocks. If the index had been built with compression (which means you’d only be recording a date once per leaf block) it would still be about 1.6 million leaf blocks. Fortunately we wouldn’t have to do much “real” sorting to report just a list of distinct values, or even the count(*) for each date, if we made Oracle use an index full scan – but it’s still a lot of work to read 1.6 million blocks (possibly using single block reads) and do even something as simple as a running count as you go. So I whipped up a quick and dirty bit of PL/SQL to do the job.

declare
        m_d1 date := to_date('01-Jan-0001');
        m_d2 date := to_date('01-Jan-0001');
        m_ct number := 0;
begin
        loop
                select
                        min(input_user_date)
                into
                        m_d2
                from
                        customer_account
                where
                        input_user_date > m_d1
                ;

                exit when m_d2 is null;

                m_ct := m_ct + 1;
                dbms_output.put_line('Count: ' || m_ct || '  Date: ' || m_d2);
                m_d1 := m_d2;

        end loop;
end;
/

The code assumes that the input_user_date hasn’t gone back to a silly date in the past to represent a “null date” (which shouldn’t exist anyway; if you want to use code like this but have a problem with a special “low-value” then you would probably be safest adding a prequel SQL that selects the min(columnX) where columnX is not null to get the starting value instead of using the a constant as I have done.

The execution path for the SQL statement should be an index-only: “index range scan (min/max)” which typically requires only 3 or 4 logical I/Os to find the relevant item for each date (which compares well with the estimated 2,200,000 / 420 = 5,238 leaf blocks we would otherwise have to scan through for each date). Here’s the path you should see:


--------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |       |       |     3 (100)|          |
|   1 |  SORT AGGREGATE              |       |     1 |     8 |            |          |
|   2 |   FIRST ROW                  |       |     1 |     8 |     3   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN (MIN/MAX)| CA_I1 |     1 |     8 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("INPUT_USER_DATE">:B1)

I did build a little data set as a proof of concept – and produced a wonderful example of how the scale and the preceding events makes a difference that requires you to look very closely at what has happened. I used a table t1 in my example with a column d1, but apart from the change in names the PL/SQL block was as above.Here’s the code I used to create the data and prepare for the test:


create table t1 nologging
as
select
        trunc(sysdate) + trunc((rownum - 1)/100) d1,
        rpad('x',100)   padding
from
        all_objects
where
        rownum <= 50000
;

execute dbms_stats.gather_table_stats(user,'t1')
alter table t1 modify d1 not null;

create index t1_i1 on t1(d1) nologging pctfree 95
;

select index_name, leaf_blocks from user_indexes;

alter system flush buffer_cache;

alter session set events '10046 trace name context forever, level 8';

My data set has 500 dates with 100 rows per date, and the pctfree setting for the index gives me an average of about 8 leaf blocks per date (for a total of 4,167 leaf blocks). It’s only a small index so I’m expecting to see just 2 or 3 LIOs per date, and a total of about 500 physical reads (one per date plus a handful for reading branch blocks). Here’s the output from the running tkprof against the trace file:


SELECT MIN(D1)
FROM
 T1 WHERE D1 > :B1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute    501      0.00       0.01          0          0          0           0
Fetch      501      0.08       0.18       4093       1669          0         501
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     1003      0.09       0.19       4093       1669          0         501

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 62     (recursive depth: 1)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  SORT AGGREGATE (cr=3 pr=64 pw=0 time=9131 us)
         1          1          1   FIRST ROW  (cr=3 pr=64 pw=0 time=9106 us cost=3 size=8 card=1)
         1          1          1    INDEX RANGE SCAN (MIN/MAX) T1_I1 (cr=3 pr=64 pw=0 time=9089 us cost=3 size=8 card=1)(object id 252520)

I’ve done a physical read of virtually every single block in the index; but I have done only 3 buffer gets per date – doing fewer buffer gets than physical reads.

I’ve been caught by two optimisations (which turned out to be “pessimisations” in my test): I’ve flushed the buffer cache, so the Oracle runtime engine has decided to consider “warming up” the cache by reading extra blocks from any popular-looking objects that I’m accessing, and the optimizer may have given the run-time engine enough information to allow it to recognise that this index is subject to range scans and could therefore be a suitable object to use while warming up. As you can see from the following extracts from session events and session activity stats – we’ve done a load of multiblock reads through the index.


Event                                             Waits   Time_outs           Csec    Avg Csec    Max Csec
-----                                             -----   ---------           ----    --------    --------
db file sequential read                               1           0           0.03        .031           6
db file scattered read                              136           0          13.54        .100           1


Name                                                                     Value
----                                                                     -----
physical reads                                                           4,095
physical reads cache                                                     4,095
physical read IO requests                                                  137
physical reads cache prefetch                                            3,958
physical reads prefetch warmup                                           3,958

This isn’t likely to happen, of course, in the production system where we’ll be starting with a fully loaded cache and the leaf blocks we need are (logically) spaced apart by several thousand intervening blocks.

Footnote

I can’t remember who first brought this strategy to my attention – though I’m fairly sure it was one of my Russian colleagues, who has blogged about ways to work around what is effectively a limitation of the “index skip scan”. Apologies to the originator, and if you recognise your work here please add a comment with URL below.

Preparing AWS for the Delphix Trial

There are more configurations for AWS than there are fish in the sea, but as the rush of folks arrive to test out the incredibly cool AWS Trial for Delphix, I’ll add my rendition of what to look for to know you’re AWS setup is prepped to successfully deploy.

The EC2 Dashboard View

After you’ve selected your location, set up your security user/group and key pairs, there’s a quick way to see, (at least high level) if you’re ready to deploy the AWS Trial to the zone in question.

Go to your EC2 Dashboard and to the location, (Zone) that you plan to deploy your trial to and you should see the following:

https://i0.wp.com/dbakevlar.com/wp-content/uploads/2017/01/Screen-Shot-2... 300w, https://i0.wp.com/dbakevlar.com/wp-content/uploads/2017/01/Screen-Shot-2... 768w, https://i0.wp.com/dbakevlar.com/wp-content/uploads/2017/01/Screen-Shot-2... 1195w" sizes="(max-width: 600px) 100vw, 600px" data-recalc-dims="1" />

Notice in the dashboard, you can see that the key pairs, (1) and the expected Security Groups, (3) are displayed, which tells us that we’re ready to deploy to this zone.  If we double click on the Key Pair, we’ll see that its match to the one we downloaded locally and will use in our configuration with Terraform:

https://i1.wp.com/dbakevlar.com/wp-content/uploads/2017/02/Screen-Shot-2... 300w, https://i1.wp.com/dbakevlar.com/wp-content/uploads/2017/02/Screen-Shot-2... 768w" sizes="(max-width: 272px) 100vw, 272px" data-recalc-dims="1" />

How Terraform Communicates with AWS

These are essential to deploying in an AWS zone that’s configured as part of your .tfvars file for terraform.  You’ll note in the example below, we have both designated the correct zone and the key pair that is part of the zone we’ll be using to authenticate:


#VERSION=004

#this file should be named terraform.tfvars

# ENTER INPUTS BELOW

access_key="XXXXXXX"

secret_key="XXXXXXXXXX"

aws_region="us-east-1"

your_ip="xxx.xx.xxx.xxx"

key_name="Delphix_east1" #don't include .pem in the key name 

instance_name="Delphix_AWS"

community_username="xxx@delphix.com"

community_password="password"

Hopefully this is a helpful first step in understanding how zones, key pairs and security groups interact to support the configuration file, (tfvars) file that we use with the Delphix deployment via Terraform into AWS.

 



Tags:  ,


Del.icio.us



Facebook

TweetThis

Digg

StumbleUpon




Copyright © DBA Kevlar [Preparing AWS for the Delphix Trial], All Right Reserved. 2017.

The post Preparing AWS for the Delphix Trial appeared first on DBA Kevlar.

Empowering Your Slides- Tips with PowerPoint

Ah, yes, it’s that RMOUG Training Days time of the year again!

As a techie, I didn’t put a lot of time into my slides when I first started presenting, thinking I’d simply dazzle them with my amazing knowledge.

What I found is that slide deck skills don’t just help your presentation, they help you tell the story better, which should be the goal of every presenter.  No matter how technical we are, we most likely haven’t had any formal Powerpoint training and as I’ve been upping my “PPT skills” this last year, I thought I’d share with the rest of the class…:)

Presenter View

Presenter View something that would have really helped me out when I first started.  To not have to remember every little detail and to have my notes displayed at the bottom of my screen would have been a stellar advancement.

https://i2.wp.com/dbakevlar.com/wp-content/uploads/2017/02/Screen-Shot-2... 300w, https://i2.wp.com/dbakevlar.com/wp-content/uploads/2017/02/Screen-Shot-2... 768w, https://i2.wp.com/dbakevlar.com/wp-content/uploads/2017/02/Screen-Shot-2... 1200w, https://i2.wp.com/dbakevlar.com/wp-content/uploads/2017/02/Screen-Shot-2... 1800w" sizes="(max-width: 600px) 100vw, 600px" data-recalc-dims="1" />

Having notes at the bottom upped my presentation game, as I found I removed much of the text on my slides and instead used reference links to blog posts, articles and white papers to fully engage the attendee in my sessions vs. receiving minimal thoughts displayed on a slide.

I found that I went to more full screen graphics with a single phrase and was able to simply talk with the audience, having my notes keep me on topic, without having a slide full of discussion points that were more for me than those in attendance

Animations

 

A picture is worth a thousand words, but a moving picture?  That’s a whole story and the reason people go to movies, watch TV and why even adults like cartoons.  We can display an idea with movement more completely than a still image.

Powerpoint makes animations quite simple and it just takes the ability to group, highlight and animate with the animation toolbar:

https://i0.wp.com/dbakevlar.com/wp-content/uploads/2017/02/Screen-Shot-2... 300w, https://i0.wp.com/dbakevlar.com/wp-content/uploads/2017/02/Screen-Shot-2... 768w, https://i0.wp.com/dbakevlar.com/wp-content/uploads/2017/02/Screen-Shot-2... 1200w, https://i0.wp.com/dbakevlar.com/wp-content/uploads/2017/02/Screen-Shot-2... 1800w" sizes="(max-width: 600px) 100vw, 600px" data-recalc-dims="1" />

All the basic and even a number of advanced animations are available to make your presentations pop and the ability to create a complex animation to get your point across!

Full Screen Graphics

Why is it great to have every other or every third slide a full screen graphic?  It gives the audience a rest between the data they’ve been taking in and with technical or complex topic presentations, your attendee is likely to appreciate the break.

https://i1.wp.com/dbakevlar.com/wp-content/uploads/2017/02/Screen-Shot-2... 300w, https://i1.wp.com/dbakevlar.com/wp-content/uploads/2017/02/Screen-Shot-2... 768w, https://i1.wp.com/dbakevlar.com/wp-content/uploads/2017/02/Screen-Shot-2... 1112w" sizes="(max-width: 538px) 100vw, 538px" data-recalc-dims="1" />

No matter if you’ve just discussed the algorithm used for a specific data masking feature or like the slide above, discussing networking for geeky introverts, a slide like this makes an impact and the audience will more likely remember intricate details when combined with a humorous phrase and memorable picture.

Always use stock images and if you end up using an image from someone else’s site, etc. ask and give credit.

 

Layout

I used to update all my slides to a new template by copying them over in slide view, but now I know that I can switch them to a new slide template and switch out individual slides to new single template slides with the Layout menu in Powerpoint.

https://i2.wp.com/dbakevlar.com/wp-content/uploads/2017/02/Screen-Shot-2... 188w, https://i2.wp.com/dbakevlar.com/wp-content/uploads/2017/02/Screen-Shot-2... 768w, https://i2.wp.com/dbakevlar.com/wp-content/uploads/2017/02/Screen-Shot-2... 794w" sizes="(max-width: 418px) 100vw, 418px" data-recalc-dims="1" />

Having more control about the layout-  everything from text, to graphics, etc. saves me time from manually updating everything in a single slide.  If there is a common format that you use, you can make a copy of the template and save it off to use in the future, too.

Well, it’s time for me to get more tasks in preparation for the conference done!  We’re looking forward to seeing everyone at RMOUG Training Days 2017!

Conference Sessions:

Wednesday:  Introduction for Connor McDonald from Oracle, 2017 Keynote- 9:45am, Main Ballroom

  Lunch with the Experts- 12:30pm, Main Ballroom

                       Women in Technology Round Table with Komal Goyal and Rene Antunez– 1:15pm, room 1A

Social Media, the Next Generation with Rene Antunez– 4pm, room 1A

Welcome Reception Host- 5:30pm in the Main Ballroom

Thursday:      Delphix Hands on Lab- 9:00am in the OTN Area

 Lunch with the Experts– 12:30pm, Main Ballroom

Closing Session for the OTN Area– 2:00pm

Virtualization and the Cloud– 4:00pm- room 4F

I will be found at the Registration Desk, the OTN area doing interviews, taking pictures and videos throughout the other times of the conference!



Tags:  , ,


Del.icio.us



Facebook

TweetThis

Digg

StumbleUpon




Copyright © DBA Kevlar [Empowering Your Slides- Tips with PowerPoint], All Right Reserved. 2017.

The post Empowering Your Slides- Tips with PowerPoint appeared first on DBA Kevlar.

Common Users in #Oracle 12c – what are they good for?

A 12c Multitenant Database is designed to make consolidation easier. The PDBs have not only their application data in separate tablespaces, they also have their application metadata in separate SYSTEM tablespaces. That’s what makes it so easy and fast to unplug a PDB.

The SYSTEM tablespace of the CDB contains the internal metadata that is shared by all PDBs. Internal metadata (the dictionary tables) and internal objects (like the DBMS* packages) belong to Common Users, most prominently to SYS. We don’t have to install all that internal stuff inside of the PDBs, they can just refer to it.

The point is that Common Users are what enables the Multitenant Architecture in the first place. You as a DBA may also create them as a side aspect of the matter. Now what does that mean in practice?

 

[oracle@uhesse ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Feb 6 11:34:20 2017

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select name,open_mode,con_id from v$pdbs;

NAME	   OPEN_MODE	  CON_ID
---------- ---------- ----------
PDB$SEED   READ ONLY	       2
PDB1	   READ WRITE	       3
PDB2	   READ WRITE	       4

My demo system looks like the picture above with two PDBs apart from the seed. Let’s try to clarify the difference between SYS as a common user and SCOTT as two local users.

SQL> connect sys/oracle@pdb1 as sysdba
Connected.
SQL> select common from dba_users where username='SCOTT';

COM
---
NO

SQL> select common from dba_users where username='SYS';

COM
---
YES
SQL> delete from scott.dept where deptno=40;

1 row deleted.

SQL> commit;

Commit complete.

SQL> select * from scott.dept;

    DEPTNO DNAME	  LOC
---------- -------------- -------------
	10 ACCOUNTING	  NEW YORK
	20 RESEARCH	  DALLAS
	30 SALES	  CHICAGO
SQL> connect sys/oracle@pdb2 as sysdba
Connected.
SQL> select common from dba_users where username='SCOTT';

COM
---
NO

SQL> select common from dba_users where username='SYS';

COM
---
YES

SQL> select * from scott.dept;

    DEPTNO DNAME	  LOC
---------- -------------- -------------
	10 ACCOUNTING	  NEW YORK
	20 RESEARCH	  DALLAS
	30 SALES	  CHICAGO
	40 OPERATIONS	  BOSTON

There are two local users with incidentally the same name SCOTT in the two PDBs. The common user SYS is almighty in both PDBs. What common users do we have else visible in the PDBs?

SQL> select username from dba_users where common='YES';

USERNAME
-------------------------------------------------------
SYS
SYSTEM
SYSRAC
SYS$UMF
OUTLN
DBSNMP
APPQOSSYS
CTXSYS
SI_INFORMTN_SCHEMA
GSMADMIN_INTERNAL
ORDPLUGINS
MDSYS
ORDDATA
XDB
WMSYS
ORDSYS
GGSYS
ANONYMOUS
GSMCATUSER
SYSBACKUP
GSMUSER
DIP
SYSKM
ORACLE_OCM
SYSDG
REMOTE_SCHEDULER_AGENT
DBSFWUSER
XS$NULL
OJVMSYS
AUDSYS

30 rows selected.

Apart from SYS and SYSTEM, you see some more internal users on display here. Most of them are related to certain options. This is how these options become available throughout all PDBs. Notice that DBA_USERS shows only what is visible to that PDB. The second SCOTT from PDB2 is not listed, because that user is relevant only for PDB2. It is in other words a local user inside PDB2. CDB_USERS confirms that there are two SCOTT users:

SQL> connect / as sysdba
Connected.
SQL> select con_id,common from cdb_users where username='SCOTT';

    CON_ID COM
---------- ---
	 3 NO
	 4 NO

If you want to create a common user yourself, it must be prefixed with C##. A common user is immediately visible in all PDBs. That doesn’t mean that user has any rights, though.

SQL> connect / as sysdba
Connected.
SQL> create user c##adam identified by oracle container=all;

User created.

SQL> connect sys/oracle@pdb1 as sysdba
Connected.
SQL> select username,common from dba_users where username like 'C##%';

USERNAME   COM
---------- ---
C##ADAM    YES

SQL> connect sys/oracle@pdb2 as sysdba
Connected.
SQL> select username,common from dba_users where username like 'C##%';

USERNAME   COM
---------- ---
C##ADAM    YES

SQL> connect c##adam/oracle@pdb1
ERROR:
ORA-01045: user C##ADAM lacks CREATE SESSION privilege; logon denied


Warning: You are no longer connected to ORACLE.
SQL> connect c##adam/oracle@pdb2
ERROR:
ORA-01045: user C##ADAM lacks CREATE SESSION privilege; logon denied

Common users can get privileges granted as common or as local privileges:

SQL> grant create session to c##adam container=all;

Grant succeeded.

SQL> connect c##adam/oracle@pdb1
Connected.
SQL> connect c##adam/oracle@pdb2
Connected.
SQL> connect / as sysdba
Connected.
SQL> revoke create session from c##adam;
revoke create session from c##adam
*
ERROR at line 1:
ORA-65092: system privilege granted with a different scope to 'C##ADAM'


SQL> revoke create session from c##adam container=all;

Revoke succeeded.

Notice that container=all must be specified upon the revoke as well. That was a common privilege. Now for the local privilege:

SQL> connect sys/oracle@pdb1 as sysdba
Connected.
SQL> grant  create session to c##adam container=current;

Grant succeeded.

SQL> connect c##adam/oracle@pdb1
Connected.
SQL> connect c##adam/oracle@pdb2
ERROR:
ORA-01045: user C##ADAM lacks CREATE SESSION privilege; logon denied


Warning: You are no longer connected to ORACLE.

C##ADAM has the create session privilege only for PDB1, not for PDB2. It is in other words local to PDB1. The clause container=current is optional here and can be left out to achieve the same effect:

SQL> connect sys/oracle@pdb1 as sysdba
Connected.
SQL> revoke create session from c##adam container=current;

Revoke succeeded.

SQL> grant  create session to c##adam;

Grant succeeded.

SQL> revoke create session from c##adam;

Revoke succeeded.

SQL> grant  create session to c##adam container=current;

Grant succeeded.

The two revokes and the two grants have had the same effect. A typical use case for the creation of common users would be to get less powerful superusers like in this example. I would recommend to implement those with some reservation, not to speak about common roles, granting a mixture of local and common privileges to roles and roles to (common) roles. Much is possible but that doesn’t necessarily mean it is a good idea to do it </p />
</p></div>

    	  	<div class=

Oracle Database Cloud (DBaaS) Performance - Part 4 - Network

In the last part of this installment I'll have a brief look at the network performance measured in the Oracle DBaaS environment, in particular the network interface that gets used as private interconnect in case of RAC configuration. The network performance could also be relevant when evaluating how to transfer data to the cloud database.

I've used the freely available "iperf" tool to measure the network bandwidth and got the following results:

[root@test12102rac2 ~]# iperf3 -c 10.196.49.126
Connecting to host 10.196.49.126, port 5201
[  4] local 10.196.49.130 port 41647 connected to 10.196.49.126 port 5201
[ ID] Interval           Transfer     Bandwidth       Retr  Cwnd
[  4]   0.00-1.00   sec   651 MBytes  5.46 Gbits/sec   15    786 KBytes
[  4]   1.00-2.00   sec   823 MBytes  6.90 Gbits/sec   11   1.07 MBytes
[  4]   2.00-3.00   sec   789 MBytes  6.62 Gbits/sec    7   1014 KBytes
[  4]   3.00-4.00   sec   700 MBytes  5.87 Gbits/sec   39   1.04 MBytes
[  4]   4.00-5.00   sec   820 MBytes  6.88 Gbits/sec   21    909 KBytes
[  4]   5.00-6.00   sec   818 MBytes  6.86 Gbits/sec   17   1.17 MBytes
[  4]   6.00-7.00   sec   827 MBytes  6.94 Gbits/sec   21   1005 KBytes
[  4]   7.00-8.00   sec   792 MBytes  6.64 Gbits/sec    8    961 KBytes
[  4]   8.00-9.00   sec   767 MBytes  6.44 Gbits/sec    4   1.11 MBytes
[  4]   9.00-10.00  sec   823 MBytes  6.91 Gbits/sec    6   1.12 MBytes
- - - - - - - - - - - - - - - - - - - - - - - - -
[ ID] Interval           Transfer     Bandwidth       Retr
[  4]   0.00-10.00  sec  7.63 GBytes  6.55 Gbits/sec  149             sender
[  4]   0.00-10.00  sec  7.63 GBytes  6.55 Gbits/sec                  receiver

iperf Done.

So the network bandwidth seems to be something between 6 and 7 Gbits/sec, which is not too bad.

For completeness, the UDP results look like the following:

[root@test12102rac2 ~]# iperf3 -c 10.196.49.126 -u -b 10000M
Connecting to host 10.196.49.126, port 5201
[  4] local 10.196.49.130 port 55482 connected to 10.196.49.126 port 5201
[ ID] Interval           Transfer     Bandwidth       Total Datagrams
[  4]   0.00-1.00   sec   494 MBytes  4.14 Gbits/sec  63199
[  4]   1.00-2.00   sec   500 MBytes  4.20 Gbits/sec  64057
[  4]   2.00-3.00   sec   462 MBytes  3.87 Gbits/sec  59102
[  4]   3.00-4.00   sec   496 MBytes  4.16 Gbits/sec  63491
[  4]   4.00-5.00   sec   482 MBytes  4.05 Gbits/sec  61760
[  4]   5.00-6.00   sec   425 MBytes  3.57 Gbits/sec  54411
[  4]   6.00-7.00   sec   489 MBytes  4.10 Gbits/sec  62574
[  4]   7.00-8.00   sec   411 MBytes  3.45 Gbits/sec  52599
[  4]   8.00-9.00   sec   442 MBytes  3.71 Gbits/sec  56541
[  4]   9.00-10.00  sec   481 MBytes  4.04 Gbits/sec  61614
- - - - - - - - - - - - - - - - - - - - - - - - -
[ ID] Interval           Transfer     Bandwidth       Jitter    Lost/Total Datagrams
[  4]   0.00-10.00  sec  4.57 GBytes  3.93 Gbits/sec  0.028 ms  23434/599340 (3.9%)
[  4] Sent 599340 datagrams

iperf Done.

Finally, "ping" results look like the following:
9665 packets transmitted, 9665 received, 0% packet loss, time 9665700ms
rtt min/avg/max/mdev = 0.135/0.308/199.685/3.322 ms
So an average latency of 0.3 ms also doesn't look too bad.

[Update 6.2.2017]: Thanks to Frits Hoogland who pointed out the very high "max" value for the ping. Although I didn't spot the pattern that he saw in a different network test setup ("cross cloud platform"), which was an initial slowness, it's still worth to point out the high "max" value of almost 200 ms for a ping, and also the "mdev" value of 3.322 ms seems to suggest that there were some significant variations in ping times observed that are potentially hidden behind the average values provided. I'll repeat the ping test and see if I can reproduce these outliers and if yes, find out more details.

Hotsos Symposium 2017: Feb 27 – Mar 2 Dallas, Texas (I’m Afraid of Americans)

In just 3 weeks time, I’ll again have the pleasure of presenting at the awesome Hotsos Symposium in Dallas, Texas. It’s simply the best conference with regard to Oracle performance tuning anywhere. This year, I’ll be presenting on a couple of topics: New Index Features Introduced in Oracle Database 12c Release 2 Oracle Database 12c […]