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

19 Февраль 2015
Вт, Май 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
Пн, Фев 17, 2020

It is possible to take subquery factoring (common table expressions / CTEs) too far. The most important purpose of factoring is to make a complex query easier to understand – especially if you can identify a messy piece of text that is used in more than one part of the [...]

Source: jonathanlewis.wordpress.com
Пт, Фев 14, 2020

The semantics of comparing character columns of different types can lead to some confusion, so before I get into the main body of this note here's a little test based on a table with one row:

create table t1(c2 char(2), c3 char(3), vc2 varchar2(2), vc3 varchar2(3)); insert into t1 values ('XX','XX','XX','XX'); commit; select count(*) [...]
Source: jonathanlewis.wordpress.com
Вт, Фев 04, 2020

The view v$undostat is a view holding summary information about undo activity that can be used by the automatic undo mechanism to deal with optimising the undo retention time (hence undo space allocation). The view holds one row for every ten minute interval in the last 4 days (96 hours) [...]

Source: jonathanlewis.wordpress.com
Ср, Янв 29, 2020

I hesitate to call something a bug simply because Oracle doesn't do what I thought it would do; but when a trace file says:

“I'm not going to do X because P is not true“

followed a little later by

“I'm going to do Y because P is true“

then I think it's [...]

Source: jonathanlewis.wordpress.com
Пн, Янв 27, 2020

I am seeing “traditional” Oracle SQL syntax being replaced by “ANSI”-style far more frequently than I used to – so I thought I'd just flag up another reminder that you shouldn't be too surprised if you see odd little glitches showing up in ANSI style that don't show up when [...]

Source: jonathanlewis.wordpress.com
Пт, Янв 24, 2020

Here's another anomaly that appears when you mix and match Oracle features. In this case it's “With” subqueries (common table expressions / CTEs) and Active Dataguard (ADG) Standby databases. The problem appeared on the Oracle-l listserver and luckily for the OP another member of the list had seen it [...]

Source: jonathanlewis.wordpress.com
Чт, Янв 23, 2020

These screenshot are from JDevelopers - 12c
1. Click on Window - Resources

2. In the Resources pallet - Click on Import to open the 'Import Catalogs and Connections' window.

3. Now browse to locate the *.rcx file containing connection details.

4. Then click on Import to import [...]
Source: oracleanil.blogspot.ru
Комментирование отключено.