Mauro Pagano's Blog

Leave a comment

Little big change in SQLd360

Just a super-quick note on SQLd360, starting with version v1522 (released a couple days ago), CSV and TEXT formats are disabled by default so a report that looked like this before
Screen Shot 2015-08-20 at 8.36.47 AM
will now look like this
Screen Shot 2015-08-20 at 8.37.22 AM
Nothing to be alarmed of, it’s expected!
In case you want the two formats back you can just edit file sql/sqld360_00_config.sql.

The main advantage of this change should be a significant reduction in the time it takes to collect the report (feedbacks welcome as usual) 😀
Also the number of rows per report is computed differently, which should save some extra time!

PS: the same change is in eDB360 v1526

Leave a comment

How to quickly identify if a SQL running in parallel has been downgraded using SQLd360

This is just a quick note on how to leverage a couple reports in order to identify if a PX has been downgraded. Those reports have been around for quite some time so this isn’t about any new feature but rather how to use what’s already there 😉

An execution is downgraded when the number of PX slaves used by the SQL at runtime is lower than the requested number, there are few reasons why it can happen with the most common one being the lack of available PX slaves at the time the SQL starts. Such a downgrade can cause from small to catastrophic performance degradation depending on the severity of the downgrades (measured in %) since the CBO generates an execution plan expecting to have some specific horsepower (aka number of PX slaves) at disposal but then such horsepower is reduced. It’s like planning how long it will take to drive from A to B with a Ferrari and then go to the garage and find out you only have a Jetta 😀

SQLd360 aims at making it easier (and quicker) to identify if an execution was downgraded, let’s see how.

The requested DoP for a SQL is stored in the OTHER_XML column, for each PHV/source SQLd360 provides in the “SQL Performance Summary” the MIN and MAX DoP requested, which is likely to stay stable overtime (exception made for Adaptive Degree Policy or Adaptive Multi User, which aren’t that common anyway).
Here is an example of a SQL with a requested DoP of 4:

Screen Shot 2015-08-06 at 8.58.09 AM

Starting from 11gR2 the DoP the SQL executed with can be extracted from column PX_FLAGS in ASH (TRUNC(px_flags / 2097152) as already reported by Randolf here) so for each execution that made it into ASH SQLd360 reports the “execution DoP”.
Here is the output from report “Elapsed Time for Recent Execs”:

Screen Shot 2015-08-06 at 9.18.43 AM

Column MAX_PX_DEGREE is the MAX(DoP) this specific execution used (see note below about why MAX).
Column NUM_PROCESSES is the distinct number of ASH sampled sessions that collaborated to this execution (up to 2*DoP).

From the report above it’s easy to spot how the execution from July 30 was indeed executed at the requested DoP (4) while the one from August 6 was downgraded (requested DoP 4, execution DoP 2).

Note:  ASH reports the DoP by DFO tree so if the plan had multiple DFO trees with different DoP (uncommon but possible) just keep in mind the number you see in the report is the MAX.


Historical SQL Monitor reports in 12c!

I think SQL Monitoring is one of the greatest addition to the Oracle performance diagnostics world since a long time ago (10046-time maybe?) because it allows to collect in a single shot a long list of crucial information that are otherwise painful to extract and put together. The report provides a complete picture of the execution across processes (PX), nodes (RAC), etc etc.
On the other hand, one of the major limitations (the biggest one, imho) is SQL Monitor info are quickly aged out of memory and not stored in AWR (there is no DBA_HIST_SQL_MONITOR or similar) so they are are unlikely to be around for a post-mortem investigation.

Good news is in 12c we can pull a report for a historical execution, granted the execution was expensive enough to candidate for collection. I didn’t investigate the details of the automatic collection yet but there are two ways, details from DBA_HIST_REPORTS_CONTROL:

  • REGULAR – per-minute report capture subject to DBTIME budget
  • FULL_CAPTURE – capture will be run per minute without the DBTIME budget constraints


Info about each automatically collected report are stored in DBA_HIST_REPORTS and the report itself (in XML format) is stored in DBA_HIST_REPORTS_DETAILS.

