全表扫描SQL抓取

--全表扫描SQL抓取步骤  2011-12-20整理
--FOR CRM3.3
--以下操作都在sys用户执行


--【Step1】对表进行表分析，一周一次。注: 现场请酌情进行表分析，无需全量，无需频繁操作
select 'exec dbms_stats.gather_table_stats(ownname=>'''|| owner || ''', tabname=>'''|| table_name || ''');' 
from all_all_tables
where owner in ('CCARE','ISAP','INVENTORY','CRMPUB','USM','OSMS','CHANNEL','NSER');


--【Step2】第一次建表，只执行一次，全量
create table crm33_tableaccessfull as 
select  a.sql_id,a.sql_text,a.sql_fulltext,a.executions,a.last_load_time,c.owner,c.table_name,c.num_rows,sysdate "ADD_TIME"
 from v$sqlarea a,all_all_tables c
 where a. module='JDBC Thin Client'and a.sql_id in(
          select distinct(sql_id) from v$sql_plan b
         where b.object_owner in ('CCARE','ISAP','INVENTORY','CRMPUB','USM','OSMS','CHANNEL','NSER')
          and b.operation='TABLE ACCESS' and options='FULL'
        and b.OBJECT_NAME=c.table_name  )
        and c.num_rows>=10000;  




--下面Step3 - Step7 每天执行一次，只考虑增量数据
--【Step3】建临时表
create table crm33_tableaccessfull_temp as 
select  a.sql_id,a.sql_text,a.sql_fulltext,a.executions,a.last_load_time,c.owner,c.table_name,c.num_rows,sysdate "ADD_TIME"
 from v$sqlarea a,all_all_tables c
 where a. module='JDBC Thin Client'and a.sql_id in(
          select distinct(sql_id) from v$sql_plan b
         where b.object_owner in ('CCARE','ISAP','INVENTORY','CRMPUB','USM','OSMS','CHANNEL','NSER')
          and b.operation='TABLE ACCESS' and options='FULL'
        and b.OBJECT_NAME=c.table_name  )
        and c.num_rows>=10000;  


--【Step4】清除重复SQL
delete from crm33_tableaccessfull_temp where sql_id in (select sql_id from crm33_tableaccessfull);
commit;


--【Step5】合并到主表
insert into crm33_tableaccessfull select * from crm33_tableaccessfull_temp;
commit;


--【Step6】删除临时表
drop table crm33_tableaccessfull_temp;


--【Step7】导出Excel

select * from crm33_tableaccessfull order by add_time,owner,num_rows desc;

 

 

 

--1.检查是否有sql语句长时间执行


select a.SQL_ID, c.username ,c.sid,c.program ,a.sql_text ,a.SQL_FULLTEXT, b.seconds_in_wait
from v$sqlarea a  , v$session_wait  b ,v$session c 
where c.sid=b.sid
and c.program='JDBC Thin Client'
and c.status = 'ACTIVE'
and a.address =c.sql_address 
and c.username is not null;






2.检查进程占用资源情况
（1）显示20个消耗cpu最多的进程： 
ps aux |sort -rn +2 |head -20 
（2）显示20个消耗存储空间最多的进程： 
ps aux |sort -rn +3 |head -20 
（3）按实际内存使用的多少顺序显示系统中的前十个进程： 
ps vx |grep -v PID |sort -rn +6 |head -10 


检查锁表语句
select a.sid,a.type,a.id1,c.object_name,b.username,a.lmode,B.MACHINE,D.SPID
from v$lock a,v$session b,all_objects c,V$PROCESS D
where a.sid=b.sid and a.type in ('TM','TX')
and c.object_id=a.id1
AND B.PADDR=D.ADDR
order by username
*/








AWR


创建快照
exec  dbms_workload_repository.create_snapshot;


生成报告
@$ORACLE_HOME/rdbms/admin/awrrpt.sql;