Post

I wrote an Oracle "ash_index_helper.sql" script 5 yrs ago, trying to pull all relevant info of top time-consuming *access paths* into one place, as a single SQL script. Summarize access paths to a schema or just one table/SQL. Too complex for a single script, time for AI? 😎

The idea was to start with only the access paths that consume the most response time or resources and then see how frequently the queries are actually executed *and* show cardinality estimates/filter factors ("needle in the haystack?") and propose *better* indexes (not more).

But it became clear that more than just a single SQL query is needed, for example you wouldn't see a filter predicate on a table if CBO ended up doing a full scan and just throwing rows away much higher up in some hash join. I was going to write a Python tool or something...

... but now it looks like I'll just feed the relevant info into a language model instead. Let's see how it goes! Nevertheless, people have found this tool already useful in the past! backendtales.blogspot.com/2023/02/sant...

I first used "ash_index_helper.sql" that queried v$ash and v$sql_plan, etc, but since modern 19.x something databases store access_predicates/filter_predicates in AWR too, I cloned it to "dash_index_helper.sql" so you can go back in AWR: github.com/tanelpoder/t...