Cloud Networking Basics for the Non CCNA certified – IP Address

As more and more enterprises are migrating their IT deployments to the Public Cloud, several IT personnel who are not classically trained on networking theory and practice, are routinely having to architect, setup, deploy, manage and support these Cloud based implementations. This blog post is aimed at someone who is a DBA or Developer in an organization who has to support such an implementation (Let us say a migration of on-premises assets to the public cloud), to give them a basic understanding, of networking terminology involved, in a Public Cloud implementation.

The following discussion focuses on IPv4 IP addresses.

What is an Octet ?

Octet is a unit of digital information, that consists of 8 bits.

Eg: 10110001, 11111111

Ip address Basics.

An IP address is a numerical label assigned to each device connected to a computer network. An IPv4 IP address is usually of the form xxx.xxx.xxx.xxx.

Eg: 192.168.5.129

Ip address in Octal.

An IP address is made up of 4 Octets.

Each Bit in the octet represents a power of 2
27,26,25,24,23,22,21,20

128,64,32,16,8,4,2,1

So If you had an octet 11111111, in order to convert it to decimal, you would add up each of the bits place values as shown below.

128+64+32+16+8+4+2+1 = 255

Let us convert an IP address, into its Binary Octets representation.

Eg: 192.168.5.129
11000000.10101000.00000101.10000001

Let us take a look at the First and second Octet’s
192 = 11000000 = 27 + 26 = 128 + 64

168 = 10101000 = 27 + 0 + 25 + 0 + 2^3 = 128+32+8

You can continue the process with the third and fourth octet.

Private and Public IP Address Ranges

A Public IP address is one that is Routable on the Public Internet, and a Private IP address is one that is not routable on the internet.

RFC 3330 specifies the Public IP address Ranges as follows

1.0.0.0 - 9.255.255.255
11.x.x.x - 126.255.255.255
129.0.0.0 - 169.253.255.255
169.255.0.0 - 172.15.255.255
172.32.0.0 - 191.0.1.255
192.0.3.0 - 192.88.98.255
192.88.100.0 - 192.167.255.255
192.169.0.0 - 198.17.255.255
198.20.0.0 - 223.255.255.255

RFC 1918 specifies the Private IP address Ranges as follows

10.0.0.1 - 10.255.255.254
172.16.0.1 - 172.31.255.254
192.168.0.1 - 192.168.255.254

IP SubNetworks

Ip networks may be subdivided into subnetworks (aka subnets). For this purpose, an IP address is logically recognized as consisting of two parts: the network prefix and the host identifier. The subnet mask or the CIDR prefix, determines how the IP address is divided, into network and host parts.

A detailed explanation of IPv4 subnets can be found here

CIDR Block

CIDR stands for classless Inter Domain Routing. CIDR notation is a way to represent a block of IP addresses. It is represented as a dotted decimal address followed by a slash.

For the folks who just want to understand, what it means to them in the cloud, at the very basic level, a CIDR Block is just a list of IP addresses that represent a subnet. For the curious, who need to know how exactly the IP addresses are derived from a CIDR Block, read the explanation below.

If you prefer a Video Tutorial, you can find an Excellent YouTube video on this Topic by Joshua Butcher here.

Eg: 172.10.85.60/22

If we are given a CIDR block as shown above, we can derive the following.
1) The network ID of the IP address
2) The broadcast/host ID of the IP address
3) The netmask
4) The usable IP addresses in this CIDR block

The calculations are shown below.

Here is the decimal value of each bit in the octet as we showed in the previous section

128,64,32,16,8,4,2,1

/22 tells us that, there are 22 bits in the network portion all turned on

Let us write the /22 in its Octal representation

11111111.11111111.11111100.00000000

The Decimal version of which is

255.255.252.0

So The netmask for 172.10.85.60/22 is 255.255.252.0

The Magic number is determined as follows
– Find the location of the the last bit in the network portion.
– In our case this is the 6th bit in the 3rd octet.
– The value of the 6th bit in the octet is 4, which is he magic number.

So the initial network ID is 172.10.X.0 , where X has to be determined.

The initial broadcast ID is 172.10.Y.255, where Y has to be determined.

Take the 3rd Octet from the IP address (85) and convert it to binary.

01010101 and AND it with the 3rd octet of the netmask
11111100

——–
01010100 = 84. This is the value of X

Add the magic number to this 84+4 = 88 (4 would be the next network Id, so 4-1 would be the last ID usable)

Subtract 88-1 = 87, This is the value Y

So network ID is 172.10.84.0 and Broadcast ID is 172.10.87.255.

The usable IP’s are 172.10.84.1 to 172.10.86.254.

So we have learned that, the given CIDR block 172.10.85.60/22 represents
– A netmask of 255.255.252.0
– A usable IP address range of 172.10.84.1 to 172.10.86.254
– 1024 Usable IP’s in the subnet.

You can use the webpage https://www.ipaddressguide.com/cidr, to translate your CIDR blocks into IP-Address ranges.

The reason we use subnets in the public cloud is that, we can create separate segments of our cloud network, which house different types of hosts (Based on Security and Availability requirements). With different subnets, we have the ability to granularly control, the type of network traffic that is allowed in and out of specific type of hosts (For eg: Databases can be placed in a isolated subnet that allows only TCP/IP traffic on port 1521 or a port of your choice).

Python script to query zillow

Here is a script that can be used (tested with python3 only) to query price information from zillow using the zillow API.

zillow.py

First you should register with zillow and get yourself a Zillow Web Services ID, following the steps outlined at https://www.zillow.com/howto/api/APIOverview.htm.

Then edit the script and substitute the string (On line 7) YourZillowWebServicesIdHere, with your own Zillow Web services id.

Then you can run the script as follows.

python3 zillow.py address zipcode (Both parameters are mandatory)

Eg : python3 zillow.py “4 zippy lane” 06194

Be sure to use a valid address and zipcode or else the script will error.

You will get an output similar to the output below. (Click on the image to see a larger version).

This is an easy way to checkout houses and prices of comparable houses in the same locality.

Hope someone finds this helpful.

 

12c Adaptive Query Optimization – Getting Started

In Oracle Database 12c, adaptive query optimization is a set of capabilities that enables the optimizer to make run-time adjustments to execution plans and discover additional information that can lead to better statistics. Adaptive optimization is helpful when existing statistics are not sufficient to generate an optimal plan.

aqo

Below are a series of blog posts that I have written, that explains the Basics of how these features work.

Dynamic Statistics – Some Notes

Adaptive Plans – Adaptive Joins

Adaptive Plans – Adaptive Parallel Distribution Methods

