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

19 Февраль 2015
Earlier
Пн, Ноя 27, 2023

When you specify that a column used in an index should be a “descending” column Oracle uses the internal, undocumented, sys_op_descend() function to derive a value to store for that column in the index leaf block.

For many years I've claimed that this function simply takes the one's-complement of a character [...]

Source: jonathanlewis.wordpress.com
Пн, Ноя 20, 2023

This is a note that's been awaiting completion for nearly 10 years. It's about a feature (or, at least, a hint for the feature) that appeared in 10.2.0.5 to control some of the inter-process messaging that takes place in parallel execution.

It's a very simple idea that can make a significant [...]

Source: jonathanlewis.wordpress.com
Пн, Ноя 20, 2023

A little detail to remember when trouble-shooting at the session level – some of the information summarised in the Instance Activity figures (v$sysstat) is not available in the Session Activity figures (v$sesstat / v$mystat). The difference goes right down to the x$ objects, and here are two versions of a [...]

Source: jonathanlewis.wordpress.com
Пн, Ноя 06, 2023

This is just a short note (I hope) prompted by a conversation on the Oracle-L list server. A query from a 3rd party application was doing a very large full tablescan with hash join when it should have been doing a high precision index driven nested loop join, and the [...]

Source: jonathanlewis.wordpress.com
Ср, Ноя 01, 2023

I've written a few notes about problems with “descending” indexes in the past (the word is in quotes because it's not the index that's defined as descending, it's a proper subset of the columns of the index). A silly example came up recently on twitter where the cost of [...]

Source: jonathanlewis.wordpress.com
Ср, Ноя 01, 2023

Jump to summary.

A new optimizer feature that appears in 23c (probably not 21c) was the ability to push group by clauses into union all set operations. This will happen unhinted, but can be hinted with the highly memorable [no_]push_gby_into_union_all() hint that appeared in 23.1.0.0 according to v$sql_hint. and [...]

Source: jonathanlewis.wordpress.com
Пт, Окт 27, 2023

That's not the 23rd article I've written on sql_trace, it's just that there's a little “catch-up” detail about sql_trace that I recently discovered in 23.2 (though it might also be working in 21c or even in recent versions of 19c).

Although level 4 and 8 (binds and waits) of the SQL [...]

Source: jonathanlewis.wordpress.com
Ср, Окт 18, 2023

This is a note that echoes a feature (dating back at least as far as 10.2.0.4) that I've described in a previous post on Simpletalk. I'm raising it here for three reasons

first it says something about optimising SQL by rewriting it secondly it advertises the hugely under-used feature [...]
Source: jonathanlewis.wordpress.com
Ср, Окт 11, 2023

This is a second follow-up to the video Connor McDonald produced a few days ago about the risks of depending on “current tricks” to control the order of predicate operation, then showing how some requirements for the pattern “check condition B only for rows which have already satisfied condition A” [...]

Source: jonathanlewis.wordpress.com
Вс, Окт 08, 2023

As the years roll by, Oracle Corp. introduces new procedures (and functions) to replace older procedures that were inefficient, or gave away too much privilege, or simply had used bad naming standards. In relatively recent versions Oracle Corp. has introduced a pragma in the declaration of such procedures/functions that [...]

Source: jonathanlewis.wordpress.com
Чт, Окт 05, 2023

The “common table expression” (CTE) also known as “with clause” or “factored subquery” has been the target of an optimizer upgrade in recent versions of Oracle that may cause a problem for some people – including, possibly, a few of my former clients and readers who may have adopted a [...]

Source: jonathanlewis.wordpress.com
Вт, Сен 05, 2023

A recent post on the Oracle SQL and PL/SQL forum posted the following query with a complaint that it produced the wrong results:

