sql优化

sql优化

==================================================================

1.查找索引

SELECT m.table_owner,m.index_name,n.COLUMN_POSITION,n.COLUMN_NAME,m.distinct_keys,m.num_rows from dba_indexes m,dba_ind_columns n 
where m.index_name = n.INDEX_NAME 

AND m.table_name='xxxx';

查看索引选择度

查看表大小

SELECT BYTES/1024/1024/1024 FROM dba_segments WHERE segment_name='xxxx'

 

2.输出监控信息：
SELECT DBMS_SQLTUNE.report_sql_monitor(sql_id => 'xxx', type => 'html')
 AS report FROM dual;

3.查看运行时间：
 select * from v$session_longops t where username = 'DEVMGR' order by start_time desc
 
4.输出执行计划
 EXPLAIN PLAN FOR <sql语句>
 SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());

5.
SELECT * FROM ALL_TAB_COL_STATISTICS T WHERE t.table_name = 'xxx';
 
6.sql优化方法
SQL 写多了，有时除了看执行计划外，习惯扫描下整个SQL，看语句本身是否写得有问题，
 如标量子查询、自定义函数、无谓的重复子查询、是否有能迅速过滤变少的结果集、
 列上是否适合用索引等，有时这种方法真的很有效。
 

 7.根据sql_id查看执行计划

SELECT DBMS_SQLTUNE.report_sql_monitor(sql_id => 'xxx', type => 'html')

 AS report FROM dual;

select * from table(dbms_xplan.display_cursor('g1a6hqyarsy0a'));

 

8.查看执行计划是否有大表驱动小表

9.执行计划是否改变

SELECT  *
  FROM DBA_HIST_SQL_PLAN
 WHERE SQL_ID = 'xxx'

 ORDER BY TIMESTAMP DESC;

 

10. 如果v$sql没有 

 SELECT sql_id,COUNT(*) FROM v$active_session_history  WHERE session_id = '2511' ;

 

11.查看历史sql执行消耗
select sql_text,
       sql_id,
       s.PLAN_HASH_VALUE,
       s.ELAPSED_TIME / s.EXECUTIONS / 1000000,
       s.CPU_TIME / s.EXECUTIONS / 1000000,
       s.BUFFER_GETS / s.EXECUTIONS
  from v$sql s

where sql_id = 'xxx';

select s.snap_id,
       p.end_interval_time,
       s.sql_id,
       s.plan_hash_value,
       s.executions_delta,
       s.buffer_gets_delta / s.executions_delta,
       s.elapsed_time_delta / s.executions_delta / 1000000,
       s.cpu_time_delta / s.executions_delta / 1000000,
       s.disk_reads_delta / s.executions_delta
  from dba_hist_sqlstat s, dba_hist_snapshot p
where s.snap_id = p.snap_id
   and s.sql_id = '5vwruagnc5jgz'
order by 1

 

12.查看绑定变量

 SELECT SQL_ID, NAME, DATATYPE_STRING, LAST_CAPTURED, VALUE_STRING
   FROM V$SQL_BIND_CAPTURE
  WHERE SQL_ID = 'xxxx'
  ORDER BY LAST_CAPTURED, POSITION;

 

kill session

================================================================

1. 杀会话，ALTER SYSTEM KILL SESSION '2511,34497';
    标记为killed

 

2. 查看paddr

select saddr,sid,serial#,paddr,username,status from v$session WHERE sid=2511;

3.根据paddr，查看spid

select addr, pid, spid, username from v$process where addr='000000012565EAE0'

4.后台kill

 kill $SPID