SQLNET.VALID_NODE_CHECKING

Фича, которая позволяет запрещать подключение клиентов через LISTENER.

Изначально имеем пустой файл sqlnet.ora на сервере:
> cat sqlnet.ora
>

С клиента у нас есть возможность подключиться к базе
C:>sqlplus sps/sps@v-pc-dev-3
SQL*Plus: Release 10.2.0.3.0 - Production on Sat Feb 27 20:36:28 2010
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Release 10.2.0.1.0 - Production
SQL>
Добавим в файл sqlnet.ora строки
tcp.validnode_checking=yes
tcp.excluded_nodes=(sphaera144)
и перезагружаем листенер командой
lsnrctl reloadПосле перезагрузки при подключении с клиента имеем:
C:\>sqlplus sps/sps@v-pc-dev-3
SQL*Plus: Release 10.2.0.3.0 - Production on Sat Feb 27 20:40:29 2010
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
ERROR:
ORA-12547: TNS:lost contact
Таким образом мы запретили подключаться к серверу с машины sphaera144. С остальных машин подключение разрешено.

ПРИМЕЧАНИЕ: если на сервере файла sqlnet.ora вообще не было и мы его создаем заново, то lsnrctl reload не хватает. Необходимо использовать lsnrctl stop/start.

Изменим строку в файле sqlnet.ora на сервере с tcp.excluded_nodes=(sphaera144) на tcp.invited_nodes=(sphaera144).
и перезапустим листенер lsnrctl reload.
Пробуем подключится с машины sphaera144
C:\>sqlplus sps/sps@v-pc-dev-3
SQL*Plus: Release 10.2.0.3.0 - Production on Sat Feb 27 20:45:43 2010
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Release 10.2.0.1.0 - ProductionС другой машины имеем
C:\>sqlplus sps/sps@v-pc-dev-3
SQL*Plus: Release 10.2.0.3.0 - Production on Sat Feb 27 20:40:29 2010
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
ERROR:
ORA-12547: TNS:lost contact

Подключение разрешено только с SPHAERA144. Со всех остальных машин подключение запрещено.

Теперь добавим обе строки:
tcp.invited_nodes=(sphaera144)
tcp.excluded_nodes=(sphaera144)
После подключения:
C:\>sqlplus sps/sps@v-pc-dev-3
SQL*Plus: Release 10.2.0.3.0 - Production on Sat Feb 27 20:45:43 2010
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Release 10.2.0.1.0 - Production
INVITED_NODES имеем преимущество перед EXCLUDED_NODES

выводы
1. Если нет файла sqlnet.ora на сервере, то listener необходимо перезагружать полностью. В остальных случаях хватает lsnrctl reload
2. При добавлении адреса в invited_nodes все остальные адреса запрещены.
3. invited_nodes имеет преимущество перед excluded_nodes -- подключиться можно только с тех адресов, которые разрешены.

Параметр OS_AUTHENT_PREFIX и подключение

Если приводить выдержку из документации:
OS_AUTHENT_PREFIX specifies a prefix that Oracle uses to authenticate users attempting to connect to the server. Oracle concatenates the value of this parameter to the beginning of the user's operating system account name and password. When a connection request is attempted, Oracle compares the prefixed username with Oracle usernames in the database.

Итак попробуем:

oracle@v-pc-dev-3:~> sqlplus /
ERROR:
ORA-01017: invalid username/password; logon denied

oracle@v-pc-dev-3:~> sqlplus / as sysdba
Connected to:
Oracle Database 10g Release 10.2.0.1.0 - Production

SQL> show parameter os_authent_prefix

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
os_authent_prefix string ops$

SQL> grant create session to ops$oracle identified by pass;
Grant succeeded.

SQL> exit
Disconnected from Oracle Database 10g Release 10.2.0.1.0 - Production

oracle@v-pc-dev-3:~> sqlplus /
Connected to:
Oracle Database 10g Release 10.2.0.1.0 - Production

SQL>Таким образом, создав пользователя OPS$ORACLE нам удалось подключится к локальному экземпляру без пароля.

Теперь попробуем поменять значение префикса (для этого придется перезапускать экземпляр)
SQL> show parameter OS_AUTHENT_PREFIX

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
os_authent_prefix string XYZ$
SQL> grant create session to XYZ$ORACLE identified by pass; Мы создали пользователя Oracle, соответствующего данному префиксу. Теперь подключаемся:

oracle@v-pc-dev-3:~> sqlplus /

SQL*Plus: Release 10.2.0.1.0 - Production on Sun Feb 7 15:05:04 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

ERROR:
ORA-01017: invalid username/password; logon denied
Упс. Не получилось.
Попробуем создать пользователя не с паролем, а внешнего:

SQL> create user XYZ$ORACLE identified externally;
User created.
SQL> grant create session to XYZ$ORACLE;
Grant succeeded. и подключаемся снова

oracle@v-pc-dev-3:~> sqlplus /
Connected to:
Oracle Database 10g Release 10.2.0.1.0 - Production
Выводы
1. При локальном подключении, если OS_AUTHENT_PREFIX отличен от OPS$, то пользователь в Oracle должен быть заведен identified externally
2. Если OS_AUTHENT_PREFIX=OPS$, то пользователь может быть заведен как внешний, так и с паролем.

