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

19 Февраль 2015
Earlier
Вт, Апр 29, 2025

For the Oracle performance tuning and troubleshooting Telegram channel https://t.me/ora_perf, I developed a simple helpful Telegram bot. It simplifies common Oracle database tasks directly within Telegram.

Here's what the bot can do:

FAQ Management /faq add: Reply with !faq add {description} to save a message. /faq list: Lists all [...]
Source: orasql.org
Пн, Мар 24, 2025
Summary

The “coalesce subquery” transformation introduced in the 11gR2 timeline can produce wrong results and you will find some bug reports and patches on MOS spread over versions from 12cR1 onwards. If you find that you are hitting a bug for which there appears to be no patch there are several [...]

Source: jonathanlewis.wordpress.com
Ср, Мар 19, 2025

This is just a reminder and a quick note about the Oracle error:

ORA-1652: unable to extend temp segment by 128 in tablespace {not the TEMP tablespace}

Since the named tablespace is not a temporary tablespace, the error has occured as a data sesgment was being created, rebuilt, moved, or was trying [...]

Source: jonathanlewis.wordpress.com
Пн, Мар 17, 2025

I responded to a problem on one of the Oracle forums recently about getting rid of some temporary segments that wouldn't disappear after a call to “alter table move …” command had failed, and in searching for a note I'd written about how smon handled this situation I came across [...]

Source: jonathanlewis.wordpress.com
Пн, Мар 17, 2025

There is a common misconception that partition pruning does not help in the case of global indexes and only works with local indexes or full table scans (FTS).

It is understandable how this misconception arose: indeed, when operations like PARTITION RANGE ITERATOR, PARTITION RANGE SINGLE, etc., appear in execution plans, partition [...]

Source: orasql.org
Пн, Фев 24, 2025

Here's a note that's been sitting as a draft for the last 7 years – finally dusted down, retested, and published. Following my new publishing paradigm, I'm going to tell you the conclusion at the start, then show the working that demonstrates the point.

Summary

When cloning a table, or subset of [...]

Source: jonathanlewis.wordpress.com
Сб, Янв 25, 2025
Начиная с 19 версии Partitioned Table Statistics Changed After Truncate Partition or Drop Partition Operation (Doc ID 2970098.1) On Oracle 19C, a partition tables statistics is changed, however all partitions statistics do not change. 10.3.3.2 Online Statistics Gathering for Partition Maintenance Operations Oracle Database provides analogous support for online statistics [...]
Source: iusoltsev.wordpress.com
Сб, Янв 25, 2025
Начиная с 19 версии Partitioned Table Statistics Changed After Truncate Partition or Drop Partition Operation (Doc ID 2970098.1) On Oracle 19C, a partition tables statistics is changed, however all partitions statistics do not change. 10.3.3.2 Online Statistics Gathering for Partition Maintenance Operations Oracle Database provides analogous support for online statistics [...]
Source: iusoltsev.wordpress.com
Вт, Янв 07, 2025

When the dbms_space_usage package came out reporting the “percentage free” space in ASSM blocks with ranges like 0-25%, 25%-50%, etc. with flags FS1 to FS4, I had great trouble remembering whether 0-25% was FS1 or FS4 and it was a strangely long time before I managed to get the obvious [...]

Source: jonathanlewis.wordpress.com
Пт, Дек 20, 2024

Forums, mailing lists, and StackOverflow are all great resources for Oracle performance discussions, but I've long thought it would be useful to have a dedicated online chat/group specifically for Oracle performance specialists. A place to share news, articles, and discuss performance issues. To test the waters, I've created a group: [...]

Source: orasql.org
Чт, Дек 19, 2024

In this part, I'll show how to implement Dynamic Range Segmentation (DRS) explained in the previous part using a custom Domain Index, allowing you to apply this optimization with minimal changes to your existing tables.

1. Creating the Function and Operator

First, we create a function that will be [...]

Source: orasql.org
Чт, Дек 19, 2024

In the previous part, I discussed the most efficient known methods for optimizing range queries. In this part, I'll introduce a simple version of my custom approach, which I call Dynamic Range Segmentation (DRS).

As explained earlier, a significant issue with conventional approaches is the lack of both [...]

Source: orasql.org
Чт, Дек 19, 2024

One of the most common and enduring challenges in database management is performing efficient interval searches, particularly for date intervals such as: WHERE :dt BETWEEN beg_date AND end_date.

In this series of articles, I will explore various strategies for optimizing such searches. We'll delve into well-known standard approaches, analyze their limitations, [...]

Source: orasql.org
Чт, Дек 12, 2024

Years ago, I had to solve the problem of cluster-wide contention for log table blocks during a massive number of inserts from numerous sessions.
Since the table was essentially insert-only and reads were extremely rare (only during investigations of issues), the obvious solution was for sessions from each cluster node to [...]

Source: orasql.org
Вт, Дек 10, 2024

I saw a recent tweet (on Bluesky) from SQLDaily highlighting a blog note that Lukas Eder wrote in 2016 with the title: “Avoid using COUNT() in SQL when you could use EXISTS()”. This is a good guideline because it probably maximises the options the optimizer has for producing [...]

