oracle数据库性能方面常用sql
1.清除数据库缓存
alter system flush shared_pool;
alter system flush buffer_cache;
alter system flush global_context;
2.sql方面
1).查看数据库每次执行耗时前10的sql
select
*
from (
select
v.sql_text as "sql语句",
v.elapsed_time as "sql执行耗时",
v.cpu_time as "CPU耗时",
v.disk_reads as "IO耗时",
v.executions as "执行次数",
round(v.elapsed_time/v.executions/1000000,2) as "每次执行时间/s"
rank()over(order by v.elapsed_time/v.executions desc) elapsed_rank
from
v$sql v
where
v.parsing_schema_name not in ('SYS','SYSTEM')
and v.command_type <> 47
and (v.module is null or v.module not like 'DBMS%')
) a
where
elapsed_rank <= 10
3.表方面
1)查询表空间信息
select
a.tablespace_name as "表空间名",
total as "表空间大小",
free as "表空间剩余大小",
(total - free) as "表空间使用大小",
total/(1024*1024*1024) as "表空间大小(G)",
free/(1024*1024*1024) as "表空间剩余大小(G)",
(total - free)/(1024*1024*1024) as "表空间使用大小(G)",
round((total-free)/total,4) * 100 as "使用率%"
from
(select tablespace_name,sum(bytes) free from dba_free_space group by tablespace_name) a
(select tablespace_name,sum(bytes) total from dba_data_files group by tablespace_name) b
where
a.tablespace_name = b.tablespace_name
order by
"使用率%" desc;
2)查询表扩容
select
file_name,
tablespace_name as "表空间名称",
autoextensible as "是否允许扩容",
maxbytes,
increment_by as "每次扩容大小"
from
dba_data_files
order by
increment_by desc;
3)查询表的使用频率
select
ss.owner,
ss.object_name as "表名",
ss.object_type,
sum(ss.values) as "次数"
from
v$segment_statistics ss
where
ss.object_type = 'TABLE'
group by
ss.owner,
ss.object_name,
ss.object_type
order by
"次数" desc;
4)查询数据量大于一百万的表
select
table_name as "表名",
to_char(num_rows,'999,999,999,999') as num_rows as "数据量"
from
all_tables
where
num_rows >= 1000000
order by
"数据量" desc;
4.其他
1)指定返回数据量
rownum < 10
如:
select
*
from
user u
where
u.name = '张三'
and rownum < 10;
2)使用指定索引
/+INDEX (表名 索引名)/
如:
select /+INDEX (u name_age)/
*
from
user u
where
u.name = '张三';