Database size history

Summary 

Here i will demonstrate some ways to calculate the database size and space history:

Faster query!(all the files, data and temp)

SELECT SUM(MB_ALLOC)/1024 GB_DB_SIZE FROM

(SELECT SUM(ROUND(bytes_used/(1024*1024),2) + ROUND(bytes_free/(1024*1024),2)) MB_ALLOC

FROM V$temp_space_header, dba_temp_files

WHERE V$temp_space_header.file_id (+) = dba_temp_files.file_id

UNION

SELECT SUM(BYTES)/(1024*1024) MB_ALLOC FROM dba_data_files);

Slower query

SELECT  ROUND(SUM(USED.BYTES) / 1024 / 1024 / 1024 ) || ' GB' "Database Size"

,       ROUND(SUM(USED.BYTES) / 1024 / 1024 / 1024 ) -

        ROUND(FREE.P / 1024 / 1024 / 1024) || ' GB' "Used space"

,       ROUND(FREE.P / 1024 / 1024 / 1024) || ' GB' "Free space"

FROM    (SELECT BYTES

        FROM    V$DATAFILE

        UNION   ALL

        SELECT  BYTES

        FROM    V$TEMPFILE

        UNION   ALL

        SELECT BYTES

        FROM    V$LOG) USED

,       (SELECT SUM(BYTES) AS P

        FROM DBA_FREE_SPACE) FREE

GROUP BY FREE.P;

To keep a history of how database is growing you can create a table that records for example every week the database size. The following procedure does not take into account the UNDO tablespace and TEMPORARY tablespace, only real data and indexes.

Create the table for database size history

create table db_space_hist (

        timestamp    date,

        total_space  number(8),

        used_space   number(8),

        free_space   number(8),

        pct_inuse    number(5,2),

        num_db_files number(5)

);

Create the procedure db_space_history

CREATE OR REPLACE PROCEDURE db_space_history AS

BEGIN

   INSERT INTO db_space_hist

        SELECT SYSDATE, total_space,

        total_space-NVL(free_space,0) used_space,

        NVL(free_space,0) free_space,

        ((total_space - NVL(free_space,0)) / total_space)*100 pct_inuse,

        num_db_files

 FROM ( SELECT SUM(bytes)/1024/1024 free_space

        FROM   sys.DBA_FREE_SPACE WHERE tablespace_name NOT LIKE '%UNDO%') FREE,

      ( SELECT SUM(bytes)/1024/1024 total_space,

               COUNT(*) num_db_files

        FROM   sys.DBA_DATA_FILES WHERE tablespace_name NOT LIKE '%UNDO%') FULL;

   COMMIT;

END;

/

Create the job that runs once in a week

DECLARE

  X NUMBER;

BEGIN

  SYS.DBMS_JOB.SUBMIT

    (

      job        => X

     ,what       => 'SYS.DB_SPACE_HISTORY;'

     ,next_date  => TO_DATE('22/02/2008 19:40:28','dd/mm/yyyy hh24:mi:ss')

     ,INTERVAL   => 'TRUNC(SYSDATE+7)'

     ,no_parse   => FALSE

    );

END;

Monitor how things going on periodically:

select * from db_space_hist order by timestamp desc;

Alternative:How the database size increased in GBytes per month for the last year.

SELECT TO_CHAR(creation_time, 'RRRR Month') "Month",

round(SUM(bytes)/1024/1024/1024) "Growth in GBytes"

FROM sys.v_$datafile

WHERE creation_time > SYSDATE-365

GROUP BY TO_CHAR(creation_time, 'RRRR Month');

Month          Growth in GBytes

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

2008 December              1331

2008 November               779

2008 October                447

2009 April                  797

2009 August                 344

2009 February               505

2009 January                443

2009 July                   358

2009 June                   650

2009 March                  452

2009 May                   1787

2009 October                255

2009 September              158

As you can see from the last query the database increased its size for the month: 2009 October 255 GBytes

Source: Internet

Tablespace fragmentation

Summary 
Use the following script to find if some tablespaces have fragmentation.

SELECT   dfsc.tablespace_name tablespace_name,
         DECODE (dfsc.percent_extents_coalesced,100,
(DECODE (GREATEST ((SELECT COUNT (1)
FROM dba_free_space dfs
WHERE dfs.tablespace_name = dfsc.tablespace_name), 1), 1, 'No Fragmentation',
'Bubbles, No Fragmentation')), 'Possible Honey Comb, No Fragmentation') fragmentation_status
    FROM dba_free_space_coalesced dfsc