AQO – Dynamic Statistics Examples

AQO – ReOptimization

AQP – SQL Plan Directives

 

Below are some oracle white papers and Mos notes that you could read, to familiarize yourselves with these new features.

Understanding Optimizer Statistics with Oracle 12c

Best practices for gathering statistics with Oracle 12c

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

Adaptive Query Optimization – Mos Note : 2031605.1

Automatic Dynamic Statistics – Mos Note : 2002108.1

Adaptive Execution Plans – Mos Note : 1409636.1

How to Approach Issues Related to Adaptive Optimizations – Mos Note : 2068807.1

Optimizer Statistics – Master Note – Mos Note : 1369591.1

Resolving Issues of Library Cache Pin or Cursor Pin S Wait on X – Mos Note : 1476663.1

Troubleshooting Cursor Pin S Wait on X – Mos Note : 1349387.1

Cursor Pin S Wait on X, Finding Blocker – Mos Note : 786507.1

Parameter Recommendations for Oracle 12c – Part 1 (Upgrade PM Blog)

Parameter Recommendations for Oracle 12c – Part 2 (Upgrade PM Blog)

Things to consider before upgrading to 12.1.0.2 (Upgrade PM Blog)

Adaptive Query Optimization configuration : Parameters, Preferences and Fix controls (Christian Antognini Blog)

Adaptive Query Optimization – SQL Plan Directives

Sql plan directives are automatically created based on information learned via Automatic Reoptimization. The optimizer uses Sql Plan Directives to generate a more optimal execution plan. For eg: When joining two tables that have a data skew in their join columns, a Sql plan directive can direct the optimizer to use dynamic statistics to obtain a more accurate join cardinality estimate.

Key thing to note is that SPD’s are not created on Queries or Objects. They are created on query expressions. So the SPD’s generated by one sql statement can be used by multiple sql statements. It is possible for one Sql statement to use more than one SPD. SPD’s are persisted in the SYSAUX tablespace. Any SPD that is unused for 53 weeks will be automatically purged.

After a SQL directive is used the optimizer decides if the cardinality missestimate could be resolved with a column group. If so it will create the column group, the next time statistics are gathered on the appropriate table.

Let us take a look at an example.

We are using the same sql statement from the example in my post about Adaptive ReOptimization

SQL_ID  faca0yc2h1cdr, child number 0                                                                                                                                                                   
-------------------------------------                                                                                                                                                                   
SELECT /*+ gather_plan_statistics */ count(*)  FROM   customers  WHERE                                                                                                                                  
cust_state_province = 'CA'  AND    country_id=52790                                                                                                                                                     
 
Plan hash value: 296924608                                                                                                                                                                              
 
--------------------------------------------------------------------------------------------------------------------------                                                                              
| Id  | Operation          | Name      | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |                                                                              
--------------------------------------------------------------------------------------------------------------------------                                                                              
|   0 | SELECT STATEMENT   |           |      1 |        |       |   405 (100)|          |      1 |00:00:00.01 |    1458 |                                                                              
|   1 |  SORT AGGREGATE    |           |      1 |      1 |    16 |            |          |      1 |00:00:00.01 |    1458 |                                                                              
|*  2 |   TABLE ACCESS FULL| CUSTOMERS |      1 |   1115 | 17840 |   405   (1)| 00:00:01 |   3341 |00:00:00.01 |    1458 |                                                                              
--------------------------------------------------------------------------------------------------------------------------                                                                              
 
Query Block Name / Object Alias (identified by operation id):                                                                                                                                           
-------------------------------------------------------------                                                                                                                                           
 
   1 - SEL$1                                                                                                                                                                                            
   2 - SEL$1 / CUSTOMERS@SEL$1                                                                                                                                                                          
 
Predicate Information (identified by operation id):                                                                                                                                                     
---------------------------------------------------                                                                                                                                                     
 
   2 - filter(("CUST_STATE_PROVINCE"='CA' AND "COUNTRY_ID"=52790))                                                                                                                                      
 
