Exadata X4-2 Whats New – A Technical Review

 

On Dec 11 2013 Oracle corporation announced the general availability of, the 5th generation of the Oracle Exadata Database Machine X4. This new version of the database machine introduces new hardware and software to accelerate performance, increase capacity, and improve efficiency and quality of service for database deployments.

In this blog post i’ll review in detail the enhancements in the Oracle Exadata x4-2 database machine.

The 2 socket version of the Database machine, X4-2 gets new database servers and new storage servers. The 8 socket version of the database machine, still called the x3-8, gets the new storage servers but keeps the same database serves from the previous release. Hence the Lack of change in Name to the X4-8. The name stays as X3-8.

Improvements in the database servers (X4-2)

Cpu Improvements

The database servers in the x4-2 are the Sun X4-2  servers. They come with the 2 Twelve-Core Intel® Xeon® E5-2697 v2 Processors (Ivy Bridge) (2.7GHz), with turbo boost enabled by default. This cpu at times can clock upto 3.5Ghz.

Comparing this with the previous version x3-2, this is a 50% increase in number of cores per database server. The x3-2 had 16 cores per node and the x4-2 has 24 cores per node. In a x3-2 full rack there were 128 cores and in the x4-2 full rack there are 192 cores. That gives 64 more cores in the x4-2 full rack compared to the x3-2 full rack.

Memory Improvements

Each database server has 256Gb of Dram. This is optionally expandable to 512Gb per node.So you can have either 2Tb or 4Tb of Ram in a full rack of x4-2.

Storage Improvements

The database servers now have 4, 600Gb hard disks in them (Internal storage, used for O/S, Oracle Binaries, Logs etc). The x3-2 used to have 300Gb disks. The disk controller batteries are online replaceable

Network Improvements

The database servers have 2 X Infiniband 4 X QDR (40Gb/S) Ports (PCIe 3.0). Both ports are Active. The improvements in the x4-2 are that these are now PCIe 3.0, and that the ports are used active active. (The ports were used Active/Passive in the previous release)

Improvements in the storage servers (Exadata cells x4-2)

Cpu Improvements

Each x4-2 exadata cells come with 2, Six-Core Intel® Xeon® E5-2630 v2 Processors (2.6 GHz).

Memory Improvements

Each x4-2 cell, now has 96Gb of Ram. The x3-2 used to have 64 Gb of Ram.

Flash Cache Improvements

The Exadata x4-2 cells, now have the F80 PCIe Flash cards. There are 4, 800Gb F80 flash cards in each cell. So each cell has 3.2Tb of flash cache. In an Oracle Exadata database machine x4-2 full rack there is 44TB of flash cache (Used to be 22Tb in the x3-2). The Oracle Exadata database machine x4-2 full rack now provides, 2.66Million read iops , 1.96Million write iops from the flash cache. This is 70% more flash iops than the previous generation.

Storage Improvements

The Exadata cell x4-2 High Performance version, now uses 1.2Tb 10k Rpm High Performance SAS disks.

  • A full rack of High Performance disks gives 200TB of raw space, 90TB of usable space with normal mirroring and 60TB of usable space with High Mirroring

The Exadata cell x4-2 High Capacity version, now uses 4Tb 7.2k Rpm High Capacity SAS disks.

  • A full rack of High Capacity disks gives 672TB of raw space, 300TB of usable space with normal mirroring and 200TB of usable space with High Mirroring

The Disk controller batteries are online replacable.

Network Improvements

The Exadata storage servers have 2 X Infiniband 4 X QDR (40Gb/S) Ports (PCIe 3.0). Both ports are Active. The improvements in the x4-2 are that these are now PCIe 3.0, and that the ports are used active active. (The ports were used Active/Passive in the previous release)

 

Software Improvement Highlights

The new x4-2’s get a new version of the exadata storage server software version 11.2.3.3.0 (This version of the cell software can be installed on the v2,x2,x3 and x4 platforms).

Exadata Flash Cache Improvements

Data written to the flash cache is now compressed. This compression is done by the controller (Hence near Zero overhead). This data is automatically decompressed when it is read from the flash cache. Up to 2X more data fits in smart flash cache (If you get 2x compression of the data, you could fit upto 80TB of data on the flash cache), so flash hit rates will improve and performance will improve for large data sets. This feature can be turned on in both x3-2 and x4-2 storage servers. Customers need to license the Advanced Compression option to use this new feature.

Enhancements to the smart flash cache software, enables exadata software to understand database table and partition scans and automatically caches them when it makes sense (This will help eliminate the need to specify CELL FLASH CACHE KEEP).

Exadata network resource management

With a new version of the Infiniband switch firmware 2.1.3-4, Exadata network resource management now prioritizes messages through the entire infiniband fabric. Latency sensitive messages like redo log writes are prioritized over batch, reporting and backup messages.

Infiniband Active Active Ports

Double-ported infiniband PCIe-3.0 Cards used in the database servers and storage servers, implement active-active mode usage of the infiniband ports, providing a 80GigaBits Per Second network bandwidth (Used to be 40Gigbits ber second on the x3-2, since it was Active Passive bonding).

The Rdbms software and the clusterware software already had the ability to send packets via multipe interfaces. Enhancements have been done to the RDS kernel drivers, which now have the ability to sense if one of the ports is down, and route the network traffic through the surviving port. On the x4-2’s when active active Infiniband networking is setup you will not see the bondib0 interface, instead you will see a ib0 and ib1.

Miscellaneious Info

  • The power,cooling and airflow requirements remain similar to that of the x3-2.
  • The storage expansion racks have also been refreshed and provides the increased flash cache and increased disk space.
  • A single Database Machine configuration can have servers and storage from different generations V2, X2, X3, X4.
  • Databases and Clusters can span across multiple hardware generations.
  • The half and full x4-2 racks, do not ship the spine switch anymore. The storage expansion racks still ship with the spine switch.
  • A new One Command available through patch 17784784 has the new Exadata Deployment assistant that supports the x4-2’s.

Datasheets and White Papers

Datasheet: Oracle Exadata database machine x4-2

Datasheet: Oracle Exadata database machine x3-8

Datasheet: Oracle Exadata storage expansion Rack x4-2

Datasheet: Oracle Exadata storage server x4-2

A Technical Overview of the Oracle Database Machine and Exadata Storage Server (Updated for x4-2)

Exadata Smart Flash Cache (Updated For X4-2)

 

Managing Processes and Memory Consumption on Oracle Exadata database machines.

This post is equally applicable to Rac databases deployed, both on oracle database machines as well as traditional server/storage architectures, running RHEL5 or OEL5 (OEL6 has a new feature called transparent hugepages).

The Best practices for database consolidation on exadata database machine, White Paper outlines a lot of good guidelines. However the best practices seems to be getting occasionally overlooked, in some cases resulting in Node evictions/restarts in the cluster.

High amounts of memory and cpu consumption can result in some real bad things happening (Like for eg: Node evictions). So it is important to configure the operating system and the databases you deploy on the machine optimally, to use the available CPU and memory resources.

Let us first review what those available memory and cpu resources are on an Oracle Exadata Database Machine, on each database node.

[table “1” not found /]

Anytime a workload is deployed on a database server, that exceeds the, operating systems ability to efficiently use the above mentioned, available cpu and memory resources, bad things can happen. (I know i am over simplifying in the prior comment, but you get the idea).

Managing Memory Resources.

Setting up HugePages

First we need to make sure that HugePages on each node, is setup correctly, for the databases you have deployed on the node.
Tanel Poder has explained the reasons for setting up HugePages on Exadata systems.
When the database machines are installed, the HugePages is configured to accomodate the one database that is pre-installed. So if you change the SGA setting for that database or deploy more databases you should now adjust the HugePages setting accordingly. It goes without saying that if you have already configured the hugepages to accomodate all the databases you have, and then you removed some databases from the node, or resized the SGA’s to be smaller, you should again resize your hugepages to free up the excess hugepages.

