Pipe get oracle что это
Меня зовут Артём, я back-end разработчик на продукте WFM (Workforce Management). В компании наш продукт помогает в развитии процессов розничной сети, одна из основных наших задач — это построение рабочего графика для персонала розничного магазина.
В данной статье я хотел бы познакомить вас (в очередной раз, так как на хабре уже есть несколько статей, посвященных этой библиотеке) с Jooq и показать, как можно легко работать с хранимыми процедурами Oracle. Будет немного вводной части, а затем посмотрим примеры.
Почти в каждом продукте, скорее всего, есть база данных. В нашем случае это РСУБД Oracle, большая часть логики содержится внутри в виде хранимых процедур. Удобство взаимодействия с базой всегда было достаточно важной и щепетильной задачей для любого бэкендера, особенно, если речь идет о взаимодействии с хранимками, которые выдают и принимают "сложные" plsql-типы. Конвертация plsql-типа в java-тип — задачка не самая приятная, мягко говоря, и удобных инструментов в этом деле не так уж много.
Давайте кратко вспомним некоторые популярные подходы к взаимодействию с РСУБД, какими достоинствами и недостатками они обладают.
максимальный контроль над выполнением
очень трудоемкий подход
корректность sql и обработки типов целиком на разработчике
нет гарантий стыковки с контрактом БД (узнаем в runtime)
набор api из коробки по работе с сущностями, гарантирующих корректность sql
потеря контроля исполнения sql
нет гарантий стыковки с контрактом БД (узнаем в runtime) (исключение в случае, если используется code first подход и схема БД формируется по сущностям)
Spring Data JDBC
достаточно простой и удобный api для выполнения sql по сравнению с чистым jdbc
мы сами контролируем какой sql выполняется
корректность sql и обработки типов целиком на разработчике
нет гарантий стыковки с контрактом БД (узнаем в runtime)
В рамках данной статьи более корректно было бы сравнить подходы к работе с хранимыми процедурами и добавить в список Spring Simplejdbccall, но попробуем все же выделить преимущества Jooq в более широком смысле, нежели только работа с хранимками.
Как видно, инструментов предостаточно, зачем же нужна еще одна абстракция над БД?
Jooq – Java Object Oriented Querying
Что в основе?
Jooq generates Java code from your database and lets you build type safe SQL queries through its fluent API
Это основное отличие от других инструментов. Что же нам это дает? Фактически мы получаем сгенерированный клиент для взаимодействия с базой, как, например, клиенты, которые мы генерируем для wsdl-, openapi- спецификаций. Думаю, никто не станет отрицать удобство кодегенерации по контрактам.
Когда генерировать?
перегенерация всех классов при каждой сборке;
генерация классов только когда вам это нужно с хранением классов в репозитории.
Мы придерживаемся перегенерации классов при каждой сборке, в качестве проверки совместимости с контрактом базы данных.
Из чего генерировать?
по sql-скриптам (liquibase, flyway);
по доступным объектам из БД.
В своем продукте мы генерируем классы по доступным объектам БД, то есть на этапе сборки происходит коннект к базе, считывание доступных объектов и генерация java-классов по ним.
Библиотека предоставляет исчерпывающий список поддерживаемых РСУБД. Нашей компании больше всего интересен Oracle, тут с поддержкой тоже всё хорошо.
Есть поддержка самой свежей версии 21с. Для работы с Oracle библиотека предоставляется только на коммерческой основе.
table/view — когда мы напрямую обращаемся к табличкам или вьюшкам;
stored procedure — вызов хранимых процедур.
Во втором подходе возможны различные варианты хранимых процедур:
возвращающие примитивные типы (число, строка, дата);
возвращающие «сложные» pl/sql объекты (object, record);
возвращающие sys_refcursor (нетипизированный курсор);
возвращающие refcursor (типизированный курсор);
возвращающие коллекции (в том числе pipeline-функции).
Конечно, процедуры могут не только отдавать данные, но и принимать их на вход, причем входные данные тоже могут быть сколько угодно "сложными" по своей структуре. В примерах мы посмотрим преимущественно чтение данных из базы.
Constants
This is the maximum time to wait attempting to send or receive a message.
127.7 Summary of DBMS_PIPE Subprograms
This table lists the DBMS_PIPE subprograms and briefly describes them.
Table 127-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
127.7.1 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 127-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 127-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 127-5 CREATE_PIPE Function Exception
Permission error: Pipe with the same name already exists, and you are not allowed to use it.
127.7.2 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.
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:
Примеры
Самой важной частью примеров является показать простоту обращения к объектам базы, а также удобство и безопасность работы с типами, результаты вывода абсолютно не важны, но для наглядности кода я оставлю println. Показывая код сгенерированных классов, я буду оставлять только самую важную часть.
Начнем с создания объектов БД. Обычно у нас есть схема в базе, под которой ведется разработка (обзовем dev_user), и отдельная схема для приложения, которое ходит в базу с набором выданных грантов (обзовем app_user).
Я не буду подробно останавливаться на конфигурации приложения, оставлю лишь самые важные моменты. Основные зависимости:
Плагин, с помощью которого будем производить генерацию нужных классов по схеме БД:
В общем-то, всё что нужно (дополнительно используется junit и spring boot test для прогона тестов). Прогонять примеры я буду в обычном тестовом классе. Попрошу spring boot предоставить мне все необходимое для этого.
Давайте попробуем выполнить mvn clean compile (можно и напрямую дернуть плагин jooq-codeget, но я по привычке пойду сложным путём). Если всё было настроено корректно, то в логе мы увидим, как Jooq прицепился к базе, зачитал доступные объекты из словарей и сформировал лог о своей проделанной работе. В результате в папке target (ну или куда настроили) можно найти сгенерированные классы:
Там как раз наша вьюшка, к которой мы выдали гранты. Можно посмотреть, как выглядит класс :
В нём есть то, что нас интересует, — сгенерированный маппинг полей базы в поля java-класса: поле a number(2) -> Byte, поле b varchar2(5) -> String. Дальше остается только зачитать данные:
Получим данные из view:
Достаточно просто и что важно — нам не нужно думать о типах, обо всём позаботился Jooq.
Чуть более сложный пример с фильтрацией и группировкой:
Как видно, dsl Jooq очень похож на обычный sql. Record2 - в такую структуру Jooq запаковал строку результата, где Byte - это поле A, а Integer - count() по этому полю.
Перейдем к хранимым процедурам.
Запустим плагин jooq для перегенерации, получим:
Давайте посмотрим, что внутри:
Получение значения из функции:
Очень просто — вызов метода у нужного класса, который нам подготовили (я использую глобальное пространство имён для процедур и функций — Routines) .
Далее поработаем с хранимыми процедурами/функциями внутри пакета (как правило, логика инкапсулируется в пакетах, нежели в отдельных функциях), полная спецификация пакета:
Запустим перегенерацию объектов. Смотрим:
Видим наш оракловый пакет в виде класса DemoTestPkg, все хранимые процедуры внутри и все plsql-типы в виде java-классов.
Передача массива на вход и получение двух значений на выход
Здесь TIntsRecord это сгенерированная обёртка для нашего типа в пакете бд:
Дальше попробуем почитать курсоры из базы. В Oracle курсоры могут быть типизированные (ref_cursor) и нетипизированные (sys_refcursor).
Как видим, тут дела обстоят не очень хорошо, что, в общем-то, и понятно, мы пытаемся получить курсор произвольной структуры, и тут Jooq ничем не можем нам помочь — мы не можем обратиться к возвращаемым полям, они просто не известны. В такое случае мы вынуждены "ручками" разбирать каждое поле - result.get(0).get("VAL", Integer.class). Подход не очень — удобство и безопасность работы с типами теряется.
Хорошо, что есть типизированный курсор, в спецификации пакета явно указано, какие поля он возвращает:
Но увы, и с ref_cursor дела обстоят аналогично как и с sys_refcursor — информации о возвращаемых полей нет, Record — ничем не типизирован. Баг это или фича? У меня, к сожалению, руки пока не дошли написать вопрос в поддержку (забегая вперед, скажу, что мы редко практикуем передачу курсора).
А вот с получением коллекции, типизированной тем же record-ом, дела обстоят намного лучше:
Как видим, Jooq создал нам TTabRecord, по которому можно удобно пробегаться и обращаться к полям рекорда.
Мы в продукте как правило стараемся использовать получение коллекций через pipeline-функции — такой подход позволяет более бережно относиться к ресурсам БД, если коротко, то база будет отдавать результат по мере его формирования, а не накапливать целиком, как в прошлом примере.
С точки зрения приложения, вызов ничем не отличается от предыдущего примера:
Но отличия все же есть, их можно увидеть, если запустить наш код с флагом дебага Jooq:
В случае получения коллекции из БД мы увидим:
В случае обращения к pipeline-функции:
То есть вызов pipeline-функции это фактически выражение select * from table(function) с фетчем результата. Кстати, это еще и удобно использовать при отладке непосредственно в самом oracle — заходим с свою любимую ide для plsql, выполняем select, смотрим результат.
Попробуем передать сложный объект уровня схемы и принять обратно коллекцию сложных объектов, идем в базу:
Вызываем перегенерацию классов. Смотрим:
Функция появилась, все нужные типы созданы, остается только вызвать:
Разработчик освобожден от написания мапперов. Как видим, вызов хранимых процедур становится достаточно простой задачей, а работа с типами превратилась в одно удовольствие :)
127.6 DBMS_PIPE Examples
These examples show use of DBMS_PIPE in debugging PL/SQL, debugging Pro*C, executing system commands, and an external service interface.
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.
127.1 DBMS_PIPE Overview
Pipe functionality has several potential applications: external service interface, independent transactions, alerters (non-transactional), debugging, and concentrator.
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.
127.4 DBMS_PIPE 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:
Writing and Reading 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.
Reading and Writing 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.
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 .
127.2 DBMS_PIPE 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, which are described in DBMS_PIPE Operational Notes.
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
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.
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.
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.
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 .
127.5 DBMS_PIPE Exceptions
DBMS_PIPE package subprograms can return the errors listed in the following table.
Table 127-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.
Заключение
Какие тут преимущества?
Простота интеграции в продукт.
Не мешает использовать рядом другие технологии для работы с БД.
Обширная, подробная документация.
Релизы раз в полгода + оперативные фиксы.
Быстрые ответы на stackoverflow от создателя библиотеки.
Поддержка scala, groovy, kotlin.
Кодогенерация для стыковки с контрактом БД (проверка на compile time).
Простота вызова хранимых процедур.
Когда стоит точно использовать?
Если на продукте часто приходится работать с хранимыми процедурами.
Если есть потребность в написании sql, которого нет «из коробки» в «jpa-фреймворках».
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:
127.3 DBMS_PIPE Constants
This is the maximum time to wait attempting to send or receive a message.
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
SPOOL /temp/report_sql_monitor.htm
SELECT DBMS_SQLTUNE.report_sql_monitor(
sql_id => '7h35uxf5uhmm1',
type => 'HTML',
report_level => 'ALL') AS report
FROM dual;
SPOOL OFF
Пакет 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 и продолжить эксперименты.
Using DBMS_PIPE
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.
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.
Процент попаданий в буфер блока данных (кэш данных)
если процент ниже 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 - кода сеанса
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.
Читайте также: