oracle中sql调优

1： 执行DBMS_SQLTUNE包进行sql优化需要有advisor的权限

    grant advisor to weisi;

2：模拟环境

      2.1) 创建大表

              create table big_t_sta as select * from sys.dba_objects;

              insert into big_t_sta select * from big_t_sta;

              insert into big_t_sta select * from big_t_sta;

              insert into big_t_sta select * from big_t_sta;

              insert into big_t_sta select * from big_t_sta;

      2.2) 创建小表

              create table small_t_sta as select * from sys.dba_objects;

3：big_t_stat和small_t_sta做一个等连接查询，跟踪其执行计划。

    cat $ORACLE_HOME/sqlplus/admin/plustrce.sql

                sqlplus / as sysdba

                @$ORACLE_HOME/sqlplus/admin/plustrce.sql

                grant plustrace to weisi;

    set autotrace traceonly

    select a.object_id,a.object_name,b.object_id,b.object_name from big_t_sta a,small_t_sta b where a.object_id=b.object_id and a.object_id=400;

4：通过DBMS_SQLTUNE包的CREATE_TUNING_TASK来创建一个优化任务。

        4.1）创建STA任务存储过程

        create or replace procedure sql_tuning(my_sqltext in clob,schema_name in varchar2) is

        my_task_name varchar2(30);

        begin

        my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(

                  sql_text => my_sqltext,

                  user_name => schema_name,  -- 该语句通过哪个用户执行。注意是大写，不然会报错，用户无效

                  scope    => 'COMPREHENSIVE', --优化范围（limited或comprehensive）

                  time_limit => 60,            --优化过程的时间限制

                  task_name  => 'oracle_weisi_task', --优化任务名称，自己拟定

                  description => 'Task to tune a query on a specified table');--优化任务描述，自己拟定

          DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'oracle_weisi_task');               

        end;

          /

        4.2）执行SQL调优

        exec sql_tuning('select a.object_id,a.object_name,b.object_id,b.object_name from big_t_sta a,small_t_sta b  where a.object_id=b.object_id and a.object_id=400','SYS');

        4.3）查看任务进度

            select task_name,advisor_name,status  from user_advisor_tasks where task_name='oracle_weisi_task';

        4.4)  删除任务

                exec dbms_sqltune.drop_tuning_task('oracle_weisi_task');   

5：查看优化结果

select dbms_sqltune.report_tuning_task('oracle_weisi_task') from dual;

6: SQL trace

      会话级

        alter session set sql_trace=true;

        alter session set events '10046 trace name context forever,level 12';

        exec sys.dbms_system.set_sql_trace_in_session(10 , 39196 , true);

        alter session set sql_trace=false;

        alter session set events '10046 trace name context off';

        exec sys.dbms_system.set_sql_trace_in_session(10 , 39196 , false);

      实例级(谨慎使用)

        alter system set sql_trace=true;

        alter system set sql_trace=false;

    6.1) 检查并跟踪数据库进程

          select sid,serial#,username from v$session;

          select * from v$session where username='WZX'

          Select c.spid from v$process c where c.addr=(select b.paddr from v$session b where b.sid='20' and b.serial#='7');

    6.2) 启用SQL TRACE

        exec dbms_system.set_sql_trace_in_session(146,29,true)

        exec dbms_system.set_sql_trace_in_session(sid,serial#,true)

        exec dbms_system.set_sql_trace_in_session(33,383,false);

    6.3）关闭SQL TRACE

        exec dbms_system.set_sql_trace_in_session(20,7,false)

        exec dbms_system.set_sql_trace_in_session(sid,serial#,false)

    6.4) 实例

        sqlplus weisi/weisi@weisi

        create table sql_trace_t as select * from dba_objects;

        create index object_id_index on sql_trace_t(object_id);

        sqlplus / as sysdba

        exec dbms_system.set_sql_trace_in_session(20,7,true);

        sqlplus weisi/weisi@weisi

        select * from sql_trace_t where object_id< 10;

        sqlplus / as sysdba

        exec dbms_system.set_sql_trace_in_session(33,383,false)

        show parameter dump

        ORCL_ora_4678.trc

sql trace

  6.5 ) 利用tkprof生成易读文件

          tkprof tracefile outputfile [explain= ] [table= ] [print= ] [insert= ] [sys= ] [sort= ]

        tkprof /opt/oracle/diag/rdbms/orcl/ORCL/trace/ORCL_ora_4678.trc /opt/soft/sql_trace.trc explain=weisi/weisi@weisisys=no

          /opt/oracle/diag/rdbms/orcl/ORCL/trace

          tkprof /opt/oracle/diag/rdbms/orcl/ORCL/trace/ORCL_ora_6026.trc /opt/soft/sql_trace_20160416.trc explain=weisi/weisi@weisi  sys=no

TKPROF的使用

tkprof

语法

$ tkprof

Usage: tkprof tracefile outputfile [explain= ] [table= ] [print= ] [insert= ] [sys= ] [sort= ]

table=schema.tablename Use 'schema.tablename' with 'explain=' option.

explain=user/password Connect to ORACLE and issue EXPLAIN PLAN.

print=integer List only the first 'integer' SQL statements.

aggregate=yes|no

insert=filename List SQL statements and data inside INSERT statements.

sys=no TKPROF does not list SQL statements run as user SYS.

