Automatic Stats: Gather stats only for oracle resources

If we enable full oracle scheduled maintenance job, it will also have auto stats gathering job enabled. But lot of times we observed that when our application using transient tables(GTT's are not an option due to unavoidable reasons) those get populated and then truncated as part of process, auto stats reset stats to 0 for such tables and it leads to performance issues. So in such scenarios we have to disabled Automatic stats gathering job and schedule custom job as per application requirement.

But generally DBA's disable stats gathering jobs completely and along with this system and dictionary stats also get's disabled. So even if there is hardware change these stats will not be gathered and it may lead to poor performance of database.

To avoid such situations, we can just gather stats on Oracle resources by setting appropriate preferences.

 If we are satisfied that our SQL workload is already optimal, we can set autostats_target to "oracle" to turn off automatic statistics collection in 10g and beyond.

exec dbms_stats.set_param('autostats_target','oracle');

This is a good move if you already have optimal CBO statistics and don't want to risk changing your SQL execution plans.

To check what is current target;

select dbms_stats.get_param ('AUTOSTATS_TARGET') from dual;

Oracle automatic stats gathering job

To check if auto job enabled:
SELECT CLIENT_NAME, STATUS FROM DBA_AUTOTASK_CLIENT WHERE CLIENT_NAME='auto optimizer stats collection';

To check parameters used by auto job:

SET ECHO OFF
SET TERMOUT ON
SET SERVEROUTPUT ON
SET TIMING OFF
DECLARE
   v1  varchar2(100);
   v2  varchar2(100);
   v3  varchar2(100);
   v4  varchar2(100);
   v5  varchar2(100);
   v6  varchar2(100);
   v7  varchar2(100);
   v8  varchar2(100);
   v9  varchar2(100);
   v10 varchar2(100);      
BEGIN
   dbms_output.put_line('Automatic Stats Gathering Job - Parameters');
   dbms_output.put_line('==========================================');
   v1 := dbms_stats.get_prefs('AUTOSTATS_TARGET');
   dbms_output.put_line(' AUTOSTATS_TARGET:  ' || v1);
   v2 := dbms_stats.get_prefs('CASCADE');
   dbms_output.put_line(' CASCADE:           ' || v2);
   v3 := dbms_stats.get_prefs('DEGREE');
   dbms_output.put_line(' DEGREE:            ' || v3);
   v4 := dbms_stats.get_prefs('ESTIMATE_PERCENT');
   dbms_output.put_line(' ESTIMATE_PERCENT:  ' || v4);
   v5 := dbms_stats.get_prefs('METHOD_OPT');
   dbms_output.put_line(' METHOD_OPT:        ' || v5);
   v6 := dbms_stats.get_prefs('NO_INVALIDATE');
   dbms_output.put_line(' NO_INVALIDATE:     ' || v6);
   v7 := dbms_stats.get_prefs('GRANULARITY');
   dbms_output.put_line(' GRANULARITY:       ' || v7);
   v8 := dbms_stats.get_prefs('PUBLISH');
   dbms_output.put_line(' PUBLISH:           ' || v8);
   v9 := dbms_stats.get_prefs('INCREMENTAL');
   dbms_output.put_line(' INCREMENTAL:       ' || v9);
   v10:= dbms_stats.get_prefs('STALE_PERCENT');
   dbms_output.put_line(' STALE_PERCENT:     ' || v10);
END;
/

More convenient way to list parameters:
select
dbms_stats.get_prefs('AUTOSTATS_TARGET' ) AUTOSTATS_TARGET,
dbms_stats.get_prefs('CASCADE' ) CASCADE,
dbms_stats.get_prefs('DEGREE' ) DEGREE,
dbms_stats.get_prefs('ESTIMATE_PERCENT' ) ESTIMATE_PERCENT,
dbms_stats.get_prefs('METHOD_OPT' ) METHOD_OPT,
dbms_stats.get_prefs('NO_INVALIDATE' ) NO_INVALIDATE,
dbms_stats.get_prefs('GRANULARITY' ) GRANULARITY,
dbms_stats.get_prefs('PUBLISH' ) PUBLISH,
dbms_stats.get_prefs('INCREMENTAL' ) INCREMENTAL,
dbms_stats.get_prefs('STALE_PERCENT' ) STALE_PERCENT
from DUAL;

What is the difference between auto stats gathering job and gather_schema_stats?

Both activities use the same parameters. So the stats will look the same - IF they get created. The real difference between the Automatic Statistics Gathering job and a manual invocation of GATHER_SCHEMA_STATS is that the latter will refresh ALL statistics whereas the Automatic Statistics Gathering job will refresh only statistics on objects where statistics are missing or marked as STALE.

The same behavior appears when you compare the recommendation to gather dictionary statistics before the upgrade by using DBMS_STATS.GATHER_DICTIONARY_STATS versus a DBMS_STATS.GATHER_SCHMEA_STATS('SYS')call. The latter will refresh all statistics whereas the first one will take less resources but refresh only STALE and missing statistics.

• The Automatic Statistics Gathering job prioritizes objects with NO statistics over objects with STALE statistics
• The Automatic Statistics Gathering job may get interrupted or skip objects leaving them with NO statistics gathered. You can force this by locking statistics - so the Auto job will skip those completely

Gather stats

DECLARE
   v_stmt          VARCHAR2 (200);
   v_cnt           NUMBER;
   v_deviation     NUMBER DEFAULT 10;
   v_est_percent   NUMBER DEFAULT 20;
BEGIN

   BEGIN
      EXECUTE IMMEDIATE 'DROP TABLE tabstat';
   EXCEPTION
      WHEN OTHERS
      THEN
         NULL;
   END;

   BEGIN
      EXECUTE IMMEDIATE 'CREATE TABLE tabstat (
   tabname     VARCHAR2 (30),
   cnt         NUMBER,
   nrows       NUMBER,
   deviation   NUMBER,
   last_analyzed DATE
)';
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line ('error');
   END;

   EXECUTE IMMEDIATE
      ' INSERT INTO tabstat (tabname,
                        cnt,
                        nrows,
                        last_analyzed)
      (SELECT ut.table_name,
              TO_NUMBER (
                 EXTRACTVALUE (
                    xmltype (
                       DBMS_XMLGEN.getxml (
                             ''select /*+ PARALLEL */ count(*) c from ''
                          || ut.table_name)),
                    ''/ROWSET/ROW/C''))
                 COUNT,
              num_rows,
              last_analyzed
         FROM user_tables ut,
              (SELECT DISTINCT
                      table_name, NVL (stattype_locked, ''N'') stattype_locked
                 FROM user_tab_statistics) uts
        WHERE     ut.table_name = uts.table_name
              AND ut.temporary = ''N''
              AND uts.stattype_locked = ''N''
              AND ut.table_name NOT IN (''TABSTAT'')
     AND ut.table_name NOT LIKE ''PROCESS%'')';


   EXECUTE IMMEDIATE
      ' UPDATE tabstat
      SET deviation = ROUND (ABS ( (cnt - nrows) / cnt) * 100, 2)
      WHERE cnt !=0';

   EXECUTE IMMEDIATE 'UPDATE tabstat
      SET deviation = 100
      WHERE cnt =0 AND nrows !=0 ';

