Небольшая иллюстрация того что если в вашей OLTP системе есть часто используемые запросы с привязанным sql profile'ом, то всегда нужно подумать прежде чем совершать различные, казалось бы, безобидные вещи, например, выдавать права на объекты.
Итак, наш запрос:
Посмотрим на его план:
У нас есть действующий профайл для запроса.
Предположим, нам потребовалось выдат права какому-то пользователю на таблички T0 или Т1.
Выдаем права и проверяем что происходит у нас с курсором:
Курсор инвалидировался из-за изменений прав доступа. Следующий запуск нашего запроса также форсирует проверку профайла, что может занять некоторое время, если запрос довольно сложный или объем данных внушителен.
Заглянув в трейс файл, или, пробежавшись по нему tkprof'ом, можно увидеть следующее:
В данном случае проверка заняла незначительное время, но стоит помнить что курсор при этом держится в экслюзивном режиме, и все сессии пытающиеся выполнить наш запрос, повиснут на ожидании cursor: pin S wait on X.
В нагруженной системе при длительной проверке можно успеть собрать большую очередь ожидающих сессий.
Итак, наш запрос:
| insert into test.t2
( select c0,b.c1,a.c2 from test.t0 a inner join test.t1 b using(c0) where c0 between 500000 and 1000000 ); |
Посмотрим на его план:
| SQL> @q
Connected. Explained. Plan hash value: 1792085932 --------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------- | 0 | INSERT STATEMENT | | 47926 | 6692K| 1161 (1)| 12:31:13 | | 1 | LOAD TABLE CONVENTIONAL | T2 | | | | | |* 2 | HASH JOIN | | 47926 | 6692K| 1161 (1)| 12:31:13 | |* 3 | TABLE ACCESS FULL | T0 | 47448 | 509K| 580 (0)| 06:15:27 | |* 4 | TABLE ACCESS FULL | T1 | 50912 | 6562K| 580 (0)| 06:15:27 | --------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("A"."C0"="B"."C0") 3 - filter("A"."C0">=500000 AND "A"."C0"<=1000000) 4 - filter("B"."C0">=500000 AND "B"."C0"<=1000000) Note ----- - SQL profile "SYS_SQLPROF_01425ad4fde30000" used for this statement 22 rows selected. |
У нас есть действующий профайл для запроса.
Предположим, нам потребовалось выдат права какому-то пользователю на таблички T0 или Т1.
Выдаем права и проверяем что происходит у нас с курсором:
| SQL> select invalidations,executions,object_status,is_obsolete,sql_profile from v$sql where sql_id='abryha8r76gmp';
INVALIDATIONS EXECUTIONS OBJECT_STATUS SQL_PROFILE ---------------- ---------------- ---------------- -------------------------------- 0 1 VALID SYS_SQLPROF_01425ad4fde30000 SQL> grant select on t0 to test1; Grant succeeded. SQL> grant select on t1 to test1; Grant succeeded. SQL> select invalidations,executions,object_status,is_obsolete,sql_profile from v$sql where sql_id='abryha8r76gmp'; INVALIDATIONS EXECUTIONS OBJECT_STATUS SQL_PROFILE ---------------- ---------------- ---------------- -------------------------------- 1 1 INVALID_UNAUTH SYS_SQLPROF_01425ad4fde30000 |
Курсор инвалидировался из-за изменений прав доступа. Следующий запуск нашего запроса также форсирует проверку профайла, что может занять некоторое время, если запрос довольно сложный или объем данных внушителен.
| SQL> alter session set events '10046 trace name context forever, level 12';
Session altered. SQL> insert into test.t2 2 ( 3 select c0,b.c1,a.c2 4 from test.t0 a 5 inner join test.t1 b using(c0) 6 where c0 between 500000 and 1000000 7 ); 5035 rows created. SQL> alter session set events '10046 trace name context off'; Session altered. SQL> rollback; Rollback complete. SQL> select invalidations,executions,is_obsolete,object_status,sql_profile from v$sql where sql_id='abryha8r76gmp'; INVALIDATIONS EXECUTIONS OBJECT_STATUS SQL_PROFILE ---------------- ---------------- ---------------- -------------------------------- 1 1 VALID SYS_SQLPROF_01425ad4fde30000 |
Заглянув в трейс файл, или, пробежавшись по нему tkprof'ом, можно увидеть следующее:
| SQL ID: 5mzpa3fwjsm2c Plan Hash: 2594672137
SELECT /* DS_SVC */ /*+ cursor_sharing_exact dynamic_sampling(0) no_sql_tune no_monitoring optimizer_features_enable(default) */ SUM(C1) FROM (SELECT /*+ qb_name("innerQuery") NO_INDEX_FFS( "B") */ 1 AS C1 FROM "TEST"."T1" SAMPLE BLOCK(3.75287, 8) SEED(1) "B") innerQuery 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.35 0.35 0 2230 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.35 0.35 0 2230 0 1 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 269 (recursive depth: 1) ******************************************************************************** SQL ID: 9m4013m0g9yzq Plan Hash: 1819681537 SELECT /* DS_SVC */ /*+ cursor_sharing_exact dynamic_sampling(0) no_sql_tune no_monitoring optimizer_features_enable(default) */ SUM(C1) FROM (SELECT /*+ qb_name("innerQuery") NO_INDEX_FFS( "A") */ 1 AS C1 FROM "TEST"."T0" SAMPLE BLOCK(3.75287, 8) SEED(1) "A") innerQuery 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.36 0.36 0 2230 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.36 0.37 0 2230 0 1 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 269 (recursive depth: 1) |
В данном случае проверка заняла незначительное время, но стоит помнить что курсор при этом держится в экслюзивном режиме, и все сессии пытающиеся выполнить наш запрос, повиснут на ожидании cursor: pin S wait on X.
В нагруженной системе при длительной проверке можно успеть собрать большую очередь ожидающих сессий.