record=filename Record non-recursive statements found in the trace file.

waits=yes|no Record summary for any wait events found in the trace file.

sort=option Set of zero or more of the following sort options:

prscnt number of times parse was called

prscpu cpu time parsing

prsela elapsed time parsing

prsdsk number of disk reads during parse

prsqry number of buffers for consistent read during parse

prscu number of buffers for current read during parse

prsmis number of misses in library cache during parse

execnt number of execute was called

execpu cpu time spent executing

exeela elapsed time executing

exedsk number of disk reads during execute

exeqry number of buffers for consistent read during execute

execu number of buffers for current read during execute

exerow number of rows processed during execute

exemis number of library cache misses during execute

fchcnt number of times fetch was called

fchcpu cpu time spent fetching

fchela elapsed time fetching

fchdsk number of disk reads during fetch

fchqry number of buffers for consistent read during fetch

fchcu number of buffers for current read during fetch

fchrow number of rows fetched

userid userid of user that parsed the cursor

这样可以减少分析出来的文件的复杂度，便于查看。

参数说明

tracefile：你要分析的trace文件

outputfile：格式化后的文件

explain=user/password@connectstring

table=schema.tablename

PRINT:只列出输出文件的最初N个SQL语句。默认为所有的SQL语句。

AGGREGATE:如果= NO，则不对多个相同的SQL进行汇总。如果为yes则对trace文件中的相同sql进行合并。

INSERT:SQL 语句的一种，用于将跟踪文件的统计信息存储到数据库中。在TKPROF创建脚本后，在将结果输入到数据库中。

SYS:如果设置为yes，则所有sys用户的操作(也包含用户sql语句引发的递归sql)，这样可以减少分析出来的文件的复杂度，便于查看。

TABLE:在输出到输出文件前，用于存放临时表的用户名和表名。

EXPLAIN:对每条SQL 语句确定其执行规划，并将执行规划写到输出文件中。如果不是有explain，在trace文件中我们看到的是SQL实际的执行路径，不会有sql的执行计划

sort:对trace文件的sql语句根据需要排序，其中比较有用的一个排序选项是fchela，即按照elapsed time fetching来对分析的结果排序（记住要设置初始化参数timed_statistics=true），生成的文件将把最消耗时间的sql放在最前面显示。

Tkprof命令输出的解释

首先解释输出文件中列的含义：

CALL：每次SQL语句的处理都分成三个部分

Parse：这步将SQL语句转换成执行计划，包括检查是否有正确的授权和所需要用到的表、列以及其他引用到的对象是否存在。

Execute：这步是真正的由Oracle来执行语句。对于insert、update、delete操作，这步会修改数据，对于select操作，这步就只是确定选择的记录。

Fetch：返回查询语句中所获得的记录，这步只有select语句会被执行。

COUNT:这个语句被parse、execute、fetch的次数。

CPU：这个语句对于所有的parse、execute、fetch所消耗的cpu的时间，以秒为单位。

ELAPSED：这个语句所有消耗在parse、execute、fetch的总的时间。

DISK：从磁盘上的数据文件中物理读取的块的数量。

QUERY：在一致性读模式下，所有parse、execute、fetch所获得的buffer的数量。一致性模式的buffer是用于给一个长时间运行的事务提供一个一致性读的快照，缓存实际上在头部存储了状态。

CURRENT：在current模式下所获得的buffer的数量。一般在current模式下执行insert、update、delete操作都会获取buffer。在current模式下如果在高速缓存区发现有新的缓存足够给当前的事务使用，则这些buffer都会被读入了缓存区中。

ROWS: 所有SQL语句返回的记录数目，但是不包括子查询中返回的记录数目。对于select语句，返回记录是在fetch这步，对于insert、update、delete操作，返回记录则是在execute这步。

trace文件中的性能分析

1、如果分析数与执行数之比为1，说明每次执行这个查询都要进行sql解析。如果分析数与执行数之比接近0，则意味着查询执行了很多次软解析，降低了系统的可伸缩性。

2、如果trace文件中显示对所有或者几乎所有的sql都执行一次，那有可能是因为没有正确使用绑定变量。

3、如果一个(Fetch Count)/所获得行数的比值接近1，且行数大于1，则应用程序不执行大批量取数操作，每种语言/API都有能力完成这个功能，即一次取多行。如果没有利用这个功能进行批量去，将有可能花费多得多的时间在客户端与服务器端之间来回往返。这个过多的来回转换出了产生很拥挤的网络状况之外，也会比一次调用获得很多行要慢得多，如何指示应用程序进行批量获取将随语言/API而定。

4、如果CPU时间与elasped时间有巨大差异，意味着有可能花了大量时间在等待某些事情上。如果花了一个CPU时间来执行，但它却总共花了10秒的时间，这就意味着90%的运行时间在等待一个资源。例如被一个会话等待，或者大量查询时的物理IO等待等

5、较长的CPU或经过时间往往是最消耗资源的sql，需要我们关注

6、可以通过磁盘IO所占逻辑IO的比例，disk/query+current来判断磁盘IO的情况，太大的话有可能是db_buffer_size过小，当然这也跟SQL的具体特性有关

7、query+current/rows 平均每行所需的block数，太大的话（超过20）SQL语句效率太低，数据过于分散，可以考虑重组对象