EXECUTE IMMEDIATE 'UPDATE tabstat
      SET deviation = 0
      WHERE cnt =0 AND nrows =0 ';


   EXECUTE IMMEDIATE
      '
    BEGIN
    FOR tab_rec IN (  SELECT tabname
                       FROM tabstat
                      WHERE deviation > NVL (&1, 0)
                   ORDER BY tabname)
   LOOP
      DBMS_STATS.GATHER_TABLE_STATS (
         ownname            => USER,
         tabname            => tab_rec.tabname,
         estimate_percent   => DBMS_STATS.AUTO_SAMPLE_SIZE,
         degree             => 8,
         method_opt         => ''FOR ALL COLUMNS'',
         cascade            => TRUE,
no_invalidate    => FALSE);

    UPDATE tabstat SET last_analyzed = SYSDATE WHERE tabname=tab_rec.tabname;
   END LOOP;
   END ;';

COMMIT ;

END;
/


Query to find out table name and actual number of rows in each table:

1.
SELECT ut.table_name,
       TO_NUMBER (
          EXTRACTVALUE (
             xmltype (
                DBMS_XMLGEN.getxml (
                   'select /*+ PARALLEL */ count(*) c from ' || ut.table_name)),
             '/ROWSET/ROW/C'))
          COUNT,
       num_rows,
       last_analyzed
  FROM user_tables ut ;