ORDER BY dfsc.tablespace_name;

One simple solution to avoid fragmentation is creating all tablespaces with the same uniform extent size.

Source : Internet

Partition table GL_JE_LINES

Summary 
Here are the steps to partition for example the table GL_JE_LINES in Oracle E-Business Suite. 

1. Shutdown the Apps tier 

2. Create the new partitioned table with the same column structure as the original and with the partitions. 

The table GL_JE_LINES is partitioned by period_name (monthly)

CREATE TABLE GL.GL_JE_LINES_P

(

  JE_HEADER_ID                  NUMBER(15)      NOT NULL,

  JE_LINE_NUM                   NUMBER(15)      NOT NULL,

  LAST_UPDATE_DATE              DATE            NOT NULL,

  LAST_UPDATED_BY               NUMBER(15)      NOT NULL,

  SET_OF_BOOKS_ID               NUMBER(15)      NOT NULL,

  CODE_COMBINATION_ID           NUMBER(15)      NOT NULL,

  PERIOD_NAME                   VARCHAR2(15 BYTE) NOT NULL,

  EFFECTIVE_DATE                DATE            NOT NULL,

  STATUS                        VARCHAR2(1 BYTE) NOT NULL,

  CREATION_DATE                 DATE,

  CREATED_BY                    NUMBER(15),

  LAST_UPDATE_LOGIN             NUMBER(15),

  ENTERED_DR                    NUMBER,

  ENTERED_CR                    NUMBER,

  ACCOUNTED_DR                  NUMBER,

  ACCOUNTED_CR                  NUMBER,

  DESCRIPTION                   VARCHAR2(240 BYTE),

  LINE_TYPE_CODE                VARCHAR2(20 BYTE),

  REFERENCE_1                   VARCHAR2(240 BYTE),

  REFERENCE_2                   VARCHAR2(240 BYTE),

  REFERENCE_3                   VARCHAR2(240 BYTE),

  REFERENCE_4                   VARCHAR2(240 BYTE),

  REFERENCE_5                   VARCHAR2(240 BYTE),

  ATTRIBUTE1                    VARCHAR2(150 BYTE),

  ATTRIBUTE2                    VARCHAR2(150 BYTE),

  ATTRIBUTE3                    VARCHAR2(150 BYTE),

  ATTRIBUTE4                    VARCHAR2(150 BYTE),

  ATTRIBUTE5                    VARCHAR2(150 BYTE),

  ATTRIBUTE6                    VARCHAR2(150 BYTE),

  ATTRIBUTE7                    VARCHAR2(150 BYTE),

  ATTRIBUTE8                    VARCHAR2(150 BYTE),

  ATTRIBUTE9                    VARCHAR2(150 BYTE),

  ATTRIBUTE10                   VARCHAR2(150 BYTE),

  ATTRIBUTE11                   VARCHAR2(150 BYTE),

  ATTRIBUTE12                   VARCHAR2(150 BYTE),

  ATTRIBUTE13                   VARCHAR2(150 BYTE),

  ATTRIBUTE14                   VARCHAR2(150 BYTE),

  ATTRIBUTE15                   VARCHAR2(150 BYTE),

  ATTRIBUTE16                   VARCHAR2(150 BYTE),

  ATTRIBUTE17                   VARCHAR2(150 BYTE),

  ATTRIBUTE18                   VARCHAR2(150 BYTE),

  ATTRIBUTE19                   VARCHAR2(150 BYTE),

  ATTRIBUTE20                   VARCHAR2(150 BYTE),

  CONTEXT                       VARCHAR2(150 BYTE),

  CONTEXT2                      VARCHAR2(150 BYTE),

  INVOICE_DATE                  DATE,

  TAX_CODE                      VARCHAR2(15 BYTE),

  INVOICE_IDENTIFIER            VARCHAR2(20 BYTE),

  INVOICE_AMOUNT                NUMBER,

  NO1                           VARCHAR2(150 BYTE),

  STAT_AMOUNT                   NUMBER,

  IGNORE_RATE_FLAG              VARCHAR2(1 BYTE),

  CONTEXT3                      VARCHAR2(150 BYTE),

  USSGL_TRANSACTION_CODE        VARCHAR2(30 BYTE),

  SUBLEDGER_DOC_SEQUENCE_ID     NUMBER,

  CONTEXT4                      VARCHAR2(150 BYTE),

  SUBLEDGER_DOC_SEQUENCE_VALUE  NUMBER,

  REFERENCE_6                   VARCHAR2(240 BYTE),

  REFERENCE_7                   VARCHAR2(240 BYTE),

  GL_SL_LINK_ID                 NUMBER,

  GL_SL_LINK_TABLE              VARCHAR2(30 BYTE),

  REFERENCE_8                   VARCHAR2(240 BYTE),

  REFERENCE_9                   VARCHAR2(240 BYTE),

  REFERENCE_10                  VARCHAR2(240 BYTE),

  GLOBAL_ATTRIBUTE_CATEGORY     VARCHAR2(30 BYTE),

  GLOBAL_ATTRIBUTE1             VARCHAR2(150 BYTE),

  GLOBAL_ATTRIBUTE2             VARCHAR2(150 BYTE),

  GLOBAL_ATTRIBUTE3             VARCHAR2(150 BYTE),

  GLOBAL_ATTRIBUTE4             VARCHAR2(150 BYTE),

  GLOBAL_ATTRIBUTE5             VARCHAR2(150 BYTE),

  GLOBAL_ATTRIBUTE6             VARCHAR2(150 BYTE),

  GLOBAL_ATTRIBUTE7             VARCHAR2(150 BYTE),

  GLOBAL_ATTRIBUTE8             VARCHAR2(150 BYTE),

  GLOBAL_ATTRIBUTE9             VARCHAR2(150 BYTE),

  GLOBAL_ATTRIBUTE10            VARCHAR2(150 BYTE),

  JGZZ_RECON_STATUS             VARCHAR2(1 BYTE),

  JGZZ_RECON_DATE               DATE,

  JGZZ_RECON_ID                 NUMBER,

  JGZZ_RECON_REF                VARCHAR2(240 BYTE),

  JGZZ_RECON_CONTEXT            VARCHAR2(30 BYTE),

  TAXABLE_LINE_FLAG             VARCHAR2(1 BYTE),

  TAX_TYPE_CODE                 VARCHAR2(1 BYTE),

  TAX_CODE_ID                   NUMBER(15),

  TAX_ROUNDING_RULE_CODE        VARCHAR2(1 BYTE),

  AMOUNT_INCLUDES_TAX_FLAG      VARCHAR2(1 BYTE),

  TAX_DOCUMENT_IDENTIFIER       VARCHAR2(50 BYTE),

  TAX_DOCUMENT_DATE             DATE,

  TAX_CUSTOMER_NAME             VARCHAR2(240 BYTE),

  TAX_CUSTOMER_REFERENCE        VARCHAR2(240 BYTE),

  TAX_REGISTRATION_NUMBER       VARCHAR2(50 BYTE),

  TAX_LINE_FLAG                 VARCHAR2(1 BYTE),

  TAX_GROUP_ID                  NUMBER(15)

)

