THE HOLY BIBLE - King James Version - БИБЛИЯ в Синодальном переводе
"Нас Атакуют!" Изобличи козни лукавого, запрети диаволу

Проверка выполнения и оптимизации SQL запросов в реальном времени

Оптимизатор SQL запросов Oracle достиг небывалого уровня сложности. Количество встроенных решений для предопределённых "частных случаев" иногда делает планы выполнения непредсказуемыми. Задача анализа и оптимизации SQL усложняется широким распространением параллельного выполнения и кластеров, "переписыванием" запросов "на лету", динамическим сбором статистики. В этих условиях традиционные методы анализа производительности и выполнения SQL оказываются недостаточными.

Предлагаемая читателю заметка рассказывает о новом средстве проверки выполнения и оптимизации SQL запросов в реальном времени - Active SQL Monitor Report.

Прежде чем мы продолжим, я хотел бы привести строки из Евангелия:



.................. == От Иоанна святое благовествование == .....................
=== Глава 15, Стих 12 ===
12 Сия  есть  заповедь  Моя,  да  любите  друг  друга,  как  Я   возлюбил   вас.
13 Нет больше той любви,  как  если  кто  положит  душу  свою  за  друзей своих.
14 Вы   друзья   Мои,   если   исполняете   то,   что    Я    заповедую     вам.
15 Я уже не называю вас рабами, ибо раб  не  знает,  что  делает  господин  его;
   но Я назвал вас друзьями, потому что сказал  вам  все,  что  слышал  от  Отца
   Моего.
16 Не вы Меня  избрали,  а  Я  вас  избрал  и  поставил  вас,  чтобы  вы  шли  и
   приносили плод, и чтобы плод ваш пребывал, дабы, чего ни  попросите  от  Отца
   во имя Мое, Он дал вам.
17 Сие      заповедаю      вам,      да       любите       друг           друга.
18 Если  мир  вас  ненавидит,  знайте,  что  Меня   прежде   вас   возненавидел.
19 Если бы вы были от мира, то мир любил бы свое; а как вы  не  от  мира,  но  Я
   избрал вас от мира, потому ненавидит вас мир.
20 Помните слово, которое Я сказал вам: раб не  больше  господина  своего.  Если
   Меня гнали, будут гнать и вас; если Мое слово соблюдали,  будут  соблюдать  и
   ваше.
21 Но все то сделают вам за  имя  Мое,  потому  что  не  знают  Пославшего Меня.
22 Если бы Я не пришел и не говорил им, то  не  имели  бы  греха;  а  теперь  не
   имеют извинения во грехе своем.

Лично для вас благая весть - Единородный Сын Божий Иисус Христос любит вас, Он взошёл на крест за ваши грехи, был распят и на третий день воскрес, сел одесную Бога и открыл нам дорогу в Царствие Небесное.

Господь говорит с каждым из нас - о христианской любви, о дружбе и преданности, исполнении Его заповедей. Именно так к каждому из нас приходит свобода, а с ней и ненависть греховного мира к нам. Но Иисус подбадривает нас, объясняя настоящую причину наших мирских неудач, расстройств и неприятностей. И в это же время милостиво даёт нам возможность спасения через познание Бога. Ухватимся же всеми силами за имя Его, несмотря на гонения и ненависть мира. И будем помнить слово, которое Он сказал нам.

Покайтесь, примите Иисуса как вашего Спасителя, ибо наступают последние времена и время близко - стоит Судья у ворот.

Пожалуйста, в своих каждодневных трудах, какими бы занятыми вы себе ни казались - находите время для Бога, Его заповедей и Библии.

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


Вернёмся к нашим техническим деталям.

Прежде всего мы проанализируем два варианта простого SQL запроса и найдём причину чрезмерного количества чтений блоков данных. Затем, используя Real Time SQL Monitoring, мы проверим наши выводы и посмотрим как можно избежать излишнего выполнения "проблемных" запросов во время анализа.

