dbreplay vs SPA (cage match).

DBreplay vs SPA (SQL Profile Analyzer)

DBreplay
– This product is utilized through the following steps

• 
  Capture a production workload during a critical time. All Statements are captured and stored.
  

• 
  Copy (or mount), the capture files on your “replay” server.
  

• 
  Preprocess the workload
  

• 
  Restore your database, up to the point in time of the capture (the capture can give you the exact SCN needed).
  

• 
  Replay the workload on the copy of production
  

• 
  Compare the replay to the original capture statistics (through AWR). You can also compare different runs to each other.
  

Pros

• 
  The entire workload is replayed with the same data set as your source.
  

• 
  GTT (global temporary tables), are primed properly
  

• 
  The workload is same as production (top executed sql are executed multiple times).
  

• 
  Cardinality feedback, and other optimizer pieces that may change with executions should be seen.
  

Cons

• 
  Capturing a production workload, and preparing a copy of production is a huge effort.
  

• 
  This only tests workload that occurs during the capture. If there are different workload windows (i.e. batch vs online), you must capture the
  different workloads and restore to match.
  

• 
  You cannot touch sql, and anything that part of the capture. If you receive errors, (like ora-4031) you must correct the errors before continuing.
  

SPA (SQL Profile Analyzer)
– This product is

utilized through the following steps.