PARTITION BY LIST (period_name) 

(  

  PARTITION GL_JE_LINES_01_2006 VALUES ('JAN-06'),

  PARTITION GL_JE_LINES_02_2006 VALUES ('FEB-06'),

  PARTITION GL_JE_LINES_03_2006 VALUES ('MAR-06'),

  PARTITION GL_JE_LINES_04_2006 VALUES ('APR-06'),

  PARTITION GL_JE_LINES_05_2006 VALUES ('MAY-06'),

  PARTITION GL_JE_LINES_06_2006 VALUES ('JUN-06'),

  PARTITION GL_JE_LINES_07_2006 VALUES ('JUL-06'),

  PARTITION GL_JE_LINES_08_2006 VALUES ('AUG-06'),

  PARTITION GL_JE_LINES_09_2006 VALUES ('SEP-06'),

  PARTITION GL_JE_LINES_10_2006 VALUES ('OCT-06'),

  PARTITION GL_JE_LINES_11_2006 VALUES ('NOV-06'),

  PARTITION GL_JE_LINES_12_2006 VALUES ('DEC-06'),

  PARTITION GL_JE_LINES_01_2007 VALUES ('JAN-07'),

  PARTITION GL_JE_LINES_02_2007 VALUES ('FEB-07'),

  PARTITION GL_JE_LINES_03_2007 VALUES ('MAR-07'),

  PARTITION GL_JE_LINES_04_2007 VALUES ('APR-07'),

  PARTITION GL_JE_LINES_05_2007 VALUES ('MAY-07'),

  PARTITION GL_JE_LINES_06_2007 VALUES ('JUN-07'),

  PARTITION GL_JE_LINES_07_2007 VALUES ('JUL-07'),

  PARTITION GL_JE_LINES_08_2007 VALUES ('AUG-07'),

  PARTITION GL_JE_LINES_09_2007 VALUES ('SEP-07'),

  PARTITION GL_JE_LINES_10_2007 VALUES ('OCT-07'),

  PARTITION GL_JE_LINES_11_2007 VALUES ('NOV-07'),

  PARTITION GL_JE_LINES_12_2007 VALUES ('DEC-07'),

  PARTITION GL_JE_LINES_01_2008 VALUES ('JAN-08'),

  PARTITION GL_JE_LINES_02_2008 VALUES ('FEB-08'),

  PARTITION GL_JE_LINES_03_2008 VALUES ('MAR-08'),

  PARTITION GL_JE_LINES_04_2008 VALUES ('APR-08'),

  PARTITION GL_JE_LINES_05_2008 VALUES ('MAY-08'),

  PARTITION GL_JE_LINES_06_2008 VALUES ('JUN-08'),

  PARTITION GL_JE_LINES_07_2008 VALUES ('JUL-08'),

  PARTITION GL_JE_LINES_08_2008 VALUES ('AUG-08'),

  PARTITION GL_JE_LINES_09_2008 VALUES ('SEP-08'),

  PARTITION GL_JE_LINES_10_2008 VALUES ('OCT-08'),

  PARTITION GL_JE_LINES_11_2008 VALUES ('NOV-08'),

  PARTITION GL_JE_LINES_12_2008 VALUES ('DEC-08'),

  PARTITION GL_JE_LINES_01_2009 VALUES ('JAN-09'),

  PARTITION GL_JE_LINES_02_2009 VALUES ('FEB-09'),

  PARTITION GL_JE_LINES_03_2009 VALUES ('MAR-09'),

  PARTITION GL_JE_LINES_04_2009 VALUES ('APR-09'),

  PARTITION GL_JE_LINES_05_2009 VALUES ('MAY-09'),

  PARTITION GL_JE_LINES_06_2009 VALUES ('JUN-09'),

  PARTITION GL_JE_LINES_07_2009 VALUES ('JUL-09'),

  PARTITION GL_JE_LINES_08_2009 VALUES ('AUG-09'),

  PARTITION GL_JE_LINES_09_2009 VALUES ('SEP-09'),

  PARTITION GL_JE_LINES_10_2009 VALUES ('OCT-09'),

  PARTITION GL_JE_LINES_11_2009 VALUES ('NOV-09'),

  PARTITION GL_JE_LINES_12_2009 VALUES ('DEC-09'),

  PARTITION GL_JE_LINES_01_2010 VALUES ('JAN-10'),

  PARTITION GL_JE_LINES_02_2010 VALUES ('FEB-10'),

  PARTITION GL_JE_LINES_03_2010 VALUES ('MAR-10'),

  PARTITION GL_JE_LINES_04_2010 VALUES ('APR-10'),

  PARTITION GL_JE_LINES_05_2010 VALUES ('MAY-10'),

  PARTITION GL_JE_LINES_06_2010 VALUES ('JUN-10'),

  PARTITION GL_JE_LINES_07_2010 VALUES ('JUL-10'),

  PARTITION GL_JE_LINES_08_2010 VALUES ('AUG-10'),

  PARTITION GL_JE_LINES_09_2010 VALUES ('SEP-10'),

  PARTITION GL_JE_LINES_10_2010 VALUES ('OCT-10'),

  PARTITION GL_JE_LINES_11_2010 VALUES ('NOV-10'),

  PARTITION GL_JE_LINES_12_2010 VALUES ('DEC-10')) 

  TABLESPACE APPS_TS_TX_DATA 

