===========================================
ASH Dimension
===========================================
ASH data could be grouped into 5 groups, i.e. dimensions.
ASH Session Dimension
ASH SQL Dimension
ASH Wait Events Dimension
ASH Objects Dimension
ASH Application Dimension
DBA_HIST_SNAPSHOT displays information about the snapshots in the Workload Repository, and a correlation between date and snap ID.
===========================================
Which snap ID to use
===========================================
===========================================
ASH Session Dimension
===========================================
V$ACTIVE_SESSION_HISTORY - For recent process
DBA_HIST_ACTIVE_SESS_HISTORY - For history. Each row is worth 10 seconds.
Find top Sessions:
SELECT /*+LEADING(x) USE_NL(h)*/ …
SUM(10) ash_secs
FROM DBA_HIST_ACTIVE_SESS_HISTORY SESS_HISTORY,
DBA_HIST_SNAPSHOT HIST_SNAPSHOT
WHERE HIST_SNAPSHOT.snap_id = SESS_HISTORY.snap_id
AND HIST_SNAPSHOT.dbid = SESS_HISTORY.dbid
AND HIST_SNAPSHOT.instance_number = SESS_HISTORY.instance_number
AND HIST_SNAPSHOT.end_interval_time >= …
AND HIST_SNAPSHOT.begin_interval_time <= …
AND …
GROUP BY
ORDER BY ash_secs DESC
Find all SQLs by SQL Text
SELECT SESS_HISTORY.sql_id,
SESS_HISTORY.sql_plan_hash_value,
TO_CHAR(SESS_HISTORY.sample_time,'YYYYMMDD hh24:mm:ss') AS sample_time,
SESS_HISTORY.snap_id,
SESS_HISTORY.user_id,
SESS_HISTORY.session_id,
SESS_HISTORY.session_type,
SESS_HISTORY.program,
SESS_HISTORY.module,
SESS_HISTORY.client_id,
SESS_HISTORY.ash_secs,
TO_CHAR(DBMS_LOB.substr(HIST_SQLTEXT.sql_text,100,1)) as sql_text
FROM DBA_HIST_ACTIVE_SESS_HISTORY SESS_HISTORY,
DBA_HIST_SQLTEXT HIST_SQLTEXT
WHERE 1=1
AND SESS_HISTORY.sample_time BETWEEN (SYSDATE-7) AND (SYSDATE-2)
AND SESS_HISTORY.sql_id = HIST_SQLTEXT.sql_id
AND UPPER(HIST_SQLTEXT.sql_text) LIKE UPPER('%update sfi_customer_profile set SG2%')
AND ROWNUM < 2000;
Find Top SQLs that use 'ORDER BY'
SELECT TOP_SESSIONS.sql_id,
TOP_SESSIONS.sql_plan_hash_value,
HIST_SQLTEXT.sql_text
FROM(
SELECT SESS_HISTORY.sql_id,
SESS_HISTORY.sql_plan_hash_value,
SUM(10) ash_secs
FROM DBA_HIST_SNAPSHOT HIST_SNAPSHOT,
DBA_HIST_ACTIVE_SESS_HISTORY SESS_HISTORY
WHERE 1=1
AND SESS_HISTORY.sample_time BETWEEN (SYSDATE-5) AND (SYSDATE-3)
and SESS_HISTORY.snap_id = HIST_SNAPSHOT.snap_id
AND SESS_HISTORY.dbid = HIST_SNAPSHOT.dbid
AND SESS_HISTORY.instance_number = HIST_SNAPSHOT.instance_number
-- AND SESS_HISTORY.module = 'MY_MODULE'
GROUP BY
SESS_HISTORY.sql_id,
SESS_HISTORY.sql_plan_hash_value
ORDER BY ash_secs DESC
)TOP_SESSIONS,
DBA_HIST_SQLTEXT HIST_SQLTEXT
WHERE TOP_SESSIONS.sql_id = HIST_SQLTEXT.sql_id
AND HIST_SQLTEXT.sql_text LIKE '%ORDER BY%'
AND ROWNUM < 30;
To get the execution plan of one of the Top SQLs:
SELECT * FROM TABLE(DBMS_XPLAN.display_awr('b3hb5zg0jw3g5', 3149935409,NULL,'ADVANCED'));
And this is the output:
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID b3hb5zg0jw3g5
--------------------
SELECT ROWID, F.DATE_STRING, F.KEY1, F.KEY2, F.KEY3,
F.SCENARIO_ID, F.CAMPAIGN_ID, F.CATEGORY_ID, F.COUNTRY_ID,
F.CAMPAIGN_SENT_DATE, F.CAMPAGIN_RECD_DATE, F.TS_LAST_MODIFIED,
F.MESSAGE_TYPE, F.MESSAGE_ID, F.NETWORK_ID, F.VLR_NUMBER,
F.PARTITION_KEY_ID, F.MONTH_STRING, F.YEAR_STRING, F.MESSAGE_TEXT,
F.MSISDN FROM MY_SCHEMA.MY_TABLE F ORDER BY
10 DESC NULLS FIRST
Plan hash value: 3149935409
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 3890K(100)| |
| 1 | SORT ORDER BY | | 24M| 15G| 17G| 3890K (1)| 12:58:01 |
| 2 | PARTITION RANGE ALL| | 24M| 15G| | 483K (1)| 01:36:40 |
| 3 | TABLE ACCESS FULL | FACT_ROAMER_CAMPAIGNS | 24M| 15G| | 483K (1)| 01:36:40 |
--------------------------------------------------------------------------------
Find top SQL statements from last hour
DBA_HIST_ACTIVE_SESS_HISTORY - For history. Each row is worth 10 seconds.
===========================================
ASH SQL Dimension
===========================================
SELECT /*+LEADING(x) USE_NL(h)*/ …
SUM(10) ash_secs
FROM DBA_HIST_ACTIVE_SESS_HISTORY SESS_HISTORY,
DBA_HIST_SNAPSHOT HIST_SNAPSHOT
WHERE HIST_SNAPSHOT.snap_id = SESS_HISTORY.snap_id
AND HIST_SNAPSHOT.dbid = SESS_HISTORY.dbid
AND HIST_SNAPSHOT.instance_number = SESS_HISTORY.instance_number
AND HIST_SNAPSHOT.end_interval_time >= …
AND HIST_SNAPSHOT.begin_interval_time <= …
AND …
GROUP BY
ORDER BY ash_secs DESC
Find all SQLs by SQL Text
SELECT SESS_HISTORY.sql_id,
SESS_HISTORY.sql_plan_hash_value,
TO_CHAR(SESS_HISTORY.sample_time,'YYYYMMDD hh24:mm:ss') AS sample_time,
SESS_HISTORY.snap_id,
SESS_HISTORY.user_id,
SESS_HISTORY.session_id,
SESS_HISTORY.session_type,
SESS_HISTORY.program,
SESS_HISTORY.module,
SESS_HISTORY.client_id,
SESS_HISTORY.ash_secs,
TO_CHAR(DBMS_LOB.substr(HIST_SQLTEXT.sql_text,100,1)) as sql_text
FROM DBA_HIST_ACTIVE_SESS_HISTORY SESS_HISTORY,
DBA_HIST_SQLTEXT HIST_SQLTEXT
WHERE 1=1
AND SESS_HISTORY.sample_time BETWEEN (SYSDATE-7) AND (SYSDATE-2)
AND SESS_HISTORY.sql_id = HIST_SQLTEXT.sql_id
AND UPPER(HIST_SQLTEXT.sql_text) LIKE UPPER('%update sfi_customer_profile set SG2%')
AND ROWNUM < 2000;
Find Top SQLs that use 'ORDER BY'
SELECT TOP_SESSIONS.sql_id,
TOP_SESSIONS.sql_plan_hash_value,
HIST_SQLTEXT.sql_text
FROM(
SELECT SESS_HISTORY.sql_id,
SESS_HISTORY.sql_plan_hash_value,
SUM(10) ash_secs
FROM DBA_HIST_SNAPSHOT HIST_SNAPSHOT,
DBA_HIST_ACTIVE_SESS_HISTORY SESS_HISTORY
WHERE 1=1
AND SESS_HISTORY.sample_time BETWEEN (SYSDATE-5) AND (SYSDATE-3)
and SESS_HISTORY.snap_id = HIST_SNAPSHOT.snap_id
AND SESS_HISTORY.dbid = HIST_SNAPSHOT.dbid
AND SESS_HISTORY.instance_number = HIST_SNAPSHOT.instance_number
-- AND SESS_HISTORY.module = 'MY_MODULE'
GROUP BY
SESS_HISTORY.sql_id,
SESS_HISTORY.sql_plan_hash_value
ORDER BY ash_secs DESC
)TOP_SESSIONS,
DBA_HIST_SQLTEXT HIST_SQLTEXT
WHERE TOP_SESSIONS.sql_id = HIST_SQLTEXT.sql_id
AND HIST_SQLTEXT.sql_text LIKE '%ORDER BY%'
AND ROWNUM < 30;
To get the execution plan of one of the Top SQLs:
SELECT * FROM TABLE(DBMS_XPLAN.display_awr('b3hb5zg0jw3g5', 3149935409,NULL,'ADVANCED'));
And this is the output:
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID b3hb5zg0jw3g5
--------------------
SELECT ROWID, F.DATE_STRING, F.KEY1, F.KEY2, F.KEY3,
F.SCENARIO_ID, F.CAMPAIGN_ID, F.CATEGORY_ID, F.COUNTRY_ID,
F.CAMPAIGN_SENT_DATE, F.CAMPAGIN_RECD_DATE, F.TS_LAST_MODIFIED,
F.MESSAGE_TYPE, F.MESSAGE_ID, F.NETWORK_ID, F.VLR_NUMBER,
F.PARTITION_KEY_ID, F.MONTH_STRING, F.YEAR_STRING, F.MESSAGE_TEXT,
F.MSISDN FROM MY_SCHEMA.MY_TABLE F ORDER BY
10 DESC NULLS FIRST
Plan hash value: 3149935409
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 3890K(100)| |
| 1 | SORT ORDER BY | | 24M| 15G| 17G| 3890K (1)| 12:58:01 |
| 2 | PARTITION RANGE ALL| | 24M| 15G| | 483K (1)| 01:36:40 |
| 3 | TABLE ACCESS FULL | FACT_ROAMER_CAMPAIGNS | 24M| 15G| | 483K (1)| 01:36:40 |
--------------------------------------------------------------------------------
Find top SQL statements from last hour
SET LONG 2000;
SELECT ALL_USERS.username AS user_name,
V$SQL.module,
V$SQL.sql_text,
V$SQL.sql_fulltext,
NVL(TOP_SQLS.sql_id,'NULL') AS sql_id,
ROUND(pct_load,1) AS pct_load
FROM(
SELECT user_id,
sql_id,
count(*),
count(*)*100/SUM(count(*)) OVER() AS PCT_LOAD
FROM V$ACTIVE_SESSION_HISTORY
WHERE sample_time > sysdate - 1/24
GROUP BY user_id, sql_id
)TOP_SQLS,
V$SQL,
ALL_USERS
WHERE V$SQL.sql_id(+) = TOP_SQLS.sql_id
AND ALL_USERS.user_id(+) = TOP_SQLS.user_id
AND pct_load > 1
AND username <> 'SYS'
ORDER BY pct_load DESC;
USER_NAME MODULE SQL_TEXT SQL_FULLTEXT SQL_ID PCT_LOAD
--------- -------- ------------------------------ ------------- ------------- --------
USER_A exeA.exe SELECT OPERATOR_NAME, OPERATOR_T CLOB.text afxk91rhj3a3j 69.1
USER_A exeA.exe SELECT CUSTOMER_NAME, SERVICE_NA CLOB.text cr6xvag433u6t 6.5
USER_B exeB.exe SELECT PROJECT_NAME, MANAGER_NAM CLOB.text dfs546gg455tt 2.5
SELECT ALL_USERS.username AS user_name,
V$SQL.module,
V$SQL.sql_text,
V$SQL.sql_fulltext,
NVL(TOP_SQLS.sql_id,'NULL') AS sql_id,
ROUND(pct_load,1) AS pct_load
FROM(
SELECT user_id,
sql_id,
count(*),
count(*)*100/SUM(count(*)) OVER() AS PCT_LOAD
FROM V$ACTIVE_SESSION_HISTORY
WHERE sample_time > sysdate - 1/24
GROUP BY user_id, sql_id
)TOP_SQLS,
V$SQL,
ALL_USERS
WHERE V$SQL.sql_id(+) = TOP_SQLS.sql_id
AND ALL_USERS.user_id(+) = TOP_SQLS.user_id
AND pct_load > 1
AND username <> 'SYS'
ORDER BY pct_load DESC;
USER_NAME MODULE SQL_TEXT SQL_FULLTEXT SQL_ID PCT_LOAD
--------- -------- ------------------------------ ------------- ------------- --------
USER_A exeA.exe SELECT OPERATOR_NAME, OPERATOR_T CLOB.text afxk91rhj3a3j 69.1
USER_A exeA.exe SELECT CUSTOMER_NAME, SERVICE_NA CLOB.text cr6xvag433u6t 6.5
USER_B exeB.exe SELECT PROJECT_NAME, MANAGER_NAM CLOB.text dfs546gg455tt 2.5
===========================================
ASH Wait Events Dimension
===========================================
Top I/O SQL statements from last hour
SELECT ASH.sql_id,
count(*)
FROM V$ACTIVE_SESSION_HISTORY ASH,
V$EVENT_NAME EVT
WHERE ASH.sample_time > sysdate – 1/24
AND ASH.session_state = 'WAITING'
AND ASH.event_id = EVT.event_id
AND EVT.wait_class = 'User I/O'
GROUP BY sql_id
ORDER BY count(*) desc;
===========================================
ASH Objects Dimension
===========================================
Get historical data for tablespace size growth.
SELECT V$TABLESPACE.name
AS TABLESPACE_NAME,
(HIST_USAGE.tablespace_size*DBA_TABLESPACES.block_size)/1024/1024
AS TABLESPACE_SIZE_MB,
(HIST_USAGE.tablespace_usedsize*DBA_TABLESPACES.block_size)/1024/1024
AS USED_SIZE_MB,
HIST_USAGE.rtime
FROM DBA_HIST_TBSPC_SPACE_USAGE HIST_USAGE,
V$TABLESPACE,
DBA_TABLESPACES
WHERE HIST_USAGE.tablespace_id = V$TABLESPACE.ts#
AND DBA_TABLESPACES.tablespace_name = V$TABLESPACE.name
AND HIST_USAGE.tablespace_usedsize > 0
AND V$TABLESPACE.name = 'TEMPORARY'
ORDER BY HIST_USAGE.snap_id DESC;
Get historical data for tablespace size growth.
SELECT V$TABLESPACE.name
AS TABLESPACE_NAME,
(HIST_USAGE.tablespace_size*DBA_TABLESPACES.block_size)/1024/1024
AS TABLESPACE_SIZE_MB,
(HIST_USAGE.tablespace_usedsize*DBA_TABLESPACES.block_size)/1024/1024
AS USED_SIZE_MB,
HIST_USAGE.rtime
FROM DBA_HIST_TBSPC_SPACE_USAGE HIST_USAGE,
V$TABLESPACE,
DBA_TABLESPACES
WHERE HIST_USAGE.tablespace_id = V$TABLESPACE.ts#
AND DBA_TABLESPACES.tablespace_name = V$TABLESPACE.name
AND HIST_USAGE.tablespace_usedsize > 0
AND V$TABLESPACE.name = 'TEMPORARY'
ORDER BY HIST_USAGE.snap_id DESC;
TABLESPACE_NAME TABLESPACE_SIZE_MB USED_SIZE_MB RTIME
----------------- ------------------ ------------ --------------------
TEMPORARY 12000 1 04/07/2015 17:00:05
TEMPORARY 12000 15 04/07/2015 03:00:41
TEMPORARY 12000 15 04/06/2015 03:00:39
TEMPORARY 12000 1 04/05/2015 23:00:59
TEMPORARY 12000 1 04/05/2015 22:00:57
TEMPORARY 12000 1 04/05/2015 21:00:55
TEMPORARY 12000 15 04/05/2015 03:00:19
TEMPORARY 12000 4 04/04/2015 07:00:40
TEMPORARY 12000 15 04/04/2015 03:00:23
TEMPORARY 2600 15 04/02/2015 03:00:18
TEMPORARY 2600 15 04/01/2015 03:00:14
TEMPORARY 2600 15 03/31/2015 03:00:39
12 rows selected.
----------------- ------------------ ------------ --------------------
TEMPORARY 12000 1 04/07/2015 17:00:05
TEMPORARY 12000 15 04/07/2015 03:00:41
TEMPORARY 12000 15 04/06/2015 03:00:39
TEMPORARY 12000 1 04/05/2015 23:00:59
TEMPORARY 12000 1 04/05/2015 22:00:57
TEMPORARY 12000 1 04/05/2015 21:00:55
TEMPORARY 12000 15 04/05/2015 03:00:19
TEMPORARY 12000 4 04/04/2015 07:00:40
TEMPORARY 12000 15 04/04/2015 03:00:23
TEMPORARY 2600 15 04/02/2015 03:00:18
TEMPORARY 2600 15 04/01/2015 03:00:14
TEMPORARY 2600 15 03/31/2015 03:00:39
12 rows selected.
===========================================
ASH Application Dimension
===========================================
List total number of processes and PGA allocated memory.
SELECT HIST_SNAPSHOT.snap_id,
TO_CHAR(HIST_SNAPSHOT.begin_interval_time, 'DD/MM/YYYY hh24:mi:ss') AS start_time,
HIST_PROCESS.category,
HIST_PROCESS.num_processes,
HIST_PROCESS.allocated_total
FROM DBA_HIST_PROCESS_MEM_SUMMARY HIST_PROCESS,
DBA_HIST_SNAPSHOT HIST_SNAPSHOT
WHERE HIST_SNAPSHOT.snap_id = HIST_PROCESS.snap_id
AND category = 'SQL'
ORDER BY HIST_SNAPSHOT.snap_id DESC;
SNAP_ID START_TIME CATEGORY NUM_PROCESSES ALLOCATED_TOTAL
---------- ------------------- --------------- ------------- ---------------
9539 31/12/2015 09:00:24 SQL 23 348584
9538 31/12/2015 08:00:22 SQL 18 181280
9537 31/12/2015 07:00:19 SQL 18 177048
9536 31/12/2015 06:00:16 SQL 18 180048
9535 31/12/2015 05:00:14 SQL 20 211640
9534 31/12/2015 04:00:11 SQL 19 204520
9533 31/12/2015 03:00:08 SQL 22 227592
9532 31/12/2015 02:00:04 SQL 23 261768
9531 31/12/2015 01:00:02 SQL 24 296248
SELECT HIST_SNAPSHOT.snap_id,
TO_CHAR(HIST_SNAPSHOT.begin_interval_time, 'DD/MM/YYYY hh24:mi:ss') AS start_time,
HIST_PROCESS.category,
HIST_PROCESS.num_processes,
HIST_PROCESS.allocated_total
FROM DBA_HIST_PROCESS_MEM_SUMMARY HIST_PROCESS,
DBA_HIST_SNAPSHOT HIST_SNAPSHOT
WHERE HIST_SNAPSHOT.snap_id = HIST_PROCESS.snap_id
AND category = 'SQL'
ORDER BY HIST_SNAPSHOT.snap_id DESC;
SNAP_ID START_TIME CATEGORY NUM_PROCESSES ALLOCATED_TOTAL
---------- ------------------- --------------- ------------- ---------------
9539 31/12/2015 09:00:24 SQL 23 348584
9538 31/12/2015 08:00:22 SQL 18 181280
9537 31/12/2015 07:00:19 SQL 18 177048
9536 31/12/2015 06:00:16 SQL 18 180048
9535 31/12/2015 05:00:14 SQL 20 211640
9534 31/12/2015 04:00:11 SQL 19 204520
9533 31/12/2015 03:00:08 SQL 22 227592
9532 31/12/2015 02:00:04 SQL 23 261768
9531 31/12/2015 01:00:02 SQL 24 296248
No comments:
Post a Comment