Tech Tip: Data Load / Staging poor performance / failure

Document created by Tomas Vrbka Employee on Jun 27, 2017Last modified by Tomas Vrbka Employee on Jun 30, 2017
Version 2Show Document
  • View in full screen mode

Let me share with you one effective performance tuning tip (or bug resolution - up to your opinion) which I haven't seen here neither docs. Since performance of any SQL query at Oracle is only as high as actual optimizer statistics are available, we are facing significant inefficiency on Data Load / Staging with consequent post-etl queries configured at Data Source which can leads running Data Load for many hours even days while burning DB server's CPU.

Problem

Daily purge job optionally calculate complete (Oracle Schema? ) optimizer statistics on daily basis just after staging tables are purged. Following next staging job inserts possibly millions of rows into staging tables while right away running post-etl queries on this tables leads to hardly poor execution plan. Statistics has been collected on empty tables! 

Solution

For all huge staging tables which are affected by post-etl queries gather the statistics beforehand.

HowTo

Inspect data source definition xml  file:

for example C:\Program Files\CA\Capacity Command Center 2.9.4\ApacheTomcat\webapps\DM\WEB-INF\classes

at .<dataSource_parameters> <parameter>

add call DBMS_STATS.GATHER_TABLE_STATS to gather table,column and index statistics for all tables which are affected by post_etl queries. For example of CA UIM Physical DA can be tables: SERVER_STAGING and SERVER_METRICS_STAGING:
<param name="post_etl_query0" value="CALL DBMS_STATS.GATHER_TABLE_STATS('your_CCCUSER','SERVER_STAGING')" />
<param name="post_etl_query1" value="CALL DBMS_STATS.GATHER_TABLE_STATS('your_CCCUSER','SERVER_METRICS_STAGING')" />

and reorder post_etl_queries numbering.

 

Result

Data Load overall rows/s can rise up from 10x-100x  to 1000x 

 

Share your opinions and experience.

Attachments

    Outcomes