Archive

Author Archive

Granularity impact on visualization

May 22nd, 2020

The longer the timeline we show in the graph, either the more points we show or the larger granularity we use.

For example if I show 1 hour of data, then I can show 60 points of 1 minute each granularity.

If I showed a day at 1 minute each then that would be 1440 points.

Say we wanted to limit the points to less than 300 points so our UI stays responsive no matter the time scale.

The for 24 hours I can show 5 minute granularity which is  288 points

If  I show 25 hours  that’s 300 points at 5 minute granularity so we are no longer under 300 points.

At this time I’d have to jump to the next level of granularity. In my case the next step up is 1 hour.

Using one hour granularity will look different than 5 minutes.

The following is roughly a day at 5 minute granularity first and 1 hour granularity second.
image(17)
a

image(18)

The most common affect of larger granularity is loosing the spikes i.e the 5 minute granularity could have highs and lows that get averaged out at larger granularity.

What is noticeable is that the 1 hour granularity above is that it is spiky. The highs and lows are roughly the same. The spikiness comes from the fact that the above graphs are using points. Each point represents the average value for the width of the granularity, but since we are using points, the actual width of the granularity is not visible. What would be more accurate is using bars for each “point” where the bar was the width of the granularity.

 

Here are a couple of examples from the tool DB Optimizer were the width of the bar represents the granularity:

Screen Shot 2020-05-22 at 10.41.44 AM

 

 

Screen Shot 2020-05-22 at 10.41.23 AM

Uncategorized

Jobs at RDS

April 13th, 2020

I’m personally interested in using explain plans to identify missing indexes opportunities.  Let me know directly if that is of interest to you. This could be in the roll of database engineer and design or development engineer on design and coding.

My manager has a bunch of openings in all sorts of areas from database performance monitoring, recommendations, proxy

The dev team I work with on performance monitoring and related has dev positions open

 

 

 

 

 

Uncategorized

Where to add Index on Postgres

March 30th, 2020

 

Summary

Goal in this post is to layout a methodology that can be implemented to scan the explain plans and identify opportunities to optimize the sql execution with additional indexes.

The missing index opportunities outlined here are

  1. Indexes that would avoid full table scan with a predicate filter that filters out most of the returned rows
  2. Joins that are missing index on the join field
  3. Index range scans that could be made more efficient by addition of a field to existing index

Also for future consideration, but not yet outline here

  • Recommending covering index where the addition of fields to an index avoids table access all together
  • Indexes that support the order by clause

Basic procedure is to take the output from “explain” command.
In the explain output only look at nodes that are at least say 10-30% or more of total cost of the whole query.
Look for full table scans , these are nodes with the following entry : “Node Type”: “Seq Scan”
If it’s in the second node of a nested loop, suggest an index on the join field
If it’s is the most expensive node of a Hash join suggest an index on the join field
If it’s the most expense node of Merge Join suggest an index on the join field.
If is followed by a filter, suggest an index on the filter and join column.

If if the filter is not the most expensive node in a HJ or it’s the first node in a NL, and that node is expensive and the filter is expected to return X% ( for example 10% ) or less of the table after applying the filter, then suggest an index on that filter column.
Additionally Index range scans that could be improved by adding fields to the index will require using “explain analyze”. With explain analyze we can see how many rows were filtered out after the index access.

Get plan with “Explain” and text from SQL statement

EXPLAIN (COSTS true, FORMAT json, verbose true)
select id from hypg1 where id = 2006;
                QUERY PLAN                 
-------------------------------------------
 [                                        +
   {                                      +
     "Plan": {                            +
       "Node Type": "Gather",             +
       "Parallel Aware": false,           +
       "Startup Cost": 1000.00,           +
       "Total Cost": 10633.43,            +
       "Plan Rows": 1,                    +
       "Plan Width": 4,                   +
       "Output": ["id"],                  +
       "Workers Planned": 2,              +
       "Single Copy": false,              +
       "Plans": [                         +
         {                                +
           "Node Type": "Seq Scan",       +
           "Parent Relationship": "Outer",+
           "Parallel Aware": true,        +
           "Relation Name": "hypg1",      +
           "Schema": "public",            +
           "Alias": "hypg1",              +
           "Startup Cost": 0.00,          +
           "Total Cost": 9633.33,         +
           "Plan Rows": 1,                +
           "Plan Width": 4,               +
           "Output": ["id"],              +
           "Filter": "(hypg1.id = 2006)"  +
         }                                +
       ]                                  +
     }                                    +
   }                                      +
 ]

 

Cost in Explain Plan

In all cases we should sort nodes by cost and only look at nodes that take up a certain percentage or more of the cost.
Each node has a “cost”. We need to extract the “total cost” at each node in the plan and compare that to the “total cost” of the whole query (top node) and calculate % of the “total cost” at a node compared whole query and only look at nodes that represent some value say 10% or more of total cost. Costs include the sum of all indented nodes, so to get the per node time or cost we have subtract out the child nodes.

If we can run “explain analyze” we can get the actual time per node. The command “explain analyze” actually runs the query . I would say we should plan on “explain analyze” with certain constraints. If the query is already being run multiple times a minute, then running it once more for tuning information is probably worth it. Time is just like cost where time includes times of child nodes, so we have subtract out the child nodes to see the time elapsed at every node.

Here is an example of the costs at nodes and showing the child nodes are subcomponents of the parent nodes cost

 

[                                                               +
   {                                                             +
     "Plan": {                                                   +
       "Total Cost": 22308.54,                                   +
       "Plans": [                                                +
         {                                                       +
           "Total Cost": 11675.10,                               +
             {                                                   +
               "Total Cost": 10675.00,                           +
             }                                                   +
         },                                                      +
         {                                                       +
           "Total Cost": 10633.43,                               +
             {                                                   +
               "Total Cost": 9633.33,                            +
             }                                                   +
           ]                                                     +
         }                                                       +
       ]                                                         +
     }                                                           +
   }                                                             +
 ]

for example, subtracting out the child node costs to get the per node costs

[                                                               +
   {                                                             +
     "Plan": {                                                   +
       "Total Cost": 22308.54,  --  node cost 22308.54 - (11675.10+10633.43) = 0.01
       "Plans": [                                                +
         {                                                       +
           "Total Cost": 11675.10, --  node cost 11675.10 - 10675.00 = 1000.1
             {                                                   +
               "Total Cost": 10675.00,                           +
             }                                                   +
         },                                                      +
         {                                                       +
           "Total Cost": 10633.43, --  node cost   10633.43 - 9633.33 = 1000.1
             {                                                   +
               "Total Cost": 9633.33,                            +
             }                                                   +
           ]                                                     +
         }                                                       +
       ]                                                         +
     }                                                           +
   }                                                             +
 ]

Predicate filters missing indexes

For predicate filters that can use indexes scan the explain plan for the following string

           "Node Type": "Seq Scan",       +

and see if they are followed by a “Filter”.

           "Total Cost": 9633.33,         +
           "Plan Rows": 1,                +
           "Filter": "(hypg1.id = 2006)"  +

Verify that the node is a significant cost of overall cost of query. This node takes up most to the cost of the plan 9633.33 out of 10633.43 so it is the most important node to optimize.
We should only look at nodes that take up a certain percentage of total cost, say 10% or more. ( value should be configurable in our code, as it is debatable where the cut off point is)

We can compare rows returned (“Plan Rows”) to total rows in the table statistics
If the filter eliminates a large amount of rows, then it is a candidate.
The following query had to be run in the database that the table is in, thus have to collect database as part of the sampling of the load data.

SELECT reltuples FROM pg_class WHERE relname = 'hypg1';

reltuples
-----------
 1e+06

% filtered = 100 * ( 1e+06 – 1 ) / 1e+06 = 99.99% rows are filtered out, so it’s the perfect candidate.

We should only recommend index the filter returns 10% or less of the table (i.e. 90% of rows returned from full table scanned are filtered out) and in the advisor recommendation for the index we should indicate much of the table is returned via the filter. We can give some recommendations based on the % for example
low recommendation 5%-10% of table returned after apply filter
medium recommendation 1%-5% of table returned after apply filter
high recommendation < 1% of table returned after apply filter

The filter column is hypg1.id thus we suggest an index on hypg1.id

Joins missing indexes

Using the following query in the examples below

explain (FORMAT json)
select max (t1.data)
from t1, t2
where t1.id = t2.id
and t1.clus = 10
and t2.val > 1001
;

HASH JOIN

Look for nodes of type “Hash Cond”: ” that are followed by a “Seq Scan”
If we find a “Seq Scan” is preceded by a Hash JOIN and cost is a significant amount of total query, then suggest an index on the join column
If both nodes of the hash join have Seq Scans , suggest the index join column from the more expensive node.
If that node also contains a filter, suggest including the filter in the index.

There is “Node Type”: “Seq Scan”
table “Relation Name”: “t2″,
cost “Total Cost”: 13512.67″
looking the hash join above the Seq Scan node, the join condition is

"Hash Cond": "(t2.id = t1.id)",  

suggest index on t2.id

              "Plans": [                                                 +
                 {                                                        +
                   "Node Type": "Hash Join",                              +
                   "Parent Relationship": "Outer",                        +
                   "Parallel Aware": false,                               +
                   "Join Type": "Inner",                                  +
                   "Startup Cost": 11.40,                                 +
                   "Total Cost": 15086.97,                                +
                   "Plan Rows": 41,                                       +
                   "Plan Width": 33,                                      +
                   "Output": ["t1.data"],                                 +
                   "Inner Unique": false,                                 +
                   "Hash Cond": "(t2.id = t1.id)",                        +
                   "Plans": [                                             +
                     {                                                    +
                       "Node Type": "Seq Scan",                           +
                       "Parent Relationship": "Outer",                    +
                       "Parallel Aware": true,                            +
                       "Relation Name": "t2",                             +
                       "Schema": "public",                                +
                       "Alias": "t2",                                     +
                       "Startup Cost": 0.00,                              +
                       "Total Cost": 13512.67,                            +
                       "Plan Rows": 416667,                               +
                       "Plan Width": 4,                                   +
                       "Output": ["t2.id", "t2.clus", "t2.val", "t2.data"]+
                     },

Full explain

total cost of query is 41534.23
HJ node cost is 40532.27 on condition “Hash Cond”: “(t2.id = t1.id)”,
Most expensive node is second node of hash join, “Total Cost”: 24346.00
Second node , the expensive node, is doing full scan on “Relation Name”: “t1″
Thus suggest an index on t1.id, the join condition of the hash join.
The T1 node also contains a filter that returns “Plan Rows”: 5000,” i.e. 5000 rows of 1M in the table ( we look up total rows in pg_class) . The filter is on “Filter”: “((clus) = 10)” so we suggest an index on the combine id & clus

recommend index on t1 ( id, clus)

                                                         +
 [                                                        +
   {                                                      +
     "Plan": {                                            +
       "Node Type": "Aggregate",                          +
       "Strategy": "Plain",                               +
       "Partial Mode": "Finalize",                        +
       "Parallel Aware": false,                           +
       "Startup Cost": 41534.23,                          +
       "Total Cost": 41534.24,                            +
       "Plan Rows": 1,                                    +
       "Plan Width": 32,                                  +
       "Plans": [                                         +
         {                                                +
           "Node Type": "Gather",                         +
           "Parent Relationship": "Outer",                +
           "Parallel Aware": false,                       +
           "Startup Cost": 41534.01,                      +
           "Total Cost": 41534.22,                        +
           "Plan Rows": 2,                                +
           "Plan Width": 32,                              +
           "Workers Planned": 2,                          +
           "Single Copy": false,                          +
           "Plans": [                                     +
             {                                            +
               "Node Type": "Aggregate",                  +
               "Strategy": "Plain",                       +
               "Partial Mode": "Partial",                 +
               "Parent Relationship": "Outer",            +
               "Parallel Aware": false,                   +
               "Startup Cost": 40534.01,                  +
               "Total Cost": 40534.02,                    +
               "Plan Rows": 1,                            +
               "Plan Width": 32,                          +
               "Plans": [                                 +
                 {                                        +
                   "Node Type": "Hash Join",              +
                   "Parent Relationship": "Outer",        +
                   "Parallel Aware": false,               +
                   "Join Type": "Inner",                  +
                   "Startup Cost": 24408.50,              +
                   "Total Cost": 40532.27,                +
                   "Plan Rows": 695,                      +
                   "Plan Width": 33,                      +
                   "Inner Unique": false,                 +
                   "Hash Cond": "(t2.id = t1.id)",        +
                   "Plans": [                             +
                     {                                    +
                       "Node Type": "Seq Scan",           +
                       "Parent Relationship": "Outer",    +
                       "Parallel Aware": true,            +
                       "Relation Name": "t2",             +
                       "Alias": "t2",                     +
                       "Startup Cost": 0.00,              +
                       "Total Cost": 15596.00,            +
                       "Plan Rows": 138889,               +
                       "Plan Width": 4,                   +
                       "Filter": "((val + 0) > 1001)"     +
                     },                                   +
                     {                                    +
                       "Node Type": "Hash",               +
                       "Parent Relationship": "Inner",    +
                       "Parallel Aware": false,           +
                       "Startup Cost": 24346.00,          +
                       "Total Cost": 24346.00,            +
                       "Plan Rows": 5000,                 +
                       "Plan Width": 37,                  +
                       "Plans": [                         +
                         {                                +
                           "Node Type": "Seq Scan",       +
                           "Parent Relationship": "Outer",+
                           "Parallel Aware": false,       +
                           "Relation Name": "t1",         +
                           "Alias": "t1",                 +
                           "Startup Cost": 0.00,          +
                           "Total Cost": 24346.00,        +
                           "Plan Rows": 5000,             +
                           "Plan Width": 37,              +
                           "Filter": "((clus) = 10)"      +
                         }                                +
                       ]                                  +
                     }                                    +
                   ]                                      +
                 }                                        +
               ]                                          +
             }                                            +
           ]                                              +
         }                                                +
       ]                                                  +
     }                                                    +
   }                                                      +                                                   +

Nested Loops

Similar we find a Nest Loops join where the second node has a Seq Scan, look to see if an index can be added to support the join. Only look at Nest Loop nodes that are a significant % of total query cost.

Most of the cost of the query is the next loops:

          "Node Type": "Nested Loop",        +
  ...
          "Total Cost": 1320240.33,          + 

The first node in the NL select rows that are looked up in the second node.
The second node is doing a full table scan:

                     "Node Type": "Seq Scan",       +
                       "Relation Name": "t2",         +
                       "Plan Rows": 1000000,          +

We want to avoid Full table scans on the second node of NL.
Every row in the first node will do a full table scan on the second node.
The join is on

                   "Join Filter": "(t1.id = t2.id)",  +

So we suggest an index on t2.id

[                                                    +
   {                                                  +
     "Plan": {                                        +
       "Node Type": "Aggregate",                      +
       "Strategy": "Plain",                           +
       "Partial Mode": "Finalize",                    +
       "Parallel Aware": false,                       +
       "Startup Cost": 1321240.65,                    +
       "Total Cost": 1321240.66,                      +
       "Plan Rows": 1,                                +
       "Plan Width": 32,                              +
       "Plans": [                                     +
         {                                            +
           "Node Type": "Gather",                     +
           "Parent Relationship": "Outer",            +
           "Parallel Aware": false,                   +
           "Startup Cost": 1321240.44,                +
           "Total Cost": 1321240.65,                  +
           "Plan Rows": 2,                            +
           "Plan Width": 32,                          +
           "Workers Planned": 2,                      +
           "Single Copy": false,                      +
           "Plans": [                                 +
             {                                        +
               "Node Type": "Aggregate",              +
               "Strategy": "Plain",                   +
               "Partial Mode": "Partial",             +
               "Parent Relationship": "Outer",        +
               "Parallel Aware": false,               +
               "Startup Cost": 1320240.44,            +
               "Total Cost": 1320240.45,              +
               "Plan Rows": 1,                        +
               "Plan Width": 32,                      +
               "Plans": [                             +
                 {                                    +
                   "Node Type": "Nested Loop",        +
                   "Parent Relationship": "Outer",    +
                   "Parallel Aware": false,           +
                   "Join Type": "Inner",              +
                   "Startup Cost": 0.00,              +
                   "Total Cost": 1320240.33,          +
                   "Plan Rows": 41,                   +
                   "Plan Width": 33,                  +
                   "Inner Unique": false,             +
                   "Join Filter": "(t1.id = t2.id)",  +
                   "Plans": [                         +
                     {                                +
                       "Node Type": "Seq Scan",       +
                       "Parent Relationship": "Outer",+
                       "Parallel Aware": true,        +
                       "Relation Name": "t1",         +
                       "Alias": "t1",                 +
                       "Startup Cost": 0.00,          +
                       "Total Cost": 14554.33,        +
                       "Plan Rows": 41,               +
                       "Plan Width": 37,              +
                       "Filter": "(clus = 10)"        +
                     },                               +
                     {                                +
                       "Node Type": "Seq Scan",       +
                       "Parent Relationship": "Inner",+
                       "Parallel Aware": false,       +
                       "Relation Name": "t2",         +
                       "Alias": "t2",                 +
                       "Startup Cost": 0.00,          +
                       "Total Cost": 19346.00,        +
                       "Plan Rows": 1000000,          +
                       "Plan Width": 4                +
                     }                                +
                   ]                                  +
                 }                                    +
               ]                                      +
             }                                        +
           ]                                          +
         }                                            +
       ]                                              +
     }                                                +
   }                                                  +

 

Merge Join

similar for a merge join

-- to create a merge join example 
 set enable_nestloop to 'off';
 SET enable_hashjoin TO off;

 

explain (FORMAT json)
select max (t1.data)
from t1, t2
where t1.id = t2.id
and t1.clus = 10
and t2.val  > 1001
;

We want to turn the merge join into a NL or HJ.
In order to do so efficiently we need an index on the join on one table.
We want to have the index on the table with the more rows to analyze.

t1 returns 5000 rows

                           "Relation Name": "t1",         +
                           "Plan Rows": 5000,             +

t2 returns 138889

                          "Relation Name": "t2",         +
                           "Plan Rows": 138889,           +

and join on T2 is on

                   "Merge Cond": "(t2.id = t1.id)",       +

thus suggest index on t2.id

  [                                                        +
   {                                                      +
     "Plan": {                                            +
       "Node Type": "Aggregate",                          +
       "Strategy": "Plain",                               +
       "Partial Mode": "Finalize",                        +
       "Parallel Aware": false,                           +
       "Startup Cost": 55741.63,                          +
       "Total Cost": 55741.64,                            +
       "Plan Rows": 1,                                    +
       "Plan Width": 32,                                  +
       "Plans": [                                         +
         {                                                +
           "Node Type": "Gather",                         +
           "Parent Relationship": "Outer",                +
           "Parallel Aware": false,                       +
           "Startup Cost": 55741.42,                      +
           "Total Cost": 55741.63,                        +
           "Plan Rows": 2,                                +
           "Plan Width": 32,                              +
           "Workers Planned": 2,                          +
           "Single Copy": false,                          +
           "Plans": [                                     +
             {                                            +
               "Node Type": "Aggregate",                  +
               "Strategy": "Plain",                       +
               "Partial Mode": "Partial",                 +
               "Parent Relationship": "Outer",            +
               "Parallel Aware": false,                   +
               "Startup Cost": 54741.42,                  +
               "Total Cost": 54741.43,                    +
               "Plan Rows": 1,                            +
               "Plan Width": 32,                          +
               "Plans": [                                 +
                 {                                        +
                   "Node Type": "Merge Join",             +
                   "Parent Relationship": "Outer",        +
                   "Parallel Aware": false,               +
                   "Join Type": "Inner",                  +
                   "Startup Cost": 54013.29,              +
                   "Total Cost": 54739.68,                +
                   "Plan Rows": 695,                      +
                   "Plan Width": 33,                      +
                   "Inner Unique": false,                 +
                   "Merge Cond": "(t2.id = t1.id)",       +
                   "Plans": [                             +
                     {                                    +
                       "Node Type": "Sort",               +
                       "Parent Relationship": "Outer",    +
                       "Parallel Aware": false,           +
                       "Startup Cost": 29360.10,          +
                       "Total Cost": 29707.32,            +
                       "Plan Rows": 138889,               +
                       "Plan Width": 4,                   +
                       "Sort Key": ["t2.id"],             +
                       "Plans": [                         +
                         {                                +
                           "Node Type": "Seq Scan",       +
                           "Parent Relationship": "Outer",+
                           "Parallel Aware": true,        +
                           "Relation Name": "t2",         +
                           "Alias": "t2",                 +
                           "Startup Cost": 0.00,          +
                           "Total Cost": 15596.00,        +
                           "Plan Rows": 138889,           +
                           "Plan Width": 4,               +
                           "Filter": "((val ) > 1001)" +
                         }                                +
                       ]                                  +
                     },                                   +
                     {                                    +
                       "Node Type": "Sort",               +
                       "Parent Relationship": "Inner",    +
                       "Parallel Aware": false,           +
                       "Startup Cost": 24653.19,          +
                       "Total Cost": 24665.69,            +
                       "Plan Rows": 5000,                 +
                       "Plan Width": 37,                  +
                       "Sort Key": ["t1.id"],             +
                       "Plans": [                         +
                         {                                +
                           "Node Type": "Seq Scan",       +
                           "Parent Relationship": "Outer",+
                           "Parallel Aware": false,       +
                           "Relation Name": "t1",         +
                           "Alias": "t1",                 +
                           "Startup Cost": 0.00,          +
                           "Total Cost": 24346.00,        +
                           "Plan Rows": 5000,             +
                           "Plan Width": 37,              +
                           "Filter": "((clus + 0) = 10)"  +
                         }                                +
                       ]                                  +
                     }                                    +
                   ]                                      +
                 }                                        +
               ]                                          +
             }                                            +
           ]                                              +
         }                                                +
       ]                                                  +
     }                                                    +
   }                                                      +

 

3. Mixed cases – filter and join indexing opportunities

If a filter is missing an index causing a full scan (i.e. “Seq Scan”) and the table being scanned is also used in a join (hash join, merge join, nested loops join), suggest an index on both the filter column and
In the following there is a Seq Scan on T2 followed by a filter.
The filter returns an estimated 375K rows or about 1/3 of the table , i.e. not a good candidate for an index
The Seq Scan is part of a HASH JOIN that represents most of the cost of the query. The hash join is on t2.id.

cost total query 16973.03
t2 access for the hash join cost: 14554.33
t2 has 1M rows ( have to collect this from table stats)
access is on t2.id , “Hash Cond”: “(t2.id = t1.id)”,

suggest index on t2 id and val to avoid the full table scan.

 [                                                        +
   {                                                      +
     "Plan": {                                            +
       "Node Type": "Aggregate",                          +
       "Strategy": "Plain",                               +
       "Partial Mode": "Finalize",                        +
       "Parallel Aware": false,                           +
       "Startup Cost": 16973.02,                          +
       "Total Cost": 16973.03,                            +
       "Plan Rows": 1,                                    +
       "Plan Width": 32,                                  +
       "Plans": [                                         +
         {                                                +
           "Node Type": "Gather",                         +
           "Parent Relationship": "Outer",                +
           "Parallel Aware": false,                       +
           "Startup Cost": 16972.81,                      +
           "Total Cost": 16973.02,                        +
           "Plan Rows": 2,                                +
           "Plan Width": 32,                              +
           "Workers Planned": 2,                          +
           "Single Copy": false,                          +
           "Plans": [                                     +
             {                                            +
               "Node Type": "Aggregate",                  +
               "Strategy": "Plain",                       +
               "Partial Mode": "Partial",                 +
               "Parent Relationship": "Outer",            +
               "Parallel Aware": false,                   +
               "Startup Cost": 15972.81,                  +
               "Total Cost": 15972.82,                    +
               "Plan Rows": 1,                            +
               "Plan Width": 32,                          +
               "Plans": [                                 +
                 {                                        +
                   "Node Type": "Hash Join",              +
                   "Parent Relationship": "Outer",        +
                   "Parallel Aware": false,               +
                   "Join Type": "Inner",                  +
                   "Startup Cost": 11.40,                 +
                   "Total Cost": 15972.72,                +
                   "Plan Rows": 37,                       +
                   "Plan Width": 33,                      +
                   "Inner Unique": false,                 +
                   "Hash Cond": "(t2.id = t1.id)",        +
                   "Plans": [                             +
                     {                                    +
                       "Node Type": "Seq Scan",           +
                       "Parent Relationship": "Outer",    +
                       "Parallel Aware": true,            +
                       "Relation Name": "t2",             +
                       "Alias": "t2",                     +
                       "Startup Cost": 0.00,              +
                       "Total Cost": 14554.33,            +
                       "Plan Rows": 375098,               +
                       "Plan Width": 4,                   +
                       "Filter": "(val > 1001)"           +
                     },                                   +
                     {                                    +
                       "Node Type": "Hash",               +
                       "Parent Relationship": "Inner",    +
                       "Parallel Aware": false,           +
                       "Startup Cost": 10.16,             +
                       "Total Cost": 10.16,               +
                       "Plan Rows": 99,                   +
                       "Plan Width": 37,                  +
                       "Plans": [                         +
                         {                                +
                           "Node Type": "Index Scan",     +
                           "Parent Relationship": "Outer",+
                           "Parallel Aware": false,       +
                           "Scan Direction": "Forward",   +
                           "Index Name": "t1_clus",       +
                           "Relation Name": "t1",         +
                           "Alias": "t1",                 +
                           "Startup Cost": 0.42,          +
                           "Total Cost": 10.16,           +
                           "Plan Rows": 99,               +
                           "Plan Width": 37,              +
                           "Index Cond": "(clus = 10)"    +
                         }                                +
                       ]                                  +
                     }                                    +
                   ]                                      +
                 }                                        +
               ]                                          +
             }                                            +
           ]                                              +
         }                                                +
       ]                                                  +
     }                                                    +
   }                                                      +

4. Index Range scans

To see if an index needs more columns to be efficient looks like ti will require “explain analyze” and not just “explain”

select count(*)
from t1
where t1.clus >= 10 and t1.clus < 15
and t1.val = 1001
;

There is an index on t1.clus and the explain shows it is used
but we don’t know how many rows were retrieved by the index vs filtered out by the Filter

[                                                        +
   {                                                      +
     "Plan": {                                            +
       "Node Type": "Aggregate",                          +
       "Strategy": "Plain",                               +
       "Partial Mode": "Simple",                          +
       "Parallel Aware": false,                           +
       "Startup Cost": 27.14,                             +
       "Total Cost": 27.15,                               +
       "Plan Rows": 1,                                    +
       "Plan Width": 8,                                   +
       "Plans": [                                         +
         {                                                +
           "Node Type": "Index Scan",                     +
           "Parent Relationship": "Outer",                +
           "Parallel Aware": false,                       +
           "Scan Direction": "Forward",                   +
           "Index Name": "t1_clus",                       +
           "Relation Name": "t1",                         +
           "Alias": "t1",                                 +
           "Startup Cost": 0.42,                          +
           "Total Cost": 27.13,                           +
           "Plan Rows": 1,                                +
           "Plan Width": 0,                               +
           "Index Cond": "((clus >= 10) AND (clus < 15))",+
           "Filter": "(val = 1001)"                       +
         }                                                +
       ]                                                  +
     }                                                    +
   }                                                      +

but it doesn’t show how many rows are retrieved by the index and how many are post processed filtered out by the Filter.
Using explain analyze does.
In the following output from “explain analyze”
Only 1 run is returned after index access and filter
but filter takes out 499 rows

"Filter": "(val = 1001)",                      +
           "Rows Removed by Filter": 499                  +

Thus suggest to add val to the existing index

           "Index Name": "t1_clus",                       +

 

[                                                        +
   {                                                      +
     "Plan": {                                            +
       "Node Type": "Aggregate",                          +
       "Strategy": "Plain",                               +
       "Partial Mode": "Simple",                          +
       "Parallel Aware": false,                           +
       "Startup Cost": 27.14,                             +
       "Total Cost": 27.15,                               +
       "Plan Rows": 1,                                    +
       "Plan Width": 8,                                   +
       "Actual Startup Time": 0.279,                      +
       "Actual Total Time": 0.279,                        +
       "Actual Rows": 1,                                  +
       "Actual Loops": 1,                                 +
       "Plans": [                                         +
         {                                                +
           "Node Type": "Index Scan",                     +
           "Parent Relationship": "Outer",                +
           "Parallel Aware": false,                       +
           "Scan Direction": "Forward",                   +
           "Index Name": "t1_clus",                       +
           "Relation Name": "t1",                         +
           "Alias": "t1",                                 +
           "Startup Cost": 0.42,                          +
           "Total Cost": 27.13,                           +
           "Plan Rows": 1,                                +
           "Plan Width": 0,                               +
           "Actual Startup Time": 0.053,                  +
           "Actual Total Time": 0.273,                    +
           "Actual Rows": 1,                              +
           "Actual Loops": 1,                             +
           "Index Cond": "((clus >= 10) AND (clus < 15))",+
           "Rows Removed by Index Recheck": 0,            +
           "Filter": "(val = 1001)",                      +
           "Rows Removed by Filter": 499                  +
         }                                                +
       ]                                                  +
     },                                                   +
     "Planning Time": 0.270,                              +
     "Triggers": [                                        +
     ],                                                   +
     "Execution Time": 0.388                              +
   }                                                      +

 

Check if INDEX we suggest already exists

look up existence of index ( https://stackoverflow.com/questions/2204058/list-columns-with-indexes-in-postgresql)

select
    t.relname as table_name,
    i.relname as index_name,
    array_to_string(array_agg(a.attname), ', ') as column_names
from
    pg_class t,
    pg_class i,
    pg_index ix,
    pg_attribute a
where
    t.oid = ix.indrelid
    and i.oid = ix.indexrelid
    and a.attrelid = t.oid
    and a.attnum = ANY(ix.indkey)
    and t.relkind = 'r'
    and t.relname like 'auth%'
group by
    t.relname,
    i.relname
order by
    t.relname,
    i.relname;
    

 table_name | index_name | column_names 
------------+------------+--------------
 authors    | authors_id | id

 

 

Creating data for test cases above

drop table t1;
 
 create table t1 ( 
    id  int,
    clus int,
    val  int,
    data VARCHAR(40)
 );
 
 insert into t1 (
    id, clus , val, data
)
  select 
  i,
  trunc(i/100),
  mod(i,10000),
  left(md5(random()::text), 40) 
from generate_series(1, 1000000) s(i);
  
select count(*) from t1 where clus = 1 ;
100
select count(*) from t1 where val =1 ;
100

create table t2 as select * from t1;  
  

explain (analyze,verbose,buffers)
select max(t1.data) from  t1, t2
where t1.id = t2.id 
and t1.clus = 1
;

Parameters that affect the explain plan

SET max_parallel_workers_per_gather = 0;
 set enable_mergejoin to 'off';
 set enable_nestloop to 'off';
 SET enable_hashjoin TO off;

Uncategorized

Indexes on Joins for MySQL

March 24th, 2020

MySQL is simpler than the Oracle example  because MySQL only has Nested Loops and doesn’t have Hash Join.

Here is the query, like on the Oracle example from the last blog post

select max(t1.data) 
from  t1, t2  
where t1.id = t2.id 
and t1.clus  = 1
;

 

So there are only 4 examples below which all do NL joins

  1. full table scan on T1 and T2
    1. 7.83 secs
  2. index on T1 predicate filter column
    1. 7.39 secs
  3. index on T2 join column
    1. 0.49 secs
  4. index on both T2 join column and T1 predicate filter column
    1. 0.06 secs

There isn’t an idea of “explain analyze” on MySQL until MySQL 8.0.18 and I did my testing on 8.0.17, so the explain plans costs are estimates not actual values, but the elasped time of the query is an actual value.

 

 

drop table seed;
CREATE TABLE seed ( id INT AUTO_INCREMENT PRIMARY KEY, val INT);
 insert into seed(val) values (1);
insert into seed(val) select val from seed;  /*  2 */
insert into seed(val) select val from seed;  /*  4 */
insert into seed(val) select val from seed;  /*  8 */
insert into seed(val) select val from seed;  /*  16 */
insert into seed(val) select val from seed;  /*  32 */
insert into seed(val) select val from seed;  /*  64 */
insert into seed(val) select val from seed;  /*  128 */
insert into seed(val) select val from seed;  /*  256 */
insert into seed(val) select val from seed;  /*  512 */
insert into seed(val) select val from seed;  /*  1024 */
insert into seed(val) select val from seed;  /*  2048 */
insert into seed(val) select val from seed;  /*  4096 */
insert into seed(val) select val from seed;  /*  8192 */
insert into seed(val) select val from seed;  /*  16384 */
insert into seed(val) select val from seed;  /*  32768 */
insert into seed(val) select val from seed;  /*  131072 */
insert into seed(val) select val from seed;  /*  262144 */
insert into seed(val) select val from seed;  /*  524288 */
insert into seed(val) select val from seed;  /*  1048576 */
 select count(*) from seed;

 drop table dummy;
 
  create table dummy ( 
    id  int,
    clus int,
    val int,
    data VARCHAR(40)
 );
 
 insert into dummy (
    id, clus , val, data
)
 select 
 id,
 truncate(id/169,0),
 mod(id,10000), 
 CONV(FLOOR(RAND() * 99999999999999), 10, 36) 
from seed
;

select count(*) from dummy where clus = 1;
+----------+
| count(*) |
+----------+
|      106 |
+----------+
select count(*) from dummy where val =1;
+----------+
| count(*) |
+----------+
|      106 |
+----------+

drop table t1;
drop table t2;

create table t1 as select * from dummy;
create table t2 as select * from dummy; 



Strange thing is that there are gaps in the sequence # in the seed table, thus the strange value in the truncate to get 106 values, i.e. truncate(id/169,0).

First we run the query with no indexes ( I already created the indexes so force them off in the example with “USE INDEX()” )

both nodes have access type

"access_type": "ALL"

so they are doing full table scans.

explain format=json select max(t1.data) 
from 
t1 USE INDEX ()
, t2 USE INDEX ()
where t1.id = t2.id 
and t1.clus = 1
;

1 row in set (7.83 sec)

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "18102538.86"
    },
    "nested_loop": [
      {
        "table": {
          "table_name": "t1",
          "access_type": "ALL",
          "rows_examined_per_scan": 1045682,
          "rows_produced_per_join": 172,
          "filtered": "0.02",
          "cost_info": {
            "read_cost": "105376.24",
            "eval_cost": "17.21",
            "prefix_cost": "105393.45",
            "data_read_per_join": "29K"
          },
          "used_columns": [
            "id",
            "clus",
            "data"
          ],
          "attached_condition": "(`kyle`.`t1`.`clus` = 1)"
        }
      },
      {
        "table": {
          "table_name": "t2",
          "access_type": "ALL",
          "rows_examined_per_scan": 1045682,
          "rows_produced_per_join": 172,
          "filtered": "0.00",
          "using_join_buffer": "Block Nested Loop",
          "cost_info": {
            "read_cost": "917.35",
            "eval_cost": "17.21",
            "prefix_cost": "18102538.86",
            "data_read_per_join": "29K"
          },
          "used_columns": [
            "id"
          ],
          "attached_condition": "(`kyle`.`t2`.`id` = `kyle`.`t1`.`id`)"
        }
      }
    ]
  }
}

We can see that the filter on T1 should filter most of the table (172 rows out of 1045682) so now add index on t1.clus the predicate filter ( I remove the “USE INDEX () ” on t1 so the index on T1 is now used in the explain ) . The execution time is a little faster but not that much 7.83 vs 7.39. We see that T1 is now accessed by index

explain format=json select max(t1.data) 
from  t1, t2  
USE INDEX ()
where t1.id = t2.id 
and t1.clus    = 1
;
 row in set (7.39 sec)

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "11085148.28"
    },
    "nested_loop": [
      {
        "table": {
          "table_name": "t1",
          "access_type": "ref",
          "possible_keys": [
            "t1_clus"
          ],
          "key": "t1_clus",
          "used_key_parts": [
            "clus"
          ],
          "key_length": "5",
          "ref": [
            "const"
          ],
          "rows_examined_per_scan": 106,
          "rows_produced_per_join": 106,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "26.50",
            "eval_cost": "10.60",
            "prefix_cost": "37.10",
            "data_read_per_join": "18K"
          },
          "used_columns": [
            "id",
            "clus",
            "data"
          ]
        }
      },
      {
        "table": {
          "table_name": "t2",
          "access_type": "ALL",
          "rows_examined_per_scan": 1045682,
          "rows_produced_per_join": 105,
          "filtered": "0.00",
          "using_join_buffer": "Block Nested Loop",
          "cost_info": {
            "read_cost": "881.98",
            "eval_cost": "10.60",
            "prefix_cost": "11085148.28",
            "data_read_per_join": "18K"
          },
          "used_columns": [
            "id"
          ],
          "attached_condition": "(`kyle`.`t2`.`id` = `kyle`.`t1`.`id`)"
        }
      }
    ]
  }
}

 

On T2 we see that we access 105 row after examining 1045682, thus a good opportunity for an index. Now add an index or the join column and we get a significant improvement in execution time. We go from 7.39 secs to 0.49  or 7.39/0.49 or a 15x improvement

explain format=json select max(t1.data) 
from  t1 USE INDEX () , t2  
where t1.id = t2.id 
and t1.clus   = 1
;
1 row in set (0.49 sec)

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "105582.64"
    },
    "nested_loop": [
      {
        "table": {
          "table_name": "t1",
          "access_type": "ALL",
          "rows_examined_per_scan": 1045682,
          "rows_produced_per_join": 172,
          "filtered": "0.02",
          "cost_info": {
            "read_cost": "105376.24",
            "eval_cost": "17.21",
            "prefix_cost": "105393.45",
            "data_read_per_join": "29K"
          },
          "used_columns": [
            "id",
            "clus",
            "data"
          ],
          "attached_condition": "((`kyle`.`t1`.`clus` = 1) and (`kyle`.`t1`.`id` is not null))"
        }
      },
      {
        "table": {
          "table_name": "t2",
          "access_type": "ref",
          "possible_keys": [
            "t2_id"
          ],
          "key": "t2_id",
          "used_key_parts": [
            "id"
          ],
          "key_length": "5",
          "ref": [
            "kyle.t1.id"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 172,
          "filtered": "100.00",
          "using_index": true,
          "cost_info": {
            "read_cost": "171.98",
            "eval_cost": "17.21",
            "prefix_cost": "105582.64",
            "data_read_per_join": "29K"
          },
          "used_columns": [
            "id"
          ]
        }
      }
    ]
  }
}

Now use both indexes, which gives us a small  0.43 reduction in execution time, but as a ratio from the previous 0.49/0.06, i.e. an 8x improvement, still pretty good

explain format=json select max(t1.data) 
from  t1, t2  
where t1.id = t2.id 
and t1.clus  = 1
;
1 row in set (0.06 sec)

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "153.62"
    },
    "nested_loop": [
      {
        "table": {
          "table_name": "t1",
          "access_type": "ref",
          "possible_keys": [
            "t1_clus"
          ],
          "key": "t1_clus",
          "used_key_parts": [
            "clus"
          ],
          "key_length": "5",
          "ref": [
            "const"
          ],
          "rows_examined_per_scan": 106,
          "rows_produced_per_join": 106,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "26.50",
            "eval_cost": "10.60",
            "prefix_cost": "37.10",
            "data_read_per_join": "18K"
          },
          "used_columns": [
            "id",
            "clus",
            "data"
          ],
          "attached_condition": "(`kyle`.`t1`.`id` is not null)"
        }
      },
      {
        "table": {
          "table_name": "t2",
          "access_type": "ref",
          "possible_keys": [
            "t2_id"
          ],
          "key": "t2_id",
          "used_key_parts": [
            "id"
          ],
          "key_length": "5",
          "ref": [
            "kyle.t1.id"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 106,
          "filtered": "100.00",
          "using_index": true,
          "cost_info": {
            "read_cost": "105.93",
            "eval_cost": "10.60",
            "prefix_cost": "153.63",
            "data_read_per_join": "18K"
          },
          "used_columns": [
            "id"
          ]
        }
      }
    ]
  }

