EM12c, Rel. 4, OMS and OMR Health, Part II

There are a large number of “moving parts” when performance tuning or trouble shooting an Enterprise Manager environment.  The new EM performance features, (available in release 12.1.0.4) are there to assist you in understanding the source of the issue and can really make the difference for those that are unfamiliar with the challenges of Weblogic, java, network or other complexities that make up EM12c and aren’t commonly thought of as part of the DBA’s job role.

Now that we’ve finished with the Health Overview, we can look deeper into the health and performance of the two most well known components of the EM12c architecture, the Oracle Management Repository, (OMR) and the Oracle Management Services, (OMS).

Due to the impressive features offered in the new EM performance consoles, I’m going to break these up into multiple posts and start with OMR and focus on the Repository Tab.

The Repository

The Repository Tab is accessed via the same Setup menu in EM12c console:

rep1

Once accessed, there are a number of tabs at the top of the page, Repository, Metrics and Schema.  Starting with the Repository tab, (Left to Right) we’ll inspect what specific performance data is important when reviewing an OMR.

Repository Tab

The Repository page is going to display a number of graphs that tells you everything from specific information about the OMR database, incidents involving the OMR database and even how collections performance at the database repository level.  It is important to remember that this tab is all about the Repository, (OMR) and should not be confused with the Service, (OMS).

Basic Information

We begin by viewing information about database name, space allocated, space used number of current sessions actively connected to the OMR.

rep5

All of these are links, so you can click on the link and a you’ll be taken to a detailed view of the data and more information to investigate if you have questions.

For the Target type for the OMR, you can click on the target name, (db name) and the console will take you to the home page for the OMR database.

Click on Last Backup date and the console will take you to the backup activity report for the OMR database.

Click on Space Used and the console will then bring you to the Management Services and Repository page drill down for Repository Tablespace Used.

rep6

There is a ton of information in this location and we’ll dig deeper into it as a separate post, but just to understand how user friendly the interface is, note the links you have at your fingertips right here.

If you click on Management Service Repository Sessions, the following table with session type and counts will display:

rep7

Incidents

On the right hand side of the top of the page, we access to the incidents linked to the OMR.  No other incidents will be listed except the ones connected to the OMR, so this is a great place to check first when you are experiencing issues.

rep8

Notice that it includes incidents for page processing time outs to the OMS and collection timeouts.  This can be very helpful when you are experiencing slow response and need to know where the issue is sourced from.

Initialization Parameters for the OMR

Not only does the next graph identify what size category you fall into for your Enterprise Manager environment, (small, medium or large) but it also lets you know if any of your parameters are outside of the recommended sizing for that category.

rep9

In our example, you can see that we don’t have a set MEMORY_TARGET value and that is outside of our compliance as we recommend to have this set to one.  We can also view each of the values we do have set and how they compare to what Oracle thinks the minimum value for that category of OMR size should be.

Job Scheduler Status

To the right of the Init parameters is all the graph with information pertaining to all the jobs running in the OMR to support the Enterprise Manager environment.  Unlike Job Activity in the console, this is reporting all those jobs that are taking care of the Enterprise Manager.

If a job fails and you have the option to edit the schedule to run again, (the glasses icon) then you can click on the glasses and the following popup will show and you can then enter a new time for the job to retry:

rep11

Once you enter in the new time to run the job, click on Save and verify that the job has been successful in the console view, (green check mark vs. a red X.)

Collections Performance

At the bottom left, Collections is the next category that’s covered.  If collections aren’t uploading to the OMR, then the console isn’t able to provide the most up to date data and notifications of incidents and alerts aren’t sent out to notify administrators of issues.  Timely collections and the performance of collections is of a great concern to an EM Cloud Control administrator.

rep12

The graph is well laid out and shows clearly the number of collections in backlog and throughput performance.   The top of the graph, when hovered over, will show you the warning and critical threshold line for number of backlogs allowed.

Backlog is an issue, as if it gets too high and hits the threshold, your agent can stop uploading.  You can also see the duration, on average of the collections and view over time if the duration is increasing.  If you use a lot of metric extensions or plug-ins, this is something you’ll want to monitor, so this graph is extremely helpful when inspecting collection performance.

By hovering your cursor over the Collections Backlog line in the graph, I then am offered a number of options to look into the performance:

rep13

You have the option to click on Problem Analysis to look into the backlog, Metrics Detail or go to the Target Home.

Problem Analysis

As my EM environment is running quite smoothing at the OMR level, there isn’t a lot to show you in the Problem Analysis, but I wanted to at least give everyone a peak into this cool, new tool.

rep14

First of all, if I did have an issue, there would be collections showing in backlog.  This is very important for an administrator to check and ensure that backlog is not occurring.

As there is no backlog, you can see, my resource usage by my collections is pretty consistent and quite below the thresholds expected for most of the resource types shown:

rep15

You can also export the data from the table view, (small link at the bottom right of the screen, not shown) if you need the raw data.

You will note that my memory utilization is creeping, little by little to the critical threshold.  This is commonly due to java garbage collection causing a small memory leak and should be reviewed from time to time.  If it is considerable, the java heap should be examined and a more efficient value set.

Adding Metrics to the Performance Analysis

On the right hand side of the Performance Analysis, you will notice the Metric Palette.  This offers you the opportunity to go from the standard configuration to display more data on the existing metrics or add analysis on other metrics, such as Agents and Page Performance.

It’s important to know, even though you can be brought to this page from many different links within the OMR/OMS Performance pages, while you are in the Performance Analysis, you can inspect other performance metric factors than just the original ones you are reviewing.

For our example, we’ll add an additional metric graph,(Time estimates for clearing backlog) for review to the left hand analysis page-

rep16

We now have an additional graph on the left hand side analysis to compare to our existing data to see if the load times correlate to resource usage:

rep17

