/************************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

 

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

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

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

|   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 id):

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

 

   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 idx_empno on emp_BAK(empno);


explain plan for SELECT * FROM EMP_BAK WHERE empno = 7566;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);


Plan hash value: 3807183402

 

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

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

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

|   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          | IDX_EMPNO |     1 |       |     1   (0)| 00:00:01 |

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

 

Predicate Information (identified by operation id):

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

 

   2 - access("EMPNO"=7566)

 

Note

-----

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

   

   

/************************INDEX UNIQUE SCAN****************************/

DROP INDEX idx_empno;

Create unique index idx_empno on emp_BAK(empno);


explain plan for SELECT * FROM EMP_BAK WHERE empno = 408;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);


Plan hash value: 4109241139

 

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

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

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

|   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         | IDX_EMPNO |     1 |       |     0   (0)| 00:00:01 |

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

 

Predicate Information (identified by operation id):

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

 

   2 - access("EMPNO"=7566)

   

/************************INDEX FULL SCAN****************************/

Create index idx_empno_name on emp_BAK(empno,ename);

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

 

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

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

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

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

|*  1 |  INDEX FULL SCAN | IDX_EMPNO_NAME |    14 |   280 |     1   (0)| 00:00:01 |

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

 

Predicate Information (identified by operation id):

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

 

   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

 

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

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

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

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

|*  1 |  INDEX FAST FULL SCAN| IDX_EMPNO_NAME |    14 |   140 |     2   (0)| 00:00:01 |

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

 

Predicate Information (identified by operation id):

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

 

   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

 

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

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

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

|   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           | IDX_EMPNO_NAME |     1 |       |     1   (0)| 00:00:01 |

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

 

Predicate Information (identified by operation id):

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

 

   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

 

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

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

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

|   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 id):

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

 

   1 - access("E"."DEPTNO"="D"."DEPTNO")

   

   

/************************Nested Loop****************************/ 

drop index idx_empno_name;

Create index idx_empno on emp_bak(empno);

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

 

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

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

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

|   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 | IDX_EMPNO |     1 |     4 |     0   (0)| 00:00:01 |

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

 

Predicate Information (identified by operation id):

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

 

   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

 

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

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

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

|   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 id):

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

 

   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

 

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

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

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

|   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 id):

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

 

   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

 

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

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

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

|   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 id):

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

 

   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

 

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

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

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

|   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 id):

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

 

   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 relationship between indexes and SQL performance?


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';