• 
  A sql tuning set is created on the source system (this contains the sql, the plans, and the execution statistics.
  

• 
  The sql tuning set is exported from old imported into new system.
  

• 
  A SPA job is created that will either compare the statistics with the original execution, or compare statistics by executing on both servers
  (through database links).
  

• 
  Information on the sql statements is reported on, and prioritized by the effect on the workload.
  

PROS

• 
  You don’t need to have the database synched up, as long as it is somewhat representative.
  

• 
  You can re-execute against the source system to compare executions.
  

• 
  A report is created comparing each sql execution
  

CONS

• 
  Does not work with GTT (global temporary tables), since they cannot be primed to run this process
  

• 
  The database test set most likely does not match source, so you need to interpret most of the data.
  

• 
  You do not see the interaction of SQL statements.
  

Conclusion – DBReplay is the preferred tool to test performance differences when making an infrastructure change. If GTT’s are utilized, then DBReplay
is the only tool can really give you useful data. If DBReplay cannot be used, or to supplement dbreplay, SPA is also a useful tool. You can also use
SPA to better tune specific sql (utilize DBReplay to identify sql, and SPA to deep dive them).

DBreplay why are you messing with my sequences ??

Well,
  I have been playing with dbreplay, and trying to re-run a production workload.  In order to capture a production workload, I don't have the luxury of bouncing the database.  I start the capture, and do a restore of production that is "roughly right".  A point in time recovery close to that point.

Well I've been fighting an issue for the last couple of days.  I run a replay, and I find a lot of the my sessions are getting hung up, and I have sequences that have starting values BEFORE the value they were set to at the beginning.  At first I thought the flashback somehow messed up my sequences, but now I know it is the replay.

Part of what the "prepare client" process does is this sql step

 SELECT MAX(FIRST_VALUE), MAX(LAST_VALUE), MIN(FIRST_VALUE), MIN(LAST_VALUE), SEQ_BOW, SEQ_NAME, INCREMENT_BY, CYCLE_FLAG, CACHE_SIZE 
  
 FROM WRR$_REPLAY_SEQ_DATA R, DBA_SEQUENCES S 
  
 WHERE R.SEQ_BOW = S.SEQUENCE_OWNER 
  
 AND R.SEQ_NAME = S.SEQUENCE_NAME 
  
 GROUP BY SEQ_NAME ,SEQ_BNM, SEQ_BOW, INCREMENT_BY, CYCLE_FLAG, CACHE_SIZE  

This step looks to see if the current value of the sequence is out of synch with the value when the capture was done.. If so it resynchs the sequences. Sweet eh ? but maybe not what you wanted.. This is what is actually being executed (when you run a trace).

 SELECT "OE"."CHANGE_TRACKING_SEQ".NEXTVAL FROM dual
  
 ALTER SEQUENCE "OE"."CHANGE_TRACKING_SEQ" MAXVALUE 1E27 MINVALUE -1E26 INCREMENT BY -789390 NOCACHE
  
 SELECT "OE"."CHANGE_TRACKING_SEQ".NEXTVAL FROM dual
  
 ALTER SEQUENCE "OE"."CHANGE_TRACKING_SEQ" INCREMENT BY 1 NOCACHE  

As you can see it takes the sequences, alters it to subract on the next call, executes the nextval, then resets with an alter. Pretty slick, but very hard to find.

Thank goodness for Logminer to help point me in the right direction as to who was altering my sequence.

Here is some additional advice. To check to see what sequences have been "reset" look at the last_ddl time from dba_objects. Capture the list of what's changed, and then flashback and get the originall ddl.

My process is now prepare clients, reset sequences, then release the clients.

Search words. database replay sequences out of order reset

Flashback and Sequences

I just ran into a situation using flashback and dbreplay. 

See my next post on DBReplay. this was the culprit.

Exadata presentations

Well, I'm finally getting my exadata presentations up on my blog.  I will give a synopsis of it so you can decide if you want to look through them.. I also included a some screenshots that are meaningless without some background.

Hardware  (download)

My conclusion on hardware is that the exadata is merely off the shelf hardware.  Yes putting together the hardware in the exadata configuration (with storage cells, and infiniband) does greatly improve performance over most "normal" configurations that use arrays (like Hitachi, IBM, EMC, etc. etc.) over Fiber.

You can build your own server/storage that is even faster using SSD over infininband and you can customize it to your needs balancing the storage and database for YOUR NEEDS.

Software (download)

This is where the solution shines.  As I said above you can build the hardware yourself, but the gain is in the software.. In my test case I took a 276gb table with 1.7billion rows, and scanned it in under a second by combining HCC, storage indexes and flashcache.  Pretty incredible (it only used 6gb of disk space too).

However, the more your application is OLTP like (so it is less likely to HCC compressed, and scanned) the less gain you see with this solution.

Enjoy !

indexing HCC partititions

I have been playing with what happens when you index HCC compressed data .  If you follow the recommended strategy, you will partition your history tables, and end up with some data uncompressed, some OLTP compressed, and some in different stages of HCC compressed.  What if you use an index lookup that spans all these levels of compression ?

First I took a table that started at 176g of data (uncompressed).

OLTP compression took it down to 76G

HCC query compression took it down to 6G.

Now I indexed the HCC copy.. My index (on a single column) ended up being 40g. 

Now I queried the data using the index.  First execution was longer than querying the unindexed data (with storage indexes working their magic).  Second index is much, much faster.

The lesson I learned is that HCC really helps with storage, but start indexing the HCC data, and you end up using a lot more storage.  Also unindexed lookups can be faster than indexed (until they hit the SGA).

Interesting information to help plan what happens when I go to query across all flavors of compression.

UNYOUG meeting 6/10 In Buffalo.

Yes, it's once again time for my shameless plug for the Upstate NY Oracle Users Group meeting.  This time it's in Buffalo at Buff State.

I will will be presentation (again) on the Exadata part II (software).

Val Bedard will be presenting on Golden Gate (Val ROCKS !!)

Cristophe will be presenting on ODI.  Should be a great day.

The information can be found here.

Cardinality and the in clause

I was running a query that had an in clause, and the cardinality turned out to be wrong.. After investigating, I found that the optimizer was double applying the selectivity of the index.

Here is my example

select * from system.tab1 a,system.tab2 b
where a.col1 IN ('WRH$_PARAMETER_PK','WRH$_SEG_STAT','WRH$_DB_CACHE_ADVICE')
and col1=col2;

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 54186084

------------------------------------------------------------------------------------------
| Id  | Operation                      | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |         |     3 |   162 |    14   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                  |         |       |       |            |          |
|   2 |   NESTED LOOPS                 |         |     3 |   162 |    14   (0)| 00:00:01 |
|   3 |    INLIST ITERATOR             |         |       |       |            |          |
|   4 |     TABLE ACCESS BY INDEX ROWID| TAB1    |     3 |    75 |     7   (0)| 00:00:01 |
|*  5 |      INDEX UNIQUE SCAN         | TAB1_PK |     3 |       |     4   (0)| 00:00:01 |
|*  6 |    INDEX RANGE SCAN            | TAB2_IX |     1 |       |     2   (0)| 00:00:01 |
|   7 |   TABLE ACCESS BY INDEX ROWID  | TAB2    |     1 |    29 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

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

   5 - access("A"."COL1"='WRH$_DB_CACHE_ADVICE' OR "A"."COL1"='WRH$_PARAMETER_PK'
              OR "A"."COL1"='WRH$_SEG_STAT')
   6 - access("COL1"="COL2")
       filter("COL2"='WRH$_DB_CACHE_ADVICE' OR "COL2"='WRH$_PARAMETER_PK' OR
              "COL2"='WRH$_SEG_STAT')

23 rows selected.

Notice the cardinality of 3 rows.   What made me wonder, is when I used just an '=' sign

select * from system.tab1 a,system.tab2 b
where col1 ='WRH$_DB_CACHE_ADVICE'
and col1=col2;

------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1964798218

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |    26 |  1404 |    23   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |         |    26 |  1404 |    23   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| TAB1    |     1 |    25 |     2   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN         | TAB1_PK |     1 |       |     1   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY INDEX ROWID| TAB2    |    26 |   754 |    21   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN          | TAB2_IX |    26 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   3 - access("COL1"='WRH$_DB_CACHE_ADVICE')
   5 - access("COL2"='WRH$_DB_CACHE_ADVICE')

Notice the cardinality is 26 ?  why is the optimizer changing the cardinality from 26 to 3, when I include an in clause with one other value ? I would expect the cardinality to be 78.  In fact when I use a union it comes back right.

select * from system.tab1 a,system.tab2 b

where col1 ='WRH$_DB_CACHE_ADVICE'
and col1=col2
union
select * from system.tab1 a,system.tab2 b
where col1 ='WRH$_SEG_STAT'
and col1=col2
union
select * from system.tab1 a,system.tab2 b
where col1 ='WRH$_PARAMETER_PK'
and col1=col2;

------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2805776637

---------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                    |    77 |  7014 |    71  (68)| 00:00:01 |
|   1 |  SORT UNIQUE                       |                    |    77 |  7014 |    71  (68)| 00:00:01 |
|   2 |   UNION-ALL                        |                    |       |       |            |          |
|   3 |    NESTED LOOPS                    |                    |    26 |  1404 |    23   (0)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID    | TAB1               |     1 |    25 |     2   (0)| 00:00:01 |
|*  5 |      INDEX UNIQUE SCAN             | TAB1_PK            |     1 |       |     1   (0)| 00:00:01 |
|   6 |     TABLE ACCESS BY INDEX ROWID    | TAB2               |    26 |   754 |    21   (0)| 00:00:01 |
|*  7 |      INDEX RANGE SCAN              | TAB2_IX            |    26 |       |     2   (0)| 00:00:01 |
|   8 |    NESTED LOOPS                    |                    |       |       |            |          |
|   9 |     NESTED LOOPS                   |                    |    51 |  5610 |    46   (0)| 00:00:01 |
|  10 |      VIEW                          | VW_JF_SET$623BBB07 |     2 |   162 |     4   (0)| 00:00:01 |
|  11 |       SORT UNIQUE                  |                    |     2 |    50 |     4  (50)| 00:00:01 |
|  12 |        UNION-ALL                   |                    |       |       |            |          |
|  13 |         TABLE ACCESS BY INDEX ROWID| TAB1               |     1 |    25 |     2   (0)| 00:00:01 |
|* 14 |          INDEX UNIQUE SCAN         | TAB1_PK            |     1 |       |     1   (0)| 00:00:01 |
|  15 |         TABLE ACCESS BY INDEX ROWID| TAB1               |     1 |    25 |     2   (0)| 00:00:01 |
|* 16 |          INDEX UNIQUE SCAN         | TAB1_PK            |     1 |       |     1   (0)| 00:00:01 |
|* 17 |      INDEX RANGE SCAN              | TAB2_IX            |    26 |       |     2   (0)| 00:00:01 |
|  18 |     TABLE ACCESS BY INDEX ROWID    | TAB2               |    26 |   754 |    21   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------

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

   5 - access("COL1"='WRH$_PARAMETER_PK')
   7 - access("COL2"='WRH$_PARAMETER_PK')
  14 - access("COL1"='WRH$_DB_CACHE_ADVICE')
  16 - access("COL1"='WRH$_SEG_STAT')
  17 - access("ITEM_1"="COL2")

So I ran a 10053 trace and this is what I found.

1) the table (tab2) has 1280422 rows with 49,840 distinct values

Table Stats::

Table: TAB2 Alias: B
#Rows: 1280422 #Blks: 5972 AvgRowLen: 29.00 ChainCnt: 0.00
Column (#1): COL2(
AvgLen: 23 NDV: 49840 Nulls: 0 Density: 0.000020
Index Stats::
Index: TAB2_IX Col#: 1
LVLS: 2 #LB: 5787 #DK: 49840 LB/K: 1.00 DB/K: 17.00 CLUF: 884604.00
 
2) oracle takes this cardinality and computes it for the table access (1,280,422/49,840) * 3 = 77
 
SINGLE TABLE ACCESS PATH



Single Table Cardinality Estimation for TAB2[B]
Table: TAB2 Alias: B
Card: Original: 1280422.000000 Rounded: 77 Computed: 77.07 Non Adjusted: 77.07
Access Path: TableScan
 
 
 
 
3) It then takes the cardinality of the first table (tab1) the cardinality for the second table (tab2), and applies the selectivity.
 
Join Card: 2.964306 = = outer (3.000000) * inner (77.071950) * sel (0.012821)
 
Here is my example and my 10053 trace