统计信息
针对部分sql走索引不准的情况，可以优先收集一波统计信息
--统计某一时间段内，sql运行的情况
SELECT sql_id,MODULE,count(*) FROM "V$ACTIVE_SESSION_HISTORY" vash WHERE SAMPLE_TIME >to_date('2024-01-22 11:49:17','yyyy-mm-dd hh24:mi:ss') AND SAMPLE_TIME to_date('2024-04-29 10:18:45','yyyy-mm-dd hh24:mi:ss') AND SAMPLE_TIME to_date('2024-04-28 16:50:00','yyyy-mm-dd hh24:mi:ss') AND SAMPLE_TIME 'owner', -- 表所属的模式/用户
tabname => 'table_name', -- 表名
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, -- 估算百分比
method_opt => 'FOR ALL COLUMNS SIZE AUTO', -- 统计信息收集选项
cascade => TRUE
);
END;
-- 查询SGA大小
SELECT ROUND(SUM(VALUE) / POWER(1024, 3), 2) AS "SGA Size (GB)"
FROM V$SGA;
-- 查询PGA大小
SELECT ROUND(SUM(VALUE) / POWER(1024, 3), 2) AS "PGA Size (GB)"
FROM V$PGASTAT
WHERE NAME = 'total PGA allocated';
在 Oracle 数据库中，可以使用 DBMS_STATS 包来重新收集多个用户下的多张表的统计信息。可以通过编写 PL/SQL 块来实现这个目的。以下是一个示例 PL/SQL 块，用于收集多个用户下的多张表的统计信息：
sql
BEGIN
-- Collect statistics for all tables in schema
SCHEMA1 DBMS_STATS.GATHER_SCHEMA_STATS('SCHEMA1');
-- Collect statistics for all tables in schema SCHEMA2 DBMS_STATS.GATHER_SCHEMA_STATS('SCHEMA2');
-- Collect statistics for a specific table in SCHEMA1 DBMS_STATS.GATHER_TABLE_STATS('SCHEMA1', 'TABLE1');
-- Collect statistics for a specific table in SCHEMA2 DBMS_STATS.GATHER_TABLE_STATS('SCHEMA2', 'TABLE2');
END;
/
解释
-
DBMS_STATS.GATHER_SCHEMA_STATS('SCHEMA1'): 重新收集 SCHEMA1 模式下所有表的统计信息。
-
DBMS_STATS.GATHER_SCHEMA_STATS('SCHEMA2'): 重新收集 SCHEMA2 模式下所有表的统计信息。
-
DBMS_STATS.GATHER_TABLE_STATS('SCHEMA1', 'TABLE1'): 重新收集 SCHEMA1 模式下 TABLE1 表的统计信息。
-
DBMS_STATS.GATHER_TABLE_STATS('SCHEMA2', 'TABLE2'): 重新收集 SCHEMA2 模式下 TABLE2 表的统计信息。
动态生成 PL/SQL 块
如果需要动态收集多个用户下的多张表的统计信息，可以编写一个动态生成 SQL 语句的 PL/SQL 块。以下是一个示例：
sql
DECLARE
v_owner VARCHAR2(30);
v_table_name VARCHAR2(30);
BEGIN FOR
rec
IN
(SELECT owner, table_name FROM dba_tables
WHERE owner IN ('SCHEMA1', 'SCHEMA2')) LOOP v_owner := rec.owner; v_table_name := rec.table_name;
DBMS_STATS.GATHER_TABLE_STATS(v_owner, v_table_name);
DBMS_OUTPUT.PUT_LINE('Gathered stats for: ' || v_owner || '.' || v_table_name); END LOOP;
END;
/
解释
-
查询 dba_tables 视图，获取指定模式下的所有表。
-
对每个表调用 DBMS_STATS.GATHER_TABLE_STATS 收集统计信息。
-
使用 DBMS_OUTPUT.PUT_LINE 输出收集统计信息的表名（可选）。
一条命令收集多个用户下所有表的统计信息
如果希望一条命令收集多个用户下所有表的统计信息，可以直接使用 DBMS_STATS.GATHER_DATABASE_STATS，但这会收集整个数据库的统计信息：
sql
BEGIN
DBMS_STATS.GATHER_DATABASE_STATS;
END; /
这个命令会收集整个数据库中所有表的统计信息。对于较大的数据库，这可能需要较长的时间。
