实例优化常用SQL

实例优化常用SQL

-- 查看SGA
Show sga;
select sum(value) from v$sga;
select pool, sum(bytes) from v$sgastat where pool = 'shared pool' group by pool;
select pool, bytes from v$sgastat where name = 'free memory';
/*********
识别'低效执行'的SQL语句
*********/
SELECT EXECUTIONS , DISK_READS, BUFFER_GETS,
ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2) Hit_radio,
ROUND(DISK_READS/EXECUTIONS,2) Reads_per_run,
SQL_TEXT
FROM V$SQLAREA
WHERE EXECUTIONS>0
AND BUFFER_GETS > 0
AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS < 0.8
ORDER BY 4 DESC;

/*********
数据缓冲区命中率
*********/
SELECT a.VALUE + b.VALUE logical_reads,  c.VALUE phys_reads, 
round(100*(1-c.value/(a.value+b.value)),4) hit_ratio 
FROM v$sysstat a,v$sysstat b,v$sysstat c 
WHERE a.NAME='db block gets'  AND b.NAME='consistent gets'  AND c.NAME='physical reads';

/*********
共享池命中率
*********/
select sum(pinhits)/sum(pins) * 100 "hit radio" from v$librarycache;

/*********
排序部分
如果发现 sorts (disk) / (sorts (memory) + sorts (disk)) 的比例过高，则通常意味着
sort_area_size 部分内存较小，可考虑调整相应的参数
*********/
select a.value disk_sort,b.value memory_sort,round((1 - a.value / (a.value + b.value)) * 100,4) sort_ratio
from v$sysstat a,v$sysstat b
where a.name = 'sorts (disk)'
and   b.name = 'sorts (memory)';

/*********
log_buffer
如果 redo buffer allocation retries / redo entries 的比例超过 1%，就可以考虑增大 log_buffer。
*********/
select a.value redo_entries,b.value redo_buffer_allocation_retries,round((1 - b.value / a.value) * 100,4) log_buffer_ratio
from v$sysstat a,v$sysstat b
where a.name = 'redo entries'
and   b.name = 'redo buffer allocation retries';
/*********
数据库缓冲区高速缓存中有什么
*********/
select o.owner, o.object_type, o.object_name, count(b.objd)
from v$bh b, dba_objects o
where b.objd = o.object_id
group by o.owner, o.object_type, o.object_name
having count(b.objd) > (select to_number(value*.05)
   from v$parameter
   where name = 'db_block_buffers');

-- 把对象留在内存中
exec dbms_shared_pool.keep('PNP2.GETOBJECTID');

-- 刷新共享池
alter system flush shared_pool;

-- 查看内存中的对象
col owner for a10
col name for a30
col type for a10
select owner, name, type, sharable_mem, kept
from v$db_object_cache
where type in ('PACKAGE', 'PACKAGE BODY', 'FUNCTION', 'PROCEDURE')
and kept != 'NO';

/*********
优化 Oracle 数据库的性能应该从以下几个方面考虑：
（1）数据库的设计：是调优的关键，如果设计本身有局限，性能很难有很好的优化。
（2）应用程序：对应用程序的调整，要求开发人员根据设计调整 sql 语句，有效的利用索引，
写规范的 sql 语句，利用绑定变量，以便重用 shared_pool 中的执行计划。
（3）MEMORY：调整 SGA，提高命中率，有效利用内存。
（4）I/O：根据应用将数据库表空间置于不同的磁盘，或利用 RAID 技术，减少I/O冲突。
（5）冲突焦点：监控应用中有冲突的焦点，采取相应措施。
（6）操作系统：减少操作系统的不当配置对数据库的性能产生的影响。
*********/