Installing the pg_partman extension

pg_partman is an extension that simplifies the process of partition management in postgres.

Below are the steps that I followed to install the pg_partman extension with postgres 12.8.

Change your current working directory to the directory where you unzipped the postgres 12.8 source code.

cd <SomeStaticPath>/postgresql-12.8/contrib

git clone https://github.com/pgpartman/pg_partman.git

cd pg_partman

make PG_CONFIG=<pghome>/bin/pg_config NO_BGW=1

make install

Then Edit your postgresql.conf file and add pg_partman_bgw to the parameter shared_preload_libraries

Now restart your postgres instance

At this time you are ready to create the extension from postgres and use it.

Use psql to login to your database

CREATE SCHEMA partman;

CREATE EXTENSION pg_partman SCHEMA partman;

\dx (To list the extensions and the version installed in your database)

Loading IMDB data into postgresql

IMDB (Internet Movie Database) makes the movie dataset available for free download at https://datasets.imdbws.com/. The documentation for this dataset can be found at https://www.imdb.com/interfaces/.

In this blog post, I show you how to load this data into a PostgreSql database. The steps are executed from an Ubuntu Linux workstation. I assume that you already have a postgresql database with about 50Gb of free space to upload this data into and you know the connection information.

We’ll use the s32imdbpy.py script that can be downloaded from github .

From the ubuntu workstation, install the following packages

sudo apt install python3-pip
sudo apt-get install libpq-dev

Now install the following python modules

pip3 install Psycopg2
pip3 install imdbpy

Create a new directory, to store the downloaded datafiles and download the datafiles from https://datasets.imdbws.com into this directory

mkdir dat
cd dat
wget https://datasets.imdbws.com/name.basics.tsv.gz
wget https://datasets.imdbws.com/title.akas.tsv.gz
wget https://datasets.imdbws.com/title.basics.tsv.gz
wget https://datasets.imdbws.com/title.crew.tsv.gz
wget https://datasets.imdbws.com/title.episode.tsv.gz
wget https://datasets.imdbws.com/title.principals.tsv.gz
wget https://datasets.imdbws.com/title.ratings.tsv.gz

Login to your postgresql database and create a new schema to hold the imdb tables (This step is optional. If you do not create this schema, then the tables and the corresponding data gets loaded into the public schema).

create schema imdb;

Before you run the script, lets edit the script and make one change, which will enable the script to load the data into the newly created imdb schema. This change will be made in line 183 in the file.

Change
engine = sqlalchemy.create_engine(db_uri, encoding='utf-8', echo=False)
To
engine = sqlalchemy.create_engine(db_uri, encoding='utf-8', echo=False,connect_args={'options': '-csearch_path={}'.format('imdb')})

Then Execute the script as shown below

python3 s32imdbpy.py /home/ubuntu/dat postgresql://username:password@dbhostname/dbname

Where /home/ubuntu/dat is the directory where the imdb files are downloaded into.

This will take some time to load (Close to an hour on a reasonably sized ubuntu workstation) and will consume about 15Gb of space in your postgresql database.

Substitution variables in psql scripts

As postgresql users and administrators, we tend to create lot of scripts, and run them routinely, for common queries we need to execute against the database. It is likely that in some of those scripts, you would want to parameterize values used in filter conditions. Below is an example of how this can be done.

psql is the terminal based front end tool to interact with postgresql. You can run scripts stored in files in the file system, using the \i directive in plsql. You can use the \set command to set variables in psql. If you want to prompt for the value to be entered by the user, you can use the \prompt directive.

\prompt 'Enter Table Name : ' tabname


select last_vacuum,last_autovacuum,last_analyze ,last_autoanalyze,n_live_tup,n_dead_tup
from pg_stat_user_tables
where relname = :'tabname';

Now if you run this from psql you will be first prompted for the table name and then it will display the results for the table name you entered.

postgres=# \i pgstats.sql
Enter Table Name : nflstats

 last_vacuum | last_autovacuum |         last_analyze         |       last_autoanalyze        | n_live_tup | n_dead_tup 
-------------+-----------------+------------------------------+-------------------------------+------------+------------
             |                 | 2020-06-08 18:07:36.29538+00 | 2020-06-08 15:37:48.738104+00 |     270418 |          0

Listing Errors from Cloudwatch logs using Aws Cli

The following commands can be used to list the Error messages from cloudwatch logs, produced from DMS (Database Migration service).

First list the log groups

aws logs describe-log-groups

Next list the log streams in your log group

aws logs describe-log-streams --log-group-name <YourLogGroupNameHere>

Next list the error messages. Within the DMS log, the Errors are indicated with a pattern “E:” within the error string, so that is the pattern we search for.

aws logs filter-log-events --log-group-name <YourLogGroupNameHere> --log-stream-names <YourLogStreamNameHere> --filter-pattern "[message = \"*E:*\"]" --query 'events[*].message'

If you are searching in cloudwatch logs produced from other sevices, you should replace E: with the pattern that flags Error messages for that service.

