Полезные штуки для работы с ролями

Список привелегий можно получить из таблиц:
System_Privilege_Map - системные привелегии
Table_Privilege_Map - объектные привелегии

Список ролей в системе можно получить 2 способами: dba_roles, sys.user$

Список привелегий в сыром виде
sysauth$ - системные привелегии, роли
objauth$ - объектные привелегии

Список привелегий в обработанном виде
В обработанном виде, что бы посмотреть что чему грановано можно воспользоваться вьюхами: dba_tab_privs (All grants on objects in the database), dba_sys_privs (System privileges granted to users and roles), dba_role_privs (Roles granted to users and roles), dba_col_privs (All grants on columns in the database). Они представляют собой простую надстройку над сырыми таблицами, например:
create or replace view dba_tab_privs
(grantee, owner, table_name, grantor, privilege, grantable, hierarchy)
as
select ue.name, u.name, o.name, ur.name, tpm.name,
decode(mod(oa.option$,2), 1, 'YES', 'NO'),
decode(bitand(oa.option$,2), 2, 'YES', 'NO')
from sys.objauth$ oa, sys.obj$ o, sys.user$ u, sys.user$ ur, sys.user$ ue,
table_privilege_map tpm
where oa.obj# = o.obj#
and oa.grantor# = ur.user#
and oa.grantee# = ue.user#
and oa.col# is null
and oa.privilege# = tpm.privilege
and u.user# = o.owner#;

