hangover - que pasó ayer? troubleshooting con vistas ash & s-ash

Hangover - Que pasó ayer ?

Troubleshooting con vistas ASH & S-ASH

© 2009/2011 Pythian - Confidential

About Roy Salazar

2

• Oracle Technologies work experience since year 1999

• BSc in Systems Engineering

• With Pythian since 2011, Before in HP, SYSDE Int. and other national Companies

• Contact

• www.linkedin.com/pub/roy-salazar/66/224/999

• [email protected]/

• About my background

• Oracle Database Consultant (Oracle Core, Goldengate, Dbvisit, RAC, Standby, others)

• acting Team Manager / Delivery Lead

• ITIL Certified

• Technical Consultant, Systems Analyst, Developer (Oracle Forms - Reports & PL/SQL)

© 2012 – Pythian

About Pythian Recognized Leader:• Global industry leader in data infrastructure managed services and

consulting with expertise in Oracle, Oracle Applications, Microsoft SQL Server, MySQL, big data and systems administration

• Work with over 200 multinational companies such as Forbes.com, Fox Sports, Nordion and Western Union to help manage their complex IT deployments

Expertise:• One of the world’s largest concentrations of dedicated, full-time DBA

expertise. Employ 8 Oracle ACEs/ACE Directors

• Hold 7 Specializations under Oracle Platinum Partner program, including Oracle Exadata, Oracle GoldenGate & Oracle RAC

Global Reach & Scalability:• 24/7/365 global remote support for DBA and consulting, systems

administration, special projects or emergency response


Objective

The Hangover is a funny Movie about 4 guys having Bachelor Party, however when they wake up in the morning they have no idea what happened the last night and they face several incidents like friend missed, no tooth, tiger in bathroom, baby in kitchen, etc.

Compared with the Movie, sometimes we DBAs get request to find out what happened day before or past weekend, ie why a report was running slower? Or why DB performance was too bad yesterday between X and Y hour or even more detailed request, like to provide what SQL was running certain user at some specific hour, so for all those cases when DBA needs to find an event happened in the past, the ASH Views are great tool for troubleshooting and investigation.

4


Vistas ASH - Overview


Que es ASH ?

6

Active Session History - V$Active_Session_History Colecta cada segundo en memory buffer muestras detalladas de sesiones activas (únicamente), son escritas a disco por los snapshots de AWR cada 10 segundos: DBA_Hist_Active_Sess_History

Cualquier Sesion conectada a la DB y usando CPU es considerada una sesion activa (excepto idle wait class)

Es como hacer un “select * from v$session_wait” Fueron introducidas desde la version 10g y sus atributos

han ido aumentanto con cada version Habilitada por default, pero requiere Licencia Tuning

and Diagnostic Pack Incluye: Session_ID, Wait_Event, SQL_ID, Object, otros


ASH Características

7

Herramienta para Diagnóstico de Performance Siempre está encencida (SQL Trace no) Mantiene el historial (estádistico) de lo que pasó (en

terminos de sesiones activas) Es eficiente, no degrada el Performance al ser una

herramienta integrada Es robusto y no causa ningún tipo de locks Funciona bien aún cuando el system está sobre cargado Provee cantidad idonea de detalles (por ej:

AWR/Statspack => Muy Pocos Detalles vs SQL_Trace => Demasiados Detalles)

Reportes: $ORACLE_HOME/rdbms/admin/ashrpt.sql Reportes RAC: $ORACLE_HOME/rdbms/admin/ashrpti.sql


Ctrl Parameters /Do not change

8

_ash_enable: TRUE _ash_size: Tamaño del buffer ASH en Bytes _ash_sampling_interval: 1000 (en milisegundos) _ash_disk_write_enable: True (enviar al AWR) _ash_disk_filter_ratio: 10 (1 de 10 muestas escritas AWR) _sample_all: FALSE (para sesiones activas e inactivas)

v$ASH_Info existe apartir de 11g

Imagen corresponde a 12c


ASH Architecture

9


ASH Dimensions

10


ASH Waiting

11

SESSION_STATE: Si muestra WAITING la sesión estaba esperando por algo, sino entonces muestra ON CPU para indicar que estaba haciendo trabajo productivo.

EVENT: Si session_state= WAITING, muestra el evento por el cual estaba esperando.

TIME_WAITED: Si session_state=WAITING, actualiza la última muestra del set con cuanto estuvo esperando por el EVENT.

WAIT_TIME: si session_state=ON CPU, muestra cuanto esperó por el último wait event, Sino es 0.


gv$Active_Session_History

12



13



14



15

Hint: Create Table My_ASH_xxx as select * from gv$active_session_history;


dba_hist_active_sess_history

16

Retention 8 días por default

dba_hist_wr_control