2.
SELECT table_name, COLUMN_VALUE cnt
  FROM user_tables, XMLTABLE ( ('count(ora:view("' || table_name || '"))'))
 WHERE table_name IN ('EMP', 'DEPT', 'BONUS');

3.
SELECT table_name,
       DBMS_XMLGEN.getxmltype ('select count(*) c from ' || table_name).EXTRACT (
          '//text()').getnumberval ()
          tot_rows
  FROM user_tables
 WHERE iot_type IS NULL OR iot_type != 'IOT_OVERFLOW';

Current oracle patch version

Query to return current oracle patch.

select patch_id, patch_uid, version, status, description  from dba_registry_sqlpatch where bundle_series = 'PSU';

To find out latest oracle patches available:

https://www.oracle.com/technetwork/topics/security/alerts-086861.html

unusable index and truncate table

Changing index to unusable will drop the index segment but if we truncate the table, it will make index usable again.

ALTER INDEX X2F2TEST_UNUSABLE UNUSABLE

select status,VISIBILITY,SEGMENT_CREATED,index_name from user_indexes a where index_name='X2F2TEST_UNUSABLE'

will retuen
UNUSABLE VISIBLE NO X2F2TEST_UNUSABLE

select * from dba_segments where segment_name='X2F2TEST_UNUSABLE'
will return no rows. It show's that segment dropped.

So if we have index unusable, it depends if oracle maintains index during DML operations or not based on skip_unusable_indexes initialization parameter. If it is true it won't be maintained, else (don't use FALSE ever)it will throw error during DML operation that unusable index exists or during select if that select usage that index.

Truncating a table makes an unusable index valid. So if we truncate a table post changing it to unusable, it will change status of index to valid.

truncate table X2F2TEST_UNUSABLE;

select status,VISIBILITY,SEGMENT_CREATED,index_name from user_indexes a where index_name='X2F2TEST_UNUSABLE'
will retuen
VALID VISIBLE YES X2F2TEST_UNUSABLE

To make an unusable index usable, we have to rebuild the index.

ALTER INDEX scpomgr.X2F2TEST_UNUSABLE rebuild parallel 5

ALTER INDEX scpomgr.X2F2TEST_UNUSABLE  noparallel

Unusable Index:When you make an index unusable it is ignored by optimizer and not updated by dml operation. After you make index unusable.It must rebuilded before using it.

Invisible Index:When you make index invisible it is ignored by optimizer.You can use this for test purpose.you can make index  invisible when you create index or with alter index command after creating table . Hovewer if you set OPTIMIZER_USE_INVISIBLE_INDEXES parameter to TRUE invisible  index can be used by optimizer.This parameter can be set session or system level.
NOTE:When you make index invisible this will result invalidate all sql  statement in the shared memory that have execution plan using this index

Good to know things oracle

http://www.joblagao.com/voices/joblagao-university/20-jda-technical-interview-questions


1. Enable trace for others session
begin
sys.DBMS_SYSTEM.set_sql_trace_in_session(264, 37690, true );
end;

first parameter - SID, and next thread#

here in promo - one session blocks many others - so I thought to trace what that session is doing etc.

