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

19 Февраль 2015
Earlier
Пт, Май 18, 2018

I've been prompted by a recent question on the ODC database forum to revisit a note I wrote nearly five years ago about bitmap join indexes and their failure to help with join cardinalities. At the time I made a couple of unsupported claims and suggestions without supplying [...]

Source: jonathanlewis.wordpress.com
Чт, Май 17, 2018
В докуметации Database Initialization Parameters for Oracle E-Business Suite Release 12 (Doc ID 396009.1) определённо(*) рекомендуется отключить адаптивные фичи: — как указано, чтобы использовать "чистый CBO, т.о. блокируя избыточную генерацию планов и общую нестабильность (см., например, 12c: адаптивная оптимизация и CBO), что и было давно выполнено на уровне системы: , [...]
Source: iusoltsev.wordpress.com
Пт, Май 11, 2018

If you've come across any references to the “index skip scan” operation for execution plans you've probably got some idea that this can appear when the number of distinct values for the first column (or columns – since you can skip multiple columns) is small. If so, what do you [...]

Source: jonathanlewis.wordpress.com
Вт, Май 08, 2018

If your system had to do a lot of distributed queries there's a limit on indexes that might affect performance: when deriving an execution plan for a distributed query the optimizer will consider a maximum of twenty indexes on each remote table. if you have any tables with a ridiculous [...]

Source: jonathanlewis.wordpress.com
Пн, Май 07, 2018

This is a reprint (of a reprint) of a note I wrote more than 11 years ago on my old website. I've decided to republish it on the blog simply because one day I'll probably decide to stop paying for the website given how old all the material is [...]

Source: jonathanlewis.wordpress.com
Пт, Май 04, 2018

A recent question on the ODC (OTN) database forum prompted me to point out that the optimizer doesn't consider function-based indexes on remote tables in distributed joins. I then spent 20 minutes trying to find the blog note where I had demonstrated this effect, or an entry in the [...]

Source: jonathanlewis.wordpress.com
Вт, Апр 10, 2018

No-one should be using exp/imp to export and import data any more, they should be using the datapump equivalents expdp/impdp – but if you're on an older (pre-12c) version of Oracle and still using exp/imp to do things like moving tables with their production statistics over to test systems then [...]

Source: jonathanlewis.wordpress.com
Пн, Апр 09, 2018

Here's a little-known feature that has been around since at least Oracle 10, though I don't think I had ever seen it in the wild until today when someone reported on the ODC (OTN) database forum that they had a problem getting repeatable results. It's always possible, of [...]

Source: jonathanlewis.wordpress.com
Пт, Апр 06, 2018

I've mentioned the SQL Monitor report from time to time as a very useful way of reviewing execution plans – the feature is automatically enabled by parallel execution and by queries that are expected to take more than a few seconds to complete, and the inherent overheads of monitoring are [...]

Source: jonathanlewis.wordpress.com
Ср, Мар 28, 2018

Every now and again I have to check what a particular lock (or enqueue) type is for and what the associated parameter values represent. This often means I have to think about the names of a couple of views and a collection of columns – then create a few column [...]

Source: jonathanlewis.wordpress.com
Вс, Мар 25, 2018
При выполнении ALTER TABLE DROP CONSTRAINT случился TM — library cache deadlock распределённого (RAC) вида, отразившийся в ASH следующим образом: где: 1) стартовые условия (session_id,session_serial#) in ((4460,16750)) относятся к сессии, выполняющей DDL sql_id='7kpzqksdg4wu8′ и ожидающей TM-блокировки на целевой пользовательской таблице MY.PAYMENT_Z, при этом столбец DATA_OBJECT_P1RAW отражает название ASH.CURRENT_OBJ#, либо приведённое [...]
Source: iusoltsev.wordpress.com
Ср, Мар 21, 2018
По причине ошибки ORA-00600: Internal Error Code, Arguments: [qmxtcsxmlt:xmltype] (Doc ID 2079499.1), характерной для использования функции XMLAgg в бд состояния MOUNTED (standby or non-standby), переписал скрипт ASH_IO_WAITS.SQL для поиска наиболее активных в WAIT_CLASS-ах ‘User I/O‘ и ‘System I/O‘ процессов/запросов на STANDBY и прочих MOUNTED системах Например, при замедлившемся накате standby [...]
Source: iusoltsev.wordpress.com
Пн, Мар 19, 2018

The partitioning option “partition by reference” is a very convenient option which keeps acquiring more cute little features, such as cascading truncates and cascading splits, as time passes – but what does it cost and would you use it if you don't really need to.

When reference partitioning came into existence [...]

Source: jonathanlewis.wordpress.com
Чт, Мар 15, 2018

I've recently been reminded of a blog post I wrote a couple of years ago that discussed the issue of running into the hard limit of 2^20 -1 as the number of segments for a (composite) partitioned table – a problem that could arise in a relatively short time [...]

Source: jonathanlewis.wordpress.com
Вт, Мар 13, 2018

I was going to write an article on the way 12.2 has introduced the option for “deferred invalidation” for a number of DDL operations, but I did a quick google search before I started writing and found that both Franck Pachot and Richard Foote (yes, rebuild index is one of [...]

Source: jonathanlewis.wordpress.com
Пн, Мар 12, 2018

It can be difficult to find the critical differences when comparing execution plans when you want to find out why the optimizer has changed its choice of plan and what may have happened to cause the change, and even the various diff_plan_xxx() functions in dbms_xplan don't help very much, so [...]

Source: jonathanlewis.wordpress.com
Пт, Мар 09, 2018
Всплеск нагрузки на IO подсистему: сопровождался, по нашим с Дмитрием Якубеней наблюдениям, дисбалансом в отчёте AWR — совершенно незначительные цифры пользовательской нагрузки (*): никак не коррелировали с Total (MB) нагрузкой AWR IO Profile (**): , основная часть которой была сосредоточена в районе Redo Read (***) Подходящее ожидание log file sequential [...]
Source: iusoltsev.wordpress.com
Чт, Мар 08, 2018

There's a question on the ODC database forum about column groups that throws up an interesting side point. The OP is looking at a query like the following and asking about which column groups might help the optimizer get the best plan:

select [...]
Source: jonathanlewis.wordpress.com
Вт, Мар 06, 2018

In my previous post I presented a warning about the potential cost of sorting and the cost of failing to find a match after each pass of a long search. In a comment on that post Stew Ashton reminded me that the cost of repeatedly trying to find [...]

Source: jonathanlewis.wordpress.com
Пт, Мар 02, 2018

Here's a note that has been sitting around for more than 3 years (the draft date is Jan 2015), waiting for me to finish it off; and in that time we've got a new version of Oracle that changes the solution to the problem it presented. (I also managed to [...]

Source: jonathanlewis.wordpress.com
Ср, Фев 28, 2018

It's the gift that keeps on giving – no matter how many problems you find there are always more waiting to be found. It's been some time since I last wrote about tables with more than 255 columns, and I said then that there was more to come. In [...]

Source: jonathanlewis.wordpress.com
Пн, Фев 26, 2018

In the spirit of Cary Millsap's comment: “The fastest way to do anything is to not do it at all”, here's my take (possibly not an original one) on solving problems:

“The best time to solve a problem is before it has happened.”

I spend quite a lot of my “non-contact” time [...]

Source: jonathanlewis.wordpress.com
Сб, Фев 24, 2018
В процессе тестирования с Максимом Филатовым Patch 20933264: OUT-OF-PLACE COMPLETE REFRESH NOT USING DIRECT LOAD IN POPULATING OUTSIDE TABLE наблюдали следующее воспроизводимое поведение: После чего SYS.SNAP_REFOP$ содержит три операции: — первая из которых (INSERT INTO "SCOTT.EMP_SNAPSHOT2″) кажется не очень подходящей для исправленной патчем опции out_of_place => TRUE Трейс 10046 показывает, [...]
Source: iusoltsev.wordpress.com
Чт, Фев 22, 2018

A useful quick summary from Neil Chandler replying to a thread on Oracle-L:

Topic: RAC install on Linux

You should always be using Hugepages.

They give a minor performance improvement and a significant memory saving in terms of the amount of memory needed to handle the pages – less Transaction Lookaside [...]

Source: jonathanlewis.wordpress.com
Чт, Фев 22, 2018
— живой и интересный! Несколько иллюстраций к рассказанному Александром: Список используемых/наблюдаемых в системе опций подсказки RESULT_CACHE: Franck Pachot. RESULT_CACHE hint expiration options — тесты недокументированных опций: SNAPSHOT — гарантированное /независимое от DML/ время хранения/валидности Result Cache SHELFLIFE — гарантированное время инвалидации кэша в секундах, логично применяемое для кэширования системных объектов [...]
Source: iusoltsev.wordpress.com
Ср, Фев 21, 2018

Assume you've got a huge temporary tablespace, there's plenty of space in your favourite tablespace, you've got a very boring, simple table you want to copy and partition, and no-one and nothing is using the system. Would you really expect a (fairly) ordinary “create table t2 as select * from [...]

Source: jonathanlewis.wordpress.com
Вт, Фев 20, 2018

[Originally written August 2015, but not previously published]

If I'm taking notes in a presentation that you're giving there are essentially four possible reasons:

You've said something interesting that I didn't know and I'm going to check it and think about the consequences You've said something that I knew but you've said it [...]
Source: jonathanlewis.wordpress.com
Вт, Фев 20, 2018

Here's a note I've just re-discovered – at the time I was probably planning to extend it into a longer article but I've decided to publish the condensed form straight away.

In a question to the Oak Table a couple of years ago (May 2015) Cary Millsap asked the following:

If [...]

Source: jonathanlewis.wordpress.com
Вт, Фев 20, 2018

As the years roll on I've found it harder and harder to supply quick answers to “simple” questions on the Oracle-L list server and OTN/ODC forum because things are constantly changing and an answer that may have been right the last time I checked could now be wrong. A simple [...]

Source: jonathanlewis.wordpress.com
Пн, Фев 19, 2018
DML Error Logging DBMS_ERRLOG

When you have an enormous load of data to copy/merge from one table to another, you are probably concerned about:

Performance: How fast can the program copy/merge all the data? Exception handling: How well does the program deal with [...]
Source: www.oratable.com
Комментирование отключено.