hi welcome everybody to this presentation on Oracle Cloud Infrastructure Operations Insights,
an OCI native service. I am ashi sagarwal one of the product managers
in oracle enterprise and cloud manageability group.
Operations insights service enables business executives database and it administrator to make
informed data-driven database resource and performance management decisions it has two
applications capacity planning and oracle sql warehouse capacity planning helps with optimizing
operational costs and oracle sql warehouse is a long term store for SQL performance data
which can help you to maximize performance capacity planning application does trending
and forecasting of resource demand using 25 months historical data for oracle autonomous databases
it has linear and machine learning forecasting models inbuilt into the service
it provides automated daily forecast which predicts near-term capacity utilization issues
this this helps the the personas to be proactive about capacity
it helps to quickly isolate largest most utilized and fastest growing databases it answers questions
about underutilized servers so that someone can repurpose and reduce the operational cost.
Oracle SQL warehouse provides insights into sql performance using 25 months worth
of historical data in in the following dimensions one can proactively identify
sequels that are degrading in performance SQL that are unpredictable in performance
one can identify application sql performance issues and and improve performance
even more if they fix this issues it helps to identify sequels which are changing
execution plans and provides insights into top sequels across fleet of oracle
autonomous databases by cpu and I/O you can find common patterns of sql performance issues
across fleet of oracle autonomous databases as well as compare sql performance across prod
dev and test. Let's look at the actual demo of the service so after logging in into the oracle cloud
Operations Insights service is part of monitoring and diagnostics so you can see Operations Insights
under monitoring and diagnostics so let us look at the Oracle SQL Warehouse now as you can see i
am analyzing Oracle e-sales compartment in last for last seven days i have 48 databases in my
inventory and i am analyzing over 4k SQL. The top panel gives me the insights by Top SQL by CPU, Top
SQL by I/O and these are the automated findings in the four use cases that I have mentioned.
Degradation variability inefficiency and execution plan changes and also the common findings
across the entire fleet of Oracle autonomous databases and the below chart one can do a
custom analysis of the sql response time
now let's drill down into one of the use cases of a sql degradation so what it says is
there are 20 SQLs that have degraded in in last seven days these are the trends out of the 4k SQLs
that's being analyzed so let's drill down into that it gives me a load map nicely for the 20 SQL.
Based on each of the databases the SQL may be running on and the size represents
the average response time currently and and the color represents the percentage change so
sequel that begins with this zero two is degraded the most it has the most average response time
and it has degraded by more than 100 percent the legend tells me the color uh of how much
percentage it has degraded and so this is a master detail chart the selection below gives me
you know how the time series for that particular SQL is in the last seven days as I can see that
the SQL is systematically degrading in the last seven days also if the SQL is common across
multiple databases you can see this black line and is highlighted across different databases if
the same SQL is running across various databases and as well as in in the detailed chart we can see
that the same SQL is running and getting compared against this particular databases i and vcp
and a customer or dba or a developer or the devops team is welcome to do the analysis
in... in a different way i can now i'm not comparing the, i'm now doing the analysis based on
executions per hour and i can see that the execution per hours is getting reduced over
a period of time from 25th of September to 2nd October so let's drill down so we were
at the fleet level from feet level we were able to identify the 20 degrading SQL and out of the
20 degrading SQLs we have been able to identify one single SQL that have that can have the most
impact across the fleet of databases. So let's drill down into that particular
SQL and get the SQL details so I can see if i click on the show button i get the sql text
of that particular sql i get the database name the database type and the OCID also I get the oc
of that particular database the database version is 12 102 currently the performance statistics.
I see the average response time of this uh sequel has increased by over thousand percent
the executions per hour uh the variability in efficiency and and what's the executed
range for the sequel most importantly I get the execution plan insights i know that there are two
plan counts i get the analysis of the the plans itself also as to which is the best performing
plan and the worst performing plan i can see the performance analysis the performance trend based
on average response time i can go and find out based on how the executions per hour are occurring
i get the breakdown of cpu io and inefficient weight events i get the histogram for the response
time distribution currently there is only one bar because all the executions are happening over five
minutes i also get the response time breakdown by by the plan and i can do it by database also
Now let's do the comparison use case. If i drill down more I am able to compare
the sequels execution plan so I can see this particular plan
which was uh effective till September 27th is better and now i have a bad plan
similarly you can compare based on other metrics also so that's the comparison by plan if I want to
compare it between my different databases like dev test or prod I can compare it by changing the the
compare option to database and i can see based on average response time now i'm comparing the SQL.
Moving on further if I want to see what's my
best plan and worst plan so I can get that details by clicking the executions plans tab
and I can see my plan and here i see that this is the worst performing plan it has an
operations cost of 329 million and it's doing a full table scan i can see that now quickly i
can turn to the best performing plan by changing the tab I can see now that the the best plan the
operations cost is around 22.9 million and now it's doing the table access by row ID.