查看SQL的执行情况

#根据登陆时间对会话排序
select * from gv$session where sql_id=‘c1w3aq3c1pc8v’ order by logon_time;

select distinct SESSION_ID,sql_id from GV$ACTIVE_SESSION_HISTORY
where USER_ID=127 and sample_time> to_date(‘20190110 00:00:00’,‘yyyymmdd hh24:mi:ss’) order by session_id;

#查看SQL的执行信息
select FIRST_LOAD_TIME,PARSING_SCHEMA_NAME,MODULE,CPU_TIME/1000000,ELAPSED_TIME/1000000,disk_reads,buffer_gets,LAST_LOAD_TIME,LAST_ACTIVE_TIME
from gv$sqlarea where sql_id=‘1xpqdryf7pw04’;

#查看当前正在执行的SQL信息
select sql_id,FIRST_LOAD_TIME,PARSING_SCHEMA_NAME,MODULE,CPU_TIME/1000000,ELAPSED_TIME/1000000,LAST_LOAD_TIME,LAST_ACTIVE_TIME
from gv$sqlarea where EXECUTIONS=0 order by ELAPSED_TIME desc,LAST_ACTIVE_TIME desc;

#查看历史SQL的执行信息
select sql_id,FIRST_LOAD_TIME,PARSING_SCHEMA_NAME,DISK_READS/EXECUTIONS,MODULE,CPU_TIME/EXECUTIONS/1000000,ELAPSED_TIME/EXECUTIONS/1000000,EXECUTIONS,LAST_LOAD_TIME,LAST_ACTIVE_TIME
from gv$sqlarea
where EXECUTIONS>0 and PARSING_SCHEMA_NAME not in (‘OGG’,‘SYS’)
and DISK_READS/EXECUTIONS>10000
and LAST_ACTIVE_TIME>to_date(‘20190113 00:00:00’,‘yyyymmdd hh24:mi:ss’)
order by FIRST_LOAD_TIME desc,DISK_READS/EXECUTIONS desc;

查看sql的执行计划
方法1:根据sql_id查询sql的执行计划
set lines 2000
set pages 2000
select * from table(dbms_xplan.display_cursor(‘fp3p7b9kk02xg’,‘0’)) ;

方法2:根据sql语句查询执行计划
set autotrace trace
set lines 2000
set pages 2000
select count(*) from dba_objects;