SQL tuning_Intern_2015

theoforce

　　/************************Issue****************************/
　　SELECT  l.oracle_username User_name,o.object_name,o.object_type,s.sid,s.serial#,p.spid
　　FROM  v$locked_object l, dba_objects o, v$session s, v$process p
　　WHERE  l.object_id = o.object_id AND l.session_id = s.sid and s.paddr = p.addr;
　　/************************Table DDL****************************/
　　/* table of emp */
　　CREATE
　　TABLE EMP
　　(
　　EMPNO INT,
　　ENAME VARCHAR2(10),
　　JOB VARCHAR2(10),
　　MGR INT,
　　HIREDATE DATE,
　　SAL INT,
　　COMM INT,
　　DEPTNO INT
　　);
　　/* table of dept */
　　CREATE
　　TABLE dept
　　(
　　DEPTNO INT,
　　DNAME VARCHAR2(10),
　　LOC VARCHAR2(10)
　　);
　　/* table of emp_bonus */
　　CREATE
　　TABLE emp_bonus
　　(
　　EMPNO INT,
　　RECEIVED DATE,
　　TYPE INT
　　);
　　/************************Histograms****************************/
　　--Different explain plan between before analyze and after analyze
　　create table t1 as select rownum all_distinct,10000 skew from dual connect by level < =10000;
　　update temp_0120 set skew=all_distinct where rownum< =10;
　　select skew,count(*) from temp_0120 group by skew order by skew;
　　select column_name,num_distinct,density from user_tab_col_statistics where table_name='TEMP_0120';
　　select column_name,endpoint_number,endpoint_value from user_tab_histograms where  table_name='TEMP_0120' and column_name='SKEW';
　　explain plan for select * from TEMP_0120 where skew=1;
　　select * from table(dbms_xplan.display);
　　explain plan for select * from TEMP_0120 where skew=10000;
　　select * from table(dbms_xplan.display);
　　create index skew_idx on temp_0120(skew);
　　analyze table TEMP_0120 compute statistics for all COLUMNS;
　　/************************TABLE ACCESS FULL****************************/
　　explain plan for SELECT * FROM EMP E ,DEPT D WHERE E.DEPTNO = D.DEPTNO AND D.DEPTNO = '10';
　　SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
　　Plan hash value: 615168685
　　---------------------------------------------------------------------------

　　|>　　---------------------------------------------------------------------------
　　|   0 | SELECT STATEMENT   |      |     6 |   318 |     6   (0)| 00:00:01 |
　　|*  1 |  HASH JOIN         |      |     6 |   318 |     6   (0)| 00:00:01 |
　　|*  2 |   TABLE ACCESS FULL| DEPT |     2 |    30 |     3   (0)| 00:00:01 |
　　|*  3 |   TABLE ACCESS FULL| EMP  |     3 |   114 |     3   (0)| 00:00:01 |
　　---------------------------------------------------------------------------

