oracle优化脚本

oracle优化脚本

--查看PGA的最佳设计建议SQL语句
select PGA_TARGET_FOR_ESTIMATE / 1024 / 1024 PGAMB,
       PGA_TARGET_FACTOR,
       ESTD_PGA_CACHE_HIT_PERCENTAGE,
       ESTD_OVERALLOC_COUNT
  from v$pga_target_advice;

---SGA共享池优化建议语句
select t.SHARED_POOL_SIZE_FOR_ESTIMATE "SP",
       t.ESTD_LC_SIZE                  "EL",
       t.ESTD_LC_MEMORY_OBJECTS        "ELM",
       t.ESTD_LC_TIME_SAVED            "ELT",
       t.ESTD_LC_TIME_SAVED_FACTOR     "ELTS %",
       t.ESTD_LC_MEMORY_OBJECT_HITS    "ELMO"
  from v$shared_pool_advice t;

---SGA高速缓冲优化建议语句
SELECT size_for_estimate "target M",
       buffers_for_estimate,
       estd_physical_read_factor "physical_read %",
       estd_physical_reads
  FROM V$DB_CACHE_ADVICE
 WHERE name = 'DEFAULT'
   AND block_size =
       (SELECT value FROM V$PARAMETER WHERE name = 'db_block_size')
   AND advice_status = 'ON';

---根据linux进程，获取相应的执行SQL语句
SELECT sql_text
  FROM v$sqltext a
 WHERE (a.hash_value, a.address) IN
       (SELECT DECODE(sql_hash_value, 0, prev_hash_value, sql_hash_value),
               DECODE(sql_hash_value, 0, prev_sql_addr, sql_address)
          FROM v$session b
         WHERE b.paddr =
               (SELECT addr FROM v$process c WHERE c.spid = '&pid'))
 ORDER BY piece ASC
 

--查看排序的情况memory 内存排序  disk临时表空间排序   rows排序的总行数
Select * from v$sysstat where name like '%sort%';