Данные для запроса

Создадим таблицу, содержащую абстрактные данные о продажах чего-либо.

.............. == Послание к Галатам святого апостола Павла == .................
=== Глава 5, Стих 15 ===
15 Если же друг друга  угрызаете  и  съедаете,  берегитесь,  чтобы  вы  не  были
   истреблены друг другом.

(b+/b-, c+/c-, +/-, *) > 
[ora@reatdb01 ~]$ sqlplus

SQL*Plus: Release 11.2.0.2.0 Production

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

Enter user-name: test/test
SQL> 

create sequence t1_pk_sq;

create table sales (
 SALEID, CLIENT, PRODUCTID,
 constraint PK_SALES_ID primary key (SALEID)
)
as select t1_pk_sq.nextval SALEID, d.*
from (
 select owner CLIENT, HEADER_BLOCK PRODUCTID
 from dba_segments
) d;

exec dbms_stats.gather_table_stats('TEST','SALES');

23:49:03 SQL> desc sales
 Name					 Null?	  Type		
 --------------------------------------- -------- ------------------------------
 SALEID 				 NOT NULL NUMBER(38)
 CLIENT 					  VARCHAR2(30)
 PRODUCTID					  NUMBER

Проверим, действительно ли значения нашего primary key монотонно возрастают:

23:49:17 SQL> select * from sales
23:49:31   2  where rownum <10;

    SALEID CLIENT			   PRODUCTID
---------- ------------------------------ ----------
	 1 SYS					 288
	 2 SYS					 224
	 3 SYS					 264
	 4 SYS					 232
	 5 SYS					 248
	 6 SYS					 520
	 7 SYS					 240
	 8 SYS					 560
	 9 SYS					 568

9 rows selected.

Elapsed: 00:00:00.00

Теперь посмотрим, как близко находятся ("упакованы") записи внутри блоков нашего индекса, отсортированного по столбцу SALEID:

  1  select INDEX_NAME, INDEX_TYPE, UNIQUENESS, BLEVEL, LEAF_BLOCKS, 
  2  DISTINCT_KEYS, CLUSTERING_FACTOR, STATUS, SEGMENT_CREATED
  3* from user_indexes
23:53:18 SQL> /

INDEX_NAME  INDEX_TYPE       UNIQUENES	   BLEVEL LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR STATUS   SEG
----------- ---------------- --------- ---------- ----------- ------------- ----------------- -------- ---
PK_SALES_ID NORMAL	     UNIQUE		1	   12	       6127		   17 VALID    YES

1 row selected.

Elapsed: 00:00:00.04
23:53:19 SQL> 


23:53:57 SQL> select CONSTRAINT_NAME, CONSTRAINT_TYPE, STATUS, INDEX_NAME
23:54:16   2  from user_constraints;

CONSTRAINT_NAME 	       C STATUS   INDEX_NAME
------------------------------ - -------- --------------------
PK_SALES_ID		       P ENABLED  PK_SALES_ID

1 row selected.

Elapsed: 00:00:00.75
23:54:22 SQL> 

23:55:07 SQL> l
  1* select * from user_ind_columns
23:55:20 SQL> /

INDEX_NAME  TABLE_NAME    COLUMN_NAME COLUMN_POSITION COLUMN_LENGTH   CHAR_LENGTH DESC
----------- ----------    ----------- --------------- -------------   ----------- ----
PK_SALES_ID SALES     	  SALEID	            1            22	        0 ASC

1 row selected.

Elapsed: 00:00:00.04
23:55:21 SQL> 

Близость значений CLUSTERING_FACTOR и LEAF_BLOCKS говорит нам о хорошей "упаковке" записей в индексе. Это сделает индекс PK_SALES_ID более "привлекательным" для оптимизатора запросов, что теоретически должно гарантировать нам самый быстрый доступ к данным таблицы.

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

  1  select SALEID, count(*)
  2  from sales
  3* group by saleid
