Tag Archives: oracle 12c

Oracle 12c on Docker

Docker is an open platform, that gives customers the ability to deploy, multiple o/s containers on any give host. This allows for the deployment of multiple environments without having to incur the overhead of having a virtual machine per environment. Docker uses linux o/s facilities like namespaces, cgroups and union capable file systems to deploy lightweight containers.

A good explanation of Docker Architecture and concepts can be found here.

At the time of writing of this post, oracle does not support running oracle databases in docker containers. However it is conceivable that, in the future customers might want to deploy the oracle database in a docker container’s on the cloud. It could be an effective mechanism to create and maintain a large number of database copies in order to support development environments that follow agile development methadologies.

Update Jan 03 2017 : Oracle now supports running oracle databases in docker containers. Please see Mos note 2216342.1  for details.

Oracle has published docker images for Oracle Linux 6 and Oracle Linux 7. Recently Oracle has also published a DockerFile and a build script that builds a docker image that uses Oracle Linux 7, installs and creates a oracle database (11gr2 or 12cr1) on it, and creates a new image that includes the oracle database. This is an easy way to get an oracle 12.1.0.2 database up and running for development purposes.

In this blog post, I will detail the steps i followed to build an Oracle 12c database, Docker image, that runs inside of a VirtualBox virtual machine.

The high level steps are as follows.

  • Download the oracle 12cr1 installation Files.
  • Download and setup a VirtualBox Ubuntu 16.04 image from osboxes.
  • Install docker on Ubuntu 16.04
  • Download the Oracle Docker files from github
  • Stage the 12cr1 binaries Execute the build script to build the Oracle database docker image.

Download the oracle 12cr1 installation files.

  • Login to edelivery.oracle.com

d1

  • Choose Oracle Database Enterprise Edition, and Linux x86-64

d2

  • Choose the 2 files, and download them.
  • Rename (Because the Docker build script expects files to be with these names) the files as shown below
    • V46095-01_1of2.zip to linuxamd64_12102_database_1of2.zip
    • V46095-01_1of2.zip to linuxamd64_12102_database_2of2.zip

Download and setup a VirtualBox Ubuntu 16.04 image from osboxes

From www.osboxes.org/ubuntu, download the .vdi file, for “VirtualBox (VDI) 64Bit”.

d4

The downloaded file name will be Ubuntu-16.04-Xenial-VB-64bit.7z. Unzip the contents of this file, to any directory. This will give you a file named “Ubuntu 16.04 64bit.vdi”

From your VirtualBox console create a new VirtualMachine.

  • Use the expert mode
  • Name “Ubuntu1604”
  • Type “Linux”
  • Version – “Ubuntu (64-bit)”
  • Choose 3Gb of memory for the virtualmachine.
  • Choose “Do not add a virtualdisk”
  • Click Create
  • Copy the file you downloaded from www.osboxes.org, “Ubuntu 16.04 64bit.vdi” into the newly created directory, named “Ubuntu1604”
  • This brings you back to the home page.
  • Choose the newly created image “Ubuntu1604”, click on Storage, and Click on “Controller SATA”.
  • Here choose to add a new disk, and choose the file “Ubuntu 16.04 64bit.vdi”.
  • Click OK.
  • This brings you back to the home page.
  • Click on Network.
  • The Network Adapter 1, is configured to use NAT, change this to use “Bridged Adapter”, Click Ok.

Now you have a virtualmachine, which runs the latest version of Ubuntu. The / directory has 99GB of space allocated to it, and hence is sufficient to create the oracle docker image.

Start the virtualmachine and login. (The default user it creates is osboxes, and the password is osboxes.org)

Install docker on Ubuntu 16.04

