数据库常见场景分析

 

一、统计一段时间内sql的执行次数及情况

1、开启general_log记录到表中

SQL> set global general_log=1;

SQL> set global log_output='TABLE';

2、查询记录的sql

SQL> select * from mysql.general_log limit 3;

3、按SQL统计执行数量

SQL> select * from (select argument,count(*) as number from mysql.general_log group by argument) t order by number desc limit 10;

4、开启general_log耗费资源，使用完及时关闭，使用完数据及时清理

SQL> set global general_log=1;

SQL> truncate table mysql.general_log;

5、除了表日志以外，还可以设置为文件日志

SQL> set global log_output='FILE';

SQL> set global general_log_file='/tmp/general.log';

6、SQLServer查询执行过的sql次数

SELECT TOP 2000

ST.text AS '执行的SQL语句',

QS.execution_count AS '执行次数',

QS.total_elapsed_time AS '耗时',

QS.total_logical_reads AS '逻辑读取次数',

QS.total_logical_writes AS '逻辑写入次数',

QS.total_physical_reads AS '物理读取次数',

QS.creation_time AS '执行时间' ,

QS.*

FROM sys.dm_exec_query_stats QS CROSS APPLY

sys.dm_exec_sql_text(QS.sql_handle) ST

WHERE QS.creation_time BETWEEN '2020-06-11 00:00:00' AND '2020-06-12 00:00:00'

ORDER BY QS.total_elapsed_time DESC;

7、Oracle查看执行SQL的历史记录信息

# 查看当前数据库执行次数最多的SQL

SELECT SQL_TEXT, EXECUTIONS

FROM (SELECT SQL_TEXT,

EXECUTIONS,

RANK() OVER(ORDER BY EXECUTIONS DESC) EXEC_RANK

FROM V$SQLAREA)

WHERE EXEC_RANK <= 15;

 

# 查看执行SQL的历史记录信息

SELECT DBMS_LOB.SUBSTR(sql_text, 100, 1) SQL_SHORT,

tab1.sql_id,

DBMS_LOB.getlength(sql_text) SQL_Len,

ROUND(TOTAL_WAIT / 1000000, 2) TOTAL_WAIT_SECS,

ROUND(ELAPSED_TIME_DELTA / 1000000, 2) TOTAL_TIME_SECS,

TO_CHAR(BEGIN_INTERVAL_TIME, 'DAY', 'NLS_DATE_LANGUAGE=AMERICAN') Week_Day,

BEGIN_INTERVAL_TIME,

END_INTERVAL_TIME,

EXEC_COUNT,

AVG_CPU_TIME_SECS,

AVG_ELAPSED_SECS,

ROUND(ELAPSED_TIME_DELTA / 1000000, 0) ELAPSED_TIME_DELTA,

ROUND(AVG_ROWS_PROCESSED, 1) AVG_ROWS_PROCESSED,

PLAN_HASH_VALUE,

MODULE,

ACTION,

PARSING_SCHEMA_NAME,

(SELECT username FROM dba_users WHERE user_id = PARSING_USER_ID) PARSING_USER,

AVG_BUFFER_GETS,

AVG_DISK_READS,

AVG_IOWAIT AVG_iowai_secs,

AVG_CCWAIT AVG_ccwait_secs,

AVG_CLWAIT AVG_clwait_secs,

AVG_APWAIT AVG_apwait_secs,

AVG_PX_SERVERS,

AVG_PARSE_CALLS,

tab1.SNAP_ID,

tab1.INSTANCE_NUMBER,

AVG_CELL_UNCOMPRESSED_BYTES,

AVG_DIRECT_WRITES,

AVG_IO_INTERCONNECT_BYTES,

AVG_IO_OFFLOAD_ELIG_BYTES,

AVG_IO_OFFLOAD_RETURN_BYTES,

AVG_JAVEXEC_TIME,

AVG_OPTIMIZED_PHYSICAL_READS,

