Oracle Advenced Storage Management Examples

----------------------------------------------------------------------------------------
-- DEFERRED_SEGMENT_CREATION (11gR2)
----------------------------------------------------------------------------------------
-- * Oracle 11gR2 creates new tables without any storage allocated to them.
--    (Only registers them in the Data dictionary)
-- * The actual segment will be created only on the first insert.
-- * Advantages:
--   - Saves disk space for applications that create many tables upon installation.
--       (many of which might never be populated)
--   - The installation time is reduced significally. 
-- * We can control this behavior in two ways:
--   1. The DEFERRED_SEGMENT_CREATION parameter
--      - Default is TRUE.
--      - Can change at system or session level.
--   2. The SEGMENT CREATION clause of the CREATE TABLE.
--      - SEGMENT CREATION DEFERRED | IMMEDIATE
--
-- * Supported only for regular (Heap) tables.
-- * Indexes inherit the table's definition.

conn hr/hr@pdborcl

DROP TABLE seg_test ;

CREATE TABLE seg_test(c number, d varchar2(500));

SELECT segment_name FROM user_segments
WHERE segment_name = 'SEG_TEST'

INSERT INTO seg_test VALUES(1, 'aaaaaaa');

SELECT segment_name FROM user_segments
WHERE segment_name = 'SEG_TEST';

DROP TABLE seg_test PURGE;

CREATE TABLE seg_test(c number, d varchar2(500))
SEGMENT CREATION IMMEDIATE;

SELECT segment_name FROM user_segments
WHERE segment_name = 'SEG_TEST';

conn sys/password@pdborcl as sysdba 
SHOW PARAMETER deferred_segment_creation 

----------------------------------------------------------------------------------------
-- Table Compression
----------------------------------------------------------------------------------------

-- * Advantages 
--   1. Reduce space 
--   2. Reduce I/O

-- * Table compression:
--    - Specified via the COMPRESS / NOCOMPRESS clause. (Default is NoCompress)
--    - Eliminates duplicate values in a data block. 
--        (Effective for tables with highly redundant data)
--    - Transparent to the application. (No need to de-compress)
--    - Supported for regular (Heap) and Partitioned tables only.

-- * Oracle recommends to compress all data to reduce storage, IO and memory.

-- *  Oracle Database supports two main methods of table compression:
--     1. Basic table compression
--        - Compresses data only during loads. (Direct loads, CTAS etc)
--        - specify using COMPRESS [BASIC]. (Until 11gR2 - COMPRESS FOR DIRECT_LOAD OPERATIONS)
--
--     2. OLTP table compression
--        - Compresses data during all DML operations on the table. 
--        - specify using COMPRESS FOR OLTP. (Until 11gR2 - COMPRESS FOR ALL OPERATIONS)
--

-- * As compression occurs only when the block reaches PCTFREE, overhead is minimal:
--       Empty Block => Inserts (Not compressed) => PCTFREE is reached => 
--         Compression => Inserts....

-- * Architecture:
--    - Duplicate values in the block are stored once in a "Symbol Table" 
--        at the beggining of the block.
--    - All their occurances in the block are then replaced 
--        by a short reference to the symbol table.
--    - Therefore, each block un-compression is self contained.

-- Without compression:

DROP TABLE comp_tab PURGE;

CREATE TABLE comp_tab 
  AS SELECT * FROM hr.employees WHERE rownum <=5 ;

BEGIN
   FOR i IN 1..13 LOOP
      INSERT INTO comp_tab
      SELECT * FROM comp_tab;
   END LOOP;
END;
/

SELECT COUNT(*) FROM comp_tab;

SELECT bytes/1024 AS size_kb FROM dba_Segments
WHERE segment_name = 'COMP_TAB';

-- Add compression to the table:
ALTER TABLE comp_tab COMPRESS FOR ALL OPERATIONS ;

-- Insert the data again or simply rebuild the table:
ALTER TABLE comp_tab MOVE;

