Oracle性能优化-4-SQL优化

最新推荐文章于 2024-04-15 19:44:29 发布

香辣素毛肚

最新推荐文章于 2024-04-15 19:44:29 发布

阅读量728

点赞数

分类专栏： oracle 文章标签： oracle plsql SQL自动优化

本文链接：https://blog.csdn.net/qq_34153210/article/details/115457361

版权

oracle 专栏收录该内容

19 篇文章 3 订阅

订阅专栏

前面讲到了索引和提示，指出了优化的工具，而SQL优化的对象，也就是哪些语句需要被优化，这一章将讲明。

4.1查找SQL

4.1.1当前最糟糕的会话

select s.sid,
       m.physical_reads,
       m.cpu,
       m.logical_reads
from   v$sessmetric m,
       v$session    s
where  m.session_id = s.sid
       and m.session_serial_num = s.serial#
       and (m.physical_reads > 100 or m.cpu > 100 or m.logical_reads > 100)
order  by m.physical_reads desc,
          m.cpu            desc,
          m.logical_reads  desc

4.1.2七天内最糟糕的查询

select s.snap_id,
       s.module,
       s.sql_id,
       s.disk_reads_delta,
       s.executions_delta,
       s.disk_reads_delta /
       decode(s.executions_delta, 0, 1, s.executions_delta)
from   dba_hist_sqlstat s
where  s.disk_reads_delta > 10000
order  by s.disk_reads_delta desc;

select *
from   dba_hist_snapshot
where  snap_id = 10006；

  select ds.sql_text
        from   dba_hist_sqltext ds
        where  ds.sql_id = '4rtc18cbwm31v'；

4.1.3最耗时的请求

select f.PROGRAM_SHORT_NAME,
       f.PROGRAM,
       f.actual_start_date,
       f.actual_completion_date,
       (f.actual_completion_date - f.actual_start_date) * 24
  from fnd_conc_req_summary_v f
 where (f.actual_completion_date - f.actual_start_date) * 24 > 1
 and f.PROGRAM_APPLICATION_ID=50202
 order by (f.actual_completion_date - f.actual_start_date) * 24 desc

4.2SQL优化建议

4.2.1赋权

如果不赋权只能在system用户下执行操作了

切换到system用户，执行下列语句

grant administer sql tuning set to apps;
grant administer any sql tuning set to apps;
grant advisor to apps;
grant create any sql profile to apps;
grant alter any sql profile to apps;
grant drop any sql profile to apps;

4.2.2创建任务

DECLARE
  tuning_task_name VARCHAR2(240);
  tuning_sqltext   CLOB;
BEGIN
  tuning_sqltext   := 'SELECT prf.full_name,
       prf.person_id
  FROM per_people_f             prf,
       po_agents                poa,
       per_business_groups_perf pbg,
       per_assignments_f        paf_t
 WHERE prf.person_id = poa.agent_id
   AND TRUNC(SYSDATE) BETWEEN prf.effective_start_date AND prf.effective_end_date
   AND TRUNC(SYSDATE) BETWEEN NVL(poa.start_date_active, SYSDATE) AND NVL(poa.end_date_active, SYSDATE)
   AND prf.business_group_id = pbg.business_group_id
   AND SYSDATE BETWEEN paf_t.effective_start_date AND paf_t.effective_end_date
   AND prf.person_id = paf_t.person_id';
  tuning_task_name := dbms_sqltune.create_tuning_task(sql_text    => tuning_sqltext,
                                                      bind_list   => sql_binds(anydata.convertvarchar2(100)),
                                                      user_name   => 'APPS',
                                                      scope       => 'COMPREHENSIVE',
                                                      time_limit  => 60,
                                                      task_name   => 'task2021040',
                                                      description => 'optimize sql');
END;

4.2.3查询任务

select * from user_advisor_log;

4.2.4执行优化

begin
  dbms_sqltune.execute_tuning_task(task_name=>'task2021040');
end;

4.2.5查看优化是否完成