2. Below query fails and reason is outer query is enclosed in braces.

(with t as (select * from emp)
              select * from t);

3.SQLCODE and SQLERRM, why I can't insert SQLCODE and SQLERRM Values directly into table? I can insert only while taking them into separate variables?
Ans:
Reason is that SQLCODE and SQLERRM are functions that only return valid values within exception blocks of PL/SQL code. When you issue an SQL statement it is passed to the SQL engine and as such those functions would be out of scope and not provide the correct values and the SQL engine would not be able to execute such functions.It is well documented in oracle docs.

You may write a user defined function and wrap these functions in them to implement the functionality .

The SQLERRM() is a function - and is only defined in the PL engine. Kind of the opposite of the DECODE() function that is only defined in the SQL engine.

Nothing prevents you from defining a user PL/SQL function (which can be used in SQL) and use that to wrap SQLERRM(), e.g.
create or replace function oraMessage( oraError number ) return varchar2 is
begin
  return( SQLERRM(oraError) );
end;
This can be quite handy if you have a message log table (for application code) that has recorded ORA errors (via SQLCODE) - and you want to display the associated error message.

4.I Have a column where number stored as 8.756412556312453E37 I want to display it as 

87564125563124530000000000000000000000

Ans:Use to_char function.

5. Simple example for bulk collect and forall(For interview)

DECLARE
    CURSOR c_item
    IS
        SELECT item, item_descr FROM stg_item;

    TYPE item_tab_type IS TABLE OF c_item%ROWTYPE;

    item_tab   item_tab_type;
BEGIN
    OPEN c_item;

    LOOP
        FETCH c_item BULK COLLECT INTO item_tab LIMIT 200000;

        EXIT WHEN item_tab.COUNT = 0;

        FORALL i IN 1 .. item_tab.COUNT
            INSERT INTO item ( item, item_descr)
                SELECT /*+ parallel */
                      item_tab ( i).item, item_tab ( i).item_descr FROM DUAL;

        COMMIT;
    END LOOP;
END;
/

Note: although changing table ITEM to nologging then doing a simple "insert /*+ append */ into item select from stg_item" seems like a better bet.

6.Utl_File or standard spool, which one to use and when?

Ans: UTL_FILE is a server-side I/O operation on a server file system. UTL_FILE can be integrated in application code but as it will write at server side, we should be having access to read file on server.

SPOOL is a SQL*Plus client I/O operation on a client file system.

Another options to achieve same by storing result in CLOB/BFile etc. in database and export that or read by any interface.

7.I have two tables NSK_LOC_TYPE and NSK_LOC where in NSK_LOC_TYPE a location and it's type will be there and in NSK_LOC there will be other details of this location.

I need to populate NSK_LOC table based on NSK_LOC_TYPE table.

If a location LOC1 is of type 3 then I need to insert into two records into NSK_LOC as LOC1 SHIP-LOC1
if a locaiton LOC2 is of type 6 then I need to insert only one record into NSK_LOC as LOC

How can we achieve using single insert and without using union

Ans:INSERT FIRST
WHEN LOC_TYPE = 3
THEN
      INTO NSK_LOC
    VALUES (LOC, LOC)
      INTO NSK_LOC
    VALUES (LOC, 'SHIP-' || LOC)
WHEN LOC_TYPE = 6
THEN
      INTO NSK_LOC
    VALUES (LOC, LOC)
    SELECT LOC, LOC_TYPE FROM NSK_LOC_TYPE;

8.Selecting 1 records in a recursion

I have a data like this:

select 'A' c1, 'B' c2, 100 as c3 from dual

union all

select 'B' c1, 'A' c2, 100 as c3 from dual

union all

select 'D' c1, 'C' c2, 100 as c3 from dual

union all

select 'C' c1, 'D' c2, 100 as c3 from dual


