Oracle 10g Performance: chapter 01 ash

  • Published on
    26-Jan-2015

  • View
    108

  • Download
    0

DESCRIPTION

 

Transcript

  • 1. ASH Active Session HistoryFeel the Power Kyle Haileyhttp://perfvision.com#.1

2. Why should you care?Because ASH can Change your life 10g immediately Accessible Geeks: Via scripts in SQL Managers : Graphical OEM Stuck on Oracle 7,8,9 ? my apologies because 10g rocks, but no worries, the data is accessible for you too via scripts http://perfvision.com/sash.php Copyright 2006 Kyle Hailey#.2 3. Revolution and an EvolutionOracle 6 ie the dark ages there was once the Cache Buffer Hit RatioOracle 7 turned the lights on Wait Events hallelujah I can see the lightOracle 10g ASH has landed(like the wheels on the suitcases)Copyright 2006 Kyle Hailey #.3 4. SamplingNew Paradigm Clear Vision of the future Use new sampling technology Lets go of the need to know 100% Statistical approximation Cheaper Richer Multidimensional Data Identifies SQL Sessions Objects Machine UserCopyright 2006 Kyle Hailey #.4 5. Identify Players before ASH Sessions v$sesstat, v$session_event # sessions x (# wait events + statistics) Example (150 x (800+200) = 150,000 ) SQL v$sql Could be 10000s Takes out latches that compete with other sql executions Objects Also files v$filestat V$segstat9i+ Could be 1000s of objectsExpensive !Copyright 2006 Kyle Hailey #.5 6. ASHIntelligence for the new Millennium Intelligently Collects Data Samples once a second Collects active sessions only Collects Session/SQL/Wait More activity, more data collected Less activity, less data collected Those old methods collected everything Obfuscated the problem, too many statistics too late Costly Too Granular once an hour ?! Give me a break Collects fine grain multidimensional dataCopyright 2006 Kyle Hailey #.6 7. ASH FieldsSQL> v$active_session_history Name Null?TypeSAMPLE_ID NUMBERSAMPLE_TIME TIMESTAMP(3)SESSION_IDNUMBERSESSION_SERIAL# NUMBERUSER_ID NUMBERSQL_IDVARCHAR2(13)SQL_CHILD_NUMBERNUMBERSQL_PLAN_HASH_VALUE NUMBERSQL_OPCODENUMBERSERVICE_HASHNUMBERSESSION_TYPEVARCHAR2(10)SESSION_STATE VARCHAR2(7)QC_SESSION_ID NUMBERQC_INSTANCE_IDNUMBEREVENT VARCHAR2(64)EVENT_IDNUMBEREVENT#NUMBERSEQ#NUMBERP1NUMBERP2NUMBERP3NUMBERWAIT_TIME NUMBERTIME_WAITED NUMBERCURRENT_OBJ#NUMBERCURRENT_FILE# NUMBERCURRENT_BLOCK#NUMBERPROGRAM VARCHAR2(48)MODULEVARCHAR2(48)ACTIONVARCHAR2(32)CLIENT_ID Copyright 2006 Kyle HaileyVARCHAR2(64#.7 8. v$active_session_history WhenSAMPLE_ID SAMPLE_TIMENUMBERTIMESTAMP(3) SESSION_ID NUMBER SESSION_SERIAL#NUMBER Session USER_ID SERVICE_HASHNUMBERNUMBER SESSION_TYPE VARCHAR2(10) PROGRAMVARCHAR2(64) MODULE VARCHAR2(48) ACTION VARCHAR2(32) CLIENT_IDVARCHAR2(64)State SESSION_STATE VARCHAR2(7)WAIT_TIME NUMBEREVENT VARCHAR2(64)EVENT_IDNUMBEREVENT#NUMBERSEQ#NUMBER Wait P1NUMBERP2NUMBERP3NUMBERWAIT_TIME NUMBERTIME_WAITED NUMBERCURRENT_OBJ#NUMBERCURRENT_FILE# NUMBERCURRENT_BLOCK#NUMBER0SQL_IDVARCHAR2(13)SQL_CHILD_NUMBERNUMBER SQLSQL_PLAN_HASH_VALUESQL_OPCODENUMBERNUMBERQC_SESSION_ID NUMBERQC_INSTANCE_IDNUMBER Duration TIME_WAITED Copyright 2006 Kyle HaileyNUMBER #.8 9. Primary Fields of ASHActivity : Who, What, When, HowWhenSAMPLE_TIME TimeWhoSESSION_ID Session SESSION_STATEStateHow EVENTWaitWhat SQL_ID SQL Copyright 2006 Kyle Hailey #.9 10. Amazing things YOU can doTop SQLTop Consumers Top Resources SQL ID Session CPU Plan X User ObjectX Waits Child# Module.Action Event (800*) Program I/O Service File Client Block Wait Time And Aggregated over any time Period(32 columns in ASH 10.1)Copyright 2006 Kyle Hailey #.10 11. Multi-dimensionalSQLF1qcyh20550cfPackageProcedure fj6gjgsshtxyxPlan0cjsxw5ndqdbcChild #8t8as9usk11qwdr1rkrznhh95bW St a10dkqv3kr8xa5 C at its38zhkf4jdyff4En PU ist298wmz1kxjs1m S q:db QL TX icsdb fil *Ne 25 34 36 38 45 63 65 87 fil e s t b rowSessions e ca r ese tt a locqu ere k/r ken d es coProgramSqlplusToadt i a re e t n t e l r ad to nteac l io d ie n ntUserSys ScottSystem ApplServiceicGLOEIOat ion #.11 12. Groupings Top ConsumerSESSION_IDSESSION_SERIAL# (identify SID reuse)SESSION_TYPE(FOREGROUND,BACKGROUND)USER_ID (SYS, SYSTEM, SCOTT etc)SERVICE_HASH(OE,GL,HR)MODULE.ACTION (PLSQL tagging)CLIENT_ID (identifying users in session pool)PROGRAM (SQL, JDBC, Forms etc)SQL_IDQC_SESSION_ID (Query Coordinator)QC_INSTANCE_ID(RAC)EVENT + P1, P2, P3CURRENT_OBJ#CURRENT_FILE#CURRENT_BLOCK# Copyright 2006 Kyle Hailey #.12 13. Top CPU SessionTop CPU consuming Session in last 5 minutes Select Selectsession_id,Who is the rogue session ? session_id,count(*) count(*) fromfromv$active_session_history v$active_session_history where wheresession_state= ON CPU and session_state= ON CPU andSAMPLE_TIME > sysdate (5/(24*60)) SAMPLE_TIME > sysdate (5/(24*60)) group by group bysession_idsession_id order by order by count(*) desc;count(*) desc;Copyright 2006 Kyle Hailey#.13 14. Results Top CPU Session SESSION_ID SESSION_ID COUNT(*)COUNT(*) ---------- ---------- ---------- ----------257257299 299263263 6262256256 32322642649 92772773 32582581 1 Copyright 2006 Kyle Hailey #.14 15. CPU with Bars SESSION_IDSESSION_ID COUNT(*) COUNT(*) % Bar % Bar ---------- ---------- ---------- ---------------------- ---------- ---------- ------------257 257 29929999 |**********|99 |**********|263 26362 6221 |**21 |** ||256 25632 3211 |*11 |*||264 264 99 3 | 3 | ||277 277 33 1 | 1 | ||258 258 11 0 | 0 | ||280 280 11 0 | 0 | || Bar shows 10% increments Copyright 2006 Kyle Hailey #.15 16. Top Waiting Session Top Waiting Session in last 5 minutesSelect Select session_id,session_id, count(*)count(*)from from v$active_session_historyv$active_session_historywhere where session_state=WAITING andsession_state=WAITING and SAMPLE_TIME > SYSDATE -- (5/(24*60))SAMPLE_TIME > SYSDATE (5/(24*60))group by group by session_idsession_idorder by order by count(*) desc;count(*) desc;Copyright 2006 Kyle Hailey#.16 17. Top Waiting Session ResultsSESSION_IDSESSION_ID COUNT(*) COUNT(*)---------- -------------------- ---------- 272 272224224 254 25488 249 24955 276 27655 277 27744 270 27011Copyright 2006 Kyle Hailey #.17 18. Top SQL from ASHselect select ash.SQL_ID , ,ash.SQL_IDsum(decode(ash.session_state,ON CPU,1,0)) sum(decode(ash.session_state,ON CPU,1,0)) "CPU","CPU",sum(decode(ash.session_state,WAITING,1,0)) sum(decode(ash.session_state,WAITING,1,0))--sum(decode(ash.session_state,WAITING, decode(en.wait_class, User I/O,1,0),0)) sum(decode(ash.session_state,WAITING, decode(en.wait_class, User I/O,1,0),0)) "WAIT" , ,"WAIT"sum(decode(ash.session_state,WAITING, decode(en.wait_class, User I/O,1,0),0)) sum(decode(ash.session_state,WAITING, decode(en.wait_class, User I/O,1,0),0)) "IO" , ,"IO" sum(decode(ash.session_state,ON CPU,1,1)) "TOTAL"sum(decode(ash.session_state,ON CPU,1,1)) "TOTAL"from v$active_session_history ash, from v$active_session_history ash,v$event_name en v$event_name enwhere SQL_ID is not NULL and en.event#=ash.event# where SQL_ID is not NULL and en.event#=ash.event#group by sql_id group by sql_idorder by sum(decode(session_state,ON CPU,1,1)) desc order by sum(decode(session_state,ON CPU,1,1)) descCopyright 2006 Kyle Hailey #.18 19. Top SQL from ASH ResultsSQL_ID SQL_IDCPUCPU WAITING WAITINGIO IO TOTAL TOTAL------------- ---------- ---------- ---------- ---------- ------------- ---------- ---------- ---------- ----------4c1xvq9ufwcjc 4c1xvq9ufwcjc 2338623386 00 00 23386 233866wjw6rz5uvbp3 6wjw6rz5uvbp399 99 0023 23 122 122968dm8hr9qd03 968dm8hr9qd0397 97 0022 22 119 119938jp5gasmrah 938jp5gasmrah90 90 0025 25 115 115cv8xnv81kf582 cv8xnv81kf58242 42 00 9951516p9bzu19v965k 6p9bzu19v965k21 21 00 0021215zu8pxnun66bu 5zu8pxnun66bu15 15 00 001515db2jr13nup72v db2jr13nup72v 99 00 00 997ks5gnj38hghv 7ks5gnj38hghv 88 00 00 88 Copyright 2006 Kyle Hailey#.19 20. Top Session select selectselect ash.session_id, ash.session_id,ash.session_id, ash.session_serial#, ash.session_serial#,ash.session_serial#, ash.user_id, ash.user_id,ash.user_id, ash.program, ash.program,ash.program, sum(decode(ash.session_state,ON CPU,1,0)) "CPU", sum(decode(ash.session_state,ON CPU,1,0)) "CPU",sum(decode(ash.session_state,ON CPU,1,0)) "CPU", sum(decode(ash.session_state,WAITING,1,0)) -- - sum(decode(ash.session_state,WAITING,1,0))sum(decode(ash.session_state,WAITING,1,0)) sum(decode(ash.session_state,WAITING, sum(decode(ash.session_state,WAITING,sum(decode(ash.session_state,WAITING, decode(en.wait_class,User I/O,1, 0 ), 0)) 0))"WAITING", , ,decode(en.wait_class,User I/O,1, 0 ),0 ), "WAITING" decode(en.wait_class,User I/O,1, 0)) "WAITING" sum(decode(ash.session_state,WAITING, sum(decode(ash.session_state,WAITING,sum(decode(ash.session_state,WAITING, decode(en.wait_class,User I/O,1, 0 ), 0)) 0))"IO", , ,decode(en.wait_class,User I/O,1, 0 ),0 ), "IO" decode(en.wait_class,User I/O,1, 0)) "IO" sum(decode(session_state,ON CPU,1,1)) "TOTAL" sum(decode(session_state,ON CPU,1,1)) "TOTAL"sum(decode(session_state,ON CPU,1,1)) "TOTAL" from v$active_session_history ash,from v$active_session_history ash,from v$active_session_history ash, v$event_name en en v$event_namev$event_name en where en.event# = ash.event#where where en.event# = ash.event#en.event# = ash.event# group by session_id,user_id,session_serial#,programgroup by session_id,user_id,session_serial#,programgroup by session_id,user_id,session_serial#,program order by sum(decode(session_state,ON CPU,1,1))order by sum(decode(session_state,ON CPU,1,1))order by sum(decode(session_state,ON CPU,1,1)) Copyright 2006 Kyle Hailey#.20 21. Top Session ResultsSESSION_ID SERIAL#USER_ID PROGRAMCPUWAITING IO SESSION_ID SERIAL#USER_ID PROGRAMCPUWAITING IO---------- ------- ---------- ------------------------- ------- ---------- ---------- ---------- ------- ---------- ------------------------- ------- ---------- ---------- 24761970 11 sqlplus11698 000024761970sqlplus11698 2771100 oracle@labsfrh903 (LGWR)1421 00277 oracle@labsfrh903 (LGWR)1421 2761 0 oracle@labsfrh903 (CKPT) 1910 02761 0 oracle@labsfrh903 (CKPT) 1910 0 2781100 oracle@labsfrh903 (DBW0)29 0000278 oracle@labsfrh903 (DBW0)29 2801100 oracle@labsfrh903 (PMON)19 0000280 oracle@labsfrh903 (PMON)19 25422617 55 Executor.exe13 003325422617Executor.exe13 25512877 55 Executor.exe11 005525512877Executor.exe11 25733729 55 Executor.exe15 001125733729Executor.exe15 25513417 55 Executor.exe14 002225513417Executor.exe14 Copyright 2006 Kyle Hailey#.21 22. Top Session w/ Usernameselect select decode(nvl(to_char(s.sid),-1),-1,DISCONNECTED,CONNECTED) decode(nvl(to_char(s.sid),-1),-1,DISCONNECTED,CONNECTED)"STATUS","STATUS", topsession.session_id topsession.session_id "SESSION_ID", "SESSION_ID", u.name "NAME", u.name "NAME", topsession.program topsession.program "PROGRAM","PROGRAM", max(topsession.CPU) max(topsession.CPU)"CPU","CPU", max(topsession.WAITING) max(topsession.WAITING) "WAITING","WAITING", max(topsession.IO) max(topsession.IO)"IO","IO", max(topsession.TOTAL) max(topsession.TOTAL)"TOTAL""TOTAL" from ( ( {previous query} ) ) topsession, from {previous query} topsession,v$session s,v$session s,user$ uuuser$where where u.user# =topsession.user_id and u.user# =topsession.user_id and/*/* outerjoin to v$session because the session might be disconnected */*/ outer join to v$session because the session might be disconnectedtopsession.session_idtopsession.session_id== s.sid s.sid (+) and(+) andtopsession.session_serial# ==s.serial# (+)topsession.session_serial# s.serial# (+)group by topsession.session_id, topsession.session_serial#, topsession.user_id, group by topsession.session_id, topsession.session_serial#, topsession.user_id,topsession.program, s.username,s.sid,s.paddr,u.nametopsession.program, s.username,s.sid,s.paddr,u.nameorder by max(topsession.TOTAL) desc order by max(topsession.TOTAL) descCopyright 2006 Kyle Hailey #.22 23. Top SessionFinding a Rogue UserSTATUSSESSION_ID NAME PROGRAM CPUWAITING STATUSSESSION_ID NAME PROGRAM CPUWAITING IOIO--------------- ---------- ---------- ------------------------- ----- ---------- ---- --------------- ---------- ---------- ------------------------- ----- ---------- ----CONNECTED247 CPU_Monger ChMgr304.exe1170400 00 CONNECTED247 CPU_Monger ChMgr304.exe11704CONNECTED277 SYSoracle@labsfrh903 (LGWR) 14 1900 CONNECTED277 SYSoracle@labsfrh903 (LGWR) 14 19CONNECTED278 SYSoracle@labsfrh903 (DBW0) 2900 00 CONNECTED278 SYSoracle@labsfrh903 (DBW0) 29CONNECTED276 SYSoracle@labsfrh903 (CKPT) 1899 00 CONNECTED276 SYSoracle@labsfrh903 (CKPT) 18CONNECTED280 SYSoracle@labsfrh903 (PMON) 2000 00 CONNECTED280 SYSoracle@labsfrh903 (PMON) 20DISCONNECTED 255 SYSTEM Executor.exe 1144 55 DISCONNECTED 255 SYSTEM Executor.exe 11DISCONNECTED 257 SYSTEM Executor.exe 1300 33 DISCONNECTED 257 SYSTEM Executor.exe 13DISCONNECTED 255 SYSTEM Executor.exe 1400 22 DISCONNECTED 255 SYSTEM Executor.exe 14DISCONNECTED 257 SYSTEM Executor.exe 1300 33 DISCONNECTED 257 SYSTEM Executor.exe 13 Copyright 2006 Kyle Hailey#.23 24. Many Ways to Attack ProblemsConfusing How to Attack the problem? Top SQL? Top wait for that SQL? Top Waiting Session ? Top Waits for that Session Top Waits for Database? Top Session waiting for that wait Top SQL for that waitSolution - GraphicsCopyright 2006 Kyle Hailey #.24 25. Mining Data is Non Trivial Many Dimensions to consider Constantly Varying time frames Luckily 10g automates all of this functionality. collectsall of the data analyses all the wait events reports on bottlenecks supplies solutions Graphics add tremendous ease and power Copyright 2006 Kyle Hailey #.25 26. OEM Perf Page Copyright 2006 Kyle Hailey #.26 27. ASH Sampling Cheap Cost 1. With PL/SQL its less that 1 % CPU with 10 averageactive sessions 2. DMA - direct memory attach to SGA Using C code 200xCheaper 3. ASH in 10g should be even cheaper than DMA becauseits done in the kernel Size Circular Buffer - 1M to 128M (~2% of SGA) Avg row around 150bytes 3600 secs in an hour ~ Meg per Active Session per hourCopyright 2006 Kyle Hailey#.27 28. How ASH works V$session_wait + v$session + extras MMON/ MMNLv$active_session_history Only writes out 1 in 10 rows via direct path insertswrh$_active_session_history MMON - manageability monitor, AWR M000 MMON background slave, runs snapshots MMNL - MMON lite, flushes ASH to diskCopyright 2006 Kyle Hailey#.28 29. ASH bufferSelect readsbackwards Insert pointInsert one direction-No latchingTouch up wait times-Index on time-No read consistency Copyright 2006 Kyle Hailey#.29 30. Family of ASH Tables Real time v$session (before 10g v$session_wait)Last 10 waitsv$session_wait_history~2 hours v$active_session_history1 week DBA_HIST_ACTIVE_SESS_HISTORY wrh$active_session_history Copyright 2006 Kyle Hailey #.30 31. Oradebug Dump to trace fileSQL> oradebug dump ashdump 5SQL> oradebug dump ash 5SQL> Alter session set events immediateSQL> Alter session set events immediate trace name ashdump level 5; tracename ashdump level 5;level 5 = # of minutesloader file rdbms/demo/ashldr.ctlCopyright 2006 Kyle Hailey #.31 32. INIT.ORA statistics_level= Typical (default) PARAMETERSESSION_VALUE INSTANCE_VAL ----------------------- ---------- ------------ _ash_sampling_interval10001000milliseconds _ash_size 1048618 1048618ASH buffer size _ash_enableTRUE TRUETurn on/off ASH sampling, flushing and the V$ views on ASH _ash_disk_write_enable TRUE TRUEFlush to disk _ash_disk_filter_ratio 10 10write 1 in 10 points _ash_sample_allFALSEFALSESample including idle waits Copyright 2006 Kyle Hailey#.32 33. 10.2 ASH Extras Blocking Session ! BLOCKING_SESSION RAC event Fixup BLOCKING_SESSION_STATUSPlan Hash Fix up10gR1 - during parsing no plan, BLOCKING_SESSION_SERIAL#good way to find parsingParameter Namesproblems P1TEXT , P2TEXT, P3TEXT10gR2 get plan hash fixup good but lose some ability to findWait Groupingparsing problems WAIT_CLASS WAIT_CLASS_IDXIDFORCE_MATCHING_SIGNATURE Cursor sharing02/20/13 #.33 33 34. 10.2.0.3 ASH ExtrasPLSQL_ENTRY_OBJECT_IDPLSQL_ENTRY_SUBPROGRAM_IDPLSQL_OBJECT_IDPLSQL_SUBPROGRAM_ID ALL_PROCEDURES where object_id = plsql_object_id and subprogram_id = plsql_subprogram_id02/20/13 #.34 34 35. PLSQL TrackingPackage/Procedure/SQL tracking 10.2.0.3calling_codeSQL_IDCOUNT(*)----------------------------------------------- ----------ORDERENTRY.NEWORDER 0uuqgjq7k12nf258ORDERENTRY.NEWCUSTOMER0bzhqhhj9mpaa262ORDERENTRY.BROWSEANDUPDATEORDERS 41zu158rqf4kf 301ORDERENTRY.NEWORDER 0yas01u2p9ch4569PARSE_SAME3vjxpmhhzngu4874ORDERENTRY.BROWSEANDUPDATEORDERS 05s4vdwsf58021669 selectobject_name package||.|| procedure_name, sql_id, count(*) from v$active_session_history ash, all_procedures procswhereash.PLSQL_ENTRY_OBJECT_ID = procs.object_id and ash.PLSQL_ENTRY_SUBPROGRAM_ID = procs.SUBPROGRAM_ID02/20/13#.3535 36. 11.1g ASH extrasSQL Elapsed Operation bit vector non timed ops SQL_EXEC_IDIN_CONNECTION_MGMT SQL_EXEC_START IN_PARSEIN_HARD_PARSESQL Row SourceIN_SQL_EXECUTIONSQL_PLAN_LINE_IDIN_PLSQL_EXECUTIONSQL_PLAN_OPERATIONIN_PLSQL_RPCSQL_PLAN_OPTIONSIN_PLSQL_COMPILATIONIN_JAVA_EXECUTIONIN_BINDWhich instance requested block?IN_CURSOR_CLOSE REMOTE_INSTANCE#ETCRecursive SQL CURRENT_ROW#EVENT#TOP_LEVEL_SQL_IDQC_SESSION_SERIAL#TOP_LEVEL_SQL_OPCODECONSUMER_GROUP_IDFLAGSCopyright 2006 Kyle Hailey#.36 37. 11.2g ASH extrasStatisticsTM_DELTA_TIMETM_DELTA_CPU_TIMETM_DELTA_DB_TIMEDELTA_TIMEDELTA_READ_IO_REQUESTSDELTA_WRITE_IO_REQUESTSDELTA_READ_IO_BYTESDELTA_WRITE_IO_BYTESDELTA_INTERCONNECT_IO_BYTESPGA_ALLOCATEDTEMP_SPACE_ALLOCATECopyright 2006 Kyle Hailey #.37 38. ASH Across Versions When Who SQLASH instrumentation is exploding across the versions WaitsStatisticsBlocker10.1 10.2 10.2.0.3 11.1 11.2 Copyright 2006 Kyle Hailey #.38 39. How Many Active Sessions? How much data does ASH Collect ? 1 CPU means max 1 Avg Active Session unless there is a bottleneck Big site examples: Oracle 4 way RAC internal apps 10,000 connected, 200 active One Site 3000 connected, 30 Active Site 12,000 connected, 100 active Copyright 2006 Kyle Hailey #.39 40. ASH Summary Rich & Multidimensional Overview of system load Drilldowns into problems Sql Session Wait event Details information on problem resolution Sampling is the future Took clean vision to create - Bit of a leap of faith Less Accurate but more powerful than past Inaccuracies unimportant for the most part Feasible since version 7 You can implement it yourself OEM simplifies mining of ASH data Copyright 2006 Kyle Hailey #.40 41. Q1How can you immediately find the top IO consuming SQstatement in the last 60 secondsa. v$active_session_historyb. v$sqlstats answerc. v$sqla onlyd. v$sqlareaothersb,c,d - only have cumulative valuessince database startupCopyright 2006 Kyle Hailey #.41 42. Q2Which view can you query directly toget the specific waits that occurred30 minutes ago:a. v$active_session_historyb. v$waitclassmetricc. v$system_eventd. v$session_wait_historye. v$waitclassmetric_historya onlyb - last 60 seconds onlyc - cumulative info since db startupd - last 10 waits onlye - only wait groups, not wait events, but hasthe history for last hourCopyright 2006 Kyle Hailey #.42 43. Q3ASH (v$active_session_history) is a revolutionary datasource for monitoring and analyzing databaseperformance. The view v$active_session_history is newin 10g, but most of the data needed in order to simulatev$active_session_history by hand has been availablesince which version b - since version 7 when wait eventsa. 6 were introduced along with the viewb. 7 v$session_waitc. 8d. 9 which is the foundation for ASHe. 10Copyright 2006 Kyle Hailey#.43

Recommended

View more >