DBMS_SHARED_POOL.KEEP('DBMS_DDL');
DBMS_SHARED_POOL.KEEP('DBMS_DESCRIBE');
DBMS_SHARED_POOL.KEEP('DBMS_LOCK');
DBMS_SHARED_POOL.KEEP('DBMS_OUTPUT');
DBMS_SHARED_POOL.KEEP('DBMS_PIPE');
DBMS_SHARED_POOL.KEEP('DBMS_SESSION');
DBMS_SHARED_POOL.KEEP('DBMS_SHARED_POOL');
DBMS_SHARED_POOL.KEEP('DBMS_STANDARD');
DBMS_SHARED_POOL.KEEP('DBMS_UTILITY');
DBMS_SHARED_POOL.KEEP('STANDARD');
END;
/
/* 解决索引赤色 */
1, 分析索引结构
ANALYZE INDEX &index_name VALIDATE STRUCTURE
2, 查询索引是否对称, 如果比率超过20%就应该重建索引
SELECT ROUND(del_lf_rows_len / lf_rows_len * 100) || '%' BALANCE_RATIO
FROM index_stats
WHERE name = UPPER('&index_name')
3, 重建索引
ALTER INDEX &index_name REBUILD ONLINE;
/* 定位数据库中的热点块 */
SELECT b.obj object, o.owner, o.object_name, o.object_type,
o.status, b.dbarfil file#, b.dbablk block#, b.tch touches
FROM x$bh b, dba_objects o
WHERE b.tch > 10
AND o.object_id = b.obj
ORDER BY b.tch DESC
/* 字典缓存的命中率 */
SELECT parameter, gets, getmisses, usage, (gets - getmisses) / gets WHEN 0 THEN NULL
ELSE gets END
FROM v$rowcache
/* 查询回闪区的容量 */
SELECT SUBSTR (name, 1, 30) name, space_limit AS total, space_used AS used,
space_used / space_limit AS pct_used, space_reclaimable AS reclaimable,
number_of_files AS files
FROM v$recovery_file_dest;
参数
db_recovery_file_dest_size --回闪区的大小
/* Oracle中对内存分配的建议 */
SELECT * FROM v$pga_target_advice
SELECT * FROM v$mttr_target_advice
SELECT * FROM v$px_buffer_advice
SELECT * FROM v$db_cache_advice
SELECT * FROM v$sga_target_advice
SELECT * FROM v$shared_pool_advice
SELECT * FROM v$java_pool_advice
SELECT * FROM v$streams_pool_advice
-- 查找低劣的SQL
/* RMAN */
crosscheck archivelog all;
delete expired archivelog all;
-- 查询隐含参数
SELECT a.indx, a.ksppinm, a.ksppdesc, a.ksppiflg,
a.ksppilrmflg, b.ksppstvl, b.ksppstdf, b.ksppstvf
FROM x$ksppi a, x$ksppcv b
WHERE a.indx = b.indx
AND ksppinm LIKE '\_%' ESCAPE '\'
--得到服务器ip
set serverout on
exec dbms_output.put_line(utl_inaddr.get_host_address);
-- 有关latch的
/* miss的百分比，不要超过1% */
SELECT name,
gets, misses * 100 / DECODE (gets, 0, 1, gets) misses,
spin_gets * 100 / DECODE (misses, 0, 1, misses) spins,
immediate_gets igets,
immediate_misses * 100 / DECODE (immediate_gets, 0, 1, immediate_gets) imisses
FROM v$latch
WHERE gets <> 0
ORDER BY gets DESC
SELECT a.name, a.gets gets,
a.misses * 100 /
DECODE(a.gets, 0, 1, a.gets) miss,
TO_CHAR(a.spin_gets * 100 / DECODE(a.misses, 0, 1, a.misses), '990.9') || TO_CHAR (a.sleep6 * 100 / DECODE (a.misses, 0, 1, a.misses), '90.9') cspins,
TO_CHAR (a.sleep1 * 100 / DECODE (a.misses, 0, 1, a.misses), '90.9') || TO_CHAR (a.sleep7 * 100 / DECODE (a.misses, 0, 1, a.misses), '90.9') csleep1,
TO_CHAR (a.sleep2 * 100 / DECODE (a.misses, 0, 1, a.misses), '90.9') || TO_CHAR (a.sleep8 * 100 / DECODE (a.misses, 0, 1, a.misses), '90.9') csleep2,
TO_CHAR (a.sleep3 * 100 / DECODE (a.misses, 0, 1, a.misses), '90.9') || TO_CHAR (a.sleep9 * 100 / DECODE (a.misses, 0, 1, a.misses), '90.9') csleep3,
TO_CHAR (a.