Building an sql execution plan history

1.5k Views Asked by At

Using Oracle 11.2.

Is there a standard approach to build an execution plan history like how an execution plan may change ofer time? I thought about tracking changes in dba_hist_sql_plan (plans are kept there for 10 days in our env.). But are all plans stored in dba_hist_sql_plan or does that depent on the awr config? Or is there a standard approach for doing that (may without dba_hist_sql_plan). I'm rather new to Oracle so every idea is welcomed.

1

There are 1 best solutions below

0
On

Not all plans are stored in the AWR. Generally, though, all the plans that you're interested in are. Few people are interested in tracking changes to query plans for queries that don't consume a lot of resources in a particular snapshot window. They're generally only interested in the behavior of the top resource consumers. You can adjust the N in the top N queries that AWR captures with each snapshot.

If you're looking at a single database, the simplest option is to keep data in the AWR as long as you're likely to need it. If you want data back beyond 10 days, the simplest option is to increase your AWR retention. AWR data doesn't consume that much disk space so it's generally easier to let Oracle retain more data than to write anything of your own.

Sometimes, people configure replication processes that take AWR data from many individual databases and write them to a single central database. That requires a decent amount of setup but it makes it much easier to compare information across databases (either to compare data among dev/ test/ staging/ production environments or to compare across multiple client-specific instances).