Transcript document
It’s time to do ASH! Tuesday, Februrary 8th 2005 Gaja Krishna Vaidyanatha Principal, DBPerfMan LLC [email protected] http://www.dbperfman.com Confession#1 I am not an expert…not by any stretch of the imagination. Confession#2 I am an engineer not a scientist. Confession#3 Advanced Tuning, Turbo-charged Tuning, Push-Me-For-More-Power Tuning…Lies…Just Plain Lies… Or is it called Marketing these days!!! Confession#4 There is only one way to optimize Oracle performance – The Right Way...Using the Wait Interface Plan of Action What is ASH? Oracle 10g ASH! Why should you use ASH? Components of ASH ASH Architecture ASH Details Using ASH - Some Initial Findings Future Ideas for ASH Conclusion What is ASH? Non-Oracle ASH Ashland Inc. (NYSE – ASH) Action on Smoking and Health American Society of Hypertension ASH Karo!!! Means “Have FUN in Hindi” ASH in Oracle 10g Active Session History “ASH Karo Said in another way “Use Oracle 10g ASH” Oracle 10g ASH! New source of Oracle database performance data in 10g An active session is one which is in a user call Parse Execute Fetch On the CPU Provides historical information about recently sampled “active” sessions Oracle 10g ASH! ASH = V$SESSION_WAIT++ with History Note: In 10g V$SESSION_WAIT is integrated with V$SESSION It facilitates spot analysis of both foreground and background sessions Why should you use ASH? Great for performance diagnostics Logs wait events along with SQL details and session-specific context in a circular buffer in memory Fixed session sampling algorithm uses < 0.1% of 1 CPU Can be modified by the use of an _ parameter Primary data provider for the Automatic Database Diagnostic Monitor (ADDM) ADDM supports proactive performance diagnostics within the Oracle Kernel Components of ASH Memory buffers in the fixed areas New Oracle Background Process MMNL – MMON Lite V$ACTIVE_SESSION_HISTORY X$ASH DBA_HIST_ACTIVE_SESS_HISTORY Based on WRH$_ACTIVE_SESSION_HISTORY ASH Architecture Indexed on time Indexed on time V$ACTIVE_SESSION_HISTORY X$ASH WRH$_ACTIVE_SESSION_HISTORY Circular buffer MMON Lite (MMNL) in SGA Every 30 mins or when buffer is full AWR Direct-path inserts Samples with variable size rows ASH Details - General No installation or setup required Intended 30-min circular buffer in the SGA In memory ASH contains as much history as it can store. Circular buffer not cleared when written to disk ASH on Disk (1 of 10 in memory samples) Init.ora STATISTICS_LEVEL = TYPICAL (Default) Master Switch _ACTIVE_SESSION_HISTORY = TRUE (Default) ASH Details - General 30-minute circular buffer in the SGA - GOAL May scale down to smaller duration on large systems Circular Buffer Sizing Formula: Max( Min (# of CPUs * 2MB, 5% of SHARED_POOL_SIZE, 30MB), 1MB) If SHARED_POOL_SIZE is not explicitly set Formula changes to 2% of SGA target ASH Details - General Assumptions for MAX Size - 30MB 100 active sessions Sampled at once per second (60 samples in 1 minute) Assume 17 minutes of non-stop collection Assume 300 bytes per sample Size = 100*60*17*300 bytes ~ 29.18MB Fudge Factor of 0.82 MB ASH Details - General History flushed to Automatic Workload Repository (AWR) every 30 minutes Part of the AWR snapshot Database metrics Session Wait Information Hot files and segments ASH Details - General Sampling done every second Can support sub-second sampling _ash_sampling_interval = 1000 (milliseconds by default) Can dump to process trace (if required) Estimated 2500 CPU Instructions per active session per sample 400 active sessions on a 1 Ghz processor consumes < 1 millisecond The sampler (MMNL) does not take any latches It supports dirty reads Can write to the in-memory buffer without any issues ASH Details – View Describe SQL> desc v$active_session_history Name --------------------------------------SAMPLE_ID SAMPLE_TIME SESSION_ID SESSION_SERIAL# USER_ID SQL_ID SQL_CHILD_NUMBER SQL_PLAN_HASH_VALUE SQL_OPCODE SERVICE_HASH SESSION_TYPE SESSION_STATE QC_SESSION_ID QC_INSTANCE_ID EVENT EVENT_ID EVENT_# SEQ# P1 P2 P3 WAIT_TIME TIME_WAITED CURRENT_OBJ# CURRENT_FILE# CURRENT_BLOCK# PROGRAM MODULE ACTION CLIENT_ID Null? ------- Type ---------------------------NUMBER TIMESTAMP(3) NUMBER NUMBER NUMBER VARCHAR2(13) NUMBER NUMBER NUMBER NUMBER VARCHAR2(10) VARCHAR2(7) NUMBER NUMBER VARCHAR2(64) NUMBER NUMBER NUMBER NUMBER NUMBER NUMBER NUMBER NUMBER NUMBER NUMBER NUMBER VARCHAR2(64) VARCHAR2(48) VARCHAR2(32) VARCHAR2(64) ASH Details – View Definition SQL> select view_definition from v$fixed_view_definition 2* where view_name = 'GV$ACTIVE_SESSION_HISTORY'; VIEW_DEFINITION -------------------------------------------------------------------------------SELECT /*+ no_merge ordered use_nl(s,a) */ a.inst_id, s.sample_id, s.sample_time, a.session_id, a.session_serial#, a.user_id, a.sql_id, a.sql_child_number, a.sql_plan_hash_value, a.sql_opcode, a.service_hash, decode(a.session_type, 1,'FOREGROUND', 2,'BACKGROUND', 'UNKNOWN'), decode(a.wait_time, 0, 'WAITING', 'ON CPU'), a.qc_session_id, a.qc_instance_id, a.event, a.event_id, a.event#, a.seq#, a.p1, a.p2, a.p3, a.wait_time, a.time_waited, a.current_obj#, a.current_file#, a.current_block#, a.program, a.module, a.action, a.client_id FROM x$kewash s, x$ash a WHERE s.sample_addr = a.sample_addr and s.sample_id = a.sample_id and s.sample_time = a.sample_time The New Oracle 10g Car Wash ASH Details – WRH$_ASH View Describe SQL> desc wrh$_active_session_history Name -----------------------------------SNAP_ID DBID INSTANCE_NUMBER SAMPLE_ID SAMPLE_TIME SESSION_ID SESSION_SERIAL# USER_ID SQL_ID SQL_CHILD_NUMBER SQL_PLAN_HASH_VALUE SERVICE_HASH SESSION_TYPE SQL_OPCODE QC_SESSION_ID QC_INSTANCE_ID CURRENT_OBJ# CURRENT_FILE# CURRENT_BLOCK# SEQ# EVENT_ID P1 P2 P3 WAIT_TIME TIME_WAITED PROGRAM MODULE ACTION CLIENT_ID Null? -------NOT NULL NOT NULL NOT NULL NOT NULL NOT NULL NOT NULL Type --------------NUMBER NUMBER NUMBER NUMBER TIMESTAMP(3) NUMBER NUMBER NUMBER VARCHAR2(13) NUMBER NUMBER NUMBER NUMBER NUMBER NUMBER NUMBER NUMBER NUMBER NUMBER NUMBER NUMBER NUMBER NUMBER NUMBER NUMBER NUMBER VARCHAR2(48) VARCHAR2(48) VARCHAR2(32) VARCHAR2(64) Using ASH – Some Initial Findings Querying V$ACTIVE_SESSION_HISTORY needs a session New logins may be impossible on badly crippled systems Query V$ACTIVE_SESSION_HISTORY requires all relevant latches in the SQL layer On systems crippled on shared pool and library cache latches, queries to ASH will impose even more overhead on these latches Proposed Workaround for Limitations 1. Data in buffer is first dumped to a process trace file – Host 1 2. Transport the file (ftp) to another box – Host 2 3. Sanitize the file of its headers and other information on Host 2 4. Build a SQL*Loader Control File for the load on Host 2 5. Create a user-defined ASH table (with the same structure) in a database in Host2 6. Load the data from #3 using #4 into #5 Optional Demo!!! Future Ideas for ASH Keep a persistent and “reserved” session Eliminates the need to “logon” Pre-compile some standard cursors on ASH, Eliminates the need to soft parse Open a non-PL/SQL API so that data from the collector can be directly read Eliminates the need to SQL*Load trace data into another database Conclusion Great performance diagnostic data source But it is only for 10g Granular enough data for most problems Makes for a good performance management suite when combined with ADDM and AWR Brand new code – Maturity will come with time Yet to replace 3rd-party direct-SGA-attached collectors But it may be cheaper…;-) Thank You! Visit us at http://www.dbperfman.com Gaja Krishna Vaidyanatha, Principal, DBPerfMan LLC, [email protected]