ORACLE的SQL语句记录

一、基础SQL

1.1 数据库内存

select name,bytes/1024/1024 as "Size(M)",resizeable from v$sgainfo;

1.2 修改内存大小

alter system set sga_max_size=10240m scope=spfile;

1.3 查看表空间

SELECT a.tablespace_name "表空间名",
       total "表空间大小",
       free "表空间剩余大小",
       (total - free) "表空间使用大小",
       total / (1024 * 1024 * 1024) "表空间大小(G)",
       free / (1024 * 1024 * 1024) "表空间剩余大小(G)",
       (total - free) / (1024 * 1024 * 1024) "表空间使用大小(G)",
	   round((total - free) / total, 4) * 100 "使用率 %"
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 ;

1.4 扩展表空间

alter tablespace vgsm_db add datafile 'D:\ORADATA\VGSM\DATAFILE\VGSM16.DBF' 
size 4096m 
autoextend off;

1.5 模板

---

二、优化SQL语句

2.1 查看消耗磁盘读取最多的前10条SQL语句

select *
from (select v.sql_id,
        v.child_number,
        v.sql_text,
        v.elapsed_time,
        v.cpu_time,
        v.disk_reads,
        rank() over(order by v.disk_reads desc) elapsed_rank
     from v$sql v) a
where elapsed_rank <= 10;

2.2 查看总消耗时间最多的前10条SQL语句

select *
from (select v.sql_id,
        v.child_number,
        v.sql_text,
        v.elapsed_time,
        v.cpu_time,
        v.disk_reads,
        rank() over(order by v.elapsed_time desc) elapsed_rank
      from v$sql v) a
where elapsed_rank <= 10;

2.3 查看CPU消耗时间最多的前10条SQL语句

select *
from (select v.sql_id,
        v.child_number,
        v.sql_text,
        v.elapsed_time,
        v.cpu_time,
        v.disk_reads,
        rank() over(order by v.cpu_time desc) elapsed_rank
      from v$sql v) a
where elapsed_rank <= 10;