常用SQL（2）

来源：互联网发布：咸鱼退款淘宝介入编辑：程序博客网时间：2024/05/21 16:54

/**修改系统统计参数**/
ALTER system SET statistics_level = ALL;

/**查看内存SGA PGA**/
show parameter target；
SELECT * FROM v$sgainfo;
show sga;

/**内存表**/
--设置内存表
ALTER TABLE t_team storage(buffer_pool keep);
--查询改表是否放在keep区中
SELECT table_name FROM dba_tables WHERE buffer_pool = 'KEEP';
ALTER TABLE t_subscriber cache;
SELECT table_name FROM dba_tables WHERE rtrim(cache) = 'Y';

SELECT * FROM t_subscriber t;

/**表空间**/
--表空间使用情况
SELECT a.tablespace_name,
       a.bytes / (1204 * 1024) total,
       b.bytes / (1204 * 1024) used,
       c.bytes / (1204 * 1024) free,
       (b.bytes * 100) / a.bytes "% USED",
       (c.bytes * 100) / a.bytes "% FREE"
FROM sys.sm$ts_avail a, sys.sm$ts_used b, sys.sm$ts_free c
WHERE a.tablespace_name = b.tablespace_name
   AND a.tablespace_name = c.tablespace_name;

/**SQL执行情况**/
--sql语句的缓存情况
SELECT t.sql_id,
       t.executions_total, --总共执行次数
       t.disk_reads_total, --从磁盘读取数据的次数
       t.parse_calls_total,
       a.sql_text
FROM sys.wrh$_sqlstat t, sys.wrh$_sqltext a
WHERE t.parsing_schema_name = 'ECONSOLE'
   AND t.sql_id = a.sql_id;
--先用下面的公式计算高速缓存区的命中率：
-- 1-(physical reads/(db block gets+consisten gets))
SELECT NAME, VALUE
FROM v$sysstat
WHERE NAME IN ('physical reads', 'db block gets', 'consistent gets');

select 1-(10316/(20936+253736)) from dual;