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
- SQL ID – SQL ID for which SQLd360 will collect diagnostics.
- 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.
Pingback: Learn how free new tool sqld360 can tell you so much about your favorite SQL! | Carlos Sierra's Tools and Tips
Pingback: SQLd360, SQL diagnostics collection made faster | vinoracle
February 16, 2015 at 10:14 pm
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
LikeLiked by 1 person
February 16, 2015 at 10:32 pm
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!
LikeLiked by 1 person
February 18, 2015 at 2:23 am
Great! Thanks a lot for the response.
LikeLike
February 17, 2015 at 11:53 am
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.
LikeLike
February 17, 2015 at 1:20 pm
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!
LikeLike
February 17, 2015 at 10:48 pm
Can you please share download link ?
LikeLike
February 17, 2015 at 10:50 pm
Hi James,
on the right side of the screen, under Download section, you can find the link.
Let me know if it doesn’t work so I can fix it (I tested it now and works for me)
Thanks.
Mauro
LikeLike
Pingback: Joining the Accenture Enkitec Group | bdt's oracle blog
March 16, 2015 at 4:23 am
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
LikeLike
March 16, 2015 at 12:32 pm
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
LikeLike
April 30, 2015 at 6:06 pm
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
LikeLike
April 30, 2015 at 6:28 pm
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
LikeLike
May 4, 2015 at 4:02 pm
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
LikeLike
Pingback: Useful links | Something new in a developers life
Pingback: Interview with eDB360 Author and performance expert Carlos Sierra | Madora Consulting
Pingback: Get information about Cursor Sharing for a SQL_ID | DBA survival BLOG
Pingback: The Twelve Days of SQLT: Day Two: Miles To Go Before We Sleep - Oracle - Oracle - Toad World
Pingback: SQL Monitoring without MONITOR Hint | Carlos Sierra's Tools and Tips
Pingback: #EMd360 … OEM health checks made easy | DBASolved
Pingback: 9 Ferramentas de Tuning grátis que você deve conhecer | DBTimeWizard
Pingback: How to fix CPU usage problem in 12c due to DBMS_FEATURE_AWR | DBA survival BLOG
July 27, 2016 at 5:29 pm
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
LikeLike
July 27, 2016 at 5:43 pm
Any chance you can send me the zip file generated so I can look into it?
LikeLiked by 1 person
July 27, 2016 at 5:47 pm
Sorry Mauro : That is from MCA prod database and will be violation of security .
Sital
LikeLike
July 27, 2016 at 5:51 pm
Any chance to take a look at just the log file? It’s inside the zip file and it contains no data, just info about the execution. Sounds like something didn’t run as expected and without log there isn’t much I can comment about 😦
LikeLiked by 1 person
July 27, 2016 at 5:58 pm
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
LikeLike
July 27, 2016 at 6:00 pm
Correct, I need log file 00002, can you get me that at least? Othwerise it will be ver yhard to help
LikeLike
July 27, 2016 at 6:16 pm
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
LikeLike
July 27, 2016 at 6:44 pm
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
LikeLike
Pingback: SQLd360, SQL diagnostics collection made faster – Power to Build
November 30, 2016 at 1:48 pm
Hello Mauro, It is possible to create a SQL Profile using SQLd360? I used to create SQL Profiles using SQLT.
Thanks.
LikeLike
November 30, 2016 at 2:02 pm
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
LikeLike
Pingback: eDB360 new features (March 2017) | Carlos Sierra's Tools and Tips
April 15, 2017 at 10:28 pm
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
LikeLike
April 15, 2017 at 10:42 pm
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
LikeLiked by 1 person
April 16, 2017 at 5:33 pm
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
LikeLiked by 1 person
October 19, 2017 at 5:19 pm
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”
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
LikeLike
October 19, 2017 at 5:25 pm
Can you send me all the files via email? Looks like the problem might have started sooner than what you copied here
LikeLike
November 15, 2017 at 4:45 am
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
LikeLike
November 15, 2017 at 7:31 am
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
LikeLiked by 1 person
November 15, 2017 at 1:00 pm
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
LikeLike
November 18, 2017 at 3:15 am
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
LikeLike
November 18, 2017 at 10:06 pm
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
LikeLike
November 21, 2017 at 7:27 am
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
LikeLike
November 21, 2017 at 9:22 am
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
LikeLike
November 21, 2017 at 10:44 am
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
LikeLike
November 21, 2017 at 10:54 am
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 😉
LikeLike
November 21, 2017 at 10:58 am
Of course – thanks again for looking into it.
regards
Jo
LikeLike
August 3, 2018 at 6:21 am
Hello sir,
can you please share yourmail id, mine ganesh81.mp@gmail.com
LikeLike
August 3, 2018 at 6:33 am
Hi,
You can find it on the right side of the screen, below my picture 🙂
Cheers,
Mauro
LikeLike
Pingback: Creating a SQL Plan Baseline from Cursor Cache or AWR | Carlos Sierra's Tools and Tips
Pingback: Comparing Plans | Oracle Scratchpad
August 31, 2018 at 9:05 pm
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
LikeLike
August 31, 2018 at 9:19 pm
Hi Manesh,
You cannot, you need to generate the whole thing. Any reason why you don’t want to? Shouldn’t take more than a few mins anyway.
Cheers,
Mauro
LikeLike
September 8, 2018 at 12:23 am
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 ..
LikeLike
September 8, 2018 at 7:02 am
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?
LikeLike
October 15, 2018 at 7:24 am
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 .
LikeLike
October 15, 2018 at 10:02 pm
It’s hard to figure out why without additional info, if you can get a sqld360 for the same showing both plans I can try to help
LikeLike
October 19, 2018 at 12:52 am
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.
LikeLike
October 19, 2018 at 11:31 am
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
LikeLike
October 20, 2018 at 7:21 pm
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
LikeLike
October 20, 2018 at 8:46 pm
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
LikeLike
October 21, 2018 at 9:21 am
Hi Mauro,
Not sure if i can do it.
LikeLike
October 21, 2018 at 10:31 am
Understood, if you decide to I’ll be glad to assist
LikeLike
November 23, 2018 at 5:19 am
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
LikeLike
November 23, 2018 at 7:23 am
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
LikeLike
November 29, 2018 at 9:41 pm
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.
LikeLike
November 29, 2018 at 9:48 pm
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)
LikeLike
December 8, 2018 at 4:30 am
Thanks Mauro
LikeLike
November 11, 2020 at 7:41 pm
Why is the graph in the generated sqldb360 report not displayed?Thanks
LikeLike
November 11, 2020 at 9:10 pm
Are you opening it on a machine connected to the internet? That’s the most common reason for not displaying plots
LikeLike