Просторы интернета

19 Февраль 2015
Earlier
Вт, Ноя 20, 2018
Pagination Queries in Oracle: Analytics, Row Limiting Clause

Pagination is the process of dividing query results (typically on a user interface) into discrete pages, where each page contains a smaller/more manageable number of rows. In classic offset pagination, each page shows a fixed count of rows [...]

Source: www.oratable.com
Пн, Ноя 19, 2018

Over the last few days I've highlighted on Twitter a couple of older posts showing show 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 [...]

Source: jonathanlewis.wordpress.com
Чт, Ноя 15, 2018

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 [...]

Source: jonathanlewis.wordpress.com
Ср, Ноя 14, 2018

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 [...]

Source: jonathanlewis.wordpress.com
Чт, Ноя 08, 2018

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 [...]

Source: jonathanlewis.wordpress.com
Пн, Ноя 05, 2018

One month to go before the big event in Liverpool. so I've been browsing the agenda to get some idea of the talks I'll probably go to. At present this is what my list looksl like:

Sunday 14:00 Database block checking - the unknown truth 15:00 TBD 16:10 Oracle Database 12c consolidation: why and how to [...]
Source: jonathanlewis.wordpress.com
Чт, Ноя 01, 2018

So far in this series I've written about the way that the optimizer estimates cardinality for an equijoin where one end of the join has a frequency histogram and the other end has a histogram of type:

Frequency Top-Frequency Hybrid

It's now time to look at a join where the other [...]

Source: jonathanlewis.wordpress.com
Вт, Окт 30, 2018

In yesterday's article I described the mechanism that Oracle for an index leaf block split when you try to insert a new entry into a leaf block that is already full, and I demonstrated that the “50-50” split and the “90-10” split work in the same way, namely:

save the [...]
Source: jonathanlewis.wordpress.com
Пн, Окт 29, 2018

