【Oracle中使用sta和hint进行性能优化】

最近工作中遇到一个非常诡异的事情： sql直接执行快，在存过/匿名块/视图里面执行慢。查看语句的执行计划一模一样，通过对SQL的逐步分析，发现是后台实际的SQL_ID不一致导致的。本文用于对记录对于问题的分析步骤，以及用到的一些优化方式。

一：优化思路：

1：确定SQL卡在那个部分。
将sql分解成一个个select，然后通过insert into test select……在直接执行和在declare匿名块中执行的时间进行比较。然后在plsqldev sessions里面定位卡住的sql，不断对语句进行拆解，最终确认慢的不能再拆的sql。
2：确定实际的执行计划
抓取实际的sql_id,sql_child_number，然后比较执行计划，通过hint，让慢的向快的靠拢；或者用sta进行优化。

--查询当前创建session的sid 及SERIAL#
select * From v$mystat
select * from v$session where sid=3968

--1，执行快的sql（多执行几次）
--2，执行慢的sql

--用上面的sid 及SERIAL#
create table gsc_test as
select * from v$active_session_history a where a.SESSION_ID=3968 and a.SESSION_SERIAL#='12485'

--找到快慢sql的sql_id和sql_child_number
select * from gsc_test 
order by 2 desc

--比较执行计划
select * from v$sqlarea where sql_id in ('4nuwpcuxtbmtz','9903hw5j2gyu7')
select * from table(dbms_xplan.display_cursor('4nuwpcuxtbmtz',0,'advanced'))
select * from table(dbms_xplan.display_cursor('9903hw5j2gyu7',0,'advanced'))

二：常规优化

1：查询执行计划，该用的索引是否使用到，执行计划是否合理。在多个表进行关联时关键的字段上需要有索引，而且字段类型需要一致，否者索引不会用的。注意执行的顺序，让更多的数据尽早的过滤掉。但索引并不是万能的，索引字段的值过于分散，如字典类的集中对于性能提升并没有太大的提升。
2：对于数据量很大的表查询，一般子查询的的效率很低，尽可能的合并到from后面。
3：使用with as关键字，这个关键字有个好处，可以吧多个表进行分开查询缓存，然后再一起计算，但是具体效果，得看实际情况。
4：更改缓存策略，将常用的表在CACHE中置顶。注意：只适合查询非常频繁而且数据量不太大的表。

三：sta 优化

SQL优化器(SQL Tuning Advisor STA)是Oracle10g中推出的帮助DBA优化工具，它主要用来优化复杂SQL的执行计划。该工具的使用方法调用dbms_sqltune包来完成。
Oracle 11g dbms_sqltune包官方文档

--举例
--这是需要被优化的SQL语句
select * From gsc_test;
--sql定位，找到实际执行的SQL_ID
select * from v$sqlarea where upper(sql_text) like '%gsc_test%'
--1，创建、执行优化任务
declare
   v_sqlid      varchar2(255) := '5xgrv9dr086jx'; --根据实际情况改一下
   v_taskname   varchar2(255);
begin
    v_taskname := 'query_for_'||v_sqlid;
   v_taskname := dbms_sqltune.create_tuning_task(sql_id     => v_sqlid,
                                                --time_limit  => 60,
                                                task_name   => v_taskname,
                                                description => v_sqlid);
    --执行创建好的优化任务                                          
   dbms_sqltune.execute_tuning_task(task_name => v_taskname);
   dbms_output.put_line('v_taskname='||v_taskname);
end;
/

--2,查看优化建议
select dbms_sqltune.report_tuning_task( 'query_for_5xgrv9dr086jx') from dual;
            
--3，接收优化建议，注意这段语句优化建议生成的，执行代表接受该优化建议
exec  dbms_sqltune.accept_sql_profile(task_name =>'query_for_5xgrv9dr086jx', task_owner => 'IRMQA2', replace =>TRUE, profile_type => DBMS_SQLTUNE.PX_PROFILE);


--4,查看优化的效果(注意，如果是jdbc连接池，那要重启应用才会生效)
select dbms_xplan.display_cursor('5xgrv9dr086jx') from dual

--确认某个sql是否在用profile
declare
   v_signature number;
   v_sql varchar2(4000) := '5xgrv9dr086jx';--sql 从v$sqlarea查询出来，去掉最后的空格
begin
   v_signature := dbms_sqltune.sqltext_to_signature(sql_text    => v_sql,
                                                    force_match => false);
   if v_signature is null then
      dbms_output.put_line('profile没有这个sql语句');
   else
      dbms_output.put_line('profile存在该语句');
   end if;
end;
/
  

--5,profile查看及删除
select * from user_advisor_log
select * from user_advisor_objects
select * from user_advisor_tasks

select * 
 from dba_sql_profiles 
where name='SYS_SQLPROF_01773daae3f00002'
   or upper(sql_text) like '%X009%'

begin
  dbms_sqltune.drop_sql_profile(name => 'SYS_SQLPROF_01773daae3f00002');
end;
/

四：hint优化

parallel

parallel用于为sql开启并行模式，类似java中的多线程。parallel对于大表的查询有显著的提升作用，但使用前注意查看CPU的负载情况。Oracle中有一个参数cpu_count，这个参数控制了数据库可以使用的CPU数量。还有一些其他的参数，可以在命令下面输入show parameter parallel命令进行查看。parallel_max_servers参数控制了最大并行数。

--语法：
/*+parallel(表名,并行数)*/
并行数一般需要小于cpu数量-1。
--实例
select /*+parallel(t1,6)*/ count(*) from gsc_test t;

full

full用于强制进行全表扫描，对于非常复杂的多表查询，一些数据量并不是很大的表使用全表扫描会有奇效，反而会减少实际开销。

/*+full(表名) full(表名) full(表名)*/
--实例
select /*+full(t1)*/ count(*) from gsc_test ;

rule

rule代表使用RBO优化器模式，这是一种Oracle已经废弃和不再支持的一个优化器模式，现在95%以上的系统都采用CBO 而不再采用RULE 的RBO。但是对于一些Oracle的内置视图查询会有奇效。

/*+ RULE */
--实例
select /*+ RULE */ count(*) from gsc_test ;

以上列举的的三个hint关键字是我常用的一些关键字，更多hint可以参考这一篇文章。ORACLE的HINT详解