23:57:25 SQL> /

..... output ....

6127 rows selected.

Elapsed: 00:00:00.12

Execution Plan
----------------------------------------------------------
Plan hash value: 1330191699

---------------------------------------------------------------------------------------------
| Id  | Operation		      | Name	    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	      | 	    |  6127 | 24508 |	  6  (17)| 00:00:01 |
|   1 |  HASH GROUP BY		      | 	    |  6127 | 24508 |	  6  (17)| 00:00:01 |
|   2 |   INDEX STORAGE FAST FULL SCAN| PK_SALES_ID |  6127 | 24508 |	  5   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
	  1  recursive calls
	  0  db block gets
	 17  consistent gets
	  0  physical reads
	  0  redo size
     130285  bytes sent via SQL*Net to client
       5011  bytes received via SQL*Net from client
	410  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
       6127  rows processed

00:02:06 SQL> 

Получен ожидаемый результат - обращений к таблице не было вообще, все необходимые данные были получены из самого индекса. Изменим запрос так, чтобы использование индекса не было таким привлекательным.

"Хороший" SQL запрос

В этот раз нам надо узнать сколько наименований продуктов купил каждый клиент.

........................... == Притчи Соломона == ..............................
=== Глава 23, Стих 4 ===
4 Не  заботься  о  том,  чтобы  нажить  богатство;  оставь  такие  мысли   твои.

(b+/b-, c+/c-, +/-, *) > 
[ora@reatdb01 ~]$ sqlplus

SQL*Plus: Release 11.2.0.2.0 Production

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

Enter user-name: test/test

00:06:46 SQL> l
  1   select client, count(distinct PRODUCTID)
  2   from sales
  3* group by client
00:06:47 SQL> /

CLIENT			       COUNT(DISTINCTPRODUCTID)
------------------------------ ------------------------
MDSYS						    566
CTXSYS						     53
OLAPSYS 					    141
HR						     25
OUTLN						      9
SYSTEM						    396
EXFSYS						     58
APEX_030200					    467
SCOTT						      5
ORDSYS						      7
SYSMAN						    579
OE						     70
SH						    179
PM						     41
DBSNMP						      3
XDB						    865
ORDDATA 					    160
IX						     28
SYS						   2333
WMSYS						     56

20 rows selected.

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 3472529357

------------------------------------------------------------------------------------------
| Id  | Operation		     | Name	 | Rows  | Bytes | Cost (%CPU)| Time	 |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	     |		 |    20 |   380 |     8  (13)| 00:00:01 |	
|   1 |  HASH GROUP BY		     |		 |    20 |   380 |     8  (13)| 00:00:01 | уменьшение числа строк
|   2 |   VIEW			     | VM_NWVW_1 |  6127 |   113K|     8  (13)| 00:00:01 |
|   3 |    HASH GROUP BY	     |		 |  6127 | 67397 |     8  (13)| 00:00:01 |
|   4 |     TABLE ACCESS STORAGE FULL| SALES	 |  6127 | 67397 |     7   (0)| 00:00:01 |
------------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
	  1  recursive calls
	  0  db block gets
	 19  consistent gets	-- Таблица уже в buffer cache (после сбора статистики и первого запроса)
	  0  physical reads
	  0  redo size
       1104  bytes sent via SQL*Net to client
	534  bytes received via SQL*Net from client
	  3  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	 20  rows processed

00:06:49 SQL> 

В этот раз результат немного неожиданный - индекс не был использован вообще, поскольку полное чтение всей таблицы требует приблизительно такого же количества чтений блоков из буфера (19 буферов за 7 операций чтения), как и сканирование индекса (17 буферов за 5 операций.).