This can be done for dozens of metrics and offers some incredible analysis power when researching performance issues with EM12c.  The Performance Analysis link is one of the most powerful tools for locating where a bottleneck in performance is coming from and very quickly.  The fluid ability to add metrics to the graphs section and see how they correspond to the other resource usage is incredibly beneficial as well.

Metric Details

Now back to our Collections graph, if you remember we had three options when we click on the blue line:

rep13

By clicking on the Metrics Details link, we’ll then go to performance page for All Metrics.

rep18

This page displays information about the number of short and long running collections in backlog and will display the status if the threshold value has been hit for backlog quantity.  The page functions similar to Incidents, in that you can click on the right middle button to display the Task Class information highlighted to full page.

You are also offered the option to modify thresholds if the current values don’t meet the demands of the system is under currently, but know that the recommended values are their for a reason and the option to change them should be seriously researched beforehand.

Target Home

This link takes you to the Overview and Health page for the OMR.  I get to save a lot of typing by just sending you to my blog post on this great feature! :)

A final clarification, too-  the three options available, Performance Analysis, Metric Details and Target Home are options available for each metric by double-clicking in the Repository Collections Performance or the Metric Data Rollup graph, which we’ll discuss next.

Metric Data Rollup Performance

The last graph, in the right hand bottom corner, is the for metric data.  This graph displays the number of metric records rolled up and the throughput per minute for this data to be uploaded into the OMR.

We again have the ability to inspect performance analysis by double-clicking on the metric in the graph.

rep19

Each of the three options work almost exactly the same way as I demonstrated for the Collections Performance, but the data is based on the metrics rollup.

The main functionality of each of these sections is to realize how many different ways you can do performance analysis on different performance data:

rep20

Yes, even the legend can be clicked on and a detail option chosen.

That completes the review of the Repository Tab, remember, I have two more tabs to cover in posts before we dig into the Management Services and Agents performance consoles.

rep21

 

 

 

 

 

 

 

 

 

 



Tags:  

Del.icio.us
Facebook
TweetThis
Digg
StumbleUpon


Copyright © DBA Kevlar [EM12c, Rel. 4, OMS and OMR Health, Part II], All Right Reserved. 2014.

OLTP Compression, Drop Column, Partition Exchange

With Basic Compression, you cannot drop a column.

create table t1
(col1 number
,col2 number)
compress;
 
table T1 created.

alter table t1 drop column col2;

SQL Error: ORA-39726: unsupported add/drop column operation on compressed tables
39726. 00000 -  "unsupported add/drop column operation on compressed tables"
*Cause:    An unsupported add/drop column operation for compressed table
           was attemped.
*Action:   When adding a column, do not specify a default value.
           DROP column is only supported in the form of SET UNUSED column
           (meta-data drop column).

But with Basic Compression, you can set as unused.

alter table t1a set unused column col2;  

table T1A altered

With OLTP Compression, on the face of it, you can drop a column.

drop table t1;

create table t1
(col1 number
,col2 number)
compress for oltp;

select table_name, column_name, hidden_column 
from   user_tab_cols 
where  table_name = 'T1' order by column_id;

TABLE_NAME COLUMN_NAME HIDDEN_COLUMN
---------- ----------- -------------
T1         COL1        NO
T1         COL2        NO

alter table t1 drop column col2;

table T1 altered

But this is a lie/misdirection.
In reality, the dropped column is just renamed.
We see the column is not dropped but hidden:

select table_name, column_name, hidden_column 
from   user_tab_cols 
where  table_name = 'T1' order by column_id;

TABLE_NAME COLUMN_NAME                HIDDEN_COLUMN
---------- -------------------------- -------------
T1         COL1                       NO
T1         SYS_C00002_14111103:30:14$ YES

Note
1. The hidden column name is system generated and suffixed with YYMMDDHH24:MI:SS$
2. Hidden columns are not exposed via USER_TAB_COLUMNS hence usage of USER_TAB_COLS.

I’m not a fan of this behaviour.
It is intended and documented.

Not in OLTP Compression master note 1223705.1.

https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=1223705.1

But in doc id 1068820.1

https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=1068820.1

Only advanced compression, which requires Advanced Compression license, supports drop on compressed tables but even in this situation there is no real drop but internally the database sets the column UNUSED to avoid long-running decompression and recompression operations.

Why might this matter?

I can think of at least one time when this might matter – partition exchange.
Ok, the circumstances are probably niche and more relevant for a DEV environment than PROD.
But imagine you add a column to one side of the partition exchange operation and then think better of it and immediately drop it.

drop table t1;
drop table t2;

create table t1
(col1 number
,col2 number)
compress for oltp;
 
create table t2
(col1 number
,col2 number)
partition by range (col1) interval(1)
(partition p0 values less than (0))
compress for oltp;

Show that partition exchange works initially:

lock table t2 partition for (1) in exclusive mode;

lock succeeded.

alter table t2 exchange partition for (1) with table t1;

table T2 altered.

But if I add and drop a column

alter table t1 add col3 number;

table T1 altered.

alter table t1 drop column col3;

table T1 altered.

Then my partition exchange no longer works:

alter table t2 exchange partition for (1) with table t1;

SQL Error: ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION
14097. 00000 -  "column type or size mismatch in ALTER TABLE EXCHANGE PARTITION"
*Cause:    The corresponding columns in the tables specified in the
           ALTER TABLE EXCHANGE PARTITION are of different type or size
*Action:   Ensure that the two tables have the same number of columns
           with the same type and size.

Until I recreate the table or use DBMS_REDEFINITION.
Or until I do the same to the other side of the exchange operation:

alter table t2 add col345 number;

table T2 altered.

alter table t2 drop column col345;

table T2 altered.

Then

alter table t2 exchange partition for (1) with table t1;

table T2 altered.

An interview with enterprise mobility lead Luc Bors

We're on a roll in interviewing Oracle customers who specialize in enterprise mobility. Following on from my interview of Andrew Paterson and Simon Canil, this time I've taken the opportunity to interview Luc Bors from AMIS in the Netherlands.  AMIS is one of our key Oracle partners, a FMW powerhouse as I like to say, and Luc is an Oracle ACE who just recently published the first book on Oracle's Mobile Application Framework.

