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

19 Февраль 2015
Earlier
Пт, Май 17, 2019
С Максимом Филатовым рассматривали кейс с заметным кол-вом ожиданий cursor: pin S wait on X в том месте/тесте, где их быть по идее быть не должно По данным AWR проблема выглядела следующим образом: ASH показывает параллельные процессы (P…) тестовых INSERT STATEMENT, блокирующие друг друга на cursor: pin S wait on [...]
Source: iusoltsev.wordpress.com
Вт, Май 14, 2019

Sometimes it might be useful to analyze top time-consuming filter and access predicates from ASH, especially in cases when db load is spread evenly enough by different queries and top segments doesn't show anything special, except usual things like “some tables are requested more often than others”.
Of course, we can [...]

Source: orasql.org
Вт, Май 07, 2019

Here's an execution plan that's just been published on the ODC database forum. The plan comes from a call to dbms_xplan.display_cursor() with rowsource execution statistics enabled.

There's something unusual about the execution statistics that I don't think I've seen before – can anyone else see anytying really odd, or (better still) [...]

Source: jonathanlewis.wordpress.com
Пт, Май 03, 2019

Before you comment – I do know that the title has a spelling mistake in it. That's because the Oracle code uses exactly this spelling in one of the little-used features of tracing.

I write a note a few years ago about enabling sql_trace (and other tracing events) system-wide for [...]

Source: jonathanlewis.wordpress.com
Чт, Май 02, 2019

This note is a reminder combined with a warning about unexpected changes as you move from version to version. Since it involves LOBs (large objects) it may not be relevant for most people but since there's a significant change in the default character set for the database as you move [...]

Source: jonathanlewis.wordpress.com
Вт, Апр 23, 2019

When dealing with the library cache / shared pool it's always worth checking from time to time to see if a new version of Oracle has changed any of the statistics you rely on as indicators of potential problems. Today is also (coincidentally) a day when comments about “parses” and [...]

Source: jonathanlewis.wordpress.com
Пт, Апр 19, 2019
Предыдущее наблюдение касалось обычных (неадаптивных) планов выполнения, адаптивные планы демонстрируют тот же эффект в версии 12.2, что логично и любопытно выглядит Итак, запрос с нестабильным / небыстрым выполнением показывает признаки адаптивности — столбцы "V$SQL.Adapt, "XML.Adapt (V$SQL_PLAN.OTHER_XML), плюс BIND_SENSE=Y — т.е. чувствителен к значениям связанных переменных, но для применения Adaptive Cursor [...]
Source: iusoltsev.wordpress.com
Пт, Апр 12, 2019

This note was prompted by a recent email asking about the optimizer's method for estimating the selectivity of a predicate which compared two columns in the same table – for example: “where orders.amount_invoiced = orders.amount_paid”. It's been about 14 years since I wrote “Cost Based Oracle – Fundamentals” so [...]

Source: jonathanlewis.wordpress.com
Вт, Апр 09, 2019
Начиная с версии 12.2 часто наблюдаю, как 2 плана выполнения с различными PHV используются для обозначения одного и того же фактического плана выполнения Например, запрос в анамнезе показывает 2 несовпадающих PLAN_HASH_VALUE при эквивалентной паре (FULL_PLAN_HASH_VALUE, PLAN_HASH_2): При этом, PHV=2164351587 и PHV=3400946657 по содержимому совпадают абсолютно: — и по набору операций [...]
Source: iusoltsev.wordpress.com
Пн, Апр 08, 2019

Here's an odd little change between Oracle versions that could have a stunning impact on the application performance if the thing that generates your client code happens to use an unlucky selection of constructs. It's possible to demonstrate the effect remarkably easily – you just have to describe a [...]

Source: jonathanlewis.wordpress.com
Сб, Апр 06, 2019
Продолжая тесты с Oracle LiveSQL 19c, проверил наличие проблемы с плохими FULL SCAN-ами при [авто]добавлении [INTERVAL] PARTITION в процессе каждодневной DML активности и запросами с условием IN Тестовая таблица простая, и в процессе первоначальной Direct PATH загрузки партиции добавляются, по крайней мере global statistics успешно собирается: NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE [...]
Source: iusoltsev.wordpress.com
Сб, Апр 06, 2019
Запись V$SESSION_LONGOPS, в частности, поля MESSAGE, TOTALWORK для операций Dynamic Sampling выглядят очень серьёзно: — при рекордно малой продолжительности ELAPSED_SECONDS = 8 для немаленькой таблицы — 278,127,738 Blocks — и только пустые SQL_PLAN_HASH_VALUE, SQL_EXEC_START, SQL_EXEC_ID да SQL_PLAN_OPTIONS выдают неладное) В ASH та же операция Dynamic Sampling также отчётливо отображена: — [...]
Source: iusoltsev.wordpress.com
Вт, Мар 26, 2019
INVALID Objects in Oracle

It is quite normal for Oracle database objects to become INVALID, especially when a dependency chain is broken. This articles takes a closer look at:

typical reasons why Oracle objects become INVALID how to identify INVALID objects [...]
Source: www.oratable.com
Пт, Мар 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
Комментирование отключено.