Mauro Pagano's Blog

SQLd360, SQL diagnostics collection made faster

32 Comments

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.

Download

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

Instructions

Download SQLd360 and review the README.txt file included.
Unzip sqld360-master.zip 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).

Sample

# unzip sqld360-master.zip
# 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.

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

32 thoughts on “SQLd360, SQL diagnostics collection made faster

  1. Pingback: Learn how free new tool sqld360 can tell you so much about your favorite SQL! | Carlos Sierra's Tools and Tips

  2. Pingback: SQLd360, SQL diagnostics collection made faster | vinoracle

  3. Mauro,
    Great job! I did a quick test out and it looks pretty good.

    A few question for you, in edb360, for each top query, it generates the output from planx, sqlmon, sqlash, and sqlhc. It seems your sqld360 tool keeps sqlmon, and sqlash, but doesn’t include planx and sqlhc. The sqld360 reports does have some of output similar to what sqlhc generates. It seems run faster than sqlhc. Just curious, do you have a plan to include planx and sqlhc in the future?

    Also for a SQL with binding variables, the most useful script is to generate a test SQL with the input from the values of binding variables. I uses Kerry’s bbv script to generate the testing query. Your Test Case Section looks good, but sometime I usually just need a simple testing query similar like bbv script does. Is it possible to include a script like bbv in your tool? I would love to get everything I need in one run.

    Thanks,

    Weidong

    Liked by 1 person

  4. Hi Weidong,

    SQLd360 doesn’t exactly keep sqlash/sqlmon but it rather replace the functionalities so whatever was provided by them is in SQLd360 too. The same applies to planx.sql, pretty much everything that was in there is already in SQLd360, the reports are slightly different because SQLd360 can zoom/chart plus other reports are coming in the next releases.
    On SQLHC no, no plan to include it for two main reasons 1. every diagnostic provided by SQLHC (observations excluded) is already included inside SQLd360 and 2. SQLHC belongs to Oracle while SQLd360 is 100% free.

    About the test SQL yes that sounds a good idea, the binds info are already there as well as the SQL (also “xpanded” for views) but there is no script that packs everything together, I added to the TODO list and it should be there in the next release!

    About the Testcase, that comes from Testcase Builder (DBMS_SQLDIAG) and it’s the way Oracle pushes TCs to be built when working with Support/Development, I included it so the files are already there and it’s pretty quick to get a SR moving😉

    Thanks for the feedbacks!

    Liked by 1 person

  5. Hi Mauro,
    Excellent tool working good with sqlplus, with sql_developer showing below error

    line 29: SQLPLUS Command Skipped: set SERVEROUT ON
    line 29: SQLPLUS Command Skipped: set FEED OFF

    Error starting at line : 93 File @ C:\sqld360-master\sqld360.sql
    In command –
    @sqld360_driver.sql
    Error report –
    Unable to open file: “sqld360_driver.sql”

    ERROR: There was output on STDERR
    ‘rm’ is not recognized as an internal or external command,
    operable program or batch file.

    Like

    • Hi Azhar,

      Yes that’s normal, SQLd360 requires a SQL*Plus connection, I wouldn’t expect it to work from SQL Developer.
      It’s designed having the bare minimum requirements in mind, aka SQL*Plus🙂

      Thanks for the feedback!

      Like

  6. Can you please share download link ?

    Like

  7. Pingback: Joining the Accenture Enkitec Group | bdt's oracle blog

  8. Hi Mauro,

    I do not want to generate text and excel output. Can you please let me know how can i turn off ?

    Thanks
    James

    Like

    • Hi James,

      as of now there is no “easy” way to turn that off and that is actually intentional because we noticed that skipping two formats doesn’t improve performance so much but removes flexibility.
      Out of curiosity, why do you want to turn txt and csv off? Is the tool taking too long to run? If yes, could you send me the zip file so I can take a look at what is consuming time?
      Also there are some info (ie. ASH raw data, that has been enhanced in v1505 I released last night) that are much easier to consume in csv/txt
      Not trying to convince you here though, just trying to understand the reason!🙂

      Thanks,
      Mauro

      Like

  9. I really like the idea of not having to install the SQLTXPLAIN packages into each target database, but having to install the sqld360 executable on each DB server is even more work. I connect to hundreds of Oracle installations from my Windows PC rather than check out OS passwords to run SQL*Plus on each individual system. Do you have any plans to create a version of sqld360 that can be run on the PC through a SQL*Net connection to any database? Creating the report files right on my PC would also be much more convenient than having the FTP them from the target system.

    Thanks,
    David

    Like

    • Hi David,

      Assuming I understood correctly what you mean (if no, please correct me) then SQLd360 already works from a remote client.
      This is actually how I run the tool myself, I’ve the scripts on my laptop (Mac) and I connect to my test db via sqlnet, the files are created directly on my laptop and zipped together.
      The only problem I can see using Windows would be “zip” not available (there is no “seeded” command to do it AFAIK) so you would have to zip them manually.
      Did I answer your question?

      Thanks,
      Mauro

      Like

      • Hi,

        I might have misunderstood the question (if so, I apologize and please correct me) but I couldn’t find any evidence that you can’t control which tablespace is used by each user.
        Not sure if you referred to tablespace groups where in that case the usage would be distributed, but here it was a simple old tablespace🙂

        Thanks,
        Mauro

        Like

  10. Pingback: Useful links | Something new in a developers life

  11. Pingback: Interview with eDB360 Author and performance expert Carlos Sierra | Madora Consulting

  12. Pingback: Get information about Cursor Sharing for a SQL_ID | DBA survival BLOG

  13. Pingback: The Twelve Days of SQLT: Day Two: Miles To Go Before We Sleep - Oracle - Oracle - Toad World

  14. Pingback: SQL Monitoring without MONITOR Hint | Carlos Sierra's Tools and Tips

  15. Pingback: #EMd360 … OEM health checks made easy | DBASolved

  16. Pingback: 9 Ferramentas de Tuning grátis que você deve conhecer | DBTimeWizard

  17. Pingback: How to fix CPU usage problem in 12c due to DBMS_FEATURE_AWR | DBA survival BLOG

  18. Hello Mauro
    I downloaded the sqld360 today generated report for one SQL ID and unzip the output on my PC, after opening the first file if click on any link showed on the main page , the next page does not open getting error page can not be displayed? Please advise.

    Thanks
    Sital

    Like

  19. There are log files and has lots of data in it.
    00002_sqld360_687341_52grkyhrw7wk6_log.txt
    00004_sqld360_687341_52grkyhrw7wk6_log2.txt
    and there are 100’s of html files too

    Sital

    Like

  20. text size was too large and when I tried to send to as an attachment with outlook , found that the files were not extracted from zip files, extracted in different folder and now I can open the LINK.
    Sorry for the inconvenience and greatly appreciated with prompt response and help.

    Yesterday we had SQL tuning session with Carlos and he recommended this tool, I downloaded today and tried to use it first time and will seek more help from you as and when I will end up with any issue or will need clarifications.
    It was really a great to chat with you and really impressed with highly professionalism.

    Thanks again
    Sital

    Like

    • I was in the call yesterday too🙂 thanks for nice words! Usually the issue you see is caused by running SQLd360 from Windows without the “unix-tools” installed. SQLd360 needs basic unix utilities like cp, zip, etc

      Like

  21. Pingback: SQLd360, SQL diagnostics collection made faster – Power to Build

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s