.

Uncategorized

Indexes for Joins on Oracle

March 23rd, 2020

Looking at ways to programmatically analyze explain plans to see if adding indexes would help.

The analysis is straight forward for for full table scans with strong predicate filters. If there is a strong predicate filter on an explain plan node with a full table scan then it’s a good candidate for an index.

Now a similar situation exists for a join.

Again if the join has a strong predicate filter on a full table scan, that is a candidate.

On the other hand when the actual join fields look like they will benefit from an index, it’s less obvious.

In the queries below T1 and T2 have the same data.

Here is the query

select  max(t1.data) from  t1, t2 
where 
      t1.id = t2.id  
   and t1.clus   = 1  ;

 

First we try with no indexes.

Then we try with an index on the predicate filter on T1.

Then we add a index on the join column on T2.

The idea being that we should see performance improvements at the addition of each index so that the final efficient path uses the predicate filter index on T1 returning a small amount of rows then  drive into T2 with those small amount of rows using the index on T2 join field via an NL join.

The surprising thing is that a HJ that simply hashes values from the predicate filtered rows from T1 and driving into the hash result with all the rows from T2 is just efficient and we don’t have to maintain the index on the t2 join column! It surprising to me. Sure HJs are cool, but I always thought of them as just in time indexes (ie the hashed result) which is work that could be avoid by having an index supporting an NL join and not having to recreate the hash result every query execution. (I’ll have to do some load testing to see what the actual impact of the NL vs HJ query on total database resource usage and load).

Simply adding an index on T1 filter column gets us a HJ that is just as efficient as having a NL using the T2 join field but in the case of the HJ we don’t have to maintain the T2 join column filter.

There is a strong predicate filter on T1.clus ( called “clus” because the values a clustered, we can look non-clustered later, i.e. the same query but using the “val” column which is not clustered)

In  the queries below, a NL driving on T1 rows after the predicate filter into T2 using an index on the join, is efficient.

But the HJ , hashing T1 after predicate filter and driving into the hash with all the rows from T2 is surprisingly (to me) as efficient or more efficient).

First example will start with out any index usage.

Create test data:

drop table seed;
create table seed as
select rownum n from dual connect by level <= 1e6
;

drop table t1;
drop table t2;
create table t1 ( 
    id  int,
    clus int,
    val int,
    data VARCHAR(40)
 );
 
 insert into t1 (
    id, clus , val, data
)
 select 
 n,
 trunc(n/100),
 mod(n,10000),
dbms_random.string('U',dbms_random.value(2,7))
from seed;


select count(*) from t1 where clus = 1;
100
select count(*) from t1 where val = 1;
100

create table t2 as select * from t1;

alter session set Statistics_level=all;
set timing on;

The following 3 examples will use full table scans on both tables.

( I use ” + 0 ” to turn indexes off so I don’t have to drop and recreate for demo since I’ve actually already created the indexes before running these queries )

The first NL below is slower than the next HASH join because the NL has to read the full table T2 for every row in T1 resulting in 300K+ buffer accesses (100 rows after T1 filter time ~3000 buffers per row ).

  • NL
    • For every row from T1 (which is all rows from T1 where t1.clus =0 , which is 100 rows)
    • Look up that row in T2 (T2 doesn’t have an index so we do a full table scan or each 100 rows from above
    • -> 6.83 secs

The HASH just has to read T2 once (3K+ buffer accesses)  and look up each of those rows in a hash of T1 , still a lot of rows ( should be 1M, i.e. all the rows in T2 which is what the estimate says, but actual says 1212 which I don’t understand).

  • HJ
    • hash T1 (1000 rows)
    • look up every row from T2 (1M) in hash of T1
    • -> 0.09 sec

Hashing T2 instead of T1 is a lot more memory, 50M instead of 1.5 M and takes longer even though its much less look ups from the filter on T1 (only 100).

  • HJ
    • hash T2  (1M rows, a good bit of work)
    • for every row from T1 (100 rows after filter) look them up in hash of T2
    • -> 0.32 sec
select  /*+ leading(t1 t2)  use_nl(t2) */ max(t1.data) from  t1, t2 
where 
      t1.id = t2.id  + 0 
   and t1.clus + 0  = 1  ;
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

--------------------------------------------------------------------------------------
| Id  | Operation	    | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |	   |	  1 |	     |	    1 |00:00:06.83 |	 328K|
|   1 |  SORT AGGREGATE     |	   |	  1 |	   1 |	    1 |00:00:06.83 |	 328K|
|   2 |   NESTED LOOPS	    |	   |	  1 |	  36 |	  100 |00:00:06.83 |	 328K|
|*  3 |    TABLE ACCESS FULL| T1   |	  1 |	  36 |	  100 |00:00:00.07 |	3275 |
|*  4 |    TABLE ACCESS FULL| T2   |	100 |	   1 |	  100 |00:00:06.76 |	 325K|
--------------------------------------------------------------------------------------
   3 - filter("T1"."CLUS"+0=1)
   4 - filter("T1"."ID"="T2"."ID"+0)


select max(t1.data) from  t1, t2 
where 
    t1.id = t2.id  + 0 
   and t1.clus + 0  = 1  ;
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation	    | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |	OMem |	1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |	   |	  1 |	     |	    1 |00:00:00.09 |	3281 |	     |	     |		|
|   1 |  SORT AGGREGATE     |	   |	  1 |	   1 |	    1 |00:00:00.09 |	3281 |	     |	     |		|
|*  2 |   HASH JOIN SEMI    |	   |	  1 |	  36 |	  100 |00:00:00.09 |	3281 |	1753K|	1753K| 1414K (0)|
|*  3 |    TABLE ACCESS FULL| T1   |	  1 |	  36 |	  100 |00:00:00.09 |	3275 |	     |	     |		|
|   4 |    TABLE ACCESS FULL| T2   |	  1 |	1000K|	 1212 |00:00:00.01 |	   6 |	     |	     |		|
-----------------------------------------------------------------------------------------------------------------

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

   2 - access("T1"."ID"="T2"."ID"+ 0)
   3 - filter("T1"."CLUS"+ 0=1)


select  /*+ leading(t2 t1) use_hash(t1) max(t1.data) */   max(t1.data)  from  t1, t2 
where 
    t1.id = t2.id  + 0 
   and t1.clus + 0  = 1  ;
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation	    | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |	OMem |	1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |	   |	  1 |	     |	    1 |00:00:00.32 |	6526 |	     |	     |		|
|   1 |  SORT AGGREGATE     |	   |	  1 |	   1 |	    1 |00:00:00.32 |	6526 |	     |	     |		|
|*  2 |   HASH JOIN	    |	   |	  1 |	  36 |	  100 |00:00:00.32 |	6526 |	  50M|	9345K|	 49M (0)|
|   3 |    TABLE ACCESS FULL| T2   |	  1 |	1000K|	 1000K|00:00:00.03 |	3251 |	     |	     |		|
|*  4 |    TABLE ACCESS FULL| T1   |	  1 |	  36 |	  100 |00:00:00.08 |	3275 |	     |	     |		|
-----------------------------------------------------------------------------------------------------------------


Add an index on the predicate filter:

create index t1_clus on t1 (clus);

And we get a much faster hash join access as we access a lot less buffers on T1.

NL driving from T1 still has to do a full table scan on T2 for every row so is slow.

 

 

select max(t1.data) from  t1, t2 
where 
    t1.id = t2.id  + 0 
   and t1.clus    = 1  ;
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));


-------------------------------------------------------------------------------------------------------------------------------- 
| Id  | Operation			      | Name	| Starts | E-Rows | A-Rows |   A-Time	| Buffers |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT		      | 	|      1 |	  |	 1 |00:00:00.01 |      10 |	  |	  |
|   1 |  SORT AGGREGATE 		      | 	|      1 |	1 |	 1 |00:00:00.01 |      10 |	  |	  ||
|*  2 |   HASH JOIN SEMI		      | 	|      1 |    100 |    100 |00:00:00.01 |      10 |  1753K|  1753K| 1408K (0)|
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| T1	|      1 |    100 |    100 |00:00:00.01 |	4 |	  |	  ||
|*  4 |     INDEX RANGE SCAN		      | T1_CLUS |      1 |    100 |    100 |00:00:00.01 |	3 |	  |	  ||
|   5 |    TABLE ACCESS FULL		      | T2	|      1 |   1000K|   1212 |00:00:00.01 |	6 |	  |	  ||
-------------------------------------------------------------------------------------------------------------------------------- 

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

2 - access("T1"."ID"="T2"."ID")
4 - access("T1"."MILLI"=1)


select /*+ leading(t1 t2)  use_nl(t2) */  max(t1.data) from  t1, t2 
where 
      t1.id = t2.id + 0
   and t1.clus   = 1  ;
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS'));

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation			      | Name	| Starts | E-Rows | A-Rows |   A-Time	| Buffers | Reads  |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT		      | 	|      1 |	  |	 1 |00:08:14.23 |    2893K|   2892K|
|   1 |  SORT AGGREGATE 		      | 	|      1 |	1 |	 1 |00:08:14.23 |    2893K|   2892K|
|   2 |   NESTED LOOPS			      | 	|      1 |    100 |    100 |00:08:14.23 |    2893K|   2892K|
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| T1	|      1 |    100 |    100 |00:00:00.01 |	4 |	 0 |
|*  4 |     INDEX RANGE SCAN		      | T1_CLUS |      1 |    100 |    100 |00:00:00.01 |	3 |	 0 |
|*  5 |    TABLE ACCESS FULL		      | T2	|    100 |	1 |    100 |00:08:14.23 |    2893K|   2892K|
--------------------------------------------------------------------------------------------------------------------

Adding on index on T2.id and allowing the NL driving from T1 to look up rows in T2 by index gets the NL down to about the same as HASH JOIN.

The HJ is on hash of  T2 is faster when accessing T1 by it’s index on the predicate filter but still hashing all of T2 slows it down.

I don’t understand why the HJ hashing on T2 is slower when accessing T1 by index instead of full table scan.

drop index t2_id;

create index t2_id on t2 (id);  

select /*+ leading(t1 t2)  use_nl(t2) */  max(t1.data) from  t1, t2 
where 
      t1.id = t2.id  
   and t1.clus   = 1  ;
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS'));

-----------------------------------------------------------------------------------------------------------
| Id  | Operation			      | Name	| Starts | E-Rows | A-Rows |   A-Time	| Buffers |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT		      | 	|      1 |	  |	 1 |00:00:00.01 |      17 |
|   1 |  SORT AGGREGATE 		      | 	|      1 |	1 |	 1 |00:00:00.01 |      17 |
|   2 |   NESTED LOOPS SEMI		      | 	|      1 |    100 |    100 |00:00:00.01 |      17 |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| T1	|      1 |    100 |    100 |00:00:00.01 |	4 |
|*  4 |     INDEX RANGE SCAN		      | T1_CLUS |      1 |    100 |    100 |00:00:00.01 |	3 |
|*  5 |    INDEX RANGE SCAN		      | T2_ID	|    100 |   1000K|    100 |00:00:00.01 |      13 |
-----------------------------------------------------------------------------------------------------------

select /*+ leading(t2 t1) use_hash(t1)  */
max(t1.data) from  t1, t2 
where 
      t1.id = t2.id +1 
   and t1.clus = 1  ;
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

--------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation			      | Name	| Starts | E-Rows | A-Rows |   A-Time	| Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT		      | 	|      1 |	  |	 1 |00:00:00.22 |    3255 |	  |	  ||
|   1 |  SORT AGGREGATE 		      | 	|      1 |	1 |	 1 |00:00:00.22 |    3255 |	  |	  ||
|*  2 |   HASH JOIN			      | 	|      1 |     78 |    100 |00:00:00.22 |    3255 |    50M|  9345K|   49M (0)|
|   3 |    TABLE ACCESS FULL		      | T2	|      1 |   1000K|   1000K|00:00:00.03 |    3251 |	  |	  ||
|   4 |    TABLE ACCESS BY INDEX ROWID BATCHED| T1	|      1 |    100 |    100 |00:00:00.01 |	4 |	  |	  ||
|*  5 |     INDEX RANGE SCAN		      | T1_CLUS |      1 |    100 |    100 |00:00:00.01 |	3 |	  |	  ||
-------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T1"."ID"="T2"."ID"+1)
   5 - access("T1"."CLUS"=1)

select /*+ leading(t2 t1) use_hash(t1)  */
max(t1.data) from  t1, t2 
where 
      t1.id = t2.id 
   and t1.clus = 1  ;
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

--------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation			      | Name	| Starts | E-Rows | A-Rows |   A-Time	| Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT		      | 	|      1 |	  |	 1 |00:00:01.25 |    2246 |	  |	  ||
|   1 |  SORT AGGREGATE 		      | 	|      1 |	1 |	 1 |00:00:01.25 |    2246 |	  |	  ||
|*  2 |   HASH JOIN			      | 	|      1 |    100 |    100 |00:00:01.25 |    2246 |    43M|  6111K|   42M (0)|
|   3 |    INDEX FAST FULL SCAN 	      | T2_ID	|      1 |   1000K|   1000K|00:00:00.31 |    2242 |	  |	  ||
|   4 |    TABLE ACCESS BY INDEX ROWID BATCHED| T1	|      1 |    100 |    100 |00:00:00.01 |	4 |	  |	  ||
|*  5 |     INDEX RANGE SCAN		      | T1_CLUS |      1 |    100 |    100 |00:00:00.01 |	3 |	  |	  ||
--------------------------------------------------------------------------------------------------------------------------------

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

   2 - access("T1"."ID"="T2"."ID")
   5 - access("T1"."CLUS"=1)

For reference, the  order of NL and HJ access and hints

 

Screen Shot 2020-03-23 at 1.24.20 PM

reference

 

http://datavirtualizer.com/right-deep-left-deep-and-bushy-joins-in-sql/

 

Followup:

The Hash Join put about 7x (0.22 AAS)  as much load on the DB as the next loops (0.03AAS) and both are doing 178 executions/sec.

First Load Chart image with top SQL drill down filters for the  efficient HJ version. Th HJ plan is shown here.

 

-------------------------------------------------------------------------------------------------------------------------------- 
| Id  | Operation			      | Name	| Starts | E-Rows | A-Rows |   A-Time	| Buffers |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT		      | 	|      1 |	  |	 1 |00:00:00.01 |      10 |	  |	  |
|   1 |  SORT AGGREGATE 		      | 	|      1 |	1 |	 1 |00:00:00.01 |      10 |	  |	  ||
|*  2 |   HASH JOIN SEMI		      | 	|      1 |    100 |    100 |00:00:00.01 |      10 |  1753K|  1753K| 1408K (0)|
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| T1	|      1 |    100 |    100 |00:00:00.01 |	4 |	  |	  ||
|*  4 |     INDEX RANGE SCAN		      | T1_CLUS |      1 |    100 |    100 |00:00:00.01 |	3 |	  |	  ||
|   5 |    TABLE ACCESS FULL		      | T2	|      1 |   1000K|   1212 |00:00:00.01 |	6 |	  |	  ||
-------------------------------------------------------------------------------------------------------------------------------- 

 

Screen Shot 2020-03-24 at 11.21.06 AM

The second image is the load filtered by the NJ join and here is the explain.

-----------------------------------------------------------------------------------------------------------
| Id  | Operation			      | Name	| Starts | E-Rows | A-Rows |   A-Time	| Buffers |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT		      | 	|      1 |	  |	 1 |00:00:00.01 |      17 |
|   1 |  SORT AGGREGATE 		      | 	|      1 |	1 |	 1 |00:00:00.01 |      17 |
|   2 |   NESTED LOOPS SEMI		      | 	|      1 |    100 |    100 |00:00:00.01 |      17 |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| T1	|      1 |    100 |    100 |00:00:00.01 |	4 |
|*  4 |     INDEX RANGE SCAN		      | T1_CLUS |      1 |    100 |    100 |00:00:00.01 |	3 |
|*  5 |    INDEX RANGE SCAN		      | T2_ID	|    100 |   1000K|    100 |00:00:00.01 |      13 |
-----------------------------------------------------------------------------------------------------------

 

Screen Shot 2020-03-24 at 11.21.13 AM

 

The following hash join, which hashes T2, was not as efficient  hashing T1 results but it confused me where it looked like using the index on T1 filter was more expensive than not using it. Looks like it’s the other way around in actual load.

Even though skipping the T1 index shows an A-Time of 0.22 and using the T1 Index shows 1.25, when running a concurrent load it looks the opposite.

The first example below avoids the index on T1 and has an AAS of 10.36  and elapsed of 3.7 secs (instead of 0.22 in explain)

The second which uses the index on T1 filter has an AAS of 8.2 and elapsed of 3.1 secs (instead of 1.25 in explain)

Both are executing 2.24 executes/sec.

 

select /*+ leading(t2 t1) use_hash(t1)  */
max(t1.data) from  t1, t2 
where 
      t1.id = t2.id +1 
   and t1.clus = 1  ;
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

--------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation			      | Name	| Starts | E-Rows | A-Rows |   A-Time	| Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT		      | 	|      1 |	  |	 1 |00:00:00.22 |    3255 |	  |	  ||
|   1 |  SORT AGGREGATE 		      | 	|      1 |	1 |	 1 |00:00:00.22 |    3255 |	  |	  ||
|*  2 |   HASH JOIN			      | 	|      1 |     78 |    100 |00:00:00.22 |    3255 |    50M|  9345K|   49M (0)|
|   3 |    TABLE ACCESS FULL		      | T2	|      1 |   1000K|   1000K|00:00:00.03 |    3251 |	  |	  ||
|   4 |    TABLE ACCESS BY INDEX ROWID BATCHED| T1	|      1 |    100 |    100 |00:00:00.01 |	4 |	  |	  ||
|*  5 |     INDEX RANGE SCAN		      | T1_CLUS |      1 |    100 |    100 |00:00:00.01 |	3 |	  |	  ||
-------------------------------------------------------------------------------------------------------------------------------

.

Screen Shot 2020-03-24 at 12.09.16 PM

--------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation			      | Name	| Starts | E-Rows | A-Rows |   A-Time	| Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT		      | 	|      1 |	  |	 1 |00:00:01.25 |    2246 |	  |	  ||
|   1 |  SORT AGGREGATE 		      | 	|      1 |	1 |	 1 |00:00:01.25 |    2246 |	  |	  ||
|*  2 |   HASH JOIN			      | 	|      1 |    100 |    100 |00:00:01.25 |    2246 |    43M|  6111K|   42M (0)|
|   3 |    INDEX FAST FULL SCAN 	      | T2_ID	|      1 |   1000K|   1000K|00:00:00.31 |    2242 |	  |	  ||
|   4 |    TABLE ACCESS BY INDEX ROWID BATCHED| T1	|      1 |    100 |    100 |00:00:00.01 |	4 |	  |	  ||
|*  5 |     INDEX RANGE SCAN		      | T1_CLUS |      1 |    100 |    100 |00:00:00.01 |	3 |	  |	  ||
--------------------------------------------------------------------------------------------------------------------------------

.Screen Shot 2020-03-24 at 12.09.24 PM

Reference

http://datavirtualizer.com/right-deep-left-deep-and-bushy-joins-in-sql/

http://www.nocoug.org/download/2012-08/Jonathan_Lewis_Creating_Tests.pdf

Uncategorized

Oracle Imp (import) to RDS

March 2nd, 2020

Had and old export dump file I wanted to import into RDS.

I first tried to use S3 as a hosting site for the .dmp file and use rdsadmin.rdsadmin_s3_tasks.download_from_s3 to copy the file to the RDS database then import it with DBMS_DATAPUMP.

Unfortunately DBMS_DATAPUMP only works with expdp files so instead of using DBMS_DATAPUMP , I got the old “imp” binary and used it with TWO_TASK to the RDS database I wanted it loaded into.

For the record here were the steps  I took

 

First put the file into an S3 bucket called

kylelf-datadump

Create an IAM policy to allow Amazon RDS access to an Amazon S3 bucket

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/oracle-s3-integration.html#oracle-s3-integration.preparing

My policy looks like

{
 "Version": "2012-10-17",
 "Statement": [
 {
 "Sid": "VisualEditor0",
 "Effect": "Allow",
 "Action": [
 "s3:PutObject",
 "s3:GetObject",
 "s3:ListBucket"
 ],
 "Resource": "arn:aws:s3:::kylelf-datadump"
 }
 ]
}

Create an IAM role to allow Amazon RDS access to an Amazon S3 bucket

again, see: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/oracle-s3-integration.html#oracle-s3-integration.preparing

add the role to the database you want to import into

  1. Sign in to the AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/rds/.
    Choose the Oracle DB instance name to display its details.
    On the Connectivity & security tab, in the Manage IAM roles section, choose the role to add under Add IAM roles to this instance.
    For Feature, choose S3_INTEGRATION.

create s3 option group

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_WorkingWithOptionGroups.html#USER_WorkingWithOptionGroups.Create

  1. create an option group
  2. add s3 integration to option group
  3. modify instance, change option group to s3 option group

Then download the dump file from S3.

SELECT rdsadmin.rdsadmin_s3_tasks.download_from_s3(
 p_bucket_name => 'kylelf-datadump', 
 p_directory_name => 'DATA_PUMP_DIR') 
 AS TASK_ID FROM DUAL; 

TASK_ID

——————————————————————————–

1583185124793-43

Check status of download

SELECT text FROM
table(rdsadmin.rds_file_util.read_text_file(‘BDUMP’,’dbtask-1583185124793-43.log’));

Check to see the file is there

select * from table(RDSADMIN.RDS_FILE_UTIL.LISTDIR('DATA_PUMP_DIR')) order by mtime;
FILENAME-  TYPE     FILESIZE MTIME
---------- ---------- ---------
TC1.dmp  file     39829504 02-MAR-20

My file is the TC1.dmp file

Now import it

DECLARE
hdnl NUMBER;
BEGIN
hdnl := DBMS_DATAPUMP.OPEN( operation => 'IMPORT', job_mode => 'SCHEMA', job_name=>null);
DBMS_DATAPUMP.ADD_FILE( handle => hdnl, filename => 'TC1.dmp', directory => 'DATA_PUMP_DIR', filetype => dbms_datapump.ku$_file_type_dump_file);
DBMS_DATAPUMP.METADATA_FILTER(hdnl,'SCHEMA_EXPR','IN (''KYLELF'')');
DBMS_DATAPUMP.START_JOB(hdnl);
END;
/

This gave me the error

ORA-39001: invalid argument value
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3507
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3756
ORA-06512: at line 5

Not very informative.

 Found an example to get more verbose error messaging on

https://stackoverflow.com/questions/45348688/oracle-datapump-export-error-invalid-argument-value

SET VERIFY OFF
SET FEEDBACK OFF
SET SERVEROUTPUT ON

DECLARE
  ind        NUMBER;
  fileType   NUMBER;
  value      VARCHAR2(2048);
  infoTab    KU$_DUMPFILE_INFO := KU$_DUMPFILE_INFO();
 
BEGIN
  --
  -- Get the information about the dump file into the infoTab.
  --
  BEGIN
    DBMS_DATAPUMP.GET_DUMPFILE_INFO('TC1.dmp','DATA_PUMP_DIR',infoTab,fileType);
    DBMS_OUTPUT.PUT_LINE('---------------------------------------------');
    DBMS_OUTPUT.PUT_LINE('Information for file: TC1.dmp');
 
    --
    -- Determine what type of file is being looked at.
    --
    CASE fileType
      WHEN 1 THEN
        DBMS_OUTPUT.PUT_LINE('TC1.dmp is a Data Pump dump file');
      WHEN 2 THEN
        DBMS_OUTPUT.PUT_LINE('TC1.dmp is an Original Export dump file');
      WHEN 3 THEN
        DBMS_OUTPUT.PUT_LINE('TC1.dmp is an External Table dump file');
      ELSE
        DBMS_OUTPUT.PUT_LINE('TC1.dmp is not a dump file');
        DBMS_OUTPUT.PUT_LINE('---------------------------------------------');
    END CASE;
 
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('---------------------------------------------');
      DBMS_OUTPUT.PUT_LINE('Error retrieving information for file: ' ||
                           'TC1.dmp');
      DBMS_OUTPUT.PUT_LINE(SQLERRM);
      DBMS_OUTPUT.PUT_LINE('---------------------------------------------');
      fileType := 0;
  END;
 
  --
  -- If a valid file type was returned, then loop through the infoTab and 
  -- display each item code and value returned.
  --
  IF fileType > 0
  THEN
    DBMS_OUTPUT.PUT_LINE('The information table has ' || 
                          TO_CHAR(infoTab.COUNT) || ' entries');
    DBMS_OUTPUT.PUT_LINE('---------------------------------------------');
 
    ind := infoTab.FIRST;
    WHILE ind IS NOT NULL
    LOOP
      --
      -- The following item codes return boolean values in the form
      -- of a '1' or a '0'. Display them as 'Yes' or 'No'.
      --
      value := NVL(infoTab(ind).value, 'NULL');
      IF infoTab(ind).item_code IN
         (DBMS_DATAPUMP.KU$_DFHDR_MASTER_PRESENT,
          DBMS_DATAPUMP.KU$_DFHDR_DIRPATH,
          DBMS_DATAPUMP.KU$_DFHDR_METADATA_COMPRESSED,
          DBMS_DATAPUMP.KU$_DFHDR_DATA_COMPRESSED,
          DBMS_DATAPUMP.KU$_DFHDR_METADATA_ENCRYPTED,
          DBMS_DATAPUMP.KU$_DFHDR_DATA_ENCRYPTED,
          DBMS_DATAPUMP.KU$_DFHDR_COLUMNS_ENCRYPTED)
      THEN
        CASE value
          WHEN '1' THEN value := 'Yes';
          WHEN '0' THEN value := 'No';
        END CASE;
      END IF;
 
      --
      -- Display each item code with an appropriate name followed by
      -- its value.
      --
      CASE infoTab(ind).item_code
        --
        -- The following item codes have been available since Oracle
        -- Database 10g, Release 10.2.
        --
        WHEN DBMS_DATAPUMP.KU$_DFHDR_FILE_VERSION   THEN
          DBMS_OUTPUT.PUT_LINE('Dump File Version:         ' || value);
        WHEN DBMS_DATAPUMP.KU$_DFHDR_MASTER_PRESENT THEN
          DBMS_OUTPUT.PUT_LINE('Master Table Present:      ' || value);
        WHEN DBMS_DATAPUMP.KU$_DFHDR_GUID THEN
          DBMS_OUTPUT.PUT_LINE('Job Guid:                  ' || value);
        WHEN DBMS_DATAPUMP.KU$_DFHDR_FILE_NUMBER THEN
          DBMS_OUTPUT.PUT_LINE('Dump File Number:          ' || value);
        WHEN DBMS_DATAPUMP.KU$_DFHDR_CHARSET_ID  THEN
          DBMS_OUTPUT.PUT_LINE('Character Set ID:          ' || value);
        WHEN DBMS_DATAPUMP.KU$_DFHDR_CREATION_DATE THEN
          DBMS_OUTPUT.PUT_LINE('Creation Date:             ' || value);
        WHEN DBMS_DATAPUMP.KU$_DFHDR_FLAGS THEN
          DBMS_OUTPUT.PUT_LINE('Internal Dump Flags:       ' || value);
        WHEN DBMS_DATAPUMP.KU$_DFHDR_JOB_NAME THEN
          DBMS_OUTPUT.PUT_LINE('Job Name:                  ' || value);
        WHEN DBMS_DATAPUMP.KU$_DFHDR_PLATFORM THEN
          DBMS_OUTPUT.PUT_LINE('Platform Name:             ' || value);
        WHEN DBMS_DATAPUMP.KU$_DFHDR_INSTANCE THEN
          DBMS_OUTPUT.PUT_LINE('Instance Name:             ' || value);
        WHEN DBMS_DATAPUMP.KU$_DFHDR_LANGUAGE THEN
          DBMS_OUTPUT.PUT_LINE('Language Name:             ' || value);
        WHEN DBMS_DATAPUMP.KU$_DFHDR_BLOCKSIZE THEN
          DBMS_OUTPUT.PUT_LINE('Dump File Block Size:      ' || value);
        WHEN DBMS_DATAPUMP.KU$_DFHDR_DIRPATH THEN
          DBMS_OUTPUT.PUT_LINE('Direct Path Mode:          ' || value);
        WHEN DBMS_DATAPUMP.KU$_DFHDR_METADATA_COMPRESSED THEN
          DBMS_OUTPUT.PUT_LINE('Metadata Compressed:       ' || value);
        WHEN DBMS_DATAPUMP.KU$_DFHDR_DB_VERSION THEN
          DBMS_OUTPUT.PUT_LINE('Database Version:          ' || value);
 
        --
        -- The following item codes were introduced in Oracle Database 11g
        -- Release 11.1
        --

        WHEN DBMS_DATAPUMP.KU$_DFHDR_MASTER_PIECE_COUNT THEN
          DBMS_OUTPUT.PUT_LINE('Master Table Piece Count:  ' || value);
        WHEN DBMS_DATAPUMP.KU$_DFHDR_MASTER_PIECE_NUMBER THEN
          DBMS_OUTPUT.PUT_LINE('Master Table Piece Number: ' || value);
        WHEN DBMS_DATAPUMP.KU$_DFHDR_DATA_COMPRESSED THEN
          DBMS_OUTPUT.PUT_LINE('Table Data Compressed:     ' || value);
        WHEN DBMS_DATAPUMP.KU$_DFHDR_METADATA_ENCRYPTED THEN
          DBMS_OUTPUT.PUT_LINE('Metadata Encrypted:        ' || value);
        WHEN DBMS_DATAPUMP.KU$_DFHDR_DATA_ENCRYPTED THEN
          DBMS_OUTPUT.PUT_LINE('Table Data Encrypted:      ' || value);
        WHEN DBMS_DATAPUMP.KU$_DFHDR_COLUMNS_ENCRYPTED THEN
          DBMS_OUTPUT.PUT_LINE('TDE Columns Encrypted:     ' || value);
 
        --
        -- For the DBMS_DATAPUMP.KU$_DFHDR_ENCRYPTION_MODE item code a
        -- numeric value is returned. So examine that numeric value
        -- and display an appropriate name value for it.
        --
        WHEN DBMS_DATAPUMP.KU$_DFHDR_ENCRYPTION_MODE THEN
          CASE TO_NUMBER(value)
            WHEN DBMS_DATAPUMP.KU$_DFHDR_ENCMODE_NONE THEN
              DBMS_OUTPUT.PUT_LINE('Encryption Mode:           None');
            WHEN DBMS_DATAPUMP.KU$_DFHDR_ENCMODE_PASSWORD THEN
              DBMS_OUTPUT.PUT_LINE('Encryption Mode:           Password');
            WHEN DBMS_DATAPUMP.KU$_DFHDR_ENCMODE_DUAL THEN
              DBMS_OUTPUT.PUT_LINE('Encryption Mode:           Dual');
            WHEN DBMS_DATAPUMP.KU$_DFHDR_ENCMODE_TRANS THEN
              DBMS_OUTPUT.PUT_LINE('Encryption Mode:           Transparent');
          END CASE;
 
        --
        -- The following item codes were introduced in Oracle Database 12c
        -- Release 12.1
        --
 
        --
        -- For the DBMS_DATAPUMP.KU$_DFHDR_COMPRESSION_ALG item code a
        -- numeric value is returned. So examine that numeric value and
        -- display an appropriate name value for it.
        --
        
      END CASE;
      ind := infoTab.NEXT(ind);
    END LOOP;
  END IF;
END;
/

Now I can see the error better:

39001: ORA-39001: invalid argument value
-39000: ORA-39000: bad dump file specification
-39143: ORA-39143: dump file "/rdsdbdata/datapump/TC1.dmp" may be an original
export dump file

 

Looking this up via google, indicates that this is an old “exp” dump file and is not compatible with “impdp”

Thus have use old imp tool

Off of OTN

https://www.oracle.com/database/technologies/instant-client/linux-x86-64-downloads.html

I got

oracle-instantclient19.6-basic-19.6.0.0.0-1.x86_64.rpm
oracle-instantclient19.6-sqlplus-19.6.0.0.0-1.x86_64.rpm
oracle-instantclient19.6-tools-19.6.0.0.0-1.x86_64.rpm

Installed these on an EC2

sudo rpm -ivh

sudo rpm -ivh oracle-instantclient19.6-basic-19.6.0.0.0-1.x86_64.rpm
sudo rpm -ivh oracle-instantclient19.6-sqlplus-19.6.0.0.0-1.x86_64.rpm
sudo rpm -ivh oracle-instantclient19.6-tools-19.6.0.0.0-1.x86_64.rpm

Then copy the TC1.dmp file to the EC2

Then use TWO_TASK to point to my database

export TWO_TASK='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=ora19.xxxxxxxx.us-east-1.rds.amazonaws.com)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl)))‘

/usr/lib/oracle/19.6/client64/bin/imp user/pw file=TC1.dmp FROMUSER=’scott’ TOUSER=’kylelf’

also had a hickup because the first database I used was 11.2 and the imp is from 19.0 distribution so got error

 

IIMP-00003: ORACLE error 942 encountered
ORA-00942: table or view does not exist
IMP-00023: Import views not installed, please notify your DBA
IMP-00000: Import terminated unsuccessfully

So bad to create a version 19 Oracle database and then the import worked

 

Uncategorized

Index Advisor ? PostgreSQL, Oracle and SQL Server : yes , MySQL : no

January 30th, 2020

How does one check if there are missing indexes in a database?

Is there a programatic way to do it?

Explain Plan
Most blogs on the subject will say look at the explain plan for your query.
Basic concept is to look for full table scans in the explain plan and the look and see if there is a predicate filter (i.e. SQL text of form “where table.field=value”) with a full table scan or a full table scan in a join ( i.e. tableA.field = tableB.field). There are other considerations, things to check, and other places for indexes, but this is a good start.

Explain Plans for whole database
What if you don’t have a particular query and instead want to check the whole running database?
Is there a programatic way to do it? If the answer to finding missing indexes for one query is looking at the explain plan then by extension the answer for the whole database is looking at the explain plans for the whole database.

Execution Plan Cache
On Oracle and SQL Server the databases have a cache of execution plans (the plans actually used by the optimizer as opposed to the plan the optimizer thinks it would use the “explain” plan. Execution plans are from executions and explain plans from the explain command without executing the statement. They should be the same but for many reason the might be different).
Thus on Oracle (V$SQL_PLAN) and SQL Server ( sys.dm_exec_query_plan) the execution plans can be programmatically queried and analyzed.

Explain plan on SQL from SQL cache
What about PostgreSQL and MySQL. Neither have a query plan cache.
Both PostgreSQL (pg_stat_statements) and MySQL (events_statements_history) do have the text for executed queries. With the SQL queries one can programmatically collect the SQL queries and run the “explain” command and get the explain plan.
Another issue is getting the full SQL text. Without the full SQL text, the Explain plan can’t be run. On MySQL the SQL text length is limited by default to 1K but can be adjusted with parameter performance_schema_max_sql_text_length system variable at server startup. Postgres is also limited to 1K by default can be increased with the parameter track_activity_query_size.

Explain Analyze
For select statements (i.e. queries that don’t modify data) one could even consider running “explain analyze” which both PostgreSQL and MySQL have. Explain Analyze which runs the query to give the execution plan as well as the estimated costs and the actual execution statistics which is useful as we will discuss later.

Explain on SQL with bind variables
Unfortunately the Explain command breaks with SQL that have variable parameters. In order to get Explain to work requires substituting real values for variables.

Bind variable capture
Bind variables can be captured on PostgreSQL with extensions like PG_qualstats. PG_qualstats also captures other information that is perfect for finding missing indexes.
Bind variable capture is not possible on MySQL even with slow query log.

So on MySQL without execution plans caches and without bind variables we can’t programatic index advisor on MySQL.

Uncategorized

SQLcmd on my Mac: Seg fault

January 23rd, 2020

trying to connect to SQL Server from my Mac so install sqlcmd:

brew tap microsoft/mssql-release https://github.com/Microsoft/homebrew-mssql-release
brew update
brew install mssql-tools

then get error

$ sqlcmd -S kylelfsqls.xxxxxx.us-west-2.amazonaws.com,1433 -U kylelf  
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : SSL Provider: [OpenSSL library could not be loaded, make sure OpenSSL 1.0 or 1.1 is installed].
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Client unable to establish connection.
Segmentation fault: 11

Did’t find much info google, so blogging here.
Ran

ln -s /usr/local/opt/openssl@1.1 /usr/local/opt/openssl

and it worked

Uncategorized

MySQL – manually updating table and index optimizer statistics

January 22nd, 2020

Goal was to create a empty table and poplulate it with optimizer table and index stats from a poplulated table to try and get the optimizer to give the same explain plan and costs on the empty table as the populated table. The purpose of this was to be able to modify the empty table by adding indexes quickly and cheaply and still be able to get correct optimizer costs. This worked in the case of table access but unfortunately didn’t work when bring in an index.

Create procedure to populate test table

DROP PROCEDURE IF EXISTS populate_data;
DELIMITER //
CREATE PROCEDURE populate_data(max_val int)
BEGIN
DECLARE int_val INT DEFAULT 0;
test_loop : LOOP
IF (int_val = max_val) THEN
LEAVE test_loop;
END IF;
INSERT INTO source(id) VALUES (FLOOR(RAND()*(1000000+1)+1));
SET int_val = int_val +1;
END LOOP;
END;//
DELIMITER ;

Create source table and populate it.
Create source_clone that will be empty but we want to manually fill in optimizer statistics to mimic source table

CREATE TABLE source(id integer);
call populate_data(1000000);
CREATE TABLE source_clone (id integer) ;

comparing table and index stats on source and source_clone we can see as expected they are different:

select * from mysql.innodb_table_stats where table_name like 'sourc%';
+---------------+--------------+---------------------+--------+----------------------+--------------------------+
| database_name | table_name   | last_update         | n_rows | clustered_index_size | sum_of_other_index_sizes |
+---------------+--------------+---------------------+--------+----------------------+--------------------------+
| kyle          | source       | 2020-01-22 18:49:48 |   9980 |                   21 |                        0 |
| kyle          | source_clone | 2020-01-22 18:49:44 |      0 |                    1 |                        0 |
+---------------+--------------+---------------------+--------+----------------------+--------------------------+
select * from mysql.innodb_index_stats where table_name like 'sourc%';
+---------------+--------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+
| database_name | table_name   | index_name      | last_update         | stat_name    | stat_value | sample_size | stat_description                  |
+---------------+--------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+
| kyle          | source       | GEN_CLUST_INDEX | 2020-01-22 18:49:48 | n_diff_pfx01 |       9980 |          20 | DB_ROW_ID                         |
| kyle          | source       | GEN_CLUST_INDEX | 2020-01-22 18:49:48 | n_leaf_pages |         20 |        NULL | Number of leaf pages in the index |
| kyle          | source       | GEN_CLUST_INDEX | 2020-01-22 18:49:48 | size         |         21 |        NULL | Number of pages in the index      |
| kyle          | source_clone | GEN_CLUST_INDEX | 2020-01-22 18:49:44 | n_diff_pfx01 |          0 |           1 | DB_ROW_ID                         |
| kyle          | source_clone | GEN_CLUST_INDEX | 2020-01-22 18:49:44 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| kyle          | source_clone | GEN_CLUST_INDEX | 2020-01-22 18:49:44 | size         |          1 |        NULL | Number of pages in the index      |
+---------------+--------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+

running the explain plan we get different rows returned as expected

--
mysql> explain select id from kyle.source where id > 2056;
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | source | ALL  | NULL          | NULL | NULL    | NULL | 9980 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.05 sec)

mysql> explain select id from kyle.source_clone where id > 2056;
+----+-------------+--------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table        | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | source_clone | ALL  | NULL          | NULL | NULL    | NULL |    1 | Using where |
+----+-------------+--------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.04 sec)

Now let’s copy the stats from the populated source table to the empty source_clone data

-- TABLE  STATS
update mysql.innodb_table_stats AS `dest`,
     (SELECT  n_rows , clustered_index_size 
     FROM mysql.innodb_table_stats 
     WHERE 
         table_name = 'source'
    ) AS `src`
SET dest.n_rows  = src.n_rows , 
    dest.clustered_index_size  = src.clustered_index_size
WHERE 
         table_name = 'source_clone';
-- INDEX STATS
UPDATE mysql.innodb_index_stats AS `dest`,
(SELECT stat_value, stat_name
FROM mysql.innodb_index_stats
WHERE
table_name = 'source'
and index_name = 'GEN_CLUST_INDEX'
) AS `src`
SET dest.stat_value = src.stat_value
WHERE dest.table_name = 'source_clone'
and dest.index_name = 'GEN_CLUST_INDEX'
and dest.stat_name = src.stat_name;

The optimizer table and index statistics are now the same

mysql> select * from mysql.innodb_table_stats where table_name like 'sourc%';
+---------------+--------------+---------------------+--------+----------------------+--------------------------+
| database_name | table_name   | last_update         | n_rows | clustered_index_size | sum_of_other_index_sizes |
+---------------+--------------+---------------------+--------+----------------------+--------------------------+
| kyle          | source       | 2020-01-22 18:49:48 |   9980 |                   21 |                        0 |
| kyle          | source_clone | 2020-01-22 18:54:31 |   9980 |                   21 |                        0 |
+---------------+--------------+---------------------+--------+----------------------+--------------------------+
--
mysql> select * from mysql.innodb_index_stats where table_name like 'sourc%';
+---------------+--------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+
| database_name | table_name   | index_name      | last_update         | stat_name    | stat_value | sample_size | stat_description                  |
+---------------+--------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+
| kyle          | source       | GEN_CLUST_INDEX | 2020-01-22 18:49:48 | n_diff_pfx01 |       9980 |          20 | DB_ROW_ID                         |
| kyle          | source       | GEN_CLUST_INDEX | 2020-01-22 18:49:48 | n_leaf_pages |         20 |        NULL | Number of leaf pages in the index |
| kyle          | source       | GEN_CLUST_INDEX | 2020-01-22 18:49:48 | size         |         21 |        NULL | Number of pages in the index      |
| kyle          | source_clone | GEN_CLUST_INDEX | 2020-01-22 18:54:46 | n_diff_pfx01 |       9980 |           1 | DB_ROW_ID                         |
| kyle          | source_clone | GEN_CLUST_INDEX | 2020-01-22 18:54:46 | n_leaf_pages |         20 |        NULL | Number of leaf pages in the index |
| kyle          | source_clone | GEN_CLUST_INDEX | 2020-01-22 18:54:46 | size         |         21 |        NULL | Number of pages in the index      |
+---------------+--------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+

Running explain we are still getting different rows returned on the source clone

--
mysql> explain select id from kyle.source_clone where id > 2056;
+----+-------------+--------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table        | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | source_clone | ALL  | NULL          | NULL | NULL    | NULL |    1 | Using where |
+----+-------------+--------------+------+---------------+------+---------+------+------+-------------+

But by doing a flush table on the source_clone we not get the same rows returned in the explain as the source

 FLUSH TABLE source_clone;
--
mysql> explain select id from kyle.source_clone where id > 2056;
+----+-------------+--------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table        | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | source_clone | ALL  | NULL          | NULL | NULL    | NULL | 9980 | Using where |
+----+-------------+--------------+------+---------------+------+---------+------+------+-------------+

Now let’s create an index and try to manually update the optimizer index stats

CREATE INDEX source_id ON source (id);
CREATE INDEX source_clone_id ON source_clone (id);
--
mysql> explain select id from kyle.source where id > 2056;
+----+-------------+--------+-------+---------------+-----------+---------+------+------+--------------------------+
| id | select_type | table  | type  | possible_keys | key       | key_len | ref  | rows | Extra                    |
+----+-------------+--------+-------+---------------+-----------+---------+------+------+--------------------------+
|  1 | SIMPLE      | source | range | source_id     | source_id | 5       | NULL | 4990 | Using where; Using index |
+----+-------------+--------+-------+---------------+-----------+---------+------+------+--------------------------+
--
mysql> explain select id from kyle.source_clone where id > 2056;
+----+-------------+--------------+-------+-----------------+-----------------+---------+------+------+--------------------------+
| id | select_type | table        | type  | possible_keys   | key             | key_len | ref  | rows | Extra                    |
+----+-------------+--------------+-------+-----------------+-----------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | source_clone | range | source_clone_id | source_clone_id | 5       | NULL |    1 | Using where; Using index |
+----+-------------+--------------+-------+-----------------+-----------------+---------+------+------+--------------------------+
--
UPDATE mysql.innodb_index_stats AS `dest`,
(SELECT stat_value, stat_name
FROM mysql.innodb_index_stats
WHERE
table_name = 'source'
and index_name = 'source_id'
) AS `src`
SET dest.stat_value = src.stat_value
WHERE dest.table_name = 'source_clone'
and dest.index_name = 'source_clone_id'
and dest.stat_name = src.stat_name;
--
mysql> select * from mysql.innodb_index_stats where table_name like 'sourc%';
+---------------+--------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+
| database_name | table_name   | index_name      | last_update         | stat_name    | stat_value | sample_size | stat_description                  |
+---------------+--------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+
| kyle          | source       | GEN_CLUST_INDEX | 2020-01-22 18:56:46 | n_diff_pfx01 |       9980 |          20 | DB_ROW_ID                         |
| kyle          | source       | GEN_CLUST_INDEX | 2020-01-22 18:56:46 | n_leaf_pages |         20 |        NULL | Number of leaf pages in the index |
| kyle          | source       | GEN_CLUST_INDEX | 2020-01-22 18:56:46 | size         |         21 |        NULL | Number of pages in the index      |
| kyle          | source       | source_id       | 2020-01-22 18:56:46 | n_diff_pfx01 |       9950 |          11 | id                                |
| kyle          | source       | source_id       | 2020-01-22 18:56:46 | n_diff_pfx02 |      10000 |          11 | id,DB_ROW_ID                      |
| kyle          | source       | source_id       | 2020-01-22 18:56:46 | n_leaf_pages |         11 |        NULL | Number of leaf pages in the index |
| kyle          | source       | source_id       | 2020-01-22 18:56:46 | size         |         12 |        NULL | Number of pages in the index      |
| kyle          | source_clone | GEN_CLUST_INDEX | 2020-01-22 18:56:46 | n_diff_pfx01 |       9980 |           1 | DB_ROW_ID                         |
| kyle          | source_clone | GEN_CLUST_INDEX | 2020-01-22 18:56:46 | n_leaf_pages |         20 |        NULL | Number of leaf pages in the index |
| kyle          | source_clone | GEN_CLUST_INDEX | 2020-01-22 18:56:46 | size         |         21 |        NULL | Number of pages in the index      |
| kyle          | source_clone | source_clone_id | 2020-01-22 18:57:38 | n_diff_pfx01 |       9950 |           1 | id                                |
| kyle          | source_clone | source_clone_id | 2020-01-22 18:57:38 | n_diff_pfx02 |      10000 |           1 | id,DB_ROW_ID                      |
| kyle          | source_clone | source_clone_id | 2020-01-22 18:57:38 | n_leaf_pages |         11 |        NULL | Number of leaf pages in the index |
| kyle          | source_clone | source_clone_id | 2020-01-22 18:57:38 | size         |         12 |        NULL | Number of pages in the index      |
+---------------+--------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+
--
mysql> explain select id from kyle.source_clone where id > 2056;
+----+-------------+--------------+-------+-----------------+-----------------+---------+------+------+--------------------------+
| id | select_type | table        | type  | possible_keys   | key             | key_len | ref  | rows | Extra                    |
+----+-------------+--------------+-------+-----------------+-----------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | source_clone | range | source_clone_id | source_clone_id | 5       | NULL |    1 | Using where; Using index |
+----+-------------+--------------+-------+-----------------+-----------------+---------+------+------+--------------------------+
--
 FLUSH TABLE source_clone;
