Oracle DB Histograms.

select * from DBA_TAB_STAT_PREFS;



12:31 PM

LN_MI 

 LN_BRWR_GRP 

 BR_BRWR_CRDT_SCR

BEGIN

    dbms_stats.delete_column_stats(

          ownname=>'ELODBP06', tabname=>'LN_MI', colname=>'PROD_ID',

                                   col_stat_type=>'HISTOGRAM');

 END;







exec dbms_stats.set_table_prefs(ownname=>'ABCD05', tabname=>'TAB1', pname=>'METHOD_OPT',pvalue=>'FOR ALL COLUMNS SIZE AUTO FOR COLUMNS SIZE 1 MD_BCH_OBJ_ID') 



exec DBMS_STATS.DELETE_TABLE_PREFS ('ABCD06', 'TAB2', 'METHOD_OPT');

exec DBMS_STATS.DELETE_COLUMN_STATS('ABCD06', 'TAB2', 'METHOD_OPT');



How does the METHOD_OPT parameter work? 

https://blogs.oracle.com/optimizer/how-does-the-methodopt-parameter-work

POELO02

ELODBP06 MD_SRC_SYS_LKUP          METHOD_OPT           FOR COLUMNS SIZE 1 MD_SRC_SYS_LKUP_OBJ_ID FOR ALL COLUMNS SIZE AUTO

 ELODBP06 MD_DATA_ENUM_LKUP        METHOD_OPT           FOR COLUMNS SIZE 1 MD_DATA_ENUM_LKUP_OBJ_ID FOR ALL COLUMNS SIZE AUTO

 ABCD05 TAB1              METHOD_OPT           FOR ALL COLUMNS SIZE AUTO FOR COLUMNS SIZE 1 MD_BCH_OBJ_ID





select count(*) from ABCD12.MD_SRC_SYS_LKUP;

select count(*) from ABCD12.MD_DATA_ENUM_LKUP;





exec dbms_stats.delete_column_stats(ownname=>'ABCD12', tabname=>'MD_SRC_SYS_LKUP', colname=>'MD_SRC_SYS_LKUP_OBJ_ID', col_stat_type=>'HISTOGRAM'); 

 exec dbms_stats.delete_column_stats(ownname=>'ABCD12', tabname=>'MD_DATA_ENUM_LKUP', colname=>'MD_DATA_ENUM_LKUP_OBJ_ID', col_stat_type=>'HISTOGRAM')



BEGIN

 dbms_stats.set_table_prefs(

 ownname=>'ABCD12', 

 tabname=>'MD_SRC_SYS_LKUP', 

 pname=>'METHOD_OPT',

 pvalue=>'FOR COLUMNS SIZE 1 MD_SRC_SYS_LKUP_OBJ_ID FOR ALL COLUMNS SIZE AUTO');

 END;

 /



BEGIN

 dbms_stats.delete_table_stats(ownname=>'ABCD12', tabname=>'MD_SRC_SYS_LKUP');

 END;

 /

BEGIN

 dbms_stats.gather_table_stats('ABCD12', 'MD_SRC_SYS_LKUP',

 method_opt=>'FOR COLUMNS SIZE 1 MD_SRC_SYS_LKUP_OBJ_ID FOR ALL COLUMNS SIZE AUTO',

 force => true,

 degree=>32);

 END;

 /

















BEGIN

 dbms_stats.set_table_prefs(

 ownname=>'ABCD12', 

 tabname=>'MD_DATA_ENUM_LKUP', 

 pname=>'METHOD_OPT',

 pvalue=>'FOR COLUMNS SIZE 1 MD_DATA_ENUM_LKUP_OBJ_ID FOR ALL COLUMNS SIZE AUTO');

 END;

 /



BEGIN

 dbms_stats.delete_table_stats(ownname=>'ABCD12', tabname=>'MD_DATA_ENUM_LKUP');

 END;

 /

BEGIN

 dbms_stats.gather_table_stats('ABCD12', 'MD_DATA_ENUM_LKUP',

 method_opt=>'FOR COLUMNS SIZE 1 MD_DATA_ENUM_LKUP_OBJ_ID FOR ALL COLUMNS SIZE AUTO',

 force => true,

 degree=>32);

 END;

 /









select * from DBA_TAB_STAT_PREFS;



12:31 PM



BEGIN

 dbms_stats.delete_table_stats(ownname=>'ABCD05', tabname=>'TAB1');

 END;

 /

BEGIN

 dbms_stats.gather_table_stats('ABCD05', 'TAB1',

 method_opt=>'FOR ALL COLUMNS SIZE AUTO FOR COLUMNS SIZE 1 MD_BCH_OBJ_ID',

 force => true,

 degree=>32);

 END;

 /



BEGIN

 dbms_stats.gather_table_stats('ELODBP06', 'MD_DATA_ENUM_LKUP',

 method_opt=>'FOR COLUMNS SIZE 1 MD_DATA_ENUM_LKUP_OBJ_ID FOR ALL COLUMNS SIZE AUTO',

 force => true,

 degree=>32);

 END;

 /