Follow the instructions at https://docs.docker.com/engine/installation/linux/ubuntulinux/, to install docker.

  • login as the user osboxes into Ubuntu
  • Invoke a linux terminal.
  • sudo su –
  • apt-get update
  • apt-get install apt-transport-https ca-certificates
  • apt-key adv –keyserver hkp://p80.pool.sks-keyservers.net:80 –recv-keys 58118E89F3A912897C070ADBF76221572C52609D
  • vi /etc/apt/sources.list.d/docker.list
    • Add the following line
    • deb https://apt.dockerproject.org/repo ubuntu-xenial main
    • save and quit
  • apt-get update
  • apt-cache policy docker-engine
  • apt-get install linux-image-extra-$(uname -r)
  • apt-get install docker-engine
  • sudo service docker start
  • sudo groupadd docker

Create a new o/s user for running docker images.I will be performing all the docker operations going forward, from this OS User.

  • I created a new o/s user named rramdas (Regular user, not admin) (Feel free to create a user with any name you want to use)
  • I added this user to the sudoers file, so I can sudo to root from this user.
  • Add the new user rramdas to the docker group
    • sudo usermod -aG docker rramdas

Login as rramdas and ping yahoo.com to ensure that you are able to communicate with machines on the internet.

Next I installed opensshd-server so that I can ssh to this virtual host from my laptop. (Not required)

Download the oracle Docker files from github

  • Login as rramdas
  • Create a directory /u01 in which we will place all the docker files
    • sudo su –
    • mkdir /u01
    • chown rramdas:rramdas /u01
    • exit
  • cd /u01
  • git clone https://github.com/oracle/docker-images
  • cd /u01/docker-images
  • remove all the directories other than OracleDatabase.

Stage the 12cr1 binaries

From the directory where the oracle installation binaries were downloaded in step 1.

  • Copy the oracle installation binaries to the virtual host. (10.1.1.156 is the Ip address of my virtual host)
    • scp linuxamd64_12102_database_* rramdas@10.1.1.156:/u01/docker-images/OracleDatabase/dockerfiles/12.1.0.2/.

We are staging the oracle installation files in this directory because the Docker build script expects the files in this directory.

Build the Docker Image with Oracle 12c

  • login as rramdas to the ubuntu virtualhost.
  • cd /u01/docker-images/OracleDatabase/dockerfiles
  • ./buildDockerImage.sh -v 12.1.0.2 -e -p manager -i

docker1

docker2

It Took 43 minutes to build the image.

If we take a deeper look into the DockerFile.ee, we can see that the following actions have been executed.

  • Download the latest docker image for Oracle Linux
  • Create the necessary O/S users and groups.
  • Install the oracle-rdbms-server-12cR1-preinstall rpm, which checks and installs all the pre-requesite rpm’s for an oracle install and sets up the required kernel parameters.
  • Runs the Universal Installer (Using a response file) to install the oracle binaries and create an oracle database.
  • Creates the oracle listener and tnsnames entries.

After the script completes execution, we have a new docker image database:12.1.0.2-ee

docker3-2

Start the Docker database container.

You can start the Docker container using the command docker run -p 1521:1521 -p 5500:5500 oracle/database:12.1.0.2-ee

docker4

Now we have a 12c CDB, with 1 PDB running, in the Docker Container.

You can list the running docker containers using the “docker ps” command

docker7-2

This database can be accessed from any oracle database client (like sqlcl or sqlplus, or any application via jdbc , odbc etc).

docker5

This should get you started with using an Oracle 12c database in a Docker container.

Adaptive Query Optimization – Adaptive Plans – Adaptive Joins

Based on the available statistics on the objects, the Oracle optimizer could incorrectly estimate the cardinality of certain row sources, resulting in a sub-optimal plan. Adaptive plans are a technique used by the Oracle 12c optimizer to adapt a plan, based on information learned during execution, to pick a better plan.

There are two types of optimizations oracle 12c can do under adaptive plans. One is a technique called “Adaptive Joins” and the second is called “Adaptive Parallel Distribution Methods”. This article deals with “Adaptive Joins”