NOCOMPRESS 

NOCACHE

NOPARALLEL

MONITORING;

3. Insert data from the original table to the partitioned table. Use parallel DML.

ALTER SESSION ENABLE PARALLEL DML;

INSERT /*+ append parallel(A 8) */ INTO GL.GL_JE_LINES_P A 

SELECT /*+ parallel(B 8) */ * FROM GL.GL_JE_LINES B;

COMMIT;

4. Rename the indexes of the original table(Execute the output of the script)

SELECT 'alter index ' || owner || '.' || index_name || ' rename to ' || index_name || '_O;' SQL 

FROM dba_indexes WHERE table_name = 'GL_JE_LINES';

5. Create indexes to the partition table with the same columns as the original indexes. 

(Create new indexes as GL user with parallel option and after creation remove the parallel degree)

SELECT 'ALTER INDEX ' || owner || '.' || index_name || ' noparallel;' SQL 

FROM dba_indexes WHERE table_name = 'GL_JE_LINES';

6. GL_JE_LINES does not have any triggers so skip step 7 and go directly to step 8 

7. Rename the triggers of the original table to OLD (Run as APPS)

SELECT 'alter trigger ' || trigger_name || ' rename to ' || trigger_name || '_O;' SQL

FROM dba_triggers WHERE table_name = 'GL_JE_LINES'