SELECT uat.task_name,
       uat.status
  FROM user_advisor_tasks uat

4.2.6显示优化报告

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

GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : task2021040
Tuning Task Owner : APPS
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status : COMPLETED
Started at         : 04/06/2021 15:32:50
Completed at       : 04/06/2021 15:32:52

-------------------------------------------------------------------------------
Schema Name: APPS
SQL ID     : 8rt4d4m8jj06h
SQL Text   : SELECT prf.full_name,
                    prf.person_id
               FROM per_people_f             prf,
                    po_agents                poa,
                    per_business_groups_perf pbg,
                    per_assignments_f        paf_t
              WHERE prf.person_id = poa.agent_id
                AND TRUNC(SYSDATE) BETWEEN prf.effective_start_date AND
             prf.effective_end_date
                AND TRUNC(SYSDATE) BETWEEN NVL(poa.start_date_active,
             SYSDATE) AND NVL(poa.end_date_active, SYSDATE)
                AND prf.business_group_id = pbg.business_group_id
                AND SYSDATE BETWEEN paf_t.effective_start_date AND
             paf_t.effective_end_date
                AND prf.person_id = paf_t.person_id

-------------------------------------------------------------------------------
ERRORS SECTION
-------------------------------------------------------------------------------
- ORA-16951: Too many bind variables supplied for this SQL statement.

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

看样子绑定变量是必要的，我们在讲索引价值的使用的sql，传入该优化器，看是否能生效

DECLARE
  tuning_task_name VARCHAR2(240);
  tuning_sqltext   CLOB;
BEGIN
  tuning_sqltext   := 'SELECT *
  FROM mtl_system_items_b msi
 WHERE msi.inventory_item_status_code = :A
  ';
  tuning_task_name := dbms_sqltune.create_tuning_task(sql_text    => tuning_sqltext,
                                                      bind_list   => sql_binds(anydata.convertvarchar2(100)),
                                                      user_name   => 'APPS',
                                                      scope       => 'COMPREHENSIVE',
                                                      time_limit  => 60,
                                                      task_name   => 'task2021042',
                                                      description => 'optimize sql');
END;

经过同样的操作，得到如下结果

GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : task2021042
Tuning Task Owner : APPS
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status : COMPLETED
Started at         : 04/06/2021 15:50:28
Completed at       : 04/06/2021 15:50:30

-------------------------------------------------------------------------------
Schema Name: APPS
SQL ID     : 82nv5hzvns262
SQL Text   : SELECT *
               FROM mtl_system_items_b msi
              WHERE msi.inventory_item_status_code = :A


-------------------------------------------------------------------------------
There are no recommendations to improve the statement.

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

看来这个优化器并不是特别智能，而且测试只能绑定一个变量，什么时候拿到一个复杂SQL，再看看效果。

关于函数“dbms_sqltune.create_tuning_task”的用法，请参考https://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_sqltun.htm#CHDGBCDB

4.3SQL自动优化

4.3.1启动优化

system用户下执行

BEGIN
  dbms_auto_task_admin.enable(client_name => 'sql tuning advisor', -- name of the client, as found inDBA_AUTOTASK_CLIENT View.                              
                              operation   => NULL, -- Name of the operation as specified inDBA_AUTOTASK_OPERATION View                      
                              window_name => NULL);
END;

4.3.2修改参数‘’

查询下列SQL

SELECT DAP.parameter_value
  FROM dba_advisor_parameters dap
 WHERE dap.task_name = 'SYS_AUTO_SQL_TUNING_TASK'
 AND DAP.parameter_name='ACCEPT_SQL_PROFILES'

执行下列方法前遇到权限问题，找了一下如何修改密码

添加ORACLE_HOME = <Oracle installation Directory>环境变量
打开命令提示符
将目录更改为 %ORACLE_HOME%\bin
输入命令 sqlplus /nolog
SQL> connect /as sysdba
SQL> alter user SYS identified by newpassword;

参考：https://stackoverflow.com/questions/740119/default-passwords-of-oracle-11g

