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;