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

19 Февраль 2015
Пн, Июн 29, 2020

A question arrived in my email a few days ago with the following observations on a statement that was supposed to query the data dictionary for some information about a specified composite partitioned table. The query was wrapped in a little PL/SQL, similar to the following:

declare [...]
Source: jonathanlewis.wordpress.com
Вс, Июн 28, 2020

I've just released new version of my Simple Android Oracle Client.

New features:

Supported Oracle versions: 11.2, 12.1, 12.2, 18, 19, 20. SQL Templates: now you can save and load own script templates Server output (dbms_output) Export results as JSON, CSV and HTML files (long tap on results) Copy results to the Clipboard as JSON [...]
Source: orasql.org
Ср, Июн 17, 2020

This is an update on a post I wrote nearly 10 years ago describing how the optimizer could choose to ignore a lower cost indexed access path and use a higher cost index if the lower cost were based on guesswork. The original article article used (select {constant} from [...]

Source: jonathanlewis.wordpress.com
Ср, Июн 10, 2020

This note is an index to the articles I've written about Subquery Factoring (aka Common Table Expressions / CTEs, aka “with” subqueries).

Subquery factoring and the merge command When does Oracle materialize a “with” subquery automatically Rewriting to use subquery factoring can cause changes to execution plans Another example where [...]
Source: jonathanlewis.wordpress.com
Пт, Июн 05, 2020

Here's a surprising costing error that was raised on the Oracle Developer Forum a few days ago. There's a glitch in the cost atributed to sorting when an analytic over() clause – with corresponding “window sort” operation – makes a “sort order by” operation redundant. Here's a script to [...]

Source: jonathanlewis.wordpress.com
Ср, Июн 03, 2020

A question about mixing the (relatively new) “fetch first” syntax with “select for update” appeared a little while ago on the Oracle Developer Forum. The requirement was for a query something like:

select * from t1 order by [...]
Source: jonathanlewis.wordpress.com
Пн, Июн 01, 2020

This is a brief note with an odd history – and the history is more significant than the note.

While searching my library for an example of an odd costing effect for the “order by” clause I discovered a script that looked as if I'd written for in 2008 to [...]

Source: jonathanlewis.wordpress.com
Вт, Май 12, 2020

This is a reference note for a question that came up as a comment on a lengthy note I wrote about reading execution plans.

How do you interpret something like: from$_subquery$_001@SEL$1 in the Query Block Name / Object Alias section of an execution plan.

The simple answer is that if you've got [...]

Source: jonathanlewis.wordpress.com
Пн, Май 11, 2020
Обратил внимание, что в стандартном автоматически созданном (Sql Tuning Advisor) SQL Profile на фоне многочисленных корректирующих OPT_ESTIMATE лишь иногда (нечасто) проскакивают хинты типа TABLE_STATS / INDEX_STATS / COLUMN_STATS: , фиксирующие текущую статистику таблиц, столбцов и индексов: Это выглядит странно, учитывая что "основной для SQL Profile хинт OPT_ESTIMATE: применяется в большинстве [...]
Source: iusoltsev.wordpress.com
Вт, Май 05, 2020

In a comment to a recent post on reading a non-trivial execution someone asked me to repeat the exercise using a plan I had published a few days previously in a post about tweaking the hints in an outline. The query in question involved a number of subqueries [...]

Source: jonathanlewis.wordpress.com
Пт, Май 01, 2020

In previous articles on reading execution plans I've made the point that the optimizer is very “keen” to transform complex queries into queries consisting of a single query block and that there's a simple “First Child First (FCF)” rule for reading the plan for a single query block. I've [...]

Source: jonathanlewis.wordpress.com
Пт, Май 01, 2020

How do you work out what hints you need to tweak an execution plan into the shape you want?

Here's a “case study” that's been playing out over a few weeks on the Oracle Developer Community (here and here) and most recently ended up (in one of its versions) [...]

Source: jonathanlewis.wordpress.com
Ср, Апр 29, 2020

A couple of days ago I discussed an execution plan that displayed some variation in the way it handled subqueries and even threw in a little deception by displaying an anti-join that was the result of transforming a “not exists” subquery and a semi-join that looked at first sight [...]

Source: jonathanlewis.wordpress.com
Пн, Апр 27, 2020

In a recent blog note I made the point that there is a very simple rule (“first child first”) for reading execution plans if the query (as written or after transformation by the optimizer) consists of a single “query block”. However, if you have a plan that is [...]

Source: jonathanlewis.wordpress.com
Пт, Апр 24, 2020

I've been meaning to write this note for at least three years and was prompted to write up my draft notes this morning as a follow-up to yesterday's note on the perils of applying a to_date() function to a date column. But then I took a look at the [...]

Source: jonathanlewis.wordpress.com
Чт, Апр 23, 2020

Every now and again someone posts a piece of SQL on the Oracle Developer Forum that includes a predicate with an expression like to_date(date_column). This is a problem for several reasons – not the least being the type of performance problem that showed up in a post from a [...]

Source: jonathanlewis.wordpress.com
Ср, Апр 22, 2020

I woke up last night with a brilliant solution to a problem that's been bugging me for quite some time. How does a call to report_sql_monitor() manage to produce output like this:

SQL Plan Monitoring Details (Plan Hash Value=4262489872) ====================================================================================================================================================== | Id | [...]
Source: jonathanlewis.wordpress.com
Вт, Апр 21, 2020
Столкнулись с проблемой, подготовил простой тесткейс на данных схемы HR В SR отправил, но, возможно, я упустил что-то очевидное или кто-то уже сталкивался, или есть идеи и/или время/желание поэкспериментировать Запрос: в версии выдаёт простой и быстрый план: с преобразованием VIEW PUSHED PREDICATE и операцией TABLE ACCESS BY INDEX ROWID [...]
Source: iusoltsev.wordpress.com
Пн, Апр 20, 2020

One of the most important skills needed when investigating badly performing SQL is the ability to read Execution Plans. It's a topic I've written and spoken about frequently – even to the extent of doing full-day seminars – but there's always scope for finding another way of presenting the method.

I'll [...]

Source: jonathanlewis.wordpress.com
Вт, Апр 14, 2020
С версии 12.1 несколько раз наблюдал фэйковые Table Expansion Transformation (TE) — случаи, когда в присутствии на таблице частично Usable/Unusable Partitioned Index, оптимизатор задействует TE для операций, никак не использующих/не касающихся этого партицированного индекса Описываемый пример ( для таблицы с включеным In Database Row Archiving: и частично USABLE индексом (экономили [...]
Source: iusoltsev.wordpress.com
Пт, Апр 10, 2020

Many years ago I wrote a note with some comments about conveting the low_value and high_value columns of user_tab_columns to see what values they actually held. At the time the dbms_stats package held a few procedures to convert raw values and it was necessary to wrap these procedures in [...]

Source: jonathanlewis.wordpress.com
Пн, Мар 23, 2020

I've made casual remarks in the past about how “ANSI”-style SQL introduces extra complications in labelling or identifying query blocks – which means it's harder to hint correctly. This is a note to show how the optimizer first transforms “ANSI” SQL into “Oracle” syntax. I'm going to write a simple [...]

Source: jonathanlewis.wordpress.com
Пт, Мар 20, 2020
В процессе тестирования Oracle 19c попытка сгенерировать CBO trace неожиданно неудачна: , файл трейса обрывается на этапе SPM plan reproduce: — относительно свежей (неопределяемой на сайте поддержки, по крайней мере, для моего аккаунта) ошибкой ORA-07445: exception encountered: core dump [kkopmDumpPlanInfo()+… При этом целевой запрос имеет 4-х летней выдержки SQL Patch [...]
Source: iusoltsev.wordpress.com
Ср, Мар 18, 2020

The Oracle Developer Community forum often sees SQL that is hard to read – sometimes because it's a brutal tangle of subqueries, sometimes because the format it bad, sometimes because the use of table and column aliases is poorly done. One particular case of the last weakness is the code [...]

Source: jonathanlewis.wordpress.com
Пт, Мар 13, 2020

You're probably aware of the “identity” construct that appeared in 12.1 and uses Oracle's sequence mechanism to model the Autonumber or Identity columns that other databases have. A posting from Clay Jackson on the Oracle-L list server suggests that something about their code path has introduced a surprising [...]

Source: jonathanlewis.wordpress.com
Чт, Мар 12, 2020

I've just been prompted to complete and publish a draft I started a few years ago. It's (ultimately) about a feature that appeared in 9i but doesn't seem to show up very often at client sites or as a common solution to performance problems on the various Oracle forums – [...]

Source: jonathanlewis.wordpress.com
Вс, Мар 01, 2020
После формально успешного создания нестандартного индекса по 5 полям — многовато, но так советовал SQL Tuning Advisor!) — для стандартной таблицы EBS: с коллегой Максимом Филатовым наблюдали необычно абсолютное игнорирование этого индекса в трейсе CBO: т.е. по таблице XLA_DISTRIBUTION_LINKS и всем её индексам статистика приводится/рассматривается, кроме нового, с виду вполне [...]
Source: iusoltsev.wordpress.com
Вт, Фев 25, 2020

I've just received an email asking (yet again) a question about counting the number of rows in a table.

We have a large table with a CLOB column, where the CLOB occupies 85% storage space.
when we use select count(*) from , the DBA says that you should not use count(*) as [...]

Source: jonathanlewis.wordpress.com
Пн, Фев 24, 2020

Many years ago (2011) I wrote a note describing how you could attach the Outline Information from one query to the SQL_ID of another query using the official Oracle mechanism of calling dbms_spm.load_plans_from_cursor_cache(). Shortly after publishing that note I drafted a follow-up note with an example demonstrating that even when [...]

Source: jonathanlewis.wordpress.com
Вт, Фев 18, 2020

A quirky little feature of interval partitioning showed up on Twitter today – a parallel insert that would only use a single PX slave process to do the inserting. With 1.1 billion rows and the option for running parallel 32 this this made the loading process rather slower than [...]

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