Oracle

Franck Pachot's picture

I ‘fixed’ execution plan regression with optimizer_features_enable, what to do next?

Here is a simple example of using Mauro Pagano ‘pathfinder’ tool where you don’t really want to run the query, but just get the execution plan with all variations of optimizer settings. That’s something I used many times in situations similar to this one:

Franck Pachot's picture

Easy Oracle Cloud wallet location in the JDBC connection string

I wrote about the 19c easy-connect string recently and the possibility to use a wallet with it (and no need for a tnsnames.ora then):

19c EZCONNECT and Wallet (Easy Connect and External Password File)

That was with sqlplus and setting TNS_ADMIN and still requires sqlnet.ora to set the wallet location directory. This post adds two things:

  • TNS_NAMES parameter in the JDBC URL with no need for
    the java -Doracle.net.tns_admin
  • We can add our password to the cloud wallet downloaded from the Autonomous Database (ATP/ADW)

Oracle Cloud user

For this test I’ve created a new user in my Autonomous Transaction Processing cloud service.

The click-path is:

martin.bach's picture

Learning about Kubernetes: JDBC database connectivity to an Oracle database

In the past few months I have spent some time trying to better understand Kubernetes and how application developers can make use of it in the context of the Oracle database. In this post I’m sharing what I learned along that way. Please be careful: this is very much a moving target, and I wouldn’t call myself an expert in the field. If you find anything in this post that could be done differently/better, please let me know!

By the way, I am going to put something similar together where Oracle Restful Data Services (ORDS) will provide a different, more popular yet potentially more difficult-to-get-right connection method.

Jonathan Lewis's picture

Execution Plan Puzzle

Here’s an execution plan that’s just been published on the ODC database forum. The plan comes from a call to dbms_xplan.display_cursor() with rowsource execution statistics enabled.

There’s something unusual about the execution statistics that I don’t think I’ve seen before – can anyone else see anything really odd, or (better still) anything which they would expect others to find odd but which they can easily explain.

A couple of hints:

Jonathan Lewis's picture

Occurence

Before you comment – I do know that the title has a spelling mistake in it. That’s because the Oracle code uses exactly this spelling in one of the little-used features of tracing.

Jonathan Lewis's picture

LOB length

This note is a reminder combined with a warning about unexpected changes as you move from version to version. Since it involves LOBs (large objects) it may not be relevant for most people but since there’s a significant change in the default character set for the database as you move up to 18.3 (or maybe even as you move to 12.2) anyone using character LOBs may get a surprise.

Here’s a simple script that I’m first going to run on an instance of 11.2.0.4:

Franck Pachot's picture

Avoid compound hints for better Hint Reporting in 19c

Even if the syntax accepts it, it is not a good idea to write a hint like:

https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Comments.html#GUID-56DAA0EC-54BB-4E9D-9049-BCEA934F7A89

/*+ USE_NL(A B) */ with multiple aliases (‘tablespec’) even if it is documented.

One reason is that it is misleading. How many people think that this tells the optimizer to use a Nested Loop between A and B? That’s wrong. This hint just declares that Nested Loop should be used if possible when joining from any table to A, and for joining from any table to B.

Actually, this is a syntax shortcut for: /*+ USE_NL(A) USE_NL(B) */

Jonathan Lewis's picture

Parse Calls

When dealing with the library cache / shared pool it’s always worth checking from time to time to see if a new version of Oracle has changed any of the statistics you rely on as indicators of potential problems. Today is also (coincidentally) a day when comments about “parses” and “parse calls” entered my field of vision from two different directions. I’ve tweeted out references to a couple of quirkly little posts I did some years ago about counting parse calls and what a parse call may entail, but I thought I’d finish the day off with a little demo of what the session cursor cache does for you when your client code issues parse calls.

Franck Pachot's picture

You should set OCSID.CLIENTID

each time you grab an Oracle JDBC connection from the pool

For troubleshooting and monitoring performance, you want to follow what happens from the end-user to the database. It is then mandatory to identify the end-user and application from the database session. With Oracle there are some ‘dbms_application_info’ strings to be set, like MODULE, ACTION and CLIENT_INFO. That’s about the tasks in the application code (like identifying the Java class or method from which the SQL statement is prepared) but that’s not about the end-user.

Jonathan Lewis's picture

In-table predicates

This note was prompted by a recent email asking about the optimizer’s method for estimating the selectivity of a predicate which compared two columns in the same table – for example:  “where orders.amount_invoiced = orders.amount_paid”. It’s been about 14 years since I wrote “Cost Based Oracle – Fundamentals” so my memory of what I wrote (and whether I even mentioned this case) was rather hazy, so I sent off a quick reply and decided to do a little checking.

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