ASH Family of Tables
ASH family of tables
Circular Buffer - 1M to 128M (~2% of SGA) Flushed every hour to disk or when buffer 2/3 full (it protects itself so you can relax) Avg row around 150bytes 3600 secs in an hour ~ ½ Meg per Active Session per hour That’s generally over an hour of ASH
Dumping ASH to flat file
- oradebug dump ashdump 5
- Alter session set events ‘immediate trace name ashdump level 5’;
level 5 = # of minutes loader file rdbms/demo/ashldr.ctl
statistics_level = Typical (default)
| PARAMETER | SESSION_VALUE | INSTANCE_VAL |
| _ash_sampling_interval | 1000 | 1000 |
| milliseconds | ||
| _ash_size | 1048618 | 1048618 |
| ASH buffer size | ||
| _ash_enable | TRUE | TRUE |
| Turn on/off ASH sampling, flushing and the V$ views on ASH | ||
| _ash_disk_write_enable | TRUE | TRUE |
| Flush to disk | ||
| _ash_disk_filter_ratio | 10 | 10 |
| write 1 in 10 points | ||
| _ash_sample_all | FALSE | FALSE |
| Sample including idle waits | ||
10.2 added fields to ASH
- Blocking Session !
- BLOCKING_SESSION
- BLOCKING_SESSION_STATUS
- BLOCKING_SESSION_SERIAL#
- Parameter Names
- P1TEXT , P2TEXT, P3TEXT
- Wait Grouping
- WAIT_CLASS
- WAIT_CLASS_ID
- Transactions
- XID
- Cursor sharing
- FORCE_MATCHING_SIGNATURE
10.2.0.2 added
- PLSQL_ENTRY_OBJECT_ID
- PLSQL_ENTRY_SUBPROGRAM_ID
- PLSQL_OBJECT_ID
- PLSQL_SUBPROGRAM_ID
connect to ALL_PROCEDURES with
- where object_id = plsql_object_id
- and subprogram_id = plsql_subprogram_id
11.1.0 added
- Identifying SQL Execution - Is this same execution as last sample?
- SQL_EXEC_ID
- SQL_EXEC_START
- SQL Execution Row Source - Identifies current row source within plan
- SQL_PLAN_LINE_ID
- SQL_PLAN_OPERATION
- SQL_PLAN_OPTIONS
- RAC block transfers - Remote instance id for Cache transfers, Which instance sourced requested block?
- REMOTE_INSTANCE#
- Recursive SQL
- TOP_LEVEL_SQL_ID
- TOP_LEVEL_SQL_OPCODE
- CPU breakdown - Operation bit vector , Capture non-timed operations
- IN_CONNECTION_MGMT
- IN_PARSE
- IN_HARD_PARSE
- IN_SQL_EXECUTION
- IN_PLSQL_EXECUTION
- IN_PLSQL_RPC
- IN_PLSQL_COMPILATION
- IN_JAVA_EXECUTION
- IN_BIND
- IN_CURSOR_CLOSE
- ETC
- CURRENT_ROW#
- EVENT#
- QC_SESSION_SERIAL#
- CONSUMER_GROUP_ID
- FLAGS
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
[File:ash_circular_buffer2.PNG]]