TA的每日心情 | 开心 2023-8-9 11:05 |
---|
|
- SELECT S.SQL_TEXT,
- S.SQL_FULLTEXT,
- S.SQL_ID,
- ROUND(ELAPSED_TIME / 1000000 / (CASE
- WHEN (EXECUTIONS = 0 OR NVL(EXECUTIONS, 1) = 1) THEN
- 1
- ELSE
- EXECUTIONS
- END),
- 2) "执行时间'S'",
- S.EXECUTIONS "执行次数",
- S.OPTIMIZER_COST "COST",
- S.SORTS,
- S.MODULE, --连接模式(JDBC THIN CLIENT:程序)
- -- S.LOCKED_TOTAL,
- --S.PHYSICAL_READ_BYTES "物理读",
- -- S.PHYSICAL_READ_REQUESTS "物理读请求",
- --S.PHYSICAL_WRITE_REQUESTS "物理写",
- -- S.PHYSICAL_WRITE_BYTES "物理写请求",
- S.ROWS_PROCESSED "返回行数",
- S.DISK_READS "磁盘读",
- S.DIRECT_WRITES "直接路径写",
- S.PARSING_SCHEMA_NAME,
- S.LAST_ACTIVE_TIME
- FROM GV$SQLAREA S
- WHERE ROUND(ELAPSED_TIME / 1000000 / (CASE
- WHEN (EXECUTIONS = 0 OR NVL(EXECUTIONS, 1) = 1) THEN
- 1
- ELSE
- EXECUTIONS
- END),
- 2) > 5 --100 0000微秒=1S
- AND S.PARSING_SCHEMA_NAME = USER
- AND TO_CHAR(S.LAST_LOAD_TIME, 'YYYY-MM-DD') =
- TO_CHAR(SYSDATE, 'YYYY-MM-DD')
- AND S.COMMAND_TYPE IN (2, 3, 5, 6, 189)
- ORDER BY "执行时间'S'" DESC;
复制代码- select a.sql_text SQL语句,
- b.etime 执行耗时,
- c.user_id 用户ID,
- c.SAMPLE_TIME 执行时间,
- c.INSTANCE_NUMBER 实例数,
- u.username 用户名, a.sql_id SQL编号
- from dba_hist_sqltext a,
- (select sql_id, ELAPSED_TIME_DELTA / 1000000 as etime
- from dba_hist_sqlstat
- where ELAPSED_TIME_DELTA / 1000000 >= 1) b,
- dba_hist_active_sess_history c,
- dba_users u
- where a.sql_id = b.sql_id
- and u.username = 'MYDB'
- and c.user_id = u.user_id
- and b.sql_id = c.sql_id
- and a.sql_text like '%select * from big_data_tbl%'
- order by SAMPLE_TIME desc,
- b.etime desc;
复制代码
|
|