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
 




8 comments:

  1. This comment has been removed by a blog administrator.

    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. This comment has been removed by a blog administrator.

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

    ReplyDelete
  5. Great effort you have taken about oracle DBA. You have shared really useful information with us. Thanks to shared your knowledge with us.
    Oracle dba training | DBA training

    ReplyDelete
  6. Awesome post…It is very interesting to read. I am a regular follower of your blog. Very informative post you shared here. Kindly keep blogging.
    Wonderful information and very much useful. Thanks for sharing and keep updating More like this

    Java Training Institute in Chennai
    Java Training Institute in Velachery
    Java Training Institute in thambaram
    Java Training Institute in Gaind
    Java Training Institute in medavakkam

    ReplyDelete