执行需要sys用户，而输入sys用户登录时，会报错ORA-01031

oracle数据库只允许本地系统上连接DBA，将任意用户添加到DBA组，则该用户则可以作为SYSDBA连接ORACLE，无需指定用户名/密码

我们使用groups命令查看oracle用户所在的组，关于groups命令参考https://blog.csdn.net/qq_34153210/article/details/107166040

因为oracle用户所在的组含有dba,则可以连接sysdba

可以看到我们成功以sys用户登录

然后执行下列方法，将parameter为'ACCEPT_SQL_PROFILES'的值改为true

我们再次执行上面的查询

成功修改

可是用sqlplus输入命令太麻烦了，如果我们要用PLSQL DEVELOPER登录，就需要密码文件这个东西。

首先确认下面的值为exclusive

创建密码文件

以oracle用户执行下列语句

orapwd file=orapw$ORACLE_SID password=bar entries=20

接着去$ORACLE_HOME/dbs 路径下查看是否产生了orapw($ORACLE_SID).ora文件

这时我们就可以在PLSQL/dev软件上使用sys/bar进入sysdba

同样可以执行下面语句

BEGIN
  dbms_sqltune.set_tuning_task_parameter(task_name => 'SYS_AUTO_SQL_TUNING_TASK',
                                         parameter => 'ACCEPT_SQL_PROFILES',
                                         VALUE     => 'TRUE');
END;

最后我们可以将apps也赋予sysdba权限（正式环境不要这么做）

在sys用户下执行

grant sysdba to apps

这样apps用户也拥有了sysdba权限，比如这样

4.3.3执行任务

sysdba下执行

BEGIN
  dbms_sqltune.execute_tuning_task(task_name => 'SYS_AUTO_SQL_TUNING_TASK');
END;

4.3.3查看优化结果

DECLARE

  p_report CLOB;
  PROCEDURE print_clob_data(p_clob IN CLOB) IS
    l_off INTEGER := 1;
    l_amt INTEGER := 4096;
    l_str VARCHAR2(4096);
  
  BEGIN
    LOOP
      dbms_lob.read(p_clob, l_amt, l_off, l_str); --将p_clob 数据从 l_off位置 取出l_amt长度,放入 l_str 中
      l_off := l_off + l_amt; --移动位置
      dbms_output.put_line(l_str);
    
    END LOOP;
  EXCEPTION
    WHEN OTHERS THEN
      NULL;
  END;
BEGIN
  p_report := dbms_sqltune.report_auto_tuning_task(begin_exec   => NULL,
                                                   end_exec     => NULL,
                                                   TYPE         => dbms_sqltune.type_text,
                                                   LEVEL        => dbms_sqltune.level_typical,
                                                   section      => dbms_sqltune.section_all,
                                                   object_id    => NULL,
                                                   result_limit => NULL);

  print_clob_data(p_report);
END;

查看输出看到了这一段

这不是我们在讲hint的时候写的SQL语句吗

看到这一段，新的执行计划提升了100硬盘读取，99.84%内存读取

告诉我们使用唯一性索引，作为新的执行计划，将会大大提高运行效率

4.3.4应用优化

BEGIN
  dbms_stats.gather_table_stats(ownname          => 'INV',
                                tabname          => 'MTL_SYSTEM_ITEMS_B',
                                estimate_percent => dbms_stats.auto_sample_size,
                                method_opt       => 'FOR ALL COLUMNS SIZE AUTO');
END;

发现full hint被优化器否决了

4.4SAP性能分析器

4.4.1建立测试环境

create table object_tab as  select * from dba_objects;

4.4.2收集统计信息

BEGIN
  dbms_stats.gather_table_stats(ownname => 'APPS', tabname => 'OBJECT_TAB', cascade => TRUE);
END;

4.4.3清空内存共享池

alter system flush shared_pool;

4.4.4执行查询

select count(*) from object_tab ot where ot.object_id=100;
select count(*) from object_tab ot where ot.object_id<100;
select count(*) from object_tab ot where ot.object_id=1000;
select count(*) from object_tab ot where ot.object_id<1000;