Source: jonathanlewis.wordpress.com
Вт, Дек 10, 2024

Here's a cut-n-paste from a simple SQL*Plus session running under 19.11.0.0 (23.4 produces the same effects):

SQL> drop table t purge; Table dropped. SQL> create table t (id number, val number) rowdependencies; Table created. SQL> insert into t values(1,0); 1 row created. SQL> commit; Commit complete. SQL> insert into t values(2,0); 1 row created. SQL> update t set val = 1; 2 [...]
Source: jonathanlewis.wordpress.com
Чт, Дек 05, 2024

Part of my effort to clear my long backlog of drafts

This is a trivial script I wrote many years ago – the date on the draft (one of the oldest on my blog) was August 2009 – but the script itself must have started life in 8i if not earlier

All [...]

Source: jonathanlewis.wordpress.com
Чт, Ноя 28, 2024

Many years ago I wrote a short note to introduce the view v$sql_optimizer_env (and the session and system equivalents) which report the current values of optimizer related parameters (some of which are not found in the startup file) for a cursor child, or a session, or the system. I [...]

Source: jonathanlewis.wordpress.com
Вс, Ноя 24, 2024

What would Shakespeare say?

Q: So, Mr. Shakespeare, which RDBMS do you use to look up your “famous quotes”? A: My lords, I refer me to the Oracle.

Q: And how would you feel about migrating to SQL Server or DB2? A: A plague a' both your houses.

Q: So what is your basic requirement [...]
Source: jonathanlewis.wordpress.com
Пн, Окт 28, 2024

If you thought 1,000 columns was over the top for a table in Oracle, then you'll love 23ai which allows you to stretch that to 4,096 columns if you've set the parameter max_columns = extended in the spfile (or the pdb equivalent).

Naturally I was curious to see if there had [...]

Source: jonathanlewis.wordpress.com
Ср, Окт 16, 2024

This note is an observation of an odd little detail that's nice to know about the way that Unified Audit (see part 8 of Neil Chandler's series on Fundamental Security) handles deleting old data. I don't have a lot of background experience with Unified Audit, but I was surprised [...]

Source: jonathanlewis.wordpress.com
Сб, Окт 12, 2024

A question appeared on the Oracle developer forum recently (Oct 2024) that could be answered by a blog note that I started drafting about 18 months ago after answering a very similar question that had appeared in the same forum. Take a look at the following [...]

Source: jonathanlewis.wordpress.com
Ср, Окт 09, 2024

Some years ago I wrote a note describing how with a query using identical literal values in the predicate, the optimizer was able to produce exactly the same plan body with exactly the same Predicate Information section while producing different cardinality estimates depending on how you expressed the [...]

Source: jonathanlewis.wordpress.com
Вт, Сен 17, 2024

What could make a “create index” statement fail with an error about a table having too many columns (ORA-01792)? And why would this suddenly happen when the code that raised the error had been dropping and recreating the index every night for the best part of 3 years with [...]

Source: jonathanlewis.wordpress.com
Ср, Авг 21, 2024
После не очень удачной попытки фиксации очевидно более лучшего плана с помощью SQL Profile: , наблюдал странность отображения выполнения запроса (из генерируемого пакета типа XLA_00001_AAD_C_001234_PKG): и странность эта была не в том, что SQL Profile применился не ко всем выполнениям (что не соответствует ожидаемому рез-ту, конечно, но знакомо), а в [...]
Source: iusoltsev.wordpress.com
Пт, Авг 09, 2024

A recent MOS Community forum posting (needs an account) raised the problem of a query with a specific index hint switching between two plans, one using “inlist iteration of a unique index scan” the other using a simple “index range scan with filter predicate”. Here's an example of [...]

Source: jonathanlewis.wordpress.com
Ср, Авг 07, 2024

This is a small case study from one of the MOS Community Fora (needs a MOS account) that I drafted about a year ago. It started out as a question about a two node RAC system running Oracle 19.17 where an SQL Monitor report showed time spent in a [...]

Source: jonathanlewis.wordpress.com
Вс, Июл 28, 2024
, потребляющий заметную долю DB Time, может быть замечен на версиях 19c, описан ранее — Kun Sun. JDBC, Oracle object/collection, dbms_pickler, NOPARALLEL sys.type$ query: When using the JDBC Thin driver to invoke PL/SQL programs with Oracle objects / collections, we can observe dbms_pickler calls, and two NOPARALLEL hinted sys.type$ queries [...]
Source: iusoltsev.wordpress.com
Вс, Июл 28, 2024
1-я проблема sql parse возникла с запросом типа: Parsed short stack: повторяемо указывал на kkooqb / kkoiqb / kkeosl (**), что соответствует длинному циклу OR Expansion с инициализацией/оптимизацией нового Query Block для каждого элемента генерируемой конструкции UNION ALL — классическая и хорошо документированная в Advice for Reduction of Long Parse [...]
Source: iusoltsev.wordpress.com
Комментирование отключено.