We see that the data has a recursive pattern on C1 and C2.. Like A --> B, B--> A or D-->C, C--> D.  When we have data like this I only need to get 2 records, Basically the 1st and the 3rd one. So the output should be :
select 'A' c1, 'B' c2, 100 as c3 from dual
union all
select 'D' c1, 'C' c2, 100 as c3 from dual

Ans:with data as (
select 'A' c1, 'B' c2, 100 as c3 from dual
union all
select 'B' c1, 'A' c2, 100 as c3 from dual
union all
select 'D' c1, 'C' c2, 100 as c3 from dual
union all
select 'C' c1, 'D' c2, 100 as c3 from dual
)
select c1, c2, c3
from (
  select d.*, row_number() over (partition by least(c1, c2), greatest(c1, c2) order by c1) rn
  from data d
)
where rn = 1;

9. Bitmap Indexes and Deadlocks

Bitmap indexes are not appropriate for tables that have lots of single row DML operations (inserts) and especially concurrent single row DML operations. Deadlock situations are the result of concurrent inserts as the following example shows: Open two windows, one for Session 1 and one for Session 2

Session 1	Session 2
create table bitmap_index_demo (   value varchar2(20) );
insert into bitmap_index_demo select decode(mod(rownum,2),0,'M','F')   from all_objects;
create bitmap index   bitmap_index_demo_idx   on bitmap_index_demo(value);
insert into bitmap_index_demo   values ('M'); 1 row created.
	insert into bitmap_index_demo   values ('F'); 1 row created.
insert into bitmap_index_demo   values ('F'); ...... waiting ......
ERROR at line 1: ORA-00060: deadlock detected while waiting for resource	insert into bitmap_index_demo   values ('M'); ...... waiting ......

10.Stats gathering auto commits the pending transactions in the session.

Oracle 12.1.0.2: Export failed with ORA-20002: Version of statistics table "MYSTATSTAB" is too old. Please try upgrading it with dbms_stats.upgrade_stat_table

I am trying to follow these steps in 12.1.0.2 and it fails when I run the step 3 (export stats).  This works well in 11g.  

BEGIN

dbms_stats.Create_stat_table(user, 'MY_STATS_TAB');

END;

exec dbms_stats.upgrade_stat_table(user, 'MY_STATS_TAB')

BEGIN

   DBMS_STATS.Export_table_stats (USER,

                                  stattab   => 'MY_STATS_TAB',

                                  tabname   => 'MD_TABLE_INFO',

                                  statid    => '11g_stats');

END;

ORA-20002: Version of statistics table "wwfuser"."MY_STATS_TAB" is too old.  Please try upgrading it with dbms_stats.upgrade_stat_table

ORA-06512: at "SYS.DBMS_STATS", line 18000

ORA-06512: at line 2

On further research we found that this issue occurs when database NLS_LENGTH_SEMANTICS are set to CHAR. By default we have CHAR settings in place of BYTE.

This is oracle bug 18459892 that fixed in 12.2.

After changing NLS_LENGTH_SEMANTICS it worked fine.

exec dbms_stats.drop_stat_table(ownname => 'WWFUSER', stattab => 'MY_STATS_TAB');

ALTER SESSION SET NLS_LENGTH_SEMANTICS = BYTE

exec dbms_stats.create_stat_table(ownname => 'WWFUSER', stattab => 'MY_STATS_TAB');

EXEC dbms_stats.export_table_stats(ownname => 'WWFUSER',tabname => 'MD_TABLE_INFO',stattab => 'MY_STATS_TAB',statid => 'Q21212');

Another alternative is to create stats table in SYS schema or with BYTE character set.

by SYS user:

EXEC dbms_stats.create_stat_table(ownname => 'SYS',stattab => 'STATTAB',tblspace => 'WWFDATA'); 

grant all on STATTAB TO wwfmgr;

then by wwf user:

EXEC dbms_stats.export_table_stats(ownname => 'WWFUSER',tabname => 'MD_TABLE_INFO',stattab => 'STATTAB',statid => 'Q21212',statown => 'SYS');

 Hope it helps.