Head over to LinkedIn to read the full Q&A article where Luc shares his experiences and thoughts on building successful enterprise mobility apps with Oracle's mobile tooling.  I hope you find this article useful.

The Importance of the In-Memory DUPLICATE Clause for a RAC System

With the INMEMORY clause you can specify 4 sub-clauses:

  • The MEMCOMPRESS clause specifies whether and how compression is used
  • The PRIORITY clause specifies the priority (“order”) in which the segments are loaded when the IMCS is populated
  • The DISTRIBUTE clause specifies how data is distributed across RAC instances
  • The DUPLICATE clause specifies whether and how data is duplicated across RAC instances

The aim of this post is not to describe these attribues in detail. Instead, this post shows you the impact of the DUPLICATE clause when a query is executed in a RAC environment. For a short description, have a look to this table in the Database Reference guide. For more details, have a look to the Oracle Database In-Memory white paper.

It is essential to understand that the DUPLICATE clause (also known as “Fault Tolerant In-Memory Column Store”), according to the Oracle Database Licensing Information guide, requires Exadata or Supercluster. Also note that, according to this Twitter conversation, Oracle also plans to support the feature on ODA. Hence, the idea is to support it on all engineered systems.

In this post I would like to show you examples that make clear why the DUPLICATE clause is key to get good performance on a RAC system. To do so, I will execute a simple query like “SELECT sum(col) FROM tab” with different settings.

First of all, let me set the scene…

  • Since the In-Memory option is required, I have to use version 12.1.0.2.0
SQL> SELECT banner
  2  FROM v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE    12.1.0.2.0      Production
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production
  • What you cannot see from the previous output is that the database is hosted on RAC/Exadata system
  • The test table stores 12GB of data in about 1.5 million blocks (BTW, this is the same table I used in this post)
SQL> SELECT blocks, round(blocks*8192/1024/1024) AS size_mb
  2  FROM user_tables
  3  WHERE table_name = 'T';

    BLOCKS    SIZE_MB
---------- ----------
   1550061      12110
  • Note that parallel processing is disabled at the table level
  • At the beginning, make sure that the In-Memory Column Store (IMCS) is not used for the test table
SQL> ALTER TABLE t NO INMEMORY;

SQL> SELECT inst_id, populate_status, inmemory_size, bytes, bytes_not_populated
  2  FROM gv$im_user_segments
  3  WHERE segment_name = 'T';

no rows selected



TEST 1

Let me prepare the environment for the first test:

  • One instance is up and running
SQL> SELECT count(*)
  2  FROM gv$instance;

  COUNT(*)
----------
         1
  • Specify to use the IMCS for the test table and trigger the population
SQL> ALTER TABLE t INMEMORY;

SQL> SELECT count(*)
  2  FROM t;

  COUNT(*)
----------
 100000000
  • After waiting for few minutes, check that the population is over (POPULATE_STATUS=COMPLETED) and that the test table is completely stored in the IMCS (BYTES_NO_POPULATED=0).
SQL> SELECT inst_id, populate_status, inmemory_size, bytes, bytes_not_populated
  2  FROM gv$im_user_segments
  3  WHERE segment_name = 'T';

   INST_ID POPULATE_ INMEMORY_SIZE      BYTES BYTES_NOT_POPULATED
---------- --------- ------------- ---------- -------------------
         1 COMPLETED    1.0921E+10 1.2707E+10                   0
  • Finally, flush the buffer cache (note that the IMCS is not impacted by this action)
SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;

Now that the environment is ready, I execute the test query and, at the same time, I activate extended SQL trace and gather runtime information:

  • The execution took 2.75 seconds
SQL> execute dbms_session.session_trace_enable

SQL> SELECT /*+ gather_plan_statistics */ sum(n_32)
  2  FROM t;

 SUM(N_32)
----------
1550000000

Elapsed: 00:00:02.75

SQL> execute dbms_session.session_trace_disable
  • A serial execution taking advantage of an IM scan (TABLE ACCESS INMEMORY FULL) was used
SQL> SELECT * FROM table(dbms_xplan.display_cursor('7qry07h06c2xs',0,'iostats last'));

SQL_ID  7qry07h06c2xs, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ sum(n_32) FROM t

Plan hash value: 2966233522

-------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |      1 |        |      1 |00:00:02.73 |       4 |      2 |
|   1 |  SORT AGGREGATE             |      |      1 |      1 |      1 |00:00:02.73 |       4 |      2 |
|   2 |   TABLE ACCESS INMEMORY FULL| T    |      1 |    100M|    100M|00:00:00.91 |       4 |      2 |
-------------------------------------------------------------------------------------------------------
  • The corresponding extended SQL trace information as shown by TKPROF is the following
SELECT /*+ gather_plan_statistics */ sum(n_32)
FROM t

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      2.72       2.73          2          4          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      2.72       2.73          2          4          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 95
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  SORT AGGREGATE (cr=4 pr=2 pw=0 time=2730184 us)
 100000000  100000000  100000000   TABLE ACCESS INMEMORY FULL T (cr=4 pr=2 pw=0 time=907260 us cost=17717 size=300000000 card=100000000)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  cell single block physical read                 2        0.00          0.00
  SQL*Net message from client                     2        0.00          0.00

In summary, the execution was, for a query that had to process 12GB of data, quite fast. This was possible because all data was extracted from the IMCS.



TEST 2

The second test uses two instances. As a result, after starting the second instance, the Im-Memory attributes of the test table needed to be changed, and the IMCS had to be re-populated.

  • Two instances are up and running
SQL> SELECT count(*)
  2  FROM gv$instance;

  COUNT(*)
----------
         2
  • Data is duplicated on both instances
SQL> ALTER TABLE t INMEMORY DUPLICATE;
  • Trigger population
SQL> SELECT count(*)
  2  FROM t;

  COUNT(*)