For eg: If the optimizer estimated the where clause item_id = 20, is going to generate 10 rows and in reality if there were 100,000 rows in the table with item_id = 20, the optimizer might have chosen a nested loops join to this table. With adaptive plans, the optimizer gets the opportunity to switch this nested loops to a hash join, during the execution of this statement.

When the sql statement is parsed, the optimizer creates what is called a “default plan”. If there are some incorrect cardinality estimates performed by the optimizer, then it is probable that it has picked an execution plan with wrong join methods. An adaptive plan, contains multiple pre-determined “Sub plan’s”. A subplan is a portion of the plan that the optimizer can switch to as an alternative at runtime. The optimizer cannot change the whole execution plan at Execution time, it can adapt portions of it.

There are multiple “Statistics Collector’s”, inserted as rowsource’s, at key points in the execution plan.

During execution the statistics collector buffers some rows received by the subplan. Based on the information observed by the collector, the optimizer chooses a specific subplan.

Based on the observations of the statistics collectors a “Final plan” is then chosen by the optimizer and executed. After the first execution, any subsequent execution of this statement, uses this “final plan”. ie it does not incur the overhead of the statistics collection at execution time.

Let us take a look at the following sql statement and its execution plan.

SELECT product_name
FROM order_items o, product_information p
WHERE o.unit_price = 15
AND quantity > 1
AND p.product_id = o.product_id
/

This is a sql where the oracle 12c optimizer, decides to use Adaptive optimization.
If we use dbms_xplan.display_cursor to show the execution plan, it shows the following. This is the “final plan”

SQL_ID  971cdqusn06z9, child number 0                                                                                                                                      
-------------------------------------                                                                                                                                      
SELECT product_name   FROM   order_items o, product_information p                                                                                                          
WHERE  o.unit_price = 15  AND    quantity > 1   AND    p.product_id =                                                                                                      
o.product_id                                                                                                                                                               
                                                                                                                                                                           
Plan hash value: 1553478007                                                                                                                                                
                                                                                                                                                                           
------------------------------------------------------------------------------------------                                                                                 
| Id  | Operation          | Name                | Rows  | Bytes | Cost (%CPU)| Time     |                                                                                 
------------------------------------------------------------------------------------------                                                                                 
|   0 | SELECT STATEMENT   |                     |       |       |     7 (100)|          |                                                                                 
|*  1 |  HASH JOIN         |                     |     4 |   128 |     7   (0)| 00:00:01 |                                                                                 
|*  2 |   TABLE ACCESS FULL| ORDER_ITEMS         |     4 |    48 |     3   (0)| 00:00:01 |                                                                                 
|   3 |   TABLE ACCESS FULL| PRODUCT_INFORMATION |     1 |    20 |     1   (0)| 00:00:01 |                                                                                 
------------------------------------------------------------------------------------------                                                                                 
                                                                                                                                                                           
Outline Data                                                                                                                                                               
-------------                                                                                                                                                              
                                                                                                                                                                           
  /*+                                                                                                                                                                      
      BEGIN_OUTLINE_DATA                                                                                                                                                   
      FULL(@"SEL$1" "P"@"SEL$1")                                                                                                                                           
      USE_HASH(@"SEL$1" "P"@"SEL$1")                                                                                                                                       
      IGNORE_OPTIM_EMBEDDED_HINTS                                                                                                                                          
      OPTIMIZER_FEATURES_ENABLE('12.1.0.2')                                                                                                                                
      DB_VERSION('12.1.0.2')                                                                                                                                               
      ALL_ROWS                                                                                                                                                             
      OUTLINE_LEAF(@"SEL$1")                                                                                                                                               
      FULL(@"SEL$1" "O"@"SEL$1")                                                                                                                                           
      LEADING(@"SEL$1" "O"@"SEL$1" "P"@"SEL$1")                                                                                                                            
      END_OUTLINE_DATA                                                                                                                                                     
  */                                                                                                                                                                       
                                                                                                                                                                           
