Statistics 0 plan cost 1 temp tablespace 历史占用情况dba_hist_active_sess_history

T表的Statistics 为0 

 

导致执行计划中显示T 后面行数是1， 1 hash join 21K的 正常情况hash join的后是1 不是11k，由于1导致最后40亿的nest loop执行了4+小时。 gather Statistics 自动修改执行计划后1分钟

---- 查看过去TEMP使用率
select a.SAMPLE_TIME,
sum(temp_space_allocated) / 1024 / 1024 / 1024,
sqL_id
from dba_hist_active_sess_history a
where a.TEMP_SPACE_ALLOCATED is not null
and sample_time between to_date('202407300101', 'yyyymmddhh24mi') and
to_date('202407301101', 'yyyymmddhh24mi')
group by a.SAMPLE_TIME, sql_id
order by 2 desc;
 
---查看某个时段占用TEMP空间的SQL
select SQL_ID,
sum(trunc(TEMP_SPACE_ALLOCATED / 1024 / 1024 / 1024)) used_GB
from gv$active_session_history
where (SAMPLE_TIME between to_date('202407300101', 'yyyymmddhh24mi') and to_date('202407301101', 'yyyymmddhh24mi'))
--and sql_id in ('')
and TEMP_SPACE_ALLOCATED is not null
and event like '%temp%'
group by sql_id;
 
--查看SQL文本
select sqL_id, to_char(sql_fulltext), plan_hash_value
from v$sql
where sqL_id in (select distinct SQL_ID
from DBA_HIST_ACTIVE_SESS_HISTORY
where (SAMPLE_TIME between to_date('202407300101', 'yyyymmddhh24mi') and to_date('202407301101', 'yyyymmddhh24mi'))
--and sql_id in ('')
and TEMP_SPACE_ALLOCATED is not null
and event like '%temp%')
 ; 

--查看这批SQL分别占用多少TEMP空间
select *
from (select sample_time,
session_id,
session_serial#,
sql_id,
sum(trunc(TEMP_SPACE_ALLOCATED / 1024 / 1024)) usedMB,
count(*)
from v$active_session_history
where TEMP_SPACE_ALLOCATED is not null
and SAMPLE_TIME between to_date('202407300101', 'yyyymmddhh24mi') and to_date('202407301101', 'yyyymmddhh24mi')
group by sample_time, session_id, session_serial#, sqL_id)
where usedMB > 800
order by 1;
 

select a.SAMPLE_TIME,
sql_id,
round(sum(temp_space_allocated) / 1024 / 1024, 3) sum_MB
from v$active_session_history a
where a.TEMP_SPACE_ALLOCATED is not null
and SAMPLE_TIME between to_date('202407300101', 'yyyymmddhh24mi') and to_date('202407301101', 'yyyymmddhh24mi')
group by a.SAMPLE_TIME, sqL_id
order by 2 desc;
 
select a.SAMPLE_TIME,
sql_exec_id,
sql_exec_start,
sum(temp_space_allocated) / 1024 / 1024 / 1024,
sql_id
from dba_hist_active_sess_history a
where a.TEMP_SPACE_ALLOCATED is not null
and SAMPLE_TIME between to_date('202407300101', 'yyyymmddhh24mi') and to_date('202407301101', 'yyyymmddhh24mi')
--and sql_id = ‘xxxxxxxx’
group by a.SAMPLE_TIME, sql_exec_id, sql_exec_start,sql_id
order by 1;
 
–找到SQL对应的对象

select -- to_char(wm_concat(distinct '''' || current_obj# || '''')) currentobj
a.current_obj# ,a.* 
from dba_hist_active_sess_history a
where SAMPLE_TIME between to_date('202407300101', 'yyyymmddhh24mi') and to_date('202407301101', 'yyyymmddhh24mi')
--and sql_id = ‘xxxxxxxx’
group by current_obj#

select distinct owner, object_name
from dba_objects
where object_id in ('628165')