The whole framework is also used for Real-Time ADDM so the DBA_HIST_REPORTS* views are not organized in a SQL Monitor-friendly way (ie. SQL ID/SQL Exec ID/SQL Exec Start) but rather in a report-oriented way, the key is REPORT_ID.
Column COMPONENT_NAME helps track down the source of the report, “sqlmonitor” in this case.
A summary of the report is stored in REPORT_SUMMARY in XML format, so a simple SQL like the following pulls the list of REPORT_ID/SQL_ID (plus anything else you may want to extract from the summary, ie. SQL Exec ID and SQL Exec Start):

SELECT report_id,
       EXTRACTVALUE(XMLType(report_summary),'/report_repository_summary/sql/@sql_id') sql_id,
       EXTRACTVALUE(XMLType(report_summary),'/report_repository_summary/sql/@sql_exec_id') sql_exec_id,
       EXTRACTVALUE(XMLType(report_summary),'/report_repository_summary/sql/@sql_exec_start') sql_exec_start
  FROM dba_hist_reports
 WHERE component_name = 'sqlmonitor'

From my system I have

---------- --------------- ---------- ------------------------------
      1022 fx439nus0rtcz     16777216 04/29/2015 13:34:15
      1024 fjvsmy2yujbqd     16777216 04/29/2015 13:40:00
      1025 9qn59dh1w8352     16777216 04/29/2015 13:41:12
      1026 1uqrk6t8gfny8     16777216 04/29/2015 13:41:14

Using the REPORT_ID we can now extract the report in different format (HTML, TEXT, ACTIVE, XML) using DBMS_AUTO_REPORT.REPORT_REPOSITORY_DETAIL

SQL> set long 10000000 longchunksize 10000000 pages 0
       FROM dual;
SQL Monitoring Report

SQL Text
select count(*) from test_inmemory

Global Information
 Status             : DONE (ALL ROWS)
 Instance ID        : 1
 Session            : MPAGANO (12:35570)
 SQL ID             : fx439nus0rtcz
 SQL Execution ID   : 16777216
 Execution Started  : 04/29/2015 13:34:15
 First Refresh Time : 04/29/2015 13:34:15
 Last Refresh Time  : 04/29/2015 13:34:15
 Duration           : .064582s
 Module/Action      : SQL*Plus/-
 Service            : orcl
 Program            : sqlplus@Mauros-iMac.local (TNS V1-V3)
 Fetch Calls        : 1

or if we want it in flashy ACTIVE format

SQL> set trimspool on
SQL> set trim on
SQL> set pages 0
SQL> set linesize 1000
SQL> set long 1000000
SQL> set longchunksize 1000000
SQL> spool historical_sqlmon.sql
       FROM dual;

Screen Shot 2015-05-04 at 3.32.23 PM

Several details are missing from this post (and from my knowledge of how the whole framework works) but I’ll make sure to pass them along as soon as I find out more.

Hopefully this blog post will be useful next time you are trying to figure out why a SQL ran so slow last weekend 🙂

UPDATE: DBA_HIST_REPORTS also includes SQL_ID, SQL_EXEC_ID, SQL_EXEC_START and several other information (concatenated together into a single column and separated by ‘3#’ sign) as basic columns KEY1, KEY2, KEY3 and KEY4. The following SQL provides the same information as the original SQL posted in this blog post

SELECT report_id, key1 sql_id, key2 sql_exec_id, key3 sql_exec_start
  FROM dba_hist_reports
 WHERE component_name = 'sqlmonitor'


Poor man SQL Monitor with SQLd360…kind of!

One of the most interesting section of a SQL Monitor report is IMHO the Metrics page where we can see on a timeline the impact / requirement on an execution in terms of CPU, IOPS, MBPS, PGA and TEMP.
Starting 11gR2, we can get something similar (not exactly the same) from ASH, this is from DBA_HIST_ACTIVE_SESS_HISTORY

Screen Shot 2015-04-27 at 9.37.15 AM

For each TM_DELTA_TIME we know how much CPU and DB Time has been spent, as well as how many RW IOPS and RW and Interconnect (M)BPS per DELTA_TIME. The metrics are accumulated and reported at the time of the ASH sample “over the Delta Time” that roughly matches with the ASH sampling one, so ie. from V$ACTIVE_SESSION_HISTORY we are able to see how many RW IOPS per second (since the DELTA_TIME is roughly going to be the second) are accounted by the session we focus on. Also each time the ASH sample is taken the PGA and TEMP consumed by the session at that time is tracked.

Starting SQLd360 v1511 those columns are now collected and aggregated/charted over time so we can look at the single consumption via SQL Monitor (or from raw ASH data) and at the same time evaluate the historical trend from SQLd360. The PlansAnalysis page in SQLd360 now looks something like this

Screen Shot 2015-04-27 at 10.54.29 AM

Other new additions since the last post (v1507) are:

  • SQLd360 now uses a configuration file to determine how many days to collect info for and which “major” external APIs to collect / ignore
  • Several reports have been rewritten to better represent info from Parallel Executions
  • New sections provide info on
    • Fix Controls (default and non-default) settings
    • NLS settings
    • Adaptive Cursor Sharing info
    • SQL Monitoring info
    • Partition Statistics history info
    • SQLs with the same Force Matching Signature

plus as usual bug fixes here and there.

Thanks to everybody who reported issues and provided feedback for new features! 🙂

Leave a comment

SQLd360 and ORA-7445 [evaopn3()]

A few kind people reported to me that SQLd360 disconnected in the middle of the execution. Looking into the details the cause was an ORA-7445 evaopn3() but such errors are a little tricky to track down because they only show up with a combination of faulty plan (that’s the bug) with specific data, that’s why the error never reproduced in my labs as well as at several other folks’ systems.

Galo Balda was kind and patience enough to report the error and test my suspects (Thanks Galo!!!) so we were able to identify a way to avoid the error.

The ORA-7445 was caused by bug 12672969 (I suggest you check the details in MOS, the bug is pretty “common”) and starting today SQLd360 works it around so that your execution shouldn’t disconnect anymore!

If you tried SQLd360 in the past but ignored it because of the unexpected disconnect then I suggest you give it one more shot now that the error should be taken care!

Leave a comment

Quick update on SQLd360 recent changes

The last blog post was about SQLd360 v1504 and last night I released v1507 so a couple released (v1505, v1506) were silent. This post is just a quick update of what’s changed in the last weeks.

  • ASH SQL reports have been removed, the API is very slow (was taking up to 90% of the overall SQLd360 elapsed time) and the report itself is usually not that useful compared to raw data.
  • ASH Raw Data report has been enhanced with several new columns (mostly wait events related), this section is useful for quick searches on the most common ASH columns
  • SQLd360 now leverages eAdam to export GV$ACTIVE_SESSION_HISTORY and DBA_HIST_ACTIVE_SESS_HISTORY for the SQL ID of interest, this is useful for detailed ASH mining operations
  • Two new reports have been included, number of execs by PHV and total elapsed time by PHV, they can help in understanding how impact each PHV is (ie. one single exec of the bad plan took as much as 20 execs for the good plan)
  • CBO environment and IO Calibration statistics are now collected

Plus a few minor improvements as well as several bug fixes 🙂

As usual please reach out of me if you have any question/comment/concern/suggestion!!

1 Comment

Trouble reading an execution plan? Maybe SQLd360 can help!

Last week I was in Dallas, TX for Hotsos 2015 and I had a few questions on how to read an execution plan so I thought why not including in SQLd360 an alternative representation of the execution plan, maybe one that is easier to understand and closer to the academic explanation of the execution plan as a tree.

The main improvements for version v1504 (released yesterday, March 8th) are around execution plans analysis, there is a new link “Plans analysis” in the Plans section with a few new reports, all organized by PHV.
Basically this new page will show N columns where N is the number of distinct plans for the SQL we focus on and for each PHV we have

  • Plan Tree
  • Average elapsed time per execution (recent and historical) from ASH
  • Top 15 wait events
  • Top 15 objects accessed
  • Top 15 execution plan step id / operation

The plan tree is an organizational chart where the execution plan is represented as a tree upside-down where the first operation executed is the first leaf on the extreme left and the next operation is the next left (walking from left to right) and where each couple (or more) nodes are combined together by their parent node.

Ie. in the following execution plan the first step is #5 but somebody might be tricked thinking it’s #13 (or #7) since more indentedScreen Shot 2015-03-09 at 10.21.40 PM

while looking at the new chart is way easier to understand that step #5 is indeed the first one executedplan

Hopefully you will like it too!!

Other small additions for v1504 are Optimizer system statistics, Segments/Objects information and fixes here and there that (hopefully) reduce the number of bugs in the code 🙂


SQLd360 v3 now available, new features and (hopefully) less bugs!

Couple of weeks ago SQLd360 v1 came out and now v3, so what happened to v2? It was a “silent” release to fix a bug and sneak in a couple features I didn’t complete in time for v1 but most of the new features are in v3.
The main page now looks something like thisScreen Shot 2015-02-26 at 10.01.13 PM

so the list of content grew a bit and it will keep growing release after release.
List of bugs (hopefully) shrank as well but no promise on that 🙂

The major additions are

  • Plan Control section reporting info about SQL Profiles, SQL Plan Baselines and SQL Patches
  • Execution plans details from SQL Plan Baselines
  • Time series report/chart for average and median elapsed for the SQL (regardless of the plan), this can help answer the question “how is my SQL doing over time?”
  • Time series report/chart for average elapsed time per plan hash value for the SQL, this can help identify if a change in performance is caused by a plan change
  • A standalone script to execute the SQL from SQL*Plus, the script includes bind variables definition/assignment in case the SQL has binds
  • Cursor Sharing section reporting info from GV$SQL_SHARED_CURSOR
  • Bind datatype mismatch report, this can help identify those cases where apps define binds for the same SQL using different datatypes

plus some other minor reports/tweaks.

Bug fixes here and there, specially in the histograms section where now every endpoint value is (AFAIK, if you see issues let me know so I can fix them!) properly converted and charted so we can leverage charts to analyze histograms 😀
Ie the following one is for SH.CUSTOMERS.CUST_YEAR_OF_BIRTHhistogram

From the chart above we can see how the data is distributed and for each bar(/bucket) the (approximate) value, how many rows belong to each bucket and the selectivity for an equality predicate for that specific value.

More things in the work and as usual feedback/comments/reported issues are very much welcome!!!!


SQLd360, SQL diagnostics collection made faster

Another tool to help with SQL Tuning? Yes! Hopefully with some advantage over what you used until today 🙂

Collecting diagnostics around a SQL is not fun, if you’ve been there you know that. It requires to extract info from several different places, the amount is high and it’s time-sensitive aka you should do it as quick as possible so that things around the SQL don’t change!

SQLd360 is a free tool that provides a 360-degree overview around a SQL statement. The output is a single zip file that allows offline analysis, trend analysis and detailed root-cause investigations. The content of the zip is a set of small reports, each specific to an area of interest, easy to navigate through an index file.

SQLd360 doesn’t require any installation and can be executed by any user that has access to dictionary views. The tool can be used by DBAs, Developers, SysAdmin, etc to investigate several aspects of the SQL, not only in terms of isolated execution but also in the context of the overall database impact, it can also help with trend analysis.
The list of contents is going to grow (fast) with time so make sure you use the latest version.

SQLd360 works successfully in 10g, 11g and 12c, specifically on Linux/Unix platforms, it has not been tested on Windows.

If what described so far sounds familiar then you are probably right. SQLd360 has been created to complement eDB360 tool created by Carlos Sierra. The design is similar but at the same time different having to deal with different challenges.
SQLd360 is also included in eDB360 starting version v1504.


SQLd360 is free, you can download it from either GitHub or from the link under the Download section on the sidebar of this blog.


Download SQLd360 and review the README.txt file included.
Unzip on the database server and navigate to the master directory.
Connect to the database as any other user with access to dictionary views (SYS not recommended) and start sqld360.sql

SQLd360 has two input parameters

  1. SQL ID – SQL ID for which SQLd360 will collect diagnostics.
  2. License – SQLd360 extracts information from both AWR (licensed by Oracle under the Diagnostics Pack) and SQL Monitoring repository (part of the Oracle Tuning Pack). This parameter accepts one of 3 values: “T”, “D” or “N”. If your database is licensed under the Oracle Tuning Pack, enter then the value of “T”. If your database is not licensed to use the Oracle Tuning Pack but it is licensed to use the Oracle Diagnostics Pack, enter “D” then. If your site is not licensed on any of these two Oracle Packs, enter “N” then. Be aware that a value of “N” reduces substantially the content and value of the output. Thus the preferred parameter value is “T” (Oracle Tuning Pack).


# unzip
# cd sqld360-master
# sqlplus / as sysdba
SQL> @sqld360.sql 0vy6pt4krb3gm T

SQLd360 usually completes within a few minutes but it can take longer in case the amount of data to analyze in AWR/ASH is significant. The output file can reach up to 100Mb so make sure the directory where the tool is executed from has enough free space.

For any feedback please post on this blog or just email me directly.