Из книги SQL*Plus Definitive Guide

1. В *nix системах перед коннектом к базе данных можно прописать базу и параметры окружения при помощи утилиты oraenv. Она спрашивает дефолтовый сид подключения

oracle@v-dev-10102sl-2:~&> oraenv
ORACLE_SID = [db1] ? db1
oracle@v-dev-10102sl-2:~&.>

2. Синонимами команды HOST являются $ под Windows и ! под *nix

3. ? заменяет путь к ORACLE_HOME в non*nix средах, например
@?/rdbms/admin/utlxplanвыполнит скрипт по созданию таблицы. В *nix средах альтернативой этому является
@$ORACLE_HOME/rdbms/admin/utlxplan
4. Не следует использовать пароли в строке запуска sqlplus, т.к. их можно подсмотреть в линуксе. В строке подключения задавать пользователя и имя базы, вводя пароль ручками. При использовании easyconnect строка подключения //host/service_name задается в строке пароля (глюк десятки)

5. Если Sql*Plus переносит строку в результатах запроса, он вставляет после нее пустую. Это отключается SET RECSEP OFF

6. &&А сохраняет переменную А в буфер. После этого все обращения &&A и &A не запрашивают значения у пользователя

7. Значения переменных подстановок не запрашиваются в одиноко стоящей строке комментария и после REM. Внутри SQL и PL/SQL блоков &XXX будет запрашивать значение переменной.

8. Есть способ сохранить данные из SQL*Plus в формате Excel через html:

SET MARKUP HTML ON
SET TERMOUT OFF
SET FEEDBACK OFF
SPOOL current_employees.xls
SELECT employee_id,
employee_billing_rate employee_hire_date,
employee_name
FROM employee
WHERE employee_termination_date IS NULL;
SPOOL OFF

9. Есть команда сохраняющая настройки:
STORE SET original_settings REPLACE
SET ...
-- восстанавливаем настройки
@original_settings

10. Очень полезная вьюха DICTIONARY, содержит информацию о словаре данных Oracle.

11. Интересные способы реализации бранчинга в SQL*Plus:
- использовать refcursor-ы и bind-переменные
- использовать автогенерацию имени следующего скрипта (но только до 20 уровней вложенности)
- автогенерация кода в файл с последующим выполнением

12. Реализовать цикл можно при помощи рекурсивного вызова самого себя (но не более 20 уровней вложенности)

13. В Линуксе можно вернуть значение из скрипта в переменную при помощи передачи ее в EXIT
#!/bin/bash sqlplus -s gennick/secret << EOF
COLUMN tab_count NEW_VALUE table_count
SELECT COUNT(*) tab_count FROM user_all_tables;
EXIT table_count
EOF