dbms_workload_repository.modify_snapshot_settings

(interval=>retention=>)


Otras vistas históricas

17

DBA_HIST_ACTIVE_SESS_HISTORYDBA_HIST_BASELINEDBA_HIST_BG_EVENT_SUMMARYDBA_HIST_BUFFERED_QUEUESDBA_HIST_BUFFERED_SUBSCRIBERSDBA_HIST_BUFFER_POOL_STATDBA_HIST_COMP_IOSTATDBA_HIST_CR_BLOCK_SERVERDBA_HIST_CURRENT_BLOCK_SERVERDBA_HIST_DATABASE_INSTANCEDBA_HIST_DATAFILEDBA_HIST_DB_CACHE_ADVICEDBA_HIST_DLM_MISCDBA_HIST_ENQUEUE_STATDBA_HIST_EVENT_NAMEDBA_HIST_FILEMETRIC_HISTORYDBA_HIST_FILESTATXSDBA_HIST_INSTANCE_RECOVERYDBA_HIST_INST_CACHE_TRANSFERDBA_HIST_JAVA_POOL_ADVICEDBA_HIST_LATCHDBA_HIST_LATCH_CHILDRENDBA_HIST_LATCH_MISSES_SUMMARYDBA_HIST_LATCH_NAMEDBA_HIST_LATCH_PARENT

DBA_HIST_LIBRARYCACHEDBA_HIST_LOGDBA_HIST_METRIC_NAMEDBA_HIST_MTTR_TARGET_ADVICEDBA_HIST_OPTIMIZER_ENVDBA_HIST_OSSTATDBA_HIST_OSSTAT_NAMEDBA_HIST_PARAMETERDBA_HIST_PARAMETER_NAMEDBA_HIST_PGASTATDBA_HIST_PGA_TARGET_ADVICEDBA_HIST_PROCESS_MEM_SUMMARYDBA_HIST_RESOURCE_LIMITDBA_HIST_ROWCACHE_SUMMARYDBA_HIST_RULE_SETDBA_HIST_SEG_STATDBA_HIST_SEG_STAT_OBJDBA_HIST_SERVICE_NAMEDBA_HIST_SERVICE_STATDBA_HIST_SERVICE_WAIT_CLASSDBA_HIST_SESSMETRIC_HISTORYDBA_HIST_SESS_TIME_STATSDBA_HIST_SGADBA_HIST_SGASTATDBA_HIST_SGA_TARGET_ADVICE


Otras vistas históricas

18

DBA_HIST_SHARED_POOL_ADVICEDBA_HIST_SNAPSHOTDBA_HIST_SNAP_ERRORDBA_HIST_SQLBINDDBA_HIST_SQLSTATDBA_HIST_SQLTEXTDBA_HIST_SQL_BIND_METADATADBA_HIST_SQL_PLANDBA_HIST_SQL_SUMMARYDBA_HIST_SQL_WORKAREA_HSTGRMDBA_HIST_STAT_NAMEDBA_HIST_STREAMS_APPLY_SUMDBA_HIST_STREAMS_CAPTUREDBA_HIST_STREAMS_POOL_ADVICEDBA_HIST_SYSMETRIC_HISTORYDBA_HIST_SYSMETRIC_SUMMARYDBA_HIST_SYSSTATDBA_HIST_SYSTEM_EVENTDBA_HIST_SYS_TIME_MODELDBA_HIST_TABLESPACE_STATDBA_HIST_TBSPC_SPACE_USAGEDBA_HIST_TEMPFILEDBA_HIST_TEMPSTATXSDBA_HIST_THREADDBA_HIST_UNDOSTAT

DBA_HIST_WAITCLASSMET_HISTORYDBA_HIST_WAITSTATDBA_HIST_WR_CONTROL


Queries de ejemplo y Caso de Uso con Vistas ASH


Top I/O Objects últimos 15 min

20