Также надо заметить что при пустом buffer cache платформа Exadata позволяет операции "TABLE ACCESS STORAGE FULL" использовать в одной сессии два разных способа чтения данных в buffer cache (а не PGA) - "cell single block physical read" для доступа к заголовку сегмента и "cell multiblock physical read" для прочтения "за один раз" всех оставшихся блоков. Если же buffer cache оказывается не пустым, как в этом примере "хорошего" запроса - сессия прочитает все необходимые блоки из памяти SGA (а не PGA), опять же несмотря на способ доступа к таблице "TABLE ACCESS STORAGE FULL".

Возможно ли что Оракл выбрал неоптимальный способ чтения данных и нам надо использовать индекс?

"Плохой" SQL запрос

Заставим Оракл использовать индекс и оценим результат.

....................... == Деяния святых апостолов == ..........................
=== Глава 20, Стих 24 ===
24 Но я ни на что не взираю и не дорожу  своею  жизнью,  только  бы  с  радостью
   совершить поприще мое  и  служение,  которое  я  принял  от  Господа  Иисуса,
   проповедать Евангелие благодати Божией.

(b+/b-, c+/c-, +/-, *) >
[ora@reatdb01 ~]$ sqlplus

SQL*Plus: Release 11.2.0.2.0 Production

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

Enter user-name: test/test

SQL> l

  1   select /*+ INDEX (sales, PK_SALES_ID) */ client, count(distinct PRODUCTID)
  2   from sales
  3* group by client
00:13:08 SQL> /

CLIENT			       COUNT(DISTINCTPRODUCTID)
------------------------------ ------------------------
MDSYS						    566
CTXSYS						     53
OLAPSYS 					    141
HR						     25
OUTLN						      9
SYSTEM						    396
EXFSYS						     58
APEX_030200					    467
SCOTT						      5
ORDSYS						      7
SYSMAN						    579
OE						     70
SH						    179
PM						     41
DBSNMP						      3
XDB						    865
ORDDATA 					    160
IX						     28
SYS						   2333
WMSYS						     56

20 rows selected.

Elapsed: 00:00:00.02

Execution Plan
----------------------------------------------------------
Plan hash value: 3227008845

----------------------------------------------------------------------------------------------
| Id  | Operation		       | Name	     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	       |	     |	  20 |	 380 |	  31   (4)| 00:00:01 | Cost вырос в 3 раза
|   1 |  HASH GROUP BY		       |	     |	  20 |	 380 |	  31   (4)| 00:00:01 | CPU меньше в 3 раза
|   2 |   VIEW			       | VM_NWVW_1   |	6127 |	 113K|	  31   (4)| 00:00:01 |
|   3 |    HASH GROUP BY	       |	     |	6127 | 67397 |	  31   (4)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID| SALES	     |	6127 | 67397 |	  30   (0)| 00:00:01 | Очевидно, что индекс
|   5 |      INDEX FULL SCAN	       | PK_SALES_ID |	6127 |	     |	  13   (0)| 00:00:01 | тут не нужен.
----------------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
	  1  recursive calls
	  0  db block gets
	 30  consistent gets	-- Логический IO возрос всего на 1/3 из-за хорошей "плотности" индекса
	  0  physical reads
	  0  redo size
       1104  bytes sent via SQL*Net to client
	534  bytes received via SQL*Net from client
	  3  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	 20  rows processed

00:13:11 SQL> 

В этом случае raw trace файл показывает, что "cr" равен "cost" для обеих строк плана "INDEX FULL SCAN" и "TABLE ACCESS BY INDEX ROWID" - наша сессия читает по одному блоку за раз. Просто для полного прочтения таблицы через индекс нам понадобится один блок для "корня" индекса, 12 блоков для "листьев" индекса и 17 прочтений блоков (смотрите CLUSTERING_FACTOR в начале заметки) для доступа к строкам самой таблицы - итого 30 consistent gets - буферов, прочитанных из buffer cache, равным по размеру 8192 байт каждый.

