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

19 Февраль 2015
Earlier
Пн, Ноя 30, 2020

If you're like me there are probably lots of little things that you know but find terribly easy to forget. Here's one of my bêtes noires – starting with a little quiz:

rem rem Script: lob_in_row.sql rem [...]
Source: jonathanlewis.wordpress.com
Чт, Ноя 26, 2020

Several years ago I published a couple of examples of scripts that I'd been using since Oracle 6 (with minor changes over time) to analyse the content of tables in some detail. The first was a breakdown of the lengths of the rows in the table, the second [...]

Source: jonathanlewis.wordpress.com
Чт, Ноя 26, 2020

Many years ago Tom Kyte published a small PL/SQL procedure to output each row in a table as a list of (column_name , value). I often use the original version, even though Tom refined it several times over the years. From time to time the links in my blog [...]

Source: jonathanlewis.wordpress.com
Чт, Ноя 12, 2020

Here's a little demonstration of the benefit of rowsets. It started with a very simple question that arrived in my in-box from Kaley Crum.

“Why does the query with the UNION ALL take so much longer than the query without the UNION ALL?”

Here are the two queries – [...]

Source: jonathanlewis.wordpress.com
Ср, Ноя 11, 2020

This is one of those notes on the “thinking about the data / indexes” theme that I like to write; it's a draft I wrote over two and a half years ago that I've just rediscovered and polished slightly and refers to a “recent” question that came up on [...]

Source: jonathanlewis.wordpress.com
Вт, Ноя 10, 2020

Here's a fun little item that I've just rediscovered from 2016.

There's a fairly commonly cited query that aggregates and tabulates the first_time column from the v$log_history view by hour of day, reporting the hour across the page and the date down the page. It often appears in the responses to [...]

Source: jonathanlewis.wordpress.com
Пн, Ноя 09, 2020

Here's a lovely little mechanism new to Postgres 13 that can minimise sorting costs: the “incremental sort”. It would be nice to see it in Oracle as well as it could make an enormous difference to “fetch first N” queries.

The concept is simple – if a rowsource moving up [...]

Source: jonathanlewis.wordpress.com
Вт, Ноя 03, 2020

This note has has been sitting with the other 800 drafts since some time in May 2019, and started with a comment about following on from “a recent talk on how to engineer indexes properly”. Unfortunately I don't remember when I wrote it, or why it came about.I mention this [...]

Source: jonathanlewis.wordpress.com
Ср, Окт 21, 2020

Here's an odd little detail about the statistics of column groups. At first glance it's counter-intuitive but it's actually an “obvious” (once you've thought about it for a bit) consequence of the approximate_ndv() algorithm for gathering stats.

I'll present it as a question:

I have a table with two columns: [...]

Source: jonathanlewis.wordpress.com
Сб, Окт 10, 2020

Interval partitioning is a popular strategy for partitioning date-based data. It's an enhanced variant of range partitioning that allows you to define a starting partition and an interval that should be used to derive the high values for all subsequent partitions – and Oracle doesn't even have to create intervening [...]

Source: jonathanlewis.wordpress.com
Пт, Окт 09, 2020

If you've ever used subquery factoring (“with” subqueries or common table expressions (CTEs) as they are often called) then you're probably aware of the (undocumented) hints /*+ materialize */ , which forces Oracle to create a local temporary table to hold the result of the subquery for subsequent use, [...]

Source: jonathanlewis.wordpress.com
Чт, Окт 08, 2020

This is a little addendum to a note I wrote a couple of days ago about serial direct path reads and KO (fast object checkpoint) enqueue waits.

The original note was prompted by a problem where someone had set the hidden parameter “_serial_direct_read” to ‘always' because there were running 11g [...]

Source: jonathanlewis.wordpress.com
Вт, Окт 06, 2020

