How to Prepare a SQL select statement for SQL Tuning in oracle?

How to Prepare a SQL select statement for SQL Tuning in oracle?



1) connect sqlplus, then set following command

set linesize 1000
set pagesize 1000
set trimout on trimspool on

2) then issue following command

alter session set statistics_level=all;

3) [then Run your query ]

4) then issue following command

select * from table ( sys.dbms_xplan.display_cursor( '','','ADVANCED ALLSTATS LAST'));

5)
and then copy&paste the sqlplus output for sql tuning purpose.



example:-


SQL*Plus: Release 10.2.0.1.0 - Production on Sun Oct 2 13:11:14 2011

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL>
SQL> conn scott/tiger
Connected.
SQL>
SQL>
SQL> set linesize 1000
SQL>
SQL> set pagesize 1000
SQL>
SQL> set trimout on trimspool on
SQL>
SQL> alter session set statistics_level=all;

Session altered.

SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> select ename,sum(sal) from emp where ename like '%L%' group by ename order by ename ;

ENAME SUM(SAL)
---------- ----------
ALLEN 1600
BLAKE 2850
CLARK 2450
MILLER 1300

SQL>
SQL> select * from table ( sys.dbms_xplan.display_cursor( '','','ADVANCED ALLSTATS LAST'));




PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------
SQL_ID 0rxmvbjh43c88, child number 0
-------------------------------------
select ename,sum(sal) from emp where ename like '%L%' group by ename order by ename

Plan hash value: 15469362

-------------------------------------------------------------------------------------------------------------------
-----------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers
| OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------
-----------------------------
| 1 | SORT GROUP BY | | 1 | 4 | 80 | 4 (25)| 00:00:01 | 4 |00:00:00.01 | 7
| 9216 | 9216 | 8192 (0)|
|* 2 | TABLE ACCESS FULL| EMP | 1 | 4 | 80 | 3 (0)| 00:00:01 | 4 |00:00:00.01 | 7
| | | |
-------------------------------------------------------------------------------------------------------------------
-----------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$1
2 - SEL$1 / EMP@SEL$1

Outline Data
-------------

/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "EMP"@"SEL$1")
END_OUTLINE_DATA
*/

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("ENAME" LIKE '%L%')

Column Projection Information (identified by operation id):
-----------------------------------------------------------

1 - (#keys=1) "ENAME"[VARCHAR2,10], SUM("SAL")[22]
2 - "ENAME"[VARCHAR2,10], "SAL"[NUMBER,22]

Note
-----
- dynamic sampling used for this statement


47 rows selected.

SQL>