--
mysql> explain select id from kyle.source_clone where id > 2056;
+----+-------------+--------------+-------+-----------------+-----------------+---------+------+------+--------------------------+
| id | select_type | table        | type  | possible_keys   | key             | key_len | ref  | rows | Extra                    |
+----+-------------+--------------+-------+-----------------+-----------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | source_clone | range | source_clone_id | source_clone_id | 5       | NULL |    1 | Using where; Using index |
+----+-------------+--------------+-------+-----------------+-----------------+---------+------+------+--------------------------+
--
Flush tables;
--
mysql> explain select id from kyle.source_clone where id > 2056;
+----+-------------+--------------+-------+-----------------+-----------------+---------+------+------+--------------------------+
| id | select_type | table        | type  | possible_keys   | key             | key_len | ref  | rows | Extra                    |
+----+-------------+--------------+-------+-----------------+-----------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | source_clone | range | source_clone_id | source_clone_id | 5       | NULL |    1 | Using where; Using index |
+----+-------------+--------------+-------+-----------------+-----------------+---------+------+------+--------------------------+
-- REBOOT database
mysql> explain select id from kyle.source_clone where id > 2056;
+----+-------------+--------------+-------+-----------------+-----------------+---------+------+------+--------------------------+
| id | select_type | table        | type  | possible_keys   | key             | key_len | ref  | rows | Extra                    |
+----+-------------+--------------+-------+-----------------+-----------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | source_clone | range | source_clone_id | source_clone_id | 5       | NULL |    1 | Using where; Using index |
+----+-------------+--------------+-------+-----------------+-----------------+---------+------+------+--------------------------+

The optimizer index stats are the same, we have flushed the tables, and even did a reboot of the database, but the explain plan still shows different results on the source_clone.
The optimizer must be basing costs in this case on things other than mysql.innodb_index_stats and mysql.innodb_table_stats
 

 

Uncategorized

Oracle: compare DB Time and CPU time to ASH

April 26th, 2019

 

 

Good reference on subject: https://blog.orapub.com/20150210/what-is-light-green-oracle-database-cpu-wait-time.html
Screen Shot 2019-04-26 at 1.31.49 PM

Below I’m comparing AAS from ASH verses SYSMETRICs specificially

  • ASH CPU in AAS vs SYSMETRIC CPU in AAS
  • ASH total AAS vs SYSMETRIC  total AAS derived from DB TIME

ASH CPU is consistently lower that SYSMETRIC  CPU and that could be because waits burn CPU but show up as waits and not CPU in ASH.

On the other hand, not sure why total AAS from SYSMETRIC  is consistently smaller than total AAS from ASH.

  Def v_secs=60 --  bucket size

select   DBTT.begin_time ,ASH_CPU, DBT_CPU , ASH_AAS,  DBT_AAS from
(
    select to_char(begin_time,'MON DD YYYY HH24:MI') begin_time,
          round(value/100,2) DBT_AAS
    from v$sysmetric_history 
    where metric_name='Database Time Per Sec'
    and INTSIZE_CSEC > 2000
    order by 1
)  DBTT,
(
    select to_char(begin_time,'MON DD YYYY HH24:MI') begin_time,
          round(value/100,2) DBT_CPU
    from v$sysmetric_history 
    where metric_name='CPU Usage Per Sec'
)  DBTC,
(
   select
        to_char(to_date(
                         trunc((id*&v_secs)/ (24*60*60)) || ' ' ||  -- Julian days
                           mod((id*&v_secs),  24*60*60)             -- seconds in the day
                , 'J SSSSS' ), 'MON DD YYYY HH24:MI')     start_time,
        round(CPU/&v_secs,2) ASH_CPU,
        round(total/&v_secs,2)  ASH_AAS
   from ( 
     select
        trunc((to_char(sample_time,'J')*(24*60*60)+to_char(sample_time,'SSSSS'))/&v_secs)  id,
        sum(decode(session_state,'ON CPU',1,0))     CPU,
        sum(decode(session_state,'ON CPU',0,1))     Wait,
       --  decode(session_state,'ON CPU','ON CPU','WAIT')     event,
        count(*) total
     from
        v$active_session_history ash
     where SAMPLE_TIME > sysdate - 60/(24*60)
     group by
         trunc((to_char(sample_time,'J')*(24*60*60)+to_char(sample_time,'SSSSS'))/&v_secs)
        )
) AAS
where
     DBTT.begin_time=DBTC.begin_time
 and DBTT.begin_time=AAS.start_time (+)
order by DBTT.begin_time ;

Low load – data looks pretty comparable:

BEGIN_TIME		      ASH_CPU	 DBT_CPU    ASH_AAS    DBT_AAS
-------------------------- ---------- ---------- ---------- ----------
APR 26 2019 18:24			       0		     0
APR 26 2019 18:25		  .02	       0	.02	     0
APR 26 2019 18:26			       0		     0
APR 26 2019 18:27			       0		     0
APR 26 2019 18:28			       0		     0
APR 26 2019 18:29		  .03	       0	.03	     0
APR 26 2019 18:30			       0		     0
APR 26 2019 18:31			       0		     0
APR 26 2019 18:32		    0	       0	 .8	     0
APR 26 2019 18:33		    0	       0	  1	     0
APR 26 2019 18:34		  .02	       0	.05	     0
APR 26 2019 18:35		  .22	     .21	.22	   .21
APR 26 2019 18:36			       0		     0
APR 26 2019 18:37		  .22	     .21	.22	   .21
APR 26 2019 18:38		  .02	       0	.02	     0
APR 26 2019 18:39			       0		     0
APR 26 2019 18:40			       0		     0

Higher load – we start to see consistent differences :

BEGIN_TIME		      ASH_CPU	 DBT_CPU    ASH_AAS    DBT_AAS
-------------------------- ---------- ---------- ---------- ----------
APR 26 2019 18:56		  .52	     .85	3.7	  2.54
APR 26 2019 18:57		   .4	     .62       2.95	  1.96
APR 26 2019 18:58		  .23	     .39       1.73	   1.2
APR 26 2019 18:59		  .17	      .4       1.32	  1.27
APR 26 2019 19:00		  .45	     .85       3.17	  2.51
APR 26 2019 19:01		  .53	     .81	3.2	  2.57
APR 26 2019 19:02		  .33	      .4       2.18	  1.28
APR 26 2019 19:03		   .2	     .39	.87	  1.24
APR 26 2019 19:04		  .28	      .4       1.88	  1.17
APR 26 2019 19:05		  .43	     .81       3.12	  2.56
APR 26 2019 19:06		  .48	     .81       3.33	  2.36
APR 26 2019 19:07		   .3	     .41       1.43	  1.33
APR 26 2019 19:08		  .43	      .4	2.2	   1.2
APR 26 2019 19:09		  .13	      .4       1.43	  1.29
APR 26 2019 19:10		  .48	     .87       3.48	  2.49
APR 26 2019 19:11		  .48	     .72       3.03	  2.25
APR 26 2019 19:12		  .27	     .39       2.03	   1.2

 

The screen shot below reproduces much of the above except DB Time and CPU used by this Session come from v$systat and not SYSMETRICs and in this case DB TIME is showing a higher AAS than ASH whereas it is the other way around for DB TIME from SYSMETRICs.

 

Screen Shot 2019-04-26 at 12.28.06 PM

John Beresniewicz pointed out that DB Time doesn’t include BACKGROUND processes, so here is slight update just filtering for FOREGROUND processes

 

  Def v_secs=60 --  bucket size

select   DBTT.begin_time ,ASH_CPU, DBT_CPU , ASH_AAS,  DBT_AAS from
(
    select to_char(begin_time,'MON DD YYYY HH24:MI') begin_time,
          round(value/100,2) DBT_AAS
    from v$sysmetric_history
    where metric_name='Database Time Per Sec'
    and INTSIZE_CSEC > 2000
    order by 1
)  DBTT,
(
    select to_char(begin_time,'MON DD YYYY HH24:MI') begin_time,
          round(value/100,2) DBT_CPU
    from v$sysmetric_history
    where metric_name='CPU Usage Per Sec'
)  DBTC,
(
   select
        to_char(to_date(
                         trunc((id*&v_secs)/ (24*60*60)) || ' ' ||  -- Julian days
                           mod((id*&v_secs),  24*60*60)             -- seconds in the day
                , 'J SSSSS' ), 'MON DD YYYY HH24:MI')     start_time,
        round(CPU/&v_secs,2) ASH_CPU,
        round(total/&v_secs,2)  ASH_AAS
   from (
     select
        trunc((to_char(sample_time,'J')*(24*60*60)+to_char(sample_time,'SSSSS'))/&v_secs)  id,
        sum(decode(session_state,'ON CPU',1,0))     CPU,
        sum(decode(session_state,'ON CPU',0,1))     Wait,
       --  decode(session_state,'ON CPU','ON CPU','WAIT')     event,
        count(*) total
     from
        v$active_session_history ash
     where SAMPLE_TIME > sysdate - 60/(24*60)
        and session_type = 'FOREGROUND'
     group by
         trunc((to_char(sample_time,'J')*(24*60*60)+to_char(sample_time,'SSSSS'))/&v_secs)
        )
) AAS
where
     DBTT.begin_time=DBTC.begin_time
 and DBTT.begin_time=AAS.start_time (+)
order by DBTT.begin_time ;

and the output is much closer to being inline

BEGIN_TIME		      ASH_CPU	 DBT_CPU    ASH_AAS    DBT_AAS
-------------------------- ---------- ---------- ---------- ----------
APR 27 2019 01:25			       0		     0
APR 27 2019 01:26			       0		     0
APR 27 2019 01:27			       0		     0
APR 27 2019 01:28			       0		     0
APR 27 2019 01:29		  .02	     .01	.02	   .02
APR 27 2019 01:30		    0	     .15	.05	   .42
APR 27 2019 01:31		  .35	     .83	2.8	  2.56
APR 27 2019 01:32		  .55	     .79       2.62	  2.38
APR 27 2019 01:33		  .17	     .41       1.67	  1.31
APR 27 2019 01:34		   .3	     .42       1.37	  1.28
APR 27 2019 01:35		  .48	     .78	2.8	  2.61

BEGIN_TIME		      ASH_CPU	 DBT_CPU    ASH_AAS    DBT_AAS
-------------------------- ---------- ---------- ---------- ----------
APR 27 2019 01:36		  .42	     .84	2.6	  2.56
APR 27 2019 01:37		   .3	     .58       1.88	  1.87
APR 27 2019 01:38		  .23	     .41       1.48	  1.25
APR 27 2019 01:39		  .23	     .41       1.57	  1.32
APR 27 2019 01:40		  .43	     .83       2.82	  2.53
APR 27 2019 01:41		  .55	      .8       2.67	  2.52
APR 27 2019 01:42		   .3	     .41       1.62	  1.28
APR 27 2019 01:43		  .32	      .4	1.1	  1.27
APR 27 2019 01:44		  .25	      .4       1.17	  1.19
APR 27 2019 01:45		  .52	     .79       2.38	  2.55
APR 27 2019 01:46		   .3	      .8       2.23	  2.39

BEGIN_TIME		      ASH_CPU	 DBT_CPU    ASH_AAS    DBT_AAS
-------------------------- ---------- ---------- ---------- ----------
APR 27 2019 01:47		  .25	      .4       1.67	  1.32
APR 27 2019 01:48		  .32	     .41       1.47	  1.18
APR 27 2019 01:49		  .27	     .42	1.4	  1.34
APR 27 2019 01:50		  .42	     .86       2.65	  2.51
APR 27 2019 01:51		   .4	     .76       2.57	   2.4
APR 27 2019 01:52		  .18	     .41	1.8	  1.32
APR 27 2019 01:53		  .25	     .41       1.58	  1.32
APR 27 2019 01:54		   .2	     .41       1.35	  1.21
APR 27 2019 01:55		  .42	      .8       2.28	  2.56
APR 27 2019 01:56		   .4	     .72       2.55	   2.2
APR 27 2019 01:57		  .23	     .41       1.32	  1.34

BEGIN_TIME		      ASH_CPU	 DBT_CPU    ASH_AAS    DBT_AAS
-------------------------- ---------- ---------- ---------- ----------
APR 27 2019 01:58		  .25	     .41       1.18	  1.19
APR 27 2019 01:59		   .2	     .51       1.47	  1.87
APR 27 2019 02:00		  1.3	     1.5       4.05	  4.19
APR 27 2019 02:01		 1.12	    1.34       3.15	  3.14
APR 27 2019 02:02		  .27	     .43       1.45	  1.26
APR 27 2019 02:03		  .25	     .42       1.02	   1.3
APR 27 2019 02:04		  .17	     .42       1.37	  1.21
APR 27 2019 02:05		  .47	     .82       2.17	  2.49
APR 27 2019 02:06		  .42	     .78	2.6	   2.3
APR 27 2019 02:07		  .17	     .41       1.15	  1.44
APR 27 2019 02:08		  .22	     .42       1.75	  1.22

BEGIN_TIME		      ASH_CPU	 DBT_CPU    ASH_AAS    DBT_AAS
-------------------------- ---------- ---------- ---------- ----------
APR 27 2019 02:09		   .2	     .42       1.33	  1.31
APR 27 2019 02:10		  .32	     .85       2.37	  2.53
APR 27 2019 02:11		  .52	     .74       2.38	  2.28
APR 27 2019 02:12		  .22	     .41       1.78	  1.25
APR 27 2019 02:13		  .28	     .41       1.27	  1.27
APR 27 2019 02:14		  .27	     .42       1.48	  1.19
APR 27 2019 02:15		  .35	     .82       2.37	  2.53
APR 27 2019 02:16		  .32	     .75       2.85	  2.17
APR 27 2019 02:17		  .17	      .4	.87	  1.35
APR 27 2019 02:18		  .25	     .43       1.68	  1.21
APR 27 2019 02:19		  .22	     .42       1.57	  1.32

BEGIN_TIME		      ASH_CPU	 DBT_CPU    ASH_AAS    DBT_AAS
-------------------------- ---------- ---------- ---------- ----------
APR 27 2019 02:20		  .47	     .88       2.42	   2.5
APR 27 2019 02:21		  .37	     .71       2.27	  2.18
APR 27 2019 02:22		   .2	     .42       1.47	  1.27
APR 27 2019 02:23		  .28	     .41	1.2	  1.28
APR 27 2019 02:24		  .17	     .43       1.27	  1.25
APR 27 2019 02:25		  .47	     .82       2.05	  2.54

Now we can see it's much closer

 

Screen Shot 2019-04-26 at 8.36.24 PM

 

AWR from load period

WORKLOAD REPOSITORY report for

DB Name         DB Id    Instance     Inst Num Startup Time    Release     RAC
------------ ----------- ------------ -------- --------------- ----------- ---
ORCL          1534324168 ORCL                1 25-Apr-19 22:07 11.2.0.4.0  NO

Host Name        Platform                         CPUs Cores Sockets Memory(GB)
---------------- -------------------------------- ---- ----- ------- ----------
ip-10-13-0-252   Linux x86 64-bit                    2     1       1      15.38

              Snap Id      Snap Time      Sessions Curs/Sess
            --------- ------------------- -------- ---------
Begin Snap:        23 26-Apr-19 21:00:37        34       1.0
  End Snap:        24 26-Apr-19 22:00:04        33       1.0
   Elapsed:               59.46 (mins)
   DB Time:               98.53 (mins)

Load Profile                    Per Second   Per Transaction  Per Exec  Per Call
~~~~~~~~~~~~~~~            ---------------   --------------- --------- ---------
             DB Time(s):               1.7               0.0      0.00      0.00
              DB CPU(s):               0.6               0.0      0.00      0.00
      Redo size (bytes):       4,762,632.4           4,196.8
  Logical read (blocks):          57,271.0              50.5
          Block changes:          36,004.1              31.7
 Physical read (blocks):               0.2               0.0
Physical write (blocks):             239.9               0.2
       Read IO requests:               0.2               0.0
      Write IO requests:              70.5               0.1
           Read IO (MB):               0.0               0.0
          Write IO (MB):               1.9               0.0
             User calls:           3,746.0               3.3
           Parses (SQL):           1,872.4               1.7
      Hard parses (SQL):               0.0               0.0
     SQL Work Area (MB):               1.3               0.0
                 Logons:               0.0               0.0
         Executes (SQL):           1,895.9               1.7
              Rollbacks:               0.0               0.0
           Transactions:           1,134.8

Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
            Buffer Nowait %:   97.86       Redo NoWait %:  100.00
            Buffer  Hit   %:  100.00    In-memory Sort %:  100.00
            Library Hit   %:  101.55        Soft Parse %:  100.00
         Execute to Parse %:    1.24         Latch Hit %:   99.22
Parse CPU to Parse Elapsd %:    0.20     % Non-Parse CPU:  100.00

Top 10 Foreground Events by Total Wait Time
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                                            Tota    Wait   % DB
Event                                 Waits Time Avg(ms)   time Wait Class
------------------------------ ------------ ---- ------- ------ ----------
log file sync                     4,048,143 3688       1   62.4 Commit
DB CPU                                      1947           32.9
buffer busy waits                 2,395,899 341.       0    5.8 Concurrenc
enq: TX - row lock contention       412,330 71.6       0    1.2 Applicatio
latch: cache buffers chains           9,514  3.8       0     .1 Concurrenc
log file switch (checkpoint in           26  3.5     135     .1 Configurat
SQL*Net message to client         6,685,887  2.9       0     .0 Network
enq: SQ - contention                 11,266  2.4       0     .0 Configurat
log file switch completion              335  2.2       7     .0 Configurat
latch: In memory undo latch           2,863  1.8       1     .0 Concurrenc
^L
Wait Classes by Total Wait Time
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                                                        Avg             Avg
                                        Total Wait     Wait   % DB   Active
Wait Class                  Waits       Time (sec)     (ms)   time Sessions
---------------- ---------------- ---------------- -------- ------ --------
Commit                  4,048,145            3,689        1   62.4      1.0
DB CPU                                       1,947            32.9      0.5
System I/O              2,649,191            1,614        1   27.3      0.5
Concurrency             2,408,339              347        0    5.9      0.1
Application               412,330               72        0    1.2      0.0
Other                     182,809               17        0     .3      0.0
Configuration              11,676                8        1     .1      0.0
Network                 6,686,094                3        0     .0      0.0
User I/O                    2,124                1        1     .0      0.0

Host CPU
~~~~~~~~                  Load Average
 CPUs Cores Sockets     Begin       End     %User   %System      %WIO     %Idle
----- ----- ------- --------- --------- --------- --------- --------- ---------
    2     1       1      1.38      1.19       3.3       6.4      12.5      65.9

Instance CPU
~~~~~~~~~~~~
              % of total CPU for Instance:      30.2
              % of busy  CPU for Instance:      88.6
  %DB time waiting for CPU - Resource Mgr:       0.0

IO Profile                  Read+Write/Second     Read/Second    Write/Second
~~~~~~~~~~                  ----------------- --------------- ---------------
            Total Requests:             897.6             9.6           888.0
         Database Requests:              70.7             0.2            70.5
        Optimized Requests:               0.0             0.0             0.0
             Redo Requests:             816.3             5.2           811.2
                Total (MB):              16.7             5.1            11.7
             Database (MB):               1.9             0.0             1.9
      Optimized Total (MB):               0.0             0.0             0.0
                 Redo (MB):               9.8             4.9             4.9
         Database (blocks):             240.1             0.2           239.9
 Via Buffer Cache (blocks):             239.7             0.0           239.7
           Direct (blocks):               0.5             0.2             0.2

Memory Statistics
~~~~~~~~~~~~~~~~~                       Begin          End
                                 ------------ ------------
                  Host Mem (MB):     15,753.8     15,753.8
                   SGA use (MB):     11,680.0     11,680.0
                   PGA use (MB):        265.3        260.0
    % Host Mem used for SGA+PGA:        75.82        75.79

Cache Sizes                       Begin        End
~~~~~~~~~~~                  ---------- ----------
               Buffer Cache:     9,952M     9,952M  Std Block Size:         8K
           Shared Pool Size:     1,422M     1,422M      Log Buffer:     9,920K

 Shared Pool Statistics        Begin    End
~~~~~~~~~~~~~~~~~~~~~~~~~~~~  ------  ------
             Memory Usage %:   29.71   29.73
    % SQL with executions>1:   95.33   95.28
  % Memory for SQL w/exec>1:   82.84   82.66

^LTime Model Statistics                        DB/Inst: ORCL/ORCL  Snaps: 23-24
-> Total time in database user-calls (DB Time): 5912.1s
-> Statistics including the word "background" measure background process
   time, and so do not contribute to the DB time statistic
-> Ordered by % or DB time desc, Statistic name

Statistic Name                                       Time (s) % of DB Time
------------------------------------------ ------------------ ------------
DB CPU                                                1,947.3         32.9
sql execute elapsed time                              1,835.4         31.0
parse time elapsed                                       51.9           .9
sequence load elapsed time                               11.7           .2
connection management call elapsed time                   1.0           .0
repeated bind elapsed time                                0.2           .0
PL/SQL execution elapsed time                             0.0           .0
DB time                                               5,912.1
background elapsed time                               1,738.5
background cpu time                                     169.1
                          ------------------------------------------------------

Operating System Statistics                   DB/Inst: ORCL/ORCL  Snaps: 23-24
-> *TIME statistic values are diffed.
   All others display actual values.  End Value is displayed if different
-> ordered by statistic type (CPU Use, Virtual Memory, Hardware Config), Name

Statistic                                  Value        End Value
------------------------- ---------------------- ----------------
BUSY_TIME                                238,871
IDLE_TIME                                461,716
IOWAIT_TIME                               87,280
NICE_TIME                                163,683
SYS_TIME                                  45,109
USER_TIME                                 22,772
LOAD                                           1                1
RSRC_MGR_CPU_WAIT_TIME                         0
VM_OUT_BYTES                           1,249,280
PHYSICAL_MEMORY_BYTES             16,519,077,888
NUM_CPUS                                       2
NUM_CPU_CORES                                  1
NUM_CPU_SOCKETS                                1
GLOBAL_RECEIVE_SIZE_MAX                4,194,304
GLOBAL_SEND_SIZE_MAX                   4,194,304
TCP_RECEIVE_SIZE_DEFAULT                  87,380
TCP_RECEIVE_SIZE_MAX                   6,291,456
TCP_RECEIVE_SIZE_MIN                       4,096
TCP_SEND_SIZE_DEFAULT                     16,384
TCP_SEND_SIZE_MAX                      4,194,304
TCP_SEND_SIZE_MIN                          4,096
                          ------------------------------------------------------

                          ------------------------------------------------------

Operating System Statistics - Detail          DB/Inst: ORCL/ORCL  Snaps: 23-24

Snap Time           Load    %busy    %user     %sys    %idle  %iowait
--------------- -------- -------- -------- -------- -------- --------
26-Apr 21:00:37      1.4      N/A      N/A      N/A      N/A      N/A
26-Apr 22:00:04      1.2     34.1      3.3      6.4     65.9     12.5
                          ------------------------------------------------------

Foreground Wait Class                         DB/Inst: ORCL/ORCL  Snaps: 23-24
-> s  - second, ms - millisecond -    1000th of a second
-> ordered by wait time desc, waits desc
-> %Timeouts: value of 0 indicates value was < .5%.  Value of null is truly 0
-> Captured Time accounts for        102.6%  of Total DB time       5,912.07 (s)
-> Total FG Wait Time:             4,119.97 (s)  DB CPU time:       1,947.29 (s)

                                                                  Avg
                                      %Time       Total Wait     wait
Wait Class                      Waits -outs         Time (s)     (ms)  %DB time
-------------------- ---------------- ----- ---------------- -------- ---------
Commit                      4,048,143     0            3,689        1      62.4
DB CPU                                                 1,947               32.9
Concurrency                 2,408,319     0              347        0       5.9
Application                   412,330     0               72        0       1.2
Configuration                  11,676     0                8        1       0.1
Network                     6,685,887     0                3        0       0.0
System I/O                        989     0                1        1       0.0
User I/O                          307     0                0        1       0.0
Other                             215     0                0        1       0.0
                          ------------------------------------------------------

^LForeground Wait Events                       DB/Inst: ORCL/ORCL  Snaps: 23-24
-> s  - second, ms - millisecond -    1000th of a second
-> Only events with Total Wait Time (s) >= .001 are shown
-> ordered by wait time desc, waits desc (idle events last)
-> %Timeouts: value of 0 indicates value was < .5%.  Value of null is truly 0

                                                             Avg
                                        %Time Total Wait    wait    Waits   % DB
Event                             Waits -outs   Time (s)    (ms)     /txn   time
-------------------------- ------------ ----- ---------- ------- -------- ------
log file sync                 4,048,143     0      3,689       1      1.0   62.4
buffer busy waits             2,395,899     0        341       0      0.6    5.8
enq: TX - row lock content      412,330     0         72       0      0.1    1.2
latch: cache buffers chain        9,514     0          4       0      0.0     .1
log file switch (checkpoin           26     0          4     135      0.0     .1
SQL*Net message to client     6,685,887     0          3       0      1.7     .0
enq: SQ - contention             11,266     0          2       0      0.0     .0
log file switch completion          335     0          2       7      0.0     .0
latch: In memory undo latc        2,863     0          2       1      0.0     .0
control file sequential re          989     0          1       1      0.0     .0
log file switch (private s           49     0          0       8      0.0     .0
db file sequential read             300     0          0       1      0.0     .0
cursor: pin S                        19     0          0       5      0.0     .0
library cache: mutex X               24     0          0       4      0.0     .0
latch: enqueue hash chains           66     0          0       1      0.0     .0
latch: undo global data              24     0          0       1      0.0     .0
latch free                           45     0          0       1      0.0     .0
latch: redo allocation               61     0          0       0      0.0     .0
latch: session allocation             1     0          0       9      0.0     .0
latch: cache buffers lru c            5     0          0       2      0.0     .0
latch: cache buffer handle            8     0          0       1      0.0     .0
Disk file operations I/O              7     0          0       0      0.0     .0
latch: checkpoint queue la            3     0          0       0      0.0     .0
SQL*Net message from clien    6,685,885     0     29,743       4      1.7
                          ------------------------------------------------------

^LBackground Wait Events                       DB/Inst: ORCL/ORCL  Snaps: 23-24
-> ordered by wait time desc, waits desc (idle events last)
-> Only events with Total Wait Time (s) >= .001 are shown
-> %Timeouts: value of 0 indicates value was < .5%.  Value of null is truly 0




                                                             Avg
                                        %Time Total Wait    wait    Waits   % bg
Event                             Waits -outs   Time (s)    (ms)     /txn   time
-------------------------- ------------ ----- ---------- ------- -------- ------
log file parallel write       2,597,621     0      1,569       1      0.6   90.3
log file sequential read         15,328     0         25       2      0.0    1.5
LGWR wait for redo copy         181,500     0         16       0      0.0     .9
db file parallel write           13,637     0          8       1      0.0     .5
control file sequential re       13,797     0          6       0      0.0     .4
control file parallel writ        4,854     0          3       1      0.0     .2
Disk file operations I/O          1,288     0          1       1      0.0     .0
db file async I/O submit          2,497     0          1       0      0.0     .0
reliable message                    721     0          1       1      0.0     .0
Log archive I/O                     155     0          0       2      0.0     .0
os thread startup                    16     0          0      12      0.0     .0
log file single write               310     0          0       0      0.0     .0
direct path write                   167     0          0       1      0.0     .0
latch: redo allocation              102     0          0       1      0.0     .0
direct path read                    347     0          0       0      0.0     .0
ADR block file read                  15     0          0       0      0.0     .0
log file sync                         2     0          0       2      0.0     .0
db file sequential read              11     0          0       0      0.0     .0
ADR block file write                  5     0          0       0      0.0     .0
asynch descriptor resize             83   100          0       0      0.0     .0
db file scattered read                4     0          0       0      0.0     .0
rdbms ipc message             1,738,809     1     51,666      30      0.4
DIAG idle wait                    7,127   100      7,127    1000      0.0
Space Manager: slave idle           976    99      4,857    4976      0.0
Streams AQ: qmn slave idle          128     0      3,585   28008      0.0
Streams AQ: qmn coordinato          256    50      3,585   14004      0.0
pmon timer                        1,207    97      3,566    2954      0.0
smon timer                           61    13      3,398   55704      0.0
SQL*Net message from clien          274     0          0       1      0.0
class slave wait                     16     0          0       0      0.0
                          ------------------------------------------------------

^LWait Event Histogram                         DB/Inst: ORCL/ORCL  Snaps: 23-24
-> Units for Total Waits column: K is 1000, M is 1000000, G is 1000000000
-> % of Waits: value of .0 indicates value was <.05%; value of null is truly 0
-> % of Waits: column heading of <=1s is truly <1024ms, >1s is truly >=1024ms
-> Ordered by Event (idle events last)

                                                    % of Waits
                                 -----------------------------------------------
                           Total
Event                      Waits  1s
-------------------------- ----- ----- ----- ----- ----- ----- ----- ----- -----
ADR block file read           15  93.3   6.7
ADR block file write           5 100.0
ADR file lock                  6 100.0
ARCH wait for archivelog l   156 100.0
Disk file operations I/O    1295  85.8   1.8  12.2    .2          .1
LGWR wait for redo copy    181.5  99.5    .2    .1    .1    .0    .0    .0
Log archive I/O              155  13.5  74.2  11.0    .6                .6
SQL*Net message to client  6629. 100.0    .0    .0    .0    .0
asynch descriptor resize      83  98.8   1.2
buffer busy waits          2395.  99.3    .6    .1    .0    .0    .0    .0    .0
control file parallel writ  4855  94.8   3.9    .7    .5    .1    .0    .0
control file sequential re 14.8K  94.8   4.0    .7    .3    .1    .0    .0
cursor: pin S                 19  42.1  15.8              42.1
db file async I/O submit    2497  99.0    .7    .2    .0
db file parallel write     13.6K  89.6   9.4    .6    .2    .1    .0    .2
db file scattered read         4 100.0
db file sequential read      311  86.8   7.7   1.6   2.9   1.0
direct path read             347  98.8   1.2
direct path write            167  91.0   9.0
enq: SQ - contention       11.3K  99.1    .6    .1    .1    .1
enq: TX - row lock content 412.3  99.8    .1    .0    .0    .0    .0    .0
latch free                    47  85.1   8.5   4.3               2.1
latch: In memory undo latc  2863  88.9   4.0   3.5   2.4    .8    .3    .0
latch: cache buffer handle     8  87.5        12.5
latch: cache buffers chain  9514  95.5   1.4   1.3   1.0    .6    .3    .0
latch: cache buffers lru c     5  60.0  20.0        20.0
latch: checkpoint queue la     4 100.0
latch: enqueue hash chains    66  86.4   4.5   4.5   4.5
latch: messages                5 100.0
latch: object queue header     1 100.0
latch: redo allocation       164  87.2   6.7   3.7   2.4
latch: session allocation      1                         100.0
latch: undo global data       24  66.7  12.5   8.3  12.5
library cache: mutex X        24  66.7         4.2        25.0   4.2
log file parallel write    2597.  96.9   2.1    .7    .3    .1    .0    .0
log file sequential read   15.3K  55.2  34.4   9.5    .4    .1    .0    .5
log file single write        310  99.7    .3
log file switch (checkpoin    26         3.8   7.7  42.3  34.6              11.5
log file switch (private s    49         6.1        51.0  42.9
log file switch completion   335   5.4   6.0   5.7  57.3  25.1    .6
log file sync              4047.  82.2  15.6   1.2    .7    .3    .1    .0
os thread startup             16                    50.0  37.5        12.5
reliable message             721  84.6  15.0    .4
DIAG idle wait              7127                                     100.0
SQL*Net message from clien 6682.  83.6   5.5    .5   2.4   7.4    .5    .0    .0
Space Manager: slave idle    957                            .1          .2  99.7
Streams AQ: qmn coordinato   256  49.2          .4          .4              50.0
Streams AQ: qmn slave idle   128                                           100.0
class slave wait              16  93.8               6.3
pmon timer                  1208          .2    .2                .1   1.2  98.3
rdbms ipc message          1738.  80.8   9.6    .8   2.9   4.8    .2    .4    .5
smon timer                    62   1.6                                19.4  79.0
                          ------------------------------------------------------


^LWait Event Histogram Detail (64 msec to 2 sec)DB/Inst: ORCL/ORCL  Snaps: 23-2
-> Units for Total Waits column: K is 1000, M is 1000000, G is 1000000000
-> Units for % of Total Waits:
   ms is milliseconds
   s is 1024 milliseconds (approximately 1 second)
-> % of Total Waits: total waits for all wait classes, including Idle
-> % of Total Waits: value of .0 indicates value was <.05%;
   value of null is truly 0
-> Ordered by Event (only non-idle events are displayed)

                                                 % of Total Waits
                                 -----------------------------------------------
                           Waits
                           64ms
Event                      to 2s =2s
-------------------------- ----- ----- ----- ----- ----- ----- ----- ----- -----
LGWR wait for redo copy        2 100.0    .0
Log archive I/O                1  99.4                .6
buffer busy waits             13 100.0    .0                            .0
control file parallel writ     1 100.0    .0
control file sequential re     4 100.0    .0    .0
db file parallel write        22  99.8    .0    .1    .1
enq: TX - row lock content     2 100.0    .0
latch: In memory undo latc     1 100.0    .0
latch: cache buffers chain     1 100.0    .0
log file parallel write       81 100.0    .0    .0    .0    .0    .0
log file sequential read      69  99.5    .0    .2    .2
log file switch (checkpoin     3  88.5                                11.5
log file sync                593 100.0    .0    .0    .0    .0    .0
os thread startup              2  87.5  12.5
                          ------------------------------------------------------

^LWait Event Histogram Detail (4 sec to 2 min) DB/Inst: ORCL/ORCL  Snaps: 23-24

                  No data exists for this section of the report.
                          ------------------------------------------------------

^LWait Event Histogram Detail (4 min to 1 hr)  DB/Inst: ORCL/ORCL  Snaps: 23-24

                  No data exists for this section of the report.
                          ------------------------------------------------------

^LService Statistics                           DB/Inst: ORCL/ORCL  Snaps: 23-24
-> ordered by DB Time

                                                           Physical      Logical
Service Name                  DB Time (s)   DB CPU (s)    Reads (K)    Reads (K)
---------------------------- ------------ ------------ ------------ ------------
SYS$USERS                           5,912        1,947            0      204,286
ORCL_A                                  0            0            0            0
SYS$BACKGROUND                          0            0            1           21
                          ------------------------------------------------------


Service Wait Class Stats                      DB/Inst: ORCL/ORCL  Snaps: 23-24
-> Wait Class info for services in the Service Statistics section.
-> Total Waits and Time Waited displayed for the following wait
   classes:  User I/O, Concurrency, Administrative, Network
-> Time Waited (Wt Time) in seconds

Service Name
----------------------------------------------------------------
 User I/O  User I/O  Concurcy  Concurcy     Admin     Admin   Network   Network
Total Wts   Wt Time Total Wts   Wt Time Total Wts   Wt Time Total Wts   Wt Time
--------- --------- --------- --------- --------- --------- --------- ---------
SYS$USERS
      307         0   2408319       347         0         0   6685891         3
SYS$BACKGROUND
     1817         1        20         0         0         0         0         0
                          ------------------------------------------------------

^LSQL ordered by Elapsed Time                  DB/Inst: ORCL/ORCL  Snaps: 23-24
-> Resources reported for PL/SQL code includes the resources used by all SQL
   statements called by the code.
-> % Total DB Time is the Elapsed Time of the SQL statement divided
   into the Total Database Time multiplied by 100
-> %Total - Elapsed Time  as a percentage of Total DB time
-> %CPU   - CPU Time      as a percentage of Elapsed Time
-> %IO    - User I/O Time as a percentage of Elapsed Time
-> Captured SQL account for   31.4% of Total DB Time (s):           5,912
-> Captured PL/SQL account for    0.0% of Total DB Time (s):           5,912

        Elapsed                  Elapsed Time
        Time (s)    Executions  per Exec (s)  %Total   %CPU    %IO    SQL Id
---------------- -------------- ------------- ------ ------ ------ -------------
           833.4      1,668,249          0.00   14.1   37.7     .0 21yp54r1kwdcw
Module: JDBC Thin Client
INSERT ALL INTO authors (id,name,email) VALUES ( serial.nextval ,'Priya','p@g
mail.com') INTO authors (id,name,email) VALUES ( serial.nextval ,'Priya','p@
gmail.com') INTO authors (id,name,email) VALUES ( serial.nextval ,'Priya','p
@gmail.com') INTO authors (id,name,email) VALUES ( serial.nextval ,'Priya','

           454.9      1,667,916          0.00    7.7   35.1     .0 djyntpq5hxwpk
Module: JDBC Thin Client
delete from authors where id < ( select * from (select max(id) - 30 from author
s) a ) and id > ( select * from (select max(id) - 500 from authors) b )

           294.0      1,668,262          0.00    5.0   28.2     .0 128ccsst17vwb
Module: JDBC Thin Client
update authors set email = 'toto' where id > ( select max(id) - 1 from authors)

           260.7      1,667,137          0.00    4.4   45.1     .0 2fpz2m7duxb64
Module: JDBC Thin Client
select count(*) from authors where id < ( select max(id) - 30 from authors) and
 id > ( select max(id) - 2500 from authors) union select count(*) from authors
where id < ( select max(id) - 30 from authors) and id > ( select max(id) - 1500
 from authors) union select count(*) from authors where id < ( select max(id) -

             5.1         83,419          0.00     .1   94.1     .0 4m7m0t6fjcs5x
update seq$ set increment$=:2,minvalue=:3,maxvalue=:4,cycle#=:5,order$=:6,cache=
:7,highwater=:8,audit$=:9,flags=:10 where obj#=:1

             4.1          1,843          0.00     .1   80.2     .0 3p9jxd3w1hdx9
Module: rdsoracleperfmon@ip-10-13-0-252 (TNS V1-V3)
select s.sid||':'||s.serial# session_id, nvl(s.username,decode(s.type,'BACKGRO
UND','SYS')) username, s.machine, q.force_matching_signature, s.sql_id,
s.sql_hash_value, substr(q.sql_text, 1, 1000) sql_text, nvl (c.command_name,
 decode(s.wait_class,'Commit',s.wait_class, decode(s.type,'BACKGROUND', b.na

             0.8             61          0.01     .0   25.0     .0 ca6tq9wk5wakf
Module: JDBC Thin Client
select * from (select name, to_char(next_time, 'YYYY/MM/DD HH24:MI:SS') as resto
rable_time, recid from sys.v_$archived_log al JOIN sys.v_$database_incarnation d
i ON di.RESETLOGS_ID = al.RESETLOGS_ID and di.STATUS = 'CURRENT' where al.name i
s NOT NULL and al.standby_dest = 'NO' AND al.archived = 'YES' AND al.thread# = 1

             0.6              1          0.64     .0   88.2     .0 bunssq950snhf
insert into wrh$_sga_target_advice (snap_id, dbid, instance_number, SGA_SIZ
E, SGA_SIZE_FACTOR, ESTD_DB_TIME, ESTD_PHYSICAL_READS) select :snap_id, :dbi
d, :instance_number, SGA_SIZE, SGA_SIZE_FACTOR, ESTD_DB_TIME, ESTD_PHYSICAL_R
EADS from v$sga_target_advice

             0.6             60          0.01     .0    2.0   38.2 3h0a0h5srz9t9
Module: JDBC Thin Client
select count(*) from sys.v_$datafile where status in ('RECOVER','ONLINE') and EN
ABLED != 'READ ONLY' and checkpoint_time < sysdate-(120/1440)

             0.4             30          0.01     .0   93.9     .0 75p1jt4wbk27n
Module: rdsoracleperfmon@ip-10-13-0-252 (TNS V1-V3)
select decode(class,1,'User',2,'Redo',4,'Enqueue',8,'Cache',16,'OS',64,'SQL','Ot
her') class, name, value from v$sysstat where class not in (32,128) and name not
 like 'session%' and name not like 'java session%' and not regexp_like (name,'(O
LAP|^IM|spare|cell|^flash|^gc|^HSC|^EHCC|^(W|w)orkload|^(C|c)luster|RAC)') order

                          ------------------------------------------------------

^LSQL ordered by CPU Time                      DB/Inst: ORCL/ORCL  Snaps: 23-24
-> Resources reported for PL/SQL code includes the resources used by all SQL
   statements called by the code.
-> %Total - CPU Time      as a percentage of Total DB CPU
-> %CPU   - CPU Time      as a percentage of Elapsed Time
-> %IO    - User I/O Time as a percentage of Elapsed Time
-> Captured SQL account for   35.1% of Total CPU Time (s):           1,947
-> Captured PL/SQL account for    0.0% of Total CPU Time (s):           1,947

    CPU                   CPU per           Elapsed
  Time (s)  Executions    Exec (s) %Total   Time (s)   %CPU    %IO    SQL Id
---------- ------------ ---------- ------ ---------- ------ ------ -------------
     314.1    1,668,249       0.00   16.1      833.4   37.7     .0 21yp54r1kwdcw
Module: JDBC Thin Client
INSERT ALL INTO authors (id,name,email) VALUES ( serial.nextval ,'Priya','p@g
mail.com') INTO authors (id,name,email) VALUES ( serial.nextval ,'Priya','p@
gmail.com') INTO authors (id,name,email) VALUES ( serial.nextval ,'Priya','p
@gmail.com') INTO authors (id,name,email) VALUES ( serial.nextval ,'Priya','

     159.8    1,667,916       0.00    8.2      454.9   35.1     .0 djyntpq5hxwpk
Module: JDBC Thin Client
delete from authors where id < ( select * from (select max(id) - 30 from author
s) a ) and id > ( select * from (select max(id) - 500 from authors) b )

     117.5    1,667,137       0.00    6.0      260.7   45.1     .0 2fpz2m7duxb64
Module: JDBC Thin Client
select count(*) from authors where id < ( select max(id) - 30 from authors) and
 id > ( select max(id) - 2500 from authors) union select count(*) from authors
where id < ( select max(id) - 30 from authors) and id > ( select max(id) - 1500
 from authors) union select count(*) from authors where id < ( select max(id) -

      83.0    1,668,262       0.00    4.3      294.0   28.2     .0 128ccsst17vwb
Module: JDBC Thin Client
update authors set email = 'toto' where id > ( select max(id) - 1 from authors)

       4.8       83,419       0.00    0.2        5.1   94.1     .0 4m7m0t6fjcs5x
update seq$ set increment$=:2,minvalue=:3,maxvalue=:4,cycle#=:5,order$=:6,cache=
:7,highwater=:8,audit$=:9,flags=:10 where obj#=:1

       3.3        1,843       0.00    0.2        4.1   80.2     .0 3p9jxd3w1hdx9
Module: rdsoracleperfmon@ip-10-13-0-252 (TNS V1-V3)
select s.sid||':'||s.serial# session_id, nvl(s.username,decode(s.type,'BACKGRO
UND','SYS')) username, s.machine, q.force_matching_signature, s.sql_id,
s.sql_hash_value, substr(q.sql_text, 1, 1000) sql_text, nvl (c.command_name,
 decode(s.wait_class,'Commit',s.wait_class, decode(s.type,'BACKGROUND', b.na

       0.6            1       0.57    0.0        0.6   88.2     .0 bunssq950snhf
insert into wrh$_sga_target_advice (snap_id, dbid, instance_number, SGA_SIZ
E, SGA_SIZE_FACTOR, ESTD_DB_TIME, ESTD_PHYSICAL_READS) select :snap_id, :dbi
d, :instance_number, SGA_SIZE, SGA_SIZE_FACTOR, ESTD_DB_TIME, ESTD_PHYSICAL_R
EADS from v$sga_target_advice

       0.3           30       0.01    0.0        0.4   93.9     .0 75p1jt4wbk27n
Module: rdsoracleperfmon@ip-10-13-0-252 (TNS V1-V3)
select decode(class,1,'User',2,'Redo',4,'Enqueue',8,'Cache',16,'OS',64,'SQL','Ot
her') class, name, value from v$sysstat where class not in (32,128) and name not
 like 'session%' and name not like 'java session%' and not regexp_like (name,'(O
LAP|^IM|spare|cell|^flash|^gc|^HSC|^EHCC|^(W|w)orkload|^(C|c)luster|RAC)') order

       0.2           61       0.00    0.0        0.8   25.0     .0 ca6tq9wk5wakf
Module: JDBC Thin Client
select * from (select name, to_char(next_time, 'YYYY/MM/DD HH24:MI:SS') as resto
rable_time, recid from sys.v_$archived_log al JOIN sys.v_$database_incarnation d
i ON di.RESETLOGS_ID = al.RESETLOGS_ID and di.STATUS = 'CURRENT' where al.name i
s NOT NULL and al.standby_dest = 'NO' AND al.archived = 'YES' AND al.thread# = 1

       0.2        1,242       0.00    0.0        0.3   63.9     .0 cm5vu20fhtnq1
select /*+ connect_by_filtering */ privilege#,level from sysauth$ connect by gra
ntee#=prior privilege# and privilege#>0 start with grantee#=:1 and privilege#>0

                          ------------------------------------------------------

^LSQL ordered by User I/O Wait Time            DB/Inst: ORCL/ORCL  Snaps: 23-24
-> Resources reported for PL/SQL code includes the resources used by all SQL
   statements called by the code.
-> %Total - User I/O Time as a percentage of Total User I/O Wait time
-> %CPU   - CPU Time      as a percentage of Elapsed Time
-> %IO    - User I/O Time as a percentage of Elapsed Time
-> Captured SQL account for   19.2% of Total User I/O Wait Time (s):
-> Captured PL/SQL account for    0.2% of Total User I/O Wait Time (s):
  User I/O                UIO per           Elapsed
  Time (s)  Executions    Exec (s) %Total   Time (s)   %CPU    %IO    SQL Id
---------- ------------ ---------- ------ ---------- ------ ------ -------------
       0.2           60       0.00   18.7        0.6    2.0   38.2 3h0a0h5srz9t9
Module: JDBC Thin Client
select count(*) from sys.v_$datafile where status in ('RECOVER','ONLINE') and EN
ABLED != 'READ ONLY' and checkpoint_time < sysdate-(120/1440)

       0.0            1       0.00    0.2        0.1   96.1    3.3 6ajkhukk78nsr
begin prvt_hdm.auto_execute( :dbid, :inst_num , :end_snap_id ); end;

       0.0            1       0.00    0.2        0.0   39.7   56.9 47mm81hm9sggy
 SELECT sum(case when a.session_type = 1 and a.wait_time = 0 then 1
else 0 end) as fgw, sum(case when a.session_type = 1 and a.wait_time <>
0 then 1 else 0 end) as fgc, sum(case when a.session_type <>
 1 and a.wait_time = 0 then 1 else 0 end) as bgw, sum(case w

       0.0            1       0.00    0.2        0.0   77.5   11.6 85px9dq62dc0q
INSERT /*+ APPEND LEADING(@"SEL$F5BB74E1" "H"@"SEL$2" "A"@"SEL$1") USE_NL(@"SE
L$F5BB74E1" "A"@"SEL$1") */ INTO WRH$_ACTIVE_SESSION_HISTORY ( snap_id,
dbid, instance_number, sample_id, sample_time , session_id, session_serial#, ses
sion_type , flags , user_id , sql_id, sql_child_number, sql_opcode, force_matchi

       0.0    1,668,262       0.00    0.1      294.0   28.2     .0 128ccsst17vwb
Module: JDBC Thin Client
update authors set email = 'toto' where id > ( select max(id) - 1 from authors)

       0.0           62       0.00    0.0        0.0   66.8     .0 0k8522rmdzg4k
select privilege# from sysauth$ where (grantee#=:1 or grantee#=1) and privilege#
>0

       0.0          240       0.00    0.0        0.1   80.1     .0 0kqxgptj0p6rt
Module: JDBC Thin Client
SELECT count(1) FROM dba_users WHERE username = 'RDSADMIN'

       0.0            1       0.00    0.0        0.0  100.2     .0 0pt4jfmq9f1q0
SELECT x.statistic# as stat_id, x.keh_id as keh_id, nvl(awr_time.value_diff, 0
) as value_diff FROM X$KEHTIMMAP x ,(SELECT startsn.stat_id as stat_id, sum(G
REATEST( 0, (endsn.value - startsn.value) )) as value_diff FROM WRH$_SYS_TIME_
MODEL startsn , WRH$_SYS_TIME_MODEL endsn WHERE endsn.dbid = :dbid AND ends

       0.0           67       0.00    0.0        0.0   87.1     .0 0ws7ahf1d78qa
select SYS_CONTEXT('USERENV', 'SERVER_HOST'), SYS_CONTEXT('USERENV', 'DB_UNIQUE_
NAME'), SYS_CONTEXT('USERENV', 'INSTANCE_NAME'), SYS_CONTEXT('USERENV', 'SERVICE
_NAME'), INSTANCE_NUMBER, STARTUP_TIME, SYS_CONTEXT('USERENV', 'DB_DOMAIN') from
 v$instance where INSTANCE_NAME=SYS_CONTEXT('USERENV', 'INSTANCE_NAME')

       0.0            1       0.00    0.0        0.0  100.0     .0 155cwuv2pfp1d
 SELECT distinct x.id, e.instance_number FROM WRM$_SNAP_ERROR e , X$KEHSQT
x WHERE e.table_name = x.name AND e.dbid = :dbid AND e.instance_number =
:inst AND e.snap_id IN (:bid, :eid) AND x.ver_type = :edge

                          ------------------------------------------------------
^LSQL ordered by Gets                          DB/Inst: ORCL/ORCL  Snaps: 23-24
-> Resources reported for PL/SQL code includes the resources used by all SQL
   statements called by the code.
-> %Total - Buffer Gets   as a percentage of Total Buffer Gets
-> %CPU   - CPU Time      as a percentage of Elapsed Time
-> %IO    - User I/O Time as a percentage of Elapsed Time
-> Total Buffer Gets:     204,307,245
-> Captured SQL account for   98.1% of Total

     Buffer                 Gets              Elapsed
      Gets   Executions   per Exec   %Total   Time (s)  %CPU   %IO    SQL Id
----------- ----------- ------------ ------ ---------- ----- ----- -------------
1.03668E+08   1,668,249         62.1   50.7      833.4  37.7     0 21yp54r1kwdcw
Module: JDBC Thin Client
INSERT ALL INTO authors (id,name,email) VALUES ( serial.nextval ,'Priya','p@g
mail.com') INTO authors (id,name,email) VALUES ( serial.nextval ,'Priya','p@
gmail.com') INTO authors (id,name,email) VALUES ( serial.nextval ,'Priya','p
@gmail.com') INTO authors (id,name,email) VALUES ( serial.nextval ,'Priya','

 51,657,287   1,667,137         31.0   25.3      260.7  45.1     0 2fpz2m7duxb64
Module: JDBC Thin Client
select count(*) from authors where id < ( select max(id) - 30 from authors) and
 id > ( select max(id) - 2500 from authors) union select count(*) from authors
where id < ( select max(id) - 30 from authors) and id > ( select max(id) - 1500
 from authors) union select count(*) from authors where id < ( select max(id) -

 28,710,182   1,667,916         17.2   14.1      454.9  35.1     0 djyntpq5hxwpk
Module: JDBC Thin Client
delete from authors where id < ( select * from (select max(id) - 30 from author
s) a ) and id > ( select * from (select max(id) - 500 from authors) b )

 16,135,337   1,668,262          9.7    7.9      294.0  28.2     0 128ccsst17vwb
Module: JDBC Thin Client
update authors set email = 'toto' where id > ( select max(id) - 1 from authors)

    254,263      83,419          3.0    0.1        5.1  94.1     0 4m7m0t6fjcs5x
update seq$ set increment$=:2,minvalue=:3,maxvalue=:4,cycle#=:5,order$=:6,cache=
:7,highwater=:8,audit$=:9,flags=:10 where obj#=:1

     10,226           1     10,226.0    0.0        0.1  96.1   3.3 6ajkhukk78nsr
begin prvt_hdm.auto_execute( :dbid, :inst_num , :end_snap_id ); end;

      7,444       1,242          6.0    0.0        0.3  63.9     0 cm5vu20fhtnq1
select /*+ connect_by_filtering */ privilege#,level from sysauth$ connect by gra
ntee#=prior privilege# and privilege#>0 start with grantee#=:1 and privilege#>0

      6,986           1      6,986.0    0.0        0.0 102.4     0 cw860p03hy5ff
 SELECT count(*) as cnt , a.SQL_ID, a.CURRENT_OBJ#, sum
(case when a.event_id in (:e0, :e1, :e2, :e3, :e4, :e5, :e6, :e7) then 1 else 0
end) as full_scan FROM WRH$_ACTIVE_SESSION_HISTORY a , WRH$_EVENT_NAME en
 WHERE a.dbid = :dbid AND a.instance_number = :inst AND a.snap_id > :bid AND

      3,840         240         16.0    0.0        0.1  80.1     0 0kqxgptj0p6rt
Module: JDBC Thin Client
SELECT count(1) FROM dba_users WHERE username = 'RDSADMIN'

      1,488          62         24.0    0.0        0.0  66.8     0 0k8522rmdzg4k
select privilege# from sysauth$ where (grantee#=:1 or grantee#=1) and privilege#
>0

                          ------------------------------------------------------

^LSQL ordered by Reads                         DB/Inst: ORCL/ORCL  Snaps: 23-24
-> %Total - Physical Reads as a percentage of Total Disk Reads
-> %CPU   - CPU Time      as a percentage of Elapsed Time
-> %IO    - User I/O Time as a percentage of Elapsed Time
-> Total Disk Reads:             810
-> Captured SQL account for    3.5% of Total

   Physical              Reads              Elapsed
      Reads  Executions per Exec   %Total   Time (s)   %CPU    %IO    SQL Id
----------- ----------- ---------- ------ ---------- ------ ------ -------------
         28           1       28.0    3.5        0.0   39.7   56.9 47mm81hm9sggy
 SELECT sum(case when a.session_type = 1 and a.wait_time = 0 then 1
else 0 end) as fgw, sum(case when a.session_type = 1 and a.wait_time <>
0 then 1 else 0 end) as fgc, sum(case when a.session_type <>
 1 and a.wait_time = 0 then 1 else 0 end) as bgw, sum(case w

         28           1       28.0    3.5        0.1   96.1    3.3 6ajkhukk78nsr
begin prvt_hdm.auto_execute( :dbid, :inst_num , :end_snap_id ); end;

          0          62        0.0    0.0        0.0   66.8     .0 0k8522rmdzg4k
select privilege# from sysauth$ where (grantee#=:1 or grantee#=1) and privilege#
>0

          0         240        0.0    0.0        0.1   80.1     .0 0kqxgptj0p6rt
Module: JDBC Thin Client
SELECT count(1) FROM dba_users WHERE username = 'RDSADMIN'

          0           1        0.0    0.0        0.0  100.2     .0 0pt4jfmq9f1q0
SELECT x.statistic# as stat_id, x.keh_id as keh_id, nvl(awr_time.value_diff, 0
) as value_diff FROM X$KEHTIMMAP x ,(SELECT startsn.stat_id as stat_id, sum(G
REATEST( 0, (endsn.value - startsn.value) )) as value_diff FROM WRH$_SYS_TIME_
MODEL startsn , WRH$_SYS_TIME_MODEL endsn WHERE endsn.dbid = :dbid AND ends

          0          67        0.0    0.0        0.0   87.1     .0 0ws7ahf1d78qa
select SYS_CONTEXT('USERENV', 'SERVER_HOST'), SYS_CONTEXT('USERENV', 'DB_UNIQUE_
NAME'), SYS_CONTEXT('USERENV', 'INSTANCE_NAME'), SYS_CONTEXT('USERENV', 'SERVICE
_NAME'), INSTANCE_NUMBER, STARTUP_TIME, SYS_CONTEXT('USERENV', 'DB_DOMAIN') from
 v$instance where INSTANCE_NAME=SYS_CONTEXT('USERENV', 'INSTANCE_NAME')

          0   1,668,262        0.0    0.0      294.0   28.2     .0 128ccsst17vwb
Module: JDBC Thin Client
update authors set email = 'toto' where id > ( select max(id) - 1 from authors)

          0           1        0.0    0.0        0.0  100.0     .0 155cwuv2pfp1d
 SELECT distinct x.id, e.instance_number FROM WRM$_SNAP_ERROR e , X$KEHSQT
x WHERE e.table_name = x.name AND e.dbid = :dbid AND e.instance_number =
:inst AND e.snap_id IN (:bid, :eid) AND x.ver_type = :edge

          0           1        0.0    0.0        0.0     .0     .0 181cvj277dvuq
Module: JDBC Thin Client
select count(*) from sys.dba_triggers where owner = 'RDSADMIN' and trigger_name
= 'RDS_GRANT_TRIGGER' and status = 'ENABLED'

          0           1        0.0    0.0        0.0  100.6     .0 18c2yb5aj919t
 SELECT nvl(e1,0) as e1, nvl(e2,0) as e2, nvl(e3,0) as e3,
nvl(e4,0) as e4, nvl(e5,0) as e5, nvl(e6,0) as e6 FROM (SELECT e.
event_id as event_id, e.event_name as event_name FROM WRH$_EVENT_NAME e
 WHERE e.dbid = :dbid AND e.event_name in ('log

                          ------------------------------------------------------


LSQL ordered by Physical Reads (UnOptimized)  DB/Inst: ORCL/ORCL  Snaps: 23-24
-> UnOptimized Read Reqs = Physical Read Reqts - Optimized Read Reqs
-> %Opt   - Optimized Reads as percentage of SQL Read Requests
-> %Total - UnOptimized Read Reqs as a percentage of Total UnOptimized Read Reqs
-> Total Physical Read Requests:             790
-> Captured SQL account for  162.2% of Total
-> Total UnOptimized Read Requests:             790
-> Captured SQL account for  162.2% of Total
-> Total Optimized Read Requests:               1
-> Captured SQL account for    0.0% of Total

UnOptimized   Physical              UnOptimized
  Read Reqs   Read Reqs Executions Reqs per Exe   %Opt %Total    SQL Id
----------- ----------- ---------- ------------ ------ ------ -------------
        741         741         61         12.1    0.0   93.8 ca6tq9wk5wakf
Module: JDBC Thin Client
select * from (select name, to_char(next_time, 'YYYY/MM/DD HH24:MI:SS') as resto
rable_time, recid from sys.v_$archived_log al JOIN sys.v_$database_incarnation d
i ON di.RESETLOGS_ID = al.RESETLOGS_ID and di.STATUS = 'CURRENT' where al.name i
s NOT NULL and al.standby_dest = 'NO' AND al.archived = 'YES' AND al.thread# = 1

        540         540         60          9.0    0.0   68.4 3h0a0h5srz9t9
Module: JDBC Thin Client
select count(*) from sys.v_$datafile where status in ('RECOVER','ONLINE') and EN
ABLED != 'READ ONLY' and checkpoint_time < sysdate-(120/1440)

          8           8          1          8.0    0.0    1.0 6ajkhukk78nsr
begin prvt_hdm.auto_execute( :dbid, :inst_num , :end_snap_id ); end;

          0           0         62          0.0    N/A    0.0 0k8522rmdzg4k
select privilege# from sysauth$ where (grantee#=:1 or grantee#=1) and privilege#
>0

          0           0        240          0.0    N/A    0.0 0kqxgptj0p6rt
Module: JDBC Thin Client
SELECT count(1) FROM dba_users WHERE username = 'RDSADMIN'

          0           0          1          0.0    N/A    0.0 0pt4jfmq9f1q0
SELECT x.statistic# as stat_id, x.keh_id as keh_id, nvl(awr_time.value_diff, 0
) as value_diff FROM X$KEHTIMMAP x ,(SELECT startsn.stat_id as stat_id, sum(G
REATEST( 0, (endsn.value - startsn.value) )) as value_diff FROM WRH$_SYS_TIME_
MODEL startsn , WRH$_SYS_TIME_MODEL endsn WHERE endsn.dbid = :dbid AND ends

          0           0         67          0.0    N/A    0.0 0ws7ahf1d78qa
select SYS_CONTEXT('USERENV', 'SERVER_HOST'), SYS_CONTEXT('USERENV', 'DB_UNIQUE_
NAME'), SYS_CONTEXT('USERENV', 'INSTANCE_NAME'), SYS_CONTEXT('USERENV', 'SERVICE
_NAME'), INSTANCE_NUMBER, STARTUP_TIME, SYS_CONTEXT('USERENV', 'DB_DOMAIN') from
 v$instance where INSTANCE_NAME=SYS_CONTEXT('USERENV', 'INSTANCE_NAME')

          0           0  1,668,262          0.0    N/A    0.0 128ccsst17vwb
Module: JDBC Thin Client
update authors set email = 'toto' where id > ( select max(id) - 1 from authors)

          0           0          1          0.0    N/A    0.0 155cwuv2pfp1d
 SELECT distinct x.id, e.instance_number FROM WRM$_SNAP_ERROR e , X$KEHSQT
x WHERE e.table_name = x.name AND e.dbid = :dbid AND e.instance_number =
:inst AND e.snap_id IN (:bid, :eid) AND x.ver_type = :edge

         0           0          1          0.0    N/A    0.0 181cvj277dvuq
Module: JDBC Thin Client
select count(*) from sys.dba_triggers where owner = 'RDSADMIN' and trigger_name
= 'RDS_GRANT_TRIGGER' and status = 'ENABLED'

                          ------------------------------------------------------

^LSQL ordered by Executions                    DB/Inst: ORCL/ORCL  Snaps: 23-24
-> %CPU   - CPU Time      as a percentage of Elapsed Time
-> %IO    - User I/O Time as a percentage of Elapsed Time
-> Total Executions:       6,763,255
-> Captured SQL account for  100.0% of Total

                                              Elapsed
 Executions   Rows Processed  Rows per Exec   Time (s)  %CPU   %IO    SQL Id
------------ --------------- -------------- ---------- ----- ----- -------------
   1,668,262      16,682,570           10.0      294.0  28.2     0 128ccsst17vwb
Module: JDBC Thin Client
update authors set email = 'toto' where id > ( select max(id) - 1 from authors)

   1,668,249      16,682,340           10.0      833.4  37.7     0 21yp54r1kwdcw
Module: JDBC Thin Client
INSERT ALL INTO authors (id,name,email) VALUES ( serial.nextval ,'Priya','p@g
mail.com') INTO authors (id,name,email) VALUES ( serial.nextval ,'Priya','p@
gmail.com') INTO authors (id,name,email) VALUES ( serial.nextval ,'Priya','p
@gmail.com') INTO authors (id,name,email) VALUES ( serial.nextval ,'Priya','

   1,667,916      16,682,560           10.0      454.9  35.1     0 djyntpq5hxwpk
Module: JDBC Thin Client
delete from authors where id < ( select * from (select max(id) - 30 from author
s) a ) and id > ( select * from (select max(id) - 500 from authors) b )

   1,667,137       1,667,976            1.0      260.7  45.1     0 2fpz2m7duxb64
Module: JDBC Thin Client
select count(*) from authors where id < ( select max(id) - 30 from authors) and
 id > ( select max(id) - 2500 from authors) union select count(*) from authors
where id < ( select max(id) - 30 from authors) and id > ( select max(id) - 1500
 from authors) union select count(*) from authors where id < ( select max(id) -

      83,419          83,419            1.0        5.1  94.1     0 4m7m0t6fjcs5x
update seq$ set increment$=:2,minvalue=:3,maxvalue=:4,cycle#=:5,order$=:6,cache=
:7,highwater=:8,audit$=:9,flags=:10 where obj#=:1

       1,843           4,199            2.3        4.1  80.2     0 3p9jxd3w1hdx9
Module: rdsoracleperfmon@ip-10-13-0-252 (TNS V1-V3)
select s.sid||':'||s.serial# session_id, nvl(s.username,decode(s.type,'BACKGRO
UND','SYS')) username, s.machine, q.force_matching_signature, s.sql_id,
s.sql_hash_value, substr(q.sql_text, 1, 1000) sql_text, nvl (c.command_name,
 decode(s.wait_class,'Commit',s.wait_class, decode(s.type,'BACKGROUND', b.na

       1,242           3,782            3.0        0.3  63.9     0 cm5vu20fhtnq1
select /*+ connect_by_filtering */ privilege#,level from sysauth$ connect by gra
ntee#=prior privilege# and privilege#>0 start with grantee#=:1 and privilege#>0

         491             491            1.0        0.0  15.8     0 d3mr8mdgarrrf
Module: JDBC Thin Client
Select 1 from dual

         357             357            1.0        0.0  15.9     0 bunvx480ynf57
Module: rdsoracleperfmon@ip-10-13-0-252 (TNS V1-V3)
SELECT 1 FROM DUAL

         240             240            1.0        0.1  80.1     0 0kqxgptj0p6rt
Module: JDBC Thin Client
SELECT count(1) FROM dba_users WHERE username = 'RDSADMIN'

                          ------------------------------------------------------

^LSQL ordered by Parse Calls                   DB/Inst: ORCL/ORCL  Snaps: 23-24
-> Total Parse Calls:       6,679,380
-> Captured SQL account for   98.0% of Total

                            % Total
 Parse Calls  Executions     Parses    SQL Id
------------ ------------ --------- -------------
   1,646,465    1,668,249     24.65 21yp54r1kwdcw
Module: JDBC Thin Client
INSERT ALL INTO authors (id,name,email) VALUES ( serial.nextval ,'Priya','p@g
mail.com') INTO authors (id,name,email) VALUES ( serial.nextval ,'Priya','p@
gmail.com') INTO authors (id,name,email) VALUES ( serial.nextval ,'Priya','p
@gmail.com') INTO authors (id,name,email) VALUES ( serial.nextval ,'Priya','

   1,644,580    1,667,916     24.62 djyntpq5hxwpk
Module: JDBC Thin Client
delete from authors where id < ( select * from (select max(id) - 30 from author
s) a ) and id > ( select * from (select max(id) - 500 from authors) b )

   1,631,166    1,667,137     24.42 2fpz2m7duxb64
Module: JDBC Thin Client
select count(*) from authors where id < ( select max(id) - 30 from authors) and
 id > ( select max(id) - 2500 from authors) union select count(*) from authors
where id < ( select max(id) - 30 from authors) and id > ( select max(id) - 1500
 from authors) union select count(*) from authors where id < ( select max(id) -

   1,618,879    1,668,262     24.24 128ccsst17vwb
Module: JDBC Thin Client
update authors set email = 'toto' where id > ( select max(id) - 1 from authors)

       1,843        1,843      0.03 3p9jxd3w1hdx9
Module: rdsoracleperfmon@ip-10-13-0-252 (TNS V1-V3)
select s.sid||':'||s.serial# session_id, nvl(s.username,decode(s.type,'BACKGRO
UND','SYS')) username, s.machine, q.force_matching_signature, s.sql_id,
s.sql_hash_value, substr(q.sql_text, 1, 1000) sql_text, nvl (c.command_name,
 decode(s.wait_class,'Commit',s.wait_class, decode(s.type,'BACKGROUND', b.na

       1,242        1,242      0.02 cm5vu20fhtnq1
select /*+ connect_by_filtering */ privilege#,level from sysauth$ connect by gra
ntee#=prior privilege# and privilege#>0 start with grantee#=:1 and privilege#>0

         444          491      0.01 d3mr8mdgarrrf
Module: JDBC Thin Client
Select 1 from dual


         357          357      0.01 bunvx480ynf57
Module: rdsoracleperfmon@ip-10-13-0-252 (TNS V1-V3)
SELECT 1 FROM DUAL

         240          240      0.00 0kqxgptj0p6rt
Module: JDBC Thin Client
SELECT count(1) FROM dba_users WHERE username = 'RDSADMIN'

          86           86      0.00 g00cj285jmgsw
update sys.mon_mods$ set inserts = inserts + :ins, updates = updates + :upd, del
etes = deletes + :del, flags = (decode(bitand(flags, :flag), :flag, flags, flags
 + :flag)), drop_segments = drop_segments + :dropseg, timestamp = :time where ob
j# = :objn

                          ------------------------------------------------------

^LSQL ordered by Sharable Memory               DB/Inst: ORCL/ORCL  Snaps: 23-24

                  No data exists for this section of the report.
                          ------------------------------------------------------

^LSQL ordered by Version Count                 DB/Inst: ORCL/ORCL  Snaps: 23-24

                  No data exists for this section of the report.
                          ------------------------------------------------------

^LKey Instance Activity Stats                  DB/Inst: ORCL/ORCL  Snaps: 23-24
-> Ordered by statistic name

Statistic                                     Total     per Second     per Trans
-------------------------------- ------------------ -------------- -------------
db block changes                        128,440,171       36,004.1          31.7
execute count                             6,763,255        1,895.9           1.7
logons cumulative                                83            0.0           0.0
opened cursors cumulative                 6,762,912        1,895.8           1.7
parse count (total)                       6,679,380        1,872.4           1.7
parse time elapsed                            4,427            1.2           0.0
physical reads                                  810            0.2           0.0
physical writes                             855,813          239.9           0.2
redo size                            16,990,119,384    4,762,632.4       4,196.8
session cursor cache hits                    77,548           21.7           0.0
session logical reads                   204,307,245       57,271.0          50.5
user calls                               13,363,557        3,746.0           3.3
user commits                              4,048,312        1,134.8           1.0
workarea executions - optimal             2,384,500          668.4           0.6
                          ------------------------------------------------------



^LOther Instance Activity Stats                DB/Inst: ORCL/ORCL  Snaps: 23-24
-> Ordered by statistic name

Statistic                                     Total     per Second     per Trans
-------------------------------- ------------------ -------------- -------------
Batched IO (bound) vector count                   0            0.0           0.0
Batched IO (full) vector count                    0            0.0           0.0
Batched IO block miss count                       0            0.0           0.0
Batched IO buffer defrag count                    0            0.0           0.0
Batched IO double miss count                      0            0.0           0.0
Batched IO same unit count                        0            0.0           0.0
Batched IO single block count                     0            0.0           0.0
Batched IO vector block count                     0            0.0           0.0
Batched IO vector read count                      0            0.0           0.0
Block Cleanout Optim referenced                   3            0.0           0.0
CCursor + sql area evicted                        0            0.0           0.0
CPU used by this session                    169,854           47.6           0.0
CPU used when call started                  169,437           47.5           0.0
CR blocks created                           445,415          124.9           0.1
Cached Commit SCN referenced                  7,260            2.0           0.0
Commit SCN cached                            26,256            7.4           0.0
DBWR checkpoint buffers written             855,036          239.7           0.2
DBWR checkpoints                                155            0.0           0.0
DBWR revisited being-written buf                  3            0.0           0.0
DBWR thread checkpoint buffers w            855,036          239.7           0.2
DBWR transaction table writes                   555            0.2           0.0
DBWR undo block writes                      853,316          239.2           0.2
Effective IO time                                 0            0.0           0.0
HSC Heap Segment Block Changes           41,562,557       11,650.7          10.3
Heap Segment Array Inserts               16,684,612        4,677.0           4.1
Heap Segment Array Updates                1,045,076          293.0           0.3
IMU CR rollbacks                                262            0.1           0.0
IMU Flushes                               2,736,609          767.1           0.7
IMU Redo allocation size              7,883,745,880    2,209,954.1       1,947.4
IMU commits                               1,447,676          405.8           0.4
IMU contention                              362,320          101.6           0.1
IMU ktichg flush                                  0            0.0           0.0
IMU pool not allocated                      127,058           35.6           0.0
IMU undo allocation size             32,646,758,648    9,151,466.5       8,064.3
IMU- failed to get a private str            127,058           35.6           0.0
Number of read IOs issued                         0            0.0           0.0
Requests to/from client                   6,685,887        1,874.2           1.7
SMON posted for undo segment shr                 50            0.0           0.0
SQL*Net roundtrips to/from clien          6,685,883        1,874.2           1.7
TBS Extension: bytes extended                     0            0.0           0.0
TBS Extension: files extended                     0            0.0           0.0
TBS Extension: tasks created                      0            0.0           0.0
TBS Extension: tasks executed                     0            0.0           0.0
active txn count during cleanout            954,481          267.6           0.2
auto extends on undo tablespace                   0            0.0           0.0
background checkpoints completed                156            0.0           0.0
background checkpoints started                  155            0.0           0.0
background timeouts                          16,771            4.7           0.0
buffer is not pinned count               59,277,768       16,616.6          14.6
buffer is pinned count                   17,737,723        4,972.2           4.4
bytes received via SQL*Net from       4,388,680,667    1,230,225.2       1,084.1
bytes sent via SQL*Net to client        624,356,803      175,018.3         154.2
calls to get snapshot scn: kcmgs         11,156,856        3,127.5           2.8
calls to kcmgas                           5,845,836        1,638.7           1.4
calls to kcmgcs                          33,990,250        9,528.1           8.4
cell physical IO interconnect by     62,515,707,392   17,524,263.6      15,442.4
change write time                            42,538           11.9           0.0
cleanout - number of ktugct call          1,139,165          319.3           0.3
cleanouts and rollbacks - consis            509,091          142.7           0.1
cleanouts only - consistent read             99,170           27.8           0.0
^LOther Instance Activity Stats                DB/Inst: ORCL/ORCL  Snaps: 23-24
-> Ordered by statistic name

Statistic                                     Total     per Second     per Trans
-------------------------------- ------------------ -------------- -------------
cluster key scan block gets                     510            0.1           0.0
cluster key scans                               510            0.1           0.0
commit batch/immediate performed                  0            0.0           0.0
commit batch/immediate requested                  0            0.0           0.0
commit cleanout failures: block                   0            0.0           0.0
commit cleanout failures: buffer                  4            0.0           0.0
commit cleanout failures: callba                135            0.0           0.0
commit cleanout failures: cannot            314,740           88.2           0.1
commit cleanouts                          7,588,079        2,127.1           1.9
commit cleanouts successfully co          7,273,200        2,038.8           1.8
commit immediate performed                        0            0.0           0.0
commit immediate requested                        0            0.0           0.0
commit txn count during cleanout            360,974          101.2           0.1
consistent changes                        3,391,626          950.7           0.8
consistent gets                          80,542,873       22,577.6          19.9
consistent gets - examination             4,656,779        1,305.4           1.2
consistent gets direct                            0            0.0           0.0
consistent gets from cache               80,542,873       22,577.6          19.9
consistent gets from cache (fast         17,153,679        4,808.5           4.2
cursor authentications                           21            0.0           0.0
data blocks consistent reads - u          3,256,005          912.7           0.8
db block gets                           123,764,360       34,693.4          30.6
db block gets direct                             28            0.0           0.0
db block gets from cache                123,764,332       34,693.3          30.6
db block gets from cache (fastpa         26,902,471        7,541.2           6.7
deferred (CURRENT) block cleanou          2,600,211          728.9           0.6
enqueue conversions                           3,455            1.0           0.0
enqueue releases                         13,832,614        3,877.5           3.4
enqueue requests                         13,832,621        3,877.5           3.4
enqueue timeouts                                  2            0.0           0.0
enqueue waits                               423,596          118.7           0.1
failed probes on index block rec                  0            0.0           0.0
free buffer inspected                        10,222            2.9           0.0
free buffer requested                     2,484,063          696.3           0.6
global undo segment hints helped                  0            0.0           0.0
global undo segment hints were s                  0            0.0           0.0
heap block compress                         107,507           30.1           0.0
immediate (CR) block cleanout ap            608,261          170.5           0.2
immediate (CURRENT) block cleano             28,028            7.9           0.0
index crx upgrade (positioned)                    0            0.0           0.0
index fast full scans (full)                    995            0.3           0.0
index fetch by key                           84,969           23.8           0.0
index scans kdiixs1                      59,960,451       16,808.0          14.8
leaf node 90-10 splits                           20            0.0           0.0
leaf node splits                                 29            0.0           0.0
lob reads                                         0            0.0           0.0
lob writes                                       48            0.0           0.0
lob writes unaligned                             48            0.0           0.0
logical read bytes from cache     1,673,684,623,360  469,163,538.3     413,427.8
max cf enq hold time                              0            0.0           0.0
messages received                         2,616,086          733.3           0.7
messages sent                             2,616,086          733.3           0.7
min active SCN optimization appl                 41            0.0           0.0
no buffer to keep pinned count                    0            0.0           0.0
no work - consistent read gets              789,829          221.4           0.2
non-idle wait count                      27,096,037        7,595.5           6.7
parse count (describe)                            0            0.0           0.0
parse count (failures)                            0            0.0           0.0
parse count (hard)                               10            0.0           0.0
parse time cpu                                    9            0.0           0.0
^LOther Instance Activity Stats                DB/Inst: ORCL/ORCL  Snaps: 23-24
-> Ordered by statistic name

Statistic                                     Total     per Second     per Trans
-------------------------------- ------------------ -------------- -------------
physical read IO requests                       790            0.2           0.0
physical read bytes                       6,635,520        1,860.1           1.6
physical read total IO requests              34,302            9.6           0.0
physical read total bytes            18,875,869,184    5,291,241.5       4,662.7
physical read total multi block              17,899            5.0           0.0
physical reads cache                             30            0.0           0.0
physical reads cache prefetch                    20            0.0           0.0
physical reads direct                           780            0.2           0.0
physical reads direct (lob)                       0            0.0           0.0
physical reads direct temporary                   0            0.0           0.0
physical reads prefetch warmup                   20            0.0           0.0
physical write IO requests                  251,370           70.5           0.1
physical write bytes                  7,010,820,096    1,965,257.4       1,731.8
physical write total IO requests          3,167,748          888.0           0.8
physical write total bytes           43,639,838,208   12,233,022.1      10,779.8
physical write total multi block             57,191           16.0           0.0
physical writes direct                          808            0.2           0.0
physical writes direct (lob)                      2            0.0           0.0
physical writes direct temporary                  0            0.0           0.0
physical writes from cache                  855,005          239.7           0.2
physical writes non checkpoint              708,767          198.7           0.2
pinned cursors current                            4            0.0           0.0
process last non-idle time                    4,560            1.3           0.0
recursive calls                           5,127,204        1,437.3           1.3
recursive cpu usage                           4,180            1.2           0.0
redo KB read                             17,814,209        4,993.6           4.4
redo blocks checksummed by FG (e          7,995,007        2,241.1           2.0
redo blocks written                      35,756,793       10,023.3           8.8
redo buffer allocation retries                  410            0.1           0.0
redo entries                             49,700,791       13,932.0          12.3
redo log space requests                         691            0.2           0.0
redo ordering marks                         112,321           31.5           0.0
redo size for direct writes                 230,428           64.6           0.1
redo subscn max counts                      152,956           42.9           0.0
redo synch long waits                         1,708            0.5           0.0
redo synch time                             369,850          103.7           0.1
redo synch time (usec)                3,698,507,708    1,036,757.4         913.6
redo synch time overhead (usec)         198,391,675       55,612.7          49.0
redo synch time overhead count (                 49            0.0           0.0
redo synch time overhead count (          4,042,627        1,133.2           1.0
redo synch time overhead count (              1,634            0.5           0.0
redo synch time overhead count (              3,886            1.1           0.0
redo synch time overhead count (                  0            0.0           0.0
redo synch writes                         4,048,305        1,134.8           1.0
redo wastage                            688,387,636      192,967.3         170.0
redo write info find                      4,048,197        1,134.8           1.0
redo write info find fail                         1            0.0           0.0
redo write time                             157,587           44.2           0.0
redo writes                               2,597,619          728.2           0.6
rollback changes - undo records                   0            0.0           0.0
rollbacks only - consistent read             22,116            6.2           0.0
rows fetched via callback                       678            0.2           0.0
session connect time                              0            0.0           0.0
shared hash latch upgrades - no          56,213,811       15,757.7          13.9
shared hash latch upgrades - wai          2,307,379          646.8           0.6
sorts (memory)                            3,861,606        1,082.5           1.0
sorts (rows)                             33,399,372        9,362.4           8.3
sql area evicted                                  0            0.0           0.0
sql area purged                                   0            0.0           0.0
switch current to new buffer              1,332,200          373.4           0.3
^LOther Instance Activity Stats                DB/Inst: ORCL/ORCL  Snaps: 23-24
-> Ordered by statistic name

Statistic                                     Total     per Second     per Trans
-------------------------------- ------------------ -------------- -------------
table fetch by rowid                         99,222           27.8           0.0
table fetch continued row                         0            0.0           0.0
table scan blocks gotten                      1,681            0.5           0.0
table scan rows gotten                       76,003           21.3           0.0
table scans (short tables)                    1,110            0.3           0.0
temp space allocated (bytes)                      0            0.0           0.0
total cf enq hold time                        4,600            1.3           0.0
total number of cf enq holders                  797            0.2           0.0
total number of times SMON poste                 54            0.0           0.0
transaction rollbacks                             0            0.0           0.0
undo change vector size               6,750,240,964    1,892,212.5       1,667.4
user logons cumulative                           67            0.0           0.0
user logouts cumulative                          68            0.0           0.0
write clones created in backgrou                  2            0.0           0.0
write clones created in foregrou                454            0.1           0.0
                          ------------------------------------------------------

^LInstance Activity Stats - Absolute Values    DB/Inst: ORCL/ORCL  Snaps: 23-24
-> Statistics with absolute values (should not be diffed)

Statistic                            Begin Value       End Value
-------------------------------- --------------- ---------------
logons current                                34              33
opened cursors current                        34              34
session cursor cache count                13,647          14,165
session pga memory                   317,235,240     308,502,240
session pga memory max               395,829,960     387,817,856
session uga memory                   131,833,304     136,933,720
session uga memory max             2,274,160,264   2,395,967,584
                          ------------------------------------------------------

Instance Activity Stats - Thread Activity     DB/Inst: ORCL/ORCL  Snaps: 23-24
-> Statistics identified by '(derived)' come from sources other than SYSSTAT

Statistic                                     Total  per Hour
-------------------------------- ------------------ ---------
log switches (derived)                          155    156.42
                          ------------------------------------------------------
IOStat by Function summary                    DB/Inst: ORCL/ORCL  Snaps: 23-24
-> 'Data' columns suffixed with M,G,T,P are in multiples of 1024
    other columns suffixed with K,M,G,T,P are in multiples of 1000
-> ordered by (Data Read + Write) desc

                Reads:   Reqs   Data    Writes:  Reqs   Data    Waits:    Avg
Function Name   Data    per sec per sec Data    per sec per sec Count    Tm(ms)
--------------- ------- ------- ------- ------- ------- ------- ------- -------
Others            17.5G     7.8   5.02M     17G     5.8  4.891M   13.5K     0.1
LGWR                86M     1.6   .024M   17.1G   811.6  4.901M   2605K     0.1
DBWR                 0M     0.0      0M    6.5G    70.2  1.873M       0     N/A
Direct Reads         6M     0.2   .002M      0M     0.0      0M       0     N/A
Direct Writes        0M     0.0      0M      6M     0.2   .002M       0     N/A
Buffer Cache Re      0M     0.0      0M      0M     0.0      0M      10     0.0
TOTAL:            17.6G     9.6  5.046M   40.6G   887.9 11.666M 2618.6K     0.1
                          ------------------------------------------------------

IOStat by Filetype summary                    DB/Inst: ORCL/ORCL  Snaps: 23-24
-> 'Data' columns suffixed with M,G,T,P are in multiples of 1024
    other columns suffixed with K,M,G,T,P are in multiples of 1000
-> Small Read and Large Read are average service times, in milliseconds
-> Ordered by (Data Read + Write) desc

                Reads:   Reqs   Data    Writes:  Reqs   Data      Small   Large
Filetype Name   Data    per sec per sec Data    per sec per sec    Read    Read
--------------- ------- ------- ------- ------- ------- ------- ------- -------
Log File            17G     5.2  4.877M     17G   811.2  4.894M     0.0     3.5
Archive Log          0M     0.0      0M     17G     4.9  4.877M     N/A     N/A
Data File           10M     0.3   .003M    6.5G    70.5  1.874M     0.1     N/A
Control File       596M     4.1   .167M     76M     1.4   .021M     0.1     1.3
TOTAL:            17.6G     9.6  5.047M   40.6G   887.9 11.666M     0.1     3.5
                          ------------------------------------------------------

IOStat by Function/Filetype summary           DB/Inst: ORCL/ORCL  Snaps: 23-24
-> 'Data' columns suffixed with M,G,T,P are in multiples of 1024
    other columns suffixed with K,M,G,T,P are in multiples of 1000
-> Ordered by (Data Read + Write) desc for each function

 Reads:   Reqs   Data    Writes:  Reqs   Data    Waits:    Avg
 Data    per sec per sec Data    per sec per sec Count    Tm(ms)
 ------- ------- ------- ------- ------- ------- ------- -------
Others
   17.5G     7.8   5.02M     17G     5.8  4.891M   10.2K     0.2
 Others (Log File)
     17G     5.1  4.877M      0M     0.0      0M     621     0.0
 Others (Archive Log)
      0M     0.0      0M     17G     4.9  4.877M       0     N/A
 Others (Control File)
    509M     2.6   .143M     51M     0.9   .014M    9276     0.2
 Others (Data File)
      2M     0.1   .001M      0M     0.0      0M     305     0.4
LGWR
     86M     1.6   .024M   17.1G   811.6  4.901M    6137     0.1
 LGWR (Log File)
      0M     0.1      0M     17G   811.2  4.894M     620     0.0
 LGWR (Control File)
     86M     1.5   .024M     24M     0.4   .007M    5517     0.1
DBWR
      0M     0.0      0M    6.5G    70.3  1.874M       0     N/A
      0M     0.0      0M    6.5G    70.3  1.874M       0     N/A
 DBWR (Data File)
      0M     0.0      0M    6.5G    70.3  1.874M       0     N/A
Direct Reads
      6M     0.2   .002M      0M     0.0      0M       0     N/A
 Direct Reads (Data File)
      6M     0.2   .002M      0M     0.0      0M       0     N/A
Direct Writes
      0M     0.0      0M      6M     0.2   .002M       0     N/A
 Direct Writes (Data File)
      0M     0.0      0M      6M     0.2   .002M       0     N/A
Buffer Cache Reads
      0M     0.0      0M      0M     0.0      0M       8     0.0
 Buffer Cache Reads (Data File)
      0M     0.0      0M      0M     0.0      0M       8     0.0
TOTAL:
   17.6G     9.6  5.046M   40.6G   887.9 11.667M   16.3K     0.1
                          ------------------------------------------------------

^LTablespace IO Stats                          DB/Inst: ORCL/ORCL  Snaps: 23-24
-> ordered by IOs (Reads + Writes) desc

Tablespace
------------------------------
          Av       Av     Av      1-bk  Av 1-bk          Writes        Av    Buf
  Reads   Rds/s  Rd(ms) Blks/Rd   Rds/s  Rd(ms)  Writes   avg/s Writes(ms)    Wa
------- ------- ------- ------- ------- ------- ------- ------- ---------- -----
UNDO_T1
    156       0     0.0     1.0 2.5E+05     0.0       0      70        0.3  813,
USERS
    156       0     0.0     1.0     653     0.0       0       0        2.9 3.55E
SYSAUX
    166       0     0.0     1.1     476     0.0       0       0        2.9
SYSTEM
    156       0     0.0     1.0     229     0.0       0       0        0.4
RDSADMIN
    156       0     0.0     1.0     156     0.0       0       0        0.0
                          ------------------------------------------------------

^LFile IO Stats                                DB/Inst: ORCL/ORCL  Snaps: 23-24
-> ordered by Tablespace, File

Tablespace               Filename
------------------------ ----------------------------------------------------
          Av       Av     Av      1-bk  Av 1-bk          Writes   Buffer  Av Buf
  Reads   Rds/s  Rd(ms) Blks/Rd   Rds/s  Rd(ms)  Writes   avg/s    Waits  Wt(ms)
------- ------- ------- ------- ------- ------- ------- ------- -------- -------
RDSADMIN                 /rdsdbdata/db/ORCL_A/datafile/o1_mf_rdsadmin_g52c5fo
    156       0     0.0     1.0       0     0.0     156       0        0     0.0
SYSAUX                   /rdsdbdata/db/ORCL_A/datafile/o1_mf_sysaux_g52bly2r_
    166       0     0.0     1.1       0     0.0     476       0        0     0.0
SYSTEM                   /rdsdbdata/db/ORCL_A/datafile/o1_mf_system_g52blcs9_
    156       0     0.0     1.0       0     0.0     229       0        0     0.0
UNDO_T1                  /rdsdbdata/db/ORCL_A/datafile/o1_mf_undo_t1_g52bmb96
    156       0     0.0     1.0       0     0.0 2.5E+05      70  813,152     0.1
USERS                    /rdsdbdata/db/ORCL_A/datafile/o1_mf_users_g52bmd5h_.
    156       0     0.0     1.0       0     0.0     653       0 3.55E+06     0.1
                          ------------------------------------------------------
^LBuffer Pool Statistics                       DB/Inst: ORCL/ORCL  Snaps: 23-24
-> Standard block size Pools  D: default,  K: keep,  R: recycle
-> Default Pools for other block sizes: 2k, 4k, 8k, 16k, 32k

                                                            Free   Writ   Buffer
     Number of Pool       Buffer     Physical    Physical   Buff   Comp     Busy
P      Buffers Hit%         Gets        Reads      Writes   Wait   Wait    Waits
--- ---------- ---- ------------ ------------ ----------- ------ ------ --------
D    1,225,340  100  203,886,410           30     855,005      0      0 4.36E+06
                          ------------------------------------------------------

Checkpoint Activity                           DB/Inst: ORCL/ORCL  Snaps: 23-24
-> Total Physical Writes:                      855,813

                                          Other    Autotune      Thread
       MTTR    Log Size    Log Ckpt    Settings        Ckpt        Ckpt
     Writes      Writes      Writes      Writes      Writes      Writes
----------- ----------- ----------- ----------- ----------- -----------
          0     853,497           0           0           0       1,539
                          ------------------------------------------------------

Instance Recovery Stats                       DB/Inst: ORCL/ORCL  Snaps: 23-24
-> B: Begin Snapshot,  E: End Snapshot

                                                                            Estd
  Targt  Estd                                     Log Ckpt Log Ckpt    Opt   RAC
  MTTR   MTTR Recovery  Actual   Target   Log Sz   Timeout Interval    Log Avail
   (s)    (s) Estd IOs RedoBlks RedoBlks RedoBlks RedoBlks RedoBlks  Sz(M)  Time
- ----- ----- -------- -------- -------- -------- -------- -------- ------ -----
B     0    41    15950   663396   636984   636984  1419489      N/A    N/A   N/A
E     0    42    15615   672998   636984   636984  1472527      N/A    N/A   N/A
                          ------------------------------------------------------

MTTR Advisory                                     DB/Inst: ORCL/ORCL  Snap: 24

                  No data exists for this section of the report.
                          ------------------------------------------------------

Buffer Pool Advisory                              DB/Inst: ORCL/ORCL  Snap: 24
-> Only rows with estimated physical reads >0 are displayed
-> ordered by Block Size, Buffers For Estimate


                                    Est
                                   Phys      Estimated                  Est
    Size for   Size      Buffers   Read     Phys Reads     Est Phys %DBtime
P    Est (M) Factor  (thousands) Factor    (thousands)    Read Time for Rds
--- -------- ------ ------------ ------ -------------- ------------ -------
D        992     .1          122    1.0             19            1     5.0
D      1,984     .2          244    1.0             19            1     5.0
D      2,976     .3          366    1.0             19            1     5.0
D      3,968     .4          489    1.0             19            1     5.0
D      4,960     .5          611    1.0             19            1     5.0
D      5,952     .6          733    1.0             19            1     5.0
D      6,944     .7          855    1.0             19            1     5.0
D      7,936     .8          977    1.0             19            1     5.0
D      8,928     .9        1,099    1.0             19            1     5.0
D      9,920    1.0        1,221    1.0             19            1     5.0
D      9,952    1.0        1,225    1.0             19            1     5.0
D     10,912    1.1        1,344    1.0             19            1     5.0
D     11,904    1.2        1,466    1.0             19            1     5.0
D     12,896    1.3        1,588    1.0             19            1     5.0
D     13,888    1.4        1,710    1.0             19            1     5.0
D     14,880    1.5        1,832    1.0             19            1     5.0
D     15,872    1.6        1,954    1.0             19            1     5.0
D     16,864    1.7        2,076    1.0             19            1     5.0
D     17,856    1.8        2,199    1.0             19            1     5.0
D     18,848    1.9        2,321    1.0             19            1     5.0
D     19,840    2.0        2,443    1.0             19            1     5.0
                          ------------------------------------------------------

^LPGA Aggr Summary                             DB/Inst: ORCL/ORCL  Snaps: 23-24
-> PGA cache hit % - percentage of W/A (WorkArea) data processed only in-memory

PGA Cache Hit %   W/A MB Processed  Extra W/A MB Read/Written
--------------- ------------------ --------------------------
          100.0              4,577                          0
                          ------------------------------------------------------

PGA Aggr Target Stats                         DB/Inst: ORCL/ORCL  Snaps: 23-24
-> B: Begin Snap   E: End Snap (rows dentified with B or E contain data
   which is absolute i.e. not diffed over the interval)
-> Auto PGA Target - actual workarea memory target
-> W/A PGA Used    - amount of memory used for all Workareas (manual + auto)
-> %PGA W/A Mem    - percentage of PGA memory allocated to workareas
-> %Auto W/A Mem   - percentage of workarea memory controlled by Auto Mem Mgmt
-> %Man W/A Mem    - percentage of workarea memory under manual control

                                                %PGA  %Auto   %Man
    PGA Aggr   Auto PGA   PGA Mem    W/A PGA     W/A    W/A    W/A Global Mem
   Target(M)  Target(M)  Alloc(M)    Used(M)     Mem    Mem    Mem   Bound(K)
- ---------- ---------- ---------- ---------- ------ ------ ------ ----------
B      1,944      1,551      265.3        0.0     .0     .0     .0    199,080
E      1,944      1,558      260.0        0.0     .0     .0     .0    199,080
                          ------------------------------------------------------

PGA Aggr Target Histogram                     DB/Inst: ORCL/ORCL  Snaps: 23-24
-> Optimal Executions are purely in-memory operations

  Low     High
Optimal Optimal    Total Execs  Optimal Execs 1-Pass Execs M-Pass Execs
------- ------- -------------- -------------- ------------ ------------
     2K      4K      2,325,896      2,325,896            0            0
    64K    128K              8              8            0            0
   512K   1024K             19             19            0            0
     1M      2M             14             14            0            0
     4M      8M              2              2            0            0
                          ------------------------------------------------------

PGA Memory Advisory                               DB/Inst: ORCL/ORCL  Snap: 24
-> When using Auto Memory Mgmt, minimally choose a pga_aggregate_target value
   where Estd PGA Overalloc Count is 0

                                       Estd Extra    Estd P Estd PGA
PGA Target    Size           W/A MB   W/A MB Read/    Cache Overallo    Estd
  Est (MB)   Factr        Processed Written to Disk   Hit %    Count    Time
---------- ------- ---------------- ---------------- ------ -------- -------
       243     0.1         25,397.1              0.0  100.0        0 1.0E+07
       486     0.3         25,397.1              0.0  100.0        0 1.0E+07
       972     0.5         25,397.1              0.0  100.0        0 1.0E+07
     1,458     0.8         25,397.1              0.0  100.0        0 1.0E+07
     1,944     1.0         25,397.1              0.0  100.0        0 1.0E+07
     2,333     1.2         25,397.1              0.0  100.0        0 1.0E+07
     2,722     1.4         25,397.1              0.0  100.0        0 1.0E+07
     3,111     1.6         25,397.1              0.0  100.0        0 1.0E+07
     3,500     1.8         25,397.1              0.0  100.0        0 1.0E+07
     3,888     2.0         25,397.1              0.0  100.0        0 1.0E+07
     5,833     3.0         25,397.1              0.0  100.0        0 1.0E+07
     7,777     4.0         25,397.1              0.0  100.0        0 1.0E+07
    11,665     6.0         25,397.1              0.0  100.0        0 1.0E+07
    15,554     8.0         25,397.1              0.0  100.0        0 1.0E+07
                          ------------------------------------------------------

^LShared Pool Advisory                             DB/Inst: ORCL/ORCL  Snap: 24
-> SP: Shared Pool     Est LC: Estimated Library Cache   Factr: Factor
-> Note there is often a 1:Many correlation between a single logical object
   in the Library Cache, and the physical number of memory objects associated
   with it.  Therefore comparing the number of Lib Cache objects (e.g. in
   v$librarycache), with the number of Lib Cache Memory Objects is invalid.


                                       Est LC Est LC  Est LC Est LC
  Shared    SP   Est LC                  Time   Time    Load   Load       Est LC
    Pool  Size     Size       Est LC    Saved  Saved    Time   Time      Mem Obj
 Size(M) Factr      (M)      Mem Obj      (s)  Factr     (s)  Factr     Hits (K)
-------- ----- -------- ------------ -------- ------ ------- ------ ------------
     480    .3       32        2,943   65,509    1.0     332    1.2       31,482
     640    .4      156        9,042   65,512    1.0     329    1.2       31,484
     800    .5      156        9,042   65,512    1.0     329    1.2       31,484
     960    .6      156        9,042   65,512    1.0     329    1.2       31,484
   1,120    .7      156        9,042   65,512    1.0     329    1.2       31,484
   1,280    .8      156        9,042   65,512    1.0     329    1.2       31,484
   1,312    .8      156        9,042   65,512    1.0     329    1.2       31,484
   1,344    .8      156        9,042   65,512    1.0     329    1.2       31,484
   1,376    .9      156        9,042   65,512    1.0     329    1.2       31,484
   1,408    .9      156        9,042   65,512    1.0     329    1.2       31,484
   1,440    .9      156        9,042   65,512    1.0     329    1.2       31,484
   1,472    .9      156        9,042   65,512    1.0     329    1.2       31,484
   1,504    .9      156        9,042   65,517    1.0     324    1.1       31,486
   1,536   1.0      156        9,042   65,529    1.0     312    1.1       31,489
   1,568   1.0      156        9,042   65,543    1.0     298    1.0       31,492
   1,600   1.0      156        9,042   65,557    1.0     284    1.0       31,512
   1,632   1.0      156        9,042   65,557    1.0     284    1.0       31,512
   1,664   1.0      156        9,042   65,557    1.0     284    1.0       31,512
   1,696   1.1      156        9,042   65,557    1.0     284    1.0       31,512
   1,728   1.1      156        9,042   65,557    1.0     284    1.0       31,512
   1,760   1.1      156        9,042   65,557    1.0     284    1.0       31,512
   1,792   1.1      156        9,042   65,557    1.0     284    1.0       31,512
   1,824   1.1      156        9,042   65,557    1.0     284    1.0       31,512
   1,856   1.2      156        9,042   65,557    1.0     284    1.0       31,512
   1,888   1.2      156        9,042   65,557    1.0     284    1.0       31,512
   1,920   1.2      156        9,042   65,557    1.0     284    1.0       31,512
   2,080   1.3      156        9,042   65,557    1.0     284    1.0       31,512
   2,240   1.4      156        9,042   65,557    1.0     284    1.0       31,512
   2,400   1.5      156        9,042   65,557    1.0     284    1.0       31,512
   2,560   1.6      156        9,042   65,557    1.0     284    1.0       31,512
   2,720   1.7      156        9,042   65,557    1.0     284    1.0       31,512
   2,880   1.8      156        9,042   65,557    1.0     284    1.0       31,512
   3,040   1.9      156        9,042   65,557    1.0     284    1.0       31,512
   3,200   2.0      156        9,042   65,557    1.0     284    1.0       31,512
                          ------------------------------------------------------

SGA Target Advisory                               DB/Inst: ORCL/ORCL  Snap: 24
SGA Target   SGA Size       Est DB     Est Physical
  Size (M)     Factor     Time (s)            Reads
---------- ---------- ------------ ----------------
     2,920        0.3       33,117           19,342
     4,380        0.4       33,117           19,280
     5,840        0.5       33,117           19,157
     7,300        0.6       33,118           18,874
     8,760        0.8       33,118           18,874
    10,220        0.9       33,118           18,874
    11,680        1.0       33,117           18,874
    13,140        1.1       33,118           18,874
    14,600        1.3       33,118           18,874
    16,060        1.4       33,118           18,874
    17,520        1.5       33,122           18,874
    18,980        1.6       33,122           18,874
    20,440        1.8       33,122           18,874
    21,900        1.9       33,122           18,874
    23,360        2.0       33,122           18,874
                          ------------------------------------------------------

Streams Pool Advisory                             DB/Inst: ORCL/ORCL  Snap: 24

                  No data exists for this section of the report.
                          ------------------------------------------------------

Java Pool Advisory                                DB/Inst: ORCL/ORCL  Snap: 24

                  No data exists for this section of the report.
                          ------------------------------------------------------

Buffer Wait Statistics                        DB/Inst: ORCL/ORCL  Snaps: 23-24
-> ordered by wait time desc, waits desc

Class                    Waits Total Wait Time (s)  Avg Time (ms)
------------------ ----------- ------------------- --------------
data block           3,537,315                 287              0
undo block             779,085                  48              0
undo header             34,065                   2              0
1st level bmb           12,393                   1              0
                          ------------------------------------------------------

^LEnqueue Activity                             DB/Inst: ORCL/ORCL  Snaps: 23-24
-> only enqueues with waits are shown
-> Enqueue stats gathered prior to 10g should not be compared with 10g data
-> ordered by Wait Time desc, Waits desc

Enqueue Type (Request Reason)
------------------------------------------------------------------------------
    Requests    Succ Gets Failed Gets       Waits  Wt Time (s) Av Wt Time(ms)
------------ ------------ ----------- ----------- ------------ --------------
TX-Transaction (row lock contention)
     461,060      461,055           0     412,330           70            .17
SQ-Sequence Cache
      94,726       94,726           0      11,266            2            .21
                          ------------------------------------------------------
^LUndo Segment Summary                         DB/Inst: ORCL/ORCL  Snaps: 23-24
-> Min/Max TR (mins) - Min and Max Tuned Retention (minutes)
-> STO - Snapshot Too Old count,  OOS - Out of Space count
-> Undo segment block stats:
-> uS - unexpired Stolen,   uR - unexpired Released,   uU - unexpired reUsed
-> eS - expired   Stolen,   eR - expired   Released,   eU - expired   reUsed

Undo   Num Undo       Number of  Max Qry   Max Tx Min/Max   STO/     uS/uR/uU/
 TS# Blocks (K)    Transactions  Len (s) Concurcy TR (mins) OOS      eS/eR/eU
---- ---------- --------------- -------- -------- --------- ----- --------------
   2      865.8       4,195,091    1,278        5 15.1/35.3 0/0   0/0/0/0/0/0
                          ------------------------------------------------------

Undo Segment Stats                            DB/Inst: ORCL/ORCL  Snaps: 23-24
-> Most recent 35 Undostat rows, ordered by Time desc

                Num Undo    Number of Max Qry  Max Tx Tun Ret STO/    uS/uR/uU/
End Time          Blocks Transactions Len (s)   Concy  (mins) OOS     eS/eR/eU
------------ ----------- ------------ ------- ------- ------- ----- ------------
26-Apr 21:57     144,285      699,750      66       5      15 0/0   0/0/0/0/0/0
26-Apr 21:47     144,099      697,502     670       5      25 0/0   0/0/0/0/0/0
26-Apr 21:37     142,931      692,838      70       5      15 0/0   0/0/0/0/0/0
26-Apr 21:27     142,850      691,432     673       5      25 0/0   0/0/0/0/0/0
26-Apr 21:17     143,712      696,514   1,278       5      35 0/0   0/0/0/0/0/0
26-Apr 21:07     147,945      717,055     677       5      25 0/0   0/0/0/0/0/0
                          ------------------------------------------------------

^LLatch Activity                               DB/Inst: ORCL/ORCL  Snaps: 23-24
-> "Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for
   willing-to-wait latch get requests
-> "NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests
-> "Pct Misses" for both should be very close to 0.0

                                           Pct    Avg   Wait                 Pct
                                    Get    Get   Slps   Time       NoWait NoWait
Latch Name                     Requests   Miss  /Miss    (s)     Requests   Miss
------------------------ -------------- ------ ------ ------ ------------ ------
AQ deq hash table latch               1    0.0             0            0    N/A
ASM db client latch               3,251    0.0             0            0    N/A
ASM map operation hash t              1    0.0             0            0    N/A
ASM network state latch              80    0.0             0            0    N/A
AWR Alerted Metric Eleme         25,303    0.0             0            0    N/A
Change Notification Hash          1,190    0.0             0            0    N/A
Consistent RBA                2,598,555    0.0    0.1      0            0    N/A
DML lock allocation          10,178,082    0.0    0.0      0            0    N/A
Event Group Locks                   167    0.0             0            0    N/A
FAL Queue                           452    0.0             0            0    N/A
FIB s.o chain latch                 932    0.0             0            0    N/A
FOB s.o list latch                1,029    0.0             0            0    N/A
File State Object Pool P              1    0.0             0            0    N/A
I/O Staticstics latch                 1    0.0             0            0    N/A
IPC stats buffer allocat              1    0.0             0            0    N/A
In memory undo latch         30,946,670    1.6    0.0      2    5,005,394    0.0
JS Sh mem access                      1    0.0             0            0    N/A
JS queue access latch                 1    0.0             0            0    N/A
JS slv state obj latch                1    0.0             0            0    N/A
KFC FX Hash Latch                     1    0.0             0            0    N/A
KFC Hash Latch                        1    0.0             0            0    N/A
KFCL LE Freelist                      1    0.0             0            0    N/A
KGNFS-NFS:SHM structure               1    0.0             0            0    N/A
KGNFS-NFS:SVR LIST                    1    0.0             0            0    N/A
KJC message pool free li              1    0.0             0            0    N/A
KJCT flow control latch               1    0.0             0            0    N/A
KMG MMAN ready and start          1,189    0.0             0            0    N/A
KTF sga latch                        18    0.0             0        1,193    0.0
Locator state objects po              1    0.0             0            0    N/A
Lsod array latch                      1    0.0             0            0    N/A
MQL Tracking Latch                    0    N/A             0           72    0.0
Memory Management Latch               1    0.0             0        1,189    0.0
Memory Queue                          1    0.0             0            0    N/A
Memory Queue Message Sub              1    0.0             0            0    N/A
Memory Queue Message Sub              1    0.0             0            0    N/A
Memory Queue Message Sub              1    0.0             0            0    N/A
Memory Queue Message Sub              1    0.0             0            0    N/A
Memory Queue Subscriber               1    0.0             0            0    N/A
MinActiveScn Latch                   40    0.0             0            0    N/A
Mutex                                 1    0.0             0            0    N/A
Mutex Stats                           1    0.0             0            0    N/A
OS process                          416    0.0             0            0    N/A
OS process allocation             7,335    0.0             0            0    N/A
OS process: request allo            167    0.0             0            0    N/A
PL/SQL warning settings              85    0.0             0            0    N/A
PX hash array latch                   1    0.0             0            0    N/A
QMT                                   1    0.0             0            0    N/A
Real-time plan statistic            348    0.0             0            0    N/A
Result Cache: RC Latch        8,096,606    0.0    0.0      0            0    N/A
SGA IO buffer pool latch         10,670    0.0             0       13,450    0.0
SGA blob parent                       1    0.0             0            0    N/A
SGA bucket locks                      1    0.0             0            0    N/A
SGA heap locks                        1    0.0             0            0    N/A
SGA pool locks                        1    0.0             0            0    N/A
SQL memory manager latch              1    0.0             0        1,188    0.0
SQL memory manager worka         86,600    0.0             0            0    N/A
Shared B-Tree                       129    0.0             0            0    N/A
Streams Generic                       1    0.0             0            0    N/A
Testing                               1    0.0             0            0    N/A
Token Manager                         1    0.0             0            0    N/A
^LLatch Activity                               DB/Inst: ORCL/ORCL  Snaps: 23-24
-> "Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for
   willing-to-wait latch get requests
-> "NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests
-> "Pct Misses" for both should be very close to 0.0

                                           Pct    Avg   Wait                 Pct
                                    Get    Get   Slps   Time       NoWait NoWait
Latch Name                     Requests   Miss  /Miss    (s)     Requests   Miss
------------------------ -------------- ------ ------ ------ ------------ ------
WCR: sync                             1    0.0             0            0    N/A
Write State Object Pool               1    0.0             0            0    N/A
X$KSFQP                               1    0.0             0            0    N/A
XDB NFS Security Latch                1    0.0             0            0    N/A
XDB unused session pool               1    0.0             0            0    N/A
XDB used session pool                 1    0.0             0            0    N/A
active checkpoint queue          15,651    0.0             0            0    N/A
active service list               2,018    0.0             0        1,639    0.0
alert log latch                     310    0.0             0            0    N/A
archive control                     648    0.0             0            0    N/A
archive process latch             1,511    0.0             0            0    N/A
begin backup scn array                4    0.0             0            0    N/A
buffer pool                           1    0.0             0            0    N/A
business card                         1    0.0             0            0    N/A
cache buffer handles         36,706,018    0.3    0.0      0            0    N/A
cache buffers chains        678,148,703    0.9    0.0      4    1,103,253    0.9
cache buffers lru chain       4,081,822    0.0    0.0      0    3,969,584    0.1
call allocation                     382    0.0             0            0    N/A
cas latch                             1    0.0             0            0    N/A
change notification clie              1    0.0             0            0    N/A
channel handle pool latc            171    0.0             0            0    N/A
channel operations paren         24,232    0.0    0.0      0            0    N/A
checkpoint queue latch        2,390,524    0.0    0.1      0      854,563    0.0
client/application info             481    0.0             0            0    N/A
compile environment latc             83    0.0             0            0    N/A
cp cmon/server latch                  1    0.0             0            0    N/A
cp pool latch                         1    0.0             0            0    N/A
cp server hash latch                  1    0.0             0            0    N/A
cp sga latch                         80    0.0             0            0    N/A
cvmap freelist lock                   1    0.0             0            0    N/A
deferred cleanup latch               80    0.0             0            0    N/A
dml lock allocation                  80    0.0             0            0    N/A
done queue latch                      1    0.0             0            0    N/A
dummy allocation                    168    0.0             0            0    N/A
eighth spare latch - X p              1    0.0             0            0    N/A
eleventh spare latch - c              1    0.0             0            0    N/A
enqueue freelist latch              177    6.2    0.0      0    9,031,974    0.0
enqueue hash chains          28,147,972    0.9    0.0      0            5    0.0
enqueues                            361    0.0             0            0    N/A
fifteenth spare latch -               1    0.0             0            0    N/A
file cache latch                  1,536    0.0             0            0    N/A
first Audit Vault latch              62    0.0             0            0    N/A
flashback copy                        1    0.0             0            0    N/A
fourteenth spare latch -              1    0.0             0            0    N/A
fourth Audit Vault latch              1    0.0             0            0    N/A
gc element                            1    0.0             0            0    N/A
gcs commit scn state                  1    0.0             0            0    N/A
gcs partitioned table ha              1    0.0             0            0    N/A
gcs pcm hashed value buc              1    0.0             0            0    N/A
gcs resource freelist                 1    0.0             0            0    N/A
gcs resource hash                     1    0.0             0            0    N/A
gcs resource scan list                1    0.0             0            0    N/A
gcs resource validate li              1    0.0             0            0    N/A
gcs shadows freelist                  1    0.0             0            0    N/A
ges domain table                      1    0.0             0            0    N/A
ges enqueue table freeli              1    0.0             0            0    N/A
ges group table                       1    0.0             0            0    N/A
ges process hash list                 1    0.0             0            0    N/A
ges process parent latch              1    0.0             0            0    N/A
ges resource hash list                1    0.0             0            0    N/A
^LLatch Activity                               DB/Inst: ORCL/ORCL  Snaps: 23-24
-> "Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for
   willing-to-wait latch get requests
-> "NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests
-> "Pct Misses" for both should be very close to 0.0

                                   Get    Get   Slps   Time       NoWait NoWait
Latch Name                     Requests   Miss  /Miss    (s)     Requests   Miss
------------------------ -------------- ------ ------ ------ ------------ ------
ges resource scan list                1    0.0             0            0    N/A
ges resource table freel              1    0.0             0            0    N/A
ges value block free lis              1    0.0             0            0    N/A
global KZLD latch for me             62    0.0             0            0    N/A
global tx hash mapping                1    0.0             0            0    N/A
granule operation                     1    0.0             0            0    N/A
hash table column usage              24    0.0             0          187    0.0
hash table modification              74    0.0             0            0    N/A
heartbeat check                       1    0.0             0            0    N/A
internal temp table obje              1    0.0             0            0    N/A
intra txn parallel recov              1    0.0             0            0    N/A
io pool granule metadata              1    0.0             0            0    N/A
job workq parent latch                1    0.0             0            0    N/A
k2q lock allocation                   1    0.0             0            0    N/A
kcbtsemkid latch                    155    0.0             0            0    N/A
kdlx hb parent latch                  1    0.0             0            0    N/A
kgb parent                            1    0.0             0            0    N/A
kgnfs mount latch                     1    0.0             0            0    N/A
kokc descriptor allocati              4    0.0             0            0    N/A
ksfv messages                         1    0.0             0            0    N/A
ksim group membership ca              1    0.0             0            0    N/A
kss move lock                        33    0.0             0            0    N/A
ksuosstats global area              360    0.0             0            0    N/A
ksv allocation latch                144    0.0             0            0    N/A
ksv class latch                      65    0.0             0            0    N/A
ksv msg queue latch                   1    0.0             0            0    N/A
ksz_so allocation latch             167    0.0             0            0    N/A
ktm global data                     246    0.0             0            0    N/A
kwqbsn:qsga                         128    0.0             0            0    N/A
lgwr LWN SCN                  2,602,992    0.0    0.0      0            0    N/A
list of block allocation             28    0.0             0            0    N/A
loader state object free          1,882    0.0             0            0    N/A
lob segment dispenser la              1    0.0             0            0    N/A
lob segment hash table l             13    0.0             0            0    N/A
lob segment query latch               1    0.0             0            0    N/A
lock DBA buffer during m              1    0.0             0            0    N/A
logical standby cache                 1    0.0             0            0    N/A
logminer context allocat              1    0.0             0            0    N/A
logminer local                        1    0.0             0            0    N/A
logminer work area                    1    0.0             0            0    N/A
longop free list parent               1    0.0             0            0    N/A
managed standby latch               297    0.0             0            0    N/A
mapped buffers lru chain              1    0.0             0            0    N/A
message pool operations           2,164    0.0             0            0    N/A
messages                      7,779,582    0.0    0.0      0            0    N/A
mostly latch-free SCN         2,620,722    0.2    0.0      0            0    N/A
msg queue latch                       1    0.0             0            0    N/A
multiblock read objects              10    0.0             0            0    N/A
name-service namespace b              1    0.0             0            0    N/A
ncodef allocation latch              80    0.0             0            0    N/A
nineth spare latch - X p              1    0.0             0            0    N/A
object queue header heap         30,131    0.0             0            2    0.0
object queue header oper      5,367,223    0.0    0.0      0            0    N/A
object stats modificatio              4    0.0             0            0    N/A
parallel query alloc buf            461    0.0             0            0    N/A
parallel query stats                  1    0.0             0            0    N/A
parameter list                       15    0.0             0            0    N/A
parameter table manageme            291    0.0             0            0    N/A
peshm                                 1    0.0             0            0    N/A
pesom_free_list                       1    0.0             0            0    N/A
^LLatch Activity                               DB/Inst: ORCL/ORCL  Snaps: 23-24
-> "Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for
   willing-to-wait latch get requests
-> "NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests
-> "Pct Misses" for both should be very close to 0.0

                                           Pct    Avg   Wait                 Pct
                                    Get    Get   Slps   Time       NoWait NoWait
Latch Name                     Requests   Miss  /Miss    (s)     Requests   Miss
------------------------ -------------- ------ ------ ------ ------------ ------
pesom_hash_node                       1    0.0             0            0    N/A
post/wait queue               6,170,847    0.0    0.0      0    5,567,423    0.1
process allocation                  183    0.0             0           83    0.0
process group creation              167    0.0             0            0    N/A
process queue                         1    0.0             0            0    N/A
process queue reference               1    0.0             0            0    N/A
qmn task queue latch                521    0.0             0            0    N/A
query server freelists                1    0.0             0            0    N/A
queued dump request                  12    0.0             0            0    N/A
queuing load statistics               1    0.0             0            0    N/A
recovery domain hash lis              1    0.0             0            0    N/A
redo allocation              18,172,705    0.2    0.0      0   49,615,134    0.6
redo copy                             1    0.0             0   49,702,067    0.8
redo writing                  8,104,776    0.0    0.0      0            0    N/A
resmgr group change latc             72    0.0             0            0    N/A
resmgr:active threads               168    0.0             0            0    N/A
resmgr:actses change gro             84    0.0             0            0    N/A
resmgr:actses change sta              1    0.0             0            0    N/A
resmgr:free threads list            167    0.0             0            0    N/A
resmgr:plan CPU method                1    0.0             0            0    N/A
resmgr:resource group CP              1    0.0             0            0    N/A
resmgr:schema config                 11    0.0             0            0    N/A
resmgr:session queuing                1    0.0             0            0    N/A
rm cas latch                          1    0.0             0            0    N/A
row cache objects               635,859    0.0             0            0    N/A
second Audit Vault latch              1    0.0             0            0    N/A
sequence cache                5,183,895    1.6    0.0      0            0    N/A
session allocation            5,005,761    0.1    0.0      0    5,005,623    0.3
session idle bit             31,732,642    0.3    0.0      0            0    N/A
session queue latch                   1    0.0             0            0    N/A
session state list latch            174    0.0             0            0    N/A
session switching                   165    0.0             0            0    N/A
session timer                     1,208    0.0             0            0    N/A
seventh spare latch - X               1    0.0             0            0    N/A
shared pool                      29,044    0.0             0            0    N/A
shared pool sim alloc                 2    0.0             0            0    N/A
shared pool simulator                14    0.0             0            0    N/A
sim partition latch                   1    0.0             0            0    N/A
simulator hash latch          1,264,761    0.0             0            0    N/A
simulator lru latch             855,006    0.0    0.0      0      409,747    0.0
sixth spare latch - X pa              1    0.0             0            0    N/A
sort extent pool                    165    0.0             0            0    N/A
space background state o             10    0.0             0            0    N/A
space background task la          4,363    0.2    0.0      0        2,381    0.0
state object free list                2    0.0             0            0    N/A
statistics aggregation              336    0.0             0            0    N/A
tablespace key chain            852,982    0.0    0.0      0            0    N/A
temp lob duration state               2    0.0             0            0    N/A
tenth spare latch - X pa              1    0.0             0            0    N/A
test excl. parent l0                  1    0.0             0            0    N/A
test excl. parent2 l0                 1    0.0             0            0    N/A
thirteenth spare latch -              1    0.0             0            0    N/A
threshold alerts latch              129    0.0             0            0    N/A
transaction allocation               43    0.0             0            0    N/A
twelfth spare latch - ch              1    0.0             0            0    N/A
twenty-fifth spare latch              1    0.0             0            0    N/A
twenty-first spare latch              1    0.0             0            0    N/A
twenty-fourth spare latc              1    0.0             0            0    N/A
twenty-second spare latc              1    0.0             0            0    N/A
twenty-third spare latch              1    0.0             0            0    N/A
^LLatch Activity                               DB/Inst: ORCL/ORCL  Snaps: 23-24
-> "Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for
   willing-to-wait latch get requests
-> "NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests
-> "Pct Misses" for both should be very close to 0.0

                                           Pct    Avg   Wait                 Pct
                                    Get    Get   Slps   Time       NoWait NoWait
Latch Name                     Requests   Miss  /Miss    (s)     Requests   Miss
------------------------ -------------- ------ ------ ------ ------------ ------
undo global data             19,869,354    0.2    0.0      0            0    N/A
virtual circuit buffers               1    0.0             0            0    N/A
virtual circuit holder                1    0.0             0            0    N/A
virtual circuit queues                1    0.0             0            0    N/A
write info latch                      0    N/A             0    2,597,776    0.0
                          ------------------------------------------------------

^LLatch Sleep Breakdown                        DB/Inst: ORCL/ORCL  Snaps: 23-24
-> ordered by misses desc

                                       Get                                 Spin
Latch Name                        Requests       Misses      Sleeps        Gets
-------------------------- --------------- ------------ ----------- -----------
cache buffers chains           678,148,703    6,039,532       9,920   6,030,019
In memory undo latch            30,946,670      498,440       3,120     495,577
enqueue hash chains             28,147,972      254,691          77     254,625
cache buffer handles            36,706,018      102,104           8     102,096
sequence cache                   5,183,895       84,508          24      84,484
session idle bit                31,732,642       80,214          15      80,199
undo global data                19,869,354       48,471          40      48,447
redo allocation                 18,172,705       41,799         177      41,636
mostly latch-free SCN            2,620,722        5,427           5       5,422
session allocation               5,005,761        5,030           1       5,029
messages                         7,779,582        3,026           5       3,021
post/wait queue                  6,170,847        1,008           2       1,006
object queue header operat       5,367,223          258           1         257
cache buffers lru chain          4,081,822          221           5         216
checkpoint queue latch           2,390,524           27           4          23
Consistent RBA                   2,598,555           10           1           9
                          ------------------------------------------------------

^LLatch Miss Sources                           DB/Inst: ORCL/ORCL  Snaps: 23-24
-> only latches with sleeps are shown
-> ordered by name, sleeps desc
Latch Name               Where                       Misses     Sleeps   Sleeps
------------------------ -------------------------- ------- ---------- --------
In memory undo latch     kticmt: child                    0      2,468        2
In memory undo latch     ktichg: child                    0        486        7
In memory undo latch     ktiFlushMe                       0        155        1
In memory undo latch     ktiFlush: child                  0         18    1,037
In memory undo latch     ktiTxnPoolFree                   0          4        0
cache buffer handles     kcbzfs                           0          8        3
cache buffers chains     kcbgcur_2                        0      4,881       49
cache buffers chains     kcbgtcr: fast path               0      3,079    7,957
cache buffers chains     kcbgtcr_2                        0        997      151
cache buffers chains     kcbrls_2                         0        750       16
cache buffers chains     kcbgcur: fast path (shr)         0        651    2,711
cache buffers chains     kcbchg1: clear MS bit            0        409        4
cache buffers chains     kcbchg1: mod cur pin             0        248       26
cache buffers chains     kcbzwb                           0        191      135
cache buffers chains     kcbchg1: mod cr pin              0        160       26
cache buffers chains     kcbnlc                           0        155        4
cache buffers chains     kcbchg1: aux pin                 0        112        0
cache buffers chains     kcbrls_1                         0         61       25
cache buffers chains     kcbso1: set no access            0         54      230
cache buffers chains     kcbrls: fast release             0         53       46
cache buffers chains     kcbgtcr: fast path (cr pin       0         40       51
cache buffers chains     kcbgcur_4                        0         25       25
cache buffers chains     kcbzgb: scan from tail. no       0         13        0
cache buffers chains     kcb_pre_apply: kcbhq61           0          7        1
cache buffers chains     kcb_commit                       0          6        0
cache buffers chains     kcbzpbuf                         0          6        7
cache buffers chains     kcb_post_apply: kcbhq62          0          4        0
cache buffers chains     kcbcge                           0          4       18
cache buffers chains     kcb_trim_hash_chain              0          3        0
cache buffers chains     kcbgtcr: kslbegin excl           0          3        0
cache buffers chains     kcb_is_private                   0          2      432
cache buffers lru chain  kcbzgws                          0          5        0
checkpoint queue latch   kcbbwthc: thread checkpoin       0          3        1
checkpoint queue latch   kcbswcu: Switch buffers          0          1        3
enqueue hash chains      ksqrcl                           0         40       21
enqueue hash chains      ksqgtl3                          0         36       56
enqueue hash chains      ksqcmi: get hash chain lat       0          1        0
lgwr LWN SCN             kcs023                           0          5        0
messages                 ksaamb: after wakeup             0          4        1
messages                 ksarcv                           0          1       14
mostly latch-free SCN    kcsnew_scn_rba                   0          1        0
object queue header oper kcbo_switch_q_bg                 0          1        0
post/wait queue          ksliwat:add:nowait               0          2        0
redo allocation          kcrfw_redo_gen: redo alloc       0        159        0
redo allocation          kcrfw_redo_write: before w       0         13       22
redo allocation          kcrfw_redo_gen: redo alloc       0          3       67
redo allocation          kcrfw_post: more space           0          2       88
sequence cache           kdnssd                           0         20        1
sequence cache           kdnnxt: cached seq               0          3       15
sequence cache           kdnss                            0          1        8
session allocation       ksucri_int : SSO                 0          1        0
session idle bit         ksupuc: set busy                 0          8       17
session idle bit         ksupuc: clear busy               0          6        0
session idle bit         ksuxds                           0          3        0
undo global data         ktudnx:child                     0         19        3
undo global data         ktufrbs_2                        0         15       21
undo global data         ktudba: KSLBEGIN                 0          5       16
undo global data         ktubnd_4                         0          1        0
                          ------------------------------------------------------

Mutex Sleep Summary                           DB/Inst: ORCL/ORCL  Snaps: 23-24
-> ordered by number of sleeps desc

                                                                         Wait
Mutex Type            Location                               Sleeps    Time (ms)
--------------------- -------------------------------- ------------ ------------
Library Cache         kglpin1   4                                33            0
Cursor Pin            kksfbc [KKSCHLPIN1]                        29            0
Library Cache         kglpndl1  95                                7            0
Library Cache         kglpnal1  90                                6            0
Cursor Pin            kksLockDelete [KKSCHLPIN6]                  5            0
                          ------------------------------------------------------

^LParent Latch Statistics                      DB/Inst: ORCL/ORCL  Snaps: 23-24

                  No data exists for this section of the report.
                          ------------------------------------------------------

Child Latch Statistics                        DB/Inst: ORCL/ORCL  Snaps: 23-24

                  No data exists for this section of the report.
                          ------------------------------------------------------

^LSegments by Logical Reads                    DB/Inst: ORCL/ORCL  Snaps: 23-24
-> Total Logical Reads:     204,307,245
-> Captured Segments account for   91.9% of Total

           Tablespace                      Subobject  Obj.       Logical
Owner         Name    Object Name            Name     Type         Reads  %Total
---------- ---------- -------------------- ---------- ----- ------------ -------
KYLELF     USERS      AUTHORS_I                       INDEX   93,967,584   45.99
KYLELF     USERS      AUTHORS                         TABLE   93,574,368   45.80
SYS        SYSTEM     I_SEQ1                          INDEX       85,200     .04
SYS        SYSTEM     SEQ$                            TABLE       83,408     .04
SYS        SYSTEM     I_SYSAUTH1                      INDEX        8,960     .00
                          ------------------------------------------------------

Segments by Physical Reads                    DB/Inst: ORCL/ORCL  Snaps: 23-24
-> Total Physical Reads:             810
-> Captured Segments account for    3.7% of Total

           Tablespace                      Subobject  Obj.      Physical
Owner         Name    Object Name            Name     Type         Reads  %Total
---------- ---------- -------------------- ---------- ----- ------------ -------
SYS        SYSAUX     WRH$_ACTIVE_SESSION_ 34324168_0 TABLE           28    3.46
SYS        SYSAUX     WRH$_IOSTAT_FUNCTION            INDEX            2     .25
                          ------------------------------------------------------

Segments by Physical Read Requests            DB/Inst: ORCL/ORCL  Snaps: 23-24
-> Total Physical Read Requests:             790
-> Captured Segments account for    1.3% of Total

           Tablespace                      Subobject  Obj.     Phys Read
Owner         Name    Object Name            Name     Type      Requests  %Total
---------- ---------- -------------------- ---------- ----- ------------ -------
SYS        SYSAUX     WRH$_ACTIVE_SESSION_ 34324168_0 TABLE            8    1.01
SYS        SYSAUX     WRH$_IOSTAT_FUNCTION            INDEX            2     .25
                          ------------------------------------------------------

Segments by UnOptimized Reads                 DB/Inst: ORCL/ORCL  Snaps: 23-24
-> Total UnOptimized Read Requests:             790
-> Captured Segments account for    1.3% of Total

           Tablespace                      Subobject  Obj.   UnOptimized
Owner         Name    Object Name            Name     Type         Reads  %Total
---------- ---------- -------------------- ---------- ----- ------------ -------
SYS        SYSAUX     WRH$_ACTIVE_SESSION_ 34324168_0 TABLE            8    1.01
SYS        SYSAUX     WRH$_IOSTAT_FUNCTION            INDEX            2     .25
                          ------------------------------------------------------

Segments by Optimized Reads                   DB/Inst: ORCL/ORCL  Snaps: 23-24

                  No data exists for this section of the report.
                          ------------------------------------------------------

Segments by Direct Physical Reads             DB/Inst: ORCL/ORCL  Snaps: 23-24

                  No data exists for this section of the report.
                          ------------------------------------------------------

Segments by Physical Writes                   DB/Inst: ORCL/ORCL  Snaps: 23-24
-> Total Physical Writes:         855,813
-> Captured Segments account for    0.1% of Total

           Tablespace                      Subobject  Obj.      Physical
Owner         Name    Object Name            Name     Type        Writes  %Total
---------- ---------- -------------------- ---------- ----- ------------ -------
KYLELF     USERS      AUTHORS                         TABLE          524     .06
SYS        SYSTEM     SEQ$                            TABLE           56     .01
KYLELF     USERS      AUTHORS_I                       INDEX           55     .01
SYS        SYSAUX     WRH$_ACTIVE_SESSION_ 34324168_0 TABLE           33     .00
SYS        SYSAUX     SMON_SCN_TIME                   TABLE           22     .00
                          ------------------------------------------------------

Segments by Physical Write Requests           DB/Inst: ORCL/ORCL  Snaps: 23-24
-> Total Physical Write Requestss:         251,370
-> Captured Segments account for    0.3% of Total

           Tablespace                      Subobject  Obj.    Phys Write
Owner         Name    Object Name            Name     Type      Requests  %Total
---------- ---------- -------------------- ---------- ----- ------------ -------
KYLELF     USERS      AUTHORS                         TABLE          442     .18
SYS        SYSTEM     SEQ$                            TABLE           56     .02
KYLELF     USERS      AUTHORS_I                       INDEX           55     .02
SYS        SYSAUX     SMON_SCN_TIME                   TABLE           22     .01
SYS        SYSAUX     WRH$_SYSMETRIC_HISTO            TABLE           12     .00
                          ------------------------------------------------------

Segments by Direct Physical Writes            DB/Inst: ORCL/ORCL  Snaps: 23-24
-> Total Direct Physical Writes:             808
-> Captured Segments account for    3.5% of Total

           Tablespace                      Subobject  Obj.        Direct
Owner         Name    Object Name            Name     Type        Writes  %Total
---------- ---------- -------------------- ---------- ----- ------------ -------
SYS        SYSAUX     WRH$_ACTIVE_SESSION_ 34324168_0 TABLE           26    3.22
SYS        SYSAUX     SYS_LOB0000006402C00            LOB              2     .25
                          ------------------------------------------------------
Segments by Table Scans                       DB/Inst: ORCL/ORCL  Snaps: 23-24
-> Total Table Scans:             995
-> Captured Segments account for  100.0% of Total

           Tablespace                      Subobject  Obj.         Table
Owner         Name    Object Name            Name     Type         Scans  %Total
---------- ---------- -------------------- ---------- ----- ------------ -------
SYS        SYSTEM     I_SYSAUTH1                      INDEX          992   99.70
SYS        SYSTEM     I_OBJ2                          INDEX            3     .30
                          ------------------------------------------------------

Segments by DB Blocks Changes                 DB/Inst: ORCL/ORCL  Snaps: 23-24
-> % of Capture shows % of DB Block Changes for each top segment compared
-> with total DB Block Changes for all segments captured by the Snapshot

           Tablespace                      Subobject  Obj.      DB Block    % of
Owner         Name    Object Name            Name     Type       Changes Capture
---------- ---------- -------------------- ---------- ----- ------------ -------
KYLELF     USERS      AUTHORS                         TABLE   26,820,688   55.81
KYLELF     USERS      AUTHORS_I                       INDEX   21,156,096   44.02
SYS        SYSTEM     SEQ$                            TABLE       83,440     .17
SYS        SYSAUX     WRH$_PARAMETER_PK    34324168_0 INDEX          112     .00
SYS        SYSAUX     WRH$_SQL_PLAN_PK                INDEX           80     .00
                          ------------------------------------------------------

^LSegments by Row Lock Waits                   DB/Inst: ORCL/ORCL  Snaps: 23-24
-> % of Capture shows % of row lock waits for each top segment compared
-> with total row lock waits for all segments captured by the Snapshot

                                                                     Row
           Tablespace                      Subobject  Obj.          Lock    % of
Owner         Name    Object Name            Name     Type         Waits Capture
---------- ---------- -------------------- ---------- ----- ------------ -------
KYLELF     USERS      AUTHORS                         TABLE      436,695  100.00
                          ------------------------------------------------------

Segments by ITL Waits                         DB/Inst: ORCL/ORCL  Snaps: 23-24

                  No data exists for this section of the report.
                          ------------------------------------------------------

Segments by Buffer Busy Waits                 DB/Inst: ORCL/ORCL  Snaps: 23-24
-> % of Capture shows % of Buffer Busy Waits for each top segment compared
-> with total Buffer Busy Waits for all segments captured by the Snapshot

                                                                  Buffer
           Tablespace                      Subobject  Obj.          Busy    % of
Owner         Name    Object Name            Name     Type         Waits Capture
---------- ---------- -------------------- ---------- ----- ------------ -------
KYLELF     USERS      AUTHORS                         TABLE    1,973,082   55.58
KYLELF     USERS      AUTHORS_I                       INDEX    1,576,624   44.42
                          ------------------------------------------------------

^LDictionary Cache Stats                       DB/Inst: ORCL/ORCL  Snaps: 23-24
-> "Pct Misses"  should be very low (< 2% in most cases)
-> "Final Usage" is the number of cache entries being used


                                   Get    Pct    Scan   Pct      Mod      Final
Cache                         Requests   Miss    Reqs  Miss     Reqs      Usage
------------------------- ------------ ------ ------- ----- -------- ----------
dc_awr_control                      64    0.0       0   N/A        2          1
dc_global_oids                       7    0.0       0   N/A        0         51
dc_histogram_data                    3   33.3       0   N/A        0      2,301
dc_histogram_defs                   77    1.3       0   N/A        0      4,392
dc_objects                         276    0.4       0   N/A        0      2,628
dc_profiles                        124    0.0       0   N/A        0          2
dc_rollback_segments               892    0.0       0   N/A        0         19
dc_segments                        753    0.0       0   N/A        9        927
dc_sequences                    83,429    0.0       0   N/A   83,429         14
dc_tablespaces                   5,185    0.0       0   N/A        0          7
dc_users                         8,085    0.0       0   N/A        0         87
global database name             2,458    0.0       0   N/A        0          1
outstanding_alerts                  24    0.0       0   N/A        0          5
                          ------------------------------------------------------

Library Cache Activity                        DB/Inst: ORCL/ORCL  Snaps: 23-24
-> "Pct Misses"  should be very low

                         Get    Pct            Pin    Pct             Invali-
Namespace           Requests   Miss       Requests   Miss    Reloads  dations
--------------- ------------ ------ -------------- ------ ---------- --------
ACCOUNT_STATUS           186    0.0              0    N/A          0        0
BODY                      16    0.0             22    0.0          0        0
DBLINK                   198    0.0              0    N/A          0        0
EDITION                   78    0.0            140    0.0          0        0
INDEX                      2    0.0              2    0.0          0        0
SCHEMA                    62    0.0              0    N/A          0        0
SQL AREA                 637    0.2      6,603,229   -2.3          0        0
SQL AREA BUILD             8   12.5              0    N/A          0        0
SQL AREA STATS             8  100.0              8  100.0          0        0
TABLE/PROCEDURE           87    0.0      3,337,268    0.0          0        0
                          ------------------------------------------------------

^LMemory Dynamic Components                    DB/Inst: ORCL/ORCL  Snaps: 23-24
-> Min/Max sizes since instance startup
-> Oper Types/Modes: INItializing,GROw,SHRink,STAtic/IMMediate,DEFerred
-> ordered by Component

                 Begin Snap     Current         Min         Max   Oper Last Op
Component         Size (Mb)   Size (Mb)   Size (Mb)   Size (Mb)  Count Typ/Mod
--------------- ----------- ----------- ----------- ----------- ------ -------
ASM Buffer Cach         .00         .00         .00         .00      0 STA/
DEFAULT 16K buf         .00         .00         .00         .00      0 STA/
DEFAULT 2K buff         .00         .00         .00         .00      0 STA/
DEFAULT 32K buf         .00         .00         .00         .00      0 STA/
DEFAULT 4K buff         .00         .00         .00         .00      0 STA/
DEFAULT 8K buff         .00         .00         .00         .00      0 STA/
DEFAULT buffer     9,952.00    9,952.00    9,952.00    9,952.00      0 INI/
KEEP buffer cac         .00         .00         .00         .00      0 STA/
PGA Target         1,952.00    1,952.00    1,952.00    1,952.00      0 STA/
RECYCLE buffer          .00         .00         .00         .00      0 STA/
SGA Target        11,680.00   11,680.00   11,680.00   11,680.00      0 STA/
Shared IO Pool          .00         .00         .00         .00      0 STA/
java pool             32.00       32.00       32.00       32.00      0 STA/
large pool            32.00       32.00       32.00       32.00      0 STA/
shared pool        1,600.00    1,600.00    1,600.00    1,600.00      0 STA/
streams pool            .00         .00         .00         .00      0 STA/
                          ------------------------------------------------------



Memory Resize Operations Summary              DB/Inst: ORCL/ORCL  Snaps: 23-24

                  No data exists for this section of the report.
                          ------------------------------------------------------

Memory Resize Ops                             DB/Inst: ORCL/ORCL  Snaps: 23-24

                  No data exists for this section of the report.
                          ------------------------------------------------------

^LProcess Memory Summary                       DB/Inst: ORCL/ORCL  Snaps: 23-24
-> B: Begin Snap   E: End Snap
-> All rows below contain absolute values (i.e. not diffed over the interval)
-> Max Alloc is Maximum PGA Allocation size at snapshot time
-> Hist Max Alloc is the Historical Max Allocation for still-connected processes
-> ordered by Begin/End snapshot, Alloc (MB) desc

                                                            Hist
                                    Avg  Std Dev     Max     Max
               Alloc      Used    Alloc    Alloc   Alloc   Alloc    Num    Num
  Category      (MB)      (MB)     (MB)     (MB)    (MB)    (MB)   Proc  Alloc
- -------- --------- --------- -------- -------- ------- ------- ------ ------
B Other        249.7       N/A      7.3     12.6      47      47     34     34
  Freeable      14.9        .0      1.0      1.3       4     N/A     15     15
  PL/SQL          .6        .5       .0       .0       0       0     34     34
  SQL             .1        .0       .0       .0       0       3     20     15
E Other        241.3       N/A      7.3     12.7      47      47     33     33
  Freeable      18.0        .0      1.4      2.4       9     N/A     13     13
  PL/SQL          .6        .5       .0       .0       0       0     33     33
  SQL             .1        .0       .0       .0       0       3     19     14
                          ------------------------------------------------------

SGA Memory Summary                            DB/Inst: ORCL/ORCL  Snaps: 23-24

                                                      End Size (Bytes)
SGA regions                     Begin Size (Bytes)      (if different)
------------------------------ ------------------- -------------------
Database Buffers                    10,435,428,352
Fixed Size                               2,264,456
Redo Buffers                            10,158,080
Variable Size                        1,744,831,096
                               -------------------
sum                                 12,192,681,984
                          ------------------------------------------------------

SGA breakdown difference                      DB/Inst: ORCL/ORCL  Snaps: 23-24
-> ordered by Pool, Name
-> N/A value for Begin MB or End MB indicates the size of that Pool/Name was
   insignificant, or zero in that snapshot


Pool   Name                                 Begin MB         End MB  % Diff
------ ------------------------------ -------------- -------------- -------
java   free memory                              32.0           32.0    0.00
large  PX msg pool                                .5             .5    0.00
large  free memory                              31.5           31.5    0.00
shared FileOpenBlock                            19.5           19.5    0.00
shared KGLH0                                    63.2           63.3    0.15
shared KGLS                                     16.6           16.6    0.00
shared KTI-UNDO                                 18.5           18.5    0.00
shared SQLA                                     93.0           93.1    0.18
shared db_block_hash_buckets                    89.0           89.0    0.00
shared dbktb: trace buffer                      25.8           25.8    0.00
shared event statistics per sess                31.4           31.4    0.00
shared free memory                             999.3          999.1   -0.02
shared ksunfy : SSO free list                   29.8           29.8    0.00
shared private strands                          35.7           35.7    0.00
       buffer_cache                          9,952.0        9,952.0    0.00
       fixed_sga                                 2.2            2.2    0.00
       log_buffer                                9.7            9.7    0.00
                          ------------------------------------------------------

^LStreams CPU/IO Usage                         DB/Inst: ORCL/ORCL  Snaps: 23-24

                  No data exists for this section of the report.
                          ------------------------------------------------------

Streams Capture                               DB/Inst: ORCL/ORCL  Snaps: 23-24

                  No data exists for this section of the report.
                          ------------------------------------------------------

Streams Capture Rate                          DB/Inst: ORCL/ORCL  Snaps: 23-24

                  No data exists for this section of the report.
                          ------------------------------------------------------

Streams Apply                                 DB/Inst: ORCL/ORCL  Snaps: 23-24

                  No data exists for this section of the report.
                          ------------------------------------------------------

Streams Apply Rate                            DB/Inst: ORCL/ORCL  Snaps: 23-24

                  No data exists for this section of the report.
                          ------------------------------------------------------

Buffered Queues                               DB/Inst: ORCL/ORCL  Snaps: 23-24

                  No data exists for this section of the report.
                          ------------------------------------------------------

Buffered Queue Subscribers                    DB/Inst: ORCL/ORCL  Snaps: 23-24

                  No data exists for this section of the report.
                          ------------------------------------------------------
Rule Set                                      DB/Inst: ORCL/ORCL  Snaps: 23-24

                  No data exists for this section of the report.
                          ------------------------------------------------------

Persistent Queues                             DB/Inst: ORCL/ORCL  Snaps: 23-24

                  No data exists for this section of the report.
                          ------------------------------------------------------

Persistent Queues Rate                        DB/Inst: ORCL/ORCL  Snaps: 23-24

                  No data exists for this section of the report.
                          ------------------------------------------------------

Persistent Queue Subscribers                  DB/Inst: ORCL/ORCL  Snaps: 23-24

                  No data exists for this section of the report.
                          ------------------------------------------------------

^LResource Limit Stats                             DB/Inst: ORCL/ORCL  Snap: 24

                  No data exists for this section of the report.
                          ------------------------------------------------------

Shared Servers Activity                       DB/Inst: ORCL/ORCL  Snaps: 23-24
-> Values represent averages for all samples

   Avg Total   Avg Active    Avg Total   Avg Active    Avg Total   Avg Active
 Connections  Connections Shared Srvrs Shared Srvrs  Dispatchers  Dispatchers
------------ ------------ ------------ ------------ ------------ ------------
           0            0            0            0            0            0
                          ------------------------------------------------------

Shared Servers Rates                          DB/Inst: ORCL/ORCL  Snaps: 23-24

  Common     Disp                        Common       Disp     Server
   Queue    Queue   Server    Server      Queue      Queue      Total     Server
 Per Sec  Per Sec Msgs/Sec    KB/Sec      Total      Total       Msgs  Total(KB)
-------- -------- -------- --------- ---------- ---------- ---------- ----------
       0        0        0       0.0          0          0          0          0
                          ------------------------------------------------------

Shared Servers Utilization                    DB/Inst: ORCL/ORCL  Snaps: 23-24

                  No data exists for this section of the report.
                          ------------------------------------------------------

Shared Servers Common Queue                   DB/Inst: ORCL/ORCL  Snaps: 23-24

                  No data exists for this section of the report.
                          ------------------------------------------------------

Shared Servers Dispatchers                    DB/Inst: ORCL/ORCL  Snaps: 23-24

                  No data exists for this section of the report.
                          ------------------------------------------------------

^Linit.ora Parameters                          DB/Inst: ORCL/ORCL  Snaps: 23-24

                                                                End value
Parameter Name                Begin value                       (if different)
----------------------------- --------------------------------- --------------
archive_lag_target            300
audit_file_dest               /rdsdbdata/admin/ORCL/adump
control_files                 /rdsdbdata/db/ORCL_A/controlfile/
db_block_checking             MEDIUM
db_create_file_dest           /rdsdbdata/db
db_name                       ORCL
db_recovery_file_dest_size    1073741824
db_unique_name                ORCL_A
dg_broker_config_file1        /rdsdbdata/config/dr1ORCL.dat
dg_broker_config_file2        /rdsdbdata/config/dr2ORCL.dat
diagnostic_dest               /rdsdbdata/log
filesystemio_options          setall
local_listener                (ADDRESS = (PROTOCOL=TCP)(HOST=lo
log_archive_dest_1            location="/rdsdbdata/db/ORCL_A/ar
log_archive_format            -%s-%t-%r.arc
memory_target                 0
open_cursors                  300
pga_aggregate_target          2038670336
plsql_warnings                DISABLE:ALL
processes                     1652
recyclebin                    OFF
sga_target                    12247367680
spfile                        /rdsdbbin/oracle/dbs/spfileORCL.o
standby_file_management       AUTO
undo_tablespace               UNDO_T1
use_large_pages               ONLY
                          ------------------------------------------------------

^Linit.ora Multi-Valued Parameters             DB/Inst: ORCL/ORCL  Snaps: 23-24

                  No data exists for this section of the report.
                          ------------------------------------------------------

Dynamic Remastering Stats                     DB/Inst: ORCL/ORCL  Snaps: 23-24

                  No data exists for this section of the report.
                          ------------------------------------------------------

End of Report

Uncategorized