Pages

Saturday, May 16, 2020

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)

Friday, February 21, 2020

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.

Monday, February 17, 2020

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.

Sunday, June 7, 2015

New table name in DDL using DBMS_METADATA

If you want to get a DDL with the desired name for a table this is function will be used.

Step 1:


Replace YOUR SCHEMA name in line # 13 in below block i.e., from 'A' to 'SCHEMA name'

  1  create or replace function remap_name(orig_name varchar2, new_name varchar2 )
  2  return clob is
  3    -- Define local variables.
  4    h   number; --handle returned by OPEN
  5    th  number; -- handle returned by ADD_TRANSFORM
  6    doc clob;
  7  begin
  8    -- Specify the object type.
  9    h := dbms_metadata.open('TABLE');
 10    -- Use filters to specify the particular object desired.
 11    dbms_metadata.set_filter(h
 12                            ,'SCHEMA'
 13                            ,'A');
 14    dbms_metadata.set_filter(h
 15                            ,'NAME'
 16                            ,orig_name);
 17    -- Request that the schema name be modified.
 18    th := dbms_metadata.add_transform(h
 19                                     ,'MODIFY');
 20    dbms_metadata.set_remap_param(th
 21                                 ,'REMAP_NAME'
 22                                 ,orig_name
 23                                 ,new_name);
 24    -- Request that the metadata be transformed into creation DDL.
 25    th := dbms_metadata.add_transform(h
 26                                     ,'DDL');
 27    -- Specify that segment attributes are not to be returned.
 28    dbms_metadata.set_transform_param(th
 29                                     ,'SEGMENT_ATTRIBUTES'
 30                                     ,false);
 31    dbms_metadata.set_transform_param(th,'SQLTERMINATOR',TRUE);
 32    -- Fetch the object.
 33    doc := dbms_metadata.fetch_clob(h);
 34    -- Release resources.
 35    dbms_metadata.close(h);
 36    return doc;
 37  end remap_name;
 38  /

PLEASE REMOVE LINE NUMBERS BEFORE EXECUTION

Step 2. Call the remape_name and spool it


set echo off head off feed off
spool renameDDL.sql
SELECT 'SELECT remap_name('''||  TABLE_NAME || ''','''||  TABLE_NAME || '_new'') FROM DUAL;' FROM DBA_TABLES where owner='A';
spool off

Step 3. Review the above script and execute accordingly


Example:

sys@TESTDB> SELECT remap_name('TEST_OBJ','TEST_OBJ_new') FROM DUAL;

REMAP_NAME('TEST_OBJ','TEST_OBJ_NEW')
-------------------------------------------------

  CREATE TABLE "A"."TEST_OBJ_new"
   (    "OWNER" VARCHAR2(30),
        "OBJECT_NAME" VARCHAR2(128),

Note: 

REMAP_NAME is not available in 10g , available from 11g

NO REMAP_NAME ins 10g
http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_metada.htm#BGBBIEGA

REMAP_NAME is available in SET_REMAP_PARAM 11g only
http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_metada.htm#BGBBIEGA 

You will get the below error in 10g but function will be created 
ORA-31600: invalid input value REMAP_NAME for parameter NAME in function SET_REMAP_PARAM