Search

Top 60 Oracle Blogs

Recent comments

SQL Tuning

SQL Diag Repository

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.

Quick and dirty Pathfinder (or SQLT XPLORE)

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 was to let it run while I was working on a testcase to emulate the problem (and maybe run full blown Pathfinder on it).

Bushy Joins – a closer look

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.

SQLTXPLAIN vs SQLd360, differences and similarities

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 </p />
</p></div>

    	  	<div class=

Simple script to remove system-generated column groups

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.

SQL Monitoring, Flamegraph and Execution Plan Temperature 2.0

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…).

Little help in creating SQL Plan Baselines

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 Tuning Set for quick implementation of baselines down the road.

Presentations on Slideshare

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 </p />
</p></div>

    	  	<div class=

The Most Important Tool for SQL Tuning

Summary: SQLT is a tool that collects comprehensive information on all aspects of a SQL performance problem. SQL tuning experts know that EXPLAIN PLAN is only the proverbial tip of the iceberg but the fact is not well recognized by the Oracle database community, so much evangelization is necessary.

I remember the time I was trying to solve a production problem a long time ago. I did not have any tools but I was good at writing queries against the Oracle data dictionary. How does one find the PID of an Oracle dedicated server process? Try something like this:

select spid from v$process where addr=(select saddr from v$session where sid = '&sid')

My boss was not amused.

After the incident, he got me a license for Toad.

Writing queries against the data dictionary is macho but it is not efficient.

Tools are in.

Truncated CTAS text and SQL Plan Baselines

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 opinions so here it goes: