DX Operational Intelligence

Tech Tip: Data Load / Staging poor performance / failure 

06-27-2017 04:05 AM

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.

Statistics
0 Favorited
1 Views
0 Files
0 Shares
0 Downloads

Tags and Keywords

Comments

03-30-2019 10:16 AM

Hi Tomos,

 

Can you please brief a little more how the changes needs to be done.

In which all files and in which all places.

 

Regards,

Ujjwal

06-29-2017 04:40 AM

Hi Tomas

 

This is a very interesting idea - I have been looking at something similar recently.  I don't have a complete set of evidence yet, but I will include this idea in the next set of investigations and report back in a couple of weeks with what we find out…

 

Thanks for the suggestion.

Related Entries and Links

No Related Resource entered.