SELECT bytes/1024 AS size_kb FROM dba_Segments
WHERE segment_name = 'COMP_TAB';

DROP TABLE comp_tab ; 

CREATE TABLE comp_tab (id number) COMPRESS FOR ALL OPERATIONS ;

DROP TABLE comp_tab ; 
------------------------------------------------
-- Compression Advisor (11gR2)
------------------------------------------------
-- * Estimates space savings for objects. 
-- * Recommends on:
--   - Compression strategy
--   - Compression algorithm.
--   - Sorts on a particular column. (Increased compression ratio)
--   - Presents tradeoffs between different compression algorithms.
--   - Can be activated using:
--     1. OEM - Graphical interface. (For OLTP compression)
--     2. DBMS_COMPRESSION

-- * DBMS_COMPRESSION:
--   - GET_COMPRESSION_RATIO:
--     - Calculates the possible compression ratio for a table.
--   - GET_COMPRESSION_TYPE :
--     - Returns the compression type for a given row.

DROP TABLE tab1 PURGE;

CREATE TABLE tab1 (
  id                NUMBER,
  code              VARCHAR2(20),
  description       VARCHAR2(50),
  clob_description  CLOB,
  created_date      DATE,
  CONSTRAINT tab1_pk PRIMARY KEY (id)
)
PARTITION BY RANGE (created_Date)
(PARTITION tab1_part_2015 VALUES LESS THAN (TO_DATE('01/01/2016', 'DD/MM/YYYY')) TABLESPACE users,
 PARTITION tab1_part_2016 VALUES LESS THAN (TO_DATE('01/01/2017', 'DD/MM/YYYY')) TABLESPACE users);

CREATE INDEX tab1_code_idx ON tab1(code) LOCAL;

INSERT INTO tab1
SELECT level,
       CASE
         WHEN MOD(level,2)=0 THEN 'CODE1'
         ELSE 'CODE2'
       END,
       CASE
         WHEN MOD(level,2)=0 THEN 'Description for CODE1'
         ELSE 'Description for CODE2'
       END,
       CASE
         WHEN MOD(level,2)=0 THEN 'CLOB description for CODE1'
         ELSE 'CLOB description for CODE2'
       END,
       CASE
         WHEN MOD(level,2)=0 THEN SYSDATE
         ELSE ADD_MONTHS(SYSDATE, 12)
       END
FROM   dual
CONNECT BY level <= 100000;
COMMIT;

EXEC DBMS_STATS.gather_table_stats(USER, 'TAB1');


SET SERVEROUTPUT ON
DECLARE
  l_blkcnt_cmp    PLS_INTEGER;
  l_blkcnt_uncmp  PLS_INTEGER;
  l_row_cmp       PLS_INTEGER;
  l_row_uncmp     PLS_INTEGER;
  l_cmp_ratio     NUMBER;
  l_comptype_str  VARCHAR2(32767);
BEGIN
  DBMS_COMPRESSION.get_compression_ratio (
    scratchtbsname  => 'USERS',
    ownname         => 'SYS',
    objname         => 'TAB1',
    subobjname      => NULL,
    comptype        => DBMS_COMPRESSION.comp_advanced,
    blkcnt_cmp      => l_blkcnt_cmp,
    blkcnt_uncmp    => l_blkcnt_uncmp,
    row_cmp         => l_row_cmp,
    row_uncmp       => l_row_uncmp,
    cmp_ratio       => l_cmp_ratio,
    comptype_str    => l_comptype_str,
    subset_numrows  => DBMS_COMPRESSION.comp_ratio_allrows,
    objtype         => DBMS_COMPRESSION.objtype_table
  );

  DBMS_OUTPUT.put_line('Number of blocks used (compressed)       : ' ||  l_blkcnt_cmp);
  DBMS_OUTPUT.put_line('Number of blocks used (uncompressed)     : ' ||  l_blkcnt_uncmp);
  DBMS_OUTPUT.put_line('Number of rows in a block (compressed)   : ' ||  l_row_cmp);
  DBMS_OUTPUT.put_line('Number of rows in a block (uncompressed) : ' ||  l_row_uncmp);
  DBMS_OUTPUT.put_line('Compression ratio                        : ' ||  l_cmp_ratio);
  DBMS_OUTPUT.put_line('Compression type                         : ' ||  l_comptype_str);
