热块表处理：
alter table web.t_order pctfree 30;
索引碎片处理：
select INDEX_NAME,INDEX_TYPE,TABLE_NAME from user_indexes where table_name='SELL';
col column_name for a20
col index_name for a20
select table_name,index_name,column_name from user_ind_columns where index_name in ('IDX_SELLSEAT_MPID','PK_SELLSEAT','UK_SELLSEAT');
execute DBMS_STATS.GATHER_TABLE_STATS('WEBDATA','GEWA_ORDER',CASCADE => TRUE);
analyze index IDX_GEWA_ORDER_ADDTIME validate structure;
select t.name, --索引名
t.lf_rows, --number of leaf rows (values in the index)
t.lf_blks,
t.del_lf_rows, --number of deleted leaf rows in the index
(t.del_lf_rows / t.lf_rows)*100 ratio --删除比例
from index_stats t
where t.name='IDX_GEWA_ORDER_ADDTIME';
如果RATIO值大于30％以上就需要考虑索引重建了。
select PCT_USED from index_stats where NAME='IDX_GEWA_ORDER_ADDTIME';
pct_userd列，如果该值<=75%，就应该重建了；改列的默认值是90%
--查询表空间使用率
select a.tablespace_name,total,free,total-free used from
( select tablespace_name,sum(bytes)/1024/1024 total from dba_data_files
group by tablespace_name) a,
( select tablespace_name,sum(bytes)/1024/1024 free from dba_free_space
group by tablespace_name) b
where a.tablespace_name=b.tablespace_name;
SELECT total.tablespace_name,
Round(total.MB, 2) AS Total_MB,
Round(total.MB - free.MB, 2) AS Used_MB,
Round(( 1 - free.MB / total.MB ) * 100, 2)
|| '%' AS Used_Pct
FROM (SELECT tablespace_name,
Sum(bytes) / 1024 / 1024 AS MB
FROM dba_free_space
GROUP BY tablespace_name) free,
(SELECT tablespace_name,
Sum(bytes) / 1024 / 1024 AS MB
FROM dba_data_files
GROUP BY tablespace_name) total
WHERE free.tablespace_name = total.tablespace_name;
col file_name for a55
SELECT tablespace_name,
file_id,
file_name,
round(bytes / (1024 * 1024), 0) total_space_mb
FROM dba_data_files
ORDER BY tablespace_name;
如何查询占CPU高的oracle进程
方法1：
top //查看最占CPU的进程
ps -ef |grep pid //查看是远程连接进程，还是DB server进程
select sql_text,spid,v$session.program,process from
v$sqlarea,v$session,v$process
where v$sqlarea.address=v$session.sql_address
and v$sqlarea.hash_value=v$session.sql_hash_value
and v$session.paddr=v$process.addr
and v$process.spid in (&PID);
getsql.sql脚本
select /*+ ORDERED */ sql_text from v$sqltext a
where (a.hash_value,a.address) in (select decode(sql_hash_value,0,prev_hash_value,sql_hash_value),
decode(sql_hash_value,0,prev_sql_addr,sql_address) from v$session b where b.paddr=(select addr from v$process c where c.spid='&pid')) order by piece asc ;
getsid.sql脚本
select sid,serial#,sql_id,username,machine from v$session b where b.paddr=(select addr from v$process c where c.spid='&pid');
跟踪SQL脚本
exec dbms_system.set_sql_trace_in_session(&sid,,&serial,true);
....
exec dbms_system.set_sql_trace_in_session(&sid,,&serial,false);
使用一下SQL找到当前session的跟踪文件
SELECT d.value|| '/' ||lower(rtrim(i.instance, chr( 0 )))|| '_ora_' ||p.spid|| '.trc' trace_file_name
from
( select p.spid from v$mystat m,v$session s, v$process p
where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr) p,
( select t.instance from v$thread t,v$parameter v
where v.name = 'thread' and (v.value = 0 or t.thread# = to_number(v.value))) i,
( select value from v$parameter where name = 'user_dump_dest' ) d;
$tkprof d:/oracle/admin/FZLGFM/udump/fzlgfm_ora_3468.trc d:/explain_format.txt
getplan.sql脚本
select * from table(dbms_xplan.display_cursor('&sid'))
set autotrace trace explain
查看Undo中资源消耗高的SQL
sqlplus -s $DB_CONN_STR@$SH_DB_SID <
select sum(undoblks)*8/1024 total_size_MB from v\$undostat ;
select *from (
select maxqueryid,
round(sum(undoblks )*8/1024) consumed_size_MB
from v\$undostat group by maxqueryid order by consumed_size_MB desc
) where rownum<50;
EOF
Exit
select sum(undoblks)*8/1024 total_size_MB from v\$undostat ;
select *from (
select maxqueryid,
round(sum(undoblks )*8/1024) consumed_size_MB
from v$undostat group by maxqueryid order by consumed_size_MB desc
) where rownum<50;
select sql_text from v$sqltext where sql_id='0rc4km05kgzb9';
杀会话：
alter system kill session '1793,25320';
------------------------
undo检查与建议脚本：
SET SERVEROUTPUT ON
SET LINES 600
ALTER SESSION SET NLS_DATE_FORMAT = 'DD/MM/YYYY HH24:MI:SS';
DECLARE
v_analyse_start_time DATE := SYSDATE - 7;
v_analyse_end_time DATE := SYSDATE;
v_cur_dt DATE;
v_undo_info_ret BOOLEAN;
v_cur_undo_mb NUMBER;
v_undo_tbs_name VARCHAR2(100);
v_undo_tbs_size NUMBER;
v_undo_autoext BOOLEAN;
v_undo_retention NUMBER(6);
v_undo_guarantee BOOLEAN;
v_instance_number NUMBER;
v_undo_advisor_advice VARCHAR2(100);
v_undo_health_ret NUMBER;
v_problem VARCHAR2(1000);
v_recommendation VARCHAR2(1000);
v_rationale VARCHAR2(1000);
v_retention NUMBER;
v_utbsize NUMBER;
v_best_retention NUMBER;
v_longest_query NUMBER;
v_required_retention NUMBER;
BEGIN
select sysdate into v_cur_dt from dual;
DBMS_OUTPUT.PUT_LINE(CHR(9));
DBMS_OUTPUT.PUT_LINE('- Undo Analysis started at : ' || v_cur_dt || ' -');
DBMS_OUTPUT.PUT_LINE('--------------------------------------------------');
v_undo_info_ret := DBMS_UNDO_ADV.UNDO_INFO(v_undo_tbs_name, v_undo_tbs_size, v_undo_autoext, v_undo_retention, v_undo_guarantee);
select sum(bytes)/1024/1024 into v_cur_undo_mb from dba_data_files where tablespace_name = v_undo_tbs_name;
DBMS_OUTPUT.PUT_LINE('NOTE:The following analysis is based upon the database workload during the period -');
DBMS_OUTPUT.PUT_LINE('Begin Time : ' || v_analyse_start_time);
DBMS_OUTPUT.PUT_LINE('End Time : ' || v_analyse_end_time);
DBMS_OUTPUT.PUT_LINE(CHR(9));
DBMS_OUTPUT.PUT_LINE('Current Undo Configuration');
DBMS_OUTPUT.PUT_LINE('--------------------------');
DBMS_OUTPUT.PUT_LINE(RPAD('Current undo tablespace',55) || ' : ' || v_undo_tbs_name);
DBMS_OUTPUT.PUT_LINE(RPAD('Current undo tablespace size (datafile size now) ',55) || ' : ' || v_cur_undo_mb || 'M');
DBMS_OUTPUT.PUT_LINE(RPAD('Current undo tablespace size (consider autoextend) ',55) || ' : ' || v_undo_tbs_size || 'M');
IF V_UNDO_AUTOEXT THEN
DBMS_OUTPUT.PUT_LINE(RPAD('AUTOEXTEND for undo tablespace is',55) || ' : ON');
ELSE
DBMS_OUTPUT.PUT_LINE(RPAD('AUTOEXTEND for undo tablespace is',55) || ' : OFF');
END IF;
DBMS_OUTPUT.PUT_LINE(RPAD('Current undo retention',55) || ' : ' || v_undo_retention);
IF v_undo_guarantee THEN
DBMS_OUTPUT.PUT_LINE(RPAD('UNDO GUARANTEE is set to',55) || ' : TRUE');
ELSE
dbms_output.put_line(RPAD('UNDO GUARANTEE is set to',55) || ' : FALSE');
END IF;
DBMS_OUTPUT.PUT_LINE(CHR(9));
SELECT instance_number INTO v_instance_number FROM V$INSTANCE;
DBMS_OUTPUT.PUT_LINE('Undo Advisor Summary');
DBMS_OUTPUT.PUT_LINE('---------------------------');
v_undo_advisor_advice := dbms_undo_adv.undo_advisor(v_analyse_start_time, v_analyse_end_time, v_instance_number);
DBMS_OUTPUT.PUT_LINE(v_undo_advisor_advice);
DBMS_OUTPUT.PUT_LINE(CHR(9));
DBMS_OUTPUT.PUT_LINE('Undo Space Recommendation');
DBMS_OUTPUT.PUT_LINE('-------------------------');
v_undo_health_ret := dbms_undo_adv.undo_health(v_analyse_start_time, v_analyse_end_time, v_problem, v_recommendation, v_rationale, v_retention, v_utbsize);
IF v_undo_health_ret > 0 THEN
DBMS_OUTPUT.PUT_LINE('Minimum Recommendation : ' || v_recommendation);
DBMS_OUTPUT.PUT_LINE('Rationale : ' || v_rationale);
DBMS_OUTPUT.PUT_LINE('Recommended Undo Tablespace Size : ' || v_utbsize || 'M');
ELSE
DBMS_OUTPUT.PUT_LINE('Allocated undo space is sufficient for the current workload.');
END IF;
SELECT dbms_undo_adv.best_possible_retention(v_analyse_start_time, v_analyse_end_time) into v_best_retention FROM dual;
SELECT dbms_undo_adv.longest_query(v_analyse_start_time, v_analyse_end_time) into v_longest_query FROM dual;
SELECT dbms_undo_adv.required_retention(v_analyse_start_time, v_analyse_end_time) into v_required_retention FROM dual;
DBMS_OUTPUT.PUT_LINE(CHR(9));
DBMS_OUTPUT.PUT_LINE('Retention Recommendation');
DBMS_OUTPUT.PUT_LINE('------------------------');
DBMS_OUTPUT.PUT_LINE(RPAD('The best possible retention with current configuration is ',60) || ' : ' || v_best_retention || ' Seconds');
DBMS_OUTPUT.PUT_LINE(RPAD('The longest running query ran for ',60) || ' : ' || v_longest_query || ' Seconds');
DBMS_OUTPUT.PUT_LINE(RPAD('The undo retention required to avoid errors is ',60) || ' : ' || v_required_retention || ' Seconds');
END;
/
------------------------
-查看某个时间DML情况1
select to_char(b.begin_interval_time, 'yyyy-mm-dd hh24:mi:ss') update_time,
c.sql_text sql_text
from dba_hist_active_sess_history a, DBA_HIST_SNAPSHOT b, v$sqlarea c
where a.snap_id = b.snap_id
and a.sql_id = c.sql_id
and b.begin_interval_time >= to_date('2015-10-18 11:00:00', 'yyyy-mm-dd hh24:mi:ss')
and b.begin_interval_time < to_date('2015-10-18 11:20:00', 'yyyy-mm-dd hh24:mi:ss')
and lower(c.sql_text) like '%insert%'
order by update_time;
-查看某个时间DML情况2
insert into WEBDATA.GEWA_ORDER
INSERT INTO WEBDATA.TICKET_ORDER
UPDATE WEBDATA.GEWA_ORDER SET PAYMETHOD
insert into WEBDATA.HIS_DATA
insert into WEBDATA.SELLSEAT
select a.sql_id,count(*),count(*)/sum(count(*)) over ()
from v$active_session_history a,v$sqlarea c
where a.sql_id=c.sql_id and a.sample_time between to_date('2015-10-20 16:30','yyyy-mm-dd hh24:mi') and to_date('2015-10-20 16:45','yyyy-mm-dd hh24:mi')
and lower(c.sql_text) like '%insert into webdata.gewa_order%'
group by a.sql_id
order by count(*);
select count(*)
from v$active_session_history a,v$sqlarea c
where a.sql_id=c.sql_id and a.sample_time between to_date('2015-10-20 16:30','yyyy-mm-dd hh24:mi') and to_date('2015-10-20 16:45','yyyy-mm-dd hh24:mi')
and lower(c.sql_text) like '%insert%' ;
select sql_text from v$sqlarea where sql_id='cv0h2af6fgaud';
select to_char(b.begin_interval_time, 'yyyy-mm-dd hh24:mi:ss') update_time,
c.sql_text sql_text
from dba_hist_active_sess_history a, DBA_HIST_SNAPSHOT b, v$sqlarea c
where a.snap_id = b.snap_id and a.snap_id in (9781,9782)
and a.sql_id = c.sql_id
and b.begin_interval_time >= to_date('2015-10-18 11:00:00', 'yyyy-mm-dd hh24:mi:ss')
and b.begin_interval_time < to_date('2015-10-18 11:20:00', 'yyyy-mm-dd hh24:mi:ss')
and lower(c.sql_text) like '%insert%'
order by update_time;
select to_char(sample_TIME, 'hh24:mi') minites,
count(1) total,
sum(case
when program = 'JDBC Thin Client' then
1
else
0
end) jdbcthine
from v$active_session_history
where sample_time between
to_date('2015-10-18 10:00:00', 'yyyy-mm-dd hh24:mi:ss') and
to_date('2015-10-18 10:15:00', 'yyyy-mm-dd hh24:mi:ss')
group by to_char(sample_TIME, 'hh24:mi')
order by to_char(sample_TIME, 'hh24:mi');