a) Again, the script first ensures that the amount of memory available to your session is reduced. Then the script executes the same query but this time asks to order the

Plan hash value: 865757019

---

---

| 0 | SELECT STATEMENT | | 1400K| 32M| 1 (0)| 00:00:01 |

|* 1 | TABLE ACCESS HASH| BIGEMP_FACT | 1400K| 32M| 1 (0)| 00:00:01 |

---

Predicate Information (identified by operation id):

--- 1 - access("DEPTNO"=10)

Statistics

--- 1090 recursive calls

10 db block gets 99509 consistent gets 6005 physical reads 0 redo size

33605911 bytes sent via SQL*Net to client

1027083 bytes received via SQL*Net from client 93335 SQL*Net roundtrips to/from client 12 sorts (memory)

0 sorts (disk) 1400000 rows processed SQL>

SQL> set autotrace off SQL> set timing off SQL>

49) Execute the query18.sql script. What do you observe?

a) Again, the script first ensures that the amount of memory available to your session is reduced. Then the script executes the same query but this time asks to order the result based on the nonsorted empno column. The optimizer can still make use of the cluster access path, but must sort the data making the cost of the query higher.

SQL> @query18 SQL> set echo on SQL>

SQL> set linesize 200 SQL> set timing on

Oracle Internal & Or acle Academy Use Only

SQL> set autotrace traceonly SQL>

SQL> alter session set workarea_size_policy=manual;

Session altered.

Elapsed: 00:00:00.00

SQL> alter session set sort_area_size=50000;

Session altered.

Elapsed: 00:00:00.00 SQL>

SQL> alter system flush shared_pool;

System altered.

Elapsed: 00:00:00.10

SQL> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00.04 SQL>

SQL> select * from bigemp_fact where deptno=10 order by empno;

1400000 rows selected.

Elapsed: 00:00:10.01 Execution Plan

--- Plan hash value: 1775608660

---

Cost (%CPU)| Time |

---

| 0 | SELECT STATEMENT | | 1400K| 32M| | 47728 (1)| 00:09:33 |

| 1 | SORT ORDER BY | | 1400K| 32M| 107M|

47728 (1)| 00:09:33 |

|* 2 | TABLE ACCESS HASH| BIGEMP_FACT | 1400K| 32M| | 1 (0)| 00:00:01 |

---

Predicate Information (identified by operation id):

--- 2 - access("DEPTNO"=10)

Statistics

---

Oracle Internal & Or acle Academy Use Only

1139 recursive calls 12 db block gets 6178 consistent gets 12238 physical reads 0 redo size

33605911 bytes sent via SQL*Net to client

1027083 bytes received via SQL*Net from client 93335 SQL*Net roundtrips to/from client 12 sorts (memory)

1 sorts (disk) 1400000 rows processed SQL>

SQL> set autotrace off SQL> set timing off SQL>

50) Execute the query19.sql script. What do you observe?

a) Again, the script first ensures that the amount of memory available to your session is reduced. Then the script executes the same query, but this time asks to order the result based on the sal, deptno key. The optimizer can still make use of the cluster access path, but must sort the data making the cost of the query higher.

SQL> @query19 SQL> set echo on SQL>

SQL> set linesize 200 SQL> set timing on

SQL> set autotrace traceonly SQL>

SQL> alter session set workarea_size_policy=manual;

Session altered.

Elapsed: 00:00:00.00

SQL> alter session set sort_area_size=50000;

Session altered.

Elapsed: 00:00:00.00 SQL>

SQL> alter system flush shared_pool;

System altered.

Elapsed: 00:00:00.10

SQL> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00.09 SQL>

SQL> select * from bigemp_fact where deptno=10 order by sal,empno;

1400000 rows selected.

Oracle Internal & Or acle Academy Use Only

Elapsed: 00:00:09.25 Execution Plan

--- Plan hash value: 1775608660

---

Cost (%CPU)| Time |

---

| 0 | SELECT STATEMENT | | 1400K| 32M| | 47728 (1)| 00:09:33 |

| 1 | SORT ORDER BY | | 1400K| 32M| 107M|

47728 (1)| 00:09:33 |

|* 2 | TABLE ACCESS HASH| BIGEMP_FACT | 1400K| 32M| | 1 (0)| 00:00:01 |

---

Predicate Information (identified by operation id):

--- 2 - access("DEPTNO"=10)

Statistics

--- 1139 recursive calls

12 db block gets 6178 consistent gets 12238 physical reads 0 redo size

33605911 bytes sent via SQL*Net to client

1027083 bytes received via SQL*Net from client 93335 SQL*Net roundtrips to/from client 12 sorts (memory)

1 sorts (disk) 1400000 rows processed SQL>

SQL> set autotrace off SQL> set timing off SQL>

51) Execute the shc_cleanup.sql script to clean up your environment.

SQL> @shc_cleanup SQL> set echo on SQL>

