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

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

A little while ago I added a post-script to a note I'd written five years ago about gathering stats on a virtual column and had updated with a reference to a problem on the Oracle database forum that complained that stats collection had taken much longer after the addition [...]

Source: jonathanlewis.wordpress.com
Пн, Сен 10, 2018

I wrote a note a couple of years ago explaining how I used to get a rough idea (with some errors) of how much time was spent in the overnight stats collection by each object. One of the nice little enhancements that appeared in 12c was the appearance of [...]

Source: jonathanlewis.wordpress.com
Ср, Сен 05, 2018

From time to time I've wanted to optimize a query by forcing Oracle to execute existence (or non-existence) subqueries in the correct order because I know which subquery will eliminate most data most efficiently, and it's always a good idea to look for ways to eliminate early. I've only just [...]

Source: jonathanlewis.wordpress.com
Ср, Сен 05, 2018
Джойны - это запросы, комбинирующие строки из нескольких таблиц, представлений или материализованных представлений, которые перечислены в секции FROM запроса.

INNER JOIN
Inner join или просто join возвращает только те строки из двух таблиц, которые удовлетворяют условию указанному в ON:

SELECT
s.id,
s.supplier_name,
[...]
Source: OracleForBeginners
Пн, Сен 03, 2018
REGEX to Split String into Rows

A typical scenario that involves splitting a comma-separated string into rows:

in a UI-driven query, the user would enter a list of IDs into an input box, and the application should retrieve details for the input list of IDs

The [...]

Source: www.oratable.com
Ср, Авг 29, 2018

My entrance at the Polish Oracle User Group conference 2017 has just resurfaced on Twitter. There is a back-story to this which contains an allegorical lesson in using Oracle. As I said in the opening remarks in the subsequent presentation: “The lesson began before I got to the stage”.

Like [...]

Source: jonathanlewis.wordpress.com
Пт, Авг 24, 2018

Error logging is a topic that I've mentioned a couple of times in the past, most recently as a follow-up in a discussion of the choices for copying a large volume of data from one table to another, but originally in an addendum about a little surprise you [...]

Source: jonathanlewis.wordpress.com
Ср, Авг 22, 2018

Following on from Monday's posting about reading execution plans and related information, I noticed a question on the ODC database forum asking about the difference between “in ({list of values})” and a list of “column = {constant}” predicates connected by OR. The answer to the question is that [...]

Source: jonathanlewis.wordpress.com
Пн, Авг 20, 2018

A recent thread on the Oracle developer community database forum raised a fairly typical question with a little twist. The basic question is “why is this (very simple) query slow on one system when it's much faster on another?” The little twist was that the OP was using “Streams [...]

Source: jonathanlewis.wordpress.com
Пт, Авг 17, 2018
Заметно замедлившайся рутинная операция обновления matview в версии Oracle 12.2 указывала, что выполняющая обновление сессия помимо необходимых и понятных операций DELETE+INSERT значительную часть времени проводила в дублирующих общую статистику DBMS_STATS подсчётах Cardinality всех подряд странного набора matview нашей базы: — при этом для некоторых matview Cardinality вычислялась с двойной точностью [...]
Source: iusoltsev.wordpress.com
Пт, Авг 17, 2018
Поскольку некоторые инструменты разработки генерируют связанные переменные вида :"1", :"2",.., а некоторые классы запросов могут по факту вытолнятся только на Readonly Standby, не присутствуя на Primary ни в каком виде, для фиксации/оптимизации плана бывает необходимо каким-то образом поместить такой запрос (sql_id) в Primary Shared Pool, что можно сделать стандартным способом [...]
Source: iusoltsev.wordpress.com
Пн, Авг 06, 2018
Post image for How to Install and Configure SQL*Plus Instant Client on Windows

GUI tools like TOAD and SQL Developer are all fine, but those who have cut their teeth on SQL*Plus will not do without the good old command-line interface. And so, a message like the one below [...]

Source: www.oratable.com
Чт, Авг 02, 2018

Following on from the previous posting which raised the idea of faking a frequency histogram for a column group (extended stats), this is just a brief demonstration of how you can do this. It's really only a minor variation of something I've published before, but it shows how you [...]

Source: jonathanlewis.wordpress.com
Ср, Авг 01, 2018

Today's little puzzle comes courtesy of the Oracle-L mailing list. A table has two columns (c2 and c3), which contain only the values ‘Y' and ‘N', with the following distribution:

select c2, c3, count(*) from t1 group by c2, c3 ; C C COUNT(*) - - ---------- N [...]
Source: jonathanlewis.wordpress.com
Пн, Июл 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
Сб, Июл 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
Комментирование отключено.