Category Archives: Administration

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

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.

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 &gt; 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"&gt;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 &gt; 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"&gt;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     = '&amp;sql_id'
  AND child_number = &amp;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     = '&amp;sql_id'
  AND child_number = &amp;sql_child
  AND other_xml   is not null
  ) map
WHERE sp.id             = map.op(+)
AND sp.sql_Id           = '&amp;sql_id'
AND sp.child_number     = &amp;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

AWR – Profiling Database I/O

Oracle Awr (Automatic Workload Repository) statistics, captures and stores fine grained information about file reads and writes (aka i/o), that the database performed, during the course of execution of, application generated database workloads. When analyzing the read and write patterns of the database, it helps a lot to understand what type of activity is generating the reads and writes. With this stored information we can get an indepth understanding of the distribution of random and sequential reads and writes.

I use this information for getting a better understanding of the I/O profile, for my Exadata sizing exercises.

This information can be used to understand clearly how much of the i/o is from Temp activity, Datafile reads and writes, Archivelog writes, log writes, and whether these are small or large reads and writes.

To the best of my understanding the small reads and writes are those < 128k and the large reads and writes are those > 128k.

This information is contained mainly in two awr Views.

Dba_Hist_Iostat_FileType
Dba_Hist_Iostat_Function

Dba_Hist_Iostat_FileType

This view displays the historical i/o statistics by file type. The main filetypes are the following

Archive Log
Archive Log Backup
Control File
Data File
Data File Backup
Data File Copy
Data File Incremental Backup
Data Pump Dump File
Flashback Log
Log File
Other
Temp File

Dba_Hist_Iostat_Function

This view displays the historical i/o statistics by i/o function. The main i/o functions are the following

Recovery
Buffer Cache Reads
Others
RMAN
Streams AQ
Smart Scan
Data Pump
XDB
Direct Writes
DBWR
LGWR
Direct Reads
Archive Manager
ARCH

From everything i have seen sofar, these reads and writes can be directly co-related to the “Physical read total IO requests” and “Physical write total IO requests” system level statistics.

I have written a script that displays information from the above mentioned views and gives a detailed breakdown of i/o gendrated from different aspects of the database activities.
In order to fit in the computer screen real estate, i have actually limited the columns the script displays (So it displays only the file types i am frequently interested in). Please feel free to take the script and modify it to add columns that you want to display.

The full version of the script  awrioftallpct-pub.sql can be found here.

The script accepts the following inputs
– A begin snap id for a snapid range you want to report for
– A End snap id for a snapid range you want to report for
– A Dbid for the database
– The snap interval in seconds (If you have a 30 minute interval input 1800 seconds)

A description of all the column names in the output, broken down by section, is provided in the header section of the script.

There are 6 sections to this script

1) Total Reads + Writes
2) Total Reads
3) Total Writes
4) Read write breakdown for datafiles
5) Data File – Direct Path v/s Buffered Read Write breakdown
6) Read write breakdown for tempfiles

1) Total Reads + Writes

This section displays the number of reads+writes by filetype, and a percentage of reads+writes for each file type, as a percentage of total reads+writes. The last column displays the total reads+writes for all file types. The column DTDP shows the i/o that bypasses flash cache by default and goes directly to spinning disk on Exadata (Temp+Archivelogs+Flashback Logs).

io1-rw

 

Click on the image to see a larger version

2) Total Reads

This section displays the number of reads by filetype, and a percentage of reads for each file type, as a percentage of total reads. The last column displays the total reads for all file types.

io1-r

Click on the image to see a larger version

3) Total Writes

This section displays the number of writes by filetype, and a percentage of writes for each file type, as a percentage of total writes. The last column displays the total writes for all file types.

io1-w

Click on the image to see a larger version

4) Read write breakdown for datafiles

This section displays the I/O information only pertaining to datafile i/o. It displays the small and large reads and writes and a percentage they constitute of the total reads+writes to datafiles, and a percentage they constitute of the total reads or writes to datafiles. It also displays the total small and large reads and writes and a percentage they constitute of the total reads+writes to datafiles.

