Extended statistics Performance issue Oracle 12c

Extended statistics Performance Issue in Oracle 12c

Extended Stats in Oracle 12c caused performance issue with following SQL executing multiple times. Find number of execution in Statspack report or AWR Report consuming lot of CPU:
select default$ from col$ where rowid=:1

On Checking,query is related with extended stats in 12c causing performance issue. In 12c, extended stats is created itself as optimizer needed.
Oracle 12c version create extended statistics automatically, it’s fixed in next 12.2 version. In oracle 11g, you have option to create extended statistics( column Group) manually.
you can follow link for manually created:
https://smarttechways.com/2017/01/06/extended-stats-in-oracle
 
In some cases, when we upgrade the database from oracle 11g to 12c.
We faced performance issue due to extended stats (column group ), So we manually disable it and drop the extended stats in database. Steps as following in blog:
 
On checking Statspack report of Oracle Database following query is consuming CPU.
select default$ from col$ where rowid=:1
 
Statspack Report:

SQL ordered by CPU DB/Inst: IC/ic Snaps: 8812-8823
-> Total DB CPU (s): 2,637
-> Captured SQL accounts for 15.8% of Total DB CPU
-> SQL reported below exceeded 1.0% of Total DB CPU

CPU CPU per Elapsd Old
Time(s)  Executions  Exec(s)  %Total  Time(s)  Buffer Gets  Hash Value
-------  ----------  -------  ------  -------  -----------  ----------
75.39    2,610,891   0.00     2.9     79.09    5,221,732    3360804353
select default$ from col$ where rowid=:1


Solution:

1. For stop using extended stats in Oracle 12c. You can disable the hidden parameter.
alter system set "_optimizer_enable_extended_stats"=FALSE scope=both;
 
2. You can also change cursor sharing parameter to “FORCE” if your application is using hard parsing(but not recommended)
alter session set cursor_sharing='force' scope=both;
 
3. You can drop the already created extended stats by following process:

Check the extended stats extension for owner and table:
SELECT extension_name, extension FROM dba_stat_extensions WHERE table_name = 'EMP' and owner ='SCOTT'
If you find the column group or extension present on table, you can take backup of stats and drop the extension (column group) from database by following package:
Note: copy paste extension column of above query to following command:
 
Drop Command:

exec dbms_stats.drop_extended_stats(ownname => 'SCOTT',tabname => 'EMP',extension => '("ENTITY_NO","ACCT_TYPE","DOC_TYPE")');

In my case, By deleting the extended stats will fixed the performance issue in Oracle 12c.
 
Scripts for drop all the table and schema extended stats

spool E:\stats.txt
SET LINESIZE 32000;
SET PAGESIZE 40000;
SET LONG 50000;
SET TRIMSPOOL ON
SET WRAP ON
set termout off
set verify off
set longchunksize 200000
set feedback off
SET HEADING Off
set echo off
SELECT 'exec dbms_stats.drop_extended_stats(ownname =>'''||owner ||''',tabname =>'''||table_name||''',extension => '''|| extension||''');' FROM dba_stat_extensions WHERE owner ='SCOTT';

For more detail, please refer following link:
https://magnusjohanssontuning.wordpress.com/2016/12/27/group-column-statistics-part-2

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.