Как видно, в dba_tab_privs не попадают некоторые привелегии (col# IS NULL). В моей базе это привелегии
GRANT UPDATE(some_column) ON table

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

Вьюха dba_role_privs построена на табличке sysauth$:
select /*+ ordered */ decode(sa.grantee#, 1, 'PUBLIC', u1.name), u2.name,
decode(min(option$), 1, 'YES', 'NO'),
decode(min(u1.defrole), 0, 'NO', 1, 'YES',
2, decode(min(ud.role#),null,'NO','YES'),
3, decode(min(ud.role#),null,'YES','NO'), 'NO')
from sysauth$ sa, user$ u1, user$ u2, defrole$ ud
where sa.grantee#=ud.user#(+)
and sa.privilege#=ud.role#(+) and u1.user#=sa.grantee#
and u2.user#=sa.privilege#
group by decode(sa.grantee#,1,'PUBLIC',u1.name),u2.name;

Мои привелегии
Сужением указанных выше вьюх являются вьюхи:

• all_tab_privs - Grants on objects for which the user is the grantor, grantee, owner, or an enabled role or PUBLIC is the grantee
• all_col_privs - Grants on columns for which the user is the grantor, grantee, owner, or an enabled role or PUBLIC is the grantee

Обратить внимание, что вьюх с all_sys и all_role (как раз те, что строятся на sysauth$) нет

• user_tab_privs - Grants on objects for which the user is the owner, grantor or grantee. Как видно, от all_tab_privs отличается отсутствием PUBLIC
• user_col_privs - Grants on columns for which the user is the owner, grantor or grantee
• user_sys_privs - System privileges granted to current user
• user_role_privs - Roles granted to current user

Полученные и розданные привелегии
Можно получить из вьюх:

• all_col_privs_made - Grants on columns for which the user is owner or grantor
• all_col_privs_recd - Grants on columns for which the user, PUBLIC or enabled role is the grantee
• all_tab_privs_made - User's grants and grants on user's objects
• all_tab_privs_recd - Grants on objects for which the user, PUBLIC or enabled role is the grantee
• user_col_privs_made - All grants on columns of objects owned by the user
• user_col_privs_recd - Grants on columns for which the user is the grantee
• user_tab_privs_made - All grants on objects owned by the user
• user_tab_privs_recd - Grants on objects for which the user is the grantee

Для этого верно, что xxx_made + xxx_recd = xxx, например:
SELECT grantee, grantor, table_name FROM user_tab_privs
MINUS
SELECT grantee, grantor, table_name FROM user_tab_privs_made
MINUS
SELECT USER grantee, grantor, table_name FROM user_tab_privs_recd

Где посмотреть привелегии для текущей сессии:

• table_privileges - Grants on objects for which the user is the grantor, grantee, owner, or an enabled role or PUBLIC is the grantee. От all_tab_privs отличается только большим набором колонок.
• session_privs - Privileges which the user currently has set. Во вьюхе содержаться системные привелегии. Сделана на основе sys.v$enabledprivs
• session_roles - Roles which the user currently has enabled. Отличается от user_role_privs тем, что роли могут быть задизейблены или отключены, например:

SQL> SELECT COUNT(*) FROM User_Role_Privs;

COUNT(*)
----------
28
SQL> SELECT COUNT(*) FROM session_roles;

COUNT(*)
----------
28
SQL> CREATE OR REPLACE PROCEDURE p IS
2 n NUMBER;
3 BEGIN
4 SELECT COUNT(*) INTO n FROM User_Role_Privs;
5 dbms_output.put_line('User_Role_Privs Count = ' || n);
6
7 SELECT COUNT(*) INTO n FROM session_roles;
8 dbms_output.put_line('session_roles Count = ' || n);
9 END;
10 /

Procedure created
SQL> EXEC p

User_Role_Privs Count = 28
session_roles Count = 0

PL/SQL procedure successfully completed


• column_privileges - Grants on columns for which the user is the grantor, grantee, owner, or an enabled role or PUBLIC is the grantee. От all_col_privs отличается только большим набором колонок.

Вьюхи для ролей
За исключением dba_roles, dba_role_privs, user_role_privs, session_roles, описанных выше, можно отметить следующие вьюхи:

• role_role_privs - Roles which are granted to roles
• role_sys_privs - System privileges granted to roles
• role_tab_privs - Table privileges granted to roles

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

Различия между Oracle и ANSI синтаксисом

Для более логичной аргументации, почему не стоит использовать ANSI синтаксис (за исключением "Читать не удобно") в этом посте собираю, на что удастся наткнуться

1. Найдено тут:
В ANSI синтаксисе не работает трансформация запросов Primary Key-Foreign Key Table Elimination. Суть фичи: если в запросе указано несколько таблиц, связанных внешним ключом и из родительской таблицы строки не выбираются (а так же она не может ни убавить ни прибавить количество строк из запроса) -- в плане таблица не используется.

Немного о ремонте запросов

По мотивам статьи Troubleshooting Bad Execution Plans

Плохой запрос -- плохой (неправильный с нашей точки зрения) план. Причины плохого плана:

1. Неправильная статистика
2. Переустановленные параметры оптимизатора (optimizer_index_..., db_file_multiblock_read_count)

Как же его отремонтировать? Очень понравилось, почему не стоит ремонтировать запросы через глобальные параметры:

1. This is a global change to a local problem
2. Although it appears to solve one problem, it is unknown how many bad execution plans resulted from this change
3. The root cause of why the index plan was not chosen is unknown, just that tweaking parameters gave the desired result
4. Using non-default parameters makes it almost impossible to correctly and effectively troubleshoot the root cause

Что же делать? Ответ традиционен -- корректировать статистику. Причины, по которой статистика может быть неадекватной (кроме очевидной, что ее собирают неправильно или совсем не собирают):

1. Data skew: Is the NDV inaccurate due to data skew and a poor dbms_stats sample?
2. Data correlation: Are two or more predicates related to each other?
3. Out-of-range values: Is the predicate within the range of known values?
4. Use of functions in predicates: Is the 5% cardinality guess for functions accurate?
5. Stats gathering strategies: Is your stats gathering strategy yielding representative stats?

Советы по исправлению ошибок можно прочитать в самой статье.

PS: если нужно поставить параметр для отдельно взятого запроса, можно использовать хинт /*+ opt_param('optimizer_index_cost_adj', 100) */

Какие колонки подходят для битовых индексов

Вовсе не те, которые имеют маленькие NDV, а те, комбинации которых дают маленькую селективность. Проблема в том, что после обработки битовых индексов получаются коллекции из ROWID. Для получения строк из таблицы используются одноблочные чтения, что не совсем эффективно для непрогретого буфера данных (в этом случае фуллскан таблицы сработает быстрее).

Оптимизатор сам примет решение о FULLSCAN, если индексы дадут низкую селективность.

Подробности тут

Как запретить подключениек базе

На сервере в файле sqlnet.ora прописываем строки:

tcp.validnode_checking = yes
tcp.invited_nodes = (hostname1, hostname2) -- список разрешенных адресов
tcp.excluded_nodes = (192.168.10.1,localhost) -- список запрещенных адресов

Перезапускаем (reload не прокатывает) листенер и вуаля.

Небольшие тонкости:

1. Нет смысла указывать одновременно invited_nodes и excluded_nodes. Если указана invited_nodes, то всем хостам, там неуказанным подключение будет запрещено
2. Нельзя использовать wildcards
3. В список разрешенных желательно внести localhost :)
4. Все узлы должны быть записаны в одну строку.
5. invited_nodes имеет привелегии перед excluded_nodes

Примеры

Маслоу: Теория человеческой мотивации

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



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



"Может показаться, что иерархия пяти описанных нами групп
потребностей обозначает конкретную зависимость — стоит, мол,
удовлетворить одну потребность, как тут же ее место занимает другая.
Отсюда может последовать следующий ошибочный вывод — возникновение
потребности возможно только после стопроцентного удовлетворения
нижележащей потребности. На самом же деле, почти о любом здоровом
представителе нашего общества можно сказать, что он одновременно и
удовлетворен, и неудовлетворен во всех своих базовых потребностях"

Как написать в alert.log

Это можно сделать при помощи функции
dbms_system.ksdwrt(2,'Строка')


Первый параметр:
1 - Писать в лог сессии
2 - Писать в alert.log
3 - Писать в оба места

Подсмотрено у Льюиса

Еще один способ получить план

Event 10132 « Oracle Scratchpad
Хороший и простой способ получить план выполнения запроса. Нужно попробовать на 10.

На 11 для скрипта
column sid new_value a
select sid from sysp where rownum = 1;

alter session set events '10132 trace name context forever, level 1';

select * from sysp where sid = &a;

alter session set events '10132 trace name context forever, level 0';


Получены следующие результаты (обрезаны):

select * from sysp where sid = 116250109
sql_text_length=43
sql=
select * from sysp where sid = 116250109

----- Explain Plan Dump -----
----- Plan Table -----

============
Plan Table
============
-------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 3 | |
| 1 | INDEX UNIQUE SCAN | PK_SYSP | 1 | 25 | 2 | 00:00:01 |
-------------------------------------+-----------------------------------+
Predicate Information:
----------------------
1 - access("SID"=116250109)

Content of other_xml column


Из книги 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. В результатах показывается только информация о бинд переменных.

Сбор статистики и создание гистограмм

По материалам Тома Кайта
Если не указывать параметр method_opt, то Oracle соберет гистограммы в режиме auto. Количество баскетов в гистограмме в режиме auto определяется по использованию колонок в запросах в таблице SYS.COL_USAGE$ и распределению данных.

Данная таблица заполняется SMON (если установлен параметр _column_traking_level).

auto: сбора гистограмм также собирается указанием параметра method_opt => 'for all indexed columns size auto'

SIZE 1: при указании параметра method_opt=>'for all columns size 1' в гистограммы попадет бакет с наименьшим и наибольшим значением и общим количеством строк.

Для запрещения сбора гистограмм вообще, необходимо собирать статистику по таблице с параметром method_opt=>'for columns '

gather_stale - собирает гистограммы по таблицам, для которых данные бурно изменялись. Изменения определяются по вьюхам *_tab_modifications. Вьюхи автоматически заполняются при значении параметра STATISTICS_LEVEL = Typical или All.

Bind peeking и гистограммы тестирование

Тесты скриптом
DROP TABLE a;

CREATE TABLE a(a NUMBER, b CHAR);

CREATE INDEX ix_a ON a(a);

INSERT INTO a SELECT 3, 'x' FROM all_objects WHERE ROWNUM <= 1;
INSERT INTO a SELECT 2, 'x' FROM all_objects WHERE ROWNUM <= 1;

INSERT INTO a SELECT 1, 'x' FROM all_objects WHERE ROWNUM <= 1000;

--EXEC dbms_stats.gather_table_stats('sps', 'a', CASCADE => TRUE)
EXEC dbms_stats.gather_table_stats('sps', 'a', method_opt => 'FOR ALL INDEXED COLUMNS SIZE repeat', CASCADE => TRUE)
--EXEC dbms_stats.gather_table_stats('sps', 'a', method_opt => 'FOR ALL INDEXED COLUMNS SIZE 254', CASCADE => TRUE)

COMMIT;

var sa NUMBER
var ea NUMBER

EXEC :sa := 0;
EXEC :ea := 1;

ALTER SESSION SET tracefile_identifier = 'size_repeat';
ALTER SESSION SET EVENTS '10053 trace name context forever, level 1';
ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';

SELECT * FROM a WHERE a BETWEEN :sa AND :ea;

EXEC :sa := 2;
EXEC :ea := 3;

SELECT * FROM a WHERE a BETWEEN :sa AND :ea;

DISCONNECT;

Дали следующие результаты:

БЕЗ УКАЗАНИЯ ГИСТОГРАММ (size=auto)
1. Bind peeking во время hard parse: +
2. План первого запроса: индексный (неправильно)
3. Ожидаемая кардинальность 334 строки
4. Bind Peeking второго запроса: - (взял разобранный курсор)

ГИСТОГРАММЫ 254
1. Bind peeking во время hard parse: +
2. План первого запроса: полный (правильно)
3. Ожидаемая кардинальность 1000 строк
4. Bind Peeking второго запроса: - (взял разобранный курсор)

ГИСТОГРАММЫ repeat
(в документации не читал, но Кайт считает, что это без статистики )
1. Bind peeking во время hard parse: + (_|_)
2. План первого запроса: индексный (неправильно)
3. Ожидаемая кардинальность 334 строки
4. Bind Peeking второго запроса: -

Выводы:
1. Bind Peeking работает вне зависимости от наличия гистограмм
2. Bind Peeking работает только при Hard Parse. Заставить работать его просто так мы не можем.
3. Гистограммы помогли (при этом стоит учитывать, что количество уникальных значений меньше, чем количество бакетов)

bind peeking и гистограммы

Oracle обещает

There are two cases where the optimizer would peek at the actual bindings of a bind variable and where the actual bindings therefore could make a difference for what plan would get generated.
Range predicates. Example:
sales_date between :1 and :2 and price > :3.
Equality predicates when the column has histograms. Example:
order_status = :4
assuming that order_status has histograms.

Проверка показывает:
CREATE TABLE a(a NUMBER, b CHAR);

CREATE INDEX ix_a ON a(a);

INSERT INTO a SELECT 2, 'x' FROM all_objects WHERE ROWNUM <= 1;

INSERT INTO a SELECT 1, 'x' FROM all_objects WHERE ROWNUM <= 1000;

BEGIN
dbms_stats.gather_table_stats(
'sps',
'a',
CASCADE => TRUE,
method_opt => 'for all indexed columns size 50');
END;
/

COMMIT;

var a NUMBER

EXEC :a := 1;

ALTER SESSION SET tracefile_identifier = 'hist_test';
ALTER SESSION SET EVENTS '10053 trace name context forever, level 1';
ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';

SELECT * FROM a WHERE a = :a;

EXEC :a := 2;

SELECT * FROM a WHERE a = :a;

DISCONNECT;


По трейсу 10053 имеем только одно вхождение строки
*******************************************
Peeked values of the binds in SQL statement
*******************************************

и имеем следующие планы выполнения:
SELECT *
FROM
a WHERE a = :a


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.34 0.38 0 0 0 0
Fetch 11 0.00 0.00 0 16 0 1000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 13 0.34 0.38 0 16 0 1000

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 781

Rows Row Source Operation
------- ---------------------------------------------------
1000 TABLE ACCESS FULL A (cr=16 pr=0 pw=0 time=1054 us)


SELECT *
FROM
a WHERE a = :a

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 7 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 7 0 1

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 781

Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS FULL A (cr=7 pr=0 pw=0 time=128 us)

Результаты получаемы через explain plan:

SQL> SELECT * FROM a WHERE a = 2;

Execution Plan
----------------------------------------------------------

-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 2 (0)|
| 1 | TABLE ACCESS BY INDEX ROWID| A | 1 | 5 | 2 (0)|
| 2 | INDEX RANGE SCAN | IX_A | 1 | | 1 (0)|
-------------------------------------------------------------------------

Это показывает, что для значения переменной :a=2 никакого дополнительного считывания переменной не было и в помине.

Bind Peeking бывает только при hard parse, судя по всему не зависимо от наличия гистограмм

Для отключения bind peeking можно воспользоваться скрытым параметром

ALTER SESSION SET "_optim_peek_user_binds"=FALSE;

Разница планов tkprof и set autotrace

По мотивам блога Тома Кайта

По сути дела set autotrace не что иное, как встроенный в SQL*Plus Explain Plan For со всеми вытекающими отсюда последствиями.

Помимо набившей оскомину: "...ваша статистика могла поменяться между реальным выполнением и explain'ом" существуют следующие особенности Explain Plan:

1. Explain Plan делает hard parse, который потом никогда ничем не используется
2. Explain Plan ничего не умеет делать с Bind-переменными, ничего не знает про bind peeking. Все предположения делаются судя по всему из селективности, посчитанной по num distincts
3. Explain plan считает, что все bind переменные имеют строковое значение, поэтому может показывать неправильный план, ввиду отсутствия неявного преобразования типов.

TKPROF при первом выполнении запроса ВСЕГДА делает hard parse, после чего использует сгенерированный план.

TKPROF

1. Row Source Operation

Показывает, сколько строк ВЫШЛО из этой операции за ОДНО выполнение запроса. Подробнее

2. Настройка aggregate=YES собирает все выполненные запросы в одну кучку и агрегирует их результаты

3. Для определения размера коллекции для выборки (array fetch size) нужно поделить количество выбранных строк на количество fetch. Если количество fetch превышает количество строк, то происходит что-то подобное:
open
execute
fetch
fetch
fetch (а строчек уже и нету)

4. БАГ ДОКУМЕНТАЦИИ Табличка, начиная с 9.2. НЕ ВКЛЮЧАЕТ в себя все операции (+ рекурсивные SQL, например в триггерах). Но в документации написано:

In ch 10 of the Oracle 9i Performance Tuning Guide, it says:
"The resources reported for a statement include those for all of the SQL issued while the statement
was being processed. Therefore, they include any resources used within a trigger, along with the
resources used by any other recursive SQL (such as that used in space allocation). With the SQL
Trace facility enabled, TKPROF reports these resources twice. Avoid trying to tune the DML
statement if the resource is actually being consumed at a lower level of recursion."

Пример

5. CR, R, W в файлах и плане показывает число логических чтений на фазе выполнения и fetch данных. Не затрагивает фазу парсинга. Вывод этих чисел включается через
ALTER SYSTEM SET statistics_level=all(хотя это надо еще проверить)

Запросы дня (SHARED POOL, SGA, LIBRARY CACHE)

V$LATCH shows aggregate latch statistics for both parent and child latches, grouped by latch name. Individual parent and child latch statistics are broken down in the views V$LATCH_PARENT and V$LATCH_CHILDREN.

Режимы защелок
Есть два варианта действия в зависимости от типа защелки : немедленная установка, установка с ожиданием. (“willing-to-wait” и “no-wait” (= immediate)). Если процесс имеет возможность продолжать работу, не получив запрашиваемую защелку, то это запрос no-wait (например, redo copy latch). Если процесс не может продолжать работу, не получив запрашиваемую блокировку, то это режим willing-to-wait. Число попыток определяется параметром инициализации spin_count. Когда число повторений достигнет spin_count , процесс переходит в состояние ожидания. Через установленное время процесс активизируется, и процедура повторяется снова.
SELECT * FROM v$latch; - отражает всю деятельность библиотечного кэша с момента последнего запуска экземпляра.

Небольшая инфа тут http://www.oracle.com/global/ru/oramag/may2001/getdoc.html

SELECT * FROM v$librarycache; - развернутая информация по SGA

сумма free memory = сумме свободной памяти в shared pool - количество unpinned recreatable chunks of the shared pool LRU lists.

Узнать unpinned можно, например, дампом shared pool
alter session set events 'immediate trace name heapdump level 2';

Информация по SGA: SELECT * FROM V$sgastat;

This view displays database objects that are cached in the library cache. Objects include tables, indexes, clusters, synonym definitions, PL/SQL procedures and packages, and triggers: SELECT * FROM v$db_object_cache;

latch: shared pool

Во время выполнения запроса из отчета столкнулся с latch: shared pool. Стало немого непонятно, откуда оно берется. Оказалось, что

library cache latch is already held while requesting for shared pool latch. Shared pool latch is acquired to request/release space from the shared pool free memory area and released immediately after that. Request for library cache latch can never be made while holding the shared pool latch (at least up to 9i) as latch level semantics will prevent that ( as shared pool latch gets are always in willing-to-wait mode).

Дальнейшее исследование навело на сайт, где описывается похожая проблема. Г-н Адамс рекомендует на своей странице следующее:

Shared pool too big

? We have recently migrated to Oracle8i, but it seems the performance is quite slow. The machine is large in terms of disks, CPU, and memory. Also, we have moved to IPC since we have batch jobs running on the database, and no users connected over the network. Could you tell me what I should diagnose on the database and the Solaris 2.6 server to find the bottlenecks? A utlestat report is attached.

This is a classic case of too large a shared pool causing extreme latch contention on the shared pool latch. Note the following statistics from your report.txt .

CPU used by this session 11639.58 (seconds)
latch free 25353.58 (seconds)

That is, you are spending twice as long waiting for latches than doing useful work, and much of your CPU time would be consumed while spinning. Note also where those latch sleeps are ...

shared pool 4292776 (sleeps)
library cache 252949 (sleeps)

That is, 94% of these sleeps are on the shared pool latch. The rest are secondary sleeps on the library cache latches.

The problem is that shared pool latch is being held too long while searching the free lists, because the free lists are too long. The free lists are too long, because the shared pool is too big. You need to make appropriate use of DBMS_SHARED_POOL.KEEP to mark valuable objects for keeping, and reduce the size of your shared pool dramatically.

? Could it be that there is not enough space in the shared pool?

No, the ideal situation is to have a small shared pool in which all the important reusable objects are marked for keeping and in which other objects are recycled quickly. People often attempt to increase the shared pool under these circumstances rather than reducing it. Normally, it appears to have worked for a while, because it takes longer for the LRU lists to begin to be flushed. But once that happens, you immediately get longer free lists and worse contention for the shared pool latch than would previously have been the case.