END;
/

------------------------------------------------
-- Proactive Tablespace Monitoring 
------------------------------------------------

-- * Tablespace disk space usage is proactively managed using:
--   - Alerts:
--     - Set Warning/Error usage thresholds, and receive notifications.
--        (Tablespace: Edit => Storage => Thresholds)
--     - Tracked every 10 minutes.
--   - Growth analysis:
--     - Oracle uses AWR statistics for growth trend analysis and capacity planning.
--     - Via Segment Advisor

-- * Resolve space problem by:
--   - Adding or resizing data file
--   - Setting AUTOEXTEND ON
--   - Shrinking objects
--   - UNDO - Reducing UNDO_RETENTION.
--   - TEMP - Checking for long-running queries.


-- Setting an alert threshold for a tablespace:
-----------------------------------------------

DROP TABLESPACE mytbs2 INCLUDING CONTENTS AND DATAFILES;

-- Create a 20MB tablespace with a table in it:
CREATE TABLESPACE mytbs2 DATAFILE 'C:\mytbs2.dbf' SIZE 20m REUSE;

CREATE TABLE bigtab TABLESPACE mytbs2 AS SELECT * FROM hr.employees;

EXECUTE DBMS_SERVER_ALERT.SET_THRESHOLD( -
   DBMS_SERVER_ALERT.TABLESPACE_PCT_FULL, /* Metric */   -
   DBMS_SERVER_ALERT.OPERATOR_GE, '60',   /* Warning */  -
   DBMS_SERVER_ALERT.OPERATOR_GE, '80',   /* Critical */ -
   1,1,'orcl', -
   DBMS_SERVER_ALERT.OBJECT_TYPE_TABLESPACE,'MYTBS2') /* Object type and name */ ;  

INSERT INTO bigtab SELECT * FROM bigtab ; 

SELECT bytes/1024/1024 size_mb
FROM user_segments
WHERE segment_name = 'BIGTAB';

-- Check again for outstanding alerts:
SELECT REASON, SUGGESTED_ACTION
FROM dba_outstanding_alerts;

------------------------------------------------
-- Shrinking Segments 
------------------------------------------------

-- High Water Mark 
------------------


DROP TABLE hr.mytab PURGE; 

CREATE TABLE hr.mytab AS SELECT * FROM hr.employees ;

-- Analyze
ANALYZE TABLE hr.mytab COMPUTE STATISTICS;

SELECT NUM_ROWS, CHAIN_CNT, AVG_ROW_LEN, BLOCKS, EMPTY_BLOCKS
FROM dba_tables
WHERE TABLE_NAME = 'MYTAB';

-- BLOCKS - Below HWM 
-- EMPTY_BLOCKS - Above HWM 

-- HWM is mainly needed for two reasons 
-- 1. RMAN Backups 
-- 2. Full Table Scans 

-- Insert some rows to the table 
INSERT INTO hr.mytab SELECT * FROM hr.mytab ;
COMMIT ; 
-- Analyze
ANALYZE TABLE hr.mytab COMPUTE STATISTICS;

-- HWM Changed
SELECT NUM_ROWS, CHAIN_CNT, AVG_ROW_LEN, BLOCKS, EMPTY_BLOCKS
FROM dba_tables
WHERE TABLE_NAME = 'MYTAB';

-- Delete some rows from the table 
DELETE FROM hr.mytab WHERE rownum <= 200 ;  
COMMIT ; 

-- Analyze
ANALYZE TABLE hr.mytab COMPUTE STATISTICS;

