Pipe get oracle что это
Обновлено: 06.07.2024
В промышленных системах часто требуется выполнить преобразования данных с использованием pl/sql кода с возможностью обращения к этим данным в sql запросе. Для этого в oracle используются табличные функции.
Табличные функции – это функции возвращающие данные в виде коллекции, к которой мы можем обратиться в секции from запроса, как если бы эта коллекция была реляционной таблицей. Преобразование коллекции в реляционный набор данных осуществляется с помощью функции table().
Однако такие функции имеют один недостаток, так как в них сначала полностью наполняется коллекция, а только потом эта коллекция возвращается в вызывающую обработку. Каждая такая коллекция храниться в памяти и в высоконагруженных системах это может стать проблемой. Так же в вызывающей обработке происходит простой на время наполнения коллекции. Решить данный недостаток призваны табличные конвейерные функции.
Конвейерными функциями называются табличные функции, которые возвращают данные в виде коллекции, но делают это асинхронно, то есть получена одна запись коллекции и сразу же эта запись отдается в вызывающий код в котором она сразу же обрабатывается. В этом случае память сохраняется, простой по времени ликвидируется.
Рассмотрим, как создаются такие функции. В данном примере будет использована учебная схема hr и три ее таблицы: employees, departments, locations.
• employees — таблица сотрудников.
• departments — таблица отделов.
• locations — таблица географического местонахождения.
Данная схема и таблицы есть в каждой базовой сборке oracle по умолчанию.
В схеме hr я создам пакет test, в нем будет реализован наш код. Создаваемая функция будет возвращать данные по сотрудникам в конкретном отделе. Для этого в спецификации пакета нужно описать тип возвращаемых данных:
• employee_id – ид сотрудника
• first_name – имя
• last_name – фамилия
• email – электронный адрес
• phone_number – телефон
• salary – зарплата
• salary_recom – рекомендуемая зарплата
• department_id – ид отдела
• department_name — наименование отдела
• city – город
Далее опишем саму функцию:
Функция принимает на вход ид отдела и возвращает коллекцию созданного нами типа t_employees_table. Ключевое слово pipelined делает эту функцию конвейерной. В целом спецификация пакета следующая:
Рассмотрим тело пакета, в нем описано тело функции get_employees_dep:
В функции мы получаем набор данных по сотрудникам конкретного отдела, каждую строчку этого набора анализируем на предмет того, что если зарплата сотрудника меньше 8 000, то рекомендуемую зарплату устанавливаем в значение 10 000, дальше каждая строчка не дожидаясь окончания наполнения всей коллекции, отдается в вызывающую обработку. Обратите внимание, что в теле функции отсутствует ключевое слово return и присутствует pipe row (rec).
Осталось вызвать созданную функцию в pl/sql блоке:
Вот так вот просто с помощью конвейерных табличных функций мы получаем возможность сделать выборку, наполненную сколько угодно сложной логикой за счет использования pl/sql кода и не просесть в плане производительности, а в ряде случаем даже ее увеличить.
The DBMS_PIPE package lets two or more sessions in the same instance communicate. Oracle pipes are similar in concept to the pipes used in UNIX, but Oracle pipes are not implemented using the operating system pipe mechanisms.
This chapter contains the following topics:
Using DBMS_PIPE
Overview
Pipe functionality has several potential applications:
External service interface: You can communicate with user-written services that are external to the RDBMS. This can be done effectively in a shared server process, so that several instances of the service are executing simultaneously. Additionally, the services are available asynchronously. The requestor of the service does not need to block a waiting reply. The requestor can check (with or without time out) at a later time. The service can be written in any of the 3GL languages that Oracle supports.
Independent transactions: The pipe can communicate to a separate session which can perform an operation in an independent transaction (such as logging an attempted security violation detected by a trigger).
Alerters (non-transactional): You can post another process without requiring the waiting process to poll. If an "after-row" or "after-statement" trigger were to alert an application, then the application would treat this alert as an indication that the data probably changed. The application would then read the data to get the current value. Because this is an "after" trigger, the application would want to do a " SELECT FOR UPDATE " to make sure it read the correct data.
Debugging: Triggers and stored procedures can send debugging information to a pipe. Another session can keep reading out of the pipe and display it on the screen or write it to a file.
Concentrator: This is useful for multiplexing large numbers of users over a fewer number of network connections, or improving performance by concentrating several user-transactions into one DBMS transaction.
Security Model
Security can be achieved by use of GRANT EXECUTE on the DBMS_PIPE package by creating a pipe using the private parameter in the CREATE_PIPE function and by writing cover packages that only expose particular features or pipenames to particular users or roles.
Depending upon your security requirements, you may choose to use either Public Pipes or Private Pipes.
Constants
This is the maximum time to wait attempting to send or receive a message.
Operational Notes
Information sent through Oracle pipes is buffered in the system global area (SGA). All information in pipes is lost when the instance is shut down.
Pipes are independent of transactions. Be careful using pipes when transaction control can be affected.
The operation of DBMS_PIPE is considered with regard to the following topics:
Public Pipes
You may create a public pipe either implicitly or explicitly. For implicit public pipes, the pipe is automatically created when it is referenced for the first time, and it disappears when it no longer contains data. Because the pipe descriptor is stored in the SGA, there is some space usage overhead until the empty pipe is aged out of the cache.
You create an explicit public pipe by calling the CREATE_PIPE function with the private flag set to FALSE . You must deallocate explicitly-created pipes by calling the REMOVE_PIPE function.
The domain of a public pipe is the schema in which it was created, either explicitly or implicitly.
Writing and Reading Pipes
Each public pipe works asynchronously. Any number of schema users can write to a public pipe, as long as they have EXECUTE permission on the DBMS_PIPE package, and they know the name of the public pipe. However, once buffered information is read by one user, it is emptied from the buffer, and is not available for other readers of the same pipe.
The sending session builds a message using one or more calls to the PACK_MESSAGE procedure. This procedure adds the message to the session's local message buffer. The information in this buffer is sent by calling the SEND_MESSAGE function, designating the pipe name to be used to send the message. When SEND_MESSAGE is called, all messages that have been stacked in the local buffer are sent.
A process that wants to receive a message calls the RECEIVE_MESSAGE function, designating the pipe name from which to receive the message. The process then calls the UNPACK_MESSAGE procedure to access each of the items in the message.
Private Pipes
You explicitly create a private pipe by calling the CREATE_PIPE function. Once created, the private pipe persists in shared memory until you explicitly deallocate it by calling the REMOVE_PIPE function. A private pipe is also deallocated when the database instance is shut down.
You cannot create a private pipe if an implicit pipe exists in memory and has the same name as the private pipe you are trying to create. In this case, CREATE_PIPE returns an error.
Access to a private pipe is restricted to:
Sessions running under the same userid as the creator of the pipe
Stored subprograms executing in the same userid privilege domain as the pipe creator
Users connected as SYSDBA
An attempt by any other user to send or receive messages on the pipe, or to remove the pipe, results in an immediate error. Any attempt by another user to create a pipe with the same name also causes an error.
As with public pipes, you must first build your message using calls to PACK_MESSAGE before calling SEND_MESSAGE . Similarly, you must call RECEIVE_MESSAGE to retrieve the message before accessing the items in the message by calling UNPACK_MESSAGE .
Exceptions
DBMS_PIPE package subprograms can return the following errors:
Table 70-1 DBMS_PIPE Errors
Pipename may not be null. This can be returned by the CREATE_PIPE function, or any subprogram that takes a pipe name as a parameter.
Insufficient privilege to access pipe. This can be returned by any subprogram that references a private pipe in its parameter list.
Examples
Example 1: Debugging - PL/SQL
This example shows the procedure that a PL/SQL program can call to place debugging information in a pipe.
Example 2: Debugging - Pro*C
The following Pro*C code receives messages from the PLSQL_DEBUG pipe in the previous example, and displays the messages. If the Pro*C session is run in a separate window, then it can be used to display any messages that are sent to the debug procedure from a PL/SQL program executing in a separate session.
Example 3: Execute System Commands
This example shows PL/SQL and Pro*C code let a PL/SQL stored procedure (or anonymous block) call PL/SQL procedures to send commands over a pipe to a Pro*C program that is listening for them.
The Pro*C program sleeps and waits for a message to arrive on the named pipe. When a message arrives, the Pro*C program processes it, carrying out the required action, such as executing a UNIX command through the system () call or executing a SQL command using embedded SQL.
DAEMON.SQL is the source code for the PL/SQL package. This package contains procedures that use the DBMS_PIPE package to send and receive message to and from the Pro*C daemon. Note that full handshaking is used. The daemon always sends a message back to the package (except in the case of the STOP command). This is valuable, because it allows the PL/SQL procedures to be sure that the Pro*C daemon is running.
You can call the DAEMON packaged procedures from an anonymous PL/SQL block using SQL*Plus or Enterprise Manager. For example:
On a UNIX system, this causes the Pro*C daemon to execute the command system (" ls -la ").
Remember that the daemon needs to be running first. You might want to run it in the background, or in another window beside the SQL*Plus or Enterprise Manager session from which you call it.
The DAEMON . SQL also uses the DBMS_OUTPUT package to display the results. For this example to work, you must have execute privileges on this package.
DAEMON.SQL Example. This is the code for the PL/SQL DAEMON package:
daemon.pc Example. This is the code for the Pro*C daemon. You must precompile this using the Pro*C Precompiler, Version 1.5.x or later. You must also specify the USERID and SQLCHECK options, as the example contains embedded PL/SQL code.
To use a VARCHAR output host variable in a PL/SQL block, you must initialize the length component before entering the block.
Then C-compile and link in the normal way.
Example 4: External Service Interface
Put the user-written 3GL code into an OCI or Precompiler program. The program connects to the database and executes PL/SQL code to read its request from the pipe, computes the result, and then executes PL/SQL code to send the result on a pipe back to the requestor.
Below is an example of a stock service request. The recommended sequence for the arguments to pass on the pipe for all service requests is:
The recommended format for returning the result is:
The "stock price request server" would do, using OCI or PRO* (in pseudo-code):
A client would do:
The stored procedure, dbms_stock_server , which is called by the preceding "stock price request server" is:
The procedure called by the client is:
You would typically only GRANT EXECUTE on DBMS_STOCK_SERVICE to the stock service application server, and would only GRANT EXECUTE on stock_request to those users allowed to use the service.
Summary of DBMS_PIPE Subprograms
Table 70-2 DBMS_PIPE Package Subprograms
Creates a pipe (necessary for private pipes)
Returns datatype of next item in buffer
Builds message in local buffer
Purges contents of named pipe
Copies message from named pipe into local buffer
Removes the named pipe
Purges contents of local buffer
Sends message on named pipe: This implicitly creates a public pipe if the named pipe does not exist
Returns unique session name
Accesses next item in buffer
CREATE_PIPE Function
This function explicitly creates a public or private pipe. If the private flag is TRUE , then the pipe creator is assigned as the owner of the private pipe.
Explicitly-created pipes can only be removed by calling REMOVE_PIPE , or by shutting down the instance.
Table 70-3 CREATE_PIPE Function Parameters
Name of the pipe you are creating.
You must use this name when you call SEND_MESSAGE and RECEIVE_MESSAGE . This name must be unique across the instance.
Caution: Do not use pipe names beginning with ORA$ . These are reserved for use by procedures provided by Oracle. Pipename should not be longer than 128 bytes, and is case insensitive. At this time, the name cannot contain Globalization Support characters.
The maximum size allowed for the pipe, in bytes.
The total size of all of the messages on the pipe cannot exceed this amount. The message is blocked if it exceeds this maximum. The default maxpipesize is 8192 bytes.
The maxpipesize for a pipe becomes a part of the characteristics of the pipe and persists for the life of the pipe. Callers of SEND_MESSAGE with larger values cause the maxpipesize to be increased. Callers with a smaller value use the existing, larger value.
Uses the default, TRUE , to create a private pipe.
Public pipes can be implicitly created when you call SEND_MESSAGE .
Table 70-4 CREATE_PIPE Function Return Values
If the pipe already exists and the user attempting to create it is authorized to use it, then Oracle returns 0, indicating success, and any data already in the pipe remains.
If a user connected as SYSDBA / SYSOPER re-creates a pipe, then Oracle returns status 0, but the ownership of the pipe remains unchanged.
Failure due to naming conflict.
If a pipe with the same name exists and was created by a different user, then Oracle signals error ORA-23322 , indicating the naming conflict.
Table 70-5 CREATE_PIPE Function Exception
Permission error: Pipe with the same name already exists, and you are not allowed to use it.
NEXT_ITEM_TYPE Function
This function determines the datatype of the next item in the local message buffer.
After you have called RECEIVE_MESSAGE to place pipe information in a local buffer, call NEXT_ITEM_TYPE.
Пакет DBMS_ALERT поддерживает отправку и получение асинхронных уведомлений о событиях (alerts). Это могут быть уведомления об изменении данных в БД, отправленные триггером, или об окончании выполнения некоторой процедуры. Приложение в отдельном сеансе ожидает уведомления, на которые подписалось, и обрабатывает их тем или иным образом, например, отражая наступившие события в пользовательском интерфейсе или выполняя операции с данными, зависящие от наступления события.
Вот основные свойства уведомлений DBMS_ALERT , почерпнутые мной из официальной документации:
Во втором сеансе отправим уведомление myalert и вернемся к первому сеансу, чтобы увидеть результат.
- один сеанс посылает уведомления при помощи DBMS_ALERT.SIGNAL и COMMIT .
- другой сеанс
- подписывается на уведомления при помощи DBMS_ALERT.REGISTER ,
- ожидает уведомления при помощи DBMS_ALERT.WAITONE (или WAITANY ) и обрабатывает их,
- удаляет подписку на уведомления, когда в них больше нет необходимости.
Попробую отправлять разные уведомления из нескольких параллельных сеансов и получать эти уведомления в другом сеансе.
Для этого создам процедуру signaller , которая будет посылать 10 уведомлений bang или boom , выбирая из двух случайным образом (строка 8 ниже). Отправив уведомление, процедура спит случайное число секунд между 1 и 7, имитируя занятость, и затем отправляет следующее уведомление. Для создания процедуры текущий пользователь должен явно (не через роль) получить привилегии EXECUTE на пакеты SYS.DBMS_ALERT и SYS.DBMS_LOCK .
Для получения уведомлений bang и boom создам процедуру consumer с параметром p_sleep - числом секунд между вызовами DBMS_ALERT.WAITANY . На это время consumer будет делать паузы между ожиданиями уведомлений, что приведет к потере некоторых уведомлений, если они отправляются достаточно часто. По умолчанию p_sleep равен 0, что практически устраняет паузы между ожиданиями, и уведомления не должны теряться.
Каждый раз процедура ожидает уведомления не более 10 секунд - см. значение параметра timeout при вызове DBMS_ALERT.WAITANY в строке 11.
Теперь, с помощью DBMS_SCHEDULER , я запущу процедуру signaller параллельно в двух сеансах и процедуру consumer в текущем сеансе:
Как видим, в текущем сеансе приняты все отправленные уведомления. Повторим эксперимент, введя паузу в 10 секунд между ожиданиями уведомлений:
На этот раз часть уведомлений была потеряна, чего и следовало ожидать.
В официальной документации по СУБД Oracle 11gR2 можно подробно познакомиться со всеми процедурами и функциями DBMS_ALERT . А я перейду к экспериментам с пакетом DBMS_PIPE , удалив ненужные теперь процедуры:
Пакет DBMS_PIPE позволяет двум или более сеансам пересылать друг другу данные по именованным каналам (pipes). Вот основные сведения о DBMS_PIPE :
Следующий PL/SQL блок открывает три канала - частный явный, публичный явный и публичный неявный:
Итак, каналы были созданы. Теперь удалю созданные каналы, воспользовавшись DBMS_PIPE.REMOVE_PIPE для явных каналов и прочитав данные из неявного:
Как видим, после удаления каналы остались во вью v$db_pipes . Однако, вызов DBMS_PIPE.REMOVE_PIPE сбросил в 0 размеры каналов и изменил тип канала my_private_pipe с PRIVATE на PUBLIC . Не совсем то, чего можно было ожидать! При этом вызовы функции DBMS_PIPE.REMOVE_PIPE вернули статус 0, следовательно, выполнились без ошибок. В утешение остается заметить, что вью v$db_pipes не упоминается в документации по пакету DBMS_PIPE . И нет необходимости в него смотреть.
Завершая разговор о DBMS_PIPE , замечу, что не все мои эксперименты с этим пакетом прошли гладко и привели к ожидаемому результату. Кто заинтересовался, может подробнее познакомиться с процедурами и функциями DBMS_PIPE по официально документации по СУБД Oracle и продолжить эксперименты.
Процент попаданий в буфер блока данных (кэш данных)
если процент ниже 95, то стоит увеличить размер DB_CACHE_SIZE
или оптимизировать запросы вызывающие много считываний с диска.
select 1- (sum(decode(name, 'physical reads',value,0))/
(sum(decode(name, 'db block gets',value,0)) +
(sum(decode(name, 'consistent gets',value,0)))))
"Read Hit Ratio"
from v$sysstat;
Покажет, как увеличение или уменьшение кэша скажется на процессе попаданий
(эффект от увеличения или уменьшения кэша данных)
select size_for_estimate, buffers_for_estimate,
estd_physical_read_factor, estd_physical_reads
from v$db_cache_advice
where name='DEFAULT'
and block_size=
(select value
from v$parameter
where name='db_block_size' )
and advice_status='ON';
Процент попадания в словарный кэш
Если меньше 95, то стоит задуматься над увеличением SHARED_POOL_SIZE
select sum(gets), sum(getmisses),
(1-(sum(getmisses)/(sum(gets)+ sum(getmisses))))* 100 HitRate
from v$rowcache;
Процент попаданий для кэша разделяемых SQL-запросов
и процедур на языке PL/SQL
процент попаданий при выполнении PinHitRatio должен быть не менее 95
процент попаданий при загрузке RelHitRatio должен быть не менее 99
select sum(pins) "Executions",
sum(pinhits) "Hits",
((sum(pinhits)/sum(pins))* 100) "PinHitRatio",
sum(reloads) "Misses",
((sum(pins)/(sum(pins) + sum(reloads)))* 100) "RelHitRatio"
from v$librarycache
Объекты PL/SQL, которые следует закрепить в памяти
(объекты, для хранения которых нужно больше 100 Кбайт)
select name, sharable_mem
from v$db_object_cache
where sharable_mem>100000
and type in ('PACKAGE','PACKAGE_BODY','FUNCTION','PROCEDURE')
and kept='NO';
Сессии, наиболее интенсивно использующие процессорное время
Сессии, наиболее интенсивно использующие ресурсы процессора,
можно определить несколькими способами:
- При помощи команды top операционной системы UNIX.
- С использованием динамической статистики Oracle,
выполнив следующий запрос:
Следует учитывать, что в данном запросе интересует не большое
значение статистики само по себе, а скорость ее роста в данный
момент времени. Связано это с тем, что сессия, функционирующая в
течение длительного периода времени, при незначительном приросте,
могла в сумме использовать большое количество процессорного времени.
Повторный разбор SQL-предложений
select name, value from v$sysstat
where name in (
'parse time cpu',
'parse time elapsed',
'parse count (hard)'
);
SQL-предложения, подвергающиеся наиболее частым разборам
select sql_text, parse_calls, executions
from v$sqlarea
order by parse_calls desc;
О том, что курсоры не разделяются между сессиями, свидетельствуют
большие и постоянно растущие значения поля VERSION_COUNT:
select sql_text, version_count
from v$sqlarea order by version_count desc;
SQL-предложения, наиболее интенсивно выполняющие обращения к блокам
данных:
select address, hash_value, buffer_gets, executions,
buffer_gets/executions "gets/exec", sql_text
from v$sqlarea
where executions > 0
order by 3 desc;
(следует обращать внимание на SQL-предложения с большим
отношением gets/exec или значением buffer_gets)
Интенсивные согласованные чтения
Система может тратить большое количество ресурсов на формирование
согласованного представления информации. Наиболее часто это происходит
в следующих ситуациях:
- В системе работают длительный запрос и множество маленьких транзакций,
выполняющих добавление данных и обращающихся к одной и той же таб-лице.
При этом запросу требуется откатывать большое количество измене-ний для
обеспечения согласованности чтения.
- Если мало число сегментов отката, то система может тратить много времени
на откат таблицы транзакций. Если запрос работает в течение длительного
времени, то поскольку число сегментов отката и таблиц транзакций слишком
мало, ваша система вынуждена часто повторно использовать слоты транзакций.
- Система сканирует слишком много буферов для того чтобы найти свободный.
Необходимо увеличить интенсивность скидывания буферов на диск процессом
DBWRn. Кроме этого можно увеличить размер кэша буферов для уменьшения
нагрузки для DBWR. Для нахождения среднего количества буферов, которое
необходимо просмотреть в списке LRU (Least Reasently Used) для нахождения
свободного буфера, необходимо использовать сле-дующий запрос:
select 1+sum(decode(name, 'free buffer inspected', value, 0)) /
sum(decode(name, 'free buffer requested', value, 0))
from v$sysstat
where name in (
'free buffer inspected',
'free buffer requested'
);
Результат должен быть равен в среднем 1-му или 2-м блокам.
Если количество блоков больше, то необходимо увеличить кэш буферов
или настроить процессы DBWRn.
Аналогично следует поступать, если велико среднее количество "грязных"
буферов в конце списка LRU:
select * from v$buffer_pool_statistics;
Для определения наиболее частых причин ожидания необходимо выполнить
следующий запрос:
select * from v$system_event
where event != 'Null event' and
event != 'rdbms ipc message' and
event != 'pipe get' and
event != 'virtual circuit status' and
event not like '%timer%' and
event not like 'SQL*Net % from %'
order by time_waited desc;
Обращать внимание следует на события с наибольшими временами ожидания.
Наиболее часто встречающиеся причины ожиданий:
- Buffer busy wait - данное событие обычно возникает, если несколько
сессий пытаются прочитать один и тот же блок, или одна или несколько
сессий ожидают окончания изменения одного блока. Конкуренция за блок
корректируется в зависимости от типа блока:
- Уменьшите количество строк в блоке путем изменения параметров
pctfree/pctused или уменьшением BD_BLOCK_SIZE.
- Проверьте на наличие right.hand.indexes (индексов, в которые добавляются
данные многими процессами в одну точку). Возможно, следует использовать
индексы с обратными ключами.
- Увеличьте количество freelists.
- Увеличьте размер экстентов для таблицы.
Заголовок сегмента отката:
- Добавьте больше сегментов отката для уменьшения количества транзакций
на сегмент.
- Уменьшите значение параметра TRANSACTION_PER_ROLLBACK_SEGMENT.
Блок сегмента отката:
- Увеличьте сегмент отката.
- Free buffer wait - обычно возникает, если процесс DBWR не справляется
с записью блоков на диск. Необходимо увеличить его пропускную способность.
- Latch free - конкуренция за доступ к защелкам. При помощи следующего
запроса можно определить защелки, которые ожидают активные сессии в
данный момент времени:
Конкуренция за доступ к защелкам
Одной из причин простоя процессов может быть конкуренция за доступ
к защелкам. Защелка - это внутренняя структура данных Oracle,
контролирующая доступ к объектам, находящимся в SGA (System Global Area).
О возникновении конкуренции за доступ к защелкам сигнализирует появление
сессий с ожиданием события "latch free" в динамическом представлении
V$SESSION_WAIT и соответственно рост статистики ожидания "latch free"
в V$SESSION_EVENT.
- Статистика по ожиданиям защелок в системе:
select * from v$system_event where event = 'latch free';
- Текущие ожидания защелок:
select * from v$session_wait where event = 'latch free';
- Защелки, доступ к которым ожидают процессы в текущий момент времени:
Выявить возникновение конкуренции за доступ к защелкам в системе поможет
скрипт response_time_breakdown.sql.
Наиболее часто встречающиеся причины ожиданий:
- Сache buffers chains - данная защелка запрашивается при поиске блока
данных, кэшированного в SGA. Поскольку буферный кэш представляет собой
последовательность блоков, каждая последовательность защищается защелкой,
которая является дочерней для данной защелки. Конкуренция за доступ к
данной защелке вызывается очень активным доступом к одному блоку, и обычно
требует для исправления переписывания приложения. Определить блоки данных
в кэше буферов, при обращении к которым возникают задержки, поможет
следующий запрос:
Cущественное уменьшение количества ожиданий защелки данного типа
можно выставлением скрытого параметра базы данных
Недостаточность ресурсов памяти
Разделяемый буфер (shared pool)
- Коэффициент попадания в библиотечный кэш, должен быть близок к 1:
select (sum(pins - reloads)) / sum(pins) "lib cache"
from v$librarycache;
- Коэффициент попадания в словарный кэш (содержащий библиотечные данные),
коэффициент должен быть близок к 1:
select (sum(gets - getmisses - usage - fixed)) / sum(gets) "row cache"
from v$rowcache;
- Свободное место в разделяемом кэше:
select * from v$sgastat where name = 'free memory';
- Коэффициент не попадания в библиотечный кэш:
select sum(pins) "executions",
sum(reloads) "cache misses while executing",
sum(reloads)/sum(pins) "miss rate"
from v$librarycache;
Кэш буферов (buffer cache)
- Коэффициент попаданий в буфер должен быть больше 0.9:
select name, 1-physical_reads/(db_block_gets+consistent_gets) "Hits"
from v$buffer_pool_statistics;
- Коэффициент ожидания LRU защелок при обращении к кэшу буферов:
Кэш журналов регистраций (redo log buffer)
Количество промахов при обращении к кэшу журналов регистрации:
select name, value
from v$sysstat
where name = 'redo buffer allocation retries';
Области сортировки (sort areas)
Количество сортировок на диске и в памяти:
select name, value
from v$sysstat
where name in ('sorts (memory)', 'sorts (disk)');
Конкуренция за доступ к ресурсам
Конкуренция за сегменты отката
Количество ожиданий доступа к сегментам отката не должно превышать 1%.
Если коэффициент ожиданий выше, то необходимо увеличить количество
сегментов отката:
select w.class, w.count/s.value "Rate"
from v$waitstat w,
( select sum(value) value from v$sysstat
where name in ('db block gets', 'consistent gets')) s
where w.class in (
'system undo header',
'system undo block',
'undo header',
'undo block');
Какие таблицы и индексы стали причиной высокого ожидания события buffer busy waits
select
OWNER ,
OBJECT_NAME ,
OBJECT_TYPE ,
TABLESPACE_NAME
from v$segment_statistics
where statistic_name = 'buffer busy waits'
order by value desc
Долго выполняющиеся транзакции:
select hash_value, executions,
round(elapsed_time/1000000,2) total_time,
round(cpu_time/1000000,2) cpu_seconds
from (select * from v$sql order by elapsed_time desc)
Далее по hash_value находим план
select * from v$sql_plan
where hash_value = 3287028449
Просмотр SQL - кода сеанса
Monitor long running operations using v$session_longops
Как посмотреть план запроса:
Запросы потребляющие ресурсы CPU:
select substr(sql_text,1,40) sql_text, hash_value, cpu_time
from v$sql
where cpu_time > 10000000
order by cpu_time;
Или любой другой запрос:
SELECT hash_value, address, child_number, sql_id, plan_hash_value
FROM v$sql
WHERE sql_text LIKE '%select sysdate from dual%'
AND sql_text NOT LIKE '%v_sql%';
По hash_value:
SELECT
lpad(' ', 2 * level) || pt.operation || ' ' || pt.options || ' ' || pt.object_name "Plan Access Path",
pt.cost,
pt.cardinality,
pt.bytes,
pt.cpu_cost,
pt.io_cost,
pt.temp_space,
pt.access_predicates,
pt.filter_predicates,
pt.qblock_name as "Query Block name"
FROM (select *
from v$sql_plan
where HASH_VALUE = 2343063137
and ADDRESS = '00007FF7C88C4128'
and CHILD_NUMBER = 0
) pt
CONNECT BY PRIOR pt.id = pt.parent_id
START WITH pt.id = 0;
Или по sql_id:
SELECT
lpad(' ', 2 * level) || pt.operation || ' ' || pt.options || ' ' || pt.object_name "Plan Access Path",
pt.cost,
pt.cardinality,
pt.bytes,
pt.cpu_cost,
pt.io_cost,
pt.temp_space,
pt.access_predicates,
pt.filter_predicates,
pt.qblock_name as "Query Block name"
FROM (select *
from v$sql_plan
where sql_id = '7h35uxf5uhmm1'
and child_number = 0
) pt
CONNECT BY PRIOR pt.id = pt.parent_id
START WITH pt.id = 0;
Из AWR по sql_id и plan_hash_value:
SELECT lpad(' ', 2 * level) || pt.operation || ' ' || pt.options || ' ' ||
pt.object_name "Query Plan",
pt.cost,
pt.cardinality,
pt.bytes,
pt.cpu_cost,
pt.io_cost,
pt.temp_space,
pt.access_predicates,
pt.filter_predicates,
pt.qblock_name
FROM (select *
from dba_hist_sql_plan
where sql_id = '7h35uxf5uhmm1'
and plan_hash_value = 1388734953
) pt
CONNECT BY PRIOR pt.id = pt.parent_id
START WITH pt.id = 0
В 11g из v$sql_plan_monitor :
SELECT lpad(' ', 2 * level) || pt.plan_operation || ' ' || pt.plan_options || ' ' ||
pt.plan_object_name "Query Plan",
pt.plan_cost,
pt.plan_cardinality,
pt.plan_bytes,
pt.plan_cpu_cost,
pt.plan_io_cost,
pt.plan_temp_space,
pt.starts,
pt.output_rows,
pt.workarea_mem,
pt.workarea_tempseg,
pt.physical_read_requests,
pt.physical_write_requests
FROM (select *
from v$sql_plan_monitor pt
where sql_id = '7h35uxf5uhmm1'
-- and sql_plan_hash_value = :sql_plan_hash_value
-- and sid = :sid
-- and sql_exec_id = :sql_exec_id
and status = 'EXECUTING') pt
CONNECT BY PRIOR pt.plan_line_id = pt.plan_parent_id
START WITH pt.plan_line_id = 0
Также можно получить отчет:
SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SET LINESIZE 1000
SET PAGESIZE 0
SET TRIM ON
SET TRIMSPOOL ON
SET ECHO OFF
SET FEEDBACK OFF
Читайте также: