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

19 Февраль 2015
Earlier
Вт, Июн 25, 2019

This is a note that was supposed to be a follow-up to an initial example of using the opt_estimate() hint to manipulate the optimizer's statistical understanding of how much data it would access and (implicitly) how much difference that would make to the resource usage. Instead, two years later, [...]

Source: jonathanlewis.wordpress.com
Сб, Июн 22, 2019

The following note is about a script that I found on my laptop while I was searching for some details about a bug that appears when you write SQL using the ANSI style format rather than traditional Oracle style. The script is clearly one that I must have cut and [...]

Source: jonathanlewis.wordpress.com
Пн, Июн 17, 2019

In an echo of a very old “conditional SQL” posting, a recent posting on the ODC general database discussion forum ran into a few classic errors of trouble-shooting. By a lucky coincidence this allowed me to rediscover and publish an old example of parallel execution gone wild [...]

Source: jonathanlewis.wordpress.com
Пт, Июн 14, 2019

Here's an answer I've just offered on the ODC database forum to a fairly generic type of problem.

The question was about finding out why a “program” that used to take only 10 minutes to complete is currently taking significantly longer. The system is running Standard Edition, and the program runs [...]

Source: jonathanlewis.wordpress.com
Чт, Июн 13, 2019

I started writing this note in March 2015 with the following introductory comment:

A little while ago I wrote a few notes about a very resource-intensive parallel query. One of the points I made about it was that it was easy to model, and then interesting to run on later [...]

Source: jonathanlewis.wordpress.com
Вт, Июн 11, 2019

A thread started on the Oracle-L list-server a few days ago asking for help analysing a problem where a simple “insert values()” (that handled millions of rows per day) was running very slowly. There are many reasons why this might happen, ranging from the trivial (someone has locked the [...]

Source: jonathanlewis.wordpress.com
Вс, Июн 09, 2019
Запрос со ср.временем выполнения 3,7 мс периодически выполняется 22 секунды (*): — учитывая, что SQL Patch NO_ADAPT_4g02xxnvrk8j9 применяется как стандартное средство отключения адаптивных фич на уровне запроса и не способен повлиять на отдельное выполнение, остаётся вопрос, что же замедлило выполнение запроса SQL_EXEC_ID = 18306846 ? SQL Monitoring Report ничего существенного [...]
Source: iusoltsev.wordpress.com
Вс, Июн 09, 2019

A recent post on the ODC General Database forum asked for an explanation of the AWR report values “%Total CPU” and “%Busy CPU” under the “Instance CPU” label, and how the “%Busy CPU “ could be greater than 100%. Here's a text reproduction of the relevant sample supplied:

Host [...]
Source: jonathanlewis.wordpress.com
Чт, Июн 06, 2019

A question came up on Oracle-l a few days ago about how Oracle calculates costs for a scalar subquery in the select list. The question included an example to explain the point of the question. I've reproduced the test below, with the output from an 18.3 test system. The [...]

Source: jonathanlewis.wordpress.com
Пн, Июн 03, 2019

… and thanks for any Douglas Adams references.

I decided on Friday that after more than 31 years using the Oracle software and more than 37 years of being self-employed in the computer industry – not to mention a further 8 years being an enthusiastic amateur – the time had finally [...]

Source: jonathanlewis.wordpress.com
Пн, Июн 03, 2019

One of the small changes (and, potentially big but temporary, threats) in 18.3 is the status of the “ignore hints” parameter. It ceases to be a hidden (underscore) parameter so you can now officially set parameter optimizer_ignore_hints to true in the parameter file, or at the system level, or at [...]

Source: jonathanlewis.wordpress.com
Пн, Июн 03, 2019
В процессе анализа запросов удобно использовать SQL Monitor через процедуру DBMS_SQLTUNE.REPORT_SQL_MONITOR напрямую или через OEM/OCM/SQL Developer Полезно при этом иметь в виду некоторые "врождённые особенности отображения статистики выполнения запроса, как я полагаю, неизменные с версии 11g: 1) Если в процессе выполнения запрос, например, через PL/SQL функции вызывает рекурсивные запросы (классический [...]
Source: iusoltsev.wordpress.com
Чт, Май 30, 2019

You may know that table() (kokbf$ collection functions), xmltable and json_table functions block Join-Predicate PushDown(JPPD).

Simple example:

DDL create table xtest(a, b, c) as select mod(level,1000),level,rpad('x',100,'x') from dual connect by level0) 8 - access("A"="C"."A")

You can see that JPPD works fine in case of global temporary tables and, obviously, the first workaround is to [...]

Source: orasql.org
Ср, Май 29, 2019

[Editorial note: this is something I started writing in 2013, managed to complete in 2017, and still failed to publish. It should have been a follow-on to another posting on the oddities of timestamp manipulation.]

Just as national language support used to be, timestamps and time-related columns are still a [...]

Source: jonathanlewis.wordpress.com
Вт, Май 28, 2019

Today I wanted to give a link to the description of v$sql_hint.target_level to show that no_parallel can be specified for statement or object, and though it's pretty obvious, but surprisingly I haven't found any articles or posts about it, so this short post describes it.
v$sql_hint.target_level is a bitset, where
1st bit [...]

Source: orasql.org
Пн, Май 27, 2019

Last week I wrote a note about turning a range-partitioned table into a range/list composite partitioned table using features included in 12.2 of Oracle. But my example was really just an outline of the method and bypassed a number of the little extra problems you're likely to see in [...]

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

In yesterday's note on the options for converting a range-partioned table into a composite range/list parititioned table I mentioned that you could do this online with a single command in 18c, so here's some demonstration code to demonstrate that claim:

rem rem Script: [...]
Source: jonathanlewis.wordpress.com
Чт, Май 23, 2019

I wrote a short note a little while ago demonstrating how flexible Oracle 12.2 can be about physically rebuilding a table online to introduce or change the partitioning while discarding data, and so on. But what do you do (as a recent question on ODC asked) if [...]

Source: jonathanlewis.wordpress.com
Ср, Май 22, 2019

It shouldn't be possible to get the wrong results by using a hint – but hints are dangerous and the threat may be there if you don't know eaxactly what a hint is supposed to do (and don't check very carefully what has happened when you've used one that you're [...]

Source: jonathanlewis.wordpress.com
Вт, Май 21, 2019

A couple of weeks ago I published a note about an execution plan which showed the details of a scalar subquery in the wrong place (as far as the typical strategies for interpreting execution plans are concerned). In a footnote to the article I commented that Andy Sayer [...]

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