SQL Tuning: practice and annotations

TM enqueue with mode=3

2011-08-23T20:56:00.000+03:00

There is yet another case when INSERT statement can be blocked. According to this Oracle doesn`t acquire table locks on the child table during insert into parent table. But I`ve discovered something else. Suppose you have a MASTER table and a DETAIL table with UNINDEXED foreign key constraint which references the MASTER table. Foreign key has been created with ON DELETE CASCADE option. And there

Hint no_query_transformation in select statement with union

2011-07-27T10:45:00.000+03:00

Recently I have worked on huge sql query which was made up from several unioned subqueries. My colleagues and I managed to parallelise job of tuning that query. Each of us worked on a single subquery. After we had finished I collected all rewritten subquery and tried to run the whole sql. But something went wrong. It seemed that some of subqueries changed its plans after they had been united into

Has FIRST ROW optimization taken place

2011-01-17T11:42:00.000+02:00

How to identify does the CBO use FIRST ROW optimization for a given query? I ran into this question several days ago when I was tuning a pagination query. I received a plan with COUNT STOPKEY operator which was right above VIEW operator. ------------------------------------------------------------------------------------------------------------ | Id | Operation | Name

Quick search of queries without binds

2010-12-15T16:15:00.000+02:00

Reading the Oracle Performance tuning guide put in my head an idea about how to find queries which are used literals instead of binds. We can use fact that such queries usually share their plans. SQL> set pages 9999 SQL> set lines 140 SQL> column sql_text format a80 SQL> select plan_hash_value, sql_text, sql_id, executions 2 from v$sql x 3 where plan_hash_value in (select

Preparing a tablespace to become read only

2010-10-08T13:29:00.000+03:00

Now I am reading the Oracle Database Administrator's Guide 11g Release 2 (11.2). And I have found some interesting detail here. That chapter says about preparing a tablespace to move in readonly state. And it proposes to take some action for doing blocks cleanout. But documentation suggests using something like this "select count(*) from table". It is not enough! Make sure Oracle really scans

DML through indexes

2010-05-31T11:28:00.002+03:00

Recently I have been rereading Effective Oracle by Design. And I realized something new for myself. Inter alia I reread "Modification DML from Start to Finish" section: In general, these statement have a query component... In general, a modification statement will find data it needs to update using the consistent-read mechanism described in the previous section. Then it will get that information

ORA-08006

2010-03-06T13:30:00.002+02:00

I have observed a lack information about ORA-08006 in world wide web. I am want to show yet another one case with situation that produces such error. You can trap the ORA-08006 if your code meet following conditions: you have got some kind of ETL engine and it work pattern is: insert row if it new (exists in source and does not exist in target), update row if it already exists and delete row in

Data generator

2009-12-02T21:49:00.004+02:00

Yesterday I had fallen to new issue with bad performance of "reusable code". It was some piece of code seemed to be very elegant from programmer point of view.Complicated select statement contained the some kind of "data generator" as inline view like following:select add_months(:p_begin_date, level - 1) dt from dualconnect by level < months_between(:p_end_date, :p_begin_date) + 2This subquery

Reusable program code

2009-11-28T22:41:00.003+02:00

I had finished the first stage of highly intensive performance tuning project yesterday. I had found the problem that is very illustrative for statement about "poor effectiveness of universal program code". In my case it was reusable code of "individual calculations" that have been reused in "mass calculations". This is mistaken itself. But another effect had took place in the run time. It was

Parameter File Search Order in Oracle RAC

2009-11-19T14:53:00.010+02:00

It is still not documented or rather poorly documented thing about "Parameter File Search Order in Oracle RAC". Even documentation release 11.2 continue to assert the following:Oracle Database searches for your parameter file in a particular order depending on your platform.On Linux and UNIX platforms, the search order is as follows: 1. $ORACLE_HOME/dbs/spfilesid.ora 2. $ORACLE_HOME/dbs/

DBMS_XPLAN.DISPLAY_CURSOR for RAC database.

2009-10-09T14:40:00.004+03:00

I had the two-node Oracle RAC till recently. And it was sufficient to use the dbms_xplan.display_cursor function to examine the query execution plans with run-time statistics with connection to required instance.However, recently my oracle databases have migrated onto new hardware with eight nodes. It is become inconveniently to use dbms_xplan.display_cursor for examination of all instances and

Представления словаря данных

2009-05-29T10:45:00.025+03:00

Преамбула: когда-то давно, лет пять назад, я стоял у истоков одной большой информационной системы, был ее архитектором. Сейчас занимаюсь поддержкой и развитем этой системы. В основе выбранной мною архитектуры лежало понятие изолированных подсистем, каждая подсистема находится в отдельной схеме базы данных. Экземпляр системы состоит из набора схем, имеющих дозированный доступ друг к другу. Кроме

The use of statistically incorrect assumptions when using this feature can lead to incorrect or undesirable results

2009-01-01T20:47:00.021+02:00

В описании sample_clause для select statement имеется такое предупреждение:Caution:The use of statistically incorrect assumptions when using this feature can lead to incorrect or undesirable results.Чем это может "грозить"?Всем известный dbms_stats широко использует sample_clause при вычислении статистических показателей. При использовании параметров сбора статистики по умолчанию возможны

Древовидные справочники в качестве таблиц-размерностей или анализ “что-если” применительно к “TUNING BY CARDINALITY FEEDBACK METHOD”

2008-12-28T20:46:00.008+02:00

Недавно консультировал коллегу по настройке запроса. Суть решаемой запросом задачи состояла в следующем: таблица фактов (далее facts) связана с таблицей размерности (далее dic$territory), а та в свою очередь, являлась древовидным справочником административно-территориального устройства. Интересующий моего коллегу запрос должен был выдавать некоторые суммарные значения по таблице facts,

Работаем с BLOB

2008-11-16T20:12:00.035+02:00

Недавно принимал участие в решении проблемы низкой производительности PL\SQL приложения. Анализ файла трассировки 10046 навел на мысль о том, что в основном код работает с BLOB-данными. Разработчики подтвердили это предположение. Соответствующий фрагмент отчета tkprof приведен ниже:declare a blob;begin a:=PrintActiveContragent(7720);end;call count cpu elapsed disk query

Неудачные значения при bind peeking.

2008-10-28T22:06:00.048+02:00

Проводя ежедневный осмотр моей боевой БД, обнаружил долгоиграющий запрос. На 3 запуска более 80000 секунд времени исполняния (по данным v$sql). Естественно, не дожидаясь отклика "благодарных" пользователей начал исследовать это явление.Промышленная база версии 10.2.0.4.Для иллюстрации буду использовать вот такие тестовые данные:create table t1 as select * from all_objects;create table t2 as

Предикат из чек-констрейнта

2008-06-25T14:42:00.004+03:00

Еще одна, очевидно, ошибка, связанная со сгенеренными предикатами.drop table ch1;create table ch1 (f1 number(1) constraint ch1_ck_f1 check (f1 in ('1','2','3')));insert into ch1 values (1);insert into ch1 values (2);insert into ch1 values (3);insert into ch1 values (4);commit;exec dbms_stats.gather_table_stats(ownname=>null,tabname=>'ch1')select * from ch1 where f1=1;explain plan for select *

Tuning by Cardinality Feedback в действии

2008-06-25T11:14:00.011+03:00

В свое время, статья Tuning by Cardinality Feedback произвеля на меня неизгладимое впечатление.И вот теперь случилось применить ее в одном интересном случае. Вот такой план имел место быть на промышленной площадке.Сразу же бросается в глаза серъезное расхождение между прогнозом оптимизатора и статистикой времени исполнения. Первая стадия анализа привела к фрагменту плана, в котором, как мне

Этот замечательный инструмент SQLTXPLAIN

2008-06-17T22:00:00.010+03:00

В ноте 215187.1 среди прочего упоминается замечательный инструмент SQLTXPLAIN - незаменимая штуковина для аналитика.После нескольких запусков я обнаружил маленькую неприятную деталь при использовании режима XTRACT: ORA-07445: caught exception [ACCESS_VIOLATION] at [_msqsub()+23].Проверял на версии 10.2.0.1 (Linux 32 bit), а также на 11.1.0.6 (Win32).Похоже, что ошибка возникает в процессе

SQL Profile - some habits

2008-03-08T15:02:00.004+02:00

Click here for access scripts and results of experiments with SQL Tuning Task.This is a supplementary for my questions in Jonathan Lewis`s article "SQL Profiles - 2"

Задача о поиске свободного слота (дискуссия программистов, работающих с MySQL и Oracle)

2007-09-10T10:01:00.001+03:00

Решение задачи возможно с использованием двух подходов: 1) «универсальный» или «независимый от платформы» (а, скорее, продиктованый возможностями, например, MySQL); 2) с использованием возможностей конкретной СУБД (в данном случае, естесственно, речь пойдет об Oracle).Исходные данные:SQL> select * from v$version;BANNER----------------------------------------------------------------Oracle Database

Стоимость Row Level Security

2007-08-14T14:09:00.001+03:00

Комментируя статью, я подумал о том, что хорошо бы попытаться простым экспериментом оценить стоимость использования, так сказать, Row Level Security (RLS, DBMS_RLS). Причем, сделать оценку с точки зрения использования защелок (latch), поскольку, похоже, именно они являются причиной проблем на нагруженных многопользователями системах, использующих RLS (по крайней мере я с такими проблемами

Использование dynamic sampling с временными таблицами

2007-06-22T15:16:00.001+03:00

Следующая проблема - тоже из практики. Другой мой коллега написал здоровенную хранимую процедуру, делающую обширные вычисления неких социальных показателей. Она интенсивно использует временные таблицы для хранения промежуточных результатов, по мере ее работы в несколько временных таблиц постепенно вставляется по нескольку десятков тысяч строк. При этом, по ходу вычислений, временные таблицы по

Агрегаты в один проход

2007-06-20T14:45:00.001+03:00

Обратился недавно ко мне мой коллега за помощью по настройке запроса. Тот запрос состоял из двух частей: 1) tab2 - соединение 5 таблиц, таких соединений было два, объединенных оператором union, вся эта конструкция предоставляла исходные данные для агрегации; 2) tab1 - "синтетическая" таблица, поставляющая 17 строк - показателей агрегации. Указанные две части соединялись и к результату соединения