И чем выше значение CLUSTERING_FACTOR для используемого индекса - тем большее количество обращений к диску или buffer cache будет необходимо для прочтения всей таблицы. В нашем примере индекс PK_SALES_ID имеет почти идеальную структуру и очень низкий CLUSTERING_FACTOR, в реальной ситуации полное прочтение таблицы по индексу потребует значительно больше ресурсов чем простой full table scan.

Также заметьте, что при таком запросе не используется специфический для Exadata "INDEX STORAGE FULL SCAN".

Становится понятным, что показанный выше способ анализа производительности SQL запроса весьма трудоёмок и потребует многократного выполнения запроса, очищения buffer cache, трассировки сессий и прочих специфических приёмов, недопустимых на "живой" системе.

Active SQL Monitor Report

Основное назначение этого отчёта - периодически оценивать степень выплонения длительного SQL запроса. То есть этот отчёт может показывать нам сколько процентов работы уже выполнено параллельными сессиями, и сколько ещё осталось. В дополнение, Active SQL Monitor Report собирает для нас всю использованную выше статистику для уже выполненных SQL запросов длинной более 5 секунд (и для всех параллельных запросов). Это позволяет нам увидеть всю необходимую информацию в одном месте, без блуждания по raw trace файлам - и почти полностью исключает необходимость перезапусков "медленных" запросов. Генерировать этот отчёт надо как можно быстрее - желательно сразу же после завершения "плохого" запроса.

Итак, освежим нашу память - в чём проблема? Предположим, что один из разработчиков "по-старинке" уверен что индекс надо использовать всегда. Наш специалист берёт "хороший" запрос и делает из него "плохой" запрос (см. выше). После попадания этого кода в "живую" систему наши пользователи начинают жаловаться на резкое замедление работы приложения.

Как администраторы баз данных, мы должны найти причину деградации производительности как можно скорее. Мы быстренько генерируем AWR report за последние 15-20 минут и обнаруживаем наш "плохой" запрос на месте лидера по потреблению ресурсов системы. У нас нет времени на подробное "разжёвывание" запроса, как было сделано выше. Нам надо просто как можно быстрее понять, на что же "плохой" запрос тратит время и IO ресурсы. Выплоняем следующий шаг:

.................. == От Марка святое благовествование == ......................
=== Глава 4, Стих 24 ===
23 Если      кто       имеет       уши       слышать,       да           слышит!
24 И сказал им: замечайте, что  слышите:  какою  мерою  мерите,  такою  отмерено
   будет вам и прибавлено будет вам, слушающим.

(b+/b-, c+/c-, +/-, *) >
[ora@reatdb01 ~]$ sqlplus

SQL*Plus: Release 11.2.0.2.0 Production

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

Enter user-name: test/test

SQL> l

  1  select * from gv$sql_monitor
  2* where sysdate-SQL_EXEC_START<1/24/4	-- последние 15 минут
00:35:53 SQL> /

no rows selected

Elapsed: 00:00:01.19
00:35:55 SQL> 

Запрос выполняется слишком быстро и не был зарегистрирован. Заставить Оракл "обратить внимание" на нужный SQL можно вручную. Мы делаем это в нашем примере исключительно в демонстрационных целях - в действительности ваш "проблемный" отчёт наверняка появится в V$SQL_MONITOR - и вы сможете его там найти, если пользователи пожалуются достаточно быстро. Таким образом, вам не понадобится выполнять "плохой" запрос снова. Ну а мы вставим в код необходимый "hint" и запустим заново оба SQL - "хороший" и "плохой".

-- "хороший" запрос
select /*+ MONITOR */ client, count(distinct PRODUCTID)
from sales
group by client
/

CLIENT			       COUNT(DISTINCTPRODUCTID)
------------------------------ ------------------------
MDSYS						    566
CTXSYS						     53
OLAPSYS 					    141
HR						     25
OUTLN						      9
SYSTEM						    396
EXFSYS						     58
APEX_030200					    467
SCOTT						      5
ORDSYS						      7
SYSMAN						    579
OE						     70
SH						    179
PM						     41
DBSNMP						      3
XDB						    865
ORDDATA 					    160
IX						     28
SYS						   2333
WMSYS						     56