Predicate Information (identified by operation id):                                                                                                                        
---------------------------------------------------                                                                                                                        
                                                                                                                                                                           
   1 - access("P"."PRODUCT_ID"="O"."PRODUCT_ID")                                                                                                                           
   2 - filter(("O"."UNIT_PRICE"=15 AND "QUANTITY">1))                                                                                                                      
                                                                                                                                                                           
Note                                                                                                                                                                       
-----                                                                                                                                                                      
   - this is an adaptive plan           

Since the Notes section says that this is an adaptive plan, we can use dbms_xplan to show the “full plan”, which includes the “default plan” and the “sub plan’s”
We have to use the ADAPTIVE keyword in dbms_xplan to display the ‘full plan’.

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('971cdqusn06z9',0,'ALLSTATS LAST +ADAPTIVE'))

SQL_ID  971cdqusn06z9, child number 0                                                                                                                                      
-------------------------------------                                                                                                                                      
SELECT product_name   FROM   order_items o, product_information p                                                                                                          
WHERE  o.unit_price = 15  AND    quantity > 1   AND    p.product_id =                                                                                                      
o.product_id                                                                                                                                                               
                                                                                                                                                                           
Plan hash value: 1553478007                                                                                                                                                
                                                                                                                                                                           
--------------------------------------------------------------------------------------------------------------------------------------------------------                   
|   Id  | Operation                     | Name                   | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |                   
--------------------------------------------------------------------------------------------------------------------------------------------------------                   
|     0 | SELECT STATEMENT              |                        |      1 |        |     13 |00:00:00.01 |      24 |     20 |       |       |          |                   
|  *  1 |  HASH JOIN                    |                        |      1 |      4 |     13 |00:00:00.01 |      24 |     20 |  2061K|  2061K|  445K (0)|                   
|-    2 |   NESTED LOOPS                |                        |      1 |      4 |     13 |00:00:00.01 |       7 |      6 |       |       |          |                   
|-    3 |    NESTED LOOPS               |                        |      1 |      4 |     13 |00:00:00.01 |       7 |      6 |       |       |          |                   
|-    4 |     STATISTICS COLLECTOR      |                        |      1 |        |     13 |00:00:00.01 |       7 |      6 |       |       |          |                   
|  *  5 |      TABLE ACCESS FULL        | ORDER_ITEMS            |      1 |      4 |     13 |00:00:00.01 |       7 |      6 |       |       |          |                   
|- *  6 |     INDEX UNIQUE SCAN         | PRODUCT_INFORMATION_PK |      0 |      1 |      0 |00:00:00.01 |       0 |      0 |       |       |          |                   
|-    7 |    TABLE ACCESS BY INDEX ROWID| PRODUCT_INFORMATION    |      0 |      1 |      0 |00:00:00.01 |       0 |      0 |       |       |          |                   
|     8 |   TABLE ACCESS FULL           | PRODUCT_INFORMATION    |      1 |      1 |    288 |00:00:00.01 |      17 |     14 |       |       |          |                   
--------------------------------------------------------------------------------------------------------------------------------------------------------                   
                                                                                                                                                                           
Predicate Information (identified by operation id):                                                                                                                        
---------------------------------------------------                                                                                                                        
                                                                                                                                                                           
   1 - access("P"."PRODUCT_ID"="O"."PRODUCT_ID")                                                                                                                           
   5 - filter(("O"."UNIT_PRICE"=15 AND "QUANTITY">1))                                                                                                                      
   6 - access("P"."PRODUCT_ID"="O"."PRODUCT_ID")                                                                                                                           
                                                                                                                                                                           
Note                                                                                                                                                                       
-----                                                                                                                                                                      
   - this is an adaptive plan (rows marked '-' are inactive)        

