SQL investigation for my reference

SELECT plan_hash_value,
       child_number,
       INVALIDATIONS,
       is_obsolete,
       IS_BIND_SENSITIVE,
       FIRST_LOAD_TIME,
       LAST_LOAD_TIME,
       LAST_ACTIVE_TIME,
       LOADS
  FROM v$sql
 WHERE sql_id = '530ubrcgf9tbw'


--One row per child for SQL_ID
SELECT *
  FROM V$SQL
 WHERE sql_id = '530ubrcgf9tbw'

-- Each child execution plan for the SQL
SELECT *
  FROM V$SQL_PLAN
 WHERE sql_id = '530ubrcgf9tbw'

-- Know Reason for Child cursors
select * from V$SQL_SHARED_CURSOR where sql_id='530ubrcgf9tbw';

--single line per SQL ID execution and other stats
-- Historical DBA_HIST_SQLSTAT
SELECT *
  FROM V$SQLSTATS
 WHERE sql_id = '530ubrcgf9tbw'

-- One row for SQL ID and plan_hash, if two plans for the same SQLID two lines with each plan
select * from V$SQLSTATS_PLAN_HASH where sql_id='530ubrcgf9tbw';

 --Bind values for the SQL
select * from V$SQL_BIND_CAPTURE   where sql_id='530ubrcgf9tbw';

-- distinct plan_hash_value for a SQL_ID

SELECT sql_id,

         parsing_schema_name parsing_schema,

         COUNT (DISTINCT (plan_hash_value)) "no_of_plans"

    FROM v$sql

   WHERE parsing_schema_name = 'SCOTT'

GROUP BY sql_id, parsing_schema_name

ORDER BY 3 DESC;

https://blogs.oracle.com/optimizer/adaptive-cursor-sharing-acs-and-bind-sensitivity
https://www.oracle.com/technical-resources/articles/database/sql-11g-sqlplanmanagement.html
https://tanelpoder.com/oracle-sql-plan-stability/

Column PLAN_HASH_VALUE IN V$SQLSTATS Continuously Changes (Doc ID 1345516.1)
Rolling Cursor Invalidations with DBMS_STATS.AUTO_INVALIDATE (Doc ID 557661.1)

Estimate Index size before creating

      Sometimes it is better to estimate the index before creating so to not see any surprise errors. From the below Oracle note got the exact code to get estimated size of index.

How to Estimate the Size of Tables and Indexes Before Being Created and Populated in the Database? (Doc ID 1585326.1)

I have queried this in 11204 database, please do comment if you test in other versions.

SQL> set serveroutput on 
SQL> declare
  2  l_used_bytes number;
  3  l_alloc_bytes number;
  4  begin
  5  dbms_space.create_index_cost (
  6  ddl => 'create index orp.ACT_IDX on orp.act (LOAN) global partition by hash (LOAN) partitions 32',
  7  used_bytes => l_used_bytes,
  8  alloc_bytes => l_alloc_bytes
  9  );
 10  dbms_output.put_line ('Used Bytes          = '||l_used_bytes);
 11  dbms_output.put_line ('Allocated Bytes     = '||l_alloc_bytes);
 12  end;
 13  /
Used Bytes              = 3464634958
Allocated Bytes         = 42949672960

PL/SQL procedure successfully completed.

 The used_bytes : how much space is for the index data

 The alloc_bytes : how much space is allocated within the tablespace for the index segment. (ACTUAL SIZE)

Another Way

There is another way using explain plan, please don't confuse with the numbers both are different indexes just trimmed names for easy

 SQL> explain plan for 

   2  create index orp.ACT_IDX on orp.act (PRO_TIME);

Explained.

01:42:48 SQL> select * from table (dbms_xplan.display());

PLAN_TABLE_OUTPUT

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

Plan hash value: 3821618068

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

| Id  | Operation              | Name                         | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | CREATE INDEX STATEMENT |                              | 46441 |  1224K|   113   (1)| 00:00:02 |

|   1 |  INDEX BUILD NON UNIQUE| ACT_IDX                      |       |       |            |          |

|   2 |   SORT CREATE INDEX    |                              | 46441 |  1224K|            |          |

|   3 |    INDEX FAST FULL SCAN| ACT_IDX                      | 46441 |  1224K|    71   (2)| 00:00:01 |

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

Note

-----

   - estimated index size: 2097K bytes

14 rows selected.

enq: HW - contention and Secure file LOB

In one of our 11204 database, we encountered the deadly ‘Enq: HW contention’ on critical business day. The majority of the DB activity is insert and the table has CLOBs. The CLOBs were traditional BASIC LOB, With the below note able to identify the LOB segment on which the contention is.

Analyzing 'enq: HW - contention' Wait Event (Doc ID 740075.1)

Manual extent allocation or event 44951 didn’t help much, so finally decided to migrate the CLOBs to SECURE FILES. Due to the segment size is in 7+ Terabyte considering the time /storage we decided to create new table and let the logs write there. This may not be feasible to many of your environment. This particular table is monthly RANGE partitioned to truncate the older partition every 2 months. Now it is created with hash sub partition to distribute the inserts and reduce concurrency.

I could not locate appropriate DDL for the RANGE-HASH composite partition, so the below test case developed. Currently the new table is with secure file LOBs and hash sub partitioned. The performance is great with the same amount of load.

Note from 12c the default LOB is Secure file LOB.

TEST CASE

CREATE TABLE hash_test

  ( prod_id       NUMBER(6)

  , cust_id       NUMBER

  , time_id       DATE

    )

PARTITION BY RANGE (time_id) INTERVAL (NUMTOYMINTERVAL(1,'MONTH')) SUBPARTITION BY HASH (cust_id)

  SUBPARTITIONS 4 STORE IN (USERS)