io1-dfrw

Click on the image to see a larger version

5) Data File – Direct Path v/s Buffered Read Write breakdown

This section provides a breakdown of I/O by function (As opposed to i/o by filetype in the previous sections). The output shows columns that display the direct path small and large reads and writes, buffered small reads and writes, smart scan small and large reads and other small and large reads and writes.

io3-bf

Click on the image to see a larger version

6) Read write breakdown for tempfiles

This section displays the I/O information only pertaining to tempfile i/o. It displays the small and large reads and writes and a percentage they constitute of the total reads+writes to tempfiles, and a percentage they constitute of the total reads or writes to tempfiles. It also displays the total small and large reads and writes and a percentage they constitute of the total reads+writes to tempfiles.

io3-tf

Click on the image to see a larger version

The full version of the script  awrioftallpct-pub.sql can be found here.

Oracle Active DataGuard – Considerations for the Wide area Network

Oracle customers use Oracle Active Dataguard to create and maintain one or many standby databases that protect their mission critical primary databases from disaster. Typically, in such deployments, the primary databases and standby databases are in geographically separate locations connected via a WAN (Wide Area Network). Log Transport Services, transfers Large volumes of redo logs from the primary location to the standby, using Sql*Net.

We have to ensure that all the components from the source to target are setup correctly to ensure that the data transfer can be done with the best throughput possible. If sufficient network bandwidth is not available with reasonable latencies, then we will start seeing the log transfer and apply,lagging on the standby site (Which is oracle speak for, your primary and standby database is now out of sync from a data perspective).

One key point to keep in mind is that, lower the network round trip time (aka latency), higher your data transfer throughput. Higher the network round trip time (aka latency), lower your data transfer throughput. So it is very important to maintain low round trip times on your Wide area network.

To understand network data transfer throughput, It is important to understand the the concepts of Tcp Window Size and Bandwidth Delay Product (aka BDP).

Tcp Window size is the amount of bytes that can be transmitted without receiving an acknowledgement from the other side. Once Tcp Window size amount of bytes are send, the sender stops sending any more bytes and waits for an acknowledgement from the receiver.

Bandwidth delay product is calculated as the product of the network bandwidth and network round trip time. bdp=network bandwidth*round trip time. This is the amount of data that left the sender before the first acknowledgement was received by the sender. If the senders output bandwidth is stable, and the bandwidth is fully used, then the BDP calculates the number of packets in transit. If we set the Tcp Window size equal to the bandwidth delay product, then in theory we should be able to fully utilize the available bandwidth.

Setup the network

We have to start by setting up the networking components to support the desired/stated bandwidth. So if you have a WAN that is a 10GigE network, all the NIC’s (Network interface cards), ports, switches in the configuration should be configured to support 10GigE full Duplex settings. After setup, run the configuration display utilities and ensure that all these component levels the transfer speeds are set to be 10 GigE. Customers often run into trouble when Auto Negotiation causes some NIC’s to set the transfer speeds to 1GigE because of configuration mismatches.

Use tools like Iperf to test the transfer speeds that your network is capable of achieving.

One important aspect to keep in mind is that it is probable that the WAN is shared by other traffic (e-mail, data replication, san replication). This has two important implications that we should consider.

  • If there is a lot of bandwidth consumption by some of this miscellaneous traffic, round trip times could be degraded periodically on the network.
  • We should be careful in our calculations that we do not completely consume the entire bandwidth for redo transport. (This could impact other processes)
    • So it is important to figure out (Working with the network admins) what the bandwidth entitlements are for redo transport and base our calculations on those numbers.

Caclulate our BDP

Use the following formula to calculate our Bandwidth delay product (BDP)

(bandwidth/8)*rount trip time in seconds.

The network bandwidth is expressed in bits per second, so we divide by 8 to convert to bytes.
Round Trip Time is usually in milli seconds, so we divide by 1000 to convert to seconds.

So for example, if we have a 10Gbit network bandwidth and a 40ms round trip time