In the plan we can see the statistics collector row source. We can see that the Index access “Access Method” was evaluated and discarded by the optimizer. You can see that the optimizer estimated 1 row from product_information and actually it returned 288 rows. The optimizer calcluates an inflection point after which the nested loops operation becomes less efficient and chooses the full scans followed by the hash join.

As indicated in the Note section, the lines that have a – at the beginning of the line, are inactive in the “Final Plan”.

Let us check the flags from v$sql

select is_reoptimizable,is_resolved_adaptive_plan
from v$sql where
sql_id = '971cdqusn06z9'

I I
- -
Y Y

The output indicates that the statement is re-optimizable and that it was resolved by using an adaptive plan.

Every step in the ‘full plan’ is stored in v$sql_plan, the information regarding which steps are ‘on’ or ‘off’ in the ‘final plan’ is stored in the column other_xml, under the xml element, display_map.

This is how it looks for sqlid 971cdqusn06z9

xml2

The op= property in the xml, maps to the id column in v$sql_plan (ie the step number). The skp= property indicates whether the step is active in the final plan or not. A value of 1 indicates that, that row is skipped in the final plan. You can display it in a row format with the following sql

select * from
  (SELECT dispmap.*
  FROM v$sql_plan,
    XMLTABLE ( '/other_xml/display_map/row' passing XMLTYPE(other_xml) COLUMNS
      op  NUMBER PATH '@op',    
      dis NUMBER PATH '@dis',   
      par NUMBER PATH '@par',   
      prt NUMBER PATH '@prt',   
      dep NUMBER PATH '@dep',   
      skp NUMBER PATH '@skp' )  
  AS dispmap
  WHERE sql_id     = '&sql_id'
  AND child_number = &sql_child
  AND other_xml   IS NOT NULL
  )
/

      OP        DIS        PAR        PRT        DEP        SKP
---------- ---------- ---------- ---------- ---------- ----------
         1          1          0          0          1          0
         2          1          1          0          1          1
         3          1          1          0          1          1
         4          1          1          0          1          1
         5          2          1          0          2          0
         6          2          1          0          1          1
         7          2          1          0          1          1
         8          3          1          0          2          0

This output can be joined with v$sql_plan to produce an output of the “final plan” .

SELECT
  NVL(map.dis,0) id,
  map.par         parent,
  map.dep         depth,
  lpad(' ',map.dep*1,' ')
  || sp.operation AS operation,
  sp.OPTIONS,
  sp.object#,
  sp.object_name
FROM v$sql_plan sp,
  (SELECT dispmap.*
  FROM v$sql_plan,
    XMLTABLE ( '/other_xml/display_map/row' passing XMLTYPE(other_xml) COLUMNS
      op  NUMBER PATH '@op',
      dis NUMBER PATH '@dis',
      par NUMBER PATH '@par',
      prt NUMBER PATH '@prt',
      dep NUMBER PATH '@dep',
      skp NUMBER PATH '@skp' )
  AS dispmap
  WHERE sql_id     = '&sql_id'
  AND child_number = &sql_child
  AND other_xml   is not null
  ) map
WHERE sp.id             = map.op(+)
AND sp.sql_Id           = '&sql_id'
AND sp.child_number     = &sql_child
AND nvl(map.skp,0)     != 1
ORDER BY nvl(map.dis,0)

If you look at the 10053 trace for the sql, you can see how the optimizer calculates an inflection point.

References

What’s new in 12c

https://martincarstenbach.wordpress.com/2015/01/13/adaptive-plans-and-vsql_plan-and-related-views/

Adaptive Plans Inflection points

Dynamic Statistics – Oracle 12c – Some notes

Adaptive Query optimization, was a set of new capabilities, introduced in oracle 12c, to allow the optimizer to discover additional information regarding statistics and make run-time adjustments to execution plans to make them better. This is a major change in the optimizer behaviour from 11g.

