• oracle优化SQL基础步骤


    ---优化SQL  23qax02dyq3t7
    1.查询SQL内容
    select * from v$sql where sql_id='23qax02dyq3t7';
    2.查询执行SQL的客户端
    select q.SAMPLE_TIME,q.SQL_EXEC_START, q.MODULE,q.MACHINE,q.PROGRAM from v$active_session_history q where q.SQL_ID='23qax02dyq3t7';
    select a.USERNAME,a.SCHEMANAME,a.MACHINE,a.PROGRAM,a.MODULE,a.CLIENT_INFO from v$session a where a.sql_id='&1';

    3.查看真实执行计划

    --SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&1',&2, 'ADVANCED -PROJECTION'));

    --第一个变量sql_id;第二个变量 一般是0

    --查询SQL的实际执行计划

    set pagesize 1000 line 1000
    explain plan for
    SELECT * from test where id='10';

    --执行计划
    select * from table(dbms_xplan.display);

    4.查询某个表可以创建索引的列
    select a.column_name,
    b.num_rows,
    a.num_distinct Cardinlity,
    round(a.num_distinct / b.num_rows * 100, 2) selectitity,
    a.histogram,
    a.num_buckets
    from dba_tab_col_statistics a, dba_tables b
    where a.owner = b.owner
    and a.table_name = b.table_name
    and a.owner = '&owner'
    and a.table_name = '&table_name';

    5.查询已经存在的索引
    select distinct table_name,index_name,column_name,column_position,created from dba_ind_columns,dba_objects where table_name=upper('&1') and object_name = index_name order by 2,4;

    6.查询表大小
    select sum(bytes/1024/1024/1024) from dba_segments where owner='&owner' and segment_name='&segment_name';

    7.查询绑定变量
    SELECT b.name,b.position,b.datatype_string,b.value_string FROM v$sql_bind_capture b,v$sqlarea a WHERE b.hash_value = a.hash_value AND b.sql_id = a.sql_id and a.sql_id = '&1';

    8.联系开发给出优化建议,或者开发联系业务是否可以下线该SQL

    例如:
    CREATE INDEX SS.MNO_CD_TE ON SS.T_MS_CD_BD (CD_UUID,CE_TE desc) TABLESPACE SS_IDX ONLINE;

    9.改SQL近期执行次数
    select a.snap_id,a.sql_id,a.instance_number,b.BEGIN_INTERVAL_TIME,b.END_INTERVAL_TIME ,a.EXECUTIONS_DELTA,a.plan_hash_value,a.CPU_TIME_DELTA
    from wrh$_sqlstat a, wrm$_snapshot b where a.snap_id = b.snap_id and a.instance_number = b.instance_number and a.sql_id = '&sql_id' order by 4, 1, 3;

    10.查询执行计划是否改变
    select a.snap_id,
    a.sql_id,
    a.instance_number,
    b.BEGIN_INTERVAL_TIME,
    b.END_INTERVAL_TIME,
    a.EXECUTIONS_TOTAL,
    a.EXECUTIONS_DELTA,
    a.plan_hash_value,
    a.CPU_TIME_DELTA
    from wrh$_sqlstat a, wrm$_snapshot b
    where a.snap_id = b.snap_id
    and a.instance_number = b.instance_number
    and a.sql_id = '&sql_id'
    order by 4, 1, 3;

  • 相关阅读:
    Java MD5机密算法的使用
    JavaWeb学习总结-12 JSTL标签语言
    HTML5学习总结-09 拖放和手机触屏事件
    HTML5学习总结-08 应用缓存(Application Cache)
    HTML5学习总结-08 WebSocket 服务器推送
    软件架构阅读笔记06
    软件架构阅读笔记05
    软件架构阅读笔记04
    软件架构阅读笔记03
    软件架构阅读笔记02
  • 原文地址:https://www.cnblogs.com/ss-33/p/12882498.html
Copyright © 2020-2023  润新知