AVG_PLSEXEC_TIME_DELTA,

AVG_SORTS_DELTA,

-- BIND_DATA, -- -- (Falta arreglar) listagg( (select * from table(dbms_sqltune.extract_binds(bind_data)), '#') WITHIN GROUP (ORDER BY 1)) Binds ,

DBMS_LOB.SUBSTR(sql_text, 2000, 1) SQL_TEXT1,

DBMS_LOB.SUBSTR(sql_text, 2000, 2001) SQL_TEXT2,

DBMS_LOB.SUBSTR(sql_text, 2000, 4001) SQL_TEXT3,

DBMS_LOB.SUBSTR(sql_text, 2000, 6001) SQL_TEXT4,

DBMS_LOB.SUBSTR(sql_text, 2000, 8001) SQL_TEXT5,

DBMS_LOB.SUBSTR(sql_text, 2000, 10001) SQL_TEXT6,

DBMS_LOB.SUBSTR(sql_text, 2000, 12001) SQL_TEXT7,

DBMS_LOB.SUBSTR(sql_text, 2000, 14001) SQL_TEXT8,

DBMS_LOB.SUBSTR(sql_text, 2000, 16001) SQL_TEXT9,

DBMS_LOB.SUBSTR(sql_text, 2000, 18001) SQL_TEXT10

FROM DBA_HIST_SQLTEXT D,

DBA_HIST_SNAPSHOT N,

(SELECT S.SQL_ID SQL_ID,

instance_number,

SUM(EXECUTIONS) EXEC_COUNT,

ROUND((SUM(CPU_TIME_DELTA) / SUM(EXECUTIONS)) / 1000000, 4) AVG_CPU_TIME_SECS,

ROUND((SUM(ELAPSED_TIME_DELTA) / SUM(EXECUTIONS)) / 1000000,

4) AVG_ELAPSED_SECS,

SUM(ELAPSED_TIME_DELTA) ELAPSED_TIME_DELTA,

ROUND((SUM(ROWS_PROCESSED_DELTA) / SUM(EXECUTIONS)), 4) AVG_ROWS_PROCESSED,

PLAN_HASH_VALUE,

MODULE,

ACTION,

PARSING_SCHEMA_NAME,

PARSING_USER_ID,

ROUND(SUM(BUFFER_GETS_DELTA) / SUM(EXECUTIONS), 4) AVG_BUFFER_GETS,

ROUND(SUM(DISK_READS_DELTA) / SUM(EXECUTIONS), 0) AVG_DISK_READS,

SNAP_ID,

ROUND((SUM(IOWAIT_DELTA) / SUM(EXECUTIONS)) / 1000000, 4) AVG_IOWAIT,

ROUND((SUM(CCWAIT_DELTA) / SUM(EXECUTIONS)) / 1000000, 4) AVG_CCWAIT,

ROUND((SUM(CLWAIT_DELTA) / SUM(EXECUTIONS)) / 1000000, 4) AVG_CLWAIT,

ROUND((SUM(APWAIT_DELTA) / SUM(EXECUTIONS)) / 1000000, 4) AVG_APWAIT,

ROUND(SUM(PX_SERVERS_EXECS_DELTA) / SUM(EXECUTIONS), 4) AVG_PX_SERVERS,

ROUND(SUM(PARSE_CALLS_DELTA) / SUM(EXECUTIONS), 4) AVG_PARSE_CALLS,

ROUND(SUM(CELL_UNCOMPRESSED_BYTES_DELTA) / SUM(EXECUTIONS), 4) AVG_CELL_UNCOMPRESSED_BYTES,

ROUND(SUM(DIRECT_WRITES_DELTA) / SUM(EXECUTIONS), 4) AVG_DIRECT_WRITES,

ROUND(SUM(IO_INTERCONNECT_BYTES_DELTA) / SUM(EXECUTIONS), 4) AVG_IO_INTERCONNECT_BYTES,

ROUND(SUM(IO_OFFLOAD_ELIG_BYTES_DELTA) / SUM(EXECUTIONS), 4) AVG_IO_OFFLOAD_ELIG_BYTES,

ROUND(SUM(IO_OFFLOAD_RETURN_BYTES_DELTA) / SUM(EXECUTIONS), 4) AVG_IO_OFFLOAD_RETURN_BYTES,

ROUND(SUM(JAVEXEC_TIME_DELTA) / SUM(EXECUTIONS), 4) AVG_JAVEXEC_TIME,

ROUND(SUM(OPTIMIZED_PHYSICAL_READS_DELTA) / SUM(EXECUTIONS),

4) AVG_OPTIMIZED_PHYSICAL_READS,

ROUND(SUM(PLSEXEC_TIME_DELTA) / SUM(EXECUTIONS), 4) AVG_PLSEXEC_TIME_DELTA,

ROUND(SUM(SORTS_DELTA) / SUM(EXECUTIONS), 4) AVG_SORTS_DELTA,

SUM(IOWAIT_DELTA + CCWAIT_DELTA + CLWAIT_DELTA + APWAIT_DELTA) TOTAL_WAIT,

MAX(BIND_DATA) BIND_DATA

FROM (SELECT S.*,

DECODE(EXECUTIONs_DELTA, 0, 1, EXECUTIONS_DELTA) EXECUTIONS

FROM DBA_HIST_SQLSTAT S) S

WHERE 1 = 1 -- and ( PARSING_SCHEMA_NAME like 'EUS%' ) -- Filter by schema name

GROUP BY S.SQL_ID,

instance_number,

S.SNAP_ID,

PLAN_HASH_VALUE,

MODULE,

ACTION,

PARSING_SCHEMA_NAME,

S.INSTANCE_NUMBER,

PARSING_USER_ID

HAVING ROUND((SUM(ELAPSED_TIME_DELTA) / SUM(EXECUTIONS)) / 1000000, 4) >= 10 -- Filter >= n secs

ORDER BY 5 DESC) TAB1

WHERE D.SQL_ID = TAB1.SQL_ID

AND TAB1.snap_id = N.snap_id -- and upper(tab1.module) like '%PKG_BATCH_PROCESSES_SDM%'

AND d.sql_id IN ('6tmcqrydfgdtc') -- AND D.SQL_ID in ( select distinct sql_id from DBA_HIST_ACTIVE_SESS_HISTORY where top_level_sql_id = '85xkhugz5kt8h' )

-- AND upper(DBMS_LOB.SUBSTR (sql_text, 100, 1)) LIKE 'INSERT%'

-- AND upper(DBMS_LOB.SUBSTR (sql_text, 1000, 1)) LIKE 'SELECT%'

-- AND upper(DBMS_LOB.SUBSTR (sql_text, 3000, 1)) LIKE '%INACTIVE%'

AND BEGIN_INTERVAL_TIME > SYSDATE - 60

-- and BEGIN_INTERVAL_TIME between to_date('05/09/2013 07:50:00', 'DD/MM/YYYY hh24:mi:ss') AND to_date('05/09/2013 16:10:00', 'DD/MM/YYYY hh24:mi:ss')

-- and BEGIN_INTERVAL_TIME >= (SELECT job_start_datetime - (2/24) FROM job_status WHERE job_name = 'RAVLDBSK' AND cob_date = (SELECT current_cob_date FROM eus_cob))

-- and BEGIN_INTERVAL_TIME <= (SELECT job_end_datetime + (2/24) FROM job_status WHERE job_name = 'RAVLDBSK' AND cob_date = (SELECT current_cob_date FROM eus_cob))

-- order by AVG_ELAPSED_SECS DESC

-- order by ELAPSED_TIME_DELTA DESC

ORDER BY BEGIN_INTERVAL_TIME DESC

-- order by TOTAL_WAIT_SECS desc;