20 rows selected.

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 3472529357

------------------------------------------------------------------------------------------
| Id  | Operation		     | Name	 | Rows  | Bytes | Cost (%CPU)| Time	 |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	     |		 |    20 |   380 |     8  (13)| 00:00:01 |
|   1 |  HASH GROUP BY		     |		 |    20 |   380 |     8  (13)| 00:00:01 |
|   2 |   VIEW			     | VM_NWVW_1 |  6127 |   113K|     8  (13)| 00:00:01 |
|   3 |    HASH GROUP BY	     |		 |  6127 | 67397 |     8  (13)| 00:00:01 |
|   4 |     TABLE ACCESS STORAGE FULL| SALES	 |  6127 | 67397 |     7   (0)| 00:00:01 |
------------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
	  1  recursive calls
	  0  db block gets
	 19  consistent gets
	  0  physical reads
	  0  redo size
       1104  bytes sent via SQL*Net to client
	534  bytes received via SQL*Net from client
	  3  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	 20  rows processed

00:42:21 SQL>

  1  select inst_id, sql_id, username, sql_exec_id, SQL_TEXT
  2  from gv$sql_monitor
  3* where sysdate-SQL_EXEC_START<1/24/4
00:43:40 SQL> /

   INST_ID  SQL_ID		USERNAME			     SQL_EXEC_ID
---------- --------------------	------------------------------ -----------------
SQL_TEXT
--------------------------------------------------------------------------------
	 1 27q9ut623h9rf	TEST 			     		16777216
EXPLAIN PLAN SET STATEMENT_ID='PLUS20024' FOR select /*+ MONITOR */ client, 
count(distinct PRODUCTID)
from sales
group by client

	 1 f1hmr6pcwzf5u	TEST 			     		16777216	-- наш "хороший" запрос
select /*+ MONITOR */ client, count(distinct PRODUCTID)
from sales
group by client


2 rows selected.

Elapsed: 00:00:00.02
00:43:41 SQL> 

Главное, что нам надо записать - SQL_ID и SQL_EXEC_ID, эти два параметра однозначно определяют о каком SQL запросе мы говорим. Найдём соответствующие значения для "плохого" отчёта с индексом.

-- теперь "плохой" запрос
00:46:20 SQL> get /tmp/2
  1  select /*+ MONITOR INDEX (sales, PK_SALES_ID) */ client, count(distinct PRODUCTID)
  2  from sales
  3* group by client
00:46:23 SQL> /

CLIENT			       COUNT(DISTINCTPRODUCTID)
------------------------------ ------------------------
MDSYS						    566
CTXSYS						     53
OLAPSYS 					    141
HR						     25
OUTLN						      9
SYSTEM						    396
EXFSYS						     58
APEX_030200					    467
SCOTT						      5
ORDSYS						      7
SYSMAN						    579
OE						     70
SH						    179
PM						     41
DBSNMP						      3
XDB						    865
ORDDATA 					    160
IX						     28
SYS						   2333
WMSYS						     56

20 rows selected.

Elapsed: 00:00:00.02

Execution Plan
----------------------------------------------------------
Plan hash value: 3227008845

----------------------------------------------------------------------------------------------
| Id  | Operation		       | Name	     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	       |	     |	  20 |	 380 |	  31   (4)| 00:00:01 |
|   1 |  HASH GROUP BY		       |	     |	  20 |	 380 |	  31   (4)| 00:00:01 |
|   2 |   VIEW			       | VM_NWVW_1   |	6127 |	 113K|	  31   (4)| 00:00:01 |
|   3 |    HASH GROUP BY	       |	     |	6127 | 67397 |	  31   (4)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID| SALES	     |	6127 | 67397 |	  30   (0)| 00:00:01 |
|   5 |      INDEX FULL SCAN	       | PK_SALES_ID |	6127 |	     |	  13   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
	  1  recursive calls
	  0  db block gets
	 30  consistent gets
	  0  physical reads
	  0  redo size
       1104  bytes sent via SQL*Net to client
	534  bytes received via SQL*Net from client
	  3  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	 20  rows processed