After writing this note I came to the conclusion that it will be of no practical benefit to anyone … but I'm publishing it anyway because it's just an interesting little observation about the thought processes of some Oracle designer/developer. (Or maybe it's an indication of how it's sensible [...]

Source: jonathanlewis.wordpress.com
Вс, Окт 28, 2018

I've got a data set which I've recreated in 11.2.0.4 and 12.2.0.1.

I've generated stats on the data set, and the stats are identical.

I don't have any indexes or extended stats, or SQL Plan directives or SQL Plan Profiles, or SQL Plan Baselines, or SQL Patches to worry about.

I'm joinig two [...]

Source: jonathanlewis.wordpress.com
Чт, Окт 25, 2018

In previous installments of this series I've been describing how Oracle estimates the join cardinality for single column joins with equality where the columns have histograms defined. So far I've covered two options for the types of histogram involved: frequency to frequency, and frequency to top-frequency. Today [...]

Source: jonathanlewis.wordpress.com
Вт, Окт 23, 2018

Here's one I've just discovered while trying to build a reproducible test case – that didn't reproduce because an internal algorithm has changed.

If you upgrade from 12c to 18c and have a number of hybrid histograms in place you may find that some execution plans change because of a change [...]

Source: jonathanlewis.wordpress.com
Пн, Окт 22, 2018

Sometimes a good thing becomes at bad thing when you hit some sort of special case – today's post is an example of this that came up on the Oracle-L listserver a couple of years ago with a question about what the optimizer was doing. I'll set the scene [...]

Source: jonathanlewis.wordpress.com
Пт, Окт 19, 2018

The following request appeared recently on the Oracle-L mailing list:

I have one scenario related to capturing of sql statement in history table.. Like dba_hist_sqltext capture the queries that ran for 10 sec or more.. How do I get the sql stmt which took less time say [...]

Source: jonathanlewis.wordpress.com
Ср, Окт 17, 2018

Here's a little question that popped up on the Oracle-L list server a few days ago:

I am facing this issue running this command in 11.2.0.4.0 (also in 12c R2 I got the same error)

SQL> SELECT TO_TIMESTAMP('1970-01-01 00:00:00.0','YYYY-MM-DD HH24:MI:SS.FF') + NUMTODSINTERVAL(2850166802000/1000, 'SECOND') FROM DUAL; SELECT TO_TIMESTAMP('1970-01-01 00:00:00.0','YYYY-MM-DD HH24:MI:SS.FF') + NUMTODSINTERVAL(2850166802000/1000, [...]
Source: jonathanlewis.wordpress.com
Пн, Окт 15, 2018

This is a short index of articles I've written on how to create the different types of histogram that the optimizer uses:

Faking a frequency histogram How to create frequency histograms (using a numeric column for the example) Histogram Tip An example of creating a simple [...]
Source: jonathanlewis.wordpress.com
Вс, Окт 14, 2018

When my mother-in-law comes round to Sunday lunch we often have roast chicken – and a serious error in estimating the requirement for the vegetable bed I was roasting on led to the discovery of home-made soup. (I did warn you that my post-operative posts would be light-weight)

Ingredients Remnants of cooked [...]
Source: jonathanlewis.wordpress.com
Пт, Окт 12, 2018

I've been a long time, though occasional, user of the undocumented dbms_system package, typically using it to write messages or insert break marks in trace files (or the alert log). Thanks to an email from Cary Millsap I've recently discovered that the procedures for writing to trace files have been [...]

Source: jonathanlewis.wordpress.com
Ср, Окт 10, 2018

Oracle 12c introduced the “Hybrid” histogram – a nice addition to the available options and one that (ignoring the bug for which a patch has been created) supplies the optimizer with better information about the data than the equivalent height-balanced histogram. There is still a problem, though, in [...]

Source: jonathanlewis.wordpress.com
Ср, Окт 10, 2018

By the time you read this I will have had the lenses in both my eyes replaced, so I won't be staring at a computer screen for a while – and that means, in particular, I won't be doing any further investigation into join cardinality for a while.

For those who [...]

Source: jonathanlewis.wordpress.com
Вт, Окт 09, 2018

In the previous posting I listed the order of precision of histograms as:

Frequency Top-Frequency Hybrid Height-balanced None

Having covered the Frequency/Frequency join (for a single column, no nulls, equijoin) in the previous posting I've decided to work down the list and address Frequency/Top-Frequency in this posting. It gets a little harder to generate data [...]

Source: jonathanlewis.wordpress.com
Вт, Окт 09, 2018
Conditional unique index on multiple columns

When we need to enforce uniqueness on a combination of table columns *only if* a certain condition is true, Oracle helps us implement this using a function-based index.

What is a Function-Based Index?

A function-based index is created on the value of [...]

Source: www.oratable.com
Пн, Окт 08, 2018

Here's a problem that (probably) won't affect the day to day running of most systems – but it could be a pain in the backside for people who write programs to generate repeatable test data. I'm not going to say much about the problem, just leave you with a test [...]

Source: jonathanlewis.wordpress.com
Пт, Окт 05, 2018

In the previous note I posted about Join Cardinality I described a method for calculating the figure that the optimizer would give for the special case where you had a query that:

joined two tables used a single-column to join on equality had no nulls in the join columns had a perfect frequency [...]
Source: jonathanlewis.wordpress.com
Ср, Окт 03, 2018

Following up my “Hacking for Skew” article from a couple of days ago, Chinar Aliyev has written an article about a method for persuading the optimizer to calculate the correct cardinality estimate without using for any undocumented, or otherwise dubious, mechanisms. His method essentially relies on the optimizer's [...]

Source: jonathanlewis.wordpress.com
Ср, Окт 03, 2018
Java Stored Procedures in Oracle

Some solution requirements are best addressed with Java code in the Oracle database. This article shows you use cases where Java methods in Oracle are useful, how Oracle lets you store Java methods and how they can be called from PL/SQL.

Java [...]
Source: www.oratable.com
Вс, Сен 30, 2018

A question about reading execution plans and optimising queries arrived on the ODC database forum a little while ago; the owner says the following statement is taking 14 minutes to return 30,000 rows and wants some help understanding why.

If you look at the original posting you'll see that we've [...]

Source: jonathanlewis.wordpress.com
Пт, Сен 28, 2018

In my presentation to the UKOUG SIG yesterday “Struggling with Statistics – part 2” I described a problem that I wrote about a few months ago: when you join a fact table with a massively skewed distribution on one of the surrogate key columns to a dimension holding the unique [...]

Source: jonathanlewis.wordpress.com
Чт, Сен 27, 2018

I seem to have written a number of aricles about column groups – the rather special, and most useful, variant on extended stats. To make it as easy as possible to find the right article I've decided to produce a little catalogue (catalog) of all the relevant articles, with a [...]

Source: jonathanlewis.wordpress.com
Ср, Сен 12, 2018

A little while ago I added a post-script to a note I'd written five years ago about gathering stats on a virtual column and had updated with a reference to a problem on the Oracle database forum that complained that stats collection had taken much longer after the addition [...]

Source: jonathanlewis.wordpress.com
Комментирование отключено.