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_id
select 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 a30
col segment_name format a30
col segment_type format a30
select 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_name
UNION
----table in the index
select 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 .0625
SCOTT EMP TABLE .0625
--根据sql_id 查看表大小:
SQL> set echo off
set echo off
set verify off
set serveroutput on
set feedback off
set lines 200
set pages 40
col 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 200
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 =(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 SQL
This 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;
--查看UNDO
select 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 d
WHERE t.addr=s.taddr
and t.xidusn=r.usn
AND d.segment_name= r.name
ORDER 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; ---再运行SQL
select * 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 emp
e,dept d where e.deptno=d.deptno and e.sal<2000
Plan 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 off
scott@JSSPDG> alter session set statistics_level=all;
Session altered.
scott@JSSPDG> select * from dept where deptno=10;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
scott@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=10
Plan 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 我手动搜集了快照 那么系统默认一个小时搜集,是不是在手动搜集快照的时间点后一个小时搜集的
Snap
Instance 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 1
AWR默认情况下的自动采样周期是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(%)";
!