Introducing SQLdb360: merging eDB360 and SQLd360, while raising the bar for community engagement
Mauro Pagano's Blog
by Mauro Pagano
3y ago
Today, we are very happy to release SQLdb360, a new tool that merges together eDB360 and SQLd360, under a single package Tools eDB360 and SQLd360 can still be used independently, but now there is only one package to download and keep updated. All the new features and updates to both tools are now in that one package. The biggest change that comes with SQLdb360 is the kind invitation to everyone interested to contribute to its development. This is why the new blended name and its release format. We do encourage your help and ideas to continue building a free, open-source, and hopefully a YMMV ..read more
Visit website
SQL Diag Repository
Mauro Pagano's Blog
by Mauro Pagano
3y ago
Every once in a while I look around the list of new (fixed) views in the instance trying to find useful / interesting things to add to SQLd360 and recently I stumbled into V$SQL_DIAG_REPOSITORY / V$SQL_DIAG_REPOSTIORY_REASON. SQL> desc v$sql_diag_repository Name Null? Type ------------------ -------- ------------- ADDRESS RAW(8) CON_ID NUMBER HASH_VALUE NUMBER SQL_ID VARCHAR2(13) CHILD_NUMBER NUMBER SQL_DIAG_REPO_ID NUMBER TYPE VARCHAR2(2 ..read more
Visit website
Quick and dirty Pathfinder (or SQLT XPLORE)
Mauro Pagano's Blog
by Mauro Pagano
3y ago
The other day I was working on a SQL with an odd plan (JPPD with pushed predicate not on the driver table inside the view) when as a test I flipped OFE back one version and got the plan I was expecting, this is (one of) the typical use case(s) for Pathfinder or SQLT XPLORE. I didn’t have a reproducible testcase and while creating it is always a good thing (IMHO), I was working in a lower environment that gave me a degree of freedom in testing things. I knew exactly which release to go from (12.1.0.1 -> 12.1.0.2) so I wrote a few lines of PL/SQL to implement a smaller Pathfinder. The idea wa ..read more
Visit website
Bushy Joins – a closer look
Mauro Pagano's Blog
by Mauro Pagano
3y ago
When 12.2 came out most of the (optimizer) focus was around SPD and how to avoid the challenges from 12.1. Still 12.2 introduced several (less acclaimed) optimizations including “Bushy Join” transformation, which is interesting since (I think, corrections welcome) Bushy Join concept isn’t necessarily tied to query transformation in general, especially before 12.2 (some reference about “manual” bushy joins here and here) or in other RDBMS (a manual example on SQL Server here). Anyway being the CBO way of improving our code query transformations here we go again. There isn’t much ..read more
Visit website
SQLTXPLAIN vs SQLd360, differences and similarities
Mauro Pagano's Blog
by Mauro Pagano
3y ago
When talking tools to help with SQL Tuning the question on SQLTXPLAIN vs SQLd360 comes out very often. “What’s the difference?”, “Which one should I use?”, “Why another tool?” are probably the top ones I’ll try to make a fair and (as much as possible) unbiased comparison between the two on some general aspects. If you want to know of any specific area just let me know in the comments. Aspect SQLTXPLAIN SQLd360 Installation Requires installation. Creates two schemas that serve as metadata and code repository, they need to be present every time the tool is executed No installation, at the ..read more
Visit website
Simple script to remove system-generated column groups
Mauro Pagano's Blog
by Mauro Pagano
3y ago
There seem to be a lot of interest (at least on Twitter and at OUG conferences) about Oracle recommendation to install a couple patches on top of 12.1.0.2, in order to emulate 12.2 behavior when it comes to SQL Plan Directives (details here, need MOS account). One of the things SQL Plan Directives do is trigger column groups (CG) creation. Column groups are virtual columns representing a hash (SYS_OP_COMBINED_HASH) of the multiple table columns they are defined on (that’s why only equality conditions can be satisfied by CG) and they have an ugly long system-generated name. According to the DEC ..read more
Visit website
SQL Monitoring, Flamegraph and Execution Plan Temperature 2.0
Mauro Pagano's Blog
by Mauro Pagano
3y ago
Two of the things that I like the most about SQL Monitoring reports are the ability to quickly spot where in the execution plan the time is spent (Activity% column, thank you ASH) and the fact you can collapse part of the plan. Too bad the two don’t “work” together meaning if you collapse a part of the plan the Activity% is not rolled up at the collapsed level. I understand why it works that way (it might be confusing otherwise) but I’d still like to be able to collapsed a node and get a “subtree Activity%” so I know if that subtree is something I should be worry about or not (kind of ..read more
Visit website
Little help in creating SQL Plan Baselines
Mauro Pagano's Blog
by Mauro Pagano
3y ago
One of the main design goals behind SQLd360 is to have no installation nor “evidence” left in the database, i.e. there is no SQLd360 repository in the database while there is a SQLTXPLAIN one (this isn’t necessarily bad, it’s just a different approach). As a consequence several little things SQLT provided are gone with SQLd360, for example few years ago (it’s been disabled by default for a while) SQLT generated a script to create a “custom” SQL Profile for the best performing plan it identified, something similar was happening for SQL Plan Baselines stored in SQL Tunin ..read more
Visit website
Presentations on Slideshare
Mauro Pagano's Blog
by Mauro Pagano
3y ago
Every once in a while I get asked if I can email the PPT for a session that I delivered. I always say YES (of course) so I figure why not be proactive and upload the material fot the presentations I delivered over the last several months. Under the “Pages” section on the right side of the page there is a new link “Presentations” that takes you to Slideshare. It’s my first experience with Slideshare and I’m pretty sure I made mistakes along the way so if you see something wrong just let me know (and let me know how to fix it PLEASE ) The list of presentation is probably incomplete so if you at ..read more
Visit website
Truncated CTAS text and SQL Plan Baselines
Mauro Pagano's Blog
by Mauro Pagano
3y ago
This is probably not earth-shattering (not that I ever blog earth-shattering things anyway) for many but it does answer a question I got today about “Do you think a truncated SQL text for CTAS affects SPM ability to give me the desired plan?”. SQL text for CTAS is truncated as result of bug 17982832 (sister bugs 18705302 and20308798 affect 10046 and AWR respectively) but does this affect SPM? Can SPM match on the truncated text? Or maybe can SPM see the whole text and match on the original SQL? Those are the questions I wanted to answer. As usual a test is worth a thousand expert opi ..read more
Visit website

Follow Mauro Pagano's Blog on FeedSpot

Continue with Google
Continue with Apple
OR