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

19 Февраль 2015
Today
Пн, Июл 16, 2018

A recent (automatic ?) tweet from Connor McDonald highlighted an article he'd written a couple of years ago about an enhancement introduced in 12c that allowed for direct path inserts to index organized tables (IOTs). The article included a demonstration seemed to suggest that direct path loads to [...]

Source: jonathanlewis.wordpress.com
Earlier
Сб, Июл 14, 2018

Because it's been a long time since the last quiz night. Here's a question prompted by a recent thread on the ODevCom database forum – how many rows will Oracle sorts (assuming you have enough rows to start with in all_objects) for the final query, and how many sort [...]

Source: jonathanlewis.wordpress.com
Пт, Июл 13, 2018

I came across this odd limitation (maybe defect) with pushing predicates (join predicate push down) a few years ago that made a dramatic difference to a client query when fixed but managed to hide itself rather cunningly until you looked closely at what was going on. Searching my library for [...]

Source: jonathanlewis.wordpress.com
Чт, Июл 12, 2018

One of the difficulties of being a DBA and being required to solve performance problems is that you probably never have enough time to think about how you got to a solution and why the solution works; and if you don't learn about the process itself , you just don't [...]

Source: jonathanlewis.wordpress.com
Вт, Июл 10, 2018

A comment arrived yesterday on an earlier posting about an enhancement to the truncate command in 12c that raised the topic of what Oracle might do to validate a foreign key constraint. Despite being sure I had the answer written down somewhere (maybe on a client site or in [...]

Source: jonathanlewis.wordpress.com
Пн, Июл 09, 2018

If you want to examine historic object stats Oracle gives you a few procedures in the dbms_stats package to compare sets of stats captured at two different time periods, but there's no view that you can query to get an idea of how a table's stats have changed over time. [...]

Source: jonathanlewis.wordpress.com
Пт, Июл 06, 2018

It's that time of year again – the UKOUG Tech conference is approaching and I'm organising a panel session on the Cost Based Optimizer.

As usual I'll be asking Christian Antognini, Nigel Bayliss, Maria Colgan and a special guest panellist (maybe David Dimbleby will be able to find the time [...]

Source: jonathanlewis.wordpress.com
Пн, Июл 02, 2018

Here's another little note on the clustering_factor for an index and the table preference table_cached_blocks that can be set with a call to dbms_stats.set_table_prefs(). I might be repeating a point that someone made in a comment on an older posting but if that's the case I can't find the [...]

Source: jonathanlewis.wordpress.com
Пт, Июн 29, 2018

Connor McDonald produced a tweet yesterday linking to a short video he'd created about an enhancement to the truncate command in 12c. I data getting into the system.fffff you have referential integrity declared between a parent and child table then in 12c you can truncate the parent table and Oracle [...]

Source: jonathanlewis.wordpress.com
Вт, Июн 26, 2018

Saturday's posting about setting cursor_sharing to force reminded me about one of the critical limitations of SQL Profiles (which is one of those little reason why you shouldn't be hacking SQL Profiles as a substitute for SQL Plan Baselines). Here's a demo (taking advantage of some code that I think [...]

Source: jonathanlewis.wordpress.com
Сб, Июн 23, 2018

Prompted by a recent ODC (OTN) question I've just written up an example of one case where setting the cursor_sharing parameter to force doesn't work as you might expect. It's a specific example of what I believe is a theme that can appear in several different circumstances: if your [...]

Source: jonathanlewis.wordpress.com
Пт, Июн 15, 2018
Скачок Load Average на обеих нодах: high_LA_06062018.png был спровоцирован частым выполнением запроса со стабильным, но не очень удачным планом 4125831042: , причём относительно высокая доля ожиданий класса Concurrency — колонка CCWAITS_PER_EXEC (соответствующая DBA_HIST_SQLSTAT.CCWAIT_DELTA/EXECUTIONS_DELTA) относилась к рекурсивным (*) / вызываемым из используемой в запросе PL/SQL функции запросам(**), на что косвенно указывает [...]
Source: iusoltsev.wordpress.com
Пн, Июн 11, 2018

In a recent ODC thread someone had a piece of SQL that was calling dbms_random.string(‘U',20) to generate random values for a table of 100,000,000 rows. The thread was about how to handle the ORA-30009 error (not enough memory for operation) that is almost inevitable when you use the “select [...]

Source: jonathanlewis.wordpress.com
Пт, Июн 08, 2018

The question of how to delete 25 million rows from a table of one billion came up on the ODC database forum recently. With changes in the numbers of rows involved it's a question that keeps coming back and I wrote a short series for AllthingsOracle a couple [...]

Source: jonathanlewis.wordpress.com
Пт, Июн 01, 2018

There's always another hurdle to overcome. After I'd finished writing up the “index bouncy scan” as an efficient probing mechanism to find the combinations of the first two columns (both declared not null) of a very large index a follow-up question appeared almost immediately: “what if it's a partitioned [...]

Source: jonathanlewis.wordpress.com
Пт, Июн 01, 2018
Евгений Калинин обнаружил запрос, факт успешности выполнения которого неожиданно зависел от использования/неиспользования столбцов Inline view(*) а качестве параметров PLSQL_FUNCTION, и худшем случае завершавшийся простой ошибкой: , возникавшей на этапе построения плана, обрывая трассировку где-то в районе Query Transformation — в данном случае трейс заканчивался упоминанием OJE(Outer Join Elimination) Соответствующая трассировка [...]
Source: iusoltsev.wordpress.com
Чт, Май 31, 2018

Here's a nice little optimizer enhancement that appeared in 12.2 to make min/max range scans (and full scans) available in more circumstances. Rather than talk through it, here's a little demonstration:

rem rem Script: 122_minmax.sql rem Author: [...]
Source: jonathanlewis.wordpress.com
Ср, Май 30, 2018

This is a follow-up to a problem I had with yesterday's example of using recursive CTEs to “bounce” along a multi-column index to pick out the unique set of combinations of the first two columns. Part of the resulting query used a pair of aggregate scalar subqueries in a [...]

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

One of my maxims for Oracle performance is: “Don't try to be too clever.” Apart from the obvious reason that no-one else may be able to understand how to adjust your code if the requirements change at a future date, there's always the possibility that an Oracle upgrade will mean [...]

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

I wrote a note some time last year about taking advantage of the “index range scan (min/max)” operation in a PL/SQL loop to find the small number distinct values in a large single column index efficiently (for example an index that was not very efficient but existed to avoid [...]

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

A two-part question about the FILTER operation appeared on the Oracle-L list server yesterday. The first part was a fairly common question – one that's often prompted by the way the optimizer used to behave in older versions of Oracle. Paraphrased, it was: “Why is the total cost of [...]

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

Just a quick little tip for Friday afternoon.

If you use the “show parameter” or “show spparameter” commands from SQL*Plus you've probably noticed that the parameter value may be too long for the defined output column, and even the parameter name may occasionally be too long. For example (from 12.2.0.1):

SQL> show [...]
Source: jonathanlewis.wordpress.com
Чт, Май 24, 2018

Here's a detail I discovered a long time ago – and rediscovered very recently: it's possible to delete data from a table which is subject to audit without the delete being audited. I think the circumstances where it would matter are a little peculiar, and I've rarely seen systems that [...]

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