dbms_xplan

Jonathan Lewis's picture

Hint Reports

Nigel Bayliss has posted a note about a frequently requested feature that has now appeared in Oracle 19c – a mechanism to help people understand what has happened to their hints.  It’s very easy to use, it’s just another format option to the “display_xxx()” calls in dbms_xplan; so I thought I’d run up a little demonstration (using an example I first generated 18 years and 11 versions ago) to make three points: first, to show the sort of report you get, second to show you that the report may tell you what has happened, but that doesn’t necessarily tell you why it has happened, and third to remind you that you should have stopped using the /*+ ordered */ hint 18 years ago.

I’ve run the following code on livesql:

Jonathan Lewis's picture

Shrink Space

I have never been keen on the option to “shrink space” for a table because of the negative impact it can have on performance.

I don’t seem to have written about it in the blog but I think there’s something in one of my books pointing out that the command moves data from the “end” of the table (high extent ids) to the “start” of the table (low extent ids) by scanning the table backwards to find data that can be moved and scanning forwards to find space to put it. This strategy can have the effect of increasing the scattering of the data that you’re interested in querying if most of your queries are about “recent” data, and you have a pattern of slowing deleting aging data. (You may end up doing a range scan through a couple of hundred table blocks for data at the start of the table that was once packed into a few blocks near the end of the table.)

Franck Pachot's picture

Explain Plan format

The DBMS_XPLAN format accepts a lot of options, which are not all documented. Here is a small recap of available information.

The minimum that is displayed is the Plan Line Id, the Operation, and the Object Name. You can add columns and/or sections with options, such as ‘rows’, optionally starting with a ‘+’ like ‘+rows’. Some options group several additional information, such ‘typical’, which is also the default, or ‘basic’, ‘all’, ‘advanced’. You can choose one of them and remove some columns, with ‘-‘, such as ‘typical -rows -bytes -cost -plan_hash -predicate -remote -parallel -partition -note’. Finally, from an cursor executed with plan statistics, you can show all execution statistics with ‘allstats’, and the last execution statistics with ‘allstats last’. Subsets of ‘allstats’ are ‘rowstats’, ‘memstats’, ‘iostats’, buffstats’.

Of course, the column/section is displayed only if the information is present.

Jonathan Lewis's picture

dbms_xplan

My favourite format options for dbms_xplan.display_cursor().

This is another of those posts where I tell you about something that I’ve frequently mentioned but never documented explicitly as a good (or, at least, convenient) idea. It also another example of how easy it is to tell half the story most of the time when someone asks a “simple” question.

tim.evdbt@gmail.com's picture

Presentation “Real-Time SQL Tuning” at Hotsos Sym2014 and UTOUG TD2014

This presentation was delivered at the Hotsos Symposium 2014 and at the Utah Oracle Users Group (UTOUG) Training Days 2014.

It describes a PL/SQL package that I put together called “ash_xplan.sql” which can be used to display real-time information about a SQL statement currently executing.  This package combines information from the Active Session History (V$ACTIVE_SESSION_HISTORY) or “ASH” view with information from the DBMS_XPLAN package.

As of Oracle12c v12.1, nothing in Oracle displays elapsed time while the SQL statement is still executing, not even SQL Monitor.

The source code for the “ash_xplan.sql” script is on the Scripts page of this website, along with sample spooled output.

tim.evdbt@gmail.com's picture

ash_xplan_output.txt

tim.evdbt@gmail.com's picture

ash_xplan.sql

marco's picture

Tuning Database XQuery Statements (2)

So we concluded the post “Tuning Database XQuery Statements (1)” with the following SQL statement…

marco's picture

Tuning Database XQuery Statements (1)

I had a question of a colleague to have a look at a statement that…

Jonathan Lewis's picture

Lunchtime quiz

There was a question on OTN a few days ago asking the following question:

Here’s a query that ran okay on 11g, but crashed with Oracle error “ORA-01843: not a valid month” after upgrade to 12c; why ?

The generically correct answer, of course, is that the OP had been lucky (or unlucky, depending on your point of view) on 11g – and I’ll explain that answer in another blog posting.

That isn’t the point of this posting, though. This posting is a test of observation and deduction. One of the respondants in the thread had conveniently supplied a little bit of SQL that I copied and fiddled about with to demonstrate a point regarding CPU costing, but as I did so I thought I’d show you the following and ask a simple question.’

To prevent automated spam submissions leave this field empty.
Syndicate content