ORALCE优化

1、PL/SQL执行Explain

 Plan Hash Value  : 1703851322 
----------------------------------------------------------------------------
| Id  | Operation            | Name       | Rows | Bytes | Cost | Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |            |  715 | 92950 | 4498 | 00:00:54 |
| * 1 |   HASH JOIN          |            |  715 | 92950 | 4498 | 00:00:54 |
| * 2 |    TABLE ACCESS FULL | SMALLTABLE |    4 |   260 |  104 | 00:00:02 |
| * 3 |    TABLE ACCESS FULL | BIGTABLE   |  176 | 11440 | 4394 | 00:00:53 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 1 - access("A"."ID"="B"."ID")
* 2 - filter("B"."ID"=40000)
* 3 - filter("A"."ID"=40000)

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

2、SQL PLUS（物理读）

执行set autotrace traceonly;然后输入语句

          0  recursive calls
          0  db block gets
      25065  consistent gets
        792  physical reads
     649988  redo size
        740  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

3、SQL Tuning Advisor

SQL> create user bamboo identified by bamboo;
User created.
SQL> grant connect,resource to bamboo;
Grant succeeded.
SQL> grant advisor to bamboo;
Grant succeeded.

SQL> create table bigtable (id number(10),name varchar2(100));
Table created.

SQL> begin
  2  for i in 1..5000000 loop
  3  insert into bigtable values(i,'test'||i);
  4  end loop;
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> commti;

SQL> create table smalltable (id number(10),name varchar2(100));
Table created.

SQL> begin
  2  for i in 1..100000 loop
  3  insert into smalltable values(i,'test'||i);
  4  end loop;
  5  end;
  6  /

PL/SQL procedure successfully completed.
SQL> commit;

3.然后对bigtable和smalltable做一个等连接查询，然后跟踪其执行计划
SQL> select a.id,a.name,b.id,b.name from bigtable a,smalltable b where a.id=b.id and a.id>1;

        ID NAME                                             ID NAME
---------- ---------------------------------------- ---------- ----------------------------------------
.................................................
...............................................

Execution Plan
----------------------------------------------------------
Plan hash value: 1703851322

---------------------------------------------------------------------------------
| Id  | Operation          | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |            |   839 |   106K|  3656   (5)| 00:00:44 |
|*  1 |  HASH JOIN         |            |   839 |   106K|  3656   (5)| 00:00:44 |
|*  2 |   TABLE ACCESS FULL| SMALLTABLE |     5 |   325 |    71   (3)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| BIGTABLE   |   173 | 11245 |  3584   (5)| 00:00:44 |
---------------------------------------------------------------------------------

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

   1 - access("A"."ID"="B"."ID")
   2 - filter("B"."ID"=40000)
   3 - filter("A"."ID"=40000)


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


Statistics
----------------------------------------------------------
          9  recursive calls
          0  db block gets
      16151  consistent gets
      11469  physical reads
          0  redo size
        588  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          1  rows processed
熟悉执行计划的就可以看出，这个sql执行是很慢的，2个表都做的是全表扫描，并且其物理读是11469，按照优化的经验，给2个表的id创建索引，减少查询时候的物理读,下面我们就看看通过优化器，oracle能我们什么样的建议呢？

4.下面就通过在sql/plus里DBMS_SQLTUNE包的CREATE_TUNING_TASK来创建一个优化任务，然后通过DBMS_SQLTUNE.EXECUTE_TUNING_TASK来执行调优任务，生成调优建议


DECLARE
my_task_name VARCHAR2(30);
my_sqltext CLOB;
BEGIN
my_sqltext := 'select a.id,a.name,b.id,b.name from bigtable a,smalltable b where a.id=b.id and a.id>1';
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text => my_sqltext,
user_name => 'WMS',
scope => 'COMPREHENSIVE',
time_limit => 60,
task_name => 'tuning_sql_test_2',
description => 'Task to tune a query on a specified table');
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'tuning_sql_test_2');
END;
/
5、执行任务
exec dbms_sqltune.execute_tuning_task('tuning_sql_test_2');

6、查询运行状态是否完成

SELECT task_name,status FROM USER_ADVISOR_TASKS WHERE task_name='tuning_sql_test_2';

7、查看优化结果(PL/SQL)然后点开那个点点查看
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'tuning_sql_test_2') from DUAL;

8、删除优化任务的命令是

exec dbms_sqltune.drop_tuning_task('tuning_sql_test');
http://blog.csdn.net/zq9017197/article/details/16857083

http://www.cnblogs.com/einyboy/archive/2012/08/14/2637659.html