BDP=(10000000000/8)*(40/1000) = 50,000,000 bytes.

Setup Sql*Net Parameters

The current recommendations for Dataguard Redo transport are as follows.

Set the SDU size to 65535

  • We can set SDU on a per connection basis using the SDU parameter in the local naming configuration file (TNSNAMES.ORA) and the listener configuration file (LISTENER.ORA)
  • We can set the SDU for all Oracle Net connections with the profile parameter DEFAULT_SDU_SIZE in the SQLNET.ORA file.

Set TCP.NODELAY to YES

To preempt delays in buffer flushing in the TCP protocol stack, disable the TCP Nagle algorithm by setting TCP.NODELAY to YES in the SQLNET.ORA file on both the primary and standby systems.

Setup RECV_BUF_SIZE and SEND_BUF_SIZE

The current recommendation is to set the SEND_BUF_SIZE and RECV_BUF_SIZE parameters (Which are the send and receive socket buffer sizes for SQL*Net) to 3 Times the BDP.

As per the above example we would set them to 50,000,000*3 = 150,000,000

Setup Operating system Kernel Parameters

If you are using the Linux operating system make sure that the values for the following kernel parameters are setup to be higher than the values set for RECV_BUF_SIZE and SEND_BUF_SIZE.

net.core.rmem_max
net.core.wmem_max

Once we have configured the network, operating system and the sql*net, and we have redo transfer, we can perform further network monitoring to see how the network bandwidth is being utilized, and make appropriate adjustments.

Links to helpful Documents

Iperf

How to calcluate Tcp throughput for long distance links (blog)

Oracle Net Performance Tuning (Mos)

Setting Send and Receive Buffer Sizes (Mos)

Tuning Sql*Net peformance (Oracle Docs)

Configuring Oracle Dataguard (Oracle Docs)

Best Practices for Sync Data Transport (White Paper)

Script to compare tkprof output files

I often use the oracle 10046 event tracing mechanism to capture sql’s from a session to identify why certain transactions are running slower in different env’s or at different points in time. Oracle does have a mechanism where you can save the trace information in database tables. One can use the INSERT parameter in tkprof to store the trace information into a database table. Once in the table you can write sql’s that compare multiple runs or multiple statements.

I wrote a python program that compares two different tkprof output files. The files are compared, and the following aspects of each of the sqlid’s in the tkprof output file,s are printed side by side. The output is sorted by the Difference in Elapsed Time, in Descending order.

  • Sql text
  • Plan Hash Value
  • Total Elapsed time
  • Total Logical Reads
  • Total Rows processed
  • Difference in Elapsed Time
  • Difference in Number of Rows processed
  • Difference in Logical reads.

Other columns can be added to this, if you desire.
I use this script output as a quick way to see which sql’s are running slower and are probably candidates for further analysis/tuning.

The sqlid’s from the file provided as the first argument to the script (referred to as the left file) are compared to the same sqlid’s in the file provided as the second argument to the script (referred to as the right file). The following columns are displayed.

sqlid                           sqlid being compared
text                             First 20 chars of the sql text
lplan                           Plan hash value from the left file
rplan                          Plan hash value from the right file
lela                             Total Elapsed time from the left file
rela                            Total Elapsed time from the right file
llreads                       Total Logical reads (query+current) from the left file
rlreads                      Total Logical reads (query+current) from the right file
lrows                         Total rows processed from the left file
rrows                        Total rows processed from the right file
eladiff                        Lela – Rela
rowsdiff                    Lrows – Rrows
lreadsdiff                  Llreads – rlreads

Here is a sample syntax for running the script. (You need the python pandas package to be installed for this to execute successfully)

python ./difftk.py /u01/tkprofout/Newplans.prf /u01/tkprofout/Stage.prf

Here is a sample output

difftk

 

Click on the image to view a larger version.

The full script is below

?View Code PYTHON
#Python script to list differences between sql executions in two tkprof output files
#useful if comparing tkprof from prod and dev for example
#Author : rajeev.ramdas
 
import sys
import os
import pandas as pd
from pandas import DataFrame
 