(PARTITION "P0"  VALUES LESS THAN (TO_DATE(' 2018-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')));

set long 99999 lines 200 pages 9999

col DDL for a200

select dbms_metadata.get_ddl('TABLE','HASH_TEST','SYS') DDL from dual;

insert into hash_test values(10023,101,sysdate);

 insert into hash_test values(10024,104,sysdate);

 insert into hash_test values(10025,107,sysdate);

 insert into hash_test values(10026,110,sysdate);

 insert into hash_test values(10027,113,sysdate);

 insert into hash_test values(10028,116,sysdate);

 insert into hash_test values(10029,119,sysdate+30);

 insert into hash_test values(10030,122,sysdate+30);

 insert into hash_test values(10031,125,sysdate+30);

 insert into hash_test values(10032,128,sysdate+30);

 insert into hash_test values(10033,131,sysdate+30);

 insert into hash_test values(10034,134,sysdate+30);

 insert into hash_test values(10035,137,sysdate+30);

 insert into hash_test values(10036,140,sysdate+60);

 insert into hash_test values(10037,143,sysdate+60);

 insert into hash_test values(10038,146,sysdate+60);

 insert into hash_test values(10039,149,sysdate+60);

 insert into hash_test values(10040,152,sysdate+60);

 insert into hash_test values(10041,155,sysdate+60);

 insert into hash_test values(10042,158,sysdate+60);

 insert into hash_test values(10043,161,sysdate+60);

 SQL> select count(*) from hash_test subpartition        (SYS_SUBP19335);

  COUNT(*)

----------

         1

SQL> select count(*) from hash_test subpartition        (SYS_SUBP19336);

  COUNT(*)

----------

         1

SQL> select count(*) from hash_test subpartition        (SYS_SUBP19337);

  COUNT(*)

----------

         5

SQL> select count(*) from hash_test subpartition        (SYS_SUBP19338);

  COUNT(*)

----------

         0

SQL> select count(*) from hash_test partition (SYS_P19339);

  COUNT(*)

----------

         7

exec DBMS_STATS.GATHER_TABLE_STATS (ownname => 'SYS' ,tabname => 'HASH_TEST',cascade => true, estimate_percent => dbms_stats.auto_sample_size,method_opt=>'for all indexed columns size AUTO', granularity => 'ALL', degree => 8);

                       

alter table hash_test truncate partition SYS_P19339;

CREATE INDEX prod_id_ix ON hash_test(prod_id) LOCAL;

Subpartition    Partition           Tablespace     Last Analyzed High Value      High Value Length      Num Rows   B-Level            Distinct Keys

                        Name  Name

SYS_SUBP19326      P0                                USERS           09-Feb-20 10:57:26 AM                      0          0            0          0

SYS_SUBP19327      P0                                USERS           09-Feb-20 10:57:26 AM                      0          0            0          0

SYS_SUBP19328      P0                                USERS           09-Feb-20 10:57:26 AM                      0          0            0          0

SYS_SUBP19329      P0                                USERS           09-Feb-20 10:57:26 AM                      0          0            0          0

SYS_SUBP19330      SYS_P19390  SYSTEM         09-Feb-20 10:57:26 AM                      0          0          0            0

SYS_SUBP19331      SYS_P19390  SYSTEM         09-Feb-20 10:57:26 AM                      0          0          0            0

SYS_SUBP19332      SYS_P19390  SYSTEM         09-Feb-20 10:57:26 AM                      0          0          0            0

SYS_SUBP19333      SYS_P19390  SYSTEM         09-Feb-20 10:57:26 AM                      0          0          0            0

SYS_SUBP19335      SYS_P19391  SYSTEM         09-Feb-20 10:57:26 AM                      0          0          0            0

SYS_SUBP19336      SYS_P19391  SYSTEM         09-Feb-20 10:57:26 AM                      0          0          0            0

SYS_SUBP19337      SYS_P19391  SYSTEM         09-Feb-20 10:57:26 AM                      0          0          0            0

SYS_SUBP19338      SYS_P19391  SYSTEM         09-Feb-20 10:57:26 AM                      0          0          0            0

SYS_SUBP19340      SYS_P19392  SYSTEM         09-Feb-20 10:57:26 AM                      0          0          0            0

SYS_SUBP19341      SYS_P19392  SYSTEM         09-Feb-20 10:57:26 AM                      0          0          0            0

SYS_SUBP19342      SYS_P19392  SYSTEM         09-Feb-20 10:57:26 AM                      0          0          0            0

SYS_SUBP19343      SYS_P19392  SYSTEM         09-Feb-20 10:57:26 AM                      0          0          0            0

 DATA Generator.

DECLARE

  t_seed NUMBER(20);

BEGIN

  -- Initialize Random Number Generator

  SELECT TO_NUMBER(TO_CHAR(SYSDATE, 'MMDDHH24MISS'))

    INTO t_seed

  FROM Dual;

  DBMS_RANDOM.INITIALIZE(t_seed);

  -- Generate Random Data

  FOR t_counter IN 1..1000 LOOP

    INSERT INTO aolmob.HASH_TEST (

      PROD_ID, CUST_ID, TIME_ID

    ) VALUES (

     DBMS_RANDOM.VALUE(0.00, 999999.00), DBMS_RANDOM.VALUE(0.00, 2147483647.00), TO_DATE(TRUNC(DBMS_RANDOM.VALUE(2415020, 2488068)), 'J')

    );

  END LOOP;

  DBMS_RANDOM.TERMINATE;

END;

/

Please leave your valuable comments to improve the blog and let me know if any specific topic to be explained.