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

19 Февраль 2015
Earlier
Чт, Янв 31, 2019

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

Source: jonathanlewis.wordpress.com
Пт, Янв 18, 2019

This note is a follow-up to a recent comment a blog note about Row Migration:

So I wonder what is the difference between the two, parallel dml and serial dml with parallel scan, which makes them behave differently while working with migrated rows. Why might the strategy of serial dml [...]

Source: jonathanlewis.wordpress.com
Чт, Янв 17, 2019

Nigel Bayliss has posted a note about a frequently requested feature that has now appeared in Oracle 19c – a mechanism to help people understand what has happened to their hints. It's very easy to use, it's just another format option to the “display_xxx()” calls in dbms_xplan; so [...]

Source: jonathanlewis.wordpress.com
Вс, Дек 30, 2018

Three interesting myths about rowlimiting clause vs rownum have recently been posted on our Russian forum:

TopN query with rownum “fetch first N rows only” is always faster than rownum “SORT ORDER BY STOPKEY” stores just N top records during sorting, while “WINDOW SORT PUSHED RANK” sorts all input and [...]
Source: orasql.org
Пт, Дек 21, 2018

One last post before closing down for the Christmas break.

Here's a little puzzle with a remarkably easy and obvious solution that Ivica Arsov presented at the UKOUG Tech2018 conference. It's a brilliant little puzzle that makes a very important point, because it reminded me that most problems are easy [...]

Source: jonathanlewis.wordpress.com
Чт, Дек 20, 2018

This is a follow-up to a note I wrote nearly 12 years ago, looking at the problems of transitive closure (or absence thereof) from the opposite direction. Transitive closure gives the optimizer one way of generating new predicates from the predicates you supply in your where clause (or, in [...]

Source: jonathanlewis.wordpress.com
Вт, Дек 18, 2018

People ask me from time to time if I'm going to write another book on the Cost Based Optimizer – and I think the answer has to be no because the product keeps growing so fast it's not possible to keep up and because there are always more and more [...]

Source: jonathanlewis.wordpress.com
Пт, Дек 14, 2018

This note is a variant of a note that I wrote a few months ago about the impact of nulls on column groups. The effect showed up recently on a client site with a little camouflage that confused the issue for a little while, so I thought it would be [...]

Source: jonathanlewis.wordpress.com
Пт, Дек 14, 2018
С Иваном Постниковым наблюдали/разбирали нечасто встречаемое ожидание library cache load lock, в течение заметного времени наблюдавшееся у ряда сессий 2-го инстанса: , содержимое ROW_WAIT_OBJ# не очень полезно: , судя по пустым SQL_EXEC_START / SQL_EXEC_ID запросы/курсоры находятся в процессе погрузки в Library Cache (на что указывает и название ожидания длительностью SECONDS_IN_WAIT [...]
Source: iusoltsev.wordpress.com
Пн, Дек 10, 2018

A recent thread on the ODC database forum highlighted a case where the optimizer was estimating 83,000 for a particular index full scan when the SQL Monitor output for the operation showed that it was returning 11,000,000 rows.

Apart from the minor detail that the OP didn't specifically ask a question, [...]

Source: jonathanlewis.wordpress.com
Пн, Дек 10, 2018
До обновления 12.1 -> 12.2 запрос удовлетворительно (SLA, timeouts,…) быстро выполнялся по плану: , с использованием OJPPD — Old-stype (non-cost-based) Join Predicate Push-Down (*) высокоизбирательного условия ("INVOICE.CLIENT_ID) в обзор (**) вида UNION-ALL PARTITION (***) с последующим быстрым индексным доступом (****) В версии 12.2, похоже, что-то существенно изменилось, OJPPD в процессе [...]
Source: iusoltsev.wordpress.com
Пт, Дек 07, 2018

Prompted by a question on the ODC database forum – and also because I failed to get to the “Bonus slides” on my presentation on basic execution plans at both the DOAG and UKOUG conferences, here's a small of slides demonstrating how to convert a text execution plan into a [...]

Source: jonathanlewis.wordpress.com
Пт, Дек 07, 2018

A recent post on the ODC database forum prompted me to write a short note about a trap that catches everyone from time to time. The trap is following the obvious; and it's a trap because it's only previous experience that lets you decide what's obvious and the similarity [...]

Source: jonathanlewis.wordpress.com
Пт, Дек 07, 2018

Recently I had to install the patch for fixing cross-platform PDB transport bug onto the docker images with Oracle, so these are easy way how to do it:

1. create directory “patches” and create “install_patches.sh”:

#!/bin/bash unzip -u ./*.zip CURDIR=`pwd` for D in *; do if [ -d "${D}" ]; [...]
Source: orasql.org
Пн, Дек 03, 2018

There's a little detail of row migration that's been bugging me for a long time – and I've finally found a comment on MoS explaining why it happens. Before saying anything, though, else I'm going to give you a little script (that I've run on 12.2.0.1 with an 8KB block [...]

Source: jonathanlewis.wordpress.com
Пт, Ноя 30, 2018

I tweeted a reference yesterday to a 9 year old article about index rebuilds, and this led me on to look for an item that I thought I'd written on a related topic. I hadn't written it (so there's another item on my todo list) but I did discover a [...]

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

Here's another little utility I use from time to time (usually for small tables) to check how many rows there are in each block of the table, and which blocks are used. It doesn't do anything clever, just call routines in the dbms_rowid package for each rowid in the table:

rem rem [...]
Source: jonathanlewis.wordpress.com
Вт, Ноя 27, 2018

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

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

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

Source: jonathanlewis.wordpress.com
Сб, Ноя 24, 2018

Just what you need after a long hike in the Lake District:

[...]
Source: jonathanlewis.wordpress.com
Вт, Ноя 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
Вт, Ноя 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
Вт, Окт 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
Комментирование отключено.