-- HWM remains the same 
SELECT NUM_ROWS, CHAIN_CNT, AVG_ROW_LEN, BLOCKS, EMPTY_BLOCKS
FROM dba_tables
WHERE TABLE_NAME = 'MYTAB';

-- 1. ALTER TABLE .. MOVE [TABLESPACE ...]; (9i)
--     * Rebuilds the table efficiently.
--     * Will lower the HWM.
--     * Solves Row Migration.
--     * Needs a full (exclusive) table lock
--     * Indexed will be left unusable and must be rebuilt. (Because ROWIDs are changed)


-- DEMO 1 
---------
ALTER TABLE hr.mytab MOVE ; 

-- Analyze
ANALYZE TABLE hr.mytab COMPUTE STATISTICS;

-- HWM Changed
SELECT NUM_ROWS, CHAIN_CNT, AVG_ROW_LEN, BLOCKS, EMPTY_BLOCKS
FROM dba_tables
WHERE TABLE_NAME = 'MYTAB';


-- 2. ALTER TABLE .. SHRINK SPACE ;  (10g)
--     * eliminates unused space below the HWM.
--     * Will lower the HWM.
--     * Does not solve Row Migration.
--     * Can be done online (Short Row-level locks and a short table-level lock at the end)
--     * Indexes are not invalidated. (Updated whenever a row moves)
--     * The table must have "Enable Row Movement". (ALTER TABLE ..)
--     * Tablespace must be Local ASSM.

-- * Shrink advantages:
--   - Full table scans (fewer and denser blocks)
--   - Faster and smaller backups. (As RMAN skips blocks above the HWM)
--   - For Indexes - Better index access 
--   (More compact tree => Less levels, Shorted range scans => Fewer I/Os)

-- * Shrink space Action:
--   1. Compaction - Rows are moved to the beggining of the segment.
--                 - Uses DELETE/INSERT internally, which do not fire triggers.
--   2. Shrink     - The high-water mark (HWM) is adjusted and the unused space is released.

-- * COMPACT performs the first phase only:
--   - If locks prevents from changing the HWM.
--   - if we have long-running queries that might attempt to read released and reclaimed 
--     blocks.
-- * CASCADE - Shrinks also dependant objects.
-- * Can perform on Tables (Heap), IOT, Partitions, Indexes, LOBs & Mviews.

DROP TABLE hr.mytab PURGE; 

CREATE TABLE hr.mytab AS SELECT * FROM hr.employees ;

-- Analyze
ANALYZE TABLE hr.mytab COMPUTE STATISTICS;

SELECT NUM_ROWS, CHAIN_CNT, AVG_ROW_LEN, BLOCKS, EMPTY_BLOCKS
FROM dba_tables
WHERE TABLE_NAME = 'MYTAB';

-- Insert some rows to the table 
INSERT INTO hr.mytab SELECT * FROM hr.mytab ;
COMMIT ; 
-- Analyze
ANALYZE TABLE hr.mytab COMPUTE STATISTICS;

-- HWM Changed
SELECT NUM_ROWS, CHAIN_CNT, AVG_ROW_LEN, BLOCKS, EMPTY_BLOCKS
FROM dba_tables
WHERE TABLE_NAME = 'MYTAB';

-- Delete some rows from the table 
DELETE FROM hr.mytab WHERE rownum <= 200 ;  
COMMIT ; 

-- Analyze
ANALYZE TABLE hr.mytab COMPUTE STATISTICS;

-- HWM remains the same 
SELECT NUM_ROWS, CHAIN_CNT, AVG_ROW_LEN, BLOCKS, EMPTY_BLOCKS
FROM dba_tables
WHERE TABLE_NAME = 'MYTAB';

-- Perform shrink:
ALTER TABLE hr.mytab SHRINK SPACE;

-- Must first enable row movement:
alter table "HR"."MYTAB" enable row movement;

