Oracle

Jonathan Lewis's picture

12c Snapshots

I published a note a few years ago about using the 12c “with function” mechanism for writing simple SQL statements to takes deltas of dynamic performance views. The example I supplied was for v$event_histogram but I’ve just been prompted by a question on ODC to supply a couple more – v$session_event and v$sesstat (joined to v$statname) so that you can use one session to get an idea of the work done and time spent by another session – the first script reports wait time:

fritshoogland's picture

Getting locale warnings when logging on to Linux

This blogpost is about the reason and solving getting the following message, or messages alike these when logging i to a linux box using ssh:

-bash: warning: setlocale: LC_CTYPE: cannot change locale (UTF-8): No such file or directory

However, this is a warning. Please mind such an issue might be come up in another way, which can be more disrupting; at least in the past I had issues running perl for the same issue:

[root@dmcel01 ~]# /usr/local/bin/ipconf -verify
perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
LANGUAGE = "en_US.UTF-8",
LC_ALL = "UTF-8",
LC_CTYPE = "UTF-8",
LANG = "en_US.UTF-8"
are supported and installed on your system.
perl: warning: Falling back to the standard locale ("C").

Franck Pachot's picture

My next Conferences in 2019

In my 2019 talks, a lot of performance stuff for DBA and Developers:

This year started with the amazing OBUG Belgium Tech Days 2019

The co-location of data and code, in present and future (like the MLE engine running JavaScript or Python in the database)

The most relevant statistics gathering in the 12.2 database (from 12cR2, 18c, 19c)

A different view on Join Methods by tracing the internal functions.

The Riga Dev Days in Latvia:

Riga Dev Days | Tech conference and the annual meeting point for developers in Baltic States.

Where I talk about microservices, and data/code co-location

Jonathan Lewis's picture

Cartesian Join

I wrote this note a little over 4 years ago (Jan 2015) but failed to publish it for some reason. I’ve just rediscovered it and it’s got a couple of details that are worth mentioning, so I’ve decided to go ahead and publish it now.

A recent [ed: 4 year old] question on the OTN SQL forum asked for help in “multiplying up” data – producing multiple rows from a single row source. This is something I’ve done fairly often when modelling a problem, for example by generating an orders table and then generating an order_lines table from the orders table, and there are a couple of traps to consider.

Jonathan Lewis's picture

Hash Optimisation-

Franck Pachot did an interesting presentation at the OBUG (Belgium user group) Tech Days showing how to use one of the O/S debug/trace tools to step through the function calls that Oracle made during different types of joins. This prompted me to ask him a question about a possible optimisation of hash joins as follows:

The hash join operation creates an in-memory hash table from the rowsource produced by its first child operation then probes the hash table with rows from the row source produced by the second child operation; but if there are no rows in the first row source then there’s no need to acquire rows from the second row source, so Oracle doesn’t call the second child operation.

Franck Pachot's picture

19c Auto Index: the dictionary views

The abbreviation AI may be misleading but it has nothing to do with Artificial Intelligence. And you may have been surprised that the ‘A’ means ‘Automatic’ rather than ‘Autonomous’ as the latter is constantly used to tag any new feature in the database since 18c. But this difference is really important: ‘Autonomous’ supposes that you don’t have anything to do and don’t even need to be notified about what happened. On the opposite, ‘Automatic’ means that some things are done without your intervention, in order to help you, but you are still in charge of managing them. And you need to look at the dictionary views, to be aware of the findings, recommendations, and implementations. Automatic Indexing is an evolution of the Advisors that were introduced since 10g and, in the same way, it provides many dictionary views to understand its activity.

Franck Pachot's picture

19c Easy Connect

When TCP/IP was the protocol used mostly everywhere, Oracle introduced EZCONNECT naming method to avoid long connection strings with parentheses everywhere. They said that it was a way to avoid tnsnames.ora but that’s not completely true:

  • you can use full connection strings without a tnsnames.ora
  • you still need a tnsnames.ora for more complex connection strings

But the idea was to replace:

(DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=APP.service.net))(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=2483)))

by:

localhost:2483/APP.service.net

and even reduce it to a simple hostname when using the default port and default service name.

I use it a lot when connecting manually, but as soon as there’s a need to add multiple hosts for transparent failover or load balancing, or to add some additional parameters, the full connection string is required.

Franck Pachot's picture

19c DG Broker export/import configuration

This is something I wanted for a long time: be able to save a broker configuration to be able to re-configure it if needed. What I usually do is maintain a script with all commands. What I dreamed was being able to export the configuration as a script. What we have now, in 19c, is the ability to export/import the configuration as a .xml file.

Actually, the configuration is already stored as XML in the broker configuration files (the .dat ones):

Franck Pachot's picture

Oracle 19c Hint Usage reporting

One reason why we try to avoid hints in our queries is that it is very difficult to use correctly. No error is raised when there’s an incorrect syntax or when the hint cannot be used semantically. 19c dbms_xplan has an important enhancement as it can report hint usage, at least for optimizer hints.

By default, DBMS_XPLAN in the default TYPICAL format will report only invalid hints:

Jonathan Lewis's picture

Descending Problem

I’ve written in the past about oddities with descending indexes ( here, here, and here, for example) but I’ve just come across a case where I may have to introduce a descending index that really shouldn’t need to exist. As so often happens it’s at the boundary where two Oracle features collide. I have a table that handles data for a large number of customers, who record a reasonable number of transactions per year, and I have a query that displays the most recent transactions for a customer.

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