Oracle 维护常用SQL

select a.session_id,       a.sql_id,       a.machine,       a.blocking_session,       a.sample_time,       a.module,       a.PROGRAM,       a.event,       b.SQL_FULLTEXT  from v$active_session_history a, v$sqlarea b where a.sql_id = b.sql_idselect a.session_id, a.sql_id,a.blocking_session, a.sample_time,a.module,a.PROGRAM, a.event, b.sql_text  from v$active_session_history a, v$sqlarea b where a.sql_id = b.sql_id and a.session_id=920 and  sample_time between to_date('201208161000', 'yyyymmddhh24mi') and       to_date('201208161100', 'YYYYMMDDHH24MI');       --清理直方图 BEGIN          DBMS_STATS.GATHER_TABLE_STATS(ownname          => 'MBFE',                                       tabname          => 'MESSAGEIN',                                      estimate_percent => 100,                                        method_opt       => 'for columns MSGID size 1',                                      no_invalidate    => FALSE,                                       degree           => 16,                                       cascade          => TRUE);       END; --批量kill锁进程:select 'alter system kill session ' || '''' || sid || ',' || serial# || '''' || ';'  from v$session   where username='DWF'and  sid in (select sid                 from v$lock                where type in ('TM', 'TX')                  and lmode = 6)--查看统计信息是否过期:select owner, table_name name, object_type, stale_stats, last_analyzed  from dba_tab_statistics where table_name in ('TEST')   and owner = 'TEST'   and (stale_stats = 'YES' or last_analyzed is null);  ----根据执行计划查看表大小SQL> explain plan for select /*+ full(dept) */ emp.ename, emp.job, emp.sal, emp.dname, dept.loc  from emp, dept where emp.deptno = dept.deptno  2    3    4  ;Explained.SQL> col owner format a30col segment_name format a30col segment_type format a30select owner, segment_name,segment_type, sum(bytes / 1024 / 1024) "Size(Mb)"  from dba_segments where owner in (select /*+ no_unnest */ object_owner from plan_table)   and segment_name in (select /*+ no_unnest */   object_name from plan_table) group by owner,segment_type, segment_nameUNION----table in the indexselect owner, '*'||segment_name ,segment_type, sum(bytes / 1024 / 1024) "Size(Mb)"  from dba_segments where owner in (select  table_owner          from dba_indexes         where owner in (select /*+ no_unnest */  object_owner from plan_table)           and index_name in (select /*+ no_unnest */  object_name from plan_table))   and segment_name in (select /*+ no_unnest */  table_name          from dba_indexes         where owner in (select /*+ no_unnest */  object_owner from plan_table)           and index_name in (select /*+ no_unnest */  object_name from plan_table)) group by owner,segment_type, segment_name order by 3,4;SQL> SQL> SQL>   2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19  OWNER          SEGMENT_NAME        SEGMENT_TYPE         Size(Mb)------------------------------ ------------------------------ ------------------------------ ----------SCOTT          DEPT         TABLE      .0625SCOTT          EMP         TABLE      .0625 --根据sql_id 查看表大小:SQL> set echo offset echo offset verify offset serveroutput onset feedback offset lines 200set pages 40col segment_name for a20 heading 'OBJECT_NAME'col segment_size for 99999999999999 heading 'SEGMENT_SIZE(KB)'col block_count for 99999999999 heading 'BLOCK_COUNT'/* Formatted on 2013/12/24 18:52:26 (QP5 v5.240.12305.39446) *//* Formatted on 2013/12/24 19:14:26 (QP5 v5.240.12305.39446) */WITH t     AS (SELECT /*+ materialize */               DISTINCT OBJECT_OWNER, OBJECT_NAME           FROM (SELECT OBJECT_OWNER, OBJECT_NAME                   FROM V$SQL_PLAN                  WHERE SQL_ID = 'gzus7ufvp3xt3' AND OBJECT_NAME IS NOT NULL                 UNION ALL                 SELECT OBJECT_OWNER, OBJECT_NAME                   FROM DBA_HIST_SQL_PLAN                  WHERE SQL_ID = 'gzus7ufvp3xt3' AND OBJECT_NAME IS NOT NULL))SELECT a.owner,       a.segment_name,       a.segment_size as MB,       TRUNC (a.segment_size / 8) block_count  FROM (  SELECT owner, segment_name , TRUNC (SUM (bytes) / 1024/1024 ) segment_size            FROM dba_segments           WHERE   /*  segment_type LIKE 'TABLE%'                 AND*/ (OWNER, segment_name) IN                        (SELECT table_owner, table_name                           FROM dba_indexes                          WHERE (owner, index_name) IN (SELECT * FROM t)                         UNION ALL                         SELECT * FROM t)        GROUP BY  (owner, segment_name)) a;---监控等待事件:select SAMPLE_TIME,       SESSION_ID,         NAME,         P1,         P2,         P3,         WAIT_TIME,         CURRENT_OBJ#,         CURRENT_FILE#,         CURRENT_BLOCK#    from v$active_session_history ash, v$event_name enm   where ash.event# = enm.event# ---查看session异常:select username, machine, status, prev_sql_addr, prev_hash_value, prev_sql_id, process, count(1) from v$session group by username, machine, status, prev_sql_addr, prev_hash_value, prev_sql_id, process --rsync仓库DB-ORACLE /home/dataun/ETL_init/DATA$ rsync -avH ./2013-08-22/CMS/ dwetl@10.128.8.51:/home/dataun/ETL_init/DATA/2013-08-22/CMS/会自动创建目录---收集统计信息BEGIN  DBMS_STATS.GATHER_TABLE_STATS(ownname          => 'SCOTT',                                tabname          => 'DEPT',                                estimate_percent => 30,                                method_opt       => 'for all columns size repeat',                                no_invalidate    => FALSE,                                degree           => 8,                                cascade          => TRUE);END;/ 对分区表收集统计信息BEGIN  DBMS_STATS.GATHER_TABLE_STATS(ownname          => 'ROBINSON',                                tabname          => 'P_TEST',                                estimate_percent => 30,                                method_opt       => 'for all columns size repeat',                                no_invalidate    => FALSE,                                degree           => 8,                                granularity      => 'ALL',                                cascade          => TRUE);END;/---通过pid 查看sql：s  <<!set linesize 200select  sql_text from v\$sqlarea where (address,hash_value) in (select /*+unnest*/  DECODE(sql_hash_value,0,prev_sql_addr,sql_address),DECODE(sql_hash_value,0,prev_hash_value,sql_hash_value) from v\$session where sid =(select sid from v\$session where paddr=(select ADDR from v\$process where SPID='$1')));! select  sql_text from v$sqlarea where (address,hash_value) in (select  /*+unnest*/ DECODE(sql_hash_value,0,prev_sql_addr,sql_address),DECODE(sql_hash_value,0,prev_hash_value,sql_hash_value) from v$session where sid =1036)---查看不能共享的SQL:Unshared SQLThis method identifies similar SQL statements that could be shared if literals were replaced with bind variables. The idea is to either: Manually inspect SQL statements that have only one execution to see whether they are similar: SELECT sql_text  FROM V$SQLAREA WHERE executions = 1 ORDER BY sql_text;Or, automate this process by grouping together what may be similar statements. Do this by estimating the number of bytes of a SQL statement which will likely be the same, and group the SQL statements by that many bytes. For example, the example below groups together statements that differ only after the first 60 bytes. SELECT SUBSTR(sql_text,1, 60), COUNT(*)  FROM V$SQLAREA WHERE executions = 1 GROUP BY SUBSTR(sql_text, 1, 60) HAVING COUNT(*) > 1;--查看UNDOselect t. used_ublk,s.username, u.name from v$transaction t,v$rollstat r, v$rollname u,v$session s  where s.taddr=t.addr and t.xidusn=r.usn and r.usn=u.usn order by s.username;SELECT r.name 回滚段名,d.tablespace_name,s.sid,s.serial#,s.username 用户名,t.status,t.cr_get,t.phy_io,t.used_ublk,t.noundo,substr(s.program,1,78) 应用程序FROM sys.v_$session s,sys.v_$transaction t,sys.v_$rollname r,Dba_Rollback_Segs dWHERE t.addr=s.taddr and t.xidusn=r.usn AND d.segment_name= r.nameORDER BY t.cr_get,t.phy_io;--查看直方图:select a.column_name,           b.num_rows,           a.num_distinct Cardinality,            round(a.num_distinct / b.num_rows * 100, 2) selectivity,           a.histogram,          a.num_buckets      from dba_tab_col_statistics a, dba_tables b     where a.owner = b.owner       and a.table_name = b.table_name      and a.owner = 'DWF'     and a.table_name = 'F_AGT_BUSINESS_CONTRACT_H'; --根据sql_id 查看执行计划SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('SQL_ID',SQL_CHILD_NUMBER));SQL> select * from table(dbms_xplan.display_cursor('crrfjnb0y4mq1'));---高级执行计划:11G：alter session set statistics_level=all; ---再运行SQLselect * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));PLAN_TABLE_OUTPUT----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------SQL_ID  ctbxh0qbtkhwf, child number 0-------------------------------------select /*+ leading(e) USE_NL(E D)*/ e.ename,e.job,d.dname from empe,dept d  where e.deptno=d.deptno and e.sal<2000Plan hash value: 351108634--------------------------------------------------------------------------------------------------| Id  | Operation                    | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |--------------------------------------------------------------------------------------------------|   1 |  NESTED LOOPS                |         |      1 |      8 |      8 |00:00:00.01 |      20 ||*  2 |   TABLE ACCESS FULL          | EMP     |      1 |      8 |      8 |00:00:00.01 |       8 ||   3 |   TABLE ACCESS BY INDEX ROWID| DEPT    |      8 |      1 |      8 |00:00:00.01 |      12 ||*  4 |    INDEX UNIQUE SCAN         | PK_DEPT |      8 |      1 |      8 |00:00:00.01 |       4 |--------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - filter("E"."SAL"<2000)   4 - access("E"."DEPTNO"="D"."DEPTNO")已选择22行。10G：高级执行计划:set serverout offscott@JSSPDG> alter session set statistics_level=all; Session altered.scott@JSSPDG> select * from dept where deptno=10;    DEPTNO DNAME   LOC---------- -------------- ------------- 10 ACCOUNTING   NEW YORKscott@JSSPDG> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));PLAN_TABLE_OUTPUT----------------------------------------------------------------------------------------------------SQL_ID 4xamnunv51w9j, child number 1-------------------------------------select * from dept where deptno=10Plan hash value: 602043285-------------------------------------------------------------------------------------------------| Id  | Operation      | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |-------------------------------------------------------------------------------------------------|   1 |  TABLE ACCESS BY INDEX ROWID| DEPT    |      1 |      1 |      1 |00:00:00.01 |       2 ||*  2 |   INDEX UNIQUE SCAN     | DEPT_PK |      1 |      1 |      1 |00:00:00.01 |       1 |-------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - access("DEPTNO"=10)18 rows selected.-----------------------------------查看高级执行计划:explain plan for select ename,deptno from emp where deptno in (select deptno from dept where dname='CHICAGO');SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, 'ADVANCED -PROJECTION')); ---采集AWR：为了生成手动的快照，可以使用DBMS_WORKLOAD_REPOSITORY程序包的CREATE_ SNAPSHOT过程：execute DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();当手动搜集快照后AWR 我手动搜集了快照 那么系统默认一个小时搜集,是不是在手动搜集快照的时间点后一个小时搜集的       SnapInstance     DB Name     Snap Id    Snap Started    Level------------ ------------ --------- ------------------ -----perass      PERASS        4803 16 Oct 2011 00:00    1          4804 16 Oct 2011 01:00    1          4805 16 Oct 2011 02:00    1          4806 16 Oct 2011 03:00    1          4807 16 Oct 2011 04:00    1          4808 16 Oct 2011 05:00    1          4809 16 Oct 2011 06:00    1          4810 16 Oct 2011 07:00    1          4811 16 Oct 2011 08:00    1          4812 16 Oct 2011 09:00    1          4813 16 Oct 2011 10:00    1          4814 16 Oct 2011 11:00    1          4815 16 Oct 2011 12:00    1          4816 16 Oct 2011 12:51    1          4817 16 Oct 2011 13:09    1AWR默认情况下的自动采样周期是1小时，因此如果前半小时出现手动采样对整点的自动采样没有影响；在后半小时出现手动采样，下一次整点的自动采样将被取消。按照文档说明接下来回在14:00采集快照,请看答案?Instance     DB Name     Snap Id    Snap Started    Level------------ ------------ --------- ------------------ -----perass      PERASS        4811 16 Oct 2011 08:00    1          4812 16 Oct 2011 09:00    1          4813 16 Oct 2011 10:00    1          4814 16 Oct 2011 11:00    1          4815 16 Oct 2011 12:00    1          4816 16 Oct 2011 12:51    1          4817 16 Oct 2011 13:09    1          4818 16 Oct 2011 14:00    1          4819 16 Oct 2011 15:00    1          4820 16 Oct 2011 16:00    1---查看表空间:sqlplus / as sysdba <<!select a.tablespace_name, round(a.total_size,1) "total(M)",     round(a.total_size)-round(nvl(b.free_size,0),1) "used(M)",     round(nvl(b.free_size,0),1) "free(M)",     round(nvl(b.free_size,0)/total_size*100,1) "free rate(%)"     from (select tablespace_name,sum(bytes)/1024/1024 total_size     from dba_data_files     group by tablespace_name) a,     (select tablespace_name,sum(bytes)/1024/1024 free_size     from dba_free_space   group by tablespace_name) b     where a.tablespace_name = b.tablespace_name(+)   order by "free rate(%)";!