Webex configuration on Ubuntu 15.10 Wily WereWolf

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

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

You can find the list of missing libraries by

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

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

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

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

Hope this helps others who have the same problem.

 

Oracle Exadata Statistics in AWR report – Part 2 (Outliers)

This blog post is a continuation of the previous blog post titled , Oracle Exadata Statistics in AWR report – Part 1 (Basics). In this post we continue on to describe the performance details displayed in the section “Exadata Outlier Summary”.

Outlier Summary Cell Level

awex3-1

This section displays cells that have performance outliers. The Awr Views DBA_HIST_CELL_DISK_SUMMARY, and DBA_HIST_CELL_GLOBAL_SUMMARY contains samples for each cell, disk and flash card.
The individual sample values, the number of samples, the average, the square of the value are all stored. Using this data the mean and the standard deviation are calculated and the range is defined as the average + or – standard deviation. Cells that have values that are above the mean + standard deviation are displayed.

This section will help us identify cells that have performance metrics, which are outside of the standard operating norms of that cell.

Outlier Summary – Disk Level

awex3-2

This section displays Disks that have performance outliers. The Awr Views DBA_HIST_CELL_DISK_SUMMARY contains this info.The individual sample values, the number of samples, the average, the square of the value are all stored. Using this data the mean and the standard deviation are calculated and the range is defined as the average + or – standard deviation. Disks that have values that are above the mean + standard deviation are displayed.

This section will help us identify Disks (Flash or Hard disk) that have performance metrics, which are outside of the standard operating norms of that Disk.

Exadata OS IO Statistics – Outlier Cells

awex4-1

This section displays cells that have IO statistics that are outliers. Per Cells averages, Per Disk Mean, Standard Deviation, Range’s of the IOPS and IO MBPS information is displayed. Averages exceeding the maximum stated capacity of the disk or cell are shown in Dark red.

This section helps identify whether there are cells or disks that exceed their stated capacities.

Exadata OS IO Statistics – Outlier Disks

awex4-2

This section displays disks (Flash and Hard disk) that have IO statistics that are outliers. Per Disk Mean, Standard Deviation, Range’s of the IOPS, IO MBPS and Disk utilization percentage information is displayed. Averages exceeding the Normal Ranges are shown in Dark red.

This section helps identify whether there are disks that are outside of the standard operating norms of that disk.

Exadata OS IO Latency – Outlier Cells

awex4-3
This section displays cells (Flash and Hard disk) that have IO latencies that are outliers.
Aggregated Across all cells, the Mean, Standard Deviation, Range’s of Average Serice times and Average Wait Times are displayed.
If there are cells Averages that exceed the Normal Range, they are displayed as outliers.

This section helps us identify whether there are cells that have I/O latencies that are outside of the standard operating norms for cells in this system.

Exadata OS IO Latency – Outlier Disks

awex5-1
This section displays disks (Flash and Hard disk) that have IO latencies that are outliers.
Aggregated Across all cells, the Mean, Standard Deviation, Range’s of Average Serice times and Average Wait Times are displayed.
If there are disks whose Averages that exceed the Normal Range of the cells, they are displayed as outliers.

This section helps us identify whether there are disks that have I/O latencies that are outside of the standard operating norms for disks in this system.
Exadata OS CPU Statistics – Outlier Cells

awex5-2
This section displays cells that have Cpu utilization that are outliers.
Aggregated Across all cells, the Mean, Standard Deviation, Range’s of Cpu utilization is displayed.
If there are cells whose Average Cpu utilization that exceed the Normal Cpu utilization Range of the cells, they are displayed as outliers.

Oracle Exadata Statistics in AWR report – Part 1 (Basic Info)

Starting with Exadata storage server 12.1.2.1.0 , used in combination with Oracle Database release 12.1.0.2, there are new sections which have been added to the Oracle AWR (Automatic Worload repository) report, that displays statistics at the Exadata storage level.

This is a really valuable enhancement, which helps with drilling down from database level statistics to cell level statistics, to identify and analyze the workload profile.
You can click on the URL’s in the section “Exadata Configuration and Statistics” to access this part of the report.

There are a few AWR history tables that store this information.

DBA_HIST_CELL_CONFIG
DBA_HIST_CELL_CONFIG_DETAIL
DBA_HIST_CELL_DB
DBA_HIST_CELL_DISKTYPE
DBA_HIST_CELL_DISK_NAME
DBA_HIST_CELL_DISK_SUMMARY
DBA_HIST_CELL_GLOBAL
DBA_HIST_CELL_GLOBAL_SUMMARY
DBA_HIST_CELL_IOREASON
DBA_HIST_CELL_IOREASON_NAME
DBA_HIST_CELL_METRIC_DESC
DBA_HIST_CELL_NAME
DBA_HIST_CELL_OPEN_ALERTS

The description of these views can be found in the Exadata Storage Server Users Guide.

The section starts off by showing the cell configuration information. Then it displays the Kernel  and the Cell Image version’s.

awex1-1

This information comes from the awr view DBA_HIST_CELL_DISK_SUMMARY.