----------
 100000000
  • After waiting for few minutes, check that the population is over and that test table is completely stored in both IMCS
SQL> SELECT inst_id, populate_status, inmemory_size, bytes, bytes_not_populated
  2  FROM gv$im_user_segments
  3  WHERE segment_name = 'T';

   INST_ID POPULATE_ INMEMORY_SIZE      BYTES BYTES_NOT_POPULATED
---------- --------- ------------- ---------- -------------------
         2 COMPLETED    1.0921E+10 1.2707E+10                   0
         1 COMPLETED    1.0921E+10 1.2707E+10                   0
  • Flush the buffer cache of both instances

Of course the test is the same as for TEST 1. The only difference is that the test table is stored in the IMCS of both instances.

  • The execution took 2.97 seconds
SQL> execute dbms_session.session_trace_enable

SQL> SELECT /*+ gather_plan_statistics */ sum(n_32)
  2  FROM t;

 SUM(N_32)
----------
1550000000

Elapsed: 00:00:02.97

SQL> execute dbms_session.session_trace_disable
  • It goes without saying that the execution plan is the same as for TEST 1
SQL> SELECT * FROM table(dbms_xplan.display_cursor('7qry07h06c2xs',0,'iostats last'));

SQL_ID  7qry07h06c2xs, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ sum(n_32) FROM t

Plan hash value: 2966233522

-------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |      1 |        |      1 |00:00:02.97 |       4 |      2 |
|   1 |  SORT AGGREGATE             |      |      1 |      1 |      1 |00:00:02.97 |       4 |      2 |
|   2 |   TABLE ACCESS INMEMORY FULL| T    |      1 |    100M|    100M|00:00:01.02 |       4 |      2 |
-------------------------------------------------------------------------------------------------------
  • And here is the extended SQL trace information
SELECT /*+ gather_plan_statistics */ sum(n_32)
FROM t

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      2.95       2.96          2          4          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      2.95       2.97          2          4          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 95
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  SORT AGGREGATE (cr=4 pr=2 pw=0 time=2967854 us)
 100000000  100000000  100000000   TABLE ACCESS INMEMORY FULL T (cr=4 pr=2 pw=0 time=1023321 us cost=17717 size=300000000 card=100000000)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  cell single block physical read                 2        0.00          0.00
  SQL*Net message from client                     2        0.00          0.00

In summary, there is no real difference between TEST 1 and TEST 2.



TEST 3

The third test also uses two instances. But, this time, data is not fully duplicated on each instance. Instead, it is only distributed. This means that every row of the test table is stored in the IMCS of either one instance or the other.

Let me prepare the environment:

  • Two instances are up and running
SQL> SELECT count(*)
  2  FROM gv$instance;

  COUNT(*)
----------
         2
  • Data is not duplicated on both instances
SQL> ALTER TABLE t INMEMORY NO DUPLICATE;
  • Trigger population
SQL> SELECT count(*)
  2  FROM t;

  COUNT(*)
----------
 100000000
  • After waiting for few minutes, check that the population is over. Notice that since no instance contains all data, the BYTES_NOT_POPULATED column remains greater than 0
SQL> SELECT inst_id, populate_status, inmemory_size, bytes, bytes_not_populated
  2  FROM gv$im_user_segments
  3  WHERE segment_name = 'T';

   INST_ID POPULATE_ INMEMORY_SIZE      BYTES BYTES_NOT_POPULATED
---------- --------- ------------- ---------- -------------------
         2 COMPLETED    5726535680 1.2707E+10          6029524992
         1 COMPLETED    5194317824 1.2707E+10          6652174336
  • Flush the buffer cache of both instances

Once more, the same SQL statements are used to run the test:

  • The execution took 2 minutes and 40 seconds!!!!!
SQL> execute dbms_session.session_trace_enable

SQL> SELECT /*+ gather_plan_statistics */ sum(n_32)
  2  FROM t;

 SUM(N_32)
----------
1550000000

Elapsed: 00:02:39.95

SQL> execute dbms_session.session_trace_disable
  • The execution plan is the same as for TEST 1 and TEST 2. The difference is that a huge number of logical and physical I/O were performed
SQL> SELECT * FROM table(dbms_xplan.display_cursor('7qry07h06c2xs',0,'iostats last'));

SQL_ID  7qry07h06c2xs, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ sum(n_32) FROM t

Plan hash value: 2966233522

-------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |      1 |        |      1 |00:02:39.94 |    1086K|   1086K|
|   1 |  SORT AGGREGATE             |      |      1 |      1 |      1 |00:02:39.94 |    1086K|   1086K|
|   2 |   TABLE ACCESS INMEMORY FULL| T    |      1 |    100M|    100M|00:02:31.06 |    1086K|   1086K|
-------------------------------------------------------------------------------------------------------
  • The information provided by extended SQL trace confirms what dbms_xplan shows. In addition, it also shows that the poor performance is due to the high number of single-block physical reads that were performed (about 275K)
SELECT /*+ gather_plan_statistics */ sum(n_32)
FROM t

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2     93.50     159.94    1086979    1086981          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4     93.50     159.94    1086979    1086981          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 95
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  SORT AGGREGATE (cr=1086981 pr=1086979 pw=0 time=159942519 us)
 100000000  100000000  100000000   TABLE ACCESS INMEMORY FULL T (cr=1086981 pr=1086979 pw=0 time=151063172 us cost=18478 size=300000000 card=100000000)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  Disk file operations I/O                        1        0.00          0.00
  SQL*Net message to client                       2        0.00          0.00
  cell single block physical read            274946        0.03         73.01
  gc cr multi block request                    6368        0.03          4.19
  cell multiblock physical read                6368        0.02         10.00
  latch: cache buffers lru chain                  6        0.00          0.00
  latch: gcs resource hash                        2        0.00          0.00
  latch: gc element                             113        0.00          0.00
  SQL*Net message from client                     2        0.00          0.00

