Mauro Pagano's Blog

SQLd360, SQL diagnostics collection made faster

71 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.

71 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

  22. Hello Mauro, It is possible to create a SQL Profile using SQLd360? I used to create SQL Profiles using SQLT.

    Thanks.

    Like

    • Hi!

      Nope it’s not possible, I suggest you keep using coe_xfr_sql_profile.sql for it
      Also that functionality has been disabled (by default) in SQLT as well since we are leaning towards SPM more than SQL Profiles (granted there are use cases that can only be addressed using the SQL Profiles)

      Cheers,
      Mauro

      Like

  23. Pingback: eDB360 new features (March 2017) | Carlos Sierra's Tools and Tips

  24. hi Mauro,
    Nice work! I have one question. is it possible to remove the dependency on the SYS owned plan_table so that it works even if PLAN_TABLE works even there is table with same name/synonym in our DB user . in many of our work schemas we have the PLAN_TABLE created for many historical reasons ( i dont remember exacts issues) . We dont have sys/sysdba privileges in the DBs but have all the dictionary access.

    Cheers,
    Sachin

    Like

    • Hi!

      The historical reason is probably SQLTXPLAIN, many years ago it used to create the PLAN_TABLE in the application schema even though that wasn’t the best idea. Also the PLAN_TABLE (the SYS owned one) is a GTT while the PLAN_TABLE in the user schema tends to be a regular heap table that people forget to clean etc etc because of what just said Instead of removing the dependency on the SYS owned one, I suggest you explore removing (or just renaming to begin with) the PLAN_TABLE in the app schema and start relying on the one Oracle provides. No need for sys/sysdba privileges either, the PLAN_TABLE owned by SYS is accessible by any user (it’s a PUBLIC synonym).
      Please let me know if you have any question/feedback/comment

      Cheers,
      Mauro

      Liked by 1 person

      • hi Mauro,
        Thanks for the reply. sometimes in our app they create the table PLAN_TABLE under different application schema and create a public synonym also on it. thats where it is causing problems for me. i will check with them to drop that synonym and create the oracle provided synonym only.

        Cheers

        Liked by 1 person

  25. I am getting this error :

    zip error: Nothing to do! (sqld360_120797_8mx7tacbq5h7y?_773008_20171019_2103.zip)

    zip error: Nothing to do! (sqld360_120797_8mx7tacbq5h7y?_773008_20171019_2103.zip)
    SP2-0109: Cannot append to file “00002_sqld360_120797_8mx7tacbq5h7y/u01/app/oracle/product/11.2.0.4/dbhome_1_fadbbp160811_log.txt”
    21:04:11 col:4of5 “00241_sqld360_120797_8mx7tacbq5h7y?_4c_171_avg_buffer_getsrowexecution_per_phv_from_awr_for_cluster.html”
    not spooling currently

    zip error: Nothing to do! (sqld360_120797_8mx7tacbq5h7y?_773008_20171019_2103.zip)
    SP2-0109: Cannot append to file “00002_sqld360_120797_8mx7tacbq5h7y/u01/app/oracle/product/11.2.0.4/dbhome_1_fadbbp160811_log.txt”
    21:04:11 col:4of5 “00242_sqld360_120797_8mx7tacbq5h7y?_4c_171_avg_buffer_getsrowexecution_per_phv_from_awr_for_cluster_line_chart.html”
    not spooling currently

    zip error: Nothing to do! (sqld360_120797_8mx7tacbq5h7y?_773008_20171019_2103.zip)

    zip error: Nothing to do! (sqld360_120797_8mx7tacbq5h7y?_773008_20171019_2103.zip)

    zip error: Nothing to do! (sqld360_120797_8mx7tacbq5h7y?_773008_20171019_2103.zip)
    0
    SP2-0109: Cannot append to file “00002_sqld360_120797_8mx7tacbq5h7y/u01/app/oracle/product/11.2.0.4/xxxx0811_log.txt”

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    Like

  26. Hi Mauro,
    Thank you so much for this tool and also for providing updates for it.

    Our clients have problem to install SQLT as it creates new schemas but as SQLD360 doesn’t need any installation I’m very pleased to use this new tool (or Inter-continental missile we can say as I can fire it from my oracle client to DB hosted on remote site).

    But while using SQLD360 (latest version), I noticed that for section Statistics History there is no data with error in report:
    System Statistics History html (0)
    Tables Statistics History html (0)
    Indexes Statistics History html (0)
    Columns Statistics History html (0)
    Partitions Statistics History html (0)

    Also, one request from my side (or suggestion), can you please write a book or post for SQLD360 the way we have book for SQLT.

    Thank You,
    Amol V. Palav

    Like

    • Hi Amol,

      Thanks for the feedback.
      About the book, probably no just because technology moves so fast that by the time the book is done things have changed. If you have any area that you’d like to know more (using SQLd360) let me know and I can blog about it.

      About the Statistics History, what do you mean “there is no data with error”?
      The 0 could just mean there is no history for the statistics. Or do you see an error in the report when you open it?

      Cheers,
      Mauro

      Liked by 1 person

      • Hi Mauro,

        Thank you for your reply.
        I get error in subreport saying, sys.xxxx table not found, is it related to permissions?

        Also, regarding blog post about sqld360, I request you to please write a post about in general for what type performance tuning problem each section of sqld360 will be helpful ( at least in 2-3 lines and if you have some extra free time then 2-3 lines about info of every section). I’m asking for this information cause, yesterday I came across a problem in which query runs slow on every Tuesday cause new partition gets created on Tue but it’s stats get gathered on next day, so CBO gets old data and runs query with poor plan. but when I tried to analyse this problem using sqld360, I was unable to look into exact section and I couldn’t find useful info about this scenario.
        So if you can write a post as per my request, it would be a big help for newbies like me.

        Thank you,
        Amol V. Palav

        Like

      • Hi Amol,

        yes it’s privilege related (if you try with SYS you won’t get that error, for example).

        About the blog post, thanks for the suggestion, I’ll work on that!

        Cheers,
        Mauro

        Like

      • Hi Mauro,

        Thank you for your reply, I will try with sys user rather than application user.

        Also, I’m really happy that you are going to write a post as per my request, really thank you so much for taking some time out from your busy schedule.

        Thank you,
        Amol V. Palav

        Like

  27. Hi Mauro,

    first of all thanks for a wonderful tool – it’s been on my list for a while and I now wish I had started using it sooner 🙂
    A small issue has cropped up :

    I ran this on a 10.2.0.4 database without Diag or tuning packs;
    when I then go to “1f22 Table-level observations” I get this :

    FROM ind_cols r,
    *

    ERROR at line 145:
    ORA-32035: unreferenced query name defined in WITH clause

    thanks & regards
    Jo

    Like

    • Hi Jo,

      Thanks for reporting this.
      I don’t have a 10.2 handy to test it (and the issue doesn’t reproduce in currently supported versions) so it might take me a bit to get this fixed.
      It seems like it’s a consequence of a transformation not happening, leaving the SQL is a “state” that exposes the ORA-32035.

      If you want I can send you a temporary workaround file that just skips this block, would that work for you? If yes just email me directly and I’ll send you

      Cheers,
      Mauro

      Like

      • Hi Mauro,

        yes, I had seen other reports after Googling that in newer versions the ORA-32035 seems to disappear for many queries.
        Anyway, the place the error is indicated is misleading (ind_cols has nothing to do with it); the issue is with the “tab_stats_history” part : your select from this query is commented out for versions 10 & 11 (by using “&&skip_10g.&&skip_11g.”).

        I THINK the solution is just adding “&&skip_10g.&&skip_11g” to the front of lines 278 – 291 of file “sqld360_1f_observations.sql” (you then skip defining the query whenever you will also skip selecting from it) but you definitely want to double-check that 🙂

        It runs for me in this way and does give me output for the table-level observations.

        regards
        Jo

        Like

      • Now THAT place makes more sense to error out 🙂

        Yes that would be the solution.
        Are you ok keeping your code change in place until I release a new version of SQLd360 for everybody? I’ll release soon but probably after a few more changes on Thxgiving weekend 😉

        Like

      • Of course – thanks again for looking into it.

        regards
        Jo

        Like

      • Hello sir,

        can you please share yourmail id, mine ganesh81.mp@gmail.com

        Like

      • Hi,

        You can find it on the right side of the screen, below my picture 🙂

        Cheers,
        Mauro

        Like

  28. Pingback: Creating a SQL Plan Baseline from Cursor Cache or AWR | Carlos Sierra's Tools and Tips

  29. Pingback: Comparing Plans | Oracle Scratchpad

  30. Hi Mauro,
    I just want to generate graph for one sql_id to see “Avg Elapsed Time/Execution (total) for Instance” only.

    Can I run SQLd360 to only generate particular graph ?

    Regards,
    Mahesh

    Like

  31. Hi Mauro ,

    For me SQL360 is executing long time. Is there any way we can limit for few days only ? My awr retention period is 60 days ..

    Like

    • Hi!

      By default sqld360 looks only at the last 31 days, also the retention doesn’t really have *that* much impact (so even if you short it then it might still take long). Any chance you can send me the log so I can check why it’s taking long?

      Like

  32. I Mauro , Sorry Did not provide you the logs It is now taking less time . Did not do anything to fix.
    I have another question out of this context –
    So curious to know this .

    One sql submitted from JDBC (from application) is executing in 1 hour with one PHV 348848932
    and if i submit the same sql from sql developer it is running in 3 minutes with different PHV 4240405867

    Please guide and help .

    Like

  33. Hi Mauro,
    How to know session level parameters for specific good plan PHV in sqld360 report?
    For eg in my case – i have two one good PHV and bad PHV.
    Session level parameter changed for good PHV , but for bad PHV it is with all default settings.

    Like

  34. If both plans are still in memory then GV$SQL_OPTIMIZER_ENV will have the details.
    If the cursor aged out (aka it’s in AWR) then there is no public API to find that, the values are stored in RAW and only the hash value is published, available in DBA_HIST_SQLPLAN.OPTIMIZER_ENV_HASH_VALUE

    Like

  35. Thanks Mauro,
    I have run SQLT , It shows in observations that Child 1 on Plan ****** is missing Peeked Binds (session parameter was set “_optim_peek_user_binds” to false for good PHV sql)
    Where as in sqld360 , did not see in observations, But in SQL Plan Statistics from History DBA_HIST_SQL_PLAN in last but 3rd column it shows . If we can have in observations then it would be good. Just a thought

    Like

    • That sounds like a good idea. SQLd360 now is only part of SQLdb360 and it’s open for PR on Github, would you like to add such observation? If you send me the PR I will test it and merge it

      Cheers,
      Mauro

      Like

  36. Hi Mauro,
    Not sure if i can do it.

    Like

  37. Hi Mauro,

    Thanks for the new version. I’m using latest one now.
    One small question – if i want to run sqldb360 at DB level for few days not for whole month , How can i do it ?

    Regards,
    Maheshwar

    Like

    • Hi!

      You can edit the config file(s) under SQL but it’s usually not needed. What are you trying to achieve? Faster execution? Because reducing the number of days has a very small effect on that

      Like

  38. Yes Mauro… Can you please let me know how to which configuration file to change. My DB size 15 T. I wanted to gather data for few days only as i need to see during that time only. for one month data it takes lot of time.

    Like

    • Just as a clarification, going for a shorter period of time will NOT make it any faster.
      You can edit edb360_00_config.sql and modify edb360_conf_date_from and edb360_conf_date_to
      SQLd360 does not obey those specific parameters by the way (as it really makes little difference longer or shorter period)

      Like

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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s