Here's a little puzzle, highlighting a “bug that's not a bug” that was “fixed but not fixed” some time in the 10.2 timeline. (If you want specifics about exactly when the fix became available they're in MOS – Bug 5099019 : DBMS_STATS DOESN'T COUNT LEAF_BLOCKS CORRECTLY.

Running 19.3.0.0, with the system [...]

Source: jonathanlewis.wordpress.com
Пн, Окт 05, 2020

Here's a note that I might have written once already – but I can't find it and I've just been reminded about what it (might have) said by a posting that came up on the Oracle database forum in the last few days.

The posting in question is asking why, [...]

Source: jonathanlewis.wordpress.com
Чт, Окт 01, 2020

There are a number of unexpected issues with the optimizer's treatment of the index fast full scan, the access path where Oracle ignores the structure of the B-tree and uses multiblock reads to do a brute-force segment scan as if the index were a “skinny table” with a few blocks [...]

Source: jonathanlewis.wordpress.com
Ср, Сен 30, 2020

Following the recent note I wrote about an enhancement to the optimizer's use of Bloom filters, I received a question by email asking about the use of Bloom filters in serial execution plans:

I'm having difficulty understanding the point of a Bloom filter when used in conjunction with a hash [...]

Source: jonathanlewis.wordpress.com
Вс, Сен 27, 2020
Если в версии 12.1 проблем с TO_NUMBER, агрегатными функциями и параллельным выполнением запроса не наблюдалось: SQL Monitoring Report: , то в версии 19.5 неожиданно стабильно получаем DOP Downgrade 100% (*) при том же плане выполнения (**) При этом строки ASH более длительного запроса с аналогичной проблемой показывают, что на этапе [...]
Source: iusoltsev.wordpress.com
Вт, Сен 15, 2020

You can add also any information from v$rtsm_sql_plan_monitor if needed

create or replace function px_session_info return varchar2 parallel_enable as vSID int; res varchar2(30); begin vSID:=userenv('sid'); select to_char(s.server_group,'fm000') ||'-'||to_char(s.server_set,'fm0000') [...]
Source: orasql.org
Ср, Сен 09, 2020

It's a common pattern of Oracle features that they start with various restrictions or limitations that disappear over time. This note is about an enhancement to Bloom filter processing that appeared in the 18.1 optimizer and, for some people, may be a good enough reason for upgrading to a newer [...]

Source: jonathanlewis.wordpress.com
Пн, Авг 31, 2020

This is a little lesson in trouble-shooting. It assumes you have the privilege to generate and edit trace files, and all I'm going to do is show how I worked out the answer to a fairly simple question that appeared recently on the Oracle Developer Community forum.

I have a [...]

Source: jonathanlewis.wordpress.com
Пт, Авг 28, 2020
По следам недавнего успешного "краудсорсингового решения проблемы в телеграм-группе @RuOUG Собственно, моей была только формулировка проблемы: а не сталкивался ли кто с нерабочим partition pruning в 19.5 при использовании в качестве предикатов значений функций? в 12.1 всё работало( Антон подтвердил, а Саян Малакшинов — тут же предложил рабочее решение: [xtender] [...]
Source: iusoltsev.wordpress.com
Пт, Авг 28, 2020

This is part 1 of a short reference note about the work load created by referential integrity constraints when you delete from a parent table. It was prompted by a question on the Oracle Developer Community forum about how to handle a very large delete from a table which [...]

Source: jonathanlewis.wordpress.com
Пн, Авг 24, 2020

Here's a problem with the “flashback versions” technology that showed up at the end of last week. There's a thread about it on the Oracle Developer community forum, and a chain of tweets that was my initial response to a twitter alert about it that Daniel Stein posted.

The [...]

Source: jonathanlewis.wordpress.com
Ср, Авг 19, 2020

I've written a couple of notes in the past about the problems of optimising queries with predicates of the form “or exists {subquery}”. A recent question on the Oracle Developer Community forum brought to my attention an improvement in this area in (very precisely) 12.2, as well [...]

Source: jonathanlewis.wordpress.com
Вт, Авг 18, 2020

This is one of those notes that I thought I'd written years ago. It answers two questions:

what can I do with my materialized view? why isn't the optimizer using my materialized view for query rewrite?

I've actually supplied an example of code to address the first question as a throwaway comment in [...]

Source: jonathanlewis.wordpress.com
Чт, Авг 06, 2020

The following description of a bug appeared on the Oracle Developer Community forum a little while ago – on an upgrade from 12c to 19c a query starting producing the wrong results on a simple call to the average() function. In fact it turned out to be a bug [...]

Source: jonathanlewis.wordpress.com
Ср, Июл 15, 2020

I've pointed out fairly frequently that if you're running Standard Edition but would like to take advantage of a few features of the Partitioning option then you might be able to do something appropriate with Partition Views (but I've just discovered while searching my blog for a suitable item to [...]

Source: jonathanlewis.wordpress.com
Пн, Июл 13, 2020

A question about the min/max index scan appeared on the Oracle Developer Community forum recently. The query supplied in the thread was a little odd – you might ask why anyone would run it as it stands – and I've modified it to make it even stranger to [...]

Source: jonathanlewis.wordpress.com
Вс, Июл 12, 2020

One of the recurrent questions on the Oracle Developer Commuity forum is:

What's the best way to delete millions of rows from a table?

There are an enormous number of relevant details that you need to know before you can give the “right” answer to this question, e.g.

Which version of Oracle Standard [...]
Source: jonathanlewis.wordpress.com
Пт, Июл 10, 2020

There's a notable change in the way the optimizer does cost and cardinality calculations for recursive subquery factoring that may make some of your execution plans change – with a massive impact on performance – as you upgrade to any version of Oracle from 12.2.0.1 onwards. The problem appeared [...]

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