SQL> drop cluster bigemp_cluster including tables;

Cluster dropped.

SQL>

Oracle Internal & Or acle Academy Use Only

52) Case 15: Execute the nic_setup.sql script to set up your environment for this case.

SQL> @nic_setup SQL> set echo on SQL>

SQL> drop cluster emp_dept including tables;

drop cluster emp_dept including tables

ERROR at line 1:

ORA-00943: cluster does not exist

SQL>

SQL> drop table emp purge;

drop table emp purge *

ERROR at line 1:

ORA-00942: table or view does not exist

SQL> drop table dept purge;

drop table dept purge *

ERROR at line 1:

ORA-00942: table or view does not exist

SQL>

SQL> CREATE TABLE emp (

2 empno NUMBER(7) , 3 ename VARCHAR2(15) NOT NULL, 4 job VARCHAR2(9) , 5 mgr NUMBER(7) , 6 hiredate DATE , 7 sal NUMBER(7) , 8 comm NUMBER(7) , 9 deptno NUMBER(3)

10 );

Table created.

SQL>

SQL> CREATE TABLE dept ( 2 deptno NUMBER(3) , 3 dname VARCHAR2(14), 4 loc VARCHAR2(14), 5 c VARCHAR2(500) 6 );

Table created.

SQL>

SQL> CREATE INDEX emp_index 2 ON emp(deptno) 3 TABLESPACE users

Oracle Internal & Or acle Academy Use Only

4 STORAGE (INITIAL 50K 5 NEXT 50K

6 MINEXTENTS 2 7 MAXEXTENTS 10 8 PCTINCREASE 33);

Index created.

SQL>

SQL> CREATE INDEX dept_index 2 ON dept(deptno) 3 TABLESPACE users 4 STORAGE (INITIAL 50K 5 NEXT 50K

6 MINEXTENTS 2 7 MAXEXTENTS 10 8 PCTINCREASE 33);

Index created.

SQL>

SQL> begin

2 for i in 1..999 loop 3 insert into dept values

(i,'D'||i,'L'||i,dbms_random.string('u',500));

4 end loop;

5 commit;

6 end;

7 /

PL/SQL procedure successfully completed.

SQL>

SQL> begin

2 for i in 1..500000 loop 3 insert into emp values

(i,dbms_random.string('u',15),dbms_random.string('u',9),i,sysdate,i, i,mod(i,999));

4 end loop;

5 commit;

6 end;

7 /

PL/SQL procedure successfully completed.

SQL>

SQL> exec dbms_stats.gather_schema_stats('SH');

PL/SQL procedure successfully completed.

SQL>

53) Execute the nic_query.sql script. What do you observe?

Oracle Internal & Or acle Academy Use Only

a) The script first ensures that the amount of memory available to your session is reduced. Then the script executes a join between the EMP and DEPT tables. The optimizer is able to make use of the index to resolve the join.

SQL> @nic_query SQL> set echo on SQL>

SQL> set timing on

SQL> set autotrace traceonly SQL> set linesize 200

SQL>

SQL> alter session set workarea_size_policy=manual;

Session altered.

Elapsed: 00:00:00.00

SQL> alter session set sort_area_size=50000;

Session altered.

Elapsed: 00:00:00.00

SQL> alter session set hash_area_size=5000;

Session altered.

Elapsed: 00:00:00.00 SQL>

SQL>

SQL> select * from emp,dept where emp.deptno=dept.deptno and emp.deptno > 800;

99000 rows selected.

Elapsed: 00:00:02.88 Execution Plan

--- Plan hash value: 128236434

---

|TempSpc| Cost (%CPU)| Time |

---

| 0 | SELECT STATEMENT | | 19780 | 10M|

| 3449 (1)| 00:00:42 |

|* 1 | HASH JOIN | | 19780 | 10M|

104K| 3449 (1)| 00:00:42 |

| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 199 | 99K|

| 18 (0)| 00:00:01 |

|* 3 | INDEX RANGE SCAN | DEPT_INDEX | 199 | |

| 2 (0)| 00:00:01 |

|* 4 | TABLE ACCESS FULL | EMP | 99198 | 5521K|

| 1207 (2)| 00:00:15 |

---

Oracle Internal & Or acle Academy Use Only

Predicate Information (identified by operation id):

--- 1 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")

3 - access("DEPT"."DEPTNO">800) 4 - filter("EMP"."DEPTNO">800)

Statistics

--- 9 recursive calls

0 db block gets 4365 consistent gets 10146 physical reads 0 redo size

57968984 bytes sent via SQL*Net to client

73009 bytes received via SQL*Net from client 6601 SQL*Net roundtrips to/from client 0 sorts (memory)

0 sorts (disk) 99000 rows processed SQL>

SQL> set autotrace off SQL> set timing off;

SQL>

54) How would you enhance the performance of the previous query? Implement your

solution.

In document 11g SQL Tuning Workshop - Volume 2 (Page 116-123)