Mauro Pagano's Blog

Simple script to remove system-generated column groups

7 Comments

There seem to be a lot of interest (at least on Twitter and at OUG conferences) about Oracle recommendation to install a couple patches on top of 12.1.0.2, in order to emulate 12.2 behavior when it comes to SQL Plan Directives (details here, need MOS account).
One of the things SQL Plan Directives do is trigger column groups (CG) creation.
Column groups are virtual columns representing a hash (SYS_OP_COMBINED_HASH) of the multiple table columns they are defined on (that’s why only equality conditions can be satisfied by CG) and they have an ugly long system-generated name. According to the DECODE in ALL_STAT_EXTENSIONS user-generated GC get a SYS_STU prefix in the name while system-generated one get SYS_STS. As far as I could tell only SPD-triggered CGs are named SYS_STS%(corrections are very welcome here), even those created as consequence of using DBMS_STATS.SEED_COL_USAGE have SYS_STU% name.

The other day somebody asked how to remove those CG in case one wanted to “start fresh” after applying the mentioned 12.1.0.2 patches. I wrote a little script that was by no mean intended to be exhaustive (or fully tested) but was good way to get started removing SPD-triggered CGs so I figured I would share, the script starts from the assumption only SPD-triggered CGs have a SYS_STS% name.
The script does NOT remove the CGs by itself, it just creates another script that include the DROP in there so that you can read, digest and only then execute it manually. Also another script is created, just to put the CG back in place (just the definition, no stats are gathered) in case some are indeed needed. Little side effect is since you put them in place manually then the names become SYS_STU% and not SYS_STS%.

Code below

PRO
PRO usage @drop_extended_stats.sql connected as the user that owns the table
PRO and pass the table name when requested.
PRO
DEF current_table = '&&table_name.'
SET SERVEROUTPUT ON VERI OFF FEED OFF TIMING OFF
SPO drop_extended_stats_&&current_table._driver.sql
BEGIN
 FOR i IN (SELECT extension_name, extension
             FROM user_stat_extensions
            WHERE table_name = UPPER('&&current_table.')
              AND extension_name LIKE 'SYS_STS%' -- SYS_STS are system generated from the DECODE in ALL_STAT_EXTENSIONS
           ) LOOP

   DBMS_OUTPUT.PUT_LINE('-- Dropping extension on '||i.extension);
   DBMS_OUTPUT.PUT_LINE('exec DBMS_STATS.DROP_EXTENDED_STATS(user, ''&&current_table.'', '''||i.extension||''');');

 END LOOP;

END;
/
SPO OFF
SPO create_extended_stats_&&current_table._driver.sql
BEGIN
 FOR i IN (SELECT extension_name, extension
             FROM user_stat_extensions
            WHERE table_name = UPPER('&&current_table.')
              AND extension_name LIKE 'SYS_STS%' -- SYS_STS are system generated from the DECODE in ALL_STAT_EXTENSIONS
           ) LOOP

   DBMS_OUTPUT.PUT_LINE('-- Dropping extension on '||i.extension);
   DBMS_OUTPUT.PUT_LINE('SELECT DBMS_STATS.CREATE_EXTENDED_STATS(user, ''&&current_table.'', '''||i.extension||''') FROM dual;');

 END LOOP;

END;
/
SPO OFF
SET SERVEROUTPUT OFF VERI ON FEED ON TIMING ON

UPDATE: just learned about this MOS note

7 thoughts on “Simple script to remove system-generated column groups

  1. Thanks Mauro, will be really useful as today we encountered this nasty bug : ORA-28113 & ORA-00904 On A 12c Database with VPD, FGA And Extended Statistics(Virtual Columns) (Doc ID 2199556.1)

    Hope to see you one day again eventhough I don’t do much Oracle stuff anymore.. 😦

    Like

  2. What about using the dba_stat_extensions.creator column to determine if the stats extension was created by a user vs the system?

    Like

  3. What about using the dba_stat_extensions.creator column to determine if the stats extension was created by a user vs the system?

    Like

  4. Pingback: Adaptive mayhem | Oracle Scratchpad

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s