oracle script to check the database growth

SET LINESIZE 200

SET PAGESIZE 200

COL "Database Size" FORMAT a13

COL "Used Space" FORMAT a11

COL "Used in %" FORMAT a11

COL "Free in %" FORMAT a11

COL "Database Name" FORMAT a13

COL "Free Space" FORMAT a12

COL "Growth DAY" FORMAT a11

COL "Growth WEEK" FORMAT a12

COL "Growth DAY in %" FORMAT a16

COL "Growth WEEK in %" FORMAT a16

SELECT

(select min(creation_time) from v$datafile) "Create Time",

(select name from v$database) "Database Name",

ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2) || ' MB' "Database Size",

ROUND((SUM(USED.BYTES) / 1024 / 1024 ) - ROUND(FREE.P / 1024 / 1024 ),2) || ' MB' "Used Space",

ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 )) / ROUND(SUM(USED.BYTES) / 1024 / 1024 ,2)*100,2) || '% MB' "Used in %",

ROUND((FREE.P / 1024 / 1024 ),2) || ' MB' "Free Space",

ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - ((SUM(USED.BYTES) / 1024 / 1024 ) - ROUND(FREE.P / 1024 / 1024 )))/ROUND(SUM(USED.BYTES) / 1024 / 1024,2 )*100,2) || '% MB' "Free in %",

ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile),2) || ' MB' "Growth DAY",

ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)/ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2)*100,3) || '% MB' "Growth DAY in %",

ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)*7,2) || ' MB' "Growth WEEK",

ROUND((((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)/ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2)*100)*7,3) || '% MB' "Growth WEEK in %"

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;

Output:

SQL Tuning Advisor for SQL_ID

Here we are going to discuss how to do SQL Tuning Advisor for SQL_ID.

1.Create SQL Tuning Advisor task

DECLARE

  my_task_name VARCHAR2(30);

begin

my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => '7a6b4442j5pcz',scope => 'COMPREHENSIVE',time_limit => 60,task_name => 'STA:7a6b4442j5pcz',description => '7a6b4442j5pcz');

end;

/

2.Check if the task has been created

SELECT task_name FROM DBA_ADVISOR_LOG WHERE owner = ‘TEST’;

3. Run Task 

EXEC DBMS_SQLTUNE.execute_tuning_task( task_name => 'STA:7a6b4442j5pcz');

3.Checking the Status

SELECT status FROM DBA_ADVISOR_TASKS WHERE task_name = ‘STA:7a6b4442j5pcz’;
SELECT sofar, totalwork, username FROM V$ADVISOR_PROGRESS ;

3. View results 

SET LONG 10000

SET PAGESIZE 1000

SET LINESIZE 200

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('STA:7a6b4442j5pcz') from dual;

DBMS_SQLTUNE.REPORT_TUNING_TASK('STA:7A6B4442J5PCZ')

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

GENERAL INFORMATION SECTION

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

---

Tuning Task Name                  : STA:7a6b4442j5pcz

Tuning Task Owner                 : SYS

Scope                             : COMPREHENSIVE

Time Limit(seconds)               : 60

Completion Status                 : COMPLETED

Started at                        : 10/16/2012 19:47:

27

Completed at                      : 10/16/2012 19:47:54

Number of SQL Profile Findings    : 1

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

FINDINGS SECTION (1 finding)

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

-----------

1- SQL Profile Finding (see explain plans section below)

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

  A potentially better execution plan was found for thi

s statement.

  Recommendation (estimated benefit: 99.94%)

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

  - Consider accepting the recommended SQL profile.

    execute dbms_sqltune.accept_sql_profile(task_nam

e => 'STA:7a6b4442j5pcz',

            replace => TRUE);

4.Delete task.

BEGIN

DBMS_SQLTUNE.drop_tuning_task (task_name => 'STA:7A6B4442J5PCZ');

END;

/

hope this article helped you. Your suggestions/feedbacks are most welcome.

Keep learning... Have a great day!!!

Oracle Baseline/ SQL plan migration to its DEV/QA/Clone

In this article I have explained step by step how to copy/migrate SQL baseline from source database to target database.

On Source database

============ 

Check the list of SQL plan baselines on Source

SQL> select SQL_HANDLE,plan_name, enabled, accepted FROM dba_sql_plan_baselines ;


SQL_172a4815a43b43db SQL_PLAN_1fak82qk3qhyv0239c560 YES NO
SQL_172a4815a43b43db SQL_PLAN_1fak82qk3qhyv3fc6434b YES NO
SQL_172a4815a43b43db SQL_PLAN_1fak82qk3qhyvccdd3f88 YES YES

Create the staging table to hold the SQL baseline

SQL> BEGIN
DBMS_SPM.CREATE_STGTAB_BASELINE(
table_name > 'spm_staging_tab',
table_owner > 'BIASDBA',
tablespace_name > 'TOOLS');
END;