# Define a class to hold the counters for each sqlid
class sqliddet:
     def init(self):
            sqlid=''
            text=''
            plan_hash=''
            tcount=0
            tcpu=0
            tela=0
            tdisk=0
            tquery=0
            tcurr=0
            trows=0
 
# Define 2 empty dictionaries to store info about each input file
leftsqliddict={}
rightsqliddict={}
 
# Process each file and add one row per sqlid to the dictionary
# We want to add the row to the dictionary only after the SQLID row and the total row has been read
# So the firstsqlid flag is used to make sure that we do not insert before total is read for the first row.
 
def processfile(p_file,p_sqliddict):
 
    myfile=open(p_file,"r")
    line=myfile.readline()
    firstsqlid=True
    linespastsqlid=99
    while line:
        linespastsqlid+=1
        line=myfile.readline()
        if line.startswith('SQL ID'):
            linespastsqlid=0
            if firstsqlid==True:
                firstsqlid=False
            else:
                p_sqliddict[currsqlid.sqlid]=[currsqlid.plan_hash,currsqlid.tcount,currsqlid.tcpu,currsqlid.tela,currsqlid.tdisk,currsqlid.tquery
                            ,currsqlid.tcurr,currsqlid.trows,currsqlid.text]
            currsqlid=sqliddet()
            currsqlid.sqlid=line.split()[2]
            currsqlid.plan_hash=line.split()[5]
        if linespastsqlid==2:
            currsqlid.text=line[0:20]
        if line.startswith('total'):
            a,currsqlid.tcount,currsqlid.tcpu,currsqlid.tela,currsqlid.tdisk,currsqlid.tquery,currsqlid.tcurr,currsqlid.trows=line.split()
        if line.startswith('OVERALL'):
            p_sqliddict[currsqlid.sqlid]=[currsqlid.plan_hash,currsqlid.tcount,currsqlid.tcpu,currsqlid.tela,currsqlid.tdisk,currsqlid.tquery
                       ,currsqlid.tcurr,currsqlid.trows,currsqlid.text]
        continue
    myfile.close()
 
# Main portion of script
if len(sys.argv) != 3:
   print('Syntax : python ./difftk.py tkprof1.out tkprof2.out')
   sys.exit()
 
if not os.path.isfile(sys.argv[1]) or not os.path.isfile(sys.argv[2]):
   print ("File Does not Exist")
   sys.exit()
 
processfile(sys.argv[1],leftsqliddict)
processfile(sys.argv[2],rightsqliddict)
 
t_difftk_lst=[]
 
# Match the sqlid's from the file on the left to the file on the right
# Gather up the statistics form both sides, insert into a list
# Transfer the list to a pandas dataframe, add some computed columns
 
for sqlid,stats in leftsqliddict.items():
    l_totlogical=int(stats[5])+int(stats[6])
    if sqlid in rightsqliddict:
       t_difftk_lst.append([sqlid,stats[8].rstrip(),stats[0],rightsqliddict[sqlid][0],float(stats[3])
                            ,float(rightsqliddict[sqlid][3]),float(l_totlogical),float(rightsqliddict[sqlid][5])+float(rightsqliddict[sqlid][6])
                            ,float(stats[7]),float(rightsqliddict[sqlid][7])
                           ])
    else:
       t_difftk_lst.append([sqlid,stats[8].rstrip(),stats[0],0,float(stats[3]),0
                            ,float(l_totlogical),0,float(stats[7]),0
                           ])
 
difftk_df=DataFrame(t_difftk_lst,columns=['sqlid','sqltext','lplan','rplan','lela','rela','llreads','rlreads','lrows','rrows'])
difftk_df['eladiff']=difftk_df['lela']-difftk_df['rela']
difftk_df['rowsdiff']=difftk_df['lrows']-difftk_df['rrows']
difftk_df['lreadsdiff']=difftk_df['llreads']-difftk_df['rlreads']
 
pd.set_option('display.width',1000)
print (difftk_df.sort(columns='eladiff',ascending=False))