Menu Close

Oracle AWR报告中数据查询

lcc0556

001. 查询AWR的snap_id

获取 2021年12月20日8时至10时的snap_id：

SET LINES 300 PAGES 999 FEED OFF

SELECT instance_number,
       snap_id,
       begin_interval_time,
       end_interval_time
  FROM dba_hist_snapshot
 WHERE to_char(begin_interval_time,'DD-MM-YYYY')='20-12-2021'
   AND EXTRACT(HOUR FROM begin_interval_time) between 8 and 10;

002. 根据 snap_id 查询Oracle数据库TOP等待事件

SET LINES 300 PAGES 999 FEED OFF

SELECT *
  FROM (SELECT a.instance_number,
               a.event,
               SUM(a.wait_time + a.time_waited) AS ttl_wait_time
          FROM dba_hist_active_sess_history a
         WHERE a.event is not null
           AND a.snap_id between 6658 and 6660
         GROUP BY a.instance_number,a.event
         ORDER BY 1,3 DESC);

003. 查询未使用绑定变量的SQL


SET LINES 300 PAGES 999
COL SQL_TEXT FOR A80
COL PLSQL_PROCEDURE FOR A20
COL USER_NAME FOR A12
SELECT *
  FROM (WITH subs AS (SELECT /*+ materialize */
                             m.sql_id, k.*,
                             m.SQL_TEXT,
                             m.SQL_FULLTEXT
                        FROM (SELECT inst_id,
                                     parsing_schema_name AS user_name,
                                     module,
                                     plan_hash_value,
                                     COUNT(0) AS copies,
                                     SUM(executions) AS executions,
                                     SUM(round(sharable_mem / (1024 * 1024), 2)) AS sharable_mem_mb
                                FROM gv$sqlarea
                               WHERE executions < 5
                                 AND kept_versions = 0
                               GROUP BY inst_id, parsing_schema_name, module, plan_hash_value
                              HAVING COUNT(0) > 10
                               ORDER BY COUNT(0) DESC) k
                        LEFT JOIN gv$sqlarea m ON k.plan_hash_value = m.plan_hash_value
                       WHERE k.plan_hash_value > 0)
         SELECT DISTINCT ki.inst_id, t.sql_id, ki.sql_text, t.plsql_procedure, ki.user_name,
                SUM(ki.copies)          AS copies,
                SUM(ki.executions)      AS executions,
                SUM(ki.sharable_mem_mb) AS sharable_mem_mb
          FROM (SELECT sql_id, program_id, program_line#, action, module, service, parsing_schema_name,
                       round(buffer_gets / decode(executions, 0, 1, executions)) AS buffer_per_Exec,
                       row_number() over(partition by sql_id order by program_id desc, program_line#) AS sira,
                       decode(program_id, 0, null, owner || '.' || object_name || '(' || program_line# || ')') AS plsql_procedure
                  FROM gv$sql a, dba_objects b
                 WHERE a.program_id = b.object_id(+)) t,
               subs ki
         WHERE ki.sql_id = t.sql_id
           AND t.sira = 1
         GROUP BY ki.inst_id, t.sql_id, ki.sql_text, t.plsql_procedure, ki.user_name
         ORDER BY SUM(ki.executions) desc
        )
 WHERE ROWNUM < 51;
Post Views: 337
Tagged ,