select count(*) from all_synonyms left join all_objects b on (b.owner,object_name)=(select table_owner,table_name [...]
Source: jonathanlewis.wordpress.com
Сб, Сен 02, 2023
Проблема проявилась в запросах типа: с использованием view with ORDER BY и PL/SQL функции При наличии всех необходимых индексов план выглядел пессимистично: со странным комментарием в трейсе: , несмотря на: — до этого я как-то искренне считал, что проблема Order BY Elimination (OBYE) для Oracle 19 давно неактуальна Легковоспроизводимый тесткейс [...]
Source: iusoltsev.wordpress.com
Сб, Авг 19, 2023

A recent post on one of the forums (needs a MOS account) asked if it was possible to change a batch of SQL Plan Profiles from “exact match” to “force match” without going through the hassle of dropping and recreating them. This took me back a couple of [...]

Source: jonathanlewis.wordpress.com
Вт, Авг 08, 2023

This is a follow-up to a comment on an earlier post about LOBs and sizing. The comment raised a question about finding the stored size of xmltype data (which is essentially a LOB enhanced to include “methods”) particularly when it's declared with compression.

This is one of those questions where [...]

Source: jonathanlewis.wordpress.com
Чт, Июл 13, 2023

Here's a note that was prompted by a report about a delete that started up lots of PX processes but apparently did a serial delete even though parallel DML has been forced (alter session force parallel DML parallel 10). The description of “serial” was subsequently refined to explain that “only [...]

Source: jonathanlewis.wordpress.com
Ср, Июл 12, 2023

A fairly common question about parallel DML is: “Why isn't it happening?” A fairly common (correct) answer to this question is: “Because you haven't enabled it.” Unlike parallel query and parallel DDL which are enabled by default parallel DML could cause strange deadlocking side-effects so you have to enable it [...]

Source: jonathanlewis.wordpress.com
Пн, Июн 26, 2023

Here's an example of reading an execution plan that appeared on one of the Oracle forums recently. It's a simple example, made harder by the initial (costmetic) presentation of the problem. Essentially the owner has said:

Here's a query that's taking too long. Here's a bit of the query that [...]
Source: jonathanlewis.wordpress.com
Пн, Июн 26, 2023
Во время периодически наблюдаемых (с коллегой Николаем Грузинцевым) приостановок выполнения заданий Scheduler Jobs, процесс CJQ0 (Job Queue Coordinator) периодически отвлекался от выполнения прямых обязанностей/координации, не всегда быстро, но неизменно параллельно выполняя служебные запросы типа a6frhy5cw03g6 и bmcj2k06ncg3y (*): с примечательными комментариями, отключенным SQL-мониторингом и указанием на допустимость непараллельного выполнения (NO_STATEMENT_QUEUING): [...]
Source: iusoltsev.wordpress.com
Пн, Июн 19, 2023

The three laws of Oracle DBAs (with apologies to Isaac Asimov)

First Law: An Oracle DBA may not harm data or, through inaction, allow data to come to harm. Second Law: An Oracle DBA must ensure that the database is highly available provided such availability does not conflict with the First law. Third [...]
Source: jonathanlewis.wordpress.com
Пт, Июн 16, 2023
Отображение в ASH sql-выполнений c PHV=0 успешно продолжается и, поскольку часто требуется оценить производительность/возможность ускорения запроса средствами SQL Plan Management (SPM) и кол-во выполнений может быть небольшим, желательно точно определить план в том числе для таких не совсем типичных выполнений Тем более, что в отличие от 12c, в версиях 19c [...]
Source: iusoltsev.wordpress.com
Вт, Июн 13, 2023

Here's a problem that appeared on the Oracle-L list server a little while ago. It features an Exadata system with a “very large” GTT (global temporary table) and a pair of queries that are repeated frequently, using a different literal value on each repetition of the pair.

The queries, with [...]

Source: jonathanlewis.wordpress.com
Пт, Апр 28, 2023
Крайне ожидаемое улучшение — Improved System Monitor (SMON) Process Scalability: …SMON is responsible for cleaning up temporary segments that are no longer in use. SMON checks regularly to see whether it is needed, and other processes can call SMON. Temporary space management can affect SMON's scalability for other critical actions. [...]
Source: iusoltsev.wordpress.com
Пн, Апр 17, 2023
Стандарный OEBS запрос 1ap5awtfdvhp9, не меняя простого плана выполнения по стандартному индексу: , значительно увеличил среднее время выполнения (ELA_PER_EXEC) в сравнении с предыдущим месяцем: , совместно с кол-вом читаемых блоков на выполнение GETS_PER_EXEC без значительного изменения ожиданий и — важно — без какого-либо взрывного роста сегментов данных запроса — таблицы [...]
Source: iusoltsev.wordpress.com
Вс, Апр 02, 2023
Ободрившись схожестью описываемых и наблюдаемых симптомов, включая: и тем фактом, что The fix for 32753472 is first included in 19.13.0.0.DBRU (т.е. включён в процесс DBRU и в составе работающих систем версии 19.13 проблем не вызывает), установили патч на нагруженную тестовую систему версии 19.10 со следующим неприятным рез-том: — где "снежные [...]
Source: iusoltsev.wordpress.com
Пт, Мар 03, 2023

Here's a little quiz that came to light on a database running 11g. It's one of those anomalies that is likely to get noticed only in fairly extreme cases and it's about DDL rather than DML so it shouldn't be happening very often on anyone's system.

Read through the following script [...]

Source: jonathanlewis.wordpress.com
Пт, Фев 10, 2023

A historic complaint about the result cache was that it did not scale. Although this complaint was often the consequence of the mechanism was being used inappropriately, there was an underlying issue that imposed a limit on how scalable (in terms of concurrency) the cache could be: it was covered [...]

Source: jonathanlewis.wordpress.com
Чт, Фев 09, 2023

This note is just a little background for an impending article on the costs and effects of exchanging partitions, splitting partitions, merging partitions – or any other DDL on partitions except dropping them – in the light ofr “deferred global index maintenance”.

Summary Information: while a “typical” rowid is stored in [...]

Source: jonathanlewis.wordpress.com
Чт, Фев 02, 2023

This is a list of articles I've written that pick up some details (usually problems or bugs) when upgrading. Each entry has a date stamp and a short note of the contents. The articles are generally listed most-recent first but the catalogue is in two sections: Benefits and Bugs and [...]

Source: jonathanlewis.wordpress.com
Ср, Фев 01, 2023

A question appeared recently in a comment on a blog note I wrote about the new (in 12c) deferred global index maintenance that allowed the execution of a drop partition to return very quickly. The question was in two parts:

Why is an exchange partition so slow by comparison? Is there [...]
Source: jonathanlewis.wordpress.com
Комментирование отключено.