00:46:23 SQL> 

  1  select inst_id, sql_id, username, sql_exec_id, SQL_TEXT
  2  from gv$sql_monitor
  3  where sysdate-SQL_EXEC_START<1/24/4
  4* and sql_text like '%INDEX%'
00:48:55 SQL> /

   INST_ID  SQL_ID		USERNAME			     SQL_EXEC_ID
---------- --------------------	------------------------------ -----------------
SQL_TEXT
--------------------------------------------------------------------------------
	 1 c4s7v8uzmdt38	TEST 			     		16777216
EXPLAIN PLAN SET STATEMENT_ID='PLUS20024' FOR 
select /*+ MONITOR INDEX (sales, PK_SALES_ID) */ client, 
count(distinct PRODUCTID)
from sales
group by client

	 1 bm6q2rmzfxjrb	TEST 			     		16777216	-- "плохой" запрос
select /*+ MONITOR INDEX (sales, PK_SALES_ID) */ client, count(distinct PRODUCTID)
from sales
group by client


2 rows selected.

Elapsed: 00:00:00.01
00:48:56 SQL> 

Итак, все необходимые данные имеются - сгенерируем Active SQL Monitor Report для обоих случаев. Необходимо правильно настроить параметры вывода SQL*Plus и потом вручную отредактировать HTML файлы. Не пропустите самую первую строку "set ..." - без неё ваш SQL Monitor Report будет нечитаемым.

-- Теперь проанализируем уже запущенные ранее запросы:

00:50:31 SQL> set lines 30000 trims on long 999999 longchunksize 9999 pages 0

00:56:16 SQL> spool /tmp/good.html
00:56:39 SQL> select dbms_sqltune.report_sql_monitor(
 sql_id=>'f1hmr6pcwzf5u', sql_exec_id=>16777216, type=>'active') from dual;

.... Огромное количество HTML строк ....

1 row selected.

Elapsed: 00:00:58.74
00:59:15 SQL> 00:59:15 SQL> spool off
00:59:20 SQL> 

00:59:20 SQL> spool /tmp/bad.html
00:59:50 SQL> select dbms_sqltune.report_sql_monitor(
 sql_id=>'bm6q2rmzfxjrb', SQL_EXEC_ID=>16777216, type=>'active') from dual;

.... Огромное количество HTML строк ....

1 row selected.

Elapsed: 00:00:02.36
01:01:00 SQL> spool off

Отредактируем оба spool файла так, чтобы в них не было лишних строк внизу и вверху и загрузим их к себе на компьютер. Используйте веб броузер, подключённый к интернету и с полностью включенными Java Script и Plugins. Я всегда пользуюсь Opera.

Открыв файл "bad.html", на странице "Details / Plan" мы сможем увидеть точное распределение 30-ти "consistent gets" между операциями доступа к таблице по индексу - 13 чтений буферов (в нашем случае и блоков) индекса и 17 чтений таблицы. "Откопать" эту информацию иным способом достаточно сложно и долго. Кроме того, время исполнения запроса указано с точностью до микросекунды. Сравните данные со вторым отчётом "good.html".

Теперь поэкспериментируйте с другими запросами посложнее и обратите внимание на очень полезную страницу "Activity".

Я уверен что "Active SQL Monitor Report" послужит вам отличным подспорьем в работе.

Спасибо что зашли,

Будьте благословенны!
Денис


Сайт не содержит активных элементов - скриптов и cookies