4.4.5创建优化集

begin
  dbms_sqltune.create_sqlset(sqlset_name=>'sql_replay_test');
end;

4.4.6加载优化集

DECLARE
  l_cursor dbms_sqltune.sqlset_cursor;
BEGIN
  OPEN l_cursor FOR
    SELECT VALUE(a)
      FROM TABLE(dbms_sqltune.select_cursor_cache(basic_filter   => 'sql_text like''%object_tab%'' and parsing_schema_name=''APPS''',
                                                  attribute_list => 'ALL')) a;
  dbms_sqltune.load_sqlset(sqlset_name => 'sql_replay_test', populate_cursor => l_cursor);

END;

4.4.7查询优化集

select sql_text from dba_sqlset_statements dss where dss.sqlset_name='sql_replay_test';

4.4.8输出优化集

DECLARE
  v_task VARCHAR2(64);
BEGIN
  v_task := dbms_sqlpa.create_analysis_task(sqlset_name => 'sql_replay_test');
  dbms_output.put_line(v_task);

END;

这里v_task返回值是TASK_44948，将会传入下面的分析任务中

4.4.9执行分析任务

BEGIN
  dbms_sqlpa.execute_analysis_task(task_name      => 'TASK_44948',
                                   execution_type => 'test execute',
                                   execution_name => 'before_change');
END;

4.4.10查看分析结果

SELECT dbms_sqlpa.report_analysis_task('TASK_44948')
  FROM dual;

我们看看sql_id=7yxbuat00zf0y对于哪条SQL语句

结果为空，是因为上面的语句是我昨天执行的，只能从历史SQL里去找了

  SELECT to_char(substr(ds.sql_text,1,4000)) sql_text,
              ds.sql_id
         FROM dba_hist_sqltext ds
        WHERE ds.sql_text LIKE '%select count(*) from object_tab%'

对应我们第三行的sql_id

这里我们插入一点，我们测试一下这个分析任务和dbms_xplan的异同，下面记录一下XPLAN的输出结果。

1.执行sql语句

select count(*) from object_tab ot where ot.object_id<101

2.启用XPLAN

SELECT t.*
  FROM v$sql s,
       table(DBMS_XPLAN.DISPLAY_CURSOR(s.sql_id, s.child_number)) t
 WHERE sql_text LIKE '%count(*) from object_tab ot where ot.object_id<101%';

4.4.11增加索引

 create index object_tab_n1 on object_tab(object_id)

4.4.12收集统计信息

 begin
 dbms_stats.gather_table_stats('APPS','OBJECT_TAB',cascade=>true);
 end;

4.4.13执行分析任务

BEGIN
  dbms_sqlpa.execute_analysis_task(task_name      => 'TASK_44948',
                                   execution_type => 'test execute',
                                   execution_name => 'after_change');
END;

4.4.14输出结果

SELECT dbms_sqlpa.report_analysis_task('TASK_44948')
  FROM dual;

大大减少了CPU读取，内存占用

这么看不太直关，我们对比看

4.4.15执行分析任务比较

BEGIN
  dbms_sqlpa.execute_analysis_task(task_name        => 'TASK_44948',
                                   execution_type   => 'compare performance',
                                   execution_params => dbms_advisor.arglist('execution_name1',
                                                                            'before_change',
                                                                            'execution_name2',
                                                                            'after_change'));
END;

4.4.16查看对比输出结果

SELECT dbms_sqlpa.report_analysis_task('TASK_44948')
  FROM dual;

香辣素毛肚

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
Oracle性能优化-4-SQL优化

前面讲到了索引和提示，指出了优化的工具，而SQL优化的对象，也就是哪些语句需要被优化，这一章将讲明。4.1查找SQL4.1.1当前最糟糕的会话select s.sid, m.physical_reads, m.cpu, m.logical_readsfrom v$sessmetric m, v$session swhere m.session_id = s.sid and m.session_serial_n
复制链接

扫一扫