Просторы интернета
If you've been wondering why I resurrected my drafts on the opt_estimate() hint, a few weeks ago I received an email containing an example of a query where a couple of opt_estimate() hints were simply not working. The critical features of the example was that the basic structure of the [...]
Over the last few days I've been tweeting little extracts from Practical Oracle 8i, and one of the tweets contained the following quote:
Practical Oracle 8i p.29
"If you are going to depend on a technological feature of Oracle, you need to make sure that you have tried to break [...]
PL/SQL functions in Oracle can be tagged with a DETERMINISTIC clause, to indicate that the function will always produce the same output for a given input and will have no side effects.
A little elaboration is in order here.
1. …will always produces the [...]A recent thread on the ODC SQL & PL/SQL forum raised the question of how data moves across a database link – is it row by row or array based (or other)? It's a good question as it's fairly common knowledge that distributed queries can be very much slower [...]
In the previous article in this series on the opt_estimate() hint I mentioned the “query_block” option for the hint. If you can identify a specify query block that becomes an “outline_leaf” in an execution plan (perhaps because you've deliberately given an query block name to an inline subquery and [...]
This is just a quick note to throw out a couple of of the lesser-known options for the opt_estimate() hint – and they may be variants that are likely to be most useful since they address a problem where the optimizer can produce consistently bad cardinality estimates. The first is [...]
Here's a question just in from Oracle-L that demonstrates the pain of assuming things work consistently when sometimes Oracle development hasn't quite finished a bug fix or enhancement. Here's the problem – which starts from the “scott.emp” table (which I'm not going to create in the code below):
rem rem [...]This is a note that was supposed to be a follow-up to an initial example of using the opt_estimate() hint to manipulate the optimizer's statistical understanding of how much data it would access and (implicitly) how much difference that would make to the resource usage. Instead, two years later, [...]
The following note is about a script that I found on my laptop while I was searching for some details about a bug that appears when you write SQL using the ANSI style format rather than traditional Oracle style. The script is clearly one that I must have cut and [...]
In an echo of a very old “conditional SQL” posting, a recent posting on the ODC general database discussion forum ran into a few classic errors of trouble-shooting. By a lucky coincidence this allowed me to rediscover and publish an old example of parallel execution gone wild [...]
Here's an answer I've just offered on the ODC database forum to a fairly generic type of problem.
The question was about finding out why a “program” that used to take only 10 minutes to complete is currently taking significantly longer. The system is running Standard Edition, and the program runs [...]
I started writing this note in March 2015 with the following introductory comment:
A little while ago I wrote a few notes about a very resource-intensive parallel query. One of the points I made about it was that it was easy to model, and then interesting to run on later [...]
A thread started on the Oracle-L list-server a few days ago asking for help analysing a problem where a simple “insert values()” (that handled millions of rows per day) was running very slowly. There are many reasons why this might happen, ranging from the trivial (someone has locked the [...]
A recent post on the ODC General Database forum asked for an explanation of the AWR report values “%Total CPU” and “%Busy CPU” under the “Instance CPU” label, and how the “%Busy CPU “ could be greater than 100%. Here's a text reproduction of the relevant sample supplied:
Host [...]A question came up on Oracle-l a few days ago about how Oracle calculates costs for a scalar subquery in the select list. The question included an example to explain the point of the question. I've reproduced the test below, with the output from an 18.3 test system. The [...]
… and thanks for any Douglas Adams references.
I decided on Friday that after more than 31 years using the Oracle software and more than 37 years of being self-employed in the computer industry – not to mention a further 8 years being an enthusiastic amateur – the time had finally [...]
One of the small changes (and, potentially big but temporary, threats) in 18.3 is the status of the “ignore hints” parameter. It ceases to be a hidden (underscore) parameter so you can now officially set parameter optimizer_ignore_hints to true in the parameter file, or at the system level, or at [...]
You may know that table() (kokbf$ collection functions), xmltable and json_table functions block Join-Predicate PushDown(JPPD).
Simple example:
You can see that JPPD works fine in case of global temporary tables and, obviously, the first workaround is to [...]
[Editorial note: this is something I started writing in 2013, managed to complete in 2017, and still failed to publish. It should have been a follow-on to another posting on the oddities of timestamp manipulation.]
Just as national language support used to be, timestamps and time-related columns are still a [...]
Today I wanted to give a link to the description of v$sql_hint.target_level to show that no_parallel can be specified for statement or object, and though it's pretty obvious, but surprisingly I haven't found any articles or posts about it, so this short post describes it.
v$sql_hint.target_level is a bitset, where
1st bit [...]
Last week I wrote a note about turning a range-partitioned table into a range/list composite partitioned table using features included in 12.2 of Oracle. But my example was really just an outline of the method and bypassed a number of the little extra problems you're likely to see in [...]
In yesterday's note on the options for converting a range-partioned table into a composite range/list parititioned table I mentioned that you could do this online with a single command in 18c, so here's some demonstration code to demonstrate that claim:
rem rem Script: [...]I wrote a short note a little while ago demonstrating how flexible Oracle 12.2 can be about physically rebuilding a table online to introduce or change the partitioning while discarding data, and so on. But what do you do (as a recent question on ODC asked) if [...]
It shouldn't be possible to get the wrong results by using a hint – but hints are dangerous and the threat may be there if you don't know eaxactly what a hint is supposed to do (and don't check very carefully what has happened when you've used one that you're [...]
A couple of weeks ago I published a note about an execution plan which showed the details of a scalar subquery in the wrong place (as far as the typical strategies for interpreting execution plans are concerned). In a footnote to the article I commented that Andy Sayer [...]