oracle 中的实时统计

CREATE TABLE tab1 (
id NUMBER,
description VARCHAR2(50)
);

创建一个过程来截断测试表，向其中填充一些数据并收集所有表统计信息。
CREATE OR REPLACE PROCEDURE reset_tab1 AS
BEGIN
EXECUTE IMMEDIATE ‘TRUNCATE TABLE tab1’;
INSERT /*+ APPEND / INTO tab1 (id, description)
SELECT level, 'Description of ’ || level
FROM dual
CONNECT BY level <= 1000;
COMMIT;
DBMS_STATS.gather_table_stats(NULL, ‘tab1’);
END;
/


EXEC reset_tab1;

SELECT table_name,
num_rows
FROM user_tables
WHERE table_name = ‘TAB1’;
num_rows：表中的行数

USER_TAB_STATISTICS：显示当前用户拥有的表的优化程序统计信息
SELECT table_name,
num_rows,
notes
FROM user_tab_statistics
WHERE table_name = ‘TAB1’;

USER_TAB_COL_STATISTICS包含从中提取的列统计信息和直方图信息
SELECT table_name,
column_name,
UTL_RAW.cast_to_number(low_value) AS low_value,
UTL_RAW.cast_to_number(high_value) AS high_value,
sample_size,
notes
FROM user_tab_col_statistics
WHERE table_name = ‘TAB1’
AND column_name = ‘ID’;
column_name：列名
SAMPLE_SIZE：分析此列所用的样本量
UTL_RAW.cast_to_number：此函数将NUMBER（in RAW）的二进制表示形式转换为NUMBER


插入数据测试
EXEC reset_tab1;
INSERT INTO tab1
SELECT level+1000 AS id,
'Description of ’ || level AS description
FROM dual
CONNECT BY level <= 1000;
COMMIT;


EXEC DBMS_STATS.flush_database_monitoring_info;

alter system set “_exadata_feature_on”=true scope=spfile;
shutdown immediate;
startup;

SELECT * FROM TABLE(DBMS_XPLAN.display_cursor);

EXEC DBMS_STATS.flush_database_monitoring_info;

SELECT table_name,
num_rows,
notes
FROM user_tab_statistics
WHERE table_name = ‘TAB1’;


SELECT MAX(id) FROM tab1;

SELECT * FROM TABLE(DBMS_XPLAN.display_cursor);

重置测试数据将监视数据刷新到字典中
EXEC reset_tab1;
INSERT /+ APPEND */ INTO tab1
SELECT level+1000 AS id,
'Description of ’ || level AS description
FROM dual
CONNECT BY level <= 1000;
COMMIT;

写入到数据字典
EXEC DBMS_STATS.flush_database_monitoring_info;



常规插入它不会触发实时统计信息
EXEC reset_tab1;
INSERT INTO tab1 VALUES (1001, ‘Description of 1001’);
COMMIT;
EXEC DBMS_STATS.flush_database_monitoring_info;




EXEC reset_tab1;
BEGIN
FOR i IN 1001 … 2000 LOOP
INSERT INTO tab1 VALUES (i, 'Description of ’ || i);
END LOOP;
COMMIT;
END;
/
EXEC DBMS_STATS.flush_database_monitoring_info;



EXEC reset_tab1;
BEGIN
FOR i IN 1001 … 2000 LOOP
INSERT INTO tab1 VALUES (i, 'Description of ’ || i);
COMMIT;
END LOOP;
END;
/
EXEC DBMS_STATS.flush_database_monitoring_info;



更新
EXEC reset_tab1;
UPDATE tab1
SET id =id+10000;
COMMIT;
EXEC DBMS_STATS.flush_database_monitoring_info;



删除
EXEC reset_tab1;
DELETE FROM tab1;
EXEC DBMS_STATS.flush_database_monitoring_info;