-- Perform shrink:
ALTER TABLE hr.mytab SHRINK SPACE;

-- Analyze
 ANALYZE TABLE hr.mytab COMPUTE STATISTICS;

-- HWM changed
SELECT NUM_ROWS, CHAIN_CNT, AVG_ROW_LEN, BLOCKS, EMPTY_BLOCKS
FROM dba_tables
WHERE TABLE_NAME = 'MYTAB';

------------------------------------------------
-- Segment Advisor
------------------------------------------------
-- * Identifies segments that have space available for reclamation
-- * Can be invoked at the segment or tablespace level.
-- * Executed automatically during the daily maintenance windows, or manually.
-- * Results can be viewed and implelemted immediatly.
-- * Results are stored in the AWR for future reference.

--------------------------------------------------------------------------
-- Segment Advisor
--------------------------------------------------------------------------

conn sys/password@pdborcl as sysdba 

DROP TABLE hr.test;

CREATE TABLE hr.test AS SELECT * FROM all_objects;

INSERT INTO hr.test SELECT * FROM hr.test;
INSERT INTO hr.test SELECT * FROM hr.test;
INSERT INTO hr.test SELECT * FROM hr.test;

commit;
-- (At least 200000 rows)

SELECT bytes/1024/1024 size_mb 
FROM dba_segments
WHERE segment_name = 'TEST' AND OWNER = 'HR';

SELECT COUNT(*) FROM hr.test ;

--------------------------------------------------------------------------
-- Create a Segment Advisor task at a tables level (HR.TEST)
--------------------------------------------------------------------------

CREATE OR REPLACE PROCEDURE seg_adv_table
(p_table  VARCHAR2,
 p_schema VARCHAR2 := user)
