ORACLE应用调优sql
select *
from (select sa.SQL_TEXT,
sa.SQL_FULLTEXT,
sa.EXECUTIONS "执行次数",
round(sa.ELAPSED_TIME / 1000000, 2) "总执行时间",
round(sa.ELAPSED_TIME / 1000000 / sa.EXECUTIONS, 2) "平均执行时间",
sa.COMMAND_TYPE,
sa.PARSING_USER_ID "用户ID",
u.username "用户名",
sa.HASH_VALUE
from v$sqlarea sa
left join all_users u
on sa.PARSING_USER_ID = u.user_id
where sa.EXECUTIONS > 0
order by (sa.ELAPSED_TIME / sa.EXECUTIONS) desc)
where rownum <= 50;
select *
from (select s.SQL_TEXT,
s.EXECUTIONS "执行次数",
s.PARSING_USER_ID "用户名",
rank() over(order by EXECUTIONS desc) EXEC_RANK
from v$sql s
left join all_users u
on u.USER_ID = s.PARSING_USER_ID) t
where exec_rank <= 100;
三、查看oracle数据库允许的最大连接数和当前连接数
1、查看当前的数据库连接数
select count(*) from v$process ; --当前的数据库连接数
2、数据库允许的最大连接数
select value from v$parameter where name ='processes'; --数据库允许的最大连接数
3、修改数据库最大连接数
alter system set processes = 300 scope = spfile; --修改最大连接数:
4、关闭/重启数据库
shutdown immediate; --关闭数据库
startup; --重启数据库
5、查看当前有哪些用户正在使用数据
select osuser, a.username, cpu_time/executions/1000000||'s', b.sql_text, machine
from v$session a, v$sqlarea b
where a.sql_address =b.address
order by cpu_time/executions desc; --查看当前有哪些用户正在使用数据
6、 --当前的session连接数
select count(*) from v$session --当前的session连接数
7、当前并发连接数
select count(*) from v$session where status='ACTIVE'; --并发连接数
v$process：
这个视图提供的信息，都是oracle服务进程的信息，没有客户端程序相关的信息
服务进程分两类，一是后台的，一是dedicate/shared server
pid, serial# 这是oracle分配的PID
spid 这才是操作系统的pid
program 这是服务进程对应的操作系统进程名
v$session：
这个视图主要提供的是一个数据库connect的信息，
主要是client端的信息，比如以下字段：
machine 在哪台机器上
terminal 使用什么终端
osuser 操作系统用户是谁
program 通过什么客户端程序，比如TOAD
process 操作系统分配给TOAD的进程号
logon_time 在什么时间
username 以什么oracle的帐号登录
command 执行了什么类型的SQL命令
sql_hash_value SQL语句信息
有一些是server端的信息：
paddr 即v$process中的server进程的addr
server 服务器是dedicate/shared
四、查询表空间情况
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 1;
五、查看表及lob对象占用情况
在ORACLE数据库中，LOB（Large Objects —— 大对象）是用来存储大量的二进制和文本数据的一种数据类型（一个LOB字段可存储可多达4GB的数据）。
select owner,segment_name,segment_type,bytes from dba_segments order by bytes desc;
发现排在前5的均为lob对象，总占用在100G左右。一般的表占用空间不会太高，基本不会超过10G（表中的lob对象是单独存储）。某些项目上，出现较大的lob对象也是正常的。
六、查询lob字段属于哪张表
lob对象占用高不一定意味着异常，得看lob属于业务表还是其他情况。
select owner,table_name,column_name,segment_name from dba_lobs where segment_name='SYS_LOB0000112457C00002$$';
查询发现这些lob对象均属于SYS_EXPORT_SCHEMA%这种表，像SYS_EXPORT_SCHEMA_01，SYS_EXPORT_FULL_01这种表，基本都是expdp方式导出备份产生的，正常导出完毕，oracle会自动清除，如果没有自动清除的话，说明导出过程中出现了异常中断情况。
select * from dba_tables where table_name like 'SYS_EXPORT_SCHEMA%';
通过查询dba_tables表，LAST_ANALYZED字段的时间都是在晚上备份时间，客户也确认过出现过导出异常，可确认是中断所致。
Oracle中清除BIN$开头的垃圾表
10g的新特性flashback闪回区。
当误删除某些表时，可以通过命令恢复回来：
flashback table tablename to before drop；
查看是否开启了闪回功能：
select flashback_on from v$database;
如果确定某些表确实可以删除，可以使用下面的语句，就不会出现BIN$开头的表了
drop table table_name purge;
-------------------------------------------------------------------------------------------------------
删除Bin开头的表,即已经drop但存在于回收站中的表：
-->
查询所有此类表SQL> select * from recyclebin where type='TABLE';
用来删除回收站中所有的表SQL> PURGE RECYCLEBIN --------这语句就能清除所有以BIN开头的残留文件
用来删除指定的表SQL> PURGE TABLE TABLE_NAME
用来闪回被删除的表SQL> FLASHBACK TABLE table_name TO BEFORE DROP;
-->
-------------------------------------------------------------------------------------------------------
可以在的Drop表时不产生Bin型表SQL> DROP TABLE "TableName" purge；