let "tabcount = $?"
echo You have $tabcount tables.
или собирая весь вывод скрипта
#!/bin/bash tabcount=`sqlplus -s gennick/secret << EOF
SET PAGESIZE 0
SELECT COUNT(*) FROM user_all_tables;
EXIT
EOF`

echo You have $tabcount tables.

14. Узнать версию базы можно командой DEFINE _O_VERSION

15. При помощи переменной LOCAL в Windows можно устанавливать базу по-умолчанию для подключения:
SET LOCAL=prod
sqlplus gennick/secret
sqlplus gennick/secret@prod -- это одно и тоже
В Linux можно использовать переменную TWO_TASK

Трансформации в оптимизаторе

По мотивам статьи

Трансформации оптимизатора бывают heuristic и cost-based (появился в Oracle 10g). При трансформации Oracle старается уменьшить количество блоков запросов (через merge), уменишить количество передающихся из шага в шаг данных (применение фильтрации на ранних стадиях, правильный порядок соединений), убирание ненужных операций.

Типы трансформаций.

1.Subquery unnesting.
В 9-м оракле делалось всегда. Оптимизатор может сделать unnest многих запросов за исключением запросов:

• связанных с non-parents
• связанных по OR
• некоторые запросы ALL с несколькими условиями связи с NULL значениями

Существует 2 типа unnest:

• в inline-view
• merge с родительским запросом

Конвертация в родительский запрос дает возможность выбирать соединение между таблицами.

2. Join elimination
Убирает таблицу из родительского запроса, если она нигде не используется, например
SELECT emp.name, emp.salary
FROM emp, dep
WHERE emp.dep_id = dep.dep_id
преобразуется в запрос
SELECT emp.name, emp.salary
FROM emp
если в таблице создан foreign key и есть условие not null на колонке (или в запросе).

3. Filter predicate movearound
Предикат может передаваться в подзапрос, идти в родительский запрос, в соседний запрос.

4. Group pruning
Убирает лишние группировки, если они не используются во внешних запросах

5. Group by и Distinct view merging
Мержит во внешний запрос блок, содержащий group by или distinct. Такое преобразование позволяет не только использовать различные пути соединения таблиц, но и отложить группировку, что может уменьшить время выполнения запроса через уменьшение количества строк для аггрегации. С другой стороны при помощи агрегации и последующей фильтрации можно уменьшить количество строк для соединения, поэтому решение о такой трансформации COST BASED.

6. Join predicate pushdown
Позволяет соединяться между view и остальным запросом при помощи индексов и nested loops. Может применяться к mergable (group by, distinct) и nonmergable (union all, union, semi, anti, outerjoined) views.
Как дополнительная оптимизация, может пропасть group by, если мы отфильтруем все записи по полям group by.

7. Group by placement
Может делать аггрегацию пораньше или попозже. Применяется совместно с Group by view merging (Group by pullup). По человечески сделают в следующих релизах.

8. Join Factorization
Применяется для Union и Union All запросов. Если в каждой части запроса есть одинаковые таблицы в джойне, то они выносятся во внешний запрос, а UNION ALL/UNION проходит без лишних объединений.

9. Predicate PullUp
Поднимает предикаты фильтрации во внешний запрос. Дорогими для выполнения считаются предикаты фильтрации, содержащие функции, пользовательсие операторы, подзапросы.
В настоящее время работает если во внешнем запросе указан rownum <= ...

10. Set operator into join
Преобразовывает операторы minus и intersect к antijoin, innerjoin, semijoin

11. Or into UNION ALL
Недостатком может стать то, что предикат OR применяется после UNION ALL и можем привести к Cartesian (к сожалению авторы ничем не пояснили эту фразу)

Общее

Запрос для трансформации (построения эквивалентного запроса) поступает из парсера. После логической трансформации (по правилам и стоимости) уходит в физическую оптимизацию.
Трансформация происходит через последовательность применяемых друг за другом шагов (возможные шаги описаны выше).

Определение количества записей в индексе

Определить количество записей в индексе можно при помощи недокументированной функции
sys_op_lbid( OBJECT_ID,'L',rowid таблицы)

Полный скрипт с привязкой к таблицам можно посмотреть тут: Measuiring Index Efficiency in 9i (JL Comp)

Анализировать индексы необходимо для принятия решения о rebuild или coalese индекса. Если всего несколько блоков индекса полностью заполнены, а остальные сильно разряжены (например проводились массовые удаления) то индекс хороший кандидат на перестроение.

Перстраивать индексы будет полезно, если:
1. Уменьшится высота индекса (что достаточно маловероятно)
2. По индексу проходят большие range или FFS.

В остальных случаях перестройка индексов не приведет в увеличению производительности (а может и деградировать ее, например индекс с реверсивным ключом вставляется во все блоки индекса, после перестройки у нас не останется свободного места в блоках, что приведет к их делению).

Перестраивать индексы можно при помощи следующих команд:

alter index t1_i1 coalesce;
alter index t1_i1 rebuild;
alter index t1_i1 rebuild online;
coalesce - перепаковка индекса. Не блокирует, но генерит много редо. Перепаковывает блоки при помощи серии коротких транзакций (что может стать причиной snapshot too old)
rebuild - можно рассматривать как удаление и создание индекса заново. Требует блокировок. Требует 2х места. Зато селекты могут пользоваться индексом
rebuild online - требуют блокировок только вначале и в конце (не проверял), добавляет row триггре на таблицу (для складирования изменений). Увеличивает потребность места на время перестройки.

PERFORMANCE TUNING WISDOM

Below are a list of traps, and some wisdom which may help you find a faster, or more accurate diagnosis.
• Don't confuse the symptom with the problem
(e.g. latch free wait event is a symptom, not the problem)
• No statistic is an island i.e. don’t rely on a single piece of evidence in isolation to make a diagnosis
(e.g. the buffer cache hit ratio can often be misleading; similarly with other rolled-up statistics)
• Don't jump to conclusions
• Don’t be sidetracked by irrelevant statistics (there are lots of them)
(out of the 255 V$SYSSTAT statistics, there are approximately 15 being useful for 99% of issues)
• If it isn’t broken, think twice before fixing it (or don’t fix it at all)
• Don’t be predisposed to finding problems you know the solutions to - this is known as the old favourite.
(i.e. The problem identified and evidence sought is related to a favourite issue encountered previously, for which there is a well known solution, rather than looking for the bottleneck. Usually, the required evidence will be found to support the preconception)
• Be wary of solutions that involve fixing a serious performance problem by applying a single, simple change.
(This usually involves setting an init.ora parameter, with _underscore parameters being popular. This type of solution is known as a silver bullet)
• Make changes to a system only after you are certain of the cause of the bottleneck
(If you do make changes hastily, in the worst case performance will degrade)
• Many times, modifying the application results in significantly larger and longer term performance gains, when compared to solutions based on tweaking init.ora parameters7 (rejection of this actuality is often accompanied by the search for a silver bullet solution)
• Removing one bottleneck may result in the dynamics of the instance changing (a good reason not to implement multiple changes at once), and hence the next bottleneck to be solved may not be the current second in the list
(с)

Rowsource execution statistics и dbms_xplan.display_cursor

Rowsource execution statistics собирается для запросов в следующих случаях:

• statistic_level = all (параметр инициализации)
• /*+ gather_plan_statistics */ в запросе
• _rowsource_execution_statistics = true (скрытый параметр)

статистику можно посмотреть во вьюхах:

• v$sql_plan_statistics
• v$sql_plan_statistics_all (дополнено использованием памяти)

Кроме того статистику можно посмотреть в планах запроса функцей dbms_xplan.display_cursor с параметром формата ALLSTATS LAST (RUNSTATS_LAST для 10.2)

dbms_xplan.display_cursor
Тема dbms_xplan.display_cursor полностью раскрыта тут

На Oracle 10.2.0.1 было проведено мини исследование, в результате которого родился скрипт для получения плана запроса

set echo off
set serveroutput off
set termout off
set feedback off
ALTER SESSION SET statistics_level = ALL;
SELECT * FROM (&1);
set termout on
select *
from table(dbms_xplan.display_cursor( null, null, 'RUNSTATS_LAST'))
/

set serveroutput on
set feedback on

Скрипт можно модифицировать по желанию.

Для запуска скрипта и получения плана пользователь необходимы привелегии на вьюхи: V$SQL_PLAN, V$SESSION, V$SQL_PLAN_STATISTICS_ALL (если указываем не стандарный параметр форматирования).

В источниках описаны следующие параметры форматирования (третий параметр функции):
BASIC показывает только объект и действие

EXPLAINED SQL STATEMENT:
------------------------
SELECT * FROM (select * from dual)

Plan hash value: 397561404

----------------------------------
| Id | Operation | Name |
----------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS FULL| DUAL |
----------------------------------

TYPICAL (по умолчанию) - BASIC + информация о кардинальности, байтах, предикатах, стоимости и т.д.


SQL_ID cp68bupvwmutb, child number 0
-------------------------------------
SELECT * FROM (select * from dual)

Plan hash value: 397561404

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------

ALL - добавляет к TYPICAL снизу табличку
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$F5BB74E1 / DUAL@SEL$2

Column Projection Information (identified by operation id):
-----------------------------------------------------------

1 - "DUAL"."DUMMY"[VARCHAR2,1]
с информацией об алиасах запросов, блоков, информацией о колонках. Пригодится при простановке хинтов в запросы.
ALLSTATS LAST (не работает в 10.1)
RUNSTATS_LAST - RUNSTATS_TOT (работает в 10.1 и 10.2) выводят информацию о выполнении курсора (последнее или общее). Требует Rowsource execution statistics (см. выше)
Отличаются только наличием колонки READS с информацией о физических чтениях.

IOSTATS (не работает в 10.1)- информация о вводе выводе (READS). Заодно выводит и всю остальную информацию.
select * from table(dbms_xplan.display_cursor( null, null, 'IOSTATS LAST'))
MEMSTATS (не работает в 10.1)- статистика об использовании запросом рабочих областей PGA (тоже можно получить по вьюхе v$slq_workarea)
select * from table(dbms_xplan.display_cursor( null, null, 'MEMSTATS LAST'))
Advanced, Outline - не испытывал, содрано у Льюиса

+NOTE (не работает в 10.1) - секция с примечаниями, например использовался ли при выполнении запроса dynamic_sampling или star_transformation.
select * from table(dbms_xplan.display_cursor( null, null, 'ALL +NOTE'));
+PEEKED_BINDS (не работает в 10.1) - с какими бинд-переменными получен данный план. Работает без Rowsource execution statistics

select * from table(dbms_xplan.display_cursor( null, null, 'ALL +PEEKED_BINDS'));
SQL_ID 0fks8359u5u12, child number 0
-------------------------------------
SELECT * FROM (select * from a where a=:a)

Plan hash value: 2248738933

-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3915 (100)|
|* 1 | TABLE ACCESS FULL| A | 49336 | 47M| 3915 (1)| 00:00:47
-------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$F5BB74E1 / A@SEL$2

Peeked Binds (identified by position):
--------------------------------------

1 - :A (NUMBER): 2

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("A"=:A)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

1 - "A"[NUMBER,22], "A"."PADDING"[VARCHAR2,2000]

Note
-----
- dynamic sampling used for this statement


PS: Как показала практика +PEEKED_BINDS не работает со сбором Rowsouce execution stat. В результатах показывается только информация о бинд переменных.