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

19 Февраль 2015
Earlier
Пт, Мар 22, 2019

This is just a little shout-out about the Stats Advisor – if you decide to give it a go, what sort of things is it likely to tell you. The answer is in a dynamic performance view called v$stats_advisor_rules – which I've list below from an instance running 18.3.0.0.

SQL> set [...]
Source: jonathanlewis.wordpress.com
Чт, Мар 21, 2019

Here's a little puzzle that came up in the ODC database forum yesterday – I've got a query that has been captured by SQL Monitor, and it's taking much longer to run than it should but the monitoring report isn't telling me what I need to know about the [...]

Source: jonathanlewis.wordpress.com
Вт, Мар 19, 2019

A few months ago Franck Pachot wrote about a recursive SQL statement that kept appearing in the library cache. I discovered the note today because I had just found a client site where the following statement suddenly appeared near the top of the “SQL ordered by Executions” section of [...]

Source: jonathanlewis.wordpress.com
Вс, Мар 17, 2019
Уважаемый коллега Максим Филатов продолжает изыскания: 1) в ходе экспериментов с Infiniband на общедоступном железе Максим доказал/добился внесения важного уточнения в неоднозначную документацию поддержки Oracle Clusterware and RAC Support for RDS Over Infiniband (Doc ID 751343.1): In 12.2+ version, RDS is supported only for databases running on the engineered systems, [...]
Source: iusoltsev.wordpress.com
Ср, Мар 13, 2019
DBMS_SCHEDULER.CREATE_JOB to Run Procedures Asynchronously

Consider a PL/SQL stored procedure that handles a heavy transaction. The procedure is extremely slow – when executed from a UI, the application hangs for minutes. On analysis it is found that the procedure is performing a complex series [...]

Source: www.oratable.com
Ср, Мар 13, 2019

Here's an important thought if you've got any large tables which are purely hash partitioned. As a general guideline you should not need partition level stats on those tables. The principle of hash partitioned tables is that the rows are distributed uniformly and randomly based on the hash key so, [...]

Source: jonathanlewis.wordpress.com
Пн, Мар 11, 2019

I've made use of the function a few times in the past, for example in this posting on the dangers of using reverse key indexes, but every time I've mentioned it I've only been interested in the “leaf blocks per key” option. There are actually four different variations of [...]

Source: jonathanlewis.wordpress.com
Пн, Мар 11, 2019
Наиболее точное определение хинта DYNAMIC_SAMPLING можно найти у Джонатана Льюиса: You can use the dynamic_sampling() hint in two different ways in an SQL statement. If you use the hint in the “cursor-level” form: /*+ dynamic_sampling ({level}) */ e.g. /*+ dynamic_sampling(4) */, this is equivalent to setting the parameter optimizer_dynamic_sampling to [...]
Source: iusoltsev.wordpress.com
Вс, Мар 10, 2019
Если вдруг в приступе доброты душевной разработчик решил не ограничивать себя в фантазиях потребление ресурсов и, например, в определении materialized view (или в запросе) по-простому вставил /*+ PARALLEL */, и это породило не всегда приятный для администраторов DML вида: , который в полном соответствии с Automatic Degree of Parallelism (AutoDOP) [...]
Source: iusoltsev.wordpress.com
Пт, Мар 08, 2019

One of the questions that came up on the CBO Panel Session at the UKOUG Tech2018 conference was about the /*+ append */ hint – specifically how to make sure it was ignored when it came from a 3rd party tool that was used to load data into the database. [...]

Source: jonathanlewis.wordpress.com
Ср, Мар 06, 2019

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

Source: jonathanlewis.wordpress.com
Пн, Мар 04, 2019

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

Source: jonathanlewis.wordpress.com
Пт, Мар 01, 2019

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

Source: jonathanlewis.wordpress.com
Пн, Фев 25, 2019
Keep the Response Body with Non-2xx Responses in PL/SQL

When UTL_HTTP web service calls nicely return 2xx status codes, all is well with the world. Things get tricky when a web service call encounters an error and sends back a non-2xx response. [...]

Source: www.oratable.com
Сб, Фев 16, 2019

Compare the results of the following query with the clause “fetch first 2 rows only”

with t1(a) as (select * from table(odcinumberlist(1,3))) ,t2(a,b) as (select * from table(ku$_objnumpairlist( [...]
Source: orasql.org
Сб, Фев 16, 2019

Everyone knows that rownum in inline views blocks many query transformations, for example pushing/pulling predicates, scalar subquery unnesting, etc, and many people use it for such purposes as a workaround to avoid unwanted transformations(or even CBO bugs).

Obviously, the main reason of that is different calculation of rownum:

If we pull the [...]

Source: orasql.org
Вт, Фев 05, 2019

Just short note: If your imported Oracle Linux image hangs on boot in the Oracle cloud, just set GRUB_DISABLE_UUID=”true” in /etc/default/grub

[...]
Source: orasql.org
Чт, Янв 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
Комментирование отключено.