I would recommend anyone who is planning an upgrade to 12c, that they make themselves familiar with the following white papers from oracle.

Understanding Optimizer Statistics with Oracle 12c

Best practices for gathering statistics with Oracle 12c

What to expect with the optimizer, with Oracle 12c.

In this article, i want to talk about some of the important concepts behind Dynamic Statistics, which is one of the components of Adaptive query optimizations.

In the section’s that are following, i show some commands to turn some of these features off. I want to be clear that I am not recommending that you turn anything off. I would prefer that customer’s adopt these new features,that are designed to improve the execution plans. Also keep in mind that the following are accurate (Afaik) on 12.1.0.2 as off the time of writing of this article, and are subject to change.

Dynamic statistics has two interesting effects, that DBA’s tend to notice initially.
– Ever so slightly, longer parse times for queries.
– Execution plan changes (Compared to what they had before upgrading) for the same query. (Sometimes unwelcome changes, especially for deployments that value stability more than performance gains).

Dynamic sampling was introduced by oracle in 9i Release 2 to improve the optimizer’s functioning. The amount of dynamic sampling done , and when it kicks in, is controlled by the parameter optimizer_dynamic_sampling. With 12c there is a new concept of Dynamic Statistics. Dynamic Statistics is different from the pre-12c traditional dynamic sampling, in the following aspects.

– Dynamic Statistics could kick in even when optimizer_dynamic_sampling is set to 2.

– Especially for parallel queries on large tables.

– Dynamic Statistics  kicks in when optimizer_dynamic_sampling is set to 11.
– Dynamic Statistics  issues more queries than dynamic sampling used to do.

– The traditional dynamic sampling, used to issue, atmost, 1 query per table SQL.
– It is not uncommon to see 10’s of Dynamic Statistics  queries being issued for a     single SQL. Multiple dynamic sampling queries for the same table.(It dpends on the volume of data, number of indexed columns, complexity of     the predicates etc).
– If you run a 10046 trace on the query, you will see a lot of additional queries in there that     have the DS_SVC hint in them, which are the queries issued by Dynamic Statistics.

Setting OPTIMIZER_ADAPTIVE_FEATURES=FALSE does NOT turn off Dynamic Statistics.

You can set Optimizer_Dynamic_Sampling = 0 to turn Dynamic Statistics  off. However this would be like throwing the baby out with the bath water. Setting Optimizer_Dynamic_Sampling=0 completely sets dynamic sampling off (Including the old style pre-12c dynamic sampling).

You can do an ALTER SESSION SET “_fix_control”=’7452863:0′; to turn just Dynamic Statistics off, if you so desire.

Dynamic Statistics  also uses oracle Results Cache. If results cache is enabled in the database (usually by setting result_cache_max_size to a value > 0), then Dynamic Statistics  uses the results cache to store the results that it queries up. This is done so that, if there are multiple query parses that have to do dynamic sampling, and they are looking at the same data, the optimizer can just look that value up from the results cache (As opposed to having to query the tables again and again).

If you have a system, that has a lot of hard parses (Due to not using bind variables), you could pottentialy see latch free waits on “Results Cache: rc latch”. Please refer to Mos note 2002089.1, that suggest’s setting “_optimizer_ads_use_result_cache” = FALSE; to work around this. Keep in mind that setting this parameter does not prevent the optimizer from using Dynamic Statistics . All it does it prevent the Dynamic Statistics  from using the results cache.

Examples of Dynamic Statistics usage.

The following Mos note’s and a presentation from Trivadis, have a lot of great information in this regard.

Automatic Dynamic Statistics (Doc ID 2002108.1)

Different Level for Dynamic Statistics (Dynamic Sampling) used than the Level Specified (Doc ID 1102413.1)

High Waits for ‘cursor: pin S wait on X’ due to Dynamic Sampling Against Parallel Queries (Doc ID 2006145.1)

Adaptive Dynamic Sampling – Trivadis