Once all the database instances are configured and started up you can run the script, hugepages_settings.sh from Mos Note 401749.1, which calculates the number of hugepages you need to setup. I usually recommend adding 10% to the value that hugepages_settings.sh suggests.

With 11.2.0.2 and 11.2.0.3 you should also be setting USE_LARGE_PAGES, preferably to ONLY, following the instructions in Mos Note 1392497.1.

You can then follow the instructions in MOS note 361323.1, under the section “Kernel version 2.6”, to set the value. You need to restart the databases so the SGA gets allocated with the hugepages.

You should also follow instructions from MOS note 1546861.1, and set the value of vm.min_free_kbytes = 524288, in /etc/sysctl.conf, to avoid page allocation failure messages when kernel memory is depleted.

Managing Memory Usage

Ideally for critical implementations, your SGA+PGA+Individual server processes, memory allocations should not exceed 75% of the physical memory on the database node.

The Exadata consolidation white paper above suggests that

For critical Hardware Pools, we recommend an even more conservative approach by not exceeding 75% physical memory per database node.
OLTP applications:
SUM of databases (SGA_TARGET +PGA_AGGREGATE_TARGET) + 4 MB * (Maximum PROCESSES) < Physical Memory per Database Node
DW/BI applications:
SUM of databases (SGA_TARGET + 3 * PGA_AGGREGATE_TARGET) < Physical Memory per Database Node

You can monitor the pga usage a few different ways.

1) SELECT s.inst_id, s.sid, s.serial#, p.spid, s.username, s.program, p.pga_used_mem FROM gv$session s JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id WHERE s.type != ‘BACKGROUND’ and s.program not like ‘%(P%’ and p.pga_used_mem > <APPLICATION_MEMORY_THRESHOLD>
order by s.inst_id, s.sid, s.serial#;

The value you use for APPLICATION_MEMORY_THRESHOLD is dependent on your application and howmuch pga it needs to efficiently run your queries (Performing the sorts in memory using the space for PGA is usually faster than using the TEMP tablespace). However the white paper suggests using 1Gb for OLTP applications and 10Gb for DSS applications.

2) You can monitor the sysmetric “Total PGA Allocated” from dba_hist_sysmetric_summary, to see how much PGA is being allocated.

3) You can use the “PGA Memory Advisory” and “SGA Target Advisory” sections of the awr report, to guage, the impacts of increasing or decreasing the SGA and PGA Allocations.

4) You can monitor the memory usage at the o/s level using the top (Mem: free),free or vmstat (bo (page-out),bi (page-in)) commands.

Managing O/S Processes (And as a consequence CPU usage).

It is important to control the number of processes that are actively executing on each node. Each of these processes consume cpu and memory resources.

The following guidelines are important to follow.
– Use Application connection pooling to appropriately limit the number of sessions connected to the oracle database.

This is important for two reasons.
– Right-Size the resource consumption on each database node.
– Minimize the impact of connection storms.

– Configure the parallel query servers appropriately.
– For all the database’s deployed on a node the total setting for PARALLEL_MAX_SERVERS should be less than or equal to the following

X2-2 or X3-2, <= 240
X2-8 or X3-8, <= 1280

I often see implimentations where parallel_max_servers = 240 (or a high value) in each instance on the machine, which results in not so good consequences. I have occassionaly seen systems where there is sustained 100% cpu usage with load averages of > 300, resulting in node reboots.

– Use Instance Caging and Database Resource Manager to manage cpu allocation on the server.

It would be a good idea to always review the Best practices for database consolidation on exadata database machine, White Paper and follow the guidelines, to have a well performing and  stable database environment.

It would be a good idea to review the performance management screens from Enterprise Manager 12c every day to have a good understanding of the database and server workloads, and/or analyze the oswatcher data to understand the server workload profile.

More about Oracle 12c identity columns

Oracle 12c has introduced the ability to define a column in a database table as a “Identity” Column. This results in the database automatically using a sequence to generate the values that get inserted into this column. The Oracle-Base blog has an article on how to use this functionality and some performance comparisons between this and trigger based methods.

Below is a bit more detail regarding this functionality.

As stated in the Oracle-Base article, oracle auto-creates a sequence and uses it to generate the values. There seems to be a new data dictionary view, USER_TAB_IDENTITY_COLS, which shows you which columns in the table are defined as the Datatype IDENTITY.

The query below can be used to, identify the sequence name, that oracle generated, for a given column, defined as an IDENTITY datatype.

SQL> set long 999999999
SQL> select data_default from user_tab_columns where table_name = 'T1'    
  2  and column_name = 'COL1';

DATA_DEFAULT
--------------------------------------------------------------------------------
"SCOTT"."ISEQ$$_92434".nextval

SEQUENCE_NAME	     INCREMENT_BY CACHE_SIZE
-------------------- ------------ ----------
ISEQ$$_92434			1	  20

The sequence it uses, gets created with a default of 20 for the cache size. This will likely be unacceptable in Real Application Clusters environments. If you try to modify the cache size of the sequence after the creation you will get a ORA-32793 error “cannot alter a system-generated sequence”.

In such cases it would be wise to use the syntax below during table creation, and specify the CACHE_SIZE and other sequence parameters you would want to change.

SQL> create table t1 (col1 number generated as identity (start with 100 increment by 2 cache 1000), col2 varchar2(50));

Table created.

Once the table is re-created, you can check the cache size again to verify.

SQL> select sequence_name,cache_size from user_sequences;

SEQUENCE_NAME	     CACHE_SIZE
-------------------- ----------
ISEQ$$_92436		   1000

Plotting AWR database metrics using R

In a previous post i showed how you can connect from R to the oracle database using the R driver. In this post i will explain how we can run queries against the AWR history tables and gather data that can be plotted using ggplot.

When you install R on linux, like i outlined in the above post, you get an executable named Rscript. Rscript is a NonInteractive variant of the R command, so you can run a R batch file from the linux shell (Like running a bash shell script). I am using Rscript as the interpreter in my script (First line).

ggplot2 is a R library that can be used for plotting in R programs. There is native plotting capability in R and there is another library named lattice. ggplot2 is much more robust and is based on the grammar of graphics. You have to install ggplot2 (install.packages(“ggplot2”)) in R before you can use this.

#!/usr/bin/Rscript
library(ROracle)
library(ggplot2)

 

Process command line arguments. This script expects 3 commandline arguments. Copy each argument to a R variable.

args <- commandArgs(TRUE)
l_dbid <- as.double(args[1])
l_bsnap <- as.double(args[2])
l_esnap <- as.double(args[3])

Connect to Oracle

drv <- dbDriver(“Oracle”)
con <- dbConnect(drv,username=”system”,password=”manager”,dbname=”burl5vb1:1521/rk01″)

Popluate a data frame with the values you will need for bind variables in the query you will be submitting.

my.data = data.frame(dbid = l_dbid, bsnap =l_bsnap,esnap=l_esnap)

Prepare and Execute the query

