查看sql的文本
set line 300
col sql_text for a200
select * from dba_hist_sqltext where sql_id='dxxxh350mfyvj';
DBID SQL_ID SQL_TEXT COMMAND_TYPE
---------- ------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------
280856689 dxxxh350mfyvj SELECT 'X' FROM tableA WHERE (RCARD, MOCODE) IN (SELECT RCARD, MOCODE FROM (SELECT /*+index(PK_tableA)*/ RCARD, MOCODE FROM tableA WHERE RCARD = :B2 AND ORGID = :B1 ORDER BY MDATE 3
DESC, MTIME DESC) WHERE ROWNUM = 1) AND ORGID = :B1 FOR UPDATE
已选择 1 行。
查看sql的历史执行计划有几个
select distinct SQL_ID,PLAN_HASH_VALUE,to_char(TIMESTAMP,'yyyymmdd hh24:mi:ss') TIMESTAMP
from dba_hist_sql_plan
where SQL_ID='dxxxh350mfyvj' order by TIMESTAMP;
SQL_ID PLAN_HASH_VALUE TIMESTAMP
------------- --------------- -----------------
dxxxh350mfyvj 3728660352 20220218 22:10:58
已选择 1 行。
select distinct SQL_ID,PLAN_HASH_VALUE,to_char(TIMESTAMP,'yyyymmdd hh24:mi:ss') TIMESTAMP
from v$sql_plan
where SQL_ID='2usurkuv91kq5' order by TIMESTAMP;
根据sql_id和plan_hash，查看sql的执行计划
set line 300
col options for a25
col operation for a30
col object_name for a30
select plan_hash_value,id,operation,options,object_name,depth,cost,to_char(TIMESTAMP,'yyyymmdd hh24:mi:ss')
from DBA_HIST_SQL_PLAN
where sql_id ='dxxxh350mfyvj'
and plan_hash_value in (3728660352)
order by plan_hash_value,ID,TIMESTAMP;
PLAN_HASH_VALUE ID OPERATION OPTIONS OBJECT_NAME DEPTH COST TO_CHAR(TIMESTAMP,'YYYYMMDDHH24:MI:SS')
--------------- ---------- ------------------------------ ------------------------- ------------------------------ ---------- ---------- ---------------------------------------
3728660352 0 SELECT STATEMENT 0 8 20220218 22:10:58
3728660352 1 FOR UPDATE 1 20220218 22:10:58
3728660352 2 BUFFER SORT 2 20220218 22:10:58
3728660352 3 NESTED LOOPS 3 8 20220218 22:10:58
3728660352 4 VIEW VW_NSO_1 4 6 20220218 22:10:58
3728660352 5 SORT UNIQUE 5 20220218 22:10:58
3728660352 6 COUNT STOPKEY 6 20220218 22:10:58
3728660352 7 VIEW 7 6 20220218 22:10:58
3728660352 8 SORT ORDER BY STOPKEY 8 6 20220218 22:10:58
3728660352 9 PARTITION HASH SINGLE 9 5 20220218 22:10:58
3728660352 10 TABLE ACCESS BY GLOBAL INDEX ROWID tableA 10 5 20220218 22:10:58
3728660352 11 INDEX RANGE SCAN PK_tableA_NEW 11 4 20220218 22:10:58
3728660352 12 PARTITION HASH ITERATOR 4 2 20220218 22:10:58
3728660352 13 INDEX UNIQUE SCAN PK_tableA_NEW 5 2 20220218 22:10:58
已选择 14 行。
查看sql执行历史的统计信息(平均执行时间，cpu,io,get等)
COL inst FOR 9
SET LINESIZE 340
SET PAGESIZE 10000
COL plan_value FOR 9999999999
COL module FOR a35
COL get_per FOR 99999999
COL read_per FOR 99999999
COL rows_per FOR 99999999
COL elas_per FOR 9999999999
COL cpu_per FOR 99999999
COL IO_per FOR 99999999
COL clu_per FOR 99999999
COL app_per FOR 99999999
COL concur_per FOR 99999999
SELECT *
FROM ( SELECT TO_CHAR (begin_interval_time, 'yyyy-mm-dd hh24:mi:ss')
begin_time,
TO_CHAR (end_interval_time, 'yyyy-mm-dd hh24:mi:ss')
end_time,
a.instance_number inst,
a.module,
plan_hash_value plan_value,
a.sql_id,
EXECUTIONS_DELTA exec,
ROUND (a.ELAPSED_TIME_DELTA / 1000, 2) total_elastime_ms,
ROUND (A.CPU_TIME_DELTA / 1000, 2) total_cputime_ms,
ROUND (a.IOWAIT_DELTA / 1000, 2) total_iotime_ms,
A.BUFFER_GETS_DELTA total_buffer_get,
a.DISK_READS_DELTA total_disk_read,
DECODE (EXECUTIONS_DELTA,
0, buffer_gets_deltA,
ROUND (BUFFER_GETS_DELTA / EXECUTIONS_DELTA))
get_per,
DECODE (EXECUTIONS_DELTA,
0, DISK_READS_DELTA,
ROUND (DISK_READS_DELTA / EXECUTIONS_DELTA))
read_per,
DECODE (EXECUTIONS_DELTA,
0, ROWS_PROCESSED_DELTA,
ROUND (ROWS_PROCESSED_DELTA / EXECUTIONS_DELTA))
rows_per,
DECODE (
EXECUTIONS_DELTA,
0, ELAPSED_TIME_DELTA,
ROUND (ELAPSED_TIME_DELTA / EXECUTIONS_DELTA / 1000, 2))
elas_per_ms,
DECODE (EXECUTIONS_DELTA,
0, CPU_TIME_DELTA,
ROUND (CPU_TIME_DELTA / EXECUTIONS_DELTA / 1000, 2))
cpu_per_ms,
DECODE (EXECUTIONS_DELTA,
0, IOWAIT_DELTA,
ROUND (IOWAIT_DELTA / EXECUTIONS_DELTA / 1000, 2))
IO_per_ms,
DECODE (EXECUTIONS_DELTA,
0, CLWAIT_DELTA,
ROUND (CLWAIT_DELTA / EXECUTIONS_DELTA / 1000, 2))
clu_per_ms,
DECODE (EXECUTIONS_DELTA,
0, APWAIT_DELTA,
ROUND (APWAIT_DELTA / EXECUTIONS_DELTA / 1000, 2))
app_per_ms,
DECODE (EXECUTIONS_DELTA,
0, CCWAIT_DELTA,
ROUND (CCWAIT_DELTA / EXECUTIONS_DELTA / 1000, 2))
concur_per_ms,
DECODE (EXECUTIONS_DELTA,
0, LOADS_DELTA,
ROUND (LOADS_DELTA / EXECUTIONS_DELTA, 2))
per_loads
FROM dba_hist_sqlstat a, DBA_HIST_SNAPSHOT b
WHERE a.snap_id = b.snap_id
AND a.instance_number = b.instance_number
AND a.sql_id = 'dxxxh350mfyvj'
ORDER BY 1 DESC)
WHERE ROWNUM < 100;
BEGIN_TIME END_TIME INST MODULE PLAN_VALUE SQL_ID EXEC TOTAL_ELASTIME_MS TOTAL_CPUTIME_MS TOTAL_IOTIME_MS TOTAL_BUFFER_GET TOTAL_DISK_READ GET_PER READ_PER ROWS_PER ELAS_PER_MS CPU_PER_MS IO_PER_MS CLU_PER_MS APP_PER_MS CONCUR_PER_MS PER_LOADS
------------------- ------------------- ---- ----------------------------------- ----------- ------------- ---------- ----------------- ---------------- --------------- ---------------- --------------- --------- --------- --------- ----------- ---------- ---------- ---------- ---------- ------------- ----------
2022-04-16 15:30:42 2022-04-16 16:02:56 1 vivoTestStand20211107154927.exe 3728660352 dxxxh350mfyvj 154010 81884356.4 12639.58 619.62 1720577 280 11 0 1 531.68 .08 0 0 .13 456.95 0
2022-04-14 14:00:33 2022-04-14 14:30:41 1 0 dxxxh350mfyvj 21918.64 9919.31
2022-04-14 13:30:24 2022-04-14 14:00:33 1 0 dxxxh350mfyvj 19395.85 8990.93
2022-04-14 13:00:15 2022-04-14 13:30:24 1 0 dxxxh350mfyvj 15279 7269.61
2022-04-14 12:30:08 2022-04-14 13:00:15 1 0 dxxxh350mfyvj 12710.42 6711.23
2022-04-14 12:00:01 2022-04-14 12:30:08 1 0 dxxxh350mfyvj 14246.66 7469.19
2022-04-14 11:30:54 2022-04-14 12:00:01 1 0 dxxxh350mfyvj 17609.83 8973.17
2022-04-14 11:00:46 2022-04-14 11:30:54 1 0 dxxxh350mfyvj 19390.37 9988.51
2022-04-14 10:30:38 2022-04-14 11:00:46 1 0 dxxxh350mfyvj 20320.79 10569.63
2022-04-14 10:00:30 2022-04-14 10:30:38 1 0 dxxxh350mfyvj 21101.4 10743.94
查询该sql的历史执行情况
col snap_id for 99999999
col date_time for a30
col plan_hash for 9999999999
col executions for 99999999
col avg_etime_s heading 'etime/exec' for 9999999.99
col avg_lio heading 'buffer/exec' for 99999999999
col avg_pio heading 'diskread/exec' for 99999999999
col avg_cputime_s heading 'cputim/exec' for 9999999.99
col avg_row heading 'rows/exec' for 9999999
select * from(
select distinct
s.snap_id,
to_char(s.begin_interval_time,'mm/dd/yy_hh24mi') || to_char(s.end_interval_time,'_hh24mi') date_time,
sql.plan_hash_value plan_hash,
sql.executions_delta executions,
(sql.elapsed_time_delta/1000000)/decode(sql.executions_delta,null,1,0,1,sql.executions_delta) avg_etime_s,
sql.buffer_gets_delta/decode(sql.executions_delta,null,1,0,1,sql.executions_delta) avg_lio,
sql.disk_reads_delta/decode(sql.executions_delta,null,1,0,1,sql.executions_delta) avg_pio,
(sql.cpu_time_delta/1000000)/decode(sql.executions_delta,null,1,0,1,sql.executions_delta) avg_cputime_s,
sql.rows_processed_total/decode(sql.executions_delta,null,1,0,1,sql.executions_delta) avg_row
from dba_hist_sqlstat sql, dba_hist_snapshot s
where sql.instance_number =(select instance_number from v$instance)
and sql.dbid =(select dbid from v$database)
and s.snap_id = sql.snap_id
and sql_id = trim('&sql_id') order by s.snap_id desc)
where rownum <= 100;
查看sql执行统计信息
SELECT INST_ID,
sql_text,
address,
HASH_VALUE,
sql_id,
CHILD_NUMBER,
CPU_TIME,
ELAPSED_TIME,
FIRST_LOAD_TIME,
LAST_LOAD_TIME,
last_active_time,
SHARABLE_MEM,
SORTS,
EXECUTIONS,
DISK_READS,
BUFFER_GETS,
CONCURRENCY_WAIT_TIME,
CLUSTER_WAIT_TIME,
USER_IO_WAIT_TIME,
sql_fulltext
FROM gv$sql
WHERE sql_id = 'abanhaqjghq3h';
查看某个sql的内存中的执行计划
set linesize 200
select * from table(DBMS_XPLAN.DISPLAY_CURSOR('abanhaqjghq3h',1));
11g,flush某个sql的执行计划
--exec sys.dbms_shared_pool.purge('ADDRESS,HASH_VALUE','c');
exec sys.dbms_shared_pool.purge('C000000F75648040,2734184560','c');
exec sys.dbms_shared_pool.purge('C000000F73273450,426993161','c');
exec sys.dbms_shared_pool.purge('C000000A73C19CF8,2734184560','c');
exec sys.dbms_shared_pool.purge('C000000F75648040,2734184560','c');
