常用查询

用户名和schema对应

SELECT
B.USERNAME AS 用户名,
A.NAME AS SCHEMA名称
FROM SYSOBJECTS A
JOIN DBA_USERS B ON A.PID = B.USER_ID
WHERE A.TYPE$ = 'SCH' and B.USERNAME NOT IN ('SYS', 'SYSTEM', 'SYSAUDITOR', 'SYSSSO') -- 排除系统用户
ORDER BY B.USERNAME;

监控表空间使用情况
SELECT t.tablespace_name,
t.total_space AS "TOTAL_SPACE",
t.total_space - f.free_space AS "USED_SPACE",
f.free_space AS "FREE_SPACE",
ROUND((t.total_space - f.free_space) / t.total_space * 100, 2) || '%' AS "USED_PECENT"
FROM (SELECT tablespace_name, SUM(bytes) / 1024 / 1024 AS total_space
FROM dba_data_files
GROUP BY tablespace_name) t
JOIN (SELECT tablespace_name, SUM(bytes) / 1024 / 1024 AS free_space
FROM dba_free_space
GROUP BY tablespace_name) f
ON t.tablespace_name = f.tablespace_name;

--估算数据库前100个大表

SELECT
OWNER AS 所属用户,
TABLE_NAME AS 表名,
TABLESPACE_NAME AS 表空间,
NUM_ROWS AS 估算行数,
BLOCKS AS 占用块数,
ROUND(BLOCKS * 8192.0 / 1024 / 1024, 2) AS "估算大小(MB)",
ROUND(BLOCKS * 8192.0 / 1024 / 1024 / 1024, 2) AS "估算大小(GB)"
FROM
DBA_TABLES
WHERE
BLOCKS > 0
AND OWNER='BIPZS01'
ORDER BY
BLOCKS DESC
FETCH FIRST 100 ROWS ONLY;

--计算表和索引大小

SELECT DISTINCT sch_name, tab_name, tab_space, idx_sum
FROM ( SELECT /*+NO_MERGE*/
sch_name, tab_name, tab_space,
SUM(idx_space) OVER (PARTITION BY sch_name, tab_name) idx_sum
FROM ( SELECT /*+JOIN_HASH_SIZE(900000000) HJ_BUF_SIZE(51200) HAGR_HASH_SIZE(900000000) HAGR_BUF_SIZE(51200) */
SF_GET_SCHEMA_NAME_BY_ID(tab.schid) sch_name,
tab.name tab_name,
idx.name idx_name,
INDEX_USED_SPACE(idx.id) * 32 / 1024 / 1024 idx_space,
TABLE_USED_SPACE(SF_GET_SCHEMA_NAME_BY_ID(tab.schid), tab.name) * 32 / 1024 / 1024 tab_space
FROM sysobjects tab
LEFT JOIN sysobjects idx ON idx.pid = tab.id
WHERE idx.type$ = 'TABOBJ' AND idx.subtype$ = 'INDEX'
AND tab.type$ = 'SCHOBJ' AND tab.subtype$ = 'UTAB'
AND tab.schid NOT IN (SELECT id FROM sysobjects WHERE type$='SCH' AND name IN ('SYS','SYSDBA','SYSSSO','SYSAUDITOR','CTISYS'))
) t0
) t1
WHERE idx_sum > 10
ORDER BY idx_sum DESC, tab_space DESC;

--定位消耗高CPu的sql

top -Hp  达梦进程--- 通过线程ID查找执行sql语句

SELECT
s.sess_id,
s.user_name,
s.state,
s.clnt_ip,
s.sql_text,
t.id AS thread_id,
t.name AS thread_name
FROM v$sessions s
LEFT JOIN v$threads t ON s.thrd_id = t.id
WHERE s.state = 'ACTIVE' and s.thrd_id=2049154;

---定位消耗内存高的sql语句

SELECT s.SESS_ID,
s.USER_NAME,
s.CLNT_HOST,
s.SQL_TEXT,
ROUND(SUM(m.TOTAL_SIZE) / 1024 / 1024, 2) AS TOTAL_ALLOC_MB, -- 总分配
ROUND(SUM(m.DATA_SIZE) / 1024 / 1024, 2) AS TOTAL_USED_MB, -- 实际占用
'SP_CLOSE_SESSION(' || s.SESS_ID || ');' AS KILL_CMD
FROM V$MEM_POOL m
JOIN V$SESSIONS s
ON m.CREATOR = s.THRD_ID
GROUP BY s.SESS_ID,
s.USER_NAME,
s.CLNT_HOST,
s.SQL_TEXT
ORDER BY TOTAL_ALLOC_MB DESC;

SELECT
s.SESS_ID,
s.USER_NAME,
m.NAME AS MEM_POOL_TYPE,
ROUND(m.TOTAL_SIZE/1024/1024,2) ALLOC_MB,
ROUND(m.DATA_SIZE/1024/1024,2) USED_MB,
s.SQL_TEXT
FROM V$MEM_POOL m
JOIN V$SESSIONS s ON m.CREATOR = s.THRD_ID
ORDER BY SESS_ID, ALLOC_MB DESC;

SELECT
SESSID,
SQL_ID,
SQL_TXT,
ROUND(MAX_MEM_USED / 1024, 2) AS MAX_MEM_MB,
EXEC_TIME / 1000 AS TOTAL_EXEC_MS,
EXEC_CPU / 1000 AS TOTAL_CPU_MS,
HARD_PARSE_CNT,
TAB_SCAN_CNT,
MULTI_WAY_SORT_CNT
FROM V$SQL_STAT
ORDER BY 6 DESC LIMIT 30;

-- 定位逻辑读高的sql语句

SELECT
SQL_TXT,
LOGIC_READ_CNT,
PHY_READ_CNT,
TAB_SCAN_CNT,
IO_WAIT_TIME / 1000 AS IO_WAIT_MS
FROM V$SQL_STAT
WHERE TAB_SCAN_CNT > 0 OR PHY_READ_CNT > 10000
ORDER BY PHY_READ_CNT DESC;