8. Do the table renaming. Rename original table to OLD and the partitioned table to original.

ALTER TABLE GL.GL_JE_LINES RENAME TO GL_JE_LINES_OLD;

ALTER TABLE GL.GL_JE_LINES_P RENAME TO GL_JE_LINES;

9. Drop the synonyms for the OLD table and recreate to point to the new partitioned (Run as APPS user)

DROP SYNONYM APPS.GL_JE_LINES FOR GL_JE_LINES;

CREATE SYNONYM APPS.GL_JE_LINES FOR GL.GL_JE_LINES;

10. Grant the appropriate privileges to new partitioned table(Run as GL user)

GRANT ALTER, DELETE, INDEX, INSERT, REFERENCES, SELECT, UPDATE ON  GL_JE_LINES TO APPS WITH GRANT OPTION;

GRANT SELECT ON  GL_JE_LINES TO APPS_READONLY;

11. No triggers to create on the new table 

12. Calculate new statistics for the table, indexes and partitions. 

13. Run Adadmin to Compile the Apps Schema, recreating the table makes many invalid objects. 

14. Start the Apps tier

Source: Internet

Monitor all DDL statements at the database

Datafiles Disk I/O

Summary 
The Physical design of the database reassures optimal performance for DISK I/O. Storing the datafiles in different filesystems (Disks) is a good technique to minimize disk contention for I/O.

How I/O is spread per datafile 

SELECT NAME, phyrds Physical_READS, ROUND((RATIO_TO_REPORT(phyrds) OVER ())*100, 2)|| '%' PERC_READS,

phywrts Physical_WRITES, ROUND((RATIO_TO_REPORT(phywrts) OVER ())*100, 2)|| '%' PERC_WRITES,

phyrds + phywrts total

FROM v$datafile df, v$filestat fs

WHERE df.FILE# = fs.FILE#

ORDER BY phyrds DESC;

Tip: ORDER BY phyrds, order by physical reads descending. ORDER BY phywrts, order by physical writes descending.

How I/O is spread per filesystem 

SELECT filesystem, ROUND((RATIO_TO_REPORT(READS) OVER ())*100, 2) || '%' PERC_READS,

ROUND((RATIO_TO_REPORT(WRITES) OVER ())*100, 2) || '%' PERC_WRITES,

ROUND((RATIO_TO_REPORT(TOTAL) OVER ())*100, 2) || '%' PERC_TOTAL

FROM (SELECT filesystem, SUM(Physical_READS) READS, SUM(Physical_WRITES) WRITES, SUM(total) TOTAL

FROM (SELECT SUBSTR(NAME, 0, 25) filesystem, phyrds Physical_READS,

ROUND((RATIO_TO_REPORT(phyrds) OVER ())*100, 2)|| '%' PERC_READS,

phywrts Physical_WRITES, ROUND((RATIO_TO_REPORT(phywrts) OVER ())*100, 2)|| '%' PERC_WRITES,

phyrds + phywrts total

FROM v$datafile df, v$filestat fs

WHERE df.FILE# = fs.FILE#

ORDER BY phyrds DESC) A

GROUP BY filesystem) B

ORDER BY ROUND((RATIO_TO_REPORT(total) OVER ())*100, 2) DESC;

Tip: To see the filesystems correct experiment with the SUBSTR(NAME, 0, 25)

How I/O is spread for the datafiles of a specific tablespace

SELECT df.NAME, phyrds Physical_READS, ROUND((RATIO_TO_REPORT(phyrds) OVER ())*100, 2)|| '%' PERC_READS,

phywrts Physical_WRITES, ROUND((RATIO_TO_REPORT(phywrts) OVER ())*100, 2)|| '%' PERC_WRITES,

phyrds + phywrts total

FROM v$datafile df, v$filestat fs, ts$ t

WHERE df.FILE# = fs.FILE#

AND df.ts# = t.ts#

AND t.NAME = 'TABLESPACE_NAME'

ORDER BY phyrds DESC;


Source: Internet