In this section we will show you how to work with the main AWR Analyzer screen.
In the menu we will select the first item.
Here is the database connection, the selection of the database.
If you have multiple Databases imported,
program allows to work with multiple databases on the same machine.
There's also a choice of instances, so we can choose a RAC.
We can work with multiple nodes at once. It's up to you.
We see in the list the snapshots description.
Choosing a snapshots is easy.
Click with the left mouse button.
If we want to select more than one, just hold down the left shift button and click again.
Now we have selected snapshots from interval 2 to 5.
And we see also the times from-to for which we want to generate the reports.
We can display the sub-menu using right-mouse button,
and we can choose from TOP queries, TOP segments and TOP events.
Let's press the TOP query.
And here we have the screens.
We have here the top query
by elapsed time, per used cpu,
processor time, according to the input and output operation,
query by gets, by reads,
according to the number of launches, parse calls and sharable manuals.
As well as Count version.
If we do with RAC environment,
the top query is also added for the cluster.
Well, let's take a look at the advantages over the standard AWR report.
The standard AWR Report is generated in HTML format.
and does not allow you to view the execution plan directly.
You must run another sql report to generate schedules for each query.
So let's look at these two columns. Here we see how many execution plans that query had.
And the last column shows us whether the variables have been captured.
So, just for curiosity, let's look at this query.
We have two options to choose from: a simple click
and on the right side are the details. Or double click.
With a double click, a separate window is opened that we can manipulate,
to put aside for later and so on.
There are several possibilities.
We can export all the tables we see here.
With a simple click, export, it will be an Excel file.
Of course supported are also open sources, like open office and so on.
Individual tables are generated in bookmarks.
The program also allows filtering according to sql module.
This is useful if, for example in Java, we set the name of a batch process,
but it doesn't have to be just in Java.
And then we are interested only in these queries that were run in that specific report,
so we can select "copy cell" and hit Enter.
Searching is not case sensitive.
So this is a searching for whole string.
as if you had an asterisk at the front and back.
Wait a while for processing.
And as we can see, we have displayed the query directly for sql developer.
Another filter allows us to change the number of queries to browse through.
So the top 10 will be displayed.
It is also possible to customize colors and default rows in the table.
This tool is added.
We have to wait until it will be finished.
Tools, preference, here is AWR Analyzer and here we have default.
So we have the colors for the even and odd lines.
And colors to highlight percentages.
It makes the searching easier and faster.
We can choose for example this color. And we'll see the change.
Let's go back.
The program does not use the stored Oracle percentage.
It is a pure written query without any inserts and therefore permissions are only for reads.
We can look at other benefits. I will choose here to find all.
Look at the right side.
At the top, we will see sql ID.
Snapshot numbers from and to are listed inside the brackets.
First we have the query that we have chosen in the left side of the panel.
And it is displayed either directly as it is stored in the database,
or when you click the format query, it will appear in that format.
Under the query we will see the execution plans and numbers.
This number is the plan hash value.
So if we have 4 different execution plans here, here's the fourth,
and there are selected the binding variables.
we can look at the individual plans,
and how much were needed the system's resources.
This plan had three tenths of a second to start. We can look.
Sample variables are displayed for the given plan.
Here is another plan. This one was far faster as you can notice.
It was running for 7 thousandths of a seconds only.
Also the related variables. Plan Number 3, details. Plan number 4.
And below the last one we have a summary for the whole query.
As you have noticed, we have added here SPM buttons.
It's the oracle sql plan management.
This functionality is in oracle since version 11.
And this functionality has been added directly to our program.
which allows you to easily disable, enable or upload a plan,
for example, we select this plan and we can see that it was set as fixed.
Immediately we did a reload of query.
and this plan is momentarily turned on and set as fixed.
This means that oracle will only use this one plan at the moment,
and not the others below.
If there are already some plans, the program loads them automatically.
Well, we can cancel this plan.
This allows very fast work.
It happens,that instead of the scan index, Oracle uses full scan.
and with each of these steps we can prevent him from doing it.
We can look further
we'll show it here
plans
There are added complete statistics for each step related to the plan.
And here it is highlighted which columns in the indexes were used.
So this RANGE SCAN here is the name of the index,
the index has three columns in itself, but in the scan was used only one.
Here are highlighted all of them, for the index fast full scan it's quite typical.
It allows us to estimate the use of the columns in the index.
Here we see one,
here are used all four.
If we have an index skip scan.
This skip scan is a feature that allows you to use columns,
that are not even the first.
In that case you will have a written skip scan and highlighted which column was used.
I don't know if we can find anything here either.
Probably in these basic queries we have here,
I don't know if we can find it.
But that's not so important.
We can also directly copy the query.
Here we have, for example, query
Simple click.
Let's open the notepad and see.
And we see, we have the query copied here, ready to use.
We can continue to work with it.
Turn on sql, so the syntax will be highlighted.
We have here another export button.
This export does not go into excel file but generates us a pdf file.
Let's take a query, like this, which has four plans so we have more information
and click here.
You can also notice how I scroll the mouse over the query,
this shows the same query in different tables.
This makes it easier to find the same query in the other tables.
So let's save. The prefix is sql details, sql id,
and these numbers shows generated snapshots for this query.
We are going to save it.
After saving, we can view the query.
At the top we can see when this report was generated.
We also have relevant sql IDs
connection to the database,
and snapshot numbers.
Next, will be shown sql text with the given query.
So, of course this output can be formatted or unformatted,
it depends on your export settings.
There are all plan hash values listed here,
Statistics for the given variable
another plan hash value,
and it's statistics.
So we can go down to see summarized statistics,
average for the given query and for all plan hash values.
As next we can show another item, the top segments.
The same rules apply for filtering according to module
and the number of rows per table.
If we click on the given object,
the ddl of the object is displayed on the right.
Whether it's an index, here we see the command how it was created.
We can also see the table,
and whether you want to have it formatted or not.
So now we can come back here.
It's already formatted.
All tables are generated as well as the solutions in AWR report,
with the fact that we will see the statistic about how many segments were captured,
how much was the logical read,
and based on those figures, the percentages are calculated.
The last item we had in the sub-menu is top events.
Here we can look at DB cpu
Also, here applies how many events you want to have displayed.
The module does not work here, so therefore nothing can be written here as well.