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

19 Февраль 2015
Earlier
Пн, Сен 12, 2022

In the past I've sometimes had to dump the contents of the redo log to a trace file when I needed to find out what work Oracle was doing behing the scenes. To minimise the volume dumped by the “alter system dump logfile” command and make it easier to find [...]

Source: jonathanlewis.wordpress.com
Пт, Сен 09, 2022

“Why did my query do parallel?”

It's a question that crops up from time to time, usually followed by a list of reasons why it shouldn't have gone parallel – no hints in the query, table is not declared parallel, parallel_degree_policy is set to manual etc.

When the question appeared recently [...]

Source: jonathanlewis.wordpress.com
Пт, Сен 02, 2022

If you want to do something about “wasted” space in an index what are the differences that you need to consider between the following three options (for the purposes of the article I'm ignoring “rebuild” and “rebuild online”):

alter index xxx coalesce; alter index xxx shrink space compact; alter index xxx shrink space;

Looking [...]

Source: jonathanlewis.wordpress.com
Пн, Авг 22, 2022

Here's a strange problem (with possible workaround) that appeared in a thread on the Oracle developer forum a couple of days ago. It looks like the sort of problem that might be a memory overflow problem in a rarely use code path, given that it seems to need a [...]

Source: jonathanlewis.wordpress.com
Пт, Авг 05, 2022

This note is about some testing I did on the consequences of the (new in 12c) “deferred global index maintenance” feature that Oracle introduced as a strategy to reduce the impact of dropping partitions from a partitioned table.

Looking at my notes I see that created my first test in August [...]

Source: jonathanlewis.wordpress.com
Вт, Июл 26, 2022

This is just a lightweight note on the risks of hinting (which might also apply occasionally to SQL Plan Baselines). I've just rediscovered a little script I wrote (or possibly last tested/edited) in 2007 with a solution to the problem of how to structure a query to use an [...]

Source: jonathanlewis.wordpress.com
Пт, Июл 22, 2022

Prompted by an unexpectedly high CPU usage on a hash join of two large dadta sets Stefan Koehler posted a poll on twitter recently asking for opinions on the following code fragment:

FROM TAB1 INNER JOIN TAB2 ON TAB1.COL1 = TAB2.COL1 AND TRIM(TAB1.COL3) > TRIM(TAB2.COL3)

While [...]

Source: jonathanlewis.wordpress.com
Чт, Июл 21, 2022

There's a thread on the MOSC database tuning forum (needs an account) at the moment asking why a “fetch first N” query to fetch next 41 rows with an offset of 8602 rows takes longer to run than the same query when the offset is zero rows. Here's [...]

Source: jonathanlewis.wordpress.com
Ср, Июл 20, 2022

There's a thread on the Oracle developer forum at present asking why calls to dbms_lob.getlength() and calls to dbms_space.space_usage() produce such different results for the storage used by a LOB column.

It's a really good question to demonstrate two points. First that it's hard to supply all the details that [...]

Source: jonathanlewis.wordpress.com
Пн, Июл 18, 2022

A recent thread on the MOS database admin forum (needs an account) demonstrated a number of little issues with bugs, debugging, complex syntax, and the never-ending list of “not quite complete” code that shows up when features (in this case LOBs and partitioning) collide.

It's a silly little thing, but [...]

Source: jonathanlewis.wordpress.com
Пт, Июл 15, 2022

This is a little case study on working around a problem caused by a 3rd-party application that couldn't be changed quickly and easily. There's a touch of irony in this case as the problem I'll address is a side effect of the best possible workaround to a more generic design [...]

Source: jonathanlewis.wordpress.com
Пт, Июл 08, 2022

This is the follow-up to an initial post that covered some details of using the errorstack and ksq traces as and aid to finding the cause of an intermittent ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired. We were (hypothetically) looking at a scenario where a [...]

Source: jonathanlewis.wordpress.com
Ср, Июл 06, 2022

If you don't recognise the number the relevant extract from the oraus.msg file is:

00054, 00000, "resource busy and acquire with NOWAIT specified or timeout expired" // *Cause: Interested resource is busy. // *Action: Retry if necessary or increase timeout.

The error is typically the result of application code trying to do some [...]

Source: jonathanlewis.wordpress.com
Пн, Июн 20, 2022

This is another little “case study” on solving a problem. It's based on a question on the MOSC DBA Forum (needs an account) about trying to drop a tablespace and seeing the error “ORA-29857: domain indexes and/or secondary objects exist in the tablespace” when (apparently) there are no domain [...]

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

The question “How do you trouble-shoot a performance problem” came up in an online session I did for the AIOUG (All-India OUG) today. It's a very broad question and the only possible answers are either extremely generic, or very specific – so here's a specific example that I talked [...]

Source: jonathanlewis.wordpress.com
Вт, Июн 07, 2022
Некоторое время назад коллега-разработчик Рустам Кучукбаев обратил внимание на проблему, для которой мне удалось подготовить и выложить на Oracle Live SQL (для эффективного общения с поддержкой) до сих пор доступный тесткейс Кратко: , использование конструкции TABLE() (в любом месте запроса) в общем случае совместимо с использованием Join Predicate Push-Down (JPPD) [...]
Source: iusoltsev.wordpress.com
Пт, Июн 03, 2022
Отличная новость — уважаемые коллеги из компании ФОРС Дистрибуция подготовили и открыли общедоступный форум: "…ФОРС Дистрибуция открывает форум под названием ORA-600 для общения специалистов по проектированию, программированию и эксплуатации систем управления базами данных, и, в первую очередь – СУБД Oracle Цели нашего форума: – поддержать контакты специалистов и их профессиональные [...]
Source: iusoltsev.wordpress.com
Пт, Июн 03, 2022
DBA_HIST_SQLSTAT показывал неудачное применение SQL Profile ftd8uxp52atxc_171718292, который, как следует из названия был создан для фиксации PHV=171718292 при выполнении запроса sql_id='ftd8uxp52atxc': Отличие целевого плана PHV=171718292 от данного в ощущениях PHV=4038921846 оказалось более чем значительным: Интересно найти причину нестабильного поведения: dbms_xplan(format => ‘hint_report') лишь констатирует проблему: trace 10053 даёт определённый ключ: [...]
Source: iusoltsev.wordpress.com
Вт, Май 31, 2022

I've written a couple of notes about deleting from join views and (ultimately) the fact that if you have muliple key-preserved tables in join view then the first key preserved table in the from clause is the one where the delete is applied. The topic came up on the [...]

Source: jonathanlewis.wordpress.com
Пт, Май 20, 2022

It's a long time since I've done a quiz night – but here's something that fooled me (briefly) when it appeared on the Oracle Developers' Forum. Here's a table definition – and I'm not going to make it easy by giving you a “create table” statement, but it's just a [...]

Source: jonathanlewis.wordpress.com
Чт, Май 05, 2022

This note is about a surprising performance difference between the lead() and lag() analytic functions (which turns out to be due to the behaviour of the nth_value() function) when the option to “ignore nulls” is included in their use (jump to conclusion). The detail I'll be writing about was [...]

Source: jonathanlewis.wordpress.com
Ср, Май 04, 2022

Here's a note about a data error generated by using (possibly mis-using) the dbms_redefinition package. The original code to demonstrate the problem comes from a note on the Oracle Developer forum, and was brought to my attention by a tweet from Daniel Stein.

The critical feature of the demo [...]

Source: jonathanlewis.wordpress.com
Ср, Апр 27, 2022
Коллега Сергей Щукин столкнулся: и идентифицировал опцию COMPRESS (BASIC|FOR OLTP|ROW STORE COMPRESS ADVANCED) как определяющую, опции ONLINE и PARALLEL не влияют на ошибку Специалисты поддержки Oracle предложили проверить, как оказалось, действенный workaround: , однако положительный feedback донести не удалось по причине безвременно прерванных коммуникаций и, как следствие, описание ошибки на [...]
Source: iusoltsev.wordpress.com
Ср, Апр 13, 2022

There's a question on the Oracle Forums at the moment about a query that's taking a long time to parse. Even after being reduced to one prebuilt (currently remote) table with two non-correlated outer joins to it the parse time is several hundred seconds. This seems fairly bizarre – I [...]

Source: jonathanlewis.wordpress.com
Пн, Апр 11, 2022

Sometimes wishes come true and in 19c – with fix_control QKSFM_DBMS_STATS_27268249 – one of mine did. The description of this fix (which is enabled by default) is: “use approximate ndv for computing leaf blocks and distinct keys”.

Here's a key item in the output file from running tkprof [...]

Source: jonathanlewis.wordpress.com
Чт, Апр 07, 2022

This is a simple script that I wrote more than 20 years ago (for 8.1.7.4 apparently) to get the session wait event figures from parallel query slaves as they were running. I'm posting it now because there's a question on the Oracle Database Forum where it would be useful as [...]

Source: jonathanlewis.wordpress.com
Чт, Апр 07, 2022

This is a simple query that I wrote more than 20 years ago (for 8.1.7.4 apparently) to get the session activity from parallel query slaves as they were running. I'm posting it now because there's a question on the Oracle Database Forum where it would be useful as a way [...]

Source: jonathanlewis.wordpress.com
Пн, Апр 04, 2022

I don't suppose many people edit and compile PL/SQL code from the SQL*Plus command line any more, but the following reminder about avoiding mistakes is probably valid in other development environments even though it may be enabled through a difference mechanism.

You can set the level of warning that you get [...]

Source: jonathanlewis.wordpress.com
Пн, Мар 28, 2022

In the previous note about a problem dropping virtual columns the “guilty party” that made it impossible to drop any columns was based on a complex data type owned by the MDSYS (Spatial) schema. This note demonstrates the same problem with a very simple example created from scratch in an [...]

Source: jonathanlewis.wordpress.com
Чт, Мар 24, 2022

Here's a problem that appeared recently on the Orace Developer forum showing one of the classic symptons of new features namely that “mix and match” often runs into problems. This example has been a long time coming so “new” is something of a misnomer, but the alternative source of unlucky [...]

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