res <- dbSendQuery(con,”select dhss.instance_number,dhss.snap_id,dhs.end_interval_time et,
round(sum(decode(dhss.metric_name,’User Transaction Per Sec’,dhss.average,0))) utps,
round(sum(decode(dhss.metric_name,’Average Active Sessions’,dhss.average,0))) aas,
round(sum(decode(dhss.metric_name,’Host CPU Utilization (%)’,dhss.average,0))) hcpu,
round(sum(decode(dhss.metric_name,’Buffer Cache Hit Ratio’,dhss.average,0))) bchr,
round(sum(decode(dhss.metric_name,’Logical Reads Per Sec’,dhss.average,0))) lr,
round(sum(decode(dhss.metric_name,’I/O Megabytes per Second’,dhss.average,0))) iombps,
round(sum(decode(dhss.metric_name,’I/O Requests per Second’,dhss.average,0))) iops,
round(sum(decode(dhss.metric_name,’Redo Generated Per Sec’,dhss.average,0))) rg,
round(sum(decode(dhss.metric_name,’Temp Space Used’,dhss.average,0))) ts,
round(sum(decode(dhss.metric_name,’Physical Write Total IO Requests Per Sec’,dhss.average,0))) pw,
round(sum(decode(dhss.metric_name,’Physical Read Total IO Requests Per Sec’,dhss.average,0))) pr
from dba_hist_sysmetric_summary dhss,dba_hist_snapshot dhs
where
dhss.dbid = :1
and dhss.snap_id between :2 and :3
and dhss.metric_name in (
‘User Transaction Per Sec’,
‘Average Active Sessions’,
‘Host CPU Utilization (%)’,
‘Buffer Cache Hit Ratio’,
‘Logical Reads Per Sec’,
‘I/O Megabytes per Second’,
‘I/O Requests per Second’,
‘Redo Generated Per Sec’,
‘Temp Space Used’,
‘Physical Write Total IO Requests Per Sec’,
‘Physical Read Total IO Requests Per Sec’)
and dhss.dbid = dhs.dbid
and dhs.instance_number=1
and dhss.snap_id = dhs.snap_id
group by dhss.instance_number,dhss.snap_id,dhs.end_interval_time
order by 1,2″,data=my.data
)

Fetch the rows, and disconnect from the db.

data <- fetch(res)
dbDisconnect(con)

Open a pdf file to save the graphs to.
Generate the graphs using ggplot.
print the graphs to the pdf file
Close the pdf file.

In the ggplot function call, ET and INSTANCE_NUMBER represent the End Snap Time and Instance Number columns output from the query, and AAS, UTPS, HCPU, PW and PR represent the AverageActiveSessions, UserTransactionPerSecond, HostCpu, PhysicalWrites and PhysicalReads columns from the query.

pdf(“plotstat.pdf”, onefile = TRUE)
p1<-ggplot(data,aes(strptime(ET,format=”%Y-%m-%d %H:%M:%S”),AAS,group=INSTANCE_NUMBER,color=INSTANCE_NUMBER))+geom_point()+geom_line()+ggtitle(“Average Active S
essions”)+labs(x=”Time of Day”,y=”Average Active Sessions”)
p2<-ggplot(data,aes(strptime(ET,format=”%Y-%m-%d %H:%M:%S”),UTPS,group=INSTANCE_NUMBER,color=INSTANCE_NUMBER))+geom_point()+geom_line()+ggtitle(“Transactions Pe
r Second”)+labs(x=”Time of Day”,y=”Transactions Per Second”)
p3<-ggplot(data,aes(strptime(ET,format=”%Y-%m-%d %H:%M:%S”),HCPU,group=INSTANCE_NUMBER,color=INSTANCE_NUMBER))+geom_point()+geom_line()+ggtitle(“CPU Usage”)+lab
s(x=”Time of Day”,y=”Cpu Usage”)
p4<-ggplot(data,aes(strptime(ET,format=”%Y-%m-%d %H:%M:%S”),PW,group=INSTANCE_NUMBER,color=INSTANCE_NUMBER))+geom_point()+geom_line()+ggtitle(“Physical Writes”)
+labs(x=”Time of Day”,y=”Phywical Writes”)
p5<-ggplot(data,aes(strptime(ET,format=”%Y-%m-%d %H:%M:%S”),PR,group=INSTANCE_NUMBER,color=INSTANCE_NUMBER))+geom_point()+geom_line()+ggtitle(“Physical Reads”)+
labs(x=”Time of Day”,y=”Physical Reads”)
print(p1)
print(p2)
print(p3)
print(p4)
print(p5)
dev.off()

You can run this script as follows from the Linux Command Line. The first argument is the dbid, the second argument is the begin snap id and the last argument is the end snap id.

./plotstat.R 220594996 5205 5217

You will then see a pdf document named plotstat.pdf in your directory that has 5 separate graphs in it.
Click on the link below to see a sample file. This is plotting awr data from a 4 node Oracle Rac Database.

plotstat

Click Here to download the whole script, plotstat.R

ggplot2 : Elegant Graphics for Data Analysis is a great book to learn about ggplot2.

Using the R Language with an Oracle Database.

R Programming Language Connectivity to Oracle.

R is an open source programming language and software environment for statistical computing and graphics. It is a fully functional programming language, widely used by statisticians to perform data analysis. It can also be a neat tool for Oracle DBA’s to graph and analyse database performance metrics. If you intend to embark on developing a sizable R+Oracle project, i’d encourage you to use Oracle Enterprise R and/or the Oracle Advanced Analytics.

Below are the steps on how to install and configure the R language on Ubuntu Linux with connectivity to Oracle.

These steps assume that you have an already installed and running Oracle 11gR2 database.

The high level steps are as follows

1) Install the R programming language environment
2) Download and install the oracle instant client
3) Download and install the following R packages
– DBI
– ROracle
4) Start using R with Oracle.

Install the R programming language environment

Refer to the installation instructions at www.r-project.org for your platform.
If you are installing this on Ubuntu Linux (As I have on Ubuntu 12.10), open the “Ubuntu Software Center” and install the following packages.
– R-base
– R-base-dev

Download and install the oracle instant Client

As your regular o/s user, download and install (Installation is nothing other than unzipping the downloaded file) the oracle instant client.
Download The instant client for your o/s platform from http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html.
You need to download
– Instant Client package – Basic
– Instant Client package – SDK

For the purpose of this installation, we are going to assume that the instant client has been installed into /u01/Rk/Apps/oracle/instantclient_11_2.

 Download and install the R packages

DBI

– Download DBI from http://cran.r-project.org/web/packages/DBI/index.html. (Download the package source)
– sudo su –
– cd <To the directory where DBI_0.2-5.tar.gz>

root# R CMD INSTALL DBI_0.2-5.tar.gz
* installing to library ‘/usr/local/lib/R/site-library’
* installing *source* package ‘DBI’ ...
** R
** inst
** preparing package for lazy loading
Creating a generic function for ‘summary’ from package ‘base’ in package ‘DBI’
** help
*** installing help indices
** building package indices
** installing vignettes
‘DBI.Rnw’
** testing if installed package can be loaded

* DONE (DBI)

ROracle

– Download the ROracle source from http://cran.r-project.org/web/packages/ROracle/index.html
– sudo su –
– cd

– Set the following environment variables

root# export OCI_LIB=/u01/Rk/Apps/oracle/instantclient_11_2
root# export LD_LIBRARY_PATH=/u01/Rk/Apps/oracle/instantclient_11_2:$LD_LIBRARY_PATH
root# R CMD INSTALL ROracle_1.1-5.tar.gz
* installing to library ‘/usr/local/lib/R/site-library’
* installing *source* package ‘ROracle’ ...
** package ‘ROracle’ successfully unpacked and MD5 sums checked
configure: creating ./config.status
config.status: creating src/Makevars
** libs
gcc -std=gnu99 -I/usr/share/R/include -DNDEBUG -I/u01/Rk/Apps/oracle/instantclient_11_2/sdk/include -fpic -O2 -pipe -g -c rodbi.c -o rodbi.o
gcc -std=gnu99 -I/usr/share/R/include -DNDEBUG -I/u01/Rk/Apps/oracle/instantclient_11_2/sdk/include -fpic -O2 -pipe -g -c rooci.c -o rooci.o
gcc -std=gnu99 -shared -o ROracle.so rodbi.o rooci.o -L/u01/Rk/Apps/oracle/instantclient_11_2 -lclntsh -L/usr/lib/R/lib -lR
installing to /usr/local/lib/R/site-library/ROracle/libs
** R
** inst
** preparing package for lazy loading
** help
*** installing help indices
** building package indices
** installing vignettes
** testing if installed package can be loaded

