1、根据sid查询sql语句
语句1：
select sql_text from v$sqlarea a,v$session b where a.SQL_ID=b.PREV_SQL_ID and b.SID=135;
语句2：
select sql_text from v$sqlarea where (hash_value,address)
=(select sql_hash_value,sql_address from v$session where sid = 6658);
2、查询那个列适合建立索引
语句1：
select column_Name,num_distinct from dba_tab_columns where table_name='TEST10' and owner='HCJ'
–查看最近执行慢的sql
set linesize 1000
col sql_id for a20
col sql_opname for a10
col min_time for a20
col max_time for a20
select session_id,
session_serial#,
sql_id,
sql_opname,
sql_plan_hash_value,
sql_exec_id,
count(1),
to_char(min(sample_time),‘yyyy-mm-dd hh24:mi:ss’) min_time,
to_char(max(sample_time),‘yyyy-mm-dd hh24:mi:ss’) max_time
from v$active_session_history
where sample_time >= to_date(‘20240603 00:00:00’, ‘yyyymmdd hh24:mi:ss’)
and sample_time <= to_date(‘20240603 18:00:00’, ‘yyyymmdd hh24:mi:ss’)
and sql_id=‘3zkdpah99grnz’
group by session_id,
session_serial#,
sql_id,
sql_opname,
sql_plan_hash_value,
sql_exec_id
order by min_time;
–查看执行计划中执行最慢的line_id
select sql_plan_line_id,sql_plan_hash_value, event, count(1)
from v$active_session_history
where session_id = ‘150’
and session_serial# = ‘41227’
and sql_id = ‘3h4xrz6zdqpqs’
and sql_exec_id = ‘16777216’
group by sql_plan_line_id,sql_plan_hash_value, event
order by count(1);
–查看等待事件
SELECT
inst_id,
sid,
serial#,
sql_id,
event,
p1text,
p1,
p2text,
p2,
p3text,
p3
FROM
gv$session
WHERE
sql_id = ‘1za57ucjrcgx1’
AND event NOT IN (‘SQL*Net message from client’, ‘rdbms ipc message’, ‘pmon timer’);
查看sqlid的子游标
SELECT INST_ID,SQL_ID,CHILD_NUMBER,OPERATION,OPTIONS,OBJECT_NAME FROM GV$SQL_PLAN WHERE SQL_ID=‘4dan4xfjp1wxw’ ORDER BY 1;