BEGIN

 dbms_stats.set_table_prefs(ownname=>'ABCD05', 

 tabname=>'TAB1', 

 pname=>'METHOD_OPT',

 pvalue=>'FOR ALL COLUMNS SIZE AUTO FOR COLUMNS SIZE 1 MD_BCH_OBJ_ID',

 degree=>32);

 END;

 /



exec DBMS_STATS.DELETE_TABLE_PREFS ('ABCD06', 'TAB2', 'METHOD_OPT');



How does the METHOD_OPT parameter work? 

https://blogs.oracle.com/optimizer/how-does-the-methodopt-parameter-work

4 comments:

  1. AnonymousTuesday, December 07, 2021

    Cleanup Existing Audit Logs

    1) Truncate Current AUD$
    2) Clear $ORACLE_HOME/rdbms/audit (SYS Auditing Dest)
    3) Clear audit_file_dest location: /opt/oracle/app/admin/prism/adump
    4) Clear audit Rules "NOAUDIT ALL"


    -------------------------------------------------------------------------------

    Enabling Unified Auditing (Required Downtime):

    Ref: How To Enable Unified Audit binaries on RAC Nodes ? (Doc ID 2371837.1)


    1)Stop All Process running on rdbms Home (DB , Listener, Enterprise Manager)

    2) Relink Binaries On All Nodes One after other

    $ cd $ORACLE_HOME/rdbms/lib
    $ make -f ins_rdbms.mk uniaud_on ioracle

    Cheack Auditing Status:
    select * from gv$option where parameter like 'Unified Auditing';
    >> Should be True on all Noads
    ------------------------------------------------------------------------------

    Enable Purging Jobs for Audit Trail Records


    BEGIN
    DBMS_AUDIT_MGMT.set_last_archive_timestamp(
    audit_trail_type => DBMS_AUDIT_MGMT.audit_trail_unified,
    last_archive_time => trunc(SYSTIMESTAMP-90)
    );
    END;
    /

    Last Archive Date:
    SELECT audit_trail,
    last_archive_ts
    FROM dba_audit_mgmt_last_arch_ts;


    Manual Cleaup:
    exec DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL( -
    AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, -
    USE_LAST_ARCH_TIMESTAMP => TRUE);

    exec DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL( -
    AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, -
    USE_LAST_ARCH_TIMESTAMP => FALSE);

    exec DBMS_AUDIT_MGMT.CREATE_PURGE_JOB (-
    AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, -
    AUDIT_TRAIL_PURGE_INTERVAL => 24, -
    AUDIT_TRAIL_PURGE_NAME => 'UNIFIED_AUDIT_TRAIL_PURGE', -
    USE_LAST_ARCH_TIMESTAMP => TRUE);

    =================================================================
    Audit Policy Creation:

    Views :
    Select * from AUDIT_UNIFIED_POLICIES

    select * from AUDIT_UNIFIED_ENABLED_POLICIES


    Statments:

    CRETE POLICY ORA_STETMENT ACTIONS ALTER TABLE;

    ALTER AUDIT POLICY ORA_STETMENT ADD ACTIONS ALTER USER;



    ** Neet to generate Statments based on Current Audit Values
    ** These Statment Can be generated in advance


















    ReplyDelete
  2. AnonymousTuesday, December 07, 2021

    CREATE OR REPLACE PROCEDURE AUDSYS.P_COPY_AUDIT_HIST is

    run_count number;
    u_audit_count number;
    u_audit_hist_count number;
    copy_stmt varchar2 (1000);

    BEGIN

    select count(*) into run_count from AUDSYS.AUDIT_HIST_COPY_STATS where to_char(COPY_PROC_RUN_DATE ,'DD-MON-YYYY')=to_char(SYSDATE,'DD-MON-YYYY');

    if run_count = 0
    then

    --copy_stmt :='insert into audsys.AUD_UNIFIED_HIST select * from audsys.aud$unified where to_char(event_timestamp,''''DD-MON-YYYY'''')=to_char(SYSDATE -1,''''DD-MON-YYYY'''')';
    --execute immediate copy_stmt;
    insert into audsys.AUD_HIST
    select * from sys.aud$ where trunc(ntimestamp#)=trunc(SYSDATE -1);
    commit;

    --DBMS_OUTPUT.PUT_LINE('String'||t_create_stme );
    DBMS_STATS.GATHER_TABLE_STATS('AUDSYS','AUD_HIST',DEGREE => 10);

    select count(*) into u_audit_count from sys.aud$ where trunc(ntimestamp#)=trunc(SYSDATE -1);
    select count(*) into u_audit_hist_count from audsys.AUD_HIST where trunc(ntimestamp#)=trunc(SYSDATE -1);


    insert into AUDSYS.AUDIT_HIST_COPY_STATS values(SYSDATE,trunc(SYSDATE -1),u_audit_count,u_audit_hist_count );
    commit;
    end if;
    DBMS_AUDIT_MGMT.set_last_archive_timestamp(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,last_archive_time => trunc(SYSTIMESTAMP-3));
    DBMS_AUDIT_MGMT.set_last_archive_timestamp(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,last_archive_time => trunc(SYSTIMESTAMP-3),database_id=>1803205224);

    end p_COPY_AUDIT_HIST;
    /

    ReplyDelete
  3. AnonymousThursday, December 09, 2021

    SQL>
    SQL> -- 2.Confirm the tablespace before executing DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION.
    SQL> col PARAMETER_NAME format a30
    SQL> col PARAMETER_VALUE format a20
    SQL> col AUDIT_TRAIL format a20
    SQL> select * from DBA_AUDIT_MGMT_CONFIG_PARAMS where AUDIT_TRAIL = 'UNIFIED AUDIT TRAIL' and PARAMETER_NAME='DB AUDIT TABLESPACE';

    PARAMETER_NAME PARAMETER_VALUE AUDIT_TRAIL
    ------------------------------ -------------------- --------------------
    DB AUDIT TABLESPACE SYSAUX UNIFIED AUDIT TRAIL

    SQL> col owner for a10;
    SQL> col table_name format a30;
    SQL> col tablespace_name format a20;
    SQL> select owner , table_name from dba_tables where owner = 'AUDSYS';

    OWNER TABLE_NAME
    ---------- ------------------------------
    AUDSYS AUD$UNIFIED

    SQL> -- 3.Create tablespace AUDIT_TS01.

    SQL> create tablespace AUDIT_TS01 datafile 'AUDIT_TS01.dbf' size 50M autoextend on;

    Tablespace created.

    SQL> -- 4.Move unified audit to tablespace AUDIT_TS01 by executing SET_AUDIT_TRAIL_LOCATION.

    SQL> EXEC DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,audit_trail_location_value => 'AUDIT_DATA') ;

    PL/SQL procedure successfully completed.

    SQL> select owner , table_name from dba_tables where owner = 'AUDSYS';

    OWNER TABLE_NAME
    ---------- ------------------------------
    AUDSYS AUD$UNIFIED
    AUDSYS CLI_SWP$fa52015f$1$1

    ReplyDelete
  4. AnonymousTuesday, December 21, 2021

    3) Move Standard Audit Tables to New table space

    SELECT table_name, tablespace_name FROM dba_tables
    WHERE table_name IN ('AUD$', 'FGA_LOG$') ORDER BY table_name;

    BEGIN
    DBMS_AUDIT_MGMT.set_audit_trail_location(
    audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
    audit_trail_location_value => 'AUDIT_DATA');
    END;
    /


    BEGIN
    DBMS_AUDIT_MGMT.set_audit_trail_location(
    audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,
    audit_trail_location_value => 'AUDIT_DATA');
    END;
    /



    4) Recompile Invalid Objects

    SQL> @?/rdbms/admin/utlrp.sql


    5) Check Audit Purging Initialized

    SET SERVEROUTPUT ON
    BEGIN
    IF DBMS_AUDIT_MGMT.is_cleanup_initialized(DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD) THEN
    DBMS_OUTPUT.put_line('YES');
    ELSE
    DBMS_OUTPUT.put_line('NO');
    END IF;
    END;
    /


    6) Initialize Audit Purging


    BEGIN
    DBMS_AUDIT_MGMT.deinit_cleanup(
    audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL);
    END;
    /

    BEGIN
    DBMS_AUDIT_MGMT.init_cleanup(
    audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
    default_cleanup_interval => 24 /* hours */);
    END;
    /



    7) Enable Last Archive Time Stamp

    BEGIN
    DBMS_AUDIT_MGMT.set_last_archive_timestamp(
    audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
    last_archive_time => SYSTIMESTAMP-3);
    END;
    /

    COLUMN audit_trail FORMAT A20
    COLUMN last_archive_ts FORMAT A40

    SELECT * FROM dba_audit_mgmt_last_arch_ts;



    8) Enable Purging Job


    BEGIN
    DBMS_AUDIT_MGMT.create_purge_job(
    audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
    audit_trail_purge_interval => 24 /* hours */,
    audit_trail_purge_name => 'PURGE_ALL_AUDIT_TRAILS',
    use_last_arch_timestamp => TRUE);
    END;
    /

    SELECT job_action
    FROM dba_scheduler_jobs
    WHERE job_name = 'PURGE_ALL_AUDIT_TRAILS';


    ** Dropping Script
    BEGIN
    DBMS_AUDIT_MGMT.drop_purge_job(
    audit_trail_purge_name => 'PURGE_ALL_AUDIT_TRAILS');
    END;
    /


    NOTE: Audit Purge Job will created be in AUDSYS Schema
    FREQ=DAILY; byhour=03; byminute=00

    ReplyDelete

Subscribe to: Posts (Atom)