PL/SQL procedure successfully completed.

Check the baseline staging table

SQL> desc biasdba.spm_staging_tab
Name Null? Type
----------------------------------------- -------- ----------------------------
VERSION NUMBER
SIGNATURE NUMBER
SQL_HANDLE VARCHAR2(30)
OBJ_NAME VARCHAR2(30)
OBJ_TYPE VARCHAR2(30)
PLAN_ID NUMBER
SQL_TEXT CLOB
CREATOR VARCHAR2(30)
ORIGIN VARCHAR2(30)
DESCRIPTION VARCHAR2(500)
DB_VERSION VARCHAR2(64)
CREATED TIMESTAMP(6)
LAST_MODIFIED TIMESTAMP(6)
LAST_EXECUTED TIMESTAMP(6)
LAST_VERIFIED TIMESTAMP(6)
STATUS NUMBER
OPTIMIZER_COST NUMBER
MODULE VARCHAR2(64)
ACTION VARCHAR2(64)
EXECUTIONS NUMBER
ELAPSED_TIME NUMBER
CPU_TIME NUMBER
BUFFER_GETS NUMBER
DISK_READS NUMBER
DIRECT_WRITES NUMBER
ROWS_PROCESSED NUMBER
FETCHES NUMBER
END_OF_FETCH_COUNT NUMBER
CATEGORY VARCHAR2(30)
SQLFLAGS NUMBER
TASK_ID NUMBER
TASK_EXEC_NAME VARCHAR2(30)
TASK_OBJ_ID NUMBER
TASK_FND_ID NUMBER
TASK_REC_ID NUMBER
INUSE_FEATURES NUMBER
PARSE_CPU_TIME NUMBER
PRIORITY NUMBER
OPTIMIZER_ENV RAW(2000)
BIND_DATA RAW(2000)
PARSING_SCHEMA_NAME VARCHAR2(30)
COMP_DATA CLOB

Now pack the baselines / load the baselines to the staging table created above

SQL> SET SERVEROUTPUT ON
DECLARE
l_plans_packed PLS_INTEGER;
SQL> 2 3 BEGIN
4 l_plans_packed : DBMS_SPM.pack_stgtab_baseline(
table_name > 'spm_staging_tab',
5 6 table_owner > 'BIASDBA');

7 8 DBMS_OUTPUT.put_line('Plans Packed: ' || l_plans_packed);
END;
9 10 /

Plans Packed: 3
PL/SQL procedure successfully completed.

Check the baseline table after loading

SQL> select sql_handle from BIASDBA.spm_staging_tab;
SQL_HANDLE
------------------------------
SQL_172a4815a43b43db
SQL_172a4815a43b43db
SQL_172a4815a43b43db


On target database
============

Copy required table to target database.

Create the DB link to the source database ( If the DBlink not an option then use export / import to copy the baseline staging table data to target).

SQL> create public database link efipfqa1 connect to biasdba identified by biasdba using 'efipfqa1';

Check the DB link is working or not

SQL> select SQL_HANDLE from biasdba.spm_staging_tab@efipfqa1;
SQL_HANDLE
------------------------------
SQL_172a4815a43b43db
SQL_172a4815a43b43db
SQL_172a4815a43b43db

Create the baseline staging table using the source database baseline staging table data

SQL> create table biasdba.spm_staging_tab as select * from biasdba.spm_staging_tab@efipfqa1;Table created.

Now Unpack / unload the baseline to the database dictionary
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2 l_plans_unpacked PLS_INTEGER;
3 BEGIN
l_plans_unpacked : DBMS_SPM.unpack_stgtab_baseline(
4 5 table_name > 'spm_staging_tab',
6 table_owner > 'BIASDBA');

7 8 DBMS_OUTPUT.put_line('Plans Unpacked: ' || l_plans_unpacked);
9 END;
10 /

Plans Unpacked: 3
PL/SQL procedure successfully completed.

Check the baselines created from the unload operation above

SQL> select SQL_HANDLE,plan_name, enabled, accepted FROM dba_sql_plan_baselines ;

SQL_HANDLE PLAN_NAME ENA ACC
------------------------------ ------------------------------ --- ---
SQL_172a4815a43b43db SQL_PLAN_1fak82qk3qhyv0239c560 YES NO
SQL_172a4815a43b43db SQL_PLAN_1fak82qk3qhyv3fc6434b YES NO
SQL_172a4815a43b43db SQL_PLAN_1fak82qk3qhyvccdd3f88 YES YES

I hope this article helped you. Your suggestions/feedbacks are most welcome.
Keep learning... Have a great day!!!

Obtaining Execution Plan in Oracle

Obtaining Execution Plan in Oracle

What is execution plan?
An execution plan describes the list of operations which are performed by SQL Engine for SQL statement. The order of operations and their implementation is decided by the oracle query optimizer. An execution plan also shows the estimated number of rows, cost require to perform the operation, how many bytes oracle will read the data and estimated time required for the operations.

