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

19 Февраль 2015
Earlier
Вс, Июл 28, 2019
Обсуждая с Юрием Пудовченко длительный разбор/long parse запроса, обратили внимание на расхождения отображение длительности процесса parsing-а в отчётах tkprof и V$ACTIVE_SESSION_HISTORY, в то время как tkprof показывает: , данные ASH говорят о том, что в течение этих 14 секунд запрос был как бы одновременно IN_SQL_EXECUTION, IN_PARSE, IN_HARD_PARSE с нулевым SQL_EXEC_ID, [...]
Source: iusoltsev.wordpress.com
Пт, Июл 12, 2019

If you've been wondering why I resurrected my drafts on the opt_estimate() hint, a few weeks ago I received an email containing an example of a query where a couple of opt_estimate() hints were simply not working. The critical features of the example was that the basic structure of the [...]

Source: jonathanlewis.wordpress.com
Ср, Июл 10, 2019
Столкнулся с принципиальной невозможностью форсировать параллельное выполнение даже на этапе EXPLAIN PLAN: — т.е. текст запроса форсирует /*+ PARALLEL(8) */, а DBMS_XPLAN.DISPLAY использует DOP 1 because of hint (*) => когнитивный диссонанс detected) К счастью, ответ легко находится в частичных трейсах оптимизатора: Становится понятнее принципиальная невозможность создания параллельного индекса на [...]
Source: iusoltsev.wordpress.com
Вт, Июл 09, 2019
🇩🇪

Over the last few days I've been tweeting little extracts from Practical Oracle 8i, and one of the tweets contained the following quote:

Practical Oracle 8i p.29

"If you are going to depend on a technological feature of Oracle, you need to make sure that you have tried to break [...]

Source: jonathanlewis.wordpress.com
Пн, Июл 08, 2019
DETERMINISTIC functions in Oracle

PL/SQL functions in Oracle can be tagged with a DETERMINISTIC clause, to indicate that the function will always produce the same output for a given input and will have no side effects.

A little elaboration is in order here.

1. …will always produces the [...]
Source: www.oratable.com
Вс, Июл 07, 2019
Особенность первая Проблема с непараллельным выполнением казалось бы гарантированно захинтованного запроса, SQL Monitor которого показывал: (*) DOP Downgrade 100% и, как следствие/признак serial execution — отсутствующую секцию Parallel Execution Details (**) медленное выполнение по честному (без PX COORDINATOR FORCED SERIAL) параллельному плану одним процессом, однако с выраженным ожиданием выделения PX-процессов [...]
Source: iusoltsev.wordpress.com
Ср, Июл 03, 2019

A recent thread on the ODC SQL & PL/SQL forum raised the question of how data moves across a database link – is it row by row or array based (or other)? It's a good question as it's fairly common knowledge that distributed queries can be very much slower [...]

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

In the previous article in this series on the opt_estimate() hint I mentioned the “query_block” option for the hint. If you can identify a specify query block that becomes an “outline_leaf” in an execution plan (perhaps because you've deliberately given an query block name to an inline subquery and [...]

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

This is just a quick note to throw out a couple of of the lesser-known options for the opt_estimate() hint – and they may be variants that are likely to be most useful since they address a problem where the optimizer can produce consistently bad cardinality estimates. The first is [...]

Source: jonathanlewis.wordpress.com
Ср, Июн 26, 2019

Here's a question just in from Oracle-L that demonstrates the pain of assuming things work consistently when sometimes Oracle development hasn't quite finished a bug fix or enhancement. Here's the problem – which starts from the “scott.emp” table (which I'm not going to create in the code below):

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