【DM版本】:v8
【问题描述】*:请问如何通过SQL语句,快速统计数据库内,每个表被查询和写入的次数是多少?
WITH SQL_TABLES AS (
SELECT
A.SQL_ID SQL_ID,
A.parse_cnt EXECUTIONS,
A.IO_WAIT_TIME IO_WAIT_TIME,
A.logic_read_cnt LOGIC_READ,
a.PHY_READ_CNT PHY_READ,
REGEXP_SUBSTR(b.TOP_SQL_TEXT, 'FROM\s+([a-zA-Z0-9_.]+)', 1, 1, 'i', 1) AS table_name
FROM
V$SQL_STAT_HISTORY A
JOIN
V$SQL_HISTORY B ON A.SQL_ID = B.SQL_ID
WHERE
B.TOP_SQL_TEXT LIKE '%FROM%'
),
AGGREGATED_STATS AS (
SELECT
TABLE_NAME,
SUM(EXECUTIONS) AS TOTAL_EXECUTIONS,
SUM(IO_WAIT_TIME) AS TOTAL_IO_WAIT_TIME,
SUM(LOGIC_READ) AS TOTAL_LOGIC_READ,
sum(PHY_READ) as TOTAL_PHY_READ
FROM
SQL_TABLES
GROUP BY
TABLE_NAME
)
SELECT
TABLE_NAME,
TOTAL_EXECUTIONS,
ROUND(TOTAL_LOGIC_READ / NULLIF(TOTAL_EXECUTIONS, 0), 2) AS AVG_LOGIC_READ,
ROUND(TOTAL_PHY_READ / NULLIF(TOTAL_EXECUTIONS, 0), 2) AS AVG_PHY_READ,
ROUND(TOTAL_IO_WAIT_TIME / NULLIF(TOTAL_EXECUTIONS, 0), 2) AS AVG_IO_WAIT_TIME,
TOTAL_LOGIC_READ,
TOTAL_PHY_READ,
TOTAL_IO_WAIT_TIME
FROM
AGGREGATED_STATS
ORDER BY
TOTAL_LOGIC_READ DESC
LIMIT 10;

查询次数不好统计,插入次数可以查看视图DBA_TAB_MODIFICATIONS或者数据字典SYSMODIFICATIONS