The next section titled “Exadata Storage Information” storage information shows the number of disks and flash cards in each cell and the entire rack.

awex1-2
The first row of the output shows the amount of flash cache in each cell, The size of the smart flash log, Number of hard disks in a cell, Number of flash cards in each cell, and the number of Grid Disks in each cell.
The second row shows the above columns aggregated for all cells in the rack.

The next section titled “Exadata Griddisks” shows the grid disk names, Number of Grid disks in each cell, the Grid Disk size and The type of Drive

awex1-3
The next section titled “Exadata Cell Disks” shows the Disk type, Size of the cell disk, Number of disks .

awex2-1
The next section “ASM disksgroups” shows the diskgroups used by this database.

awex2-2
It shows the diskgroup name,Total size of the diskgroup,Used space, Number of disks in the diskgroup and the redundancy type.

This is followed by a section “Exadata Server Health Report”, which has 3 sub sections Exadata Alerts Summary,Exadata Alerts Detail,Exadata Non-Online Disks which displays information regarding alerts on the cells and any offline disks.
The remaining sections of Exadata performance statistics in the AWR report, display a great deal of Exadata cell performance numbers.

Before we venture much into those sections, it is important to understand some cell level concepts and how they are captured in Awr.

At the cell level if you list the following attributes (On a x5-2 cell with HD drives)

list cell attributes maxpdiops,maxpdmbps,maxfdiops,maxfdmbps you get the following values

167 111 8929 343

These values are collected and stored in the confval column in DBA_HIST_CELL_CONFIG_DETAIL in an XML format.

These base values are used to calculate the maximum capacities of the cells and disks in the sections that follow.

Adaptive Query Optimization – Adaptive Plans – A primer

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.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Let us check the flags from v$sql

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

I I
- -
Y Y

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

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

This is how it looks for sqlid 971cdqusn06z9

xml2

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

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

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

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

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

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

References

What’s new in 12c

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

Adaptive Plans Inflection points

Adaptive Dynamic Sampling – 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 Adaptive Dynamic sampling. Adaptive Dynamic Sampling is different from the pre-12c traditional dynamic sampling, in the following aspects.

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

– Especially for parallel queries on large tables.

– Adaptive Dynamic sampling kicks in when optimizer_dynamic_sampling is set to 11.
– Adaptive Dynamic sampling 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 Adaptive Dynamic sampling 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 the Adaptive Dynamic             Sampling.

Setting OPTIMIZER_ADAPTIVE_FEATURES=FALSE does NOT turn off Adaptive Dynamic Sampling.

You can set Optimizer_Dynamic_Sampling = 0 to turn Adaptive Dynamic Sampling 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 Adaptive Dynamic Sampling off, if you so desire.

Adaptive Dynamic Sampling 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 Adaptive dynamic sampling 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 Adaptive dynamic sampling. All it does it prevent the Adaptive dynamic sampling from using the results cache.

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

Using Idea 3g Netsetter with Ubuntu Linux 14.04

Using Plug and play devices on Linux is probably not the best experience one can have. Usually it involves installing drivers, editing configuration files and reading online forums to find fixes. So i was pleasantly surprised when I signed up for a Idea Cellular 3g Broadband access, and it just worked.

It must be the popularity of Linux distributions on laptops, that had prompted Idea (And the maker of the device HUAWEI) to provide drivers on the device, that can be used for the installation. Even though their printed installation instructions (Included with the USB package) say nothing about installing on Linux, i was able to get it installed and working with no issues.

It made me so happy that it worked out of the box, that i had to blog about it and let the world know.

My laptop runs 64 bit Ubuntu 14.04 Trusty Tahrir.

Make sure that Idea has enabled your SIM for use in their network (This is a process that can take upto 48 hours after you have bought the device and signed up for their service).
In my case i signed up for a pre-paid connection that gives me 3Gb of usage in a month. (Atleast that is my understanding at present).
Take the SIM card provided in the box, and insert it into the USB device. Ensure that you push the SIM card all the way in (Or else it will say SIM card not detected as you try the install).
Now insert the USB device to a USB port on your laptop.

Start a terminal and login as root

sudo su -

cd to the usb device (You may have to replace the path below with your correct path name, to the usb device for your laptop)

cd /media/rramdas/Idea\ Net\ Setter/

Run the installer

sh ./install_linux

It will prompt you for the location to install the software. Accept the default location /usr/local/Idea_Net_Setter

id1

It completes the install and configuration and then asks you to register

id5

Click on register now, and enter your e-mail ID and Mobile phone#, they will text you a password, enter that.

Once you have registered you will see the following screen.

id4

Click Connect and it connects you to the mobile broadband. Clean and simple.

From hereon every time you reboot your laptop and re-insert the USB device, the o/s will automatically detect that the usb device is a netsetter broadband modem, and prompt you with the screen to connect.

You can bring up the Idea connection manager screen by invoking search and just typing in “Idea”. This application shows you a wealth of information including your current utilization, and the upload and download speeds you are getting. Pretty awesome if you ask me.