Column Projection Information (identified by operation id):                                                                                                                                             
-----------------------------------------------------------                                                                                                                                             
 
   1 - (#keys=0) COUNT(*)[22]                                                                                                                                                                           
   2 - (rowset=906)

Next I will flush the plan directive

BEGIN
DBMS_SPD.FLUSH_SQL_PLAN_DIRECTIVE;
END;
/

Lets check the plan directives that have been created

DIR_ID                                   OWNER OBJECT_NAME               COL_NAME             OBJECT_TYPE               TYPE                    STATE      INST
---------------------------------------- ----- ------------------------- -------------------- ------------------------- ----------------------- ---------- -------------
REASON                               DC                          LU                         
------------------------------------ --------------------------- ---------------------------
1581183467014446692                      SH    CUSTOMERS                 COUNTRY_ID           COLUMN                    DYNAMIC_SAMPLING        USABLE     NEW           
SINGLE TABLE CARDINALITY MISESTIMATE 20-SEP-16 08:32:22                                     
 
1581183467014446692                      SH    CUSTOMERS                 CUST_STATE_PROVINCE  COLUMN                    DYNAMIC_SAMPLING        USABLE     NEW           
SINGLE TABLE CARDINALITY MISESTIMATE 20-SEP-16 08:32:22                                     
 
1581183467014446692                      SH    CUSTOMERS                                      TABLE                     DYNAMIC_SAMPLING        USABLE     NEW           
SINGLE TABLE CARDINALITY MISESTIMATE 20-SEP-16 08:32:22

Then let us a execute a new statement, by just changing one of the predicates.

SQL_ID  aj2w592n68s4s, child number 0                                                                                                                                                                   
-------------------------------------                                                                                                                                                                   
SELECT /*+ gather_plan_statistics */ count(*)  FROM   customers  WHERE                                                                                                                                  
cust_state_province = 'FL'  AND    country_id=52790                                                                                                                                                     
 
Plan hash value: 296924608                                                                                                                                                                              
 
--------------------------------------------------------------------------------------------------------------------------                                                                              
| Id  | Operation          | Name      | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |                                                                              
--------------------------------------------------------------------------------------------------------------------------                                                                              
|   0 | SELECT STATEMENT   |           |      1 |        |       |   405 (100)|          |      1 |00:00:00.01 |    1457 |                                                                              
|   1 |  SORT AGGREGATE    |           |      1 |      1 |    16 |            |          |      1 |00:00:00.01 |    1457 |                                                                              
|*  2 |   TABLE ACCESS FULL| CUSTOMERS |      1 |   2549 | 40784 |   405   (1)| 00:00:01 |   2438 |00:00:00.01 |    1457 |                                                                              
--------------------------------------------------------------------------------------------------------------------------                                                                              
 
Query Block Name / Object Alias (identified by operation id):                                                                                                                                           
-------------------------------------------------------------                                                                                                                                           
 
   1 - SEL$1                                                                                                                                                                                            
   2 - SEL$1 / CUSTOMERS@SEL$1                                                                                                                                                                          
 
Predicate Information (identified by operation id):                                                                                                                                                     
---------------------------------------------------                                                                                                                                                     
 
   2 - filter(("CUST_STATE_PROVINCE"='FL' AND "COUNTRY_ID"=52790))                                                                                                                                      
 
Column Projection Information (identified by operation id):                                                                                                                                             
-----------------------------------------------------------                                                                                                                                             
 
   1 - (#keys=0) COUNT(*)[22]                                                                                                                                                                           
   2 - (rowset=906)                                                                                                                                                                                     
 
Note                                                                                                                                                                                                    
-----                                                                                                                                                                                                   
   - dynamic statistics used: dynamic sampling (level=2)                                                                                                                                                
   - 1 Sql Plan Directive used for this statement

In the notes section we can see that the plan directive has been used by this new statement.

Adaptive Query Optimization – Adaptive ReOptimization

Adaptive plans can choose between multiple operations during the execution of a sql to pottentially choose better access methods. But it cannot change the join orders. 11gR2 had cardinality feedback which allowed oracle to choose a better plan based on the information gathered at the end of the execution. In 12c Cardinality feedback has been renamed as Statistics Feedback.

Statistics Feedback

During the first execution of the statement, the optimizer generates an execution plan and decides if it should enable statistics feedback monitoring for the cursor. Statistics feedback is enabled in the following cases.

  • Table with no statistics
  • Multiple Conjunctive or Disjunctive predicates
  • Predicates containing complex operators for which optimizer cannot accurately compute cardinality estimates.

At the end of the excution if the actual cardinalities are different from the estimated cardinalities, the actual cardinalities are stored for subsequent use. It also creates sql plan directives, so other sql statements can benefit from this information. The statement is marked as ReOptimizable and will be Re-Parsed at the next execution and creates a new child cursor. The actual cardinalities are used during the Re-Parsing to determine a good execution plan.

Lets look at an example

SQL_ID  faca0yc2h1cdr, child number 0                                                                                                                                                                   
-------------------------------------                                                                                                                                                                   
SELECT /*+ gather_plan_statistics */ count(*)  FROM   customers  WHERE                                                                                                                                  
cust_state_province = 'CA'  AND    country_id=52790                                                                                                                                                     
 
Plan hash value: 296924608                                                                                                                                                                              
 
--------------------------------------------------------------------------------------------------------------------------                                                                              
| Id  | Operation          | Name      | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |                                                                              
--------------------------------------------------------------------------------------------------------------------------                                                                              
|   0 | SELECT STATEMENT   |           |      1 |        |       |   405 (100)|          |      1 |00:00:00.01 |    1458 |                                                                              
|   1 |  SORT AGGREGATE    |           |      1 |      1 |    16 |            |          |      1 |00:00:00.01 |    1458 |                                                                              
|*  2 |   TABLE ACCESS FULL| CUSTOMERS |      1 |   1115 | 17840 |   405   (1)| 00:00:01 |   3341 |00:00:00.01 |    1458 |                                                                              
--------------------------------------------------------------------------------------------------------------------------                                                                              
 
Query Block Name / Object Alias (identified by operation id):                                                                                                                                           
-------------------------------------------------------------                                                                                                                                           
 
   1 - SEL$1                                                                                                                                                                                            
   2 - SEL$1 / CUSTOMERS@SEL$1                                                                                                                                                                          
 
Predicate Information (identified by operation id):                                                                                                                                                     
---------------------------------------------------                                                                                                                                                     
 
   2 - filter(("CUST_STATE_PROVINCE"='CA' AND "COUNTRY_ID"=52790))                                                                                                                                      
 
Column Projection Information (identified by operation id):                                                                                                                                             
-----------------------------------------------------------                                                                                                                                             
 
   1 - (#keys=0) COUNT(*)[22]                                                                                                                                                                           
   2 - (rowset=906)

Let us check the details of the cursor

select plan_hash_value phv,child_number,parse_calls,executions,is_bind_sensitive ibs
      ,is_bind_aware iba,is_reoptimizable ropt,is_resolved_adaptive_plan aplan 
from v$sql where sql_id = 'faca0yc2h1cdr'
 
       PHV CHILD_NUMBER PARSE_CALLS EXECUTIONS I I R A
---------- ------------ ----------- ---------- - - - -
 296924608	      0 	  1	     1 N N Y

So we can see that the cursor has been flagged as ReOptimizable.

Let’s Execute the Same Sql again.

SQL_ID  faca0yc2h1cdr, child number 1                                                                                                                                                                   
-------------------------------------                                                                                                                                                                   
SELECT /*+ gather_plan_statistics */ count(*)  FROM   customers  WHERE                                                                                                                                  
cust_state_province = 'CA'  AND    country_id=52790                                                                                                                                                     
 
Plan hash value: 296924608                                                                                                                                                                              
 
--------------------------------------------------------------------------------------------------------------------------                                                                              
| Id  | Operation          | Name      | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |                                                                              
--------------------------------------------------------------------------------------------------------------------------                                                                              
|   0 | SELECT STATEMENT   |           |      1 |        |       |   405 (100)|          |      1 |00:00:00.01 |    1457 |                                                                              
|   1 |  SORT AGGREGATE    |           |      1 |      1 |    16 |            |          |      1 |00:00:00.01 |    1457 |                                                                              
|*  2 |   TABLE ACCESS FULL| CUSTOMERS |      1 |   3341 | 53456 |   405   (1)| 00:00:01 |   3341 |00:00:00.01 |    1457 |                                                                              
--------------------------------------------------------------------------------------------------------------------------                                                                              
 
Query Block Name / Object Alias (identified by operation id):                                                                                                                                           
-------------------------------------------------------------                                                                                                                                           
 
   1 - SEL$1                                                                                                                                                                                            
   2 - SEL$1 / CUSTOMERS@SEL$1                                                                                                                                                                          
 
Predicate Information (identified by operation id):                                                                                                                                                     
---------------------------------------------------                                                                                                                                                     
 
   2 - filter(("CUST_STATE_PROVINCE"='CA' AND "COUNTRY_ID"=52790))                                                                                                                                      
 
Column Projection Information (identified by operation id):                                                                                                                                             
-----------------------------------------------------------                                                                                                                                             
 
   1 - (#keys=0) COUNT(*)[22]                                                                                                                                                                           
   2 - (rowset=906)                                                                                                                                                                                     
 
Note                                                                                                                                                                                                    
-----                                                                                                                                                                                                   
   - statistics feedback used for this statement

Let’s check the details of the cursor

       PHV CHILD_NUMBER PARSE_CALLS EXECUTIONS I I R A
---------- ------------ ----------- ---------- - - - -
 296924608	      0 	  1	     1 N N Y
 296924608	      1 	  1	     1 N N N

We can see that a new child cursor has been created.
The notes secton indicates that Statistics feedback has been used for this statement.

Performance Feedback

When AutoDOP is enabled in Adaptive mode, during first execution of the statement, the optimizer determines whether the statement should execute in parallel, and what DOP should be used. After the first execution, the execution stats is used to compute a DOP. If the computed DOP is different from the original DOP the statement is marked for re-optimization and the execution statistics is stored. When the statement is executed again the executio statistics is used to compute a new DOP.

12c Adaptive Query Optimization – Dynamic Statistics – Examples

Dynamic sampling is used to compensate for missing or insufficient statistics, that would otherwise lead to a bad plan. In earlier versions, dynamic sampling used to kick in for tables that had missing statistics or stale statistics. Dynamic sampling has been enhanced in 12c and is now called dynamic statistics (And most of the functionality, including the new level 11 has been back ported to 11.2.0.4). The optimizer bases its decision to use dynamic statistics, on the complexity of the predicates used, existing base statistics, total execution time expected,Statistics feedback, Sql plan directives etc. The optimizer automatically decides whether dynamic statistics are useful, and what level to use.

Automatic dynamic statistics are enabled when optimizer_dynamic_statistics=2 (Default) or 11.

I have outlined some nuances of Dynamic Statistics in this previous article.

In this article i will show you examples of Dynamic Statistics kicking in for the following situations.

Missing statistics
Stale statistics
Parallel query
Complex where clause
Group by clause

In this Blog post I am not addressing the scenario where dynamic statistics is used , when Sql plan directives are present. I will address that in a future post regarding sql plan directives.

Heads up, this will be a long blog post.

Missing Statistics

When a new table is created, and statistics was not gathered on the table, then oracle will perform dynamic sampling during the optimization of the query, so it can choose a good execution plan.

In the example below, I am creating a new table with data in it, Creating an index on it, and then deleting the statistics on the table.

create table t1
as
select
         *
from
         customers
;
 
create index t1_idx1 on t1 (cust_state_province)
;
 
exec dbms_stats.delete_table_stats(upper('SH'),upper('T1'));
commit;
 
select /*+ gather_plan_statistics */ count(*) from t1 where cust_state_province  = 'CA'

Then I execute a query against the table.

SQL_ID  3azum47vccux0, child number 0                                                                                                                                                                   
-------------------------------------   
select /*+ gather_plan_statistics */ count(*) from t1 where                                                                                                                                             
cust_state_province  = 'CA'                                                                                                                                                                             
 
Plan hash value: 3695297570                                                                                                                                                                             
 
--------------------------------------------------------------------------------------------------------------------------------                                                                        
| Id  | Operation         | Name    | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |                                                                        
--------------------------------------------------------------------------------------------------------------------------------                                                                        
|   0 | SELECT STATEMENT  |         |      1 |        |       |    12 (100)|          |      1 |00:00:00.01 |       9 |      2 |                                                                        
|   1 |  SORT AGGREGATE   |         |      1 |      1 |    22 |            |          |      1 |00:00:00.01 |       9 |      2 |                                                                        
|*  2 |   INDEX RANGE SCAN| T1_IDX1 |      1 |   4054 | 89188 |    12   (0)| 00:00:01 |   3341 |00:00:00.01 |       9 |      2 |                                                                        
--------------------------------------------------------------------------------------------------------------------------------                                                                        
 
Query Block Name / Object Alias (identified by operation id):                                                                                                                                           
-------------------------------------------------------------                                                                                                                                           
 
   1 - SEL$1                                                                                                                                                                                            
   2 - SEL$1 / T1@SEL$1                                                                                                                                                                                 
 
Predicate Information (identified by operation id):                                                                                                                                                     
---------------------------------------------------                                                                                                                                                     
 
   2 - access("CUST_STATE_PROVINCE"='CA')                                                                                                                                                               
 
Column Projection Information (identified by operation id):                                                                                                                                             
-----------------------------------------------------------                                                                                                                                             
 
   1 - (#keys=0) COUNT(*)[22]                                                                                                                                                                           
 
Note                                                                                                                                                                                                    
-----                                                                                                                                                                                                   
   - dynamic statistics used: dynamic sampling (level=2)

In the Notes section of the execution plan, we can see that dynamic statistics was used with level 2.Since there was no statistics on the table or index, the optimizer performed dynamic sampling, to get estimates. When we review the 10046 trace file and its tkprof output we can see that there are 2 statements executed which has the hint /*+ OPT_DYN_SAMP */ that tells us that dynamic sampling has been performed.

Stale Statistics

In the example below, in order to simulate the behavior of stale statistics, I have done the following

  • Created a table, gathered stats
  • Run a query, and flushed the usage statistics.
  • Inserted more rows into the table.
  • Run a new query, on the table whose statistics is now stale.
  • In order to get the dynamic sampling to kick in, i had to set the optimizer_dynamic_sampling to 11. (Does not kick in when level is 2)

Here is the setup for the example.

create table t1
compress
as
select
         *
from
         customers
;
 
create index t1_idx1 on t1 (cust_state_province)
;
 
exec dbms_stats.gather_table_stats(upper('SH'),upper('T1'));
commit;
 
select /*+ gather_plan_statistics */ count(*) from t1 where cust_state_province  = 'CA'
;
 
connect sh/sh@10.1.1.202:1521/rkpdb1
 
insert into t1 select * from t1;
commit;
 
connect system/manager@10.1.1.202:1521/rkpdb1
exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
 
connect sh/sh@10.1.1.202:1521/rkpdb1
alter session set optimizer_dynamic_sampling=11
;
@traceon
select /*+ gather_plan_statistics q2 */ count(*) from t1 where cust_state_province  = 'CA'
;

Here is the execution plan for the statement

PLAN_TABLE_OUTPUT                                                                                                                                                                                       
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  1gwst4v211h86, child number 0                                                                                                                                                                   
-------------------------------------                                                                                                                                                                   
select /*+ gather_plan_statistics q2 */ count(*) from t1 where                                                                                                                                          
cust_state_province  = 'CA'                                                                                                                                                                             
 
Plan hash value: 3695297570                                                                                                                                                                             
 
-----------------------------------------------------------------------------------------------------------------------                                                                                 
| Id  | Operation         | Name    | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |                                                                                 
-----------------------------------------------------------------------------------------------------------------------                                                                                 
|   0 | SELECT STATEMENT  |         |      1 |        |       |    21 (100)|          |      1 |00:00:00.01 |      20 |                                                                                 
|   1 |  SORT AGGREGATE   |         |      1 |      1 |    11 |            |          |      1 |00:00:00.01 |      20 |                                                                                 
|*  2 |   INDEX RANGE SCAN| T1_IDX1 |      1 |   6591 | 72501 |    21   (0)| 00:00:01 |   6682 |00:00:00.01 |      20 |                                                                                 
-----------------------------------------------------------------------------------------------------------------------                                                                                 
 
Query Block Name / Object Alias (identified by operation id):                                                                                                                                           
-------------------------------------------------------------                                                                                                                                           
 
   1 - SEL$1                                                                                                                                                                                            
   2 - SEL$1 / T1@SEL$1                                                                                                                                                                                 
 
Predicate Information (identified by operation id):                                                                                                                                                     
---------------------------------------------------                                                                                                                                                     
 
   2 - access("CUST_STATE_PROVINCE"='CA')                                                                                                                                                               
 
Column Projection Information (identified by operation id):                                                                                                                                             
-----------------------------------------------------------                                                                                                                                             
 
   1 - (#keys=0) COUNT(*)[22]                                                                                                                                                                           
 
Note                                                                                                                                                                                                    
-----                                                                                                                                                                                                   
   - dynamic statistics used: dynamic sampling (level=AUTO)

Now you can see in the note section that dynamic sampling with level AUTO, has been utilized for this query. If you examine the 10046  trace file you will see that there are 2 queries that are executed with the DS_SVC hint in it.

Parallel Query

The following parallel query selects from the tables in the SSB  (Star Schema Benchmark) schema. The execution plan is rather large, so I am not including it below. I am only including the Notes section.

 

SQL_ID  6af5d5js0m4wg, child number 0                                                                                                                                                                   
-------------------------------------                                                                                                                                                                   
SELECT /*+ gather_plan_statistics parallel(4) */                                                                                                                                                        
d_month,s_region,c_region,sum(lo_quantity) FROM                                                                                                                                                         
ssb.lineorder,ssb.date_dim,ssb.customer,ssb.supplier,ssb.part  where                                                                                                                                    
lo_orderdate = d_datekey and    lo_custkey   = c_custkey and                                                                                                                                            
lo_suppkey   = s_suppkey and    lo_partkey   = p_partkey and    d_year                                                                                                                                  
     = 1997 and    p_mfgr       = 'MFGR#1' and    p_category   =                                                                                                                                        
'MFGR#11' and    p_brand1     = 'MFGR#1110' and    s_nation     =                                                                                                                                       
'UNITED STATES' and    c_nation     = 'CHINA' group by                                                                                                                                                  
d_month,s_region,c_region                                                                                                                                                                               
 
Plan hash value: 1014051437                                                                                                                                                                             
 
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---                                                                                                                                                                                                     
 
| Id  | Operation                          | Name      | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |    TQ  |IN-OUT| PQ Distrib | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem |  O/1/M 
  |                                                                                                                                                                                                     
 
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---                                                                                                                                                                                                     
 
|   0 | SELECT STATEMENT                   |           |      1 |        |       | 14066 (100)|          |        |      |            |      3 |00:00:24.64 |      56 |      0 |       |       |        
  |                                                                                                                                                                                                     
 
|   1 |  PX COORDINATOR                    |           |      1 |        |       |            |          |        |      |            |      3 |00:00:24.64 |      56 |      0 | 73728 | 73728 |        
 
...
...
...
Note                                                                                                                                                                                                    
 
PLAN_TABLE_OUTPUT                                                                                                                                                                                       
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----                                                                                                                                                                                                   
   - dynamic statistics used: dynamic sampling (level=5)                                                                                                                                                
   - Degree of Parallelism is 4 because of hint

As you can see in the Note section, the query has performed dynamic sampling. If you remove the parallel hint and run the query then dynamic sampling does not occur.
Here I did not have to modify the optimizer_dynamic_sampling from the default, dynamic statistics still kicks in because it is a parallel query.

Complex Where Clause

Below is a sql with conjunctive predicates in the where clause. This causes oracle to collect dynamic statistics for this query.

 

SELECT /*+ gather_plan_statistics */ *  FROM   customers  WHERE                                                                                                                                         
cust_state_province='CA'  AND    country_id=52790                                                                                                                                                       
 
Plan hash value: 2008213504                                                                                                                                                                             
 
-------------------------------------------------------------------------------------------------------------------------                                                                               
| Id  | Operation         | Name      | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |                                                                               
-------------------------------------------------------------------------------------------------------------------------                                                                               
|   0 | SELECT STATEMENT  |           |      1 |        |       |   405 (100)|          |   3341 |00:00:00.01 |    1677 |                                                                               
|*  1 |  TABLE ACCESS FULL| CUSTOMERS |      1 |   3354 |   592K|   405   (1)| 00:00:01 |   3341 |00:00:00.01 |    1677 |                                                                               
-------------------------------------------------------------------------------------------------------------------------                                                                               
 
Query Block Name / Object Alias (identified by operation id):                                                                                                                                           
-------------------------------------------------------------                                                                                                                                           
 
   1 - SEL$1 / CUSTOMERS@SEL$1                                                                                                                                                                          
 
Predicate Information (identified by operation id):                                                                                                                                                     
---------------------------------------------------                                                                                                                                                     
 
   1 - filter(("CUST_STATE_PROVINCE"='CA' AND "COUNTRY_ID"=52790))                                                                                                                                      
 
Column Projection Information (identified by operation id):                                                                                                                                             
-----------------------------------------------------------                                                                                                                                             
 
   1 - "CUSTOMERS"."CUST_ID"[NUMBER,22], "CUSTOMERS"."CUST_FIRST_NAME"[VARCHAR2,20],                                                                                                                    
       "CUSTOMERS"."CUST_LAST_NAME"[VARCHAR2,40], "CUSTOMERS"."CUST_GENDER"[CHARACTER,1],                                                                                                               
       "CUSTOMERS"."CUST_YEAR_OF_BIRTH"[NUMBER,22], "CUSTOMERS"."CUST_MARITAL_STATUS"[VARCHAR2,20],                                                                                                     
       "CUSTOMERS"."CUST_STREET_ADDRESS"[VARCHAR2,40], "CUSTOMERS"."CUST_POSTAL_CODE"[VARCHAR2,10],                                                                                                     
       "CUSTOMERS"."CUST_CITY"[VARCHAR2,30], "CUSTOMERS"."CUST_CITY_ID"[NUMBER,22], "CUST_STATE_PROVINCE"[VARCHAR2,40],                                                                                 
       "CUSTOMERS"."CUST_STATE_PROVINCE_ID"[NUMBER,22], "COUNTRY_ID"[NUMBER,22],                                                                                                                        
       "CUSTOMERS"."CUST_MAIN_PHONE_NUMBER"[VARCHAR2,25], "CUSTOMERS"."CUST_INCOME_LEVEL"[VARCHAR2,30],                                                                                                 
       "CUSTOMERS"."CUST_CREDIT_LIMIT"[NUMBER,22], "CUSTOMERS"."CUST_EMAIL"[VARCHAR2,30],                                                                                                               
       "CUSTOMERS"."CUST_TOTAL"[VARCHAR2,14], "CUSTOMERS"."CUST_TOTAL_ID"[NUMBER,22],                                                                                                                   
       "CUSTOMERS"."CUST_SRC_ID"[NUMBER,22], "CUSTOMERS"."CUST_EFF_FROM"[DATE,7], "CUSTOMERS"."CUST_EFF_TO"[DATE,7],                                                                                    
       "CUSTOMERS"."CUST_VALID"[VARCHAR2,1]                                                                                                                                                             
 
Note                                                                                                                                                                                                    
-----                                                                                                                                                                                                   
   - dynamic statistics used: dynamic sampling (level=AUTO)

Here we can see that oracle performed dynamic sampling with level Auto.
I had to set optimizer_dynamic_sampling=11 for this query to perform dynamic sampling.

Group By Clause

The statement below performs dynamic sampling because the groupby clause causes a cardinality misestimate.

 

SQL_ID  8at4un9zmq38c, child number 0                                                                                                                                                                   
-------------------------------------                                                                                                                                                                   
SELECT /*+ gather_plan_statistics */ count(*)  FROM   sh.customers                                                                                                                                      
group by COUNTRY_ID,CUST_STATE_PROVINCE                                                                                                                                                                 
 
Plan hash value: 1577413243                                                                                                                                                                             
 
-----------------------------------------------------------------------------------------------------------------------------------------------------                                                   
| Id  | Operation          | Name      | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem |  O/1/M   |                                                   
-----------------------------------------------------------------------------------------------------------------------------------------------------                                                   
|   0 | SELECT STATEMENT   |           |      1 |        |       |   407 (100)|          |    145 |00:00:00.01 |    1457 |       |       |          |                                                   
|   1 |  HASH GROUP BY     |           |      1 |    145 |  2320 |   407   (1)| 00:00:01 |    145 |00:00:00.01 |    1457 |  1016K|  1016K|     1/0/0|                                                   
|   2 |   TABLE ACCESS FULL| CUSTOMERS |      1 |  55500 |   867K|   405   (1)| 00:00:01 |  55500 |00:00:00.01 |    1457 |       |       |          |                                                   
-----------------------------------------------------------------------------------------------------------------------------------------------------                                                   
 
Query Block Name / Object Alias (identified by operation id):                                                                                                                                           
-------------------------------------------------------------                                                                                                                                           
 
   1 - SEL$1                                                                                                                                                                                            
   2 - SEL$1 / CUSTOMERS@SEL$1                                                                                                                                                                          
 
Column Projection Information (identified by operation id):                                                                                                                                             
-----------------------------------------------------------                                                                                                                                             
 
   1 - "COUNTRY_ID"[NUMBER,22], "CUST_STATE_PROVINCE"[VARCHAR2,40], COUNT(*)[22]                                                                                                                        
   2 - (rowset=256) "CUST_STATE_PROVINCE"[VARCHAR2,40], "COUNTRY_ID"[NUMBER,22]                                                                                                                         
 
Note                                                                                                                                                                                                    
-----                                                                                                                                                                                                   
   - dynamic statistics used: dynamic sampling (level=AUTO)

Here you can see that dynamic sampling was used with level Auto.
Once you execute this query, if you check the sql plan directives, you will see sql plan directives on this table with Reason of GROUP BY MISSESTIMATE.
I had to set optimizer_dynamic_sampling=11 to get dynamic statistics to kick in.

DIR_ID                                   OWNER OBJECT_NAME               COL_NAME             OBJECT_TYPE               TYPE                    STATE      INST          REASON
---------------------------------------- ----- ------------------------- -------------------- ------------------------- ----------------------- ---------- ------------- ------------------------------------
DC                          LU                         
--------------------------- ---------------------------
13371066972565290282                     SH    CUSTOMERS                 COUNTRY_ID           COLUMN                    DYNAMIC_SAMPLING        USABLE     NEW           GROUP BY CARDINALITY MISESTIMATE     
20-SEP-16 06:39:58                                     
 
13371066972565290282                     SH    CUSTOMERS                 CUST_STATE_PROVINCE  COLUMN                    DYNAMIC_SAMPLING        USABLE     NEW           GROUP BY CARDINALITY MISESTIMATE     
20-SEP-16 06:39:58                                     
 
13371066972565290282                     SH    CUSTOMERS                                      TABLE                     DYNAMIC_SAMPLING        USABLE     NEW           GROUP BY CARDINALITY MISESTIMATE     
20-SEP-16 06:39:58

The examples above can give you some working knowledge of when Dynamic Statistics comes into play with Oracle 12c.

12c Adaptive Query Optimization – Adaptive Plans – Adaptive Parallel Distribution Methods

In oracle 11gR2, oracle uses one of broadcast, hash, round robin, range or key distribution methods, to redistribute data among  parallel query servers. If the cardinality estimation at a given step happened to be incorrect (eg: the estimate was about a 100 rows and in reality there was 100,000 rows), oracle could end up broadcasting a lot of rows to all the receiving  parallel query servers. Another issue frequently encountered was that there could be skew in the popular values, resulting in hash distribution sending most of the values to one or a small subset of the receiving pq servers (Causing them to do most of the work).

To address such situations in 12cR1, as part of the new Adaptive Query Optimization techniques, oracle introduced the ability to switch the distribution method from hash to broadcast, if the actual number of rows was less than the threshold .  In 12.1 the threshold was 2X degree of parallelism .  This new functionality is called Adaptive Parallel Distribution Methods.

If you check the execution plan you will see that a “Statistics Collector” has been inserted before the send operation , and the send operation is called “PX Send Hybrid Hash”.

Let us look at an example of a simple hybrid hash distribution.

SQL_ID  d1qmm37g288qz, child number 0                                                                                                                                                                   
-------------------------------------                                                                                                                                                                   
select /*+ full(emp) full(dept) parallel(4) */                                                                                                                                                          
dept.deptno,dept.dname,emp.empno,emp.ename from dept,emp where                                                                                                                                          
dept.deptno = emp.deptno                                                                                                                                                                                
 
Plan hash value: 717794318                                                                                                                                                                              
 
------------------------------------------------------------------------------------------------------------------------------------------------                                                        
| Id  | Operation                  | Name     | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |    TQ  |IN-OUT| PQ Distrib |  OMem |  1Mem |  O/1/M   |                                                        
------------------------------------------------------------------------------------------------------------------------------------------------                                                        
|   0 | SELECT STATEMENT           |          |        |       |     4 (100)|          |        |      |            |       |       |          |                                                        
|   1 |  PX COORDINATOR            |          |        |       |            |          |        |      |            | 73728 | 73728 |          |                                                        
|   2 |   PX SEND QC (RANDOM)      | :TQ10002 |     14 |   770 |     4   (0)| 00:00:01 |  Q1,02 | P->S | QC (RAND)  |       |       |          |                                                        
|*  3 |    HASH JOIN BUFFERED      |          |     14 |   770 |     4   (0)| 00:00:01 |  Q1,02 | PCWP |            |  3105K|  2823K|     4/0/0|                                                        
|   4 |     PX RECEIVE             |          |      4 |    88 |     2   (0)| 00:00:01 |  Q1,02 | PCWP |            |       |       |          |                                                        
|   5 |      PX SEND HYBRID HASH   | :TQ10000 |      4 |    88 |     2   (0)| 00:00:01 |  Q1,00 | P->P | HYBRID HASH|       |       |          |                                                        
|   6 |       STATISTICS COLLECTOR |          |        |       |            |          |  Q1,00 | PCWC |            |       |       |          |                                                        
|   7 |        PX BLOCK ITERATOR   |          |      4 |    88 |     2   (0)| 00:00:01 |  Q1,00 | PCWC |            |       |       |          |                                                        
|*  8 |         TABLE ACCESS FULL  | DEPT     |      4 |    88 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |       |       |          |                                                        
|   9 |     PX RECEIVE             |          |     14 |   462 |     2   (0)| 00:00:01 |  Q1,02 | PCWP |            |       |       |          |                                                        
|  10 |      PX SEND HYBRID HASH   | :TQ10001 |     14 |   462 |     2   (0)| 00:00:01 |  Q1,01 | P->P | HYBRID HASH|       |       |          |                                                        
|  11 |       PX BLOCK ITERATOR    |          |     14 |   462 |     2   (0)| 00:00:01 |  Q1,01 | PCWC |            |       |       |          |                                                        
|* 12 |        TABLE ACCESS FULL   | EMP      |     14 |   462 |     2   (0)| 00:00:01 |  Q1,01 | PCWP |            |       |       |          |                                                        
------------------------------------------------------------------------------------------------------------------------------------------------

In the execution plan you can see that in steps 5 and 10 the operation is “PX SEND HYBRID HASH”. During execution, the statistics collector  in step 6 buffers the rows being retrieved from DEPT. If the number of rows are below a threshold, then all rows from DEPT are broadcast to each of the PQ servers that perform the join. If the number of rows exceed a threshold then the rows from DEPT are send using a HASH distribution to the PQ servers that perform the join.

Immediately after the query above is executed, from the same session, you can execute the script pqstat to see how the rows were distributed among the producers and consumers.

SQL> @pqstat
 
DFO_NUMBER      TQ_ID SERVER_TYPE                              PROCESS                    NUM_ROWS      BYTES      WAITS   TIMEOUTS AVG_LATENCY
---------- ---------- ---------------------------------------- ------------------------ ---------- ---------- ---------- ---------- -----------
         1          0 Producer                                 P004                              0         96          0          0           0
                                                               P005                             16        324          0          0           0
                                                               P006                              0         96          0          0           0
                                                               P007                              0         96          0          0           0
                      Consumer                                 P000                              4        153        125        119           0
                                                               P001                              4        153        127        119           0
                                                               P002                              4        153        127        119           0
                                                               P003                              4        153        125        119           0
                    2 Producer                                 P000                              0         24          0          0           0
                                                               P001                             14        375          1          0           0
                                                               P002                              0         24          0          0           0
                                                               P003                              0         24          0          0           0
                      Consumer                                 QC                               14        447          2          0           0

 

Now let us take a look at a variation of the hybrid hash distribution operation , which is denoted as “PX SEND HYBRID HASH (SKEW)”

SQL_ID  20fky17tasvn9, child number 0                                                                                                                                                                   
-------------------------------------                                                                                                                                                                   
SELECT /*+ parallel(4) full(e) full(d) gather_plan_statistics */                                                                                                                                        
department_name, sum(salary)   FROM   employees e, departments d                                                                                                                                        
WHERE  d.department_id=e.department_id   GROUP BY department_name                                                                                                                                       
 
Plan hash value: 2940813933                                                                                                                                                                             
 
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------    
| Id  | Operation                          | Name        | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |    TQ  |IN-OUT| PQ Distrib | A-Rows |   A-Time   | Buffers |  OMem |  1Mem |  O/1/M   |    
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------    
|   0 | SELECT STATEMENT                   |             |      1 |        |       |     5 (100)|          |        |      |            |     11 |00:00:00.04 |      24 |       |       |          |    
|   1 |  PX COORDINATOR                    |             |      1 |        |       |            |          |        |      |            |     11 |00:00:00.04 |      24 | 73728 | 73728 |          |    
|   2 |   PX SEND QC (RANDOM)              | :TQ10003    |      0 |     27 |   621 |     5  (20)| 00:00:01 |  Q1,03 | P->S | QC (RAND)  |      0 |00:00:00.01 |       0 |       |       |          |    
|   3 |    HASH GROUP BY                   |             |      4 |     27 |   621 |     5  (20)| 00:00:01 |  Q1,03 | PCWP |            |     11 |00:00:00.01 |       0 |  1071K|  1071K|     4/0/0|    
|   4 |     PX RECEIVE                     |             |      4 |     27 |   621 |     5  (20)| 00:00:01 |  Q1,03 | PCWP |            |     12 |00:00:00.01 |       0 |       |       |          |    
|   5 |      PX SEND HASH                  | :TQ10002    |      0 |     27 |   621 |     5  (20)| 00:00:01 |  Q1,02 | P->P | HASH       |      0 |00:00:00.01 |       0 |       |       |          |    
|   6 |       HASH GROUP BY                |             |      4 |     27 |   621 |     5  (20)| 00:00:01 |  Q1,02 | PCWP |            |     12 |00:00:00.03 |      18 |  1063K|  1063K|     4/0/0|    
|*  7 |        HASH JOIN                   |             |      4 |    106 |  2438 |     4   (0)| 00:00:01 |  Q1,02 | PCWP |            |    106 |00:00:00.03 |      18 |  1572K|  1572K|     4/0/0|    
|   8 |         PX RECEIVE                 |             |      4 |     27 |   432 |     2   (0)| 00:00:01 |  Q1,02 | PCWP |            |    108 |00:00:00.02 |       0 |       |       |          |    
|   9 |          PX SEND HYBRID HASH       | :TQ10000    |      0 |     27 |   432 |     2   (0)| 00:00:01 |  Q1,00 | P->P | HYBRID HASH|      0 |00:00:00.01 |       0 |       |       |          |    
|  10 |           STATISTICS COLLECTOR     |             |      4 |        |       |            |          |  Q1,00 | PCWC |            |     27 |00:00:00.01 |       9 |       |       |          |    
|  11 |            PX BLOCK ITERATOR       |             |      4 |     27 |   432 |     2   (0)| 00:00:01 |  Q1,00 | PCWC |            |     27 |00:00:00.01 |       9 |       |       |          |    
|* 12 |             TABLE ACCESS FULL      | DEPARTMENTS |      1 |     27 |   432 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |     27 |00:00:00.01 |       9 |       |       |          |    
|  13 |         PX RECEIVE                 |             |      4 |    107 |   749 |     2   (0)| 00:00:01 |  Q1,02 | PCWP |            |    107 |00:00:00.01 |      18 |       |       |          |    
|  14 |          PX SEND HYBRID HASH (SKEW)| :TQ10001    |      4 |    107 |   749 |     2   (0)| 00:00:01 |  Q1,01 | P->P | HYBRID HASH|    107 |00:00:00.01 |      18 |       |       |          |    
|  15 |           PX BLOCK ITERATOR        |             |      4 |    107 |   749 |     2   (0)| 00:00:01 |  Q1,01 | PCWC |            |    107 |00:00:00.01 |      18 |       |       |          |    
|* 16 |            TABLE ACCESS FULL       | EMPLOYEES   |      2 |    107 |   749 |     2   (0)| 00:00:01 |  Q1,01 | PCWP |            |    107 |00:00:00.01 |      18 |       |       |          |    
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

In the execution plans you can see the steps “PX SEND HYBRID HASH” (Step 9) and “PX SEND HYBRID HASH (SKEW)” (Step 14).

“PX SEND HYBRID HASH (SKEW)” is a technique used by 12c parallel query to avoid hash distribution of skewed popular key values. During optimization of the query, if histograms are present on the join expression, oracle finds and keeps the popular values in the cursor. At execution time , when the build table is being read, all values with matches in the popular values, are BROADCAST to all receiving pq serves  and non matching values are distributed by HASH. When the probe table is being read all values with matches in the popular values are send ROUND ROBIN/RANDOM to the receiving pq serves and the non matching values are distributed by HASH.

Please read Randolf Geist’s excellent Articles Linked below for indepth info on this topic.

Parallel Execution Skew – 12c Hybrid Hash Distribution with Skew Detection

12c Hybrid Hash Distribution with Skew Detection / Handling.

Oracle 12c on Docker – Part 2 – Usage Notes

In my previous post, Oracle 12c on Docker, you have seen how to install a docker image for the Oracle 12c database and how to run a container.

In this post, I will  review some useful Docker commands, that you can use.

The command docker images shows all the docker images that are present on your host.

docker112

The command docker exec -it <containername|containerid> bash can be used to login to a specific container.

docker212

The command docker stats can be used to see the cpu and memory usage statistics of the containers.

docker222

The command docker stop <containername|containerid> can be used to stop a container

docker142

You can start a second container on the same host using the same image. (Please refer to the previous post for the command we used to start the first container). When we are starting a second container, we are just changing the host ports that the listener and EM-Express ports are Exposed to.

The command to use is docker run -p 1522:1521 -p 5501:5500 oracle/database:12.1.0.2-ee

docker102

Then you can login using a oracle database client tool, to the database in this second container.

docker8-2

One important point to note is that any changes you make to the databases in the containers (As we have set it up) are lost once you shutdown the container. If you want to persist the changes made to the database, you can use the docker commit command to create a new image that has the changes in it.

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.

Webex configuration on Ubuntu 15.10 Wily WereWolf

I had to go through some additional package installations on Ubuntu 15.10 to get the webex client working from a FireFox browser.

Even though I was getting prompted to install the plugin and the plugin got installed, and i was getting to the Webex screen, I was unable to view the screens being presented via Webex. This is happening because there are a lot of libraries that the plugin needs, (In order to work properly)  that are missing after the base install of Ubuntu 15.10.

You can find the list of missing libraries by

  • Open a terminal with a command line prompt
  • cd .webex
  • cd 1524  (Or whatever your directory is named)
  • ldd *.so | grep -i ‘not found’

I had to perform the following steps to get all these libraries installed

  • Download java 32 bit for linux  from http://www.java.com/en/download/linux_manual.jsp
  • Downloaded and installed jre 32 bit into /u01/Rk/Apps/Java/jre32 (You can install it wherever you want to, just make sure you set LD_LIBRARY_PATH to the correct directory, in the next step)
  • Added following directories to the LD_LIBRARY_PATH setting in .bash_profile
    • /u01/Rk/Apps/Java/jre32/lib/i386:/u01/Rk/Apps/Java/jre32/lib/i386/server
  • Used apt to install the following packages
    • apt-get install libxmu6:i386
    • apt-get install libpangoxft-1.0-0:i386
    • apt-get install libpangox-1.0-0:i386
    • apt-get install libxtst6:i386
    • apt-get install -y lib32stdc++6
    • apt-get install -y libxi6:i386
    • apt-get install -y libxv1:i386
    • apt-get install -y libasound2:i386
    • apt-get install -y libgtk2.0-0:i386

After the above mentioned packages were installed, ldd did not report any missing libraries, and I was able to view and present using webex from FireFox.

Hope this helps others who have the same problem.