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

19 Февраль 2015
Earlier
Вт, Сен 15, 2020

You can add also any information from v$rtsm_sql_plan_monitor if needed

create or replace function px_session_info return varchar2 parallel_enable as vSID int; res varchar2(30); begin vSID:=userenv('sid'); select to_char(s.server_group,'fm000') ||'-'||to_char(s.server_set,'fm0000') [...]
Source: orasql.org
Ср, Сен 09, 2020

It's a common pattern of Oracle features that they start with various restrictions or limitations that disappear over time. This note is about an enhancement to Bloom filter processing that appeared in the 18.1 optimizer and, for some people, may be a good enough reason for upgrading to a newer [...]

Source: jonathanlewis.wordpress.com
Пн, Авг 31, 2020

This is a little lesson in trouble-shooting. It assumes you have the privilege to generate and edit trace files, and all I'm going to do is show how I worked out the answer to a fairly simple question that appeared recently on the Oracle Developer Community forum.

I have a [...]

Source: jonathanlewis.wordpress.com
Пт, Авг 28, 2020
По следам недавнего успешного "краудсорсингового решения проблемы в телеграм-группе @RuOUG Собственно, моей была только формулировка проблемы: а не сталкивался ли кто с нерабочим partition pruning в 19.5 при использовании в качестве предикатов значений функций? в 12.1 всё работало( Антон подтвердил, а Саян Малакшинов — тут же предложил рабочее решение: [xtender] [...]
Source: iusoltsev.wordpress.com
Пт, Авг 28, 2020

This is part 1 of a short reference note about the work load created by referential integrity constraints when you delete from a parent table. It was prompted by a question on the Oracle Developer Community forum about how to handle a very large delete from a table which [...]

Source: jonathanlewis.wordpress.com
Пн, Авг 24, 2020

Here's a problem with the “flashback versions” technology that showed up at the end of last week. There's a thread about it on the Oracle Developer community forum, and a chain of tweets that was my initial response to a twitter alert about it that Daniel Stein posted.

The [...]

Source: jonathanlewis.wordpress.com
Ср, Авг 19, 2020

I've written a couple of notes in the past about the problems of optimising queries with predicates of the form “or exists {subquery}”. A recent question on the Oracle Developer Community forum brought to my attention an improvement in this area in (very precisely) 12.2, as well [...]

Source: jonathanlewis.wordpress.com
Вт, Авг 18, 2020

This is one of those notes that I thought I'd written years ago. It answers two questions:

what can I do with my materialized view? why isn't the optimizer using my materialized view for query rewrite?

I've actually supplied an example of code to address the first question as a throwaway comment in [...]

Source: jonathanlewis.wordpress.com
Чт, Авг 06, 2020

The following description of a bug appeared on the Oracle Developer Community forum a little while ago – on an upgrade from 12c to 19c a query starting producing the wrong results on a simple call to the average() function. In fact it turned out to be a bug [...]

Source: jonathanlewis.wordpress.com
Ср, Июл 15, 2020

I've pointed out fairly frequently that if you're running Standard Edition but would like to take advantage of a few features of the Partitioning option then you might be able to do something appropriate with Partition Views (but I've just discovered while searching my blog for a suitable item to [...]

Source: jonathanlewis.wordpress.com
Пн, Июл 13, 2020

A question about the min/max index scan appeared on the Oracle Developer Community forum recently. The query supplied in the thread was a little odd – you might ask why anyone would run it as it stands – and I've modified it to make it even stranger to [...]

Source: jonathanlewis.wordpress.com
Вс, Июл 12, 2020

One of the recurrent questions on the Oracle Developer Commuity forum is:

What's the best way to delete millions of rows from a table?

There are an enormous number of relevant details that you need to know before you can give the “right” answer to this question, e.g.

Which version of Oracle Standard [...]
Source: jonathanlewis.wordpress.com
Пт, Июл 10, 2020

There's a notable change in the way the optimizer does cost and cardinality calculations for recursive subquery factoring that may make some of your execution plans change – with a massive impact on performance – as you upgrade to any version of Oracle from 12.2.0.1 onwards. The problem appeared [...]

Source: jonathanlewis.wordpress.com
Чт, Июл 09, 2020

This is an example from the Oracle Deveoloper Community of using the output of SQL Monitor to detect a problem with object statistics that resulted in an extremely poor choice of execution plan.

A short time after posting the original statement of the problem the OP identified where he thought [...]

Source: jonathanlewis.wordpress.com
Пн, Июн 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 11.1.0.6 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
Комментирование отключено.