性能测试中常用数据库操作及数据库监控SQL总结
--by newhackerman
目录
1.找出长时间执行的SQL语句： 3
2.找出使用CPU多的SQL语句： 4
3.查找没有保存在library cache中的大对象： 4
4.调整PGA优化排序： 4
5.SQL的命中率： 5
7.回滚段的争用情况： 6
监控数据库性能的语句 7
1. 监控事例的等待 7
2. 回滚段的争用情况 8
3. 监控表空间的 I/O 比例 8
4. 监控文件系统的 I/O 比例 8
5.在某个用户下找所有的索引 8
6. 监控 SGA 的命中率 9
7. 监控 SGA 中字典缓冲区的命中率 9
8. 监控 SGA 中共享缓存区的命中率，应该小于1% 9
9. 显示所有数据库对象的类别和大小 10
11. 监控内存和硬盘的排序比率，最好使它小于 .10，增加 sort_area_size 10
12. 监控当前数据库谁在运行什么SQL语句 11
13. 监控字典缓冲区 11
14. 找ORACLE字符集 11
15. 监控 MTS 11
16. 碎片程度 12
查看碎片程度高的表 12
17. 表、索引的存储情况检查 13
18、找使用CPU多的用户session 13
19.对可疑/性能不好的Server Process来进行Trace.,可以用tkprof来分析Trace的结果. 14
20.查看Lock 15
调用oracle顾问，分析SQL语句，该怎样优化 17
查看锁表的方法 19
查看SGA区剩余可用内存 19
查看表的索引： 23
查看存储过程及触发器过程 23
查看指定表空间的空间大小,表空间总大小GB 23
查看可用表空间大小,表空间总大小MB 23
根据条件高效删除重复的记录（一次只删除一份：如相同的记录有两条，一次只删除一条，保留一条） 24
备份一张表的数据到一张新表 24
输出oracle性能报告(AWRRPT.sql AWR报告) 25
启停oracle定时调度任务 25
查看回滚相关系统参数 26
查看哪些session正在使用哪些回滚段 26
通过表分析判断索引是否失效及解决方法 27
查看每分钟处理记录数SQL 28
查看本用户下的各种对象的SQL脚本 28
找出长时间执行的SQL语句：
SELECT SE.SID, OPNAME, TRUNC(SOFAR / TOTALWORK * 100, 2) || '%' AS PCT_WORK, ELAPSED_SECONDS ELAPSED, ROUND(ELAPSED_SECONDS * (TOTALWORK - SOFAR) / SOFAR) REMAIN_TIME, SQL_TEXT
FROM V$SESSION_LONGOPS SL, V$SQLAREA SA, V$SESSION SE
WHERE SL.SQL_HASH_VALUE = SA.HASH_VALUE
AND SL.SID = SE.SID AND SOFAR != TOTALWORK ORDER BY START_TIME；
-----------------------------------------------------------------------------------------
找出使用CPU多的SQL语句：
查找指定SPID正在执行的SQL语句：
1．SELECT P.pid pid,S.sid sid,P.spid spid,S.username username,S.osuser osname,P.serial# S_#,P.terminal,P.program program,P.background,S.status,RTRIM(SUBSTR(a.sql_text, 1, 80)) SQL
FROM v$process P, v$session S,v$sqlarea A WHERE P.addr = s.paddr AND S.sql_address = a.address (+) AND P.spid LIKE 'SID';
2．在linux环境可以通过ps查看进程信息包括pid,windows中任务管理器的PID与v$process中pid不能一一对应。 windows是多线程服务器,每个进程包含一系列线程。这点于unix等不同，Unix每个Oralce进程独立存在，在Nt上所有线程由Oralce 进程衍生。
指定SID查看正在执行的SQL语句：
SELECT P.pid pid,S.sid sid,P.spid spid,S.username username,S.osuser osname,P.serial# S_#,P.terminal,P.program program,P.background,S.status,RTRIM(SUBSTR(a.sql_text, 1, 80)) SQL
FROM v$process P, v$session S,v$sqlarea A WHERE P.addr = s.paddr AND S.sql_address = a.address (+) AND s.sid = 'PID';
-----------------------------------------------------------------------------------------
查找没有保存在library cache中的大对象：
Select * from v$db_object_cache where sharable_mem>10000 and type in ('PACKAGE','PROCEDURE','FUNCTION','PACKAGE BODY') and kept='NO';
将这些对象保存在library cache中：
Execute dbms_shared_pool.keep('package_name');
对应脚本：dbmspool.sql
-----------------------------------------------------------------------------------------
调整PGA优化排序：
首先查看Oracle的v$pga_target_advice：
SELECT ROUND(pga_target_for_estimate/1024/1024) AS target_mb,
estd_pga_cache_hit_percentage AS hit_ratio,
estd_overalloc_count
FROM v$pga_target_advice
ORDER BY target_mb;
然后调整PGA
alter system set pga_aggregate_target=150M;
在OLTP系统中，典型PGA内存设置应该是总内存的较小部分（例如20％），剩下80％分配给SGA。
OLTP：PGA_AGGREGATE_TARGET = (total_mem * 80%) * 20%
在DSS系统中，由于会运行一些很大的查询，典型的PGA内存最多分配70％的内存。
DSS：PGA_AGGREGATE_TARGET = (total_mem * 80%) * 50%
-----------------------------------------------------------------------------------------
SQL的命中率：
SELECT gethitratio FROM v$librarycache WHERE namespace = 'SQL AREA';
查看指定某条SQL语句的执行情况（执行次数、加载次数等）：
SELECT sql_text , users_executing , executions , loads FROM v$sqlarea where sql_text like 'select * from hr.tt';
----------------------------
v$librarycache中reloads/pins的比率应该小于1％，如果大于1％，应该增加参数shared_pool_size的值:
Select sum(pins) "executions", sum(reloads) "cache misses",sum(reloads)/sum(pins)*100 from v$librarycache;
reloads/pins>1%有两种可能:
一种是library cache空间不足
一种是sql中引用的对象不合法
查看现shared_pool_size的值
show parameter shared_pool_size
更改shared_pool_size的值
Alter System set shared_pool_size=2048M;
-----------------------------------------------------------------------------------------
SQL语句的硬解释
SQL语句运行过程
1).使用hash算法得到sql语句的hash_value值
2).如果hash_value值在内存中，叫做命中执行软解析
3).如果hash_value值不存在，执行硬解析
4).语法解析，查看是否有错误
5).语意解析，查看权限是否符合
6).若有视图，取出视图的定义
7).进行sql语句的自动改写，如将子查询改写为连接
8).选择最优的执行计划
9).变量绑定
10).运行执行计划
11).返回结果给用户
因为软解析是从此11步骤中第9步开始的，因此软解析比硬解析节约大量的系统开销，应该尽量降低硬解析的次数
检查v$librarycache中sql area的gethitratio是否超过90%，如果未超过90%，应该检查应用代码，提高应用代码的效率：
Select gethitratio*100 from v$librarycache where namespace='SQL AREA';
（此值是数据库长期运行后，才能判断）
查看某个session的硬解释个数：
select a.sid,to_char(a.value) from v$sesstat a,v$session b ,v$statname c where a.sid=b.sid and a.statistic#=c.statistic# and a.sid = 6110 and c.name='parse count (hard)';
SID|TO_CHAR(A.VALUE)
------------------------|----------------------------------------
6110|12
-----------------------------------------------------------------------------------------
回滚段的争用情况：
select name,waits,gets,waits/gets ratio from v$rollstat a,v$rollname b where a.usn=b.usn;
对含有回滚段块的缓冲区的争用也会影响到对回滚段的争用。这可以通过查询动态性能表V$WAITSTAT来检测是否存在对回滚段的争用，例如：
SELECT class,count FROM V$WAITSTAT WHERE class IN('system undo header','system undo block','undo header','undo block');
其中参数含义如下：
◆ system undo header：对含有SYSTEM回滚段标题块的缓冲区的等待次数。
◆ system undo block：对含有SYSTEM回滚段非标题块的缓冲区的等待次数。
◆ undo header：对含有非SYSTEM回滚段标题块的缓冲区的等待次数。
◆ undo block：对含有非SYSTEM回滚段非标题块的缓冲区的等待次数。
如果任何等待次数大于总请求数的1%，则应创建更多的回滚段来减少竞争，可以周期性地检查这些统计数字，并将它与总的请求数据的次数作比较。
总的请求数据次数可用如下语句求出:
SELECT SUM(value) FROM V$SYSSTAT WHERE name IN('db block gets','consistent gets');
-----------------------------------------------------------------------------------------
监控数据库性能的语句
下面是一些监控数据库性能的语句：
监控数据库性能的SQL语句
1. 监控事例的等待
select event,
sum(decode(wait_Time,0,0,1)) "Prev",
sum(decode(wait_Time,0,1,0)) "Curr",
count(*) "Tot"
from v$session_Wait
group by event
order by 4;
2. 回滚段的争用情况
select name, waits, gets, waits/gets "Ratio"
from v$rollstat a, v$rollname b
where a.usn = b.usn;
3. 监控表空间的 I/O 比例
select df.tablespace_name name,df.file_name "file",f.phyrds pyr,
f.phyblkrd pbr,f.phywrts pyw, f.phyblkwrt pbw
from v$filestat f, dba_data_files df
where f.file# = df.file_id
order by df.tablespace_name;
4. 监控文件系统的 I/O 比例
select substr(a.file#,1,2) "#", substr(a.name,1,30) "Name",
a.status, a.bytes, b.phyrds, b.phywrts
from v$datafile a, v$filestat b
where a.file# = b.file#;
5.在某个用户下找所有的索引
select user_indexes.table_name,
user_indexes.index_name,
uniqueness,
column_name
from user_ind_columns, user_indexes
where user_ind_columns.index_name = user_indexes.index_name and
user_ind_columns.table_name = user_indexes.table_name
order by user_indexes.table_type, user_indexes.table_name,
user_indexes.index_name, column_position;
6. 监控 SGA 的命中率
select a.value + b.value "logical_reads",
c.value "phys_reads",
round(100 * ((a.value+b.value)-c.value) / (a.value+b.value)) "BUFFER HIT RATIO"
from v$sysstat a, v$sysstat b, v$sysstat c
where a.statistic# = 38 and b.statistic# = 39 and
c.statistic# = 40;
7. 监控 SGA 中字典缓冲区的命中率
select parameter, gets,Getmisses ,
getmisses/(gets+getmisses)*100 "miss ratio",
(1-(sum(getmisses)/ (sum(gets)+sum(getmisses))))*100 "Hit ratio"
from v$rowcache
where gets+getmisses <>0
group by parameter, gets, getmisses;
8. 监控 SGA 中共享缓存区的命中率，应该小于1%
select sum(pins) "Total Pins", sum(reloads) "Total Reloads",
sum(reloads)/sum(pins) *100 libcache
from v$librarycache;
select sum(pinhits-reloads)/sum(pins) "hit radio",
sum(reloads)/sum(pins) "reload percent"
from v$librarycache;
9. 显示所有数据库对象的类别和大小
select type,
count(name) num_instances,
sum(source_size) source_size,
sum(parsed_size) parsed_size,
sum(code_size) code_size,
sum(error_size) error_size,
sum(source_size) +sum(parsed_size) +sum(code_size) +sum(error_size) size_required
from dba_object_size
group by type
order by 1;
10. 监控 SGA 中重做日志缓存区的命中率，应该小于1%
SELECT name, gets, misses, immediate_gets, immediate_misses,
Decode(gets,0,0,misses/gets*100) ratio1,
Decode(immediate_gets+immediate_misses,0,0,
immediate_misses/(immediate_gets+immediate_misses)*100) ratio2
FROM v$latch
WHERE name IN ('redo allocation', 'redo copy');
11. 监控内存和硬盘的排序比率，最好使它小于 .10，增加 sort_area_size
SELECT name, value
FROM v$sysstat
WHERE name IN ('sorts (memory)', 'sorts (disk)');
12. 监控当前数据库谁在运行什么SQL语句
SELECT osuser, username, sql_text
from v$session a, v$sqltext b
where a.sql_address =b.address order by address, piece;
13. 监控字典缓冲区
SELECT (SUM(PINS - RELOADS)) / SUM(PINS) "LIB CACHE"
FROM V$LIBRARYCACHE;
SELECT (SUM(GETS - GETMISSES - USAGE - FIXED)) / SUM(GETS) "ROW CACHE"
FROM V$ROWCACHE;
SELECT SUM(PINS) "EXECUTIONS", SUM(RELOADS) "CACHE MISSES WHILE EXECUTING"
FROM V$LIBRARYCACHE;
后者除以前者,此比率小于1%,接近0%为好。
SELECT SUM(GETS) "DICTIONARY GETS",SUM(GETMISSES) "DICTIONARY CACHE GET MISSES"
FROM V$ROWCACHE;
14. 找ORACLE字符集
select * from sys.props$ where name='NLS_CHARACTERSET';
15. 监控 MTS
select busy/(busy+idle) "shared servers busy" from v$dispatcher;
此值大于0.5时，参数需加大
select sum(wait)/sum(totalq) "dispatcher waits" from v$queue where type='dispatcher';
select count(*) from v$dispatcher;
select servers_highwater from v$mts;
servers_highwater接近mts_max_servers时，参数需加大
16. 碎片程度
select tablespace_name,count(tablespace_name)
from dba_free_space
group by tablespace_name
having count(tablespace_name)>10;
alter tablespace name coalesce;
alter table name deallocate unused;
create or replace view ts_blocks_v as
select tablespace_name,block_id,bytes,blocks,
segment_name
from dba_free_space
union all
select tablespace_name,block_id,bytes,blocks,
segment_name
from dba_extents;
select * from ts_blocks_v;
select tablespace_name,sum(bytes),max(bytes),count(block_id)
from dba_free_space
group by tablespace_name;
查看碎片程度高的表
SELECT segment_name table_name , COUNT(*) extents
FROM dba_segments
WHERE owner NOT IN ('SYS', 'SYSTEM')
GROUP BY segment_name
HAVING COUNT(*) = (SELECT MAX(COUNT(*)) FROM dba_segments GROUP BY segment_name);
17. 表、索引的存储情况检查
select segment_name,sum(bytes),count(*) ext_quan
from dba_extents
where tablespace_name='&tablespace_name' and
segment_type='TABLE'
group by tablespace_name,segment_name;
select segment_name,count(*)
from dba_extents
where segment_type='INDEX' and owner='&owner'
group by segment_name;
18、找使用CPU多的用户session
12是cpu used by this session
select a.sid,spid,status,substr(a.program,1,40) prog,
a.terminal,osuser,to_char(value/60/100) value
from v$session a,v$process b,v$sesstat c
where c.statistic#=12 and
c.sid=a.sid and
a.paddr=b.addr
order by value desc;
跟据topas执行结果得到消耗cpu或者内存较高的Oracle进程pid，然后执行下面的sql语句，这样就能找出效率低下的sql语句：
select z.SQL_TEXT from v$process x, v$session y, v$sqltext z where x.ADDR = y.PADDR
and y.SQL_ADDRESS = z.ADDRESS and x.SPID = 'pid值' ;
19.对可疑/性能不好的Server Process来进行Trace.,可以用tkprof来分析Trace的结果.
(1) start_trc:
#!/bin/ksh
if (( $# != 1 ))
then
echo Usuage: start_trc pid
fi
sid_serial=$(print "
connect / as sysdba;
set heading off;
set feedback off;
select a.sid,a.serial# from v\$session a,v\$process b where a.paddr=b.addr and b.spid=$1;
exit;
" | sqlplus -s /nolog | grep -v 'Connected' | sed -e 's/\([0-9]\{1,\}\)/\1,/' -e '/^$/d' )
if [[ -z $sid_serial ]]
then
print "Seems that this process $1 is not an Oracle process!"
exit 1
fi
print "
connect / as sysdba;
execute dbms_system.set_sql_trace_in_session($sid_serial,true);
exit;
" | sqlplus -s /nolog
(2) stop_trc:
#!/bin/ksh
if (( $# != 1 ))
then
echo Usuage: stop_trc pid
fi
sid_serial=$(print "
connect / as sysdba;
set heading off;
set feedback off;
select a.sid,a.serial# from v\$session a,v\$process b where a.paddr=b.addr and b.spid=$1;
exit;
" | sqlplus -s /nolog | grep -v 'Connected' | sed -e 's/\([0-9]\{1,\}\)/\1,/' -e '/^$/d' )
if [[ -z $sid_serial ]]
then
print "Seems that this process $1 is not an Oracle process!"
exit 1
fi
print "
connect / as sysdba;
execute dbms_system.set_sql_trace_in_session($sid_serial,false);
exit;
" | sqlplus -s /nolog
20.查看Lock
SELECT sn.username, m.sid, m.type,
DECODE(m.lmode, 0, 'None',
1, 'Null',
2, 'Row Share',
3, 'Row Excl.',
4, 'Share',
5, 'S/Row Excl.',
6, 'Exclusive',
lmode, ltrim(to_char(lmode,'990'))) lmode,
DECODE(m.request,0, 'None',
1, 'Null',
2, 'Row Share',
3, 'Row Excl.',
4, 'Share',
5, 'S/Row Excl.',
6, 'Exclusive',
request, ltrim(to_char(m.request,'990'))) request,
m.id1, m.id2
FROM v$session sn, v$lock m
WHERE (sn.sid = m.sid AND m.request != 0) OR
( sn.sid = m.sid AND
m.request = 0 AND
lmode != 4 AND
(id1, id2) IN (
SELECT s.id1, s.id2
FROM v$lock s
WHERE request != 0 AND
s.id1 = m.id1 AND
s.id2 = m.id2
)
)
ORDER BY id1, id2, m.request;
select l.sid,s.serial#,s.username,s.terminal,
decode(l.type,'RW','RW - Row Wait Enqueue',
'TM','TM - DML Enqueue',
'TX','TX - Trans Enqueue',
'UL','UL - User',l.type||'System') res,
substr(t.name,1,10) tab,u.name owner,
l.id1,l.id2,
decode(l.lmode,1,'No Lock',
2,'Row Share',
3,'Row Exclusive',
4,'Share',
5,'Shr Row Excl',
6,'Exclusive',null) lmode,
decode(l.request,1,'No Lock',
2,'Row Share',
3,'Row Excl',
4,'Share',
5,'Shr Row Excl',
6,'Exclusive',null) request
from v$lock l, v$session s,
sys.user$ u,sys.obj$ t
where l.sid = s.sid and
s.type != 'BACKGROUND' and
t.obj# = l.id1 and
u.user# = t.owner#;
----------------------------------------------------------------------------------------------------
调用oracle顾问，分析SQL语句，该怎样优化
declare
l_task_id varchar2(20);
l_sql varchar2(2000);
begin
l_sql := 'SQL语句';
dbms_sqltune.drop_tuning_task ('FOLIO_COUNT');
l_task_id := dbms_sqltune.create_tuning_task (
sql_text => l_sql,
user_name => 'ARUP',
scope => 'COMPREHENSIVE',
time_limit => 120,
task_name => 'FOLIO_COUNT'
);
dbms_sqltune.execute_tuning_task ('FOLIO_COUNT');
end;
/
这个包创建并执行了一个名为 FOLIO_COUNT 的调整任务。接下来，您将需要查看任务执行的结果（也就是说，查看建议）。
set serveroutput on size 999999
set long 999999
select dbms_sqltune.report_tuning_task ('FOLIO_COUNT') from dual;
-----------------------------------------------------------------------------------------------------
查看数据库正在运行的SQL：
set line 1000
set linesize 256
set pagesize 1000
set long 999999
set heading off
方法1。select user_name,sql_text
from v$open_cursor
where sid in (select sid from (select sid,serial#,username,program
from v$session
where status='ACTIVE'));
方法2。select z.sql_text from v$process x,v$session y,v$sqltext z where x.addr=y.paddr and y.sql_address=z.address and x.spid<>0 order by z.piece;
-----------------------------------------------------------------------------------------------------
查看锁表的方法
1。select c.sid,c.serial#,c.program,c.username,b.owner,c.event,a.locked_mode,b.object_name from
v$locked_object a,dba_objects b,v$session c where a.object_id=b.object_id and a.session_id=c.sid;
---------
2。SELECT B.OWNER, B.OBJECT_NAME, A.SESSION_ID, A.LOCKED_MODE
FROM V$LOCKED_OBJECT A, DBA_OBJECTS B
WHERE B.OBJECT_ID = A.OBJECT_ID;
解锁方法：
alter system kill session 'sid,serial#';
-----------------------------------------------------------------------------------------------------
查看SGA区剩余可用内存
col OBJECT_NAME format a20
col 自由空间百分比(%) format 90.99 --格式化，把小数点对齐为两位
select name,
sgasize/1024/1024 "Allocated(M)",
bytes/1024/1024 "自由空间(M)",
round(bytes/sgasize*100, 2) "自由空间百分比(%)"
from (select sum(bytes) sgasize from sys.v_$sgastat) s, sys.v_$sgastat f
where f.name = 'free memory';
-----------------------------------------------------------------------------------------------------
查看各个表空间占用磁盘情况：
col tablespace format a20
select
b.file_id 文件ID号,
b.tablespace_name 表空间名,
b.bytes/1024/1024 MB,
(b.bytes-sum(nvl(a.bytes,0)))/1024/1024 已使用MB,
sum(nvl(a.bytes,0))/1024/1024 剩余空间MB,
sum(nvl(a.bytes,0))/(b.bytes)*100 剩余百分比
from dba_free_space a,dba_data_files b
where a.file_id=b.file_id
group by b.tablespace_name,b.file_id,b.bytes
order by b.file_id
/
-----------------------------------------------------------------------------------------------------
查看数据库后台进程：
desc v$bgprocess
名称 空? 类型
----------------------------------------- -------- -----------------
PADDR RAW(4) --进程状态对象地址
NAME VARCHAR2(5) --后台进程名称
DESCRIPTION VARCHAR2(64) --后台进程描述
ERROR NUMBER --后台进程运行中所遇到的错误数
col DESCRIPTION format a40
select * from v$bgprocess where paddr<>'00';
PADDR NAME DESCRIPTION ERROR
-------- ----- ---------------------------------------------------------------- ----------
66D90CE4 PMON process cleanup 0
66D91038 DBW0 db writer process 0 0
66D9138C LGWR Redo etc. 0
66D916E0 CKPT checkpoint 0
66D91A34 SMON System Monitor Process 0
66D91D88 RECO distributed recovery 0
---------------------------------------------
SMON 系统进程
PMON 用户进程
DBWR 数据库写入进程
LGWR 日志写入进程
CKPT 检查点进程 用来减少实例恢复所需时间，
init.ora中，log_checkpoint_interval = 10000，此参数设置检查点出现的频度
ARCH 归档进程 将联机重做日志拷贝到磁盘或磁带，即将联机重做日志归档
RECO 恢复进程 用于分布式数据库中的分布式处理，
init.ora中，distributed_transactions = 10，此参数大于0时才被建立
SNPn 快照进程 数量取决于init.ora中参数job_queue_processes = 4
LCKn 锁进程 可选项，用于并行服务器
Dnnn 调度进程 可选项，仅用于多线程服务器
-----------------------------------------------------------------------------------------------------
查看什么时间有哪些数据库对象结构被修改过:
col OBJECT_NAME format a20
SELECT OBJECT_NAME, --对象名
OBJECT_TYPE, --对象类型
TO_CHAR(CREATED, 'YYYY-Mon-DD HH24:MI') CREATE_TIME, --创建时间
TO_CHAR(LAST_DDL_TIME, 'YYYY-Mon-DD HH24:MI') MOD_TIME, --修改时间
TIMESTAMP, --时间戳
STATUS --状态
FROM USER_OBJECTS
WHERE to_char(LAST_DDL_TIME,'yyyymmdd')>'&日期变量';
-----------------------------------------------------------------------------------------------------
查看用户表上的约束内容:
select a.OWNER 用户名,
a.CONSTRAINT_NAME 约束名称,
a.CONSTRAINT_TYPE 约束类型,
a.TABLE_NAME 表名,
b.COLUMN_NAME 列名,
a.SEARCH_CONDITION 约束内容
from USER_CONSTRAINTS a,USER_CONS_COLUMNS b
where a.CONSTRAINT_NAME=b.CONSTRAINT_NAME;
-----------------------------------------------------------------------------------------------------
增加表空间的大小:
1、为这个表空间增加一个数据文件
alter tablespace 表空间名 add datafile '/u1/oradata/userdata_002.ora' size 50m; --Unix中
alter tablespace 表空间名 add datafile 'c:\oradata\userdata_002.ora' size 50m; --Windows NT中
2、重新调整数据文件的大小
alter database datafile '/u1/oradata/userdata_001.ora' resize 50M; --Unix中
alter database datafile 'c:\oradata\userdata_002.ora' resize 50M; --Windows NT中
-----------------------------------------------------------------------------------------------------
sql执行计划及分析：
1.set autotrace traceonly
sql语句
set autotrace off
2.explain plan for
SQL语句
select * from TABLE(dbms_xplan.display());
查看SQL运行时间
3.set timing on
sql语句
-----------------------------------------------------------------------------------------------------
查看表的索引：
1.select owner,index_name,index_type,TABLE_OWNER,status,table_name,UNIQUENESS from dba_indexes where table_name='表名' and OWNER='用户名';
2.select index_owner 索引拥有者,INDEX_NAME 索引名,COLUMN_NAME 索引字段 ,COLUMN_POSITION
from all_ind_columns
where index_name in(select INDEX_NAME from all_indexes where upper(table_name) = upper('HSC_INF_GDECINFO') )
order by index_owner,INDEX_NAME,COLUMN_POSITION;
查看存储过程及触发器过程
select text from dba_source where type='PROCEDURE' and name='存储过程名' and owner='JMYY';
查看表所属表空间：
select tablespace_name from all_tables where table_name=upper('表名') and owner='用户';
查看指定表空间的空间大小,表空间总大小GB
1.select OWNER,SUM(BYTES)/1024/1024/1024 G from dba_segments where tablespace_name='表空间名' and owner in ('用户','用户') GROUP BY OWNER ;
---------------------------------------------------
查看可用表空间大小,表空间总大小MB
2. select (select ROUND(SUM(BYTES) / (1024 * 1024), 2) "freeMB" from SYS.DBA_FREE_SPACE where tablespace_name='表空间名' ) "FreeMB",(select ROUND(SUM(BYTES) / (1024 * 1024), 2) "总大小" from SYS.DBA_DATA_FILES where tablespace_name='表空间名') "TotalMB" from dual;
SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
D.TOT_GROOTTE_MB "表空间大小(M)",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,
2),
'990.99') "使用比",
F.TOTAL_BYTES "空闲空间(M)",
F.MAX_BYTES "最大块(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY F.TABLESPACE_NAME;
根据条件高效删除重复的记录
（一次只删除一份：如相同的记录有两条，一次只删除一条，保留一条）
delete from 表名 where rowid in(select max(rowid) from 表名 where 条件 group by 有重记录的字段名 having count(有重复记录的字段名)>1 ) ;
备份一张表的数据到一张新表
create table testa as select * from testb ;
注意，testb表的数据全部放入testa中，但没有索引*
-----------------------------------------------------------------------------------------------------
输出oracle性能报告(AWRRPT.sql AWR报告)
cd $ORACLE_HOME/rdbms/admin/
export ORACLE_SID='要导出报告的数据库实例名称' 如testdb
echo $ORACLE_SID
testdb
sqlplus 用户名/密码@testdb
SQL> @awrrpt.sql
根据提示导出AWRRPT报告
修改收集时间间隔：
BEGIN
DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
interval => 30,
retention => 7*24*60);
END;
/
这样就可以调整收集间隔为30分钟
-----------------------------------------------------------------------------------------------------
启停oracle定时调度任务
col PROGRAM_NAME for a50 //设置字段长度
select OWNER,JOB_NAME,PROGRAM_NAME,STATE,enabled from DBA_SCHEDULER_JOBS; // 查询调度任务
停止调度
exec DBMS_SCHEDULER.DISABLE ('PURGE_LOG',true);
exec DBMS_SCHEDULER.DISABLE ('GATHER_STATS_JOB',true);
exec DBMS_SCHEDULER.DISABLE ('AUTO_SPACE_ADVISOR_JOB',true);
启动调度：
exec DBMS_SCHEDULER.INABLE ('AUTO_SPACE_ADVISOR_JOB',true);
-----------------------------------------------------------------------------------------------------
查看回滚相关系统参数
show parameter undo
查看回滚段可用表空间：
select a.tablespace_name,total,free,total-free used from
( select tablespace_name,sum(bytes)/1024/1024 totalMB from dba_data_files
group by tablespace_name) a,
( select tablespace_name,sum(bytes)/1024/1024 freeMB from dba_free_space
group by tablespace_name) b
where a.tablespace_name=b.tablespace_name and b.tablespace_name='回滚段表空间';
------------
回滚段的统计信息：
SELECT n.name,s.extents,s.rssize,s.optsize,s.hwmsize,s.xacts,s.status
FROM v$rollname n,v$rollstat s
WHERE n.usn=s.usn;
回滚段的当前活动事务：
SELECT s.username,t.xidusn,t.ubafil,t.ubablk,t.used_ublk
FROM v$session s,v$transaction t
WHERE s.saddr=t.ses_addr;
查看哪些session正在使用哪些回滚段
SELECT r.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
WHERE t.addr = s.taddr and t.xidusn = r.usn
ORDER BY t.cr_get,t.phy_io
-----------------------------------------------------------------------------------------------------
通过表分析判断索引是否失效及解决方法
查看库表中未做过表分析或者表分析的日期在10之前的记录总数
SELECT T.TABLE_NAME, T.NUM_ROWS
FROM USER_TABLES T
WHERE (T.LAST_ANALYZED IS NULL OR T.LAST_ANALYZED < SYSDATE - 10)
AND T.TABLE_NAME = '物理表名';
查看库表中现在的记录数
SELECT COUNT(*) FROM CS_REC_ONEKEYLOG;
如果两者记录数相差距大则需进行表分析（更新表的状态）：
analyze table 用户.物理表名 delete system statistics;
查看表的别名及所属用户
select * from dba_synonyms where synonym_name='物理表名';
查看表是否存在
select object_name,object_type,owner from dba_objects where object_name='表名';
-----------------------------------------------------------------------------------------------------
数据库启停方法：
1。查看当前的用户进程（PMON）:在数据库所在的服务器执行：ps -ef|grep oracle|grep pmon
2。修改/定义ORACLE_SID :export ORACLE_SID=数据库实例名
echo $ORACLE_SID
3。sqlplus '/as sysdba'
4。停止数据库：shutdown abort
启动数据库
1，2，3步同上：
4。startup
-----------------------------------------------------------------------------------------------------
查看每分钟处理记录数SQL
select to_char(时间字段名, 'yyyymmddhh24:mi'), count(*)
from jmyy.reception
where 时间字段名 > to_date('2010-12-13 19:40:00', 'yyyy-mm-dd hh24:mi:ss')
and 时间字段名 < to_date('2010-12-13 20:20:00', 'yyyy-mm-dd hh24:mi:ss')
group by to_char(时间字段名, 'yyyymmddhh24:mi')
order by to_char(时间字段名, 'yyyymmddhh24:mi');
-----------------------------------------------------------------------------------------------------
获取随机数记录
SELECT * FROM (SELECT * FROM 表名 WHERE ROWNUM<=50000 ORDER BY
dbms_random.random) WHERE ROWNUM<=1
其中： ROWNUM<=50000是先指定一个范围，可以避免因为记录太多导致执行SQL后响应很慢；而ROWNUM<=1是限制最终查询出来的随机数数量．
-----------------------------------------------------------------------------------------------------
查看本用户下的各种对象的SQL脚本
表：
select * from cat;
select * from tab;
select table_name from user_tables;
视图：
select text from user_views where view_name=upper('&view_name');
索引：
select index_name,table_owner,table_name,tablespace_name,status from user_indexes order by table_name;
触发器：
select trigger_name,trigger_type,table_owner,table_name,status from user_triggers;
快照：
select owner,name,master,table_name,last_refresh,next from user_snapshots order by owner,next;
同义词：
select * from syn;
序列：
select * from seq;
数据库链路：
select * from user_db_links;
约束限制：
select TABLE_NAME,CONSTRAINT_NAME,SEARCH_CONDITION,STATUS from user_constraints;
本用户读取其他用户对象的权限：
select * from user_tab_privs;
本用户所拥有的系统权限：
select * from user_sys_privs;
用户：
select * from all_users order by user_id;
表空间剩余自由空间情况：
select tablespace_name,sum(bytes) 总字节数,max(bytes),count(*) from dba_free_space group by tablespace_name；
数据字典：
select table_name from dict order by table_name;
锁及资源信息：
select * from v$lock;不包括DDL锁
数据库字符集：
select name,value$ from props$ where name='NLS_CHARACTERSET';
inin.ora参数：
select name,value from v$parameter order by name;
SQL共享池：
select sql_text from v$sqlarea;
数据库：
select * from v$database
控制文件：
select * from V$controlfile;
重做日志文件信息：
select * from V$logfile;
来自控制文件中的日志文件信息：
select * from V$log;
来自控制文件中的数据文件信息：
select * from V$datafile;
NLS参数当前值：
select * from V$nls_parameters;
ORACLE版本信息：
select * from v$version;
描述后台进程：
select * from v$bgprocess;
查看版本信息：
select * from product_component_version;
-----------------------------------------------------------------------------------------------------
oracle数据库关闭与重启：
export ORACLE_SID=实例名称
sqlplus 'as /sysdba'
shutdown 加关闭类型
:
SHUTDOWN NORMAL：不允许新的连接、等待会话结束、等待事务结束、做一个检查点并关闭数据文件。启动时不需要实例恢复。
SHUTDOWN TRANSACTIONAL：不允许新的连接、不等待会话结束、等待事务结束、做一个检查点并关闭数据文件。启动时不需要实例恢复。
SHUTDOWN IMMEDIATE：不允许新的连接、不等待会话结束、不等待事务结束、做一个检查点并关闭数据文件。没有结束的事务是自动rollback的。启动时不需要实例恢复。
SHUTDOWN ABORT：不允许新的连接、不等待会话结束、不等待事务结束、不做检查点且没有关闭数据文件。启动时自动进行实例恢复。
----
启动：
export ORACLE_SID=实例名称
sqlplus 'as /sysdba'
startup
-------------------------------------
SHUTDOWN NORMAL：不允许新的连接、等待会话结束、等待事务结束、做一个检查点并关闭数据文件。启动时不需要实例恢复。
SHUTDOWN TRANSACTIONAL：不允许新的连接、不等待会话结束、等待事务结束、做一个检查点并关闭数据文件。启动时不需要实例恢复。
SHUTDOWN IMMEDIATE：不允许新的连接、不等待会话结束、不等待事务结束、做一个检查点并关闭数据文件。没有结束的事务是自动rollback的。启动时不需要实例恢复。
SHUTDOWN ABORT：不允许新的连接、不等待会话结束、不等待事务结束、不做检查点且没有关闭数据文件。启动时自动进行实例恢复。
----
启动：
export ORACLE_SID=实例名称
sqlplus 'as /sysdba'
startup
---------------------------------
1、--统计游标总数
select o.sid, osuser, PROCESS,PROGRAM, count(*) num_curs
from v$open_cursor o, v$session s
where osuser='lzm' and user_name = 'ZHZW' and o.sid=s.sid
group by o.sid, osuser, PROCESS,PROGRAM
order by num_curs desc
*************************************************************************************
2、--查看某个程序的所有游标语句
select SQL_TEXT
from v$open_cursor o, v$session s
where user_name = 'ZHZW' and o.sid=s.sid
and PROCESS='2146392'
*************************************************************************************
3、--知道某一特定sql查询打开的游标数
select a.sql_text, a.sql_id, b.count, to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') from v$sqlarea a,
(select sql_id, count(*) count from v$open_cursor group by sql_id) b
where a.sql_text like 'select integralcyc%' and a.sql_id = b.sql_id;
*************************************************************************************
4、根据程序名查询打开的游标数
select p.SQL_TEXT,count(*) ,sysdate from v$session s ,V$OPEN_CURSOR p
where s.SID=p.SID and s.program like'Date%' group by p.SQL_TEXT order by p.SQL_TEXT;
*************************************************************************************
5、查询表的分区：
select * from user_tab_partitions t where t.table_name = 'HSC_REC_PRODUCT';
*************************************************************************************
6、添加分区：
alter table HSC_REC_PRODUCT add PARTITION HSC_REC_PRODUCT16 values less than(TO_DATE('2013-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'));
*************************************************************************************
7.根据进程PID号查找SQL：
select sql_fulltext from V$sql a where a.sql_id=(select sql_id from V$session b where b.paddr=(select addr from V$process c where c.spid=进程ID));
获取建表语句：
select to_char(dbms_metadata.get_ddl('TABLE','GDHSC_SUBS_PRODUCT')) from dual;
获取存储过程定义：
select text from user_source where type='PROCEDURE' and name='PROCEDURE_NAME';
触发器：
select text from user_source where type='TRIGGER' and name='TRIGGER_NAME';
-- 查看视图的定义语句
-- select dbms_metadata.get_ddl('VIEW','V_CANSALETICKET') from dual