Since the bad performance is caused by the number of physical I/O, it goes without saying that if only logical I/O are taking place, the performance is much better. In this case, an immediate re-execution of the query took 8.28 seconds.

SQL> SELECT /*+ gather_plan_statistics */ sum(n_32)
  2  FROM t;

 SUM(N_32)
----------
1550000000

Elapsed: 00:00:08.28

SQL> SELECT * FROM table(dbms_xplan.display_cursor('7qry07h06c2xs',0,'iostats last'));

---------------------------------------------------------------------------------------
SQL_ID  7qry07h06c2xs, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ sum(n_32) FROM t

Plan hash value: 2966233522

----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |      1 |        |      1 |00:00:08.28 |    1086K|
|   1 |  SORT AGGREGATE             |      |      1 |      1 |      1 |00:00:08.28 |    1086K|
|   2 |   TABLE ACCESS INMEMORY FULL| T    |      1 |    100M|    100M|00:00:08.26 |    1086K|
----------------------------------------------------------------------------------------------

In summary, only part of the data was accessed through the IMCS. In addition, despite the fact that an IM scan was processed, except if data was in the buffer cache, many single-block physical reads were carried out. As a result, the performance was bad. This behaviour shows that a session is only able to access the IMCS it is managed by the instance it is connected to. This is also a good example showing that accessing data through the IMCS is faster than accessing the same data through the buffer cache.



TEST 4
The fourth and final test is a continuation of the previous one. Its aim is to check the performance of a parallel query. Hence, I added the PARALLEL hint to the test query. Also note that I flushed the buffer cache of both instances before executing the test query.

SQL> SELECT /*+ gather_plan_statistics parallel */ sum(n_32)
  2  FROM t;

 SUM(N_32)
----------
1550000000

Elapsed: 00:00:01.49

SQL> SELECT * FROM table(dbms_xplan.display_cursor('br2as2yzy7gk4',2,'iostats last'));

SQL_ID  br2as2yzy7gk4, child number 2
-------------------------------------
SELECT /*+ gather_plan_statistics parallel */ sum(n_32) FROM t

Plan hash value: 3126468333

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |          |      1 |        |      1 |00:00:01.41 |       7 |      1 |
|   1 |  SORT AGGREGATE                 |          |      1 |      1 |      1 |00:00:01.41 |       7 |      1 |
|   2 |   PX COORDINATOR                |          |      1 |        |     10 |00:00:01.34 |       7 |      1 |
|   3 |    PX SEND QC (RANDOM)          | :TQ10000 |      0 |      1 |      0 |00:00:00.01 |       0 |      0 |
|   4 |     SORT AGGREGATE              |          |      0 |      1 |      0 |00:00:00.01 |       0 |      0 |
|   5 |      PX BLOCK ITERATOR          |          |      0 |    100M|      0 |00:00:00.01 |       0 |      0 |
|*  6 |       TABLE ACCESS INMEMORY FULL| T        |      0 |    100M|      0 |00:00:00.01 |       0 |      0 |
---------------------------------------------------------------------------------------------------------------

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

   6 - inmemory(:Z>=:Z AND :Z<=:Z)

Note
-----
   - automatic DOP: Computed Degree of Parallelism is 10
   - parallel scans affinitized for inmemory

The execution took 1.49 seconds. In other words, the performance of the parallel query is way better than the one of TEST 3. The reason can be seen in the Note section of the dbms_xplan output: "parallel scans affinitized for inmemory". This means that parallel slaves were started on both instances and that every set of parallel slaves accessed only the data stored in the IMCS of the instance they were attached to. As a result, even though the data was distributed, the performance was good. The only "overhead" compared to the serial execution is that the parallel slaves had to communicate with the query coordinator.



WHAT WE LEARNED

  • DUPLICATE is only applicable to engineered systems (as I write this blog, ODA does not yet support it)
  • On a RAC system, without DUPLICATE, data is distributed across all instances
  • A process can only access data stored in the IMCS of the instance it is connected to
  • Even though an IM scan is used, serial executions on RAC systems might require many logical/physical I/O

In summary, I would now advise to use the In-Memory option on a RAC system that does not support the DUPLICATE clause. At least, not in general.

The Cloud UX Lab

There’s a post over on VoX about a OAUX new lab at Oracle HQ, the Cloud UX Lab.

labwidewithJacopy

Jeremy Ashley, VP, in the new lab, image used with permission.

Finished just before OOW in September, this lab is a showcase for OAUX projects, including a few of ours.

The lab reminds me of a spacecraft from the distant future, the medical bay or the flight deck. It’s a very cool place, directly inspired and executed by our fearless leader, Jeremy Ashley (@jrwashley), an industrial designer by trade.

I actually got to observe the metamorphosis of this space from something that felt like a doctor’s office waiting room into the new hotness. Looking back on those first meetings, I never expected it would turn out so very awesome.

Anyway, the reason why I got to tag along on this project is because our team will be filling the control room for this lab with our demos. Noel (@noelportugal) and Jeremy have a shared vision for that space, which will be a great companion piece to the lab and equally awesome.

So, if you’re at Oracle HQ, book a tour and stop by the new Cloud UX Lab, experience the new hotness and speculate on what Noel is cooking up behind the glass.Possibly Related Posts:

OTN APAC tour stop 2 – Shanghai, China

Contrary to what my family, friends and coworkers believe, not every aspect of these OTN tours is glamour or relaxation, but there is actually quite a lot of dull travelling involved (with the time used to compose blog posts like this one). The travel to Shanghai was such a day. After a last dinner in Perth, Tim and I hitched a ride with Connor to the airport, then spent the first part of the night there, waiting for our plane to depart shortly after 2am. I managed to sleep a fair bit of that first leg to Singapore where the little transition time that we had was a great reminder of the wonderful time I spent there just the prior weekend. This was one of those times were I would not have minded a cancelled flight and an extra day there. But our flight to Shanghai was right on time and another 4 to 5 hours long.
We took the maglev train from the airport which was quite an experience because a) you do not get to ride 430km/h every day and b) it was good to see what was done with my german tax money that went into the research of this technology. I felt adventurous so instead of taking a taxi for the final bit to the hotel we took the metro after a bit of a fight with the ticket machine. First of all there was only one machine that accepted bills so it had a bit of a queue. And when it was our turn we could not buy the tickets we needed because the english screen did not allow us to select the right line (only a subset). But after a bit of pointing the guy behind us helped out.

