RDBMS Forensics: Troubleshooting using ASH

[Author Note: Blogger app for IOS doesn't have all the nifty formating features of the web. I'll try to clean up later.]

Tim Gorman, Evergreen Database Technologies (EvDBT.com)

Three Case studies
- WHen someone complains of a performance problem or an error (but not happening right now)

Active Session History is a sampling mechanism
MMNL process scraps data about active sessions from session state in SGA to the ASH buffers in SGA (MMON light process)

parameter _ASH_SAMPLING_INTERVAL defaults to 1000 (usecs)

At hourly intervals, or as ASH buffers get more than 66% full, one of the 10 samples of ASH are writtent to partitioned tables in AWR in SYSAUX.
- configure AWR retension to mirror business cycles (quarterly, monthly, etc)

ASH is not a trace or audit trail. But it's not perfect. Only queries that are run in more than 1 second are captured in buffer. 

DBA_HIST_ACTIVE_SESS_HISTORY is samples of ASH taken every 10 seconds. 

[Updated: 2:38pm PST]


V$EVENT_NAME to look at events 

Long Running SQL Statement 
- we know what execution plan looks like
- we need to know the actuals

We can get this with SQL PLan Monitor
- Elapsed time, CPU Time
- number of i/o volume of data read and written
- volumne of PGA memory used
- 

v$SQL_PLAN_MONITOR
V$SQL_MONITOR


effdt and effseq processing using 
row_number() over (partition by seqno order by effdt) -- maybe?

ASH_XPLAN (available from here: EvDBT.com/scripts) can be used to add timings to each step of an exection plan, using timings based on ASH. 



Comments

Post a Comment

Popular posts from this blog

Tariffs are bad for you.

Headlines from today state that the new Trump administration will put a 25% tariff on imports from Mexico, and Canada. Additionally, a 10% tariff on goods imported from China.  A tariff is a fee charged by the Federal government on goods coming into the country. That fee is paid by the importer, not the exporter.  For example, Target wants to sell PlayStation in their stores, so they purchase them from Sony, who builds these in China. Target receives the shipment of PlayStations, where they will be charged the tariff. Target pays the tariff to the Government to receive these goods.  Target then must decide how to recoup the additional fees. They can either choose to eat the cost and reduce their revenue, or they can increase the selling cost. The outcome is a combination of the two.  As a result, consumer prices, specifically for food and cars are going to go up. And businesses lose money by having to pay tariffs and selling few items.   For th...
Read more

AWS Re:Invent 2024

 I have the privilege of attending AWS Re:Invent conference from 02-Dec to 06-Dec. Here's my planned schedule for the week.  Sunday, 01-Dec Depart MSP @ 6:45pm CST Arrive LAS @ 8:12pm PST Monday, 02-Dec Design Secure and resilient relational database architectures on AWS Observability-driven development AWS Fundamentals for the accidental SQL Server database administrator Unleashing AWS Agility with Terraform How to navigate your SQL Server Migration journey with AWS Deep Dive into Amazon DynamoDB using Design Puzzles Visionaries in the Stadium w/ Angela Duckworth and some AI folks Tuesday, 03-Dec Identify and manage Amazon Aurora & RDS Snapshots Scalable database solutions with Aurora PostgreSQL Limitless Database Data models for Amazon Neptune using GenAI and diagram-as-code tools Deep Dive into Amazon Aurora query plan management Bingo Night Hashicorp Customer Reception @ Chica Wednesday, 04-Dec AWS Re:Invent 5k Race Essentials for migrating from Oracle to Amazon Aurora...
Read more