Sample Execution Plan:

1. Access Method & Join type of data access.

2. Name of the objects on which operation is going to perform.

3. Cardinality: Estimated number of rows.

4. Bytes: How much byte of data needs to process for each operation.

5. Cost: Cost require to perform operation.

6. Time: Estimated time require to complete the operation.

Displaying Execution plan:

Below are the two mostly used methods to display an execution plan.

1.      EXPLAIN PLAN command: This displays an execution plan for SQL statement without actually executing it.

2.      V$SQL_PLAN: This dynamic performance view shows execution plan for statement that has been compiled into cursor and stored in the cursor cache.

 DBMS_XPLAN Package: This package provides the different PL/SQL interfaces to display the plan from different sources.

• Obtaining Execution Plan from EXPLAIN PLAN Command and DBMS_XPLAN.DISPLAY function:

Below example shows how to use EXLAIN_PLAN command and DBMS_XPLAN.DISPLAY function to display the plan.

SQL> explain plan for select * from test1 a,test2 b where a.emp_no=b.emp_no;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

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

Plan hash value: 497311279

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

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

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

|   0 | SELECT STATEMENT   |       |     1 |    40 |     5  (20)| 00:00:01 |

|*  1 |  HASH JOIN         |       |     1 |    40 |     5  (20)| 00:00:01 |

|   2 |   TABLE ACCESS FULL| TEST1 |     1 |    20 |     2   (0)| 00:00:01 |

|   3 |   TABLE ACCESS FULL| TEST2 |     1 |    20 |     2   (0)| 00:00:01 |

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

PLAN_TABLE_OUTPUT

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

Predicate Information (identified by operation id):

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

   1 - access("A"."EMP_NO"="B"."EMP_NO")

Note

-----

   - dynamic sampling used for this statement (level=2)

19 rows selected.

• Obtaining Plan from DBMS_XPLAN.DISPLAY_CURSOR function:

Execution plan for executed statement can be displayed by using DBMS_XPLAN.DISPLAY_CURSOR function. It takes input as SQL_ID of the statement.

SQL> select * from table(dbms_xplan.display_cursor('0kkhhb2w93cx0'));

PLAN_TABLE_OUTPUT

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

SQL_ID  0kkhhb2w93cx0, child number 0

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

update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:8,exts

ize=:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13, 65535, NULL,

:13),groups=decode(:14, 65535, NULL, :14), cachehint=:15, hwmincr=:16,

spare1=DECODE(:17,0,NULL,:17),scanhint=:18, bitmapranges=:19 where

ts#=:1 and file#=:2 and block#=:3

Plan hash value: 2170058777

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

PLAN_TABLE_OUTPUT

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

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

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

|   0 | UPDATE STATEMENT      |                |       |       |     3 (100)|          |

|   1 |  UPDATE               | SEG$           |       |       |            |          |

|   2 |   TABLE ACCESS CLUSTER| SEG$           |     1 |    72 |     3   (0)| 00:00:01 |

|*  3 |    INDEX UNIQUE SCAN  | I_FILE#_BLOCK# |     1 |       |     2   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

PLAN_TABLE_OUTPUT

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

   3 - access("TS#"=:1 AND "FILE#"=:2 AND "BLOCK#"=:3)

• Obtaining History Plan from DBMS_XPLAN.DISPLAY_AWR function

Sometimes you will not get plan from DISPLAY_CURSOR function as it might have flushed from the cursor cache. In this case we can obtain the plan using DISPLAY_AWR function. This function gives us all the execution plans that the statement had used in the past. It takes input as SQL_ID of the statement.

SQL> select * from table(dbms_xplan.display_awr('0kkhhb2w93cx0'));

PLAN_TABLE_OUTPUT

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

SQL_ID 0kkhhb2w93cx0

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

update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:8,exts

ize=:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13, 65535, NULL,

:13),groups=decode(:14, 65535, NULL, :14), cachehint=:15, hwmincr=:16,

spare1=DECODE(:17,0,NULL,:17),scanhint=:18, bitmapranges=:19 where

ts#=:1 and file#=:2 and block#=:3

Plan hash value: 2170058777

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

 PLAN_TABLE_OUTPUT

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

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

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

|   0 | UPDATE STATEMENT      |                |       |       |     2 (100)|          |

|   1 |  UPDATE               | SEG$           |       |       |            |          |

|   2 |   TABLE ACCESS CLUSTER| SEG$           |     1 |    71 |     2   (0)| 00:00:01 |

|   3 |    INDEX UNIQUE SCAN  | I_FILE#_BLOCK# |     1 |       |     1   (0)| 00:00:01 |

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

19 rows selected.

Conclusion: In this article we have seen the methods to obtain an execution plan from the cursor cache as well as from AWR. I will write an article on interpreting these execution plans very soon.