AS
  l_object_id  NUMBER;

  v_sql CLOB :=
      'SELECT f.task_name,
       f.impact,
       o.type AS object_type,
       o.attr1 AS schema,
       o.attr2 AS object_name,
       f.message,
       f.more_info
       FROM   all_advisor_findings f
              JOIN all_advisor_objects o ON f.object_id = o.object_id AND f.task_name = o.task_name
       WHERE  f.task_name = ''SEG_ADV''';
BEGIN

   BEGIN
      DBMS_SQLTUNE.DROP_TUNING_TASK(
         task_name => 'SEG_ADV'
         );
   EXCEPTION
      WHEN OTHERS THEN -- ORA-13605
         NULL;
   END;

  DBMS_ADVISOR.create_task (
    advisor_name      => 'Segment Advisor',
    task_name         => 'SEG_ADV',
    task_desc         => 'Segment Advisor on a table');

  DBMS_ADVISOR.create_object (
    task_name   => 'SEG_ADV',
    object_type => 'TABLE',
    attr1       => p_schema, 
    attr2       => p_table, 
    attr3       => NULL, 
    attr4       => 'null',
    attr5       => NULL,
    object_id   => l_object_id);

  DBMS_ADVISOR.set_task_parameter (
    task_name => 'SEG_ADV',
    parameter => 'RECOMMEND_ALL',
    value     => 'TRUE');

  DBMS_ADVISOR.execute_task(task_name => 'SEG_ADV');

--  p(v_sql);

END seg_adv_table;
/ 

exec seg_adv_table('TEST','HR')

-- Or instead of invoking P, select the findings manually:
col Message for a60
col more_info for a60
SELECT f.message,
       f.more_info
       FROM   dba_advisor_findings f
       WHERE  f.task_name = 'SEG_ADV';

-- Delete some rows:
DELETE FROM hr.test
WHERE rownum < 130000;
commit;

-- And run the Segment Advisor again...


exec seg_adv_table('TEST','HR')

col Message for a60
col mode_info for a60
SELECT f.message,
       f.more_info
       FROM   dba_advisor_findings f
       WHERE  f.task_name = 'SEG_ADV';

----------------------------------------------------------------------------
-- * Resumable Space Allocation
----------------------------------------------------------------------------

-- * Resumable space allocation whole purpose is to offer an alternative to termination of a large 
--   database operation requiring more disk space, instead of terminating, While the operation is 
--   suspended, you can allocate more disk space on the destination.

-- * Resumable Space Allocation
-------------------------------
-- * The following conditions can trigger resumable space allocation:
--   * Out of disk space in a permanent or temporary tablespace
--   * Maximum extents reached on a tablespace
--   * User space quota exceeded

-- * Resumable Commands
-----------------------
-- * Resumable SELECT Statements - will be resumable only when they run out of space 
--   in a temporary tablespace.
-- * Resumable DML Commands - such as INSERT, UPDATE, and DELETE may 
--   cause an out of space condition. 
-- * Resumable SQL*Loader Operations - SQL*Loader import operations may
--   cause an out of space condition. 
-- * Resumable DDL Commands - All DDL commands that allocate disk space for
--   new or existing segments are resumable:

-- * Configuring Resumable Space Allocation
-------------------------------------------

conn sys/password@pdborcl as sysdba 
SHOW PARAMETER RESUMABLE_TIMEOUT

-- * dynamic parameter.
-- * Value of 0 (default) means Resumable Space Allocation is disabled. 
-- * A non-zero value (in seconds) for RESUMABLE_TIMEOUT means Resumable Space 
--   Allocation is enabled.
-- * If it is enabled at the system level, then all sessions can take advantage of
--   Resumable Space Allocation.

conn sys/password@pdborcl as sysdba 
alter system set resumable_timeout = 7200;

-- * Fine grained Resumable Space Allocation
--------------------------------------------
-- * Better control of which users can enable Resumable Space Allocation, is to grant 
--   the RESUMABLE system privilege.
-- * Now, HR user can enable it at will using the ALTER SESSION command.

grant resumable to hr ; 

conn hr/hr@pdborcl

-- default resumable timeout value is 7200 seconds
alter session enable resumable;

-- It can be override by issuing the folowing command 
alter session enable resumable timeout 10000;

-- To make it easy for the DBA to identify your resumable statement in the data dictionary
-- you can enable Resumable Space Allocation with the NAME parameter:
alter session enable resumable name 'HR - Creating Big Table';

conn sys/password@pdborcl as sysdba 

select user_id, session_id, status, name, sql_text
from dba_resumable;

-- Disable Resumable Space Allocation
alter session disable resumable;

-- DEMO
-------

conn sys/password@pdborcl as sysdba 

grant resumable to hr;

create tablespace hr_tbs datafile 'C:\hr_df.dbf' size 5m autoextend off;

conn hr/hr@pdborcl

CREATE TABLE hr_test (a VARCHAR2(3000)) TABLESPACE hr_tbs;

-- Fill up the table with data:
BEGIN
    FOR i IN 1..5000 LOOP
        INSERT INTO hr_test VALUES (LPAD('*',3000,'*'));
    END LOOP;
END;
/

-- Enable Resumable Space Allocation in the HR session for 3600 seconds (60 minutes):
alter session enable resumable timeout 3600;
-- Set resumable name
alter session enable resumable name 'HR - Creating Big Table';

-- Try again 
BEGIN
    FOR i IN 1..5000 LOOP
        INSERT INTO hr_test VALUES (LPAD('*',3000,'*'));
    END LOOP;
END;
/


-- The alert log will reflect the statement suspension.

-- DBA_RESUMABLE will provide additional details about the suspended statement.
COL NAME FOR A20
COL ERROR_MSG FOR A30
SET LINES 200
select user_id, instance_id, status, name, error_msg
from dba_resumable

-- As DBA resize the DF 

conn sys/password@pdborcl as sysdba 

ALTER DATABASE DATAFILE 'C:\hr_df.dbf' RESIZE 50M ;

DROP TABLESPACE hr_tbs INCLUDING CONTENTS AND DATAFILES ;