　　Predicate Information (identified by operation>　　---------------------------------------------------
　　1 - access("E"."DEPTNO"="D"."DEPTNO")
　　2 - filter("D"."DEPTNO"=10)
　　3 - filter("E"."DEPTNO"=10)
　　/************************INDEX RANGE SCAN****************************/
　　Select * from user_indexes where table_name = 'EMP';
　　Create table EMP_BAK as select * from EMP;

　　Create index>　　explain plan for SELECT * FROM EMP_BAK WHERE empno = 7566;
　　SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
　　Plan hash value: 3807183402
　　-----------------------------------------------------------------------------------------

　　|>　　-----------------------------------------------------------------------------------------
　　|   0 | SELECT STATEMENT            |           |     1 |    88 |     2   (0)| 00:00:01 |
　　|   1 |  TABLE ACCESS BY INDEX ROWID| EMP_BAK   |     1 |    88 |     2   (0)| 00:00:01 |

　　|*  2 |   INDEX RANGE SCAN          |>　　-----------------------------------------------------------------------------------------

　　Predicate Information (identified by operation>　　---------------------------------------------------
　　2 - access("EMPNO"=7566)
　　Note
　　-----
　　- dynamic sampling used for this statement (level=2)
　　/************************INDEX UNIQUE SCAN****************************/

　　DROP INDEX>
　　Create unique index>　　explain plan for SELECT * FROM EMP_BAK WHERE empno = 408;
　　SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
　　Plan hash value: 4109241139
　　-----------------------------------------------------------------------------------------

　　|>　　-----------------------------------------------------------------------------------------
　　|   0 | SELECT STATEMENT            |           |     1 |    88 |     1   (0)| 00:00:01 |
　　|   1 |  TABLE ACCESS BY INDEX ROWID| EMP_BAK   |     1 |    88 |     1   (0)| 00:00:01 |

　　|*  2 |   INDEX UNIQUE SCAN         |>　　-----------------------------------------------------------------------------------------

　　Predicate Information (identified by operation>　　---------------------------------------------------
　　2 - access("EMPNO"=7566)
　　/************************INDEX FULL SCAN****************************/

　　Create index>　　explain plan for SELECT empno,ename FROM EMP_BAK where empno is not null order by empno;
　　SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
　　Plan hash value: 2175425688
　　-----------------------------------------------------------------------------------

　　|>　　-----------------------------------------------------------------------------------
　　|   0 | SELECT STATEMENT |                |    14 |   280 |     1   (0)| 00:00:01 |

　　|*  1 |  INDEX FULL SCAN |>　　-----------------------------------------------------------------------------------

　　Predicate Information (identified by operation>　　---------------------------------------------------
　　1 - filter("EMPNO" IS NOT NULL)
　　/************************INDEX FAST FULL SCAN****************************/
　　explain plan for SELECT /*+ INDEX_FFS(EMP_BAK)*/empno,ename FROM EMP_BAK where empno is not null;
　　SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
　　Plan hash value: 3573832906
　　---------------------------------------------------------------------------------------

　　|>　　---------------------------------------------------------------------------------------
　　|   0 | SELECT STATEMENT     |                |    14 |   140 |     2   (0)| 00:00:01 |

　　|*  1 |  INDEX FAST FULL SCAN|>　　---------------------------------------------------------------------------------------

　　Predicate Information (identified by operation>　　---------------------------------------------------
　　1 - filter("EMPNO" IS NOT NULL)
　　/************************INDEX SKIP SCAN****************************/
　　explain plan for SELECT * FROM EMP_BAK where ENAME = 'BLAKE';
　　SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
　　Plan hash value: 838373768
　　----------------------------------------------------------------------------------------------

　　|>　　----------------------------------------------------------------------------------------------
　　|   0 | SELECT STATEMENT            |                |     1 |    38 |     2   (0)| 00:00:01 |
　　|   1 |  TABLE ACCESS BY INDEX ROWID| EMP_BAK        |     1 |    38 |     2   (0)| 00:00:01 |

　　|*  2 |   INDEX SKIP SCAN           |>　　----------------------------------------------------------------------------------------------

　　Predicate Information (identified by operation>　　---------------------------------------------------
　　2 - access("ENAME"='BLAKE')
　　filter("ENAME"='BLAKE')
　　/************************Hash Join****************************/
　　explain plan for  select * from emp e,dept d where e.deptno = d.deptno;
　　SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
　　Plan hash value: 1123238657
　　---------------------------------------------------------------------------

　　|>　　---------------------------------------------------------------------------
　　|   0 | SELECT STATEMENT   |      |    13 |   702 |     6   (0)| 00:00:01 |
　　|*  1 |  HASH JOIN         |      |    13 |   702 |     6   (0)| 00:00:01 |
　　|   2 |   TABLE ACCESS FULL| EMP  |    14 |   532 |     3   (0)| 00:00:01 |
　　|   3 |   TABLE ACCESS FULL| DEPT |    16 |   256 |     3   (0)| 00:00:01 |
　　---------------------------------------------------------------------------

　　Predicate Information (identified by operation>　　---------------------------------------------------
　　1 - access("E"."DEPTNO"="D"."DEPTNO")
　　/************************Nested Loop****************************/

　　drop index>
　　Create index>　　explain plan for  select e.empno from emp_bak e,emp_bonus b where e.empno = b.empno and b.type = 1;
　　SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
　　Plan hash value: 971689140
　　--------------------------------------------------------------------------------

　　|>　　--------------------------------------------------------------------------------
　　|   0 | SELECT STATEMENT   |           |     1 |    11 |     3   (0)| 00:00:01 |
　　|   1 |  NESTED LOOPS      |           |     1 |    11 |     3   (0)| 00:00:01 |
　　|*  2 |   TABLE ACCESS FULL| EMP_BONUS |     1 |     7 |     3   (0)| 00:00:01 |

　　|*  3 |   INDEX RANGE SCAN |>　　--------------------------------------------------------------------------------

　　Predicate Information (identified by operation>　　---------------------------------------------------
　　2 - filter("B"."TYPE"=1)
　　3 - access("E"."EMPNO"="B"."EMPNO")
　　/************************Sort Merger Join****************************/
　　explain plan for  select /*+ USE_MERGE(e,d) */ ename,d.deptno from emp e,dept d where e.deptno = d.deptno;
　　SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
　　Plan hash value: 3406566467
　　----------------------------------------------------------------------------

　　|>　　----------------------------------------------------------------------------
　　|   0 | SELECT STATEMENT    |      |    13 |   169 |     8  (25)| 00:00:01 |
　　|   1 |  MERGE JOIN         |      |    13 |   169 |     8  (25)| 00:00:01 |
　　|   2 |   SORT JOIN         |      |    14 |   126 |     4  (25)| 00:00:01 |
　　|   3 |    TABLE ACCESS FULL| EMP  |    14 |   126 |     3   (0)| 00:00:01 |
　　|*  4 |   SORT JOIN         |      |    16 |    64 |     4  (25)| 00:00:01 |
　　|   5 |    TABLE ACCESS FULL| DEPT |    16 |    64 |     3   (0)| 00:00:01 |
　　----------------------------------------------------------------------------

　　Predicate Information (identified by operation>　　---------------------------------------------------
　　4 - access("E"."DEPTNO"="D"."DEPTNO")
　　filter("E"."DEPTNO"="D"."DEPTNO")
　　/************************Hints****************************/
　　explain plan for  select e.ename,d.deptno,b.type from emp e,dept d, emp_bonus b
　　where e.deptno = d.deptno and e.empno = b.empno;
　　SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
　　Plan hash value: 572357001
　　---------------------------------------------------------------------------------

　　|>　　---------------------------------------------------------------------------------
　　|   0 | SELECT STATEMENT    |           |     3 |    72 |     9   (0)| 00:00:01 |
　　|*  1 |  HASH JOIN          |           |     3 |    72 |     9   (0)| 00:00:01 |
　　|*  2 |   HASH JOIN         |           |     3 |    60 |     6   (0)| 00:00:01 |
　　|   3 |    TABLE ACCESS FULL| EMP_BONUS |     3 |    21 |     3   (0)| 00:00:01 |
　　|   4 |    TABLE ACCESS FULL| EMP       |    14 |   182 |     3   (0)| 00:00:01 |
　　|   5 |   TABLE ACCESS FULL | DEPT      |    16 |    64 |     3   (0)| 00:00:01 |
　　---------------------------------------------------------------------------------

　　Predicate Information (identified by operation>　　---------------------------------------------------
　　1 - access("E"."DEPTNO"="D"."DEPTNO")
　　2 - access("E"."EMPNO"="B"."EMPNO")
　　explain plan for select /*+ LEADING(e d) */ e.ename,d.deptno,b.type
　　from emp e,dept d, emp_bonus b where e.deptno = d.deptno and e.empno = b.empno;
　　SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
　　Plan hash value: 1970361097
　　---------------------------------------------------------------------------------

　　|>　　---------------------------------------------------------------------------------
　　|   0 | SELECT STATEMENT    |           |     3 |    72 |     9   (0)| 00:00:01 |
　　|*  1 |  HASH JOIN          |           |     3 |    72 |     9   (0)| 00:00:01 |
　　|*  2 |   HASH JOIN         |           |    13 |   221 |     6   (0)| 00:00:01 |
　　|   3 |    TABLE ACCESS FULL| EMP       |    14 |   182 |     3   (0)| 00:00:01 |
　　|   4 |    TABLE ACCESS FULL| DEPT      |    16 |    64 |     3   (0)| 00:00:01 |
　　|   5 |   TABLE ACCESS FULL | EMP_BONUS |     3 |    21 |     3   (0)| 00:00:01 |
　　---------------------------------------------------------------------------------

　　Predicate Information (identified by operation>　　---------------------------------------------------
　　1 - access("E"."EMPNO"="B"."EMPNO")
　　2 - access("E"."DEPTNO"="D"."DEPTNO")
　　explain plan for select /*+ LEADING(e d) USE_NL(e,d) */ e.ename,d.deptno,b.type
　　from emp e,dept d, emp_bonus b where e.deptno = d.deptno and e.empno = b.empno;
　　SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
　　Plan hash value: 745359820
　　---------------------------------------------------------------------------------

　　|>　　---------------------------------------------------------------------------------
　　|   0 | SELECT STATEMENT    |           |     3 |    72 |    27   (0)| 00:00:01 |
　　|*  1 |  HASH JOIN          |           |     3 |    72 |    27   (0)| 00:00:01 |
　　|   2 |   NESTED LOOPS      |           |    13 |   221 |    24   (0)| 00:00:01 |
　　|   3 |    TABLE ACCESS FULL| EMP       |    14 |   182 |     3   (0)| 00:00:01 |
　　|*  4 |    TABLE ACCESS FULL| DEPT      |     1 |     4 |     2   (0)| 00:00:01 |
　　|   5 |   TABLE ACCESS FULL | EMP_BONUS |     3 |    21 |     3   (0)| 00:00:01 |
　　---------------------------------------------------------------------------------

　　Predicate Information (identified by operation>　　---------------------------------------------------
　　1 - access("E"."EMPNO"="B"."EMPNO")
　　4 - filter("E"."DEPTNO"="D"."DEPTNO")
　　--====================================
　　Questions:
　　1. Why is searching for large-table full-table scans critical to SQL tuning?
　　a) They indicate an optimized execution plan.
　　b) They may be able to be tuned to use an index
　　c) The full-table scan should be normalized from the database design
　　d) A full-table scan is always sub-optimal.

　　2. What best describes the>　　a) Indexes are only used in special cases
　　b) Indexes are used to make table storage more efficient
　　c) Indexes rarely make a difference in SQL performance
　　d) Indexes exist solely to improve query speed.
　　4. List table joins type.
　　3. The performance is always good if using Index. Is it right? Why?
　　5. Assume EMP has 100,000 records while DEPT has 100 records. No index in both tables currently.
　　SALES department has 280 employees.
　　Please tuning the query to get the best performance you think and explan why.
　　SELECT E.EMPNO, E.ENAME, E.JOB FROM EMP E,DEPT D WHERE E.DEPTNO = D.DEPTNO AND D.DNAME = 'SALES';