Search

Top 60 Oracle Blogs

Recent comments

Oracle

Oracle Adaptive Plan info in OTHER_XML

DBMS_XPLAN displays the operation ID with no gap, even for Adaptive Plans where the inactive operations are skipped. Did you ever wonder where the information of skipped rows is stored?

Here is a simple query (but please, remember that natural join is bad ;)

SQL> set feedback on sql_id
SQL> select * from dept natural join emp natural join bonus;
no rows selected
SQL_ID: 3q7fbwk91v4ra

The execution plan shows that the plan is adaptive:

Dump logfile

Here’s a little procedure I’ve been using since Oracle 8i to dump the contents of the current log file – I’ve mentioned it several times in the past but never published it, so I’ll be checking for references to it and linking to it.

The code hasn’t changed in a long time, although I did add a query to get the full tracefile name from v$process when that became available. There’s also an (optional) called to dbms_support.my_sid to pick up the SID of the current session that slid into the code when that package became available.

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

Table order

Over the last few days I’ve highlighted on Twitter a couple of older posts showing how a change in the order that tables appear in the from clause could affect the execution plan of a query. In one case the note was purely theoretical describing a feature of the way the optimizer works with simple query blocks, in the other case the note was about an anomaly with table elimination that could appear with both “ANSI” and “traditional” Oracle syntax.

num_index_keys

The title is the name of an Oracle hint that came into existence in Oracle 10.2.0.3 and made an appearance recently in a question on the rarely used “My Oracle Support” Community forum (you’ll need a MOS account to be able to read the original). I wouldn’t have found it but the author also emailed me the link asking if I could take a look at it.  (If you want to ask me for help – without paying me, that is – then posting a public question in the Oracle (ODC) General Database or SQL forums and emailing me a private link is the strategy most likely to get an answer, by the way.)

The question was about a very simple query using a straightforward index – with a quirky change of plan after upgrading from 10.2.0.3 to 12.2.0.1. Setting the optimizer_features_enable to ‘10.2.0.3’ in the 12.2.0.1 system re-introduced the 10g execution plan. Here’s the query:

opatch investigations

This blogpost is about opatch and how to obtain information about the current oracle home(s), and how to obtain information about the patches to be applied.

Patches that can be applied using opatch are provided by oracle as zip files which have the following naming convention:
p[patchnumber]_[baseversion]_[platform]-[architecture].zip. The patch normally contains an XML file called ‘PatchSearch.xml’ and a directory with the patch number. Inside the patch number directory there is a README.txt which is lame, because it says ‘Refer to README.html’, and a README.html that contains the readme information that is also visible when the [README] button for this patch is selected in MOS.

Index Splits – 3

This is stored only for reference, and in case anyone wants to wade through the details. It’s the redo log dump from the 90/10 index leaf block split test from the previous blog posts running on 11.2.0.4 on Linux. The first part is the full block dump, the second part is an extract of the Record and Change vector headings with the embedded opcode (opc:) for the undo records in the redo vectors, and a tiny note of what each change vector is doing.

opatch versions

This blogpost is about oracle’s patching tool for the database and grid infrastructure: opatch. I personally have a love/hate relationship with opatch. In essence, opatch automates a lot of things that would be very error prone if it were to be done by hand, which is a good thing. However, there are a lot of things surrounding opatch that I despise. An example of that is the version numbering of opatch itself.

Versions and more versions

To jump into the matter directly: versions. I don’t understand why this has to be this complicated. I would even go as far as saying that somebody needs to step in and clean this up.

Oracle LIKE predicate and cardinality estimations

A new blog post on the Databases at CERN blog about the optimizer estimations with a LIKE predicate: https://db-blog.web.cern.ch/blog/franck-pachot/2018-11-oracle-predicate-and-cardinality-estimations.

Or why the cardinality estimation here is 1346 objects like ‘DBA_OBJECTS’ rather than 2:

Where / Having

There’s a very old mantra about the use of the “having” clause that tells us that if it’s valid (i.e. will always give the same results) then any predicate that could be moved from the having clause to the where clause should be moved. In recent versions of Oracle the optimizer will do this for itself in some cases but (for reasons that I’m not going to mention) I came across a silly example recently where a little manual editing produced a massive performance improvement.

Here’s a quick demo: