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