SELECT W.*, O.OBJECT_NAME FROM (Select current_obj#, round(sum(1000000/time_waited)) as est_IOwaits From v$active_session_history Where sample_time > sysdate - 15/1440 And time_waited > 0 And event in ('db file sequential read','db file scattered read') Group by current_obj# Order by 2 Desc ) W, DBA_OBJECTS OWHERE O.data_object_id = W.current_obj#AND rownum < 6;


Top I/O SQLs últimos 60 min

21

Select ash.sql_id, count(*) From v$active_session_history ash, v$event_name evtWhere ash.sample_time > sysdate - 1/24 And ash.session_state = 'WAITING'And ash.event_id = evt.event_idAnd evt.wait_class = 'User I/O' Group by sql_idOrder by count(*) desc;


Top Active últimos 60 min

22

Select sql_id, count(*), round(count(*) /sum(count(*)) over (), 2) pctload From v$active_session_history Where sample_time > sysdate -1/24/60 And session_type <> 'BACKGROUND' Group by sql_id Order by count(*) desc;


Top 5 queries últimos 60 min

23

SELECT * from ( select ash.con_id, ash.sql_id, dba_users.username, sqlarea.sql_text, sum(ash.wait_time + ash.time_waited) ttl_wait_time from v$active_session_history ash, v$sqlarea sqlarea, dba_users where ash.sample_time between sysdate - 1/24 and sysdate and ash.sql_id = sqlarea.sql_id and ash.user_id = dba_users.user_id group by ash.con_id, ash.sql_id,sqlarea.sql_text, dba_users.username order by 5 desc )WHERE rownum <6;


Top CPU usage CDB vs PDB

24


Ejemplo de caso simple de uso: Un Query varios días atrás estuvo consumiendo muchos

recursos, en especial espacio de TEMP Para este caso se cuenta con el SQL_ID= 9h1t5u7w9txxx El query estuvo corriendo el 31 de Julio, no se sabe la

hora exacta, ni quién lo estuvo ejecutando La solicitud para investigar se da el día 05 de Agosto Paso1: consultar dba_hist_active_sess_history filtrado por

SQLID y Fecha (“Datos Crudos y básicos”) Paso2: ajustar query agregando count y group (para

obtener “datos más diferenciados”) Paso3: agregar campos relevantes en el query para

análisis Paso4: consultar detalles requeridos en otras vistas

25


Paso1 SQL> select sample_time,SESSION_ID,SESSION_SERIAL#,event,SQL_ID,SQL_EXEC_START,BLOCKING_SESSION,BLOCKING_SESSION_SERIAL#from dba_hist_active_sess_historywhere sql_id='9h1t5u7w9txxx' and trunc(sample_time) = to_date('31-JUL-15')order by sample_time desc;

SAMPLE_TIME SESSION_ID SESSION_SERIAL# EVENT SQL_ID SQL_EXEC_ BLOCKING_SESSION BLOCKING_SESSION_SERIAL#------------------------- ---------- --------------- ----------------------------- ------------- --------- ---------------- ------------------------31-JUL-15 02.22.56.975 PM 298 41205 9h1t5u7w9txxx 31-JUL-1531-JUL-15 02.22.46.965 PM 298 41205 9h1t5u7w9txxx 31-JUL-1531-JUL-15 02.22.36.955 PM 298 41205 9h1t5u7w9txxx 31-JUL-1531-JUL-15 02.22.26.915 PM 298 41205 9h1t5u7w9txxx 31-JUL-1531-JUL-15 02.22.16.905 PM 298 41205 9h1t5u7w9txxx 31-JUL-1531-JUL-15 02.22.06.895 PM 298 41205 9h1t5u7w9txxx 31-JUL-15***31-JUL-15 02.14.25.869 PM 298 41205 9h1t5u7w9txxx 31-JUL-1531-JUL-15 02.14.15.849 PM 298 41205 9h1t5u7w9txxx 31-JUL-1531-JUL-15 02.14.05.829 PM 298 41205 9h1t5u7w9txxx 31-JUL-1531-JUL-15 02.13.55.809 PM 298 41205 9h1t5u7w9txxx 31-JUL-1531-JUL-15 02.13.45.789 PM 298 41205 9h1t5u7w9txxx 31-JUL-1531-JUL-15 02.13.35.769 PM 298 41205 9h1t5u7w9txxx 31-JUL-15--31-JUL-15 02.03.44.393 PM 1565 42313 9h1t5u7w9txxx 31-JUL-1531-JUL-15 02.03.34.373 PM 1565 42313 9h1t5u7w9txxx 31-JUL-1531-JUL-15 02.03.24.333 PM 1565 42313 9h1t5u7w9txxx 31-JUL-1531-JUL-15 02.03.14.286 PM 1565 42313 9h1t5u7w9txxx 31-JUL-1531-JUL-15 02.03.04.276 PM 1565 42313 9h1t5u7w9txxx 31-JUL-1531-JUL-15 02.02.54.256 PM 1565 42313 9h1t5u7w9txxx 31-JUL-15***31-JUL-15 01.46.32.155 PM 1565 42313 9h1t5u7w9txxx 31-JUL-1531-JUL-15 01.46.22.145 PM 1565 42313 9h1t5u7w9txxx 31-JUL-1531-JUL-15 01.46.12.125 PM 1565 42313 9h1t5u7w9txxx 31-JUL-1531-JUL-15 01.46.02.115 PM 1565 42313 9h1t5u7w9txxx 31-JUL-1531-JUL-15 01.45.52.105 PM 1565 42313 9h1t5u7w9txxx 31-JUL-1531-JUL-15 01.45.42.085 PM 1565 42313 9h1t5u7w9txxx 31-JUL-15

166 rows selected.

SQL>

26


Paso 2 y 3 SQL> select count(*), trunc(sample_time), SESSION_ID, SESSION_SERIAL#, event, SQL_ID, SQL_EXEC_START, BLOCKING_SESSION, BLOCKING_SESSION_SERIAL#from dba_hist_active_sess_historywhere sql_id='9h1t5u7w9txxx' and trunc(sample_time) = to_date('31-JUL-15')Group by trunc(sample_time), SESSION_ID, SESSION_SERIAL#, event, SQL_ID, SQL_EXEC_START, BLOCKING_SESSION, BLOCKING_SESSION_SERIAL#order by 2 desc; ;

COUNT(*) TRUNC(SAM SESSION_ID SESSION_SERIAL# EVENT SQL_ID SQL_EXEC_ BLOCKING_SESSION BLOCKING_SESSION_SERIAL#---------- --------- ---------- --------------- ----------------------------- ------------- --------- ---------------- ------------------------ 109 31-JUL-15 1565 42313 9h1t5u7w9txxx 31-JUL-15 57 31-JUL-15 298 41205 9h1t5u7w9txxx 31-JUL-15

SQL>

SQL> select count(*), trunc(sample_time), SESSION_ID, SESSION_SERIAL#, SQL_ID, SQL_EXEC_START, BLOCKING_SESSION, avg(PGA_ALLOCATED)/1024/1024 PGA_MB, avg(TEMP_SPACE_ALLOCATED)/1024/1024 TEMP_MB, USER_ID, Client_id, module, MACHINE, PROGRAMfrom dba_hist_active_sess_historywhere sql_id='9h1t5u7w9txxx' and trunc(sample_time) = to_date('31-JUL-15')Group by trunc(sample_time), SESSION_ID, SESSION_SERIAL#, SQL_ID, SQL_EXEC_START, BLOCKING_SESSION, BLOCKING_SESSION_SERIAL#, User_id, Client_id, module, MACHINE, PROGRAM;

COUNT(*) TRUNC(SAM SESSION_ID SESSION_SERIAL# SQL_ID SQL_EXEC_ PGA_MB TEMP_MB USER_ID CLIENT_ID MODULE---------- --------- ---------- --------------- ------------- --------- ---------- ---------- ---------- ---------- ------------------MACHINE PROGRAM-------------------------- -------------- 57 31-JUL-15 298 41205 9h1t5u7w9txxx 31-JUL-15 558.836383 17281.6429 74 SWLODARX PSQUERYPROLOGIS-NA\PLDANPSX1 psae.exe

109 31-JUL-15 1565 42313 9h1t5u7w9txxx 31-JUL-15 551.75138 32956.578 74 SWLODARX VCHR_EXPRESSpldanappx3 PSAPPSRV@pldanappp3 (TNS V1-V3)

SQL>

27


Nótese cambio en PGA y TEMP SQL> select sample_time,SESSION_ID,SESSION_SERIAL#, SQL_ID,SQL_EXEC_START,BLOCKING_SESSION, PGA_ALLOCATED/1024/1024 PGA_mb, TEMP_SPACE_ALLOCATED/1024/1024 TEMP_mbfrom dba_hist_active_sess_historywhere sql_id='9h1t5u7w9txxx' and trunc(sample_time) = to_date('31-JUL-15')order by sample_time desc;

SAMPLE_TIME SESSION_ID SESSION_SERIAL# SQL_ID SQL_EXEC_ BLOCKING_SESSION PGA_MB TEMP_MB------------------------- ---------- --------------- ------------- --------- ---------------- ---------- ----------31-JUL-15 02.22.56.975 PM 298 41205 9h1t5u7w9txxx 31-JUL-15 431.064453 3389731-JUL-15 02.22.46.965 PM 298 41205 9h1t5u7w9txxx 31-JUL-15 431.064453 3347531-JUL-15 02.22.36.955 PM 298 41205 9h1t5u7w9txxx 31-JUL-15 431.064453 3280031-JUL-15 02.22.26.915 PM 298 41205 9h1t5u7w9txxx 31-JUL-15 430.939453 3222331-JUL-15 02.22.16.905 PM 298 41205 9h1t5u7w9txxx 31-JUL-15 627.939453 31704***31-JUL-15 02.14.25.869 PM 298 41205 9h1t5u7w9txxx 31-JUL-15 100.314453 307831-JUL-15 02.14.15.849 PM 298 41205 9h1t5u7w9txxx 31-JUL-15 535.689453 230031-JUL-15 02.14.05.829 PM 298 41205 9h1t5u7w9txxx 31-JUL-15 368.064453 182331-JUL-15 02.13.55.809 PM 298 41205 9h1t5u7w9txxx 31-JUL-15 252.876953 124631-JUL-15 02.13.45.789 PM 298 41205 9h1t5u7w9txxx 31-JUL-15 159.314453 70331-JUL-15 02.13.35.769 PM 298 41205 9h1t5u7w9txxx 31-JUL-15 463.001953

31-JUL-15 02.03.44.393 PM 1565 42313 9h1t5u7w9txxx 31-JUL-15 629.376953 6636531-JUL-15 02.03.34.373 PM 1565 42313 9h1t5u7w9txxx 31-JUL-15 629.376953 6562431-JUL-15 02.03.24.333 PM 1565 42313 9h1t5u7w9txxx 31-JUL-15 629.189453 6507731-JUL-15 02.03.14.286 PM 1565 42313 9h1t5u7w9txxx 31-JUL-15 629.189453 6453231-JUL-15 02.03.04.276 PM 1565 42313 9h1t5u7w9txxx 31-JUL-15 629.189453 6398831-JUL-15 02.02.54.256 PM 1565 42313 9h1t5u7w9txxx 31-JUL-15 629.189453 63355***31-JUL-15 01.46.32.155 PM 1565 42313 9h1t5u7w9txxx 31-JUL-15 208.189453 307831-JUL-15 01.46.22.145 PM 1565 42313 9h1t5u7w9txxx 31-JUL-15 536.251953 244431-JUL-15 01.46.12.125 PM 1565 42313 9h1t5u7w9txxx 31-JUL-15 369.314453 182431-JUL-15 01.46.02.115 PM 1565 42313 9h1t5u7w9txxx 31-JUL-15 254.126953 124731-JUL-15 01.45.52.105 PM 1565 42313 9h1t5u7w9txxx 31-JUL-15 119.876953 70431-JUL-15 01.45.42.085 PM 1565 42313 9h1t5u7w9txxx 31-JUL-15 334.376953 1

166 rows selected.

SQL>

28


Paso4 SQL> select count(*), trunc(sample_time), SESSION_ID, SESSION_SERIAL#, SQL_ID, SQL_EXEC_START, BLOCKING_SESSION, avg(PGA_ALLOCATED)/1024/1024 PGA_MB, avg(TEMP_SPACE_ALLOCATED)/1024/1024 TEMP_MB, USER_ID, Client_id, module, MACHINE, PROGRAMfrom dba_hist_active_sess_historywhere sql_id='9h1t5u7w9txxx' and trunc(sample_time) = to_date('31-JUL-15')Group by trunc(sample_time), SESSION_ID, SESSION_SERIAL#, SQL_ID, SQL_EXEC_START, BLOCKING_SESSION, BLOCKING_SESSION_SERIAL#, User_id, Client_id, module, MACHINE, PROGRAMorder by 2 desc;

COUNT(*) TRUNC(SAM SESSION_ID SESSION_SERIAL# SQL_ID USER_ID CLIENT_ID MODULE MACHINE ---------- --------- ---------- --------------- ------------- ---------- ---------- ------------------ ---------------------- 57 31-JUL-15 298 41205 9h1t5u7w9txxx 74 SWLODARX PSQUERY PROLOGIS-NA\PLDANPSPX1 109 31-JUL-15 1565 42313 9h1t5u7w9txxx 74 SWLODARX VCHR_EXPRESS pldanappx3 SQL>

SQL> select username from dba_users where user_id = 74;USERNAME------------------------------SYSADM

SQL>

SQL> select SQL_TEXT from DBA_HIST_SQLTEXT where SQL_ID='9h1t5u7w9txxx'SQL_TEXT------------------------------------------------------------------------------------------------------------SELECT A.BUSINESS_UNIT, A.PROJECT_ID, TO_CHAR(A.EFFDT,'YYYY-MM-DD'), A.PLD_STRATEGY, A.PLD_PROJ_SOURCE, A.PLD_MNGMNT_STRUCT, A.PLD_ACCT_TREATMENT, C.PLD_OWNERSHIP_TYPE, C.FUND_NAME, B.PLD_MARKET_TYPE, A.PLD_SUPP_PORTFOLIO, B.PLD_PROP_USG_TYPEFROM PS_PLD_E005_USFLD2 A, PS_PLD_E005_USFLD1 B, PS_PLD_E005_OWNSHP C WHERE ( A.EFFDT = (SELECT MAX(A_ED.EFFDT) FROM PS_PLD_E005_USFLD2 A_ED WHERE A.BUSINESS_UNIT = A_ED.BUSINESS_UNIT AND A.PROJECT_ID = A_ED.PROJECT_ID AND A_ED.EFFDT <= SYSDATE)AND A.PLD_SUPP_PORTFOLIO NOT IN ('003') AND A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.BUSINESS_UNIT = C.BUSINESS_UNIT AND A.PROJECT_ID = B.PROJECT_ID AND A.PROJECT_ID = C.PROJECT_ID AND C.EFFDT = (SELECT MAX(C_ED.EFFDT) FROM PS_PLD_E005_OWNSHP C_ED WHERE C.BUSINESS_UNIT = C_ED.BUSINESS_UNIT AND C.PROJECT_ID = C_ED.PROJECT_ID AND C_ED.EFFDT <= SYSDATE) OR NOT A.PLD_STRATEGY IS NULL OR A.PLD_PROJ_SOURCE IS NULL OR A.PLD_MNGMNT_STRUCT IS NULL OR A.PLD_ACCT_TREATMENT IS NULL OR C.PLD_OWNERSHIP_TYPE IS NULL OR C.FUND_NAME IS NULL OR A.PLD_SUPP_PORTFOLIO IS NULL OR B.PLD_PROP_USG_TYPE IS NULL ) ORDER BY 2SQL>

29


Paso4 SQL> select * from table(dbms_xplan.display_cursor('9h1t5u7w9txxx'))

PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------SQL_ID: 9h1t5u7w9txxx, child number: 0 cannot be found

SQL>

SQL> select id, lpad(' ',depth*1,' ')||operation || ' ' || options plan_step, object_name, cost, cardinality, bytes, optimizerfrom dba_hist_sql_plan where sql_id='9h1t5u7w9txxx'order by id;

SQL>

ID PLAN_STEP OBJECT_NAME COST CARDINALITY BYTES OPTIMIZER---------- ---------------------------------------- ------------------------------- ---------- ----------- ---------- -------------------- 0 SELECT STATEMENT 8116555075 ALL_ROWS 1 SORT ORDER BY 8116555075 3.0577E+12 1.9875E+14 2 MERGE JOIN CARTESIAN 6395895786 3.0577E+12 1.9875E+14 3 MERGE JOIN CARTESIAN 828125 210683990 1.1166E+10 4 TABLE ACCESS FULL PS_PLD_E005_USFLD2 69 15829 696476 5 BUFFER SORT 828056 13310 119790 6 TABLE ACCESS FULL PS_PLD_E005_USFLD1 52 13310 119790 7 BUFFER SORT 8116555023 14513 174156 8 TABLE ACCESS FULL PS_PLD_E005_OWNSHP 30 14513 174156

9 rows selected.

SQL>

30


Execution Plan AWR formatoSQL> select * from table(dbms_xplan.display_awr('9h1tsu7w9grxh'));

PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------SQL_ID 9h1tsu7w9grxh--------------------SELECT A.BUSINESS_UNIT, A.PROJECT_ID, TO_CHAR(A.EFFDT,'YYYY-MM-DD'),A.PLD_STRATEGY, A.PLD_PROJ_SOURCE, A.PLD_MNGMNT_STRUCT,A.PLD_ACCT_TREATMENT, C.PLD_OWNERSHIP_TYPE, C.FUND_NAME,B.PLD_MARKET_TYPE, A.PLD_SUPP_PORTFOLIO, B.PLD_PROP_USG_TYPE FROMPS_PLD_E005_USFLD2 A, PS_PLD_E005_USFLD1 B, PS_PLD_E005_OWNSHP CWHERE ( A.EFFDT = (SELECT MAX(A_ED.EFFDT) FROMPS_PLD_E005_USFLD2 A_ED WHERE A.BUSINESS_UNIT =A_ED.BUSINESS_UNIT AND A.PROJECT_ID = A_ED.PROJECT_ID AND A_ED.EFFDT <= SYSDATE) AND A.PLD_SUPP_PORTFOLIO NOT IN('003') AND A.BUSINESS_UNIT = B.BUSINESS_UNIT ANDA.BUSINESS_UNIT = C.BUSINESS_UNIT AND A.PROJECT_ID = B.PROJECT_ID AND A.PROJECT_ID = C.PROJECT_ID AND C.EFFDT =(SELECT MAX(C_ED.EFFDT) FROM PS_PLD_E005_OWNSHP C_ED WHEREC.BUSINESS_UNIT = C_ED.BUSINESS_UNIT AND C.PROJECT_ID =C_ED.PROJECT_ID AND C_ED.EFFDT <= SYSDATE) OR NOTA.PLD_STRATEGY IS NULL OR A.PLD_PROJ_SOURCE IS NULL ORA.PLD_MNGMNT_STRUCT IS NULL OR A.PLD_ACCT_TREATMENT IS NULLOR C.PLD_OWNERSHIP_TYPE IS NULL OR C.FUND_NAME IS NULL ORA.PLD_SUPP_PORTFOLIO IS NULL OR B.PLD_PROP_USG_TYPE IS NULL )ORDER BY 2

Plan hash value: 1996959972

-----------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |-----------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | | | | 8116M(100)| || 1 | SORT ORDER BY | | 3057G| 180T| 212T| 8116M (6)|999:59:59 || 2 | MERGE JOIN CARTESIAN | | 3057G| 180T| | 6395M (1)|999:59:59 || 3 | MERGE JOIN CARTESIAN| | 210M| 10G| | 828K (1)| 02:45:38 || 4 | TABLE ACCESS FULL | PS_PLD_E005_USFLD2 | 15829 | 680K| | 69 (2)| 00:00:01 || 5 | BUFFER SORT | | 13310 | 116K| | 828K (1)| 02:45:37 || 6 | TABLE ACCESS FULL | PS_PLD_E005_USFLD1 | 13310 | 116K| | 52 (0)| 00:00:01 || 7 | BUFFER SORT | | 14513 | 170K| | 8116M (6)|999:59:59 || 8 | TABLE ACCESS FULL | PS_PLD_E005_OWNSHP | 14513 | 170K| | 30 (0)| 00:00:01 |-----------------------------------------------------------------------------------------------------

39 rows selected.

SQL>

31


Herramienta Open Source S-ASH


Que es S-ASH ?

33

OraSASH: Simulating Active Session History (no es Oracle Sash, que es Sapphire Shell y no tiene relación)

S-ASH es una herramienta Open Source, entonces a diferencia de ASH, no requiere de la Licencia de Tuning and Diagnostics Pack.

Permite al DBA colectar información de Sesiones Activas (al igual que ASH) y salvarla en un repositorio para acceso futuro (igual como lo hacen los procesos AWR)

Utiliza vistas dinámicas y está disponible en cualquier apartir de Oracle 9i (ASH solo apartir de Oracle 10g)

Está soportado no solo para la versión Enterprise Edition (como ASH), sino que además en Standard Edition e inclusive en la versión Express Edition.

Sitio actual es: http://pioro.github.io/orasash/ Manual: http://pioro.github.io/orasash/manual.html

http://pioro.github.io/orasash/

http://pioro.github.io/orasash/manual.html


Características S-ASH ?

34

S-ASH Funciona bajo el concepto de DB Target y DB Repository

SASH: Es el Schema para monitoreo y colecta de stats SASH_REPO: Es el Schema para el repositorio de los

datos colectos por SASH En 12c si se están usando Pluggable DBs, en el Container

la Instalación requiere que el schema se llame C##SASH El job sash_repo.purge_tables purga la información

basado en la retención definida (por default es un mes) Podría afectar un poco el Performance mientras está

encendido (el impacto debería ser mínimo) S-SAH se puede encender y apagar por medio de sus Jobs Algunas Tablas Históricas (similares a las de AWR) pueden

no estar presentes o bien tener otro nombre


Instalando S-ASH

35

Paso1: Se descomprimen los binarios sash.tar.gz o sash.7z

Paso2: Se conecta a la Base Datos Target como sysdba y ejecuta el script: targ_userview_<db_version>.sql

Paso3: Se conecta a la Base Datos Repository como sysdba y ejecta el script: config.sql

Paso4: Se conecta a la Base Datos Repository como SASH (o C##SASH si es 12c) y ejecuta el script: job_stat.sql

Más detalles en:

http://pioro.github.io/orasash/installation.html

http://www.pythian.com/blog/trying-out-s-ash/

http://pioro.github.io/orasash/installation.html

http://www.pythian.com/blog/trying-out-s-ash/


Scripts S-ASH

36


Jobs / Scripts de Control

37

Iniciar colecta de datos:SQL> @start.sql

SQL> exec sash_repo.start_collecting_jobs;

Detener colecta de datos:SQL> @stop

SQL> exec sash_repo.stop_collecting_jobs;

Revisión de estado de los jobs:SQL> @job_stat.sql

Parámetros de Retención:exec sash_repo.set_retention('<rtype>');

'd' := 7 días, 'w':= 1 Mes, 'h':= 24 horas, 'm':= 30 Minutos


Estado y encendido de S-ASH

38

SQL> exec sash_repo.start_collecting_jobs;


sash.v$active_session_history

39

Atributos similares a gv$ASH Version 10g

DBA_HIST_ACTIVE_SESS_HISTORYTiene los mismos campos + SNAP_ID


Vistas S-ASH

40


Otras tablas importantes

41

SASH_LOGSASH_STATSSASH_INSTANCE_STATSSASH_HIST_SAMPLESASH_SQLPLANSSASH_PARAMSSASH_EVENT_NAMESSASH_DATA_FILESSASH_USERSSASH_LATCHSASH_SESSTATSASH_SQLIDSSASH_SQLTXTSASH_SQLSTATS1SASH_OBJSSASH_TARGET_STATICSASH_TARGETS

SASH_EXTENTSWAITGROUPSSASH_CONFIGURATIONSASH_IO_SYSTEM_EVENTSASH_SYSMETRIC_HISTORYSASH_SYSMETRIC_NAMESSASH_IOFUNCSTATSSASH_EVENT_HISTOGRAM1SASH_SYS_TIME_NAMESASH_OSSTAT_NAMESASH_SYS_TIME_MODELSASH_OSSTATSASH_HOUR_SQLIDSASH_TARGET_DYNAMIC


Top Recent Waits con S-ASH

42


Top 10 SQL with the highest I/O en la última semana con S-ASH

43


Algunos queries necesitan ajustes para correr en S-ASH

44

Top SESSION by CPU usage, wait time and IO time select ash.session_id, ash.session_serial#, ash.user_id, ash.program, sum(decode(ash.session_state,'ON CPU',1,0)) "CPU", sum(decode(ash.session_state,'WAITING',1,0)) - sum(decode(ash.session_state,'WAITING', decode(en.wait_class,'User I/O',1, 0 ), 0)) "WAITING" , sum(decode(ash.session_state,'WAITING', decode(en.wait_class,'User I/O',1, 0 ), 0)) "IO" , sum(decode(session_state,'ON CPU',1,1)) "TOTAL" from v$active_session_history ash, v$event_name en where en.event# = ash.event# group by session_id,user_id,session_serial#,program order by sum(decode(session_state,'ON CPU',1,1));

VS select sash.session_id, sash.session_serial#, sash.user_id, sash.program, sum(decode(sash.session_state,'ON CPU',1,0)) "CPU", sum(decode(sash.session_state,'WAITING',1,0)) - sum(decode(sash.session_state,'WAITING', decode(en.wait_class,'User I/O',1, 0 ), 0)) "WAITING" , sum(decode(sash.session_state,'WAITING', decode(en.wait_class,'User I/O',1, 0 ), 0)) "IO" , sum(decode(session_state,'ON CPU',1,1)) "TOTAL" from v$active_session_history sash, SASH_EVENT_NAMES en where en.event# = sash.event# group by session_id,user_id,session_serial#,program order by sum(decode(session_state,'ON CPU',1,1));


Algunos queries necesitan ajustes para correr en S-ASH

45

Top 5 SQLs lentos en la última semana SELECT a.sql_id, a.sql_type, round(a.sample_cnt_sql/a.sample_cnt_total*100) as sample_pct, round(a.sample_cnt_sql /6,2) Minutes, b.SQL_TextFROM(SELECT DISTINCT sql_id, aa.name AS sql_type, COUNT(*) over (PARTITION BY sql_id) AS sample_cnt_sql, COUNT(*) over () AS sample_cnt_totalFROM dba_hist_active_sess_history ashJOIN dba_hist_snapshot s USING (dbid, INSTANCE_NUMBER, snap_id)JOIN audit_actions aa on (ash.sql_opcode = aa.action)WHERE begin_interval_time BETWEEN sysdate - 7 and sysdateORDER BY 3 desc) a JOIN DBA_HIST_SQLTEXT b on (a.sql_id = b.sql_id)WHERE rownum <= 5;

VS

SELECT a.sql_id, a.sql_type, round(a.sample_cnt_sql/a.sample_cnt_total*100) as sample_pct, round(a.sample_cnt_sql /6,2) Minutes, b.SQL_TextFROM(SELECT DISTINCT sql_id, aa.name AS sql_type, COUNT(*) over (PARTITION BY sql_id) AS sample_cnt_sql, COUNT(*) over () AS sample_cnt_totalFROM dba_hist_active_sess_history ashJOIN dba_hist_snapshot s USING (dbid, INSTANCE_NUMBER, snap_id)JOIN audit_actions aa on (ash.sql_opcode = aa.action)WHERE begin_interval_time BETWEEN sysdate - 7 and sysdateORDER BY 3 desc) a JOIN SASH_SQLTXT b on (a.sql_id = b.sql_id)WHERE rownum <= 5;


Estado y apagado de S-ASH

47

SQL> exec sash_repo.stop_collecting_jobs;

© 2012 – Pythian

Thank you and Q&A

http://www.pythian.com/blog/

http://www.facebook.com/pages/The-Pythian-Group/163902527671

@pythian

http://www.linkedin.com/company/pythian

1-877-PYTHIAN

[email protected]

To contact us…

To follow us…

@pythianjobs

hangover - que pasó ayer? troubleshooting con vistas ash & s-ash

Technology