Подключение по сети
А теперь попробуме подключится к серверу из вне без ввода пароля. Для этого (хоть Oracle этого так и не рекомендует, установим параметр REMOTE_OS_AUTHENT=TRUE.
Теперь попробуем варианты:
1. OS_AUTHENT_PREFIX <> OPS$, identified externally

SQL> create user "XYZ$ANDREY.ZAYTSEV" identified externally;

SQL> grant connect to "XYZ$ANDREY.ZAYTSEV";
Grant succeeded.

/>sqlplus /@v-pc-dev-3
Connected to:
Oracle Database 10g Release 10.2.0.1.0 - Production ПОЛУЧИЛОСЬ
2. OS_AUTHENT_PREFIX <> OPS$, identified by password

SQL> create user "XYZ$ANDREY.ZAYTSEV" identified by pass;
User created.

SQL> grant connect to "XYZ$ANDREY.ZAYTSEV";
Grant succeeded.

/>sqlplus /@v-pc-dev-3
ERROR:
ORA-01017: invalid username/password; logon denied
НЕ ПОЛУЧИЛОСЬ

3. OS_AUTHENT_PREFIX = OPS$, identified by password

SQL> create user "OPS$ANDREY.ZAYTSEV" identified by pass;
User created.

SQL> grant connect to "OPS$ANDREY.ZAYTSEV";
Grant succeeded.

C:\Documents and Settings\andrey.zaytsev>sqlplus /@v-pc-dev-3
Connected to:
Oracle Database 10g Release 10.2.0.1.0 - Production
ПОЛУЧИЛОСЬ
Таким образом для внешнего подключения при REMOTE_OS_AUTHENT=TRUE поведение такое же, как и для внутреннего.

Построчный вывод в SQL*Plus

Для построчного вывода в SQL*Plus:
SQL> set pause "Hit Enter"
SQL> set pagesize 1
SQL> set pause on
SQL> select rownum from all_objects where rownum <=5;
Hit Enter

1
Hit Enter

2
Hit Enter

3
Hit Enter

4
Hit Enter

5
Кроме того может пригодится ограничить количество выбираемых за раз строк:
SQL> SET ARRAYSIZE 1Данная возможность может быть полезна для проверки поведения в конкурирующих сессиях.

EVENTS in oracle

EVENTS в основном применяются для снятия трейсов, дампов, включения/выключения различных фич, патчей и т.д.
Задаются при помощи параметра инициализации EVENT - например для трассировки служебных процессов при старте экземпляра, утилиты ORADEBUG или с помощью команды
alter session/system SET EVENTS 'event_number trace name context forever, level event_level'
forever означает, что событие будет действовать пока мы его не отключим. Если forever нет, то событие повторяется ровно 1 раз. Так
ALTER SESSION SET EVENTS '10046 trace name context level 1'запишет в трейс ровно 1 команду ALTER и отключится.
Перечень событий можно посмотреть:
1. В интернете, например тут
2. В файле $ORACLE_HOME/rdbms/mesg/oraus.msg или используя oerr (для linuxов)

>;oerr ora 10046
10046, 00000, "enable SQL statement timing"
// *Cause:
// *Action:Номера большинства event от 10000 до 10999.

Интересные события
ALTER SYSTEM SET EVENTS '10231 trace name context forever, level 10';
пропуск бед-блоков при скинировании таблицы. Может помочь, если база вдруг нагнулась.

Снятие дампов
EVENTS можно использовать для снятия различных дампов, в случае возникновения ошибки:
ALTER {SESSION|SYSTEM} SET EVENTS 'error_code TRACE NAME dump_name LEVEL lvl'
Список доступных дампов посмотреть oradebug dumplist
Дамп "просто так":
ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME dump_name LEVEL lvl'например
ALTER SESSION SET EVENTS 'immediate trace name systemstate level 10';

P.S. Примеры взяты из книжки Norbert Debes "Secrets of the Oracle Database". Книга очень достойная

Создание v$ представлений

То немногое полезное, что удалось подчерпнуть из книги Richard Niemiec Oracle database 10G Performance tuning tips and techniques.

fixed_tables, v$, gv$

При старте экземпляра исполняемый файл создает в SGA x$ таблицы. Некоторые из них доступны в NOMOUNT
SQL> select * from x$ksutm;
ADDR INDX INST_ID KSUTMTIM
-------- ---------- ---------- ----------
00000000 0 1 1733938708
некоторые в MOUNT
select * from x$kccfe;
select * from x$kccfe
*
ERROR at line 1:
ORA-01507: database not mountedнекоторые в open. Структура таблиц зашита в исполняемом файле(?). Перечень таблиц можно получить выполнив запрос
SELECT * FROM v$fixed_table WHERE TYPE ='TABLE'
На основе x$ таблиц строятся sys.gv$ представления. Их доступность в nomount/mount зависит от того, на основе чего построен запрос представления. Запросы к этим представлениям может делать только пользователь sys.
create or replace view gv$fixed_table as
select inst_id,kqftanam, kqftaobj, 'TABLE', indx
from X$kqfta
union all
select inst_id,kqfvinam, kqfviobj, 'VIEW', 65537
from X$kqfvi
union all
select inst_id,kqfdtnam, kqfdtobj, 'TABLE', 65537
from X$kqfdt;На основе sys.gv$ представлений строятся sys.v$ представления. Выглядит это так:
select latch#,name, hash from gv$latchname where inst_id = userenv('Instance')т.е. из sys.gv$ не выбирается колонка inst_id и добавляется условие фильтрации inst_id = userenv('Instance')

Посмотреть список sys.gv$ и sys.v$ представлений можно при помощи запроса
SELECT * FROM v$fixed_table WHERE TYPE ='VIEW'Какие запросы скрыты за представлениями можно при помощи запроса к вьюхе v$fixed_view_definition.

Для того, что бы дать возможность остальным пользователям посмотреть на данный из x$ таблиц делается следующее:
1. Создаются вьюхи с подчеркиванием sys.gv_$ и sys.v_$.
2. На вьюхи sys.gv_$ и sys.v_$ создаются публичные синонимы gv$ и v$.
Посмотреть это можно в catalog.sql (и 2 штуки в catldr.sql)
create or replace view v_$gcshvmaster_info as select * from v$gcshvmaster_info;
create or replace public synonym v$gcshvmaster_info for v_$gcshvmaster_info;
grant select on v_$gcshvmaster_info to select_catalog_role;

create or replace view v_$gcspfmaster_info as select * from v$gcspfmaster_info;
create or replace public synonym v$gcspfmaster_info for v_$gcspfmaster_info;
grant select on v_$gcspfmaster_info to select_catalog_role;Для того, что бы дать конечному пользователю посмотреть v$, ему необходимо грантовать sys.v_$ представление.

Запрашивая v$ мы делаем запрос к публичному синониму!!!

Доступность V$ в режиме mount и nomount
Доступность представлений зависит от таблиц, на которых они построены (КО). Так, в режиме nomount для вышеприведнной таблицы x$ksutm, которая доступна мы получим:
SQL> select * from v$timer;
HSECS
----------
1733979624А для недоступной таблицы x$kccfe:
SQL> select * from v$filestat;
select * from v$filestat
*
ERROR at line 1:
ORA-01507: database not mountedУ меня есть предположение, что в mount не доступны те таблицы, которые зависят от ораклового словаря. Попробуем в этом убедится.
Используя приложение С из вышеописанной книги, найдем представление, которое зависит от словаря V$SEGMENT_STATISTICS (зависит от obj$, user$, x$ksolsfts, ts$, ind$) и выполним к нему запрос в режиме MOUNT:

SQL> SELECT object_name FROM V$SEGMENT_STATISTICS WHERE ROWNUM <= 1;
SELECT object_name FROM V$SEGMENT_STATISTICS WHERE ROWNUM <= 1
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 2
ORA-01219: database not open: queries allowed on fixed tables/views onlyПри этом любое другое представление, не зависящее от словаря Oracle успешно работает:
SQL> SELECT NAME FROM V$DATABASE;
NAME
---------
DB1
SQL> SELECT instance_name FROM V$INSTANCE;
INSTANCE_NAME
----------------
db1

Стив Джобс

(с) Стив Джобс и три его истории

Ваша работа заполнит большую часть жизни и единственный способ быть полностью довольным – делать то, что по-вашему является великим делом. И единственный способ делать великие дела – любить то, что вы делаете.

Команда для поиска самых больших файлов

В Linux для поиска самых больших файлов в каталоге и его подкаталогах использовать команду:
find . -type f -print | xargs du -m | sort -rn | less

Как читать CONNECT BY в плане

Нарыл очень полезную статью:
Striving for Optimal Performance » Operation CONNECT BY WITH FILTERING

В общем и целом смысл в чтении плана CONNECT BY WITH FILTERING:
---------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows |
---------------------------------------------------------------------
|* 1 | CONNECT BY WITH FILTERING | | 1 | 14 |
|* 2 | TABLE ACCESS FULL | EMP | 1 | 1 |
| 3 | NESTED LOOPS | | 4 | 13 |
| 4 | BUFFER SORT | | 4 | 14 |
| 5 | CONNECT BY PUMP | | 4 | 14 |
| 6 | TABLE ACCESS BY INDEX ROWID| EMP | 14 | 13 |
|* 7 | INDEX RANGE SCAN | EMP_MGR_I | 14 | 13 |
| 8 | TABLE ACCESS FULL | EMP | 0 | 0 |
---------------------------------------------------------------------

Имеются 3 дочерние операции:
1. Получение первой строки START WITH (2)
2. Работа по извлечению строк следующего уровня, на основании текущего (3)
3. Строка, работающая, если все не помещается в памяти (8). На этот случай заведен баг, т.к. работать будет все очень долго.

PS: надо поискать книгу этого автора.

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

Список привелегий можно получить из таблиц:
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
(с)