* DONE (ROracle)

Using The R Language with Oracle

Now you are ready to Run your first R program, Run a query against the database, and plot the output on a graph.

Invoke the R language command line by typing in the following

$ R

From the R command line use the following commands. (The formatting is a bit messed up, click on “view code” to see the actual commands)

> library(ROracle)
> drv <- dbDriver("Oracle")
> con <- dbConnect(drv,username="sh",password="sh",dbname="burl5vb1:1521/rk01")
> res <- dbSendQuery(con,"select time_id,sum(quantity_sold) from sales
+ where time_id > to_date('20-DEC-2001','DD-MON-RR')
+ group by time_id")
> data <- fetch(res)
> data
               TIME_ID SUM(QUANTITY_SOLD)
1  2001-12-20 23:00:00                473
2  2001-12-21 23:00:00                374
3  2001-12-22 23:00:00               1034
4  2001-12-23 23:00:00               1662
5  2001-12-24 23:00:00                470
6  2001-12-25 23:00:00                289
7  2001-12-26 23:00:00               1076
8  2001-12-27 23:00:00               1196
9  2001-12-28 23:00:00                232
10 2001-12-29 23:00:00                758
11 2001-12-30 23:00:00                786

> plot(data)

You will see a plot like the one below

Happy R scripting.

If you want to learn the R Language, i would recommend the book  The Art of R programming.

 

Oracle database machine x3-2

The Oracle database machine, gets a major makeover. As Larry Ellison phrased it in his Openworld 2012 Keynote, “Thought that the x2-2 was fast ? You Aint seen nothin Yet”.

If you go to http://www.oracle.com/technetwork/server-storage/engineered-systems/exadata/index.html, at the middle of the page, in the section titled “What’s New”, you can see a in depth technical discussion of the changes incorporated in the x3-2.

So without further Ado, let me explain what the changes are, in the x3-2 compared to the x2-2

Hardware Improvements

Faster CPU’s/More Cores.

– The Oracle Database Machine x3-2, uses the Intel Xeon E5-2690 Processors (2.9Ghz). 2 Sockets, 8 cores each, total 16 cores in each database node (The x2-2 had 12 cores per node). These are the Sandy bridge processors (x2-2 had the Intel Xeon westmere processors), which have a new micro architecture, and are extremely fast (Comparable in speed to the IBM Power7 cpu’s).

So now in the full Rack of x3-2, the database machine has 128 CPU Cores (The x2-2 had 96 Cores).

– The CPU’s on the exadata cells have been upgraded to use the Intel Xeon E5-2630L (2.0Ghz) Sandybridge processors. The Cpu’s are 6 cores each.

More Physical Memory (DRAM)

– The Oracle Database Machine x3-2 has 128Gb of DRAM memory per database server. This is expandable to 256Gb of Memory. So in the Full Rack you can have upto 2048Gb (2Tb) of physical memory.

– The physical memory on the x3-2 exadata cells, has been upgraded to have 64Gbytes of Ram.

More 10GigE networking ports

– The 4 Networking ports on the database server, mother board are now 1/10Gbe. They are autosensing,and are copper only. The remaining 2 Network ports are 10Gbe and can be connected via fiber.

More Flash Cache.

– The x3-2 exadata storage servers now use the Sun F40 Flash cards instead of the Sun F20 Flash cards used in the x2-2. Each Card is 400Gb. There are 4 PCI-E Flash cards in each cell. So you have 1600Gbytes of Flash cache in each cell. In a full rack x3-2, you get 22.4Tb of Flash cache (The x2-2 had 5Tb of Flash cache in a full rack).

So what does this increased amount of Flash mean in terms of performance ?

On an x3-2 full rack, you can get
– 1.5 Million datatase read iops from the flash cache.
– 1 Million database write iops from flash cache
– 100Gbytes/sec Flash Cache, scan throughput

New 1/8th Rack

A new configuration (In addition to the Full, Half & Quarter configurations) of a 1/8th Rack has been announced. So customers can now buy a configuration smaller than the quarter rack. It is really a 1/4th rack with half the cpu’s, half the flash cards and half the disks turned off. So the hardware price is lower and the software licensing costs are lower.

The other improvements include lower power consumption and improved cabling and airflow.

One notable change is that, the x3-2 now, does not have a KVM. This leaves 2U at the top of the Rack, where customers can deploy their in home switches, for network connectivity.

The number of disks, the type of disks, the disk capacities and speeds, in the exadata x3-2 cells,remain the same as it was in the x2-2 cells.

Software Improvements

Exadata Smart Flash Cache Write-Back

With the improved write speeds of the new PCI-E flash cards, the flash cache can now used as a write-back cache. This means that as soon as the data is written to flash cache, oracle database considers the write complete (ie it does not have to wait till the data is written to the physical magnetic disk). This helps improve the performance of applications that are currently bottlenecked on database writes.

On the x2-2, the random writes were written to the flash cache too, however it had to be written to disk (Or strictly speaking, to the disk controller cache) before the write was acknowledged by the database as completed. With the write-back cache functionality in x3-2 as soon as the write is persisted in the flash cache the database considers the write as complete. The writes to disk only get done when the ESS software detects that new blocks need to be read from disk to the flash cache and there is no free space in the flash cache. At such times, least frequently used data from the flash cache gets written to physical disk.

The smart flash cache algorithm makes sure that things like backups do not overwrite the entire cache.

The Full Rack x2-2 can do 1 million write iops to flash cache using this new functionality.

Reduced database brownout time during cell failure/removal.

In previous versions of the ESS software there could be upto 8 seconds of brown out time, when a cell failed, which has been now reduced to sub second.

Unbreakable Enterprise Kernel

– The database servers and Exadata storage servers on the x3-2 now use Oracle Unbreakable Enterprise Kernel 1.

The UEK1 was the operating system on the x2-8’s for a while now. With the x3-2’s we now use the UEK Kernel on the x3-2 database and storage server.

DBFS

– DBFS now supported on Solaris and Sparc Super Cluster.

The above list of hardware and software changes are just the highlights, not a complete list.

Consolidated database replay

Real Application Testing option has been enhanced to support “Consolidated Database Replay” functionality to help assess the impact of database and workload consolidation to a single database. Consolidated Database Replay allows concurrent replay of multiple captured production workloads from the same or different systems to a single database. Customers can use this functionality to validate the consolidation strategy recommended by Consolidation Planner and to accurately predict the capacity and sizing of their database infrastructure before production deployment. Here are more details regarding this announcement:

  • Consolidated Database Replay support starting from Oracle Database Release 11.2.0.2 and above. Multiple workload captures (including from  pre-11.2.0.2 database releases) can be replayed concurrently in database release 11.2.0.2 and above
  • MOS Doc ID 1453789.1 provides more details on the required patches, how to use this functionality and associated best practices

Sql to extract awr data for analysis

Awr captures a wealth of database performance statistics and metrics. Enterprise manager is usually the preferred tool to interpret and analyze this data. However lot of times i resort to using sql statements to graph and look for trends in the awr data. Below are some of the sql’s that i use (Tested only in 11gr2) to extract this information. Please keep in mind that you need the “Database tuning pack” license to access the dba_hist views.

The full script can be downloaded here.

Awr Snapshot info by DbId, by Day

select d.dbid,d.db_name,to_char(s.begin_interval_time,'DD-MON-RR') begintime,min(s.snap_id) minsnap,max(s.snap_id)  maxsnap
              from dba_hist_snapshot s,dba_hist_database_instance d where s.instance_number = 1
              and s.instance_number = d.instance_number and s.dbid = d.dbid
              group by d.dbid,d.db_name,to_char(s.begin_interval_time,'DD-MON-RR') order by 1
/

	       DBID DB_NA BEGINTIME		 MINSNAP   MAXSNAP
------------------- ----- -------------------- --------- ---------
	  220594996 QAD   16-NOV-11		    5205      5217
	  220594996 QAD   17-NOV-11		    5218      5220
	 2085202933 RK01  15-MAY-12		       3	 4

Database version and platform

select distinct version,platform_name from dba_hist_database_instance where dbid=&dbid
/

VERSION           PLATFORM_NAME
----------------- ---------------------------
11.2.0.2.0        Linux x86 64-bit

Cpu’s/Sockets/Cores/Load Average

select * from (
                  select  stat_name,value
                  from  dba_hist_osstat
                  where dbid = &dbid
                  and instance_number = 1
                  and snap_id = &esnap
                  )
                  pivot (sum(value) for stat_name in ('NUM_CPUS','NUM_CPU_SOCKETS','NUM_CPU_CORES','PHYSICAL_MEMORY_BYTES','LOAD'))
/

'NUM_CPUS' 'NUM_CPU_SOCKETS' 'NUM_CPU_CORES' 'PHYSICAL_MEMORY_BYTES'     'LOAD'
---------- ----------------- --------------- ----------------------- ----------
        24                 2              12              1.0122E+11 .209960938

O/S Cpu Usage

select
to_char(begintime,'DD-MON-YY HH24:MI:SS') begintime,
to_char(endtime,'DD-MON-YY HH24:MI:SS') endtime,
inst,
snapid,
round((utdiff/(utdiff+itdiff+stdiff+iowtdiff+ntdiff))*100)  utpct,
round((ntdiff/(utdiff+itdiff+stdiff+iowtdiff+ntdiff))*100)  ntpct,
round((stdiff/(utdiff+itdiff+stdiff+iowtdiff+ntdiff))*100)  stpct,
round((iowtdiff/(utdiff+itdiff+stdiff+iowtdiff+ntdiff))*100)  iowtpct,
(100-
(
 round((utdiff/(utdiff+itdiff+stdiff+iowtdiff+ntdiff))*100)+
 round((ntdiff/(utdiff+itdiff+stdiff+iowtdiff+ntdiff))*100)+
 round((stdiff/(utdiff+itdiff+stdiff+iowtdiff+ntdiff))*100)+
 round((iowtdiff/(utdiff+itdiff+stdiff+iowtdiff+ntdiff))*100)
)) itpct
from
(
select begintime,endtime,(extract(Minute from endtime-begintime)*60+extract(Second from endtime-begintime)) secs,
snapid,inst,
ut-(nvl(lag(ut) over (partition by inst order by inst,snapid),0)) utdiff,
bt-(nvl(lag(bt) over (partition by inst order by inst,snapid),0)) btdiff,
it-(nvl(lag(it) over (partition by inst order by inst,snapid),0)) itdiff,
st-(nvl(lag(st) over (partition by inst order by inst,snapid),0)) stdiff,
iowt-(nvl(lag(iowt) over (partition by inst order by inst,snapid),0)) iowtdiff,
nt-(nvl(lag(nt) over (partition by inst order by inst,snapid),0)) ntdiff,
vin-(nvl(lag(vin) over (partition by inst order by inst,snapid),0)) vindiff,
vout-(nvl(lag(vout) over (partition by inst order by inst,snapid),0)) voutdiff
from
(
select sn.begin_interval_time begintime,
     sn.end_interval_time EndTime,oss.snap_id SnapId,oss.instance_number Inst,
     sum(decode(oss.stat_name,'USER_TIME',value,0)) ut,
     sum(decode(oss.stat_name,'BUSY_TIME',value,0)) bt,
     sum(decode(oss.stat_name,'IDLE_TIME',value,0)) it,
     sum(decode(oss.stat_name,'SYS_TIME',value,0)) st,
     sum(decode(oss.stat_name,'IOWAIT_TIME',value,0)) iowt,
     sum(decode(oss.stat_name,'NICE_TIME',value,0)) nt,
     sum(decode(oss.stat_name,'VM_IN_BYTES',value,0)) vin,
     sum(decode(oss.stat_name,'VM_OUT_BYTES',value,0)) vout
from dba_hist_osstat oss,dba_hist_snapshot sn
where oss.dbid=&dbid
and   oss.dbid = sn.dbid
and   oss.instance_number =  sn.instance_number
and   oss.snap_id = sn.snap_id
and   oss.snap_id between &bsnap - 1 and &esnap
and   oss.stat_name in (
'USER_TIME',
'BUSY_TIME',
'IDLE_TIME',
'SYS_TIME',
'IOWAIT_TIME',
'NICE_TIME',
'VM_IN_BYTES',
'VM_OUT_BYTES'
)
group by sn.begin_interval_time,sn.end_interval_time,oss.snap_id,oss.instance_number
order by oss.instance_number,oss.snap_id
)
)
where snapid between &bsnap and &esnap
order by inst,snapid
/

BEGINTIME            ENDTIME               in     SNAPID  UTPCT  NTPCT  STPCT IOWTPCT  ITPCT
-------------------- -------------------- --- ---------- ------ ------ ------ ------- ------
16-NOV-11 21:00:12   16-NOV-11 21:20:16     1       5209   1.00    .00    .00     .00  99.00
16-NOV-11 21:20:16   16-NOV-11 21:40:12     1       5210   4.00    .00   1.00     .00  95.00
16-NOV-11 21:40:12   16-NOV-11 22:00:03     1       5211  31.00    .00   2.00     .00  67.00
16-NOV-11 22:00:03   16-NOV-11 22:20:05     1       5212  58.00    .00   2.00     .00  40.00

Elapsed Time/DB Time/Concurrent Active Users

select
to_char(begintime,'DD-MON-YY HH24:MI:SS') begintime,
to_char(endtime,'DD-MON-YY HH24:MI:SS') endtime,
inst,
snapid,
round(dbtdiff/(1000000*60),2) dbt,
round(secs/60) mins,
round(dbtdiff/(1000000*60*round(secs/60))) concactive
from
(
select begintime,endtime,(extract(Minute from endtime-begintime)*60+extract(Second from endtime-begintime)) secs,
snapid,inst,
dbt-(nvl(lag(dbt) over (partition by inst order by inst,snapid),0)) dbtdiff
from
(
select sn.begin_interval_time begintime,
     sn.end_interval_time EndTime,tm.snap_id SnapId,tm.instance_number Inst,
     sum(decode(tm.stat_name,'DB time',value,0)) dbt
from dba_hist_sys_time_model tm,dba_hist_snapshot sn
where tm.dbid=&dbid
and   tm.dbid = sn.dbid
and   tm.instance_number =  sn.instance_number
and   tm.snap_id = sn.snap_id
and   tm.snap_id between &bsnap - 1 and &esnap
and   tm.stat_name in (
'DB time'
)
group by sn.begin_interval_time,sn.end_interval_time,tm.snap_id,tm.instance_number
order by tm.instance_number,tm.snap_id
)
)
where snapid between &bsnap and &esnap
order by inst,snapid
/

BEGINTIME            ENDTIME               in     SNAPID       DBT    MINS CONCACTIVE
-------------------- -------------------- --- ---------- --------- ------- ----------
16-NOV-11 21:00:12   16-NOV-11 21:20:16     1       5209         1      20          0
16-NOV-11 21:20:16   16-NOV-11 21:40:12     1       5210       319      20         16
16-NOV-11 21:40:12   16-NOV-11 22:00:03     1       5211       657      20         33
16-NOV-11 22:00:03   16-NOV-11 22:20:05     1       5212       972      20         49
16-NOV-11 22:20:05   16-NOV-11 22:40:06     1       5213       457      20         23
16-NOV-11 22:40:06   16-NOV-11 23:00:08     1       5214       736      20         37
16-NOV-11 23:00:08   16-NOV-11 23:20:25     1       5215         3      20          0

Top 5 Foreground Waits

The percentages i get here, do not seem to match up exactly, with the percentages you get, when you run awrrpt.sql, to generate the corresponding awr report. However it always seems to be within + or – 3% of the awrrpt.sql value. I think that the variation is because of the way that the awrrpt.sql might be rounding values, and probably how it accouts for Idle events.

 

with se as (
     select sn.begin_interval_time begintime,
        sn.end_interval_time EndTime,se.snap_id SnapId,se.instance_number Inst,
        se.event_name stat,se.time_waited_micro_fg value,
        nvl(lag(se.time_waited_micro_fg) over(partition by se.instance_number,se.event_name
        order by se.instance_number,se.snap_id,se.event_name),0) prevval,
        se.time_waited_micro_fg-
        nvl(lag(se.time_waited_micro_fg) over(partition by se.instance_number,se.event_name
        order by se.instance_number,se.snap_id,se.event_name),0) valuediff
     from dba_hist_system_event se,dba_hist_snapshot sn
     where se.dbid=&dbid
     and   se.dbid = sn.dbid
     and   se.instance_number =  sn.instance_number
     and   se.snap_id = sn.snap_id
     and   se.snap_id between &bsnap-1 and &esnap
     and   se.wait_class != 'Idle'
     order by se.snap_id,se.instance_number,se.event_name
     ) ,
     sdbcpu as (
         select sn.begin_interval_time begintime,sn.end_interval_time EndTime,
         stm.snap_id snapid,stm.instance_number inst,stm.stat_name stat
         ,stm.value value
         ,nvl(lag(stm.value) over(partition by stm.instance_number order by stm.instance_number,stm.snap_id),0) prevval
         ,stm.value-
         nvl(lag(stm.value) over(partition by stm.instance_number order by stm.instance_number,stm.snap_id),0) valuediff
         from dba_hist_sys_time_model stm,dba_hist_snapshot sn
         where
         stm.stat_name = ('DB CPU')
         and stm.dbid = &dbid
         and stm.snap_id between  &bsnap-1 and &esnap
         and stm.dbid = sn.dbid
         and stm.instance_number = sn.instance_number
         and stm.snap_id = sn.snap_id
         order by stm.snap_id,stm.instance_number
     ) ,
     sunion as (
         select begintime,endtime,snapid,inst,stat,valuediff from se
         union all
         select begintime,endtime,snapid,inst,stat,valuediff from sdbcpu
         order by 3,4
     ),
     spct as (
     select begintime,endtime,snapid,inst,stat,valuediff,
     round(ratio_to_report(valuediff) over (partition by snapid,inst),4) as pct
     from sunion
     order by 3,4 asc,7  desc
     )
     select * from (
     select to_char(begintime,'DD-MON-RR HH24:MI:SS') begintime
     ,to_char(endtime,'DD-MON-RR HH24:MI:SS') endtime,snapid,inst,stat,valuediff,round(pct*100,2) pct,
     row_number() over (partition by snapid,inst order by snapid,inst asc,pct desc) as rnum
     from spct
     )
     where rnum < 6 and snapid between &bsnap and &esnap
/

BEGINTIME            ENDTIME                  SNAPID  in STAT                               VALUEDIFF    PCT
-------------------- -------------------- ---------- --- ------------------------------ ------------- ------
16-NOV-11 21:00:12   16-NOV-11 21:20:16         5209   1 DB CPU                              28856557  83.24
16-NOV-11 21:00:12   16-NOV-11 21:20:16         5209   1 name-service call wait               2073440   5.98
16-NOV-11 21:00:12   16-NOV-11 21:20:16         5209   1 control file sequential read          843201   2.43
16-NOV-11 21:00:12   16-NOV-11 21:20:16         5209   1 enq: PS - contention                  634127   1.83
16-NOV-11 21:00:12   16-NOV-11 21:20:16         5209   1 undo segment extension                423219   1.22
16-NOV-11 21:00:12   16-NOV-11 21:20:16         5209   2 DB CPU                              34408715  89.73
16-NOV-11 21:00:12   16-NOV-11 21:20:16         5209   2 enq: PS - contention                  852207   2.22
16-NOV-11 21:00:12   16-NOV-11 21:20:16         5209   2 reliable message                      351905    .92
16-NOV-11 21:00:12   16-NOV-11 21:20:16         5209   2 control file sequential read          323355    .84
16-NOV-11 21:00:12   16-NOV-11 21:20:16         5209   2 row cache lock                        286882    .75

Physical and Logical I/O

select to_char(begintime,'DD-MON-RR HH24:MI') begintime,to_char(endtime,'DD-MON-RR HH24:MI') endtime
,(extract(Minute from endtime-begintime)*60+extract(Second from endtime-begintime)) secs,
snapid,inst,
prd-nvl(lag(prd) over (partition by inst order by inst,snapid),0) prddiff,
pwrt-nvl(lag(pwrt) over (partition by inst order by inst,snapid),0) pwrtdiff,
iordreq-nvl(lag(iordreq) over (partition by inst order by inst,snapid),0) iorddiff,
iowrtreq-nvl(lag(iowrtreq) over (partition by inst order by inst,snapid),0) iowrtdiff,
prmbr-nvl(lag(prmbr) over (partition by inst order by inst,snapid),0) prmbrdiff,
cgets-nvl(lag(cgets) over (partition by inst order by inst,snapid),0) cgetsdiff,
dbgets-nvl(lag(dbgets) over (partition by inst order by inst,snapid),0) dbgetsdiff
from
(
select sn.begin_interval_time begintime,
     sn.end_interval_time EndTime,ss.snap_id SnapId,ss.instance_number Inst,
     sum(decode(ss.stat_name,'physical read total bytes',value,0)) prd,
     sum(decode(ss.stat_name,'physical write total bytes',value,0)) pwrt,
     sum(decode(ss.stat_name,'physical read total IO requests',value,0)) iordreq,
     sum(decode(ss.stat_name,'physical write total IO requests',value,0)) iowrtreq,
     sum(decode(ss.stat_name,'physical read total multi block requests',value,0)) prmbr,
     sum(decode(ss.stat_name,'consistent gets',value,0)) cgets,
     sum(decode(ss.stat_name,'db block gets',value,0)) dbgets
from dba_hist_sysstat ss,dba_hist_snapshot sn
where ss.dbid=&dbid
and   ss.dbid = sn.dbid
and   ss.instance_number =  sn.instance_number
and   ss.snap_id = sn.snap_id
and   ss.snap_id between &bsnap-1 and &esnap
and   ss.stat_name in (
'physical read total bytes',
'physical write total bytes',
'physical read total IO requests',
'physical write total IO requests',
'physical read total multi block requests',
'consistent gets',
'db block gets'
)
group by sn.begin_interval_time,sn.end_interval_time,ss.snap_id,ss.instance_number
order by ss.instance_number,ss.snap_id
)
where snapid between &bsnap and &esnap
order by 4,5
/

BEGINTIME            ENDTIME                    SECS     SNAPID  in          PRDDIFF         PWRTDIFF         IORDDIFF        IOWRTDIFF        PRMBRDIFF        CGETSDIFF       DBGETSDIFF
-------------------- -------------------- ---------- ---------- --- ---------------- ---------------- ---------------- ---------------- ---------------- ---------------- ----------------
16-NOV-11 20:40      16-NOV-11 21:00        1210.766       5208   1     309967486976     544388304896         17660147         36335142          1139579       4468788730       2328671039
16-NOV-11 20:40      16-NOV-11 21:00        1210.782       5208   2     165472269312     510302864896         16065810         34894618           144948       4168326257       2064355182
16-NOV-11 20:42      16-NOV-11 21:00        1079.081       5208   3       3122675712        153791488            56249             6006            10713          1307859            60272
16-NOV-11 20:43      16-NOV-11 21:00        1028.123       5208   4       1318486016         59018752            26009             5436             7229          1199275            56027

Top 10 sql statements By Elapsed Time

select * from (
select ss.snap_id snapid,ss.instance_number inst,ss.sql_id  sqlid
       ,round(sum(ss.elapsed_time_delta)) elapsed
       ,nvl(round(sum(ss.executions_delta)),1) execs
       ,round(sum(ss.buffer_gets_delta)) gets
       ,round(sum(ss.rows_processed_delta)) rowsp
       ,round(sum(ss.disk_reads_delta)) reads
       ,dense_rank() over(partition by snap_id,instance_number order by sum(ss.elapsed_time_delta) desc) sql_rank
from
dba_hist_sqlstat ss
where
ss.dbid = &dbid  and
ss.snap_id between &bsnap and &esnap
group by ss.snap_id,ss.instance_number,ss.sql_id
)
where sql_rank < 11 and snapid between &bsnap and &esnap
/

   SNAPID  in SQLID                                ELAPSED      EXECS           GETS          ROWSP          READS   SQL_RANK
---------- --- ------------------------------ ------------- ---------- -------------- -------------- -------------- ----------
      5209   1 1zr2ujm5sa5gc                        4547685          1         224434              1              0          1
      5209   1 3g2ugdp1af2h8                        4478848         13         221134             13              0          2
      5209   1 5k5v1ah25fb2c                        3965629         39         360232             39              0          3
      5209   1 0r1zf55mxaujd                        2959144          1         237437              1              0          4
      5209   1 8vwv6hx92ymmm                        2633353      51062         360669          51062              0          5
      5209   1 2w1s3wu1zw63a                        1958993          2         153110              4              0          6
      5209   1 f1y8kbhh6v9sv                        1709282          1                                                       7
      5209   1 6pw8uk8k0dv0q                        1699402          1                                                       8

Moving awr data from 10gR2 to 11gR2 using awrextr.sql and awrload.sql

I have been wondering for a while whether we could export awr data from a 10gr2 database (awrextr.sql) and load it into an 11gr2 database (awrload.sql). I got to test this yesterday and it works fine. I was able to do the awrload.sql with No errors. Afterwords i even ran the 11gr2 awrrpti.sql script to generate awr reports using this data. It produces awr reports, but as one would expect, the portions where the data is not available in 10gR2 (eg: wait event histograms), the report just says “no data exists for this section of the report”.

How to influence the execution plan without modifying the sql from the application

It is likely that all of us have encountered the following situation. Your company runs a packaged application, you get some poorly performing sql, root cause happens to be the bad execution plan. You could battle statistics and see if you can get the optimizer to pick a better plan. One of the options, is to put this sql through the sql tuning advisor and see if it comes up with a sql profile that improves the execution plan, and if it does, to accept the profile.

Some of these performance issues could be critical, and require urgent resolution. In such urgent situations, you might catch yourself thinking, “I wish i could hint this query to get it to pick a better execution plan”. However, this being a packaged application, your hands are tied.

This year at oracle openworld 2011, Maria Colgan and Mohamed Zait, presented a session  “Oracle Database Optimizer : Tips for preventing suboptimal execution plans”. In there, towards the end, was a gem, which illustrated how to get a sql to pick a different execution plan (preferably generated by a modified version of the sql, hinted to pick a better plan), without actually modifying the application sql. This technique uses sql plan management.

This blog post is just reproducing the exact same method, with the exact same example they used, with a little bit more illustration of the execution plan, hopefully to benefit folks, who have not used this procedure before, and did not attend the openworld session.

The original sql

SQL> connect sh/sh
Connected.

SQL> variable sup_id number;
SQL> exec :sup_id := 1;

select p.prod_name,sum(s.amount_sold) amt
from sales s,products p
where s.prod_id = p.prod_id
and p.supplier_id = :sup_id
group by p.prod_name
/ 

Envoy External 6X CD-ROM			    645586.12
Model SM26273 Black Ink Cartridge		    617732.28
Model K8822S Cordless Phone Battery		    582640.54
Bounce						    244595.65
Smash up Boxing 				    260436.75
Comic Book Heroes				     101214.6

The original Plan

Note the full table scan on the products table

SQL> select * from table (dbms_xplan.display_cursor(null,null,'TYPICAL'))
/
  2
SQL_ID	fmjmws8askq3j, child number 0
-------------------------------------
select p.prod_name,sum(s.amount_sold) amt from sales s,products p where
s.prod_id = p.prod_id and p.supplier_id = :sup_id group by p.prod_name

Plan hash value: 504757596

----------------------------------------------------------------------------------------------------
| Id  | Operation		| Name	   | Rows  | Bytes | Cost (%CPU)| Time	   | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	|	   |	   |	   |   587 (100)|	   |	   |	   |
|   1 |  HASH GROUP BY		|	   |	71 |  3550 |   587  (12)| 00:00:08 |	   |	   |
|*  2 |   HASH JOIN		|	   |	72 |  3600 |   586  (12)| 00:00:08 |	   |	   |
|   3 |    VIEW 		| VW_GBC_5 |	72 |  1224 |   583  (12)| 00:00:07 |	   |	   |
|   4 |     HASH GROUP BY	|	   |	72 |   648 |   583  (12)| 00:00:07 |	   |	   |
|   5 |      PARTITION RANGE ALL|	   |   918K|  8075K|   533   (3)| 00:00:07 |	 1 |	28 |
|   6 |       TABLE ACCESS FULL | SALES    |   918K|  8075K|   533   (3)| 00:00:07 |	 1 |	28 |
|*  7 |    TABLE ACCESS FULL	| PRODUCTS |	72 |  2376 |	 3   (0)| 00:00:01 |	   |	   |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ITEM_1"="P"."PROD_ID")
   7 - filter("P"."SUPPLIER_ID"=:SUP_ID)

26 rows selected.

Create the plan baseline for this sql

Note that we are using the sql_id we got from the output of dbms_xplan in the previous step.

SQL> variable cnt number;
SQL> execute :cnt := dbms_spm.load_plans_from_cursor_cache(sql_id=>'fmjmws8askq3j');

PL/SQL procedure successfully completed.

Verify that the plan baseline was created by checking dba_sql_plan_baselines (Notice that the plan is enabled by default when you created it)

SQL> select sql_handle,sql_text,plan_name,enabled
from
dba_sql_plan_baselines
where
sql_text like '%select p.prod_name%'  2    3    4    5
  6  /

SQL_10ed3803a09c8fe1	       select p.prod_name,sum(s.amount_sold) amt     SQL_PLAN_11v9s0fh9t3z1c47b6be0 YES
			       from sales s,products p
			       where s.prod_i

Disable the original plan baseline from being used. (Since we’d be substituting this with a new hinted plan)

Note that we are using the plan_name and sql_handle that we got from the previous query.

SQL> exec :cnt := dbms_spm.alter_sql_plan_baseline(sql_handle =>'SQL_10ed3803a09c8fe1',-
					      plan_name => 'SQL_PLAN_11v9s0fh9t3z1c47b6be0',-
					      attribute_name=>'enabled',-
					      attribute_value=>'NO');
/

Check dba_sql_plan_baselines to ensure the baseline is now disabled.

  1  select sql_handle,sql_text,plan_name,enabled
  2  from
  3  dba_sql_plan_baselines
  4  where
  5* sql_text like '%select p.prod_name%'
SQL> /
SQL_10ed3803a09c8fe1	       select p.prod_name,sum(s.amount_sold) amt     SQL_PLAN_11v9s0fh9t3z1c47b6be0 NO
			       from sales s,products p
			       where s.prod_i

Rerun the sql with an Index Hint

Note that we are using a index hint (/*+ index(p) */ , to illustrate the point that, the execution plan has changed, and now it picks an index as opposed to a full table scan from the original query. In this specific case, there is no index on the supplier_id on the table products, so it picks the primary key on the table and does a full index scan. But you can see how, hinting your queries, with the proper index names can help your query.

SQL> select /*+ index(p) */ p.prod_name,sum(s.amount_sold) amt
from sales s,products p
where s.prod_id = p.prod_id
and p.supplier_id = :sup_id
group by p.prod_name
/   2    3    4    5    6
Envoy External 6X CD-ROM			    645586.12
Model SM26273 Black Ink Cartridge		    617732.28
Model K8822S Cordless Phone Battery		    582640.54
Bounce						    244595.65
Smash up Boxing 				    260436.75
Comic Book Heroes				     101214.6
Finding Fido					     78881.08

Check the new execution plan

Notice that the new plan uses the index products_pk on the table products.

SQL> select * from table (dbms_xplan.display_cursor(null,null,'TYPICAL'))
/  2
SQL_ID	gtdunv6qmpqqw, child number 0
-------------------------------------
select /*+ index(p) */ p.prod_name,sum(s.amount_sold) amt from sales
s,products p where s.prod_id = p.prod_id and p.supplier_id = :sup_id
group by p.prod_name

Plan hash value: 4089802669

-------------------------------------------------------------------------------------------------------------
| Id  | Operation		      | Name	    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	      | 	    |	    |	    |	587 (100)|	    |	    |	    |
|   1 |  HASH GROUP BY		      | 	    |	 71 |  3550 |	587  (12)| 00:00:08 |	    |	    |
|*  2 |   HASH JOIN		      | 	    |	 72 |  3600 |	586  (12)| 00:00:08 |	    |	    |
|   3 |    VIEW 		      | VW_GBC_5    |	 72 |  1224 |	583  (12)| 00:00:07 |	    |	    |
|   4 |     HASH GROUP BY	      | 	    |	 72 |	648 |	583  (12)| 00:00:07 |	    |	    |
|   5 |      PARTITION RANGE ALL      | 	    |	918K|  8075K|	533   (3)| 00:00:07 |	  1 |	 28 |
|   6 |       TABLE ACCESS FULL       | SALES	    |	918K|  8075K|	533   (3)| 00:00:07 |	  1 |	 28 |
|*  7 |    TABLE ACCESS BY INDEX ROWID| PRODUCTS    |	 72 |  2376 |	  3   (0)| 00:00:01 |	    |	    |
|   8 |     INDEX FULL SCAN	      | PRODUCTS_PK |	 72 |	    |	  1   (0)| 00:00:01 |	    |	    |
-------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ITEM_1"="P"."PROD_ID")
   7 - filter("P"."SUPPLIER_ID"=:SUP_ID)

28 rows selected.

Switch the execution plan for the original, unhinted sql

From the dbms_xplan output above we know the new sql_id (gtdunv6qmpqqw) and the new plan_hash_value (4089802669) (For the plan that is using an index).

We can then use dbms_spm to associate this new execution plan, to the sql_handle we created, for the original un-hinted sql, in the sql plan baseline. We use the new sql_id and plan_hash_value, from our hinted plan and we associate it to the sql_handle from the original query.

Note that the sql_handle we are using here, is the sql_handle for the original unhinted sql (We get this value from the step “Verify that the plan base line was created”) .

SQL> execute :cnt := dbms_spm.load_plans_from_cursor_cache(sql_id => 'gtdunv6qmpqqw',-
						      plan_hash_value => 4089802669,-
						      sql_handle=>'SQL_10ed3803a09c8fe1');
/

PL/SQL procedure successfully completed.

Check that a new plan has been added to the baseline

Note that the new plan is enabled by default.

SQL> select sql_handle,sql_text,plan_name,enabled
from
dba_sql_plan_baselines
where
sql_text like '%select p.prod_name%'
/  

SQL_10ed3803a09c8fe1	       select p.prod_name,sum(s.amount_sold) amt     SQL_PLAN_11v9s0fh9t3z1c47b6be0 NO
			       from sales s,products p
			       where s.prod_i

SQL_10ed3803a09c8fe1	       select p.prod_name,sum(s.amount_sold) amt     SQL_PLAN_11v9s0fh9t3z1d20e849e YES
			       from sales s,products p
			       where s.prod_i

Re-Run the original sql

select p.prod_name,sum(s.amount_sold) amt
from sales s,products p
where s.prod_id = p.prod_id
and p.supplier_id = :sup_id
group by p.prod_name
/ 

Envoy External 6X CD-ROM			    645586.12
Model SM26273 Black Ink Cartridge		    617732.28
Model K8822S Cordless Phone Battery		    582640.54
Bounce						    244595.65
Smash up Boxing 				    260436.75
Comic Book Heroes				     101214.6

Check the new execution plan

You can see that the original unhinted sql statement is now using the plan hash value of the hinted query and hence is using the primary key index on the products table (As opposed to the full table scan on the original table).

SQL> select * from table (dbms_xplan.display_cursor(null,null,'TYPICAL'));
SQL_ID	fmjmws8askq3j, child number 1
-------------------------------------
select p.prod_name,sum(s.amount_sold) amt from sales s,products p where
s.prod_id = p.prod_id and p.supplier_id = :sup_id group by p.prod_name

Plan hash value: 4089802669

-------------------------------------------------------------------------------------------------------------
| Id  | Operation		      | Name	    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	      | 	    |	    |	    |	587 (100)|	    |	    |	    |
|   1 |  HASH GROUP BY		      | 	    |	 71 |  3550 |	587  (12)| 00:00:08 |	    |	    |
|*  2 |   HASH JOIN		      | 	    |	 72 |  3600 |	586  (12)| 00:00:08 |	    |	    |
|   3 |    VIEW 		      | VW_GBC_5    |	 72 |  1224 |	583  (12)| 00:00:07 |	    |	    |
|   4 |     HASH GROUP BY	      | 	    |	 72 |	648 |	583  (12)| 00:00:07 |	    |	    |
|   5 |      PARTITION RANGE ALL      | 	    |	918K|  8075K|	533   (3)| 00:00:07 |	  1 |	 28 |
|   6 |       TABLE ACCESS FULL       | SALES	    |	918K|  8075K|	533   (3)| 00:00:07 |	  1 |	 28 |
|*  7 |    TABLE ACCESS BY INDEX ROWID| PRODUCTS    |	 72 |  2376 |	  3   (0)| 00:00:01 |	    |	    |
|   8 |     INDEX FULL SCAN	      | PRODUCTS_PK |	 72 |	    |	  1   (0)| 00:00:01 |	    |	    |
-------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ITEM_1"="P"."PROD_ID")
   7 - filter("P"."SUPPLIER_ID"=:SUP_ID)

Note
-----
   - SQL plan baseline SQL_PLAN_11v9s0fh9t3z1d20e849e used for this statement