Improving FTS oracle



This blog is written after resolving sev1 performance issue post DB migration to new setup

Issue: Sql Full table Scan (FTS) Slowness due to change in execution plan post db migration

Background:
Recently we have migrated 11gr2 Single Node prod. DB (ASM/AIX) to 11gr2 RAC 2 node (Linux) using Data Pump, since then started facing the below issue

Query:
SELECT C1000000159,
       C1000000161,
       C1000000170,
       C1000002134,
       C536871133,
       substr(C1000000151, :"SYS_B_0", :"SYS_B_1") as C1000000151,
       substr(C1000000000, :"SYS_B_2", :"SYS_B_3") as C1000000000,
       C1000000001,
       C1,
       C3,
       C6
  FROM ARADMIN.T2108;

Table: Data-type CLOB/varchar2, Number of Rows 5701535, Size of the Table Segment 12 GB
Execution Plan: Old DB (Single Instance, AIX)
Elapsed Time:    2 Hrs



Execution Plan: New DB (RAC, Linux)
Elapsed Time:    4.5 Hrs



Workarounds:
The application team reluctant to do any alteration in the query (like adding where clause) as it was responding in 2 Hrs before migration
We have done below to make it work as before:

1. Gathered system stats for the interval of 48 Hrs
exec  dbms_stats.gather_system_stats ('INTERVAL',2880);

2. Gathered dictionary stats
exec dbms_stats.gather_dictionary_stats;

3. Gather fixed object stats
execute dbms_stats.gather_fixed_objects_stats;

4. Export/ import of old db stats into new db

--creating stat table in source
exec dbms_stats.create_stat_table('ARADMIN','STAT_T2108');

--collecting stats in stat table
begin
dbms_stats.export_table_stats(
ownname => 'ARADMIN',
tabname => 'T2108',
stattab => 'STAT_T2108'
);
end;
/
--exporting stats from source
exp file=exp_STAT_T2108.dmp log= exp_STAT_T2108.log tables=aradmin.stat_t2108

--importing stat table on source
Imp file=exp_STAT_T2108.dmp log= imp_STAT_T2108.log fromuser=aradmin touser=aradmin

--Importing/Populating stats into stat views
exec dbms_stats.IMPORT_TABLE_STATS('ARADMIN','T2108',STATTAB => 'STAT_T2108' , NO_INVALIDATE => 'FALSE');

5. Applied parallel hint giving slight performance by reduction in 30 min.

SELECT /*+ PARALLEL (T1, 20) */
 C1000000159,
 C1000000161,
 C1000000170,
 C1000002134,
 C536871133,
 substr(C1000000151, :"SYS_B_0", :"SYS_B_1") as C1000000151,
 substr(C1000000000, :"SYS_B_2", :"SYS_B_3") as C1000000000,
 C1000000001,
 C1,
 C3,
 C6
  FROM ARADMIN.T2108 T1;

6. Tried RULE, FIRST_ROWS hints

SELECT /*+ RULE */...
SELECT /*+ FIRST_ROWS */...
SELECT /*+ FIRST_ROWS_1000 */...

7. Gathered stats on table using different methods (with/ without histograms)

exec  dbms_stats.gather_table_stats('ARADMIN','T2108',degree => 10 , no_invalidate => false , cascade => true);
exec  dbms_stats.gather_table_stats('ARADMIN','T2108',degree => 10 ,estimate_percent =>100, no_invalidate => false , cascade => true);
exec  dbms_stats.gather_table_stats('ARADMIN','T2108',degree => 10 ,estimate_percent =>100, block_sample => TRUE, method_opt => 'FOR ALL COLUMNS SIZE 1',no_invalidate => false , cascade => true);

However all above actions failed to improve execution plan or response time of the query

Generally, all above actions should have lead to good sql performance 

Solution:

New RAC DB system stats parameters value have been set to that of Old DB manually
Immediately after setting below values we got the old db execution plan for the query in new db

select * from sys.aux_stats$;
exec dbms_stats.set_system_stats( pname => 'CPUSPEEDNW', pvalue => 978);
exec dbms_stats.set_system_stats( pname => 'IOSEEKTIM', pvalue => 10);
exec dbms_stats.set_system_stats( pname => 'IOTFRSPEED', pvalue => 4096);
exec dbms_stats.set_system_stats( pname => 'SREADTIM', pvalue => 3);
exec dbms_stats.set_system_stats( pname => 'MREADTIM', pvalue => 7);
exec dbms_stats.set_system_stats( pname => 'CPUSPEED', pvalue => 981);
exec dbms_stats.set_system_stats( pname => 'MBRC', pvalue => 11);
exec dbms_stats.set_system_stats( pname => 'MAXTHR', pvalue => 27578368);
exec dbms_stats.set_system_stats( pname => 'SLAVETHR', pvalue => 0);

 

Note:

Setting system stats manually is not recommended it should be gathered manually or allow auto stats collection job to collect it on new system to compute proper values of system resources & take benefit of the CPU, Io, threads, slaves etc.

In our case it was severity 1 issue to get o/p of above query (only one time), above scenario has worked but later we have collected new system stats.

We had 5000 MHz * 5 CPU configuration in Old DB Server but in New DB server it was 1861 MHz * 24 CPU
The per CPU reduction in speed have triggered this issue, however due to parallelism/multithreading & gathering system stats have stabilized the performance issue later
 




6 comments:

  1. There are lots of information about latest technology and how to get trained in them, like Oracle Training have spread around the web, but this is a unique one according to me. The strategy you have updated here will make me to get trained in future technologies(Oracle Training Center in Chennai). By the way you are running a great blog. Thanks for sharing this.

    ReplyDelete
  2. Really awesome blog. Your blog is really useful for me.
    Thanks for sharing this informative blog. Keep update your blog.
    Oracle Training In Chennai

    ReplyDelete
  3. Best Java Training Institute In ChennaiThis information is impressive; I am inspired with your post writing style & how continuously you describe this topic. After reading your post, thanks for taking the time to discuss this, I feel happy about it and I love learning more about this topic..

    ReplyDelete
  4. This comment has been removed by a blog administrator.

    ReplyDelete
  5. It is amazing and wonderful to visit your site.Thanks for sharing this information,this is useful to me...Dot Net Training Institute in Chennai | No.1 Dot Net Training in Chennai | Best Dot Net Training in Chennai | Online Training in Velachery

    ReplyDelete

My Popular Posts