OTN APAC Shanghai posterLater I enjoyed a wonderful dinner in a restaurant that was also partly a museum, met two of the other speakers. Maclean Liu from the UG and also the sponsororing partner in Shanghai, Joe Huang who has been working for Oracle at HQ as a product manager for years but was raised in Taiwan, so he knew mandarin. And then also our liasion and local coordinator Bo Feng who did an excellent job at looking after the international speakers and introduced us to a lot of new and strange food items.

Again, I felt adventurous and walked from my hotel to the conference site which was about 40 minutes. And I was pretty much blown away by stepping into a completely different world. I passed a street packed with little hardware shops selling all kinds of screws, nuts and bolts and other building materials, then some very local and raw parts of town next to the river. It really felt like diving into a completely different world.

The conference started with updates by 刘冰冰, a lady from Oracle, and even though all I could understand were a couple of keywords (CDB, PDB, big data SQL, cloud) I am pretty sure I have heard a very similar presentation in English a few times already. The audience understood english well enough but the organizers also translated the slides of Tim and Giusseppe Maxia into chinese so when they were talking they sometimes had to guess at what each bullet point said because there was not much english text left. Tim talked about how to avoid the most commin performance problems in PL/SQL and it was an excellent presentation with good points and a lot of small demos.
Giuseppe talked about mysql replication, how to set it up and also how to monitoring and some advanced cases, I am already looking forward to seeing him again at the Beijing conference in a few days.

OTN APAC Shanghai raffle winnersI did my talk on RAC connectivity which I have done many times now. I am sometimes getting a bit tired of my older presentations but still like this one a lot and think I should maybe turn this more into a general talk about RAC rather than just talk about load balancing and services.

Since the rest of the sessions were going to be in chinese we snuck out and walked around a bit. One of the more bizarre things we saw was a shopping mall with 3 or 4 floors of nothing but mobile phones and accessories. Even some repair shops were people were taking phones apart. We wondered how many of the phones we saw were counterfeits and how many were originals.

OTN APAC Shanghai selfieWe made it back to for the end of the conference and enjoyed another great dinner with the guys from the Shanghai User Group and speakers. The oddest food was fermented stinky tofu. Think of blue cheese. Tim made the mistake of taking a sniff first, then eating, I just avoided breathing through my nose. The taste was actually pretty good.

I decided to go for a brief walk over to the Bund and take some pictures of the Pudong skyline. It was a bit over the top with the colourful blinking lights and screens and couples in cheap tuxedos and polyester dresses having their pictures taken. The constant presence of hawkers advertising cheap “massages” was very annoying and I dared to walk about in some of the sidestreets and was rewarded with scenes of street food carts, interesting smells (both good and bad) and just generally loads of bustling life happening in the streets.

poor man ActiveDirectory password checker

To have the same users in multiple databases and no single sign on is quite a nightmare for password expiration, synchronisation and validation.

You probably were discouraged by the long long route to kerberos, where the 11.2.0.2 bugs are fixed in 11.2.0.4, the 12.1 bugs are fixed in 12.2. And lot’s of system changes that won’t be welcome by your sysadmins / winadmins.

Okay, to partly cover the password expiration issue, you could check in a profile function that the password is the one from AD.

Firstly, without SSL


CREATE OR REPLACE FUNCTION pw_function_AD
(username varchar2,
 password varchar2,
 old_password varchar2)
RETURN boolean IS
  sess raw(32);
  rc number;
BEGIN
  sess := DBMS_LDAP.init(
    'example.com',dbms_ldap.PORT);
  rc := DBMS_LDAP.simple_bind_s(
    sess, username||'@example.com', 
    password);
  rc := DBMS_LDAP.unbind_s(sess);
  RETURN(TRUE);
EXCEPTION
  WHEN OTHERS THEN
    rc := DBMS_LDAP.unbind_s(sess);
    raise;
END;
/
GRANT EXECUTE ON pw_function_ad TO PUBLIC;
CREATE PROFILE AD LIMIT 
  PASSWORD_VERIFY_FUNCTION pw_function_AD;
ALTER PROFILE AD LIMIT 
  PASSWORD_LIFE_TIME 30;
ALTER PROFILE AD LIMIT 
  PASSWORD_REUSE_MAX UNLIMITED;

alter user lsc profile AD;

When the password expires, the user must change it to its AD Password.

If I try with a dummy password, the profile will reject this


SQL> conn lsc/pw1
ERROR:
ORA-28001: the password has expired

Changing password for lsc
New password:anypassword
Retype new password:anypassword
ERROR:
ORA-28003: password verification for 
  the specified password failed
ORA-31202: DBMS_LDAP: LDAP client/server 
  error: Invalid credentials. 
  80090308: LdapErr: DSID-0C0903A9, 
  comment: AcceptSecurityContext error, 
    data 52e, v1db1
Password unchanged
Warning: You are no longer connected to ORACLE.

I need to enter my Windows password


SQL> conn lsc/pw1
ERROR:
ORA-28001: the password has expired

Changing password for lsc
New password: mywindowspassword
Retype new password: mywindowspassword
Password changed
Connected.

Secondly, with SSL.

Maybe simple bind without SSL is not possible (check http://support.microsoft.com/kb/935834). And for sure it is better to not send unencrypted plain text password over the network.

Create a wallet with password with the ROOT Certification Authority that signed your AD. You probably could download this in your trusted root certification authorities in Internet Explorer.

Internet Explorer – Tools – Internet Options – Content – Certificates – Trusted root.

Then you create a ewallet.p12 with orapki. No need for user certificate and no need for single-sign-on. Only import the trusted root (and intermediaries if applicable).

Here is the modified code


CREATE OR REPLACE FUNCTION pw_function_AD
(username varchar2,
 password varchar2,
 old_password varchar2)
RETURN boolean IS
  sess raw(32);
  rc number;
BEGIN
  sess := DBMS_LDAP.init(
    'example.com',dbms_ldap.SSL_PORT);
  rc := DBMS_LDAP.open_ssl(
    sess, 'file:/etc/wallet/MSAD', 
    'welcome1', 2);
  rc := DBMS_LDAP.simple_bind_s(
    sess, username||'@example.com', 
    password);
  rc := DBMS_LDAP.unbind_s(sess);
  RETURN(TRUE);
EXCEPTION
  WHEN OTHERS THEN
    rc := DBMS_LDAP.unbind_s(sess);
    raise;
END;
/

If you get SSL Handshake, be prepared, it could be anything! Check your wallet, your certificate, your permission, your wallet password.

One step further could be to expire users as soon as they change their password in AD or when they expire there.

For instance with powershell goodies for active directory


PS> (Get-ADuser lsc -properties PasswordLastSet).PasswordLastSet

Montag, 6. Oktober 2014 08:18:23

PS> (Get-ADuser king -properties AccountExpirationDate).AccountExpirationDate

Mittwoch, 16. Juli 2014 06:00:00

And in the database


SQL> SELECT ptime FROM sys.user$ 
  WHERE name ='LSC';

PTIME
-------------------
2014-11-10_10:33:08

If PTIME is less than PasswordLastSet or if AccountExpirationDate is not null, expire the account.

In conclusion : if you do not want to use Kerberos, nor Oracle “OctetString” Virtual Directory ovid nor Oracle Internet directory oid, this workaround may help to increase your security by addressing the “shared” and “expired” accounts problematic

There an additional hidden benefit. You could set up a self-service password reset function and send a generated expired password per mail, that the user won’t be able to change without its AD password

Past Sacrifice

This year is the 100th anniversary of the beginning of World War 1. This was not the war to end wars it was said to be, instead it was the transition from the direct man-on-man combat of earlier conflicts to a scientific, engineered, calculated way of killing people efficiently, and not just military personnel. Explosives […]

OTN APAC 2014 : Shanghai to Tokyo

Not the best night of sleep last night. During the day I noticed the load time for pages on my website is really slowly in China because it hangs waiting for a Google Javascript file to load, which eventually does a timeout. Once the timeout happens the page continues to render. That means it takes over a minute to load the page. Since I couldn’t sleep I decided to get out of bed and fix it. It turned out the Javascript file just renders the “Powered by Google” text in the search box, so I removed that and the pages now load fast. There are still some asynchronous calls to Google code that timeout, but those don’t block the page rendering, so I don’t care about them. The site is usable now… :)

After breakfast I got a taxi to pick Bjoern up from his hotel, before heading off for the airport. It was a quick flight, about 2:35, so it was pretty small compared to what we’ve done so far. I was sitting near some people who were talking really loud. I had my earphones on almost full volume, but the people were still clearly audible. The landing in Tokyo was quite hard, which prompted one of the loud women to wail somewhat, then the woman across the aisle was sick in a bag. I didn’t think the landing was that bad… :)

From there we got the Narita Express into town and a short taxi ride to Bjoern’s hotel. He dropped his stuff off, then we got a taxi over to my hotel, where most of the other speakers are staying. We took a walk around the local area to look for food, but I got scared as everything looked like it contained meat or fish, so I got some fries from McDonalds. I will try some proper food tomorrow, when we have someone who speaks Japanese to help make the choices. :)

I’m a little worried about my hotel room as the bathroom has been taken over by a Dalek.

Toilet

Cheers

Tim…


OTN APAC 2014 : Shanghai to Tokyo was first posted on November 10, 2014 at 2:12 pm.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.

Is Continuous Integration compatible with database applications ?

Continuous integration offers huge efficiency gains for companies  but is continuous integration even possible when the application’s backbone is a massive relational database. How can one spin up database copies for developers, QA, integration testing,  and delivery testing ?  Its not like Chef or Puppet can spin up a 10TB database copy in a few minutes the way one can spin up a Linux VM.

There is a way and that way is called data virtualization which allows one to spin up that 10TB database in minutes as well as branch a copy of that 10TB from Dev to QA, or for that matter branch several copies and all for a very little storage.

227840.strip.print

Old methods of application project development and rollout have a solid history of failure to meet deadlines and budget goals.

Continuation Integration (CI),  Continuous Delivery  and Agile offer an opportunity to hit deadlines on budget with tremendous gains in efficiency for companies as opposed to waterfall methods. With waterfall methods we try to get all the requirements, specifications  and architecture designed up front and then set the development teams working and then tackle integration and deployment near the end of the cycle. It’s impossible to be able to precisely target dates when the project will be completed and sufficiently QA’ed. Even worse during integration problems and bugs start to pour in further exacerbating the problems of meeting release dates on time.

Agile, CI and CD fix these issues, but there is one huge hurdle for most shops and that hurdle is getting the right data, especially when the data is large, into the Agile, CI and CD life cycle and flowing through that lifecycle.

With Agile, Continuous Integration and Continuous Delivery we are constantly getting feedback on where were are, how fast we are going and we are altering our course. Our course is also open to changing as new information comes in on customer requirements.

Agile development calls for short sprints, among other things,  for adding features and functions and with continuous integration those features can be tested daily or multiple times a day. Further enhancing continuous integration is continuos delivery, for those systems that make sense, where new code that has passed continuous integration can be rolled into continuous delivery meaning testing the code deployment in a test environment. For some shops, where it makes sense such as famously flickr, Facebook, Google the code can be passed into continuous deployment into production.

By using agile programming methods and constantly doing integration testing one can get constant feedback, do course correction, reduce technical debt and stay on top of bugs.

Compare the two approaches in the  graphs below.

In the first graph we kick off the projects. With waterfall we proceed until we are near completion and then start integration and delivery testing. At this point we come to realize how far from the mark we are. We hurriedly try to get back to the goal, but time has run out. Either we release with less than the targeted functionality or worse the wrong functionality or we miss the

Screen Shot 2014-11-07 at 2.39.09 PM

With Agile and CI its much easier to course correct with small iterations and the flexibility to modify the designs based on incoming customer and market requirements.

Screen Shot 2014-11-07 at 2.40.47 PM

With Agile and CI, code is tested in an integrated manner as soon as the first sprint is done so bugs are caught early and kept in check. With waterfall, since it takes so much longer to get working set of code working and integration isn’t even started until near the end of the cycle, bugs start to accumulate significantly towards the end of the cycle.

Screen Shot 2014-11-07 at 1.19.46 PM

In waterfall, deployment doesn’t even happen until the end of the cycle because there isn’t an integrated deployable set of code until the end. The larger the project gets and the more time goes by the more expensive and difficult the deployment is. With agile and CI the code is constantly deployable and so the cost of deployment stays constant at a low cost.

Screen Shot 2014-11-07 at 1.19.35 PM

A waterfall project can’t even start to bring in revenue until it’s completely finished but with agile, there is usable code early on and with continuous deployment that code can be leveraged for revenue early.

With all these benefits, more and more shops are moving towards continuos integration and continuos.  With tools like Jenkins,  Team City, Travis to run continuos integration test and virtualization technologies such as VMware, AWS, Openstack, Vagrant, Docker and tools like Chef, Puppet and Ansible to run the setup and configuration many shops have moved closer and closer to continuos integration and delivery. 

But there is one huge road block.

 Gene Kim lays out the top 2 bottlenecks in  IT

  1. Provisioning environments for development
  2. Setting up test and QA environments

and goes on to say

One of the most powerful things that organizations can do is to enable development and testing to get environment they need when they need it.

From Contino’s recent white paper 

Having worked with many enterprise organisations on their DevOps initiatives, the biggest pain point and source of wastage that we see across the software development lifecycle is around environment provisioning, access and management.

From an article published today in Computing [UK]  we hear the problem voiced:

“From day one our first goal was to have more testing around the system, then it moves on from testing to continuous delivery,” 

But to achieve this, while at same time maintaining the integrity of datasets, required a major change in the way Lear’s team managed its data.

“It’s a big part of the system, the database, and we wanted developers to self-serve and base their own development in their own controlled area,” he says.

 Lear was determined to speed up this process, and began looking for a solution – although he wasn’t really sure whether such a thing actually existed.

This road block as been voiced by experts in the industry more and more as the industry moves towards continuos integration.

continuous-deliveryWhen performing acceptance testing or capacity testing (or even, sometimes, unit testing), the default option for many teams is to take a dump of the production data. This is problematic for many reasons (not least the size of the dataset),

Humble, Jez; Farley, David (2010-07-27). Continuous Delivery: Reliable Software Releases through Build, Test, and Deployment Automation (Addison-Wesley Signature Series (Fowler)) (Kindle Locations 7285-7287). Pearson Education. Kindle Edition.

What can we do about this enormous obstacle to Continous Integration of providing environments that rely on databases and these databases are too big and complex to provide copies for development, QA and continuos integration?

Fortunately for us there is data virtualization technology. As virtual machine technology opened the door to  continuos integration, data virtualization swings it wide open for enterprise level application development that depend on large databases.

Data virtualization is an architecture (that can be encapsulated in software as Delphix has done) which connects to source data or database , take an initial copy and then and forever collects only the changes from the source (like EMC SRDF, Netapp SMO, Oracle Standby database). The data is saved on storage that has either snapshot capabilities (as in Netapp & ZFS or software like Delphix that maps a snapshot filesystem onto any storage even JBODs). The data is managed as a timeline on the snapshot storage. For example Delphix saves by default 30 days of changes. Changes older than the 30 days are purged out, meaning that a copy can be made down to the second anywhere within this 30 day time window. (some other technologies that address part of the virtual data stack are Oracle’s Snap Clone and Actifio).

Virtual data improves businesses’ bottom line by eliminating the enormous infrastructure, bureaucracy and time drag that it takes to provision databases and data for business intelligence groups and development environments. Development environments and business intelligence groups depend on having a copies of production data and databases and data virtualization allows provisioning in a few minutes with almost no storage overhead by sharing duplicate blocks among all the copies.

 

As a side note, but important, development and QA often require that data be masked to hide sensitive information such as credit cards or patient records,  thus its important that a solution come integrated with masking technology.  Data virtualization combined with masking can vastly reduce the surface area (amount of potentially exposed data) required to secure but eliminating full copies. Also data virtualization structure includes chain of authority where who had access to what data at what time is recorded.

The typical architecture before data virtualization looks like the following where a production database is copied to

  1. backup
  2. reporting
  3. development

In development the copies are further propagated to QA, UAT, but because of the difficulty in provisioning these environments, which takes number teams and people (DBA, storage, system, network, backup) , the environments are limited due to resource constraints and often the data is old and unrepresentative.

Screen Shot 2014-11-09 at 6.28.32 PM

 

With data virtualization, there is a time flow of data states stored efficiently on the “data virtualization appliance” and provisioning a copy  only takes a few minutes, little storage and can be run by a single administrator or even run as self service by the end users such as developers, QA and business analysts.

Screen Shot 2014-11-09 at 6.28.54 PM

 

With the ease of provisioning large data environments quickly easily and for low resources, it become easy to quickly provision copies of environments for development and to branch in minutes those copies into multiple parallel QA lanes to enable continuous integration:

 

 

 

 

Screen Shot 2014-11-09 at 6.32.22 PM

The duplicate data blocks, which is the majority  in the case of large data sets, can even be shared across development versions:

Screen Shot 2014-11-10 at 4.49.14 AM