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:
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
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
This comment has been removed by a blog administrator.
ReplyDeleteReally awesome blog. Your blog is really useful for me.
ReplyDeleteThanks for sharing this informative blog. Keep update your blog.
Oracle Training In Chennai
This comment has been removed by a blog administrator.
ReplyDeleteNice post..Keep updating... Embedded Project Center in Chennai | Embedded Training with Placement in Chennai | Embedded Project Center in Chennai
ReplyDeleteThis comment has been removed by a blog administrator.
ReplyDeleteGreat effort you have taken about oracle DBA. You have shared really useful information with us. Thanks to shared your knowledge with us.
ReplyDeleteOracle dba training | DBA training
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.
ReplyDeleteWonderful 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
very easy to learn then super content
ReplyDeleteJava full-stack Training in Chennai
Java full-stack Training in Velachery
Java full-stack Training in Tambaram
Java full-stack Training in Guindy
Java full-stack Training in medavakkam