Set define off oracle для чего
SQL*Plus - программа для работы с СУБД Oracle посредством командной строки. Используя SQL*Plus можно выполнять команды SQL и PL/SQL в интерактивном режиме или используя сценарий.
Основное преимущество SLQ*Plus - доступность, т.к. инструмент доступен практически всегда, когда в системе установлен Oracle. При этом это достаточно мощный инструмент, используя который можно решать различные задачи на удаленных машинах.
К написанию этой статьи меня подтолкнула книга "Oracle SQL*Plus: The Definitive Guide, 2nd Edition", написанная Jonathan Gennick. Как обычно, появилось желание систематизировать информацию и представить ее в удобном виде.
При этом сразу считаю нужным оговориться, что я использую SQLplus для написания и выполнения скриптов на удаленных машинах, в этой статье описываю именно используемые для этого команды.
Область возможного использования SQLplus при этом гораздо шире, например - построение отчетов, в том числе в формате HTML.
Настройки выполнения скриптов
Действуют на протяжении сессии в SQLplus.
Запомнить настройки перед выполнением скрипта и вернуть обратно по завершении выполнения:
This chapter provides information about how to tune SQL*Plus for better performance. It discusses the following topics:
For information about tuning Oracle Database, see the Oracle Database Performance Tuning Guide .
8.4 Execution Plan Output in Earlier Databases
Execution Plan output from Oracle Database 9 i Release 2 (9.2) or earlier is different.
Each line of the Execution Plan has a sequential line number. SQL*Plus also displays the line number of the parent operation.
The Execution Plan consists of four columns displayed in the following order:
Shows the line number of each execution step.
Shows the relationship between each step and its parent. This column is useful for large reports.
Shows each step of the report.
Shows database links or parallel query servers used.
The format of the columns may be altered with the COLUMN command. For example, to stop the PARENT_ID_PLUS_EXP column being displayed, enter
The Execution Plan output is generated using the EXPLAIN PLAN command.
When you trace a statement in a parallel or distributed query, the Execution Plan shows the cost based optimizer estimates of the number of rows (the cardinality). In general, the cost, cardinality and bytes at each node represent cumulative results. For example, the cost of a join node accounts for not only the cost of completing the join operations, but also the entire costs of accessing the relations in that join.
Lines marked with an asterisk (*) denote a parallel or remote operation. Each operation is explained in the second part of the report. See Distributed Transactions Concepts for more information on parallel and distributed operations.
The second section of this report consists of three columns displayed in the following order
Shows the line number of each execution step.
Describes the function of the SQL statement in the OTHER_PLUS_EXP column.
Shows the text of the query for the parallel server or remote database.
The format of the columns may be altered with the COLUMN command.
8.3 Tracing Parallel and Distributed Queries
When you trace a statement in a parallel or distributed query, the Execution Plan output depends on the statement you use.
To trace a parallel query running the parallel query option:
The following shows typical results:
If consistent gets or physical reads are high relative to the amount of data returned, it indicates that the query is expensive and needs to be reviewed for optimization. For example, if you are expecting less than 1,000 rows back and consistent gets is 1,000,000 and physical reads is 10,000, further optimization is needed.
You can also monitor disk reads and buffer gets using V$SQL or TKPROF.
8.2 About Collecting Timing Statistics
Use the SQL*Plus TIMING command to collect and display data on the amount of computer resources used to run one or more commands or blocks. TIMING collects data for an elapsed period of time, saving the data on commands run during the period in a timer.
See the TIMING command, and About Tracing Statements for information about using AUTOTRACE to collect statistics.
To delete all timers, enter CLEAR TIMING.
Выполнение PL/SQL блоков
Пример PL/SQL блока:
Правила выполнения PL/SQL блоков:
- Первое слово в PL/SQL блоке должно быть из списка: BEGIN, DECLARE, CREATE PROCEDURE,
CREATE FUNCTION, CREATE TRIGGER, CREATE PACKAGE, CREATE TYPE, CREATE TYPE BODY. Регистр не важен. - Блок может состоять из нескольких строк
- Можно вставлять /* комментарии */, они также могут быть на несколько строк
- Пустые строки не разрешены внутри блока
Сигнал к выполнению блока может быть подан двумя путями:
- Строка, содержащая только слеш "/" после блока - выполнить сразу
- Строка, содержащая точку "." после блока - поместить в буфер. Содержимое буфера может быть выведено командой LIST и выполнено, путем указания одиночного слеша "/" или команды RUN.
Одиночное выражение PL/SQL может быть выполнено, используя:
В случае ошибок, при выполнении процедуры, можно отобразить их выполнив команду:
8.1 About Tracing Statements
You can automatically get a report on the execution path used by the SQL optimizer and the statement execution statistics. The report is generated after successful SQL DML (that is, SELECT, DELETE, UPDATE and INSERT) statements. It is useful for monitoring and tuning the performance of these statements.
SQL*Plus report output may differ for DML if dynamic sampling is in effect.
8.1.1 Controlling the Autotrace Report
You can control the report by setting the AUTOTRACE system variable.
SET AUTOTRACE OFF
No AUTOTRACE report is generated. This is the default.
SET AUTOTRACE ON EXPLAIN
The AUTOTRACE report shows only the optimizer execution path.
SET AUTOTRACE ON STATISTICS
The AUTOTRACE report shows only the SQL statement execution statistics.
SET AUTOTRACE ON
The AUTOTRACE report includes both the optimizer execution path and the SQL statement execution statistics.
SET AUTOTRACE TRACEONLY
Like SET AUTOTRACE ON, but suppresses the printing of the user's query output, if any. If STATISTICS is enabled, query data is still fetched, but not printed.
To use this feature, you must create a PLAN_TABLE table in your schema and then have the PLUSTRACE role granted to you. DBA privileges are required to grant the PLUSTRACE role. For information on how to grant a role and how to create the PLAN_TABLE table, see the Oracle Database SQL Language Reference .
For more information about the roles and the PLAN_TABLE, see the Oracle Database SQL Language Reference and the AUTOTRACE variable of the SET command.
SQL*Plus AUTOTRACE does not support switching containers with the ALTER SESSION SET CONTAINER option. Statistical data gathered in this case may be inconsistent.
Run the following commands from your SQL*Plus session to create the PLAN_TABLE in the HR schema:
Run the following commands from your SQL*Plus session to create the PLUSTRACE role and grant it to the DBA:
Run the following commands from your SQL*Plus session to grant the PLUSTRACE role to the HR user:
8.1.2 Execution Plan
The Execution Plan shows the SQL optimizer's query execution path. Execution Plan output is generated using EXPLAIN PLAN and DBMS_XPLAN.
8.1.3 Statistics
The statistics are recorded by the server when your statement executes and indicate the system resources required to execute your statement. The results include the following statistics.
Number of recursive calls generated at both the user and system level. Oracle Database maintains tables used for internal processing. When Oracle Database needs to make a change to these tables, it internally generates an internal SQL statement, which in turn generates a recursive call.
Number of times a CURRENT block was requested.
Number of times a consistent read was requested for a block
Total number of data blocks read from disk. This number equals the value of "physical reads direct" plus all reads into buffer cache.
Total amount of redo generated in bytes
bytes sent through Oracle Net Services to client
Total number of bytes sent to the client from the foreground processes.
bytes received through Oracle Net Services from client
Total number of bytes received from the client over Oracle Net.
Oracle Net Services round-trips to/from client
Total number of Oracle Net messages sent to and received from the client
Number of sort operations that were performed completely in memory and did not require any disk writes
Number of sort operations that required at least one disk write
Number of rows processed during the operation
The client referred to in the statistics is SQL*Plus. Oracle Net refers to the generic process communication between SQL*Plus and the server, regardless of whether Oracle Net is installed. You cannot change the default format of the statistics report.
For a more complete list of database statistics, see Statistics Descriptions. For more information about the statistics and how to interpret them, see Gathering Database Statistics.
This option is useful when you are tuning a large query, but do not want to see the query report.
Your output may vary depending on the server version and configuration.
If the SQL buffer contains the following statement:
The statement can be automatically traced when it is run:
To trace the same statement without displaying the query data, enter:
Работа с переменными
Переменные могут быть заданы двумя способами:
Если уже была определена &&variable, то значение будет подставлено во все дальнейшие переменные как &variable так и &&variable.
Если была определена &&variable, и скрипт запущен повтороно в ходе той же сессии работы с SQLplus - будет использовано старое значение переменной. Чтобы этого избежать - можно запрашивать интерактивный ввод в скрипте принудительно, испольтзуя команду:
ACCEPT можно использовать для валидации:
Для ввода дат в определенном формате:
SQL*Plus поддерживает четыре типа переменных: CHAR, NUMBER, BINARY_FLOAT, and BINARY_DOUBLE. При вводе с клавиатуры переменная будет типа CHAR.
Несмотря на это, можно использовать NEW_VALUE, чтобы задать числовую переменную, полученную как результат запроса.
Bind-переменные могут использоваться для передачи данных между PL/SQL и SQL блоками:
Присвоить bind-переменной значение &-переменной:
Вывести значение bind-переменной:
Присвоить &-переменной значение bind-переменной:
Получаем OUT-параметр процедуры в bind-переменную:
Условное выполнение в SQLplus:
Пример скрипта, принимающего несколько переменных на вход в формате c возможностью задать дефолтные значения:
8.4 Execution Plan Output in Earlier Databases
Execution Plan output from Oracle Database 9 i Release 2 (9.2) or earlier is different.
Each line of the Execution Plan has a sequential line number. SQL*Plus also displays the line number of the parent operation.
The Execution Plan consists of four columns displayed in the following order:
Shows the line number of each execution step.
Shows the relationship between each step and its parent. This column is useful for large reports.
Shows each step of the report.
Shows database links or parallel query servers used.
The format of the columns may be altered with the COLUMN command. For example, to stop the PARENT_ID_PLUS_EXP column being displayed, enter
The Execution Plan output is generated using the EXPLAIN PLAN command.
When you trace a statement in a parallel or distributed query, the Execution Plan shows the cost based optimizer estimates of the number of rows (the cardinality). In general, the cost, cardinality and bytes at each node represent cumulative results. For example, the cost of a join node accounts for not only the cost of completing the join operations, but also the entire costs of accessing the relations in that join.
Lines marked with an asterisk (*) denote a parallel or remote operation. Each operation is explained in the second part of the report. See Distributed Transactions Concepts for more information on parallel and distributed operations.
The second section of this report consists of three columns displayed in the following order
Shows the line number of each execution step.
Describes the function of the SQL statement in the OTHER_PLUS_EXP column.
Shows the text of the query for the parallel server or remote database.
The format of the columns may be altered with the COLUMN command.
8.5 About SQL*Plus Script Tuning
Most performance benefit comes from tuning SQL queries executed in a script. This is done with tools like SQL*Plus's AUTOTRACE command. It involves restructuring queries to make best use of the Oracle Database SQL optimizer. For information about Tuning SQL statements, see the Oracle Database Performance Tuning Guide .
The performance gains made by tuning SQL*Plus-specific commands are smaller, but could be important for some applications. The following system variables and commands can influence SQL*Plus performance.
8.5.1 COLUMN NOPRINT
COLUMN NOPRINT turns off screen output and printing of the column heading and all values selected for the column.
It is better to remove an unneeded column from a SELECT then it is to use COLUMN NOPRINT to stop it displaying. Removing the column from the query means the SQL engine does not need to process it, or need to transfer the column data back to SQL*Plus.
8.5.2 SET APPINFO OFF
Sets automatic registering of scripts through the DBMS_APPLICATION_INFO package. Setting APPINFO OFF prevents administrators monitoring the performance and resource usage of scripts.
If many SQL scripts are being called, then turning APPINFO OFF stops internal SQL*Plus calls to the database DBMS_APPLICATION_INFO package.
8.5.3 SET ARRAYSIZE
Sets the number of rows that SQL*Plus will fetch from the database at one time. Valid values are 1 to 5000.
The effectiveness of setting ARRAYSIZE depends on how well Oracle Database fills network packets and your network latency and throughput. In recent versions of SQL*Plus and Oracle Database, ARRAYSIZE may have little effect. Overlarge sizes can easily take more SQL*Plus memory which may decrease overall performance.
8.5.4 SET DEFINE OFF
SET DEFINE OFF disables the parsing of commands to replace substitution variables with their values.
8.5.5 SET FLUSH OFF
SET FLUSH OFF enables the operating system to buffer output. ON disables buffering and flushes output to the screen. Any benefit from setting FLUSH either ON or OFF depends on your operating system and data. The gain may be marginal.
Use OFF only when you run a script that does not require user interaction and whose output you do not need to see until the script finishes running.
8.5.6 SET LINESIZE
SET LINESIZE sets the total number of characters that SQL*Plus displays on one line before beginning a new line.
Keep LINESIZE as small as possible to avoid extra memory allocations and memory copying.
However, if LINESIZE is too small, columns that cannot fit next to each other are put on separate lines. This may reduce performance significantly.
8.5.7 SET LONGCHUNKSIZE
SET LONGCHUNKSIZE sets the size of the increments SQL*Plus uses to retrieve a BLOB , BFILE, CLOB , LONG , NCLOB or XMLType value.
Experiment with different sizes if LONGS or LOBs are being fetched.
8.5.8 SET PAGESIZE
Sets the number of lines on each page of output.
Increase PAGESIZE to avoid printing headings frequently, or set it to 0 to prevent headings being displayed.
8.5.9 SET SERVEROUTPUT
SET SERVEROUTPUT OFF suppresses the display output ( DBMS_OUTPUT.PUT_LINE) of stored procedures or PL/SQL blocks in SQL*Plus.
Setting SERVEROUTPUT OFF stops internal SQL*Plus calls to the DBMS_OUTPUT package done after user SQL statements.
8.5.10 SET SQLPROMPT
Sets the SQL*Plus command prompt.
Use the default prompt, "SQL> " to stop variable substitution occurring each time the prompt is displayed.
8.5.11 SET TAB
Determines how SQL*Plus formats white space in terminal output.
Setting TAB ON causes multiple spaces to be compressed in terminal output. Unless this significantly reduces the written data, the processing required may marginally outweigh any benefit.
8.5.12 SET TERMOUT
SET TERMOUT OFF suppresses the display so that you can spool output from a script without seeing it on the screen.
If both spooling to file and writing to terminal are not required, use SET TERMOUT OFF in SQL scripts to disable terminal output.
8.5.13 SET TRIMOUT ON SET TRIMSPOOL ON
SET TRIMOUT ON or SET TRIMSPOOL ON removes trailing blanks at the end of each displayed or spooled line.
Setting these variables ON can reduce the amount of data written. However, if LINESIZE is optimal, it may be faster to set the variables OFF. The SQL*Plus output line is blank filled throughout the query processing routines so removing the spaces could take extra effort.
8.5.14 UNDEFINE
Deletes substitution variables that you defined either explicitly (with the DEFINE command) or implicitly (with an argument to the START command or COLUMN NEW_VAL|OLD_VAL).
Use the UNDEFINE command to remove unnecessary substitution variables. This can reduce the time taken for any operation that uses '&', new_value or old_value variables.
Здесь собраны примеры, демонстрирующие некоторые из полезных возможностей Oracle SQL*Plus. Все приведенные скрипты выполнены в SQL*Plus Release 11.2.0.1.0 в консоли Windows.
Подборка носит отчасти ностальгический характер, поскольку в последние годы всю повседневную оперативную работу с СУБД Oracle я делаю в PL/SQL Developer. Однако, SQL*Plus остается незаменимым для работы в консоли Unix/Linux и из shell-скриптов. Пример в конце статьи - только для Unix/Linux.
Для упрощения демонстрации написаны несколько маленьких скриптов-"подпрограмм", которые в дальнейшем вызываются из других скриптов. Вот они:
Чтобы избежать этого "спама", будем запускать SQL*Plus в молчаливом (silent) режиме. Следующий скрипт использует приведенные выше скрипты-подпрограммы и демонстрирует работу в silent режиме:
Запускаю скрипт в silent режиме:
Переходим собственно к демонстрации полезных возможностей SQL*Plus.
Скрипт define.sql показывает, как определить, использовать и очистить переменные (substitution variables) в SQL*Plus, а также выводит значения встроенных переменных:
Следующий скрипт показывает, что делать, если необходимо использовать символ & буквально (как символ):
Как видим, в первом случае SQL*Plus запросил значение для переменной good , интерпретировав символ & как префикс имени переменной, значение которой необходимо подставить. А во втором случае, после выполнения директивы set define off , символ & был использован буквально.
Следующий скрипт вычисляет значение выражения, введенного пользователем, и выводит результат.
Пользователь может ввести любое допустимое выражение SQL, в том числе, использовать функции SQL. Узнаем, например, последнюю дату текущего месяца:
Вычисляемые или извлекаемые из базы данных значения SQL*Plus позволяет присвоить переменным, чтобы использовать эти значения далее по ходу скрипта. Как это делается, демонстрирует следующий скрипт:
Следующий скрипт демонстрирует вывод результата запроса к БД в файл. Принимаются меры, чтобы в файл не попало ничего лишнего: ни заголовки, ни счетчик извлеченных строк.
Только что выполненный скрипт - простейший прототип отчета, выводимого в текстовый файл. Другое применение данной техники - динамически сформировать скрипт и выполнить его. Например, следующий скрипт выдает привилегии на чтение всех таблиц текущей схемы указанному пользователю ( grantee ):
Выполню скрипт, сделав таблицы текущей схемы публично доступными:
По аналогии с данным скриптом легко создать скрипт, создающий синонимы для чужих таблиц, права на которые имеет текущий пользователь, или скрипт, противоположный данному, - отбирающий привилегии на все таблицы схемы у указанного пользователя.
А следующий пример демонстрирует передачу параметров в вызываемый скрипт:
Следующий скрипт демонстрирует прерывание работы SQL*Plus при возникновении ошибки SQL или ошибки операционной системы:
Вначале запущу скрипт, выполняющийся без ошибок, и проверю код возврата. Затем запущу whenever_error.sql и также проверю код возврата:
В заключение, приведу пример, невозможный в консоли Windows, но заурядный для консоли Unix/Linux. Команды для SQL*Plus в shell-скрипте удобно записывать, используя конструкцию документ-здесь. Например, следующий фрагмент shell-скрипта создает файл all_users.lst с именами всех пользователей базы данных:
Утилита SQL*Plus позволяет создавать командные файлы, запрашивающие у пользователя параметры и подставляющие их значение в команды по ходу выполнения. Для этого используются т.н. пользовательские переменные . Такие переменные определяются в командном файле, в частности, с помощью команды DEFINE . На пользовательские переменные можно ссылаться в командах, предваряя их имя символами & или && . Конструкцию & называют подставляемой переменной .
Команда DEFINE позволяет определить пользовательскую переменную строкового типа и задать ей значение типа CHAR , либо получить значение определенной или всех пользовательских переменных. Эта команда имеет следующий синтаксис:
Если текст, присваиваемый переменной, содержит пробелы или символы пунктуации, его надо брать в апострофы.
При вызове без значения, команда DEFINE выдает значение переменной. Команда DEFINE без параметров выдает значение всех пользовательских переменных SQL*Plus, включая системные. Рассмотрим пример:
Как видите, утилита SQL*Plus автоматически определяет ряд системных пользовательских переменных.
Для удаления как явно определенной пользовательской переменной, так и параметра командной строки сценария (см. описание команды START выше) используется команда UNDEFINE . После применения этой команды к переменной значение переменной теряется, и она становится неопределенной. Команда UNDEFINE имеет следующий простой синтаксис:
Продолжая предыдущий пример:
Подставляемую переменную можно использовать в любом месте команды SQL*Plus или SQL-оператора, кроме первого слова в командной строке (по первому слову утилита SQL*Plus определяет тип команды). Когда SQL*Plus встречает в командной строке подставляемую переменную, она подставляет значение этой переменной (запрашивая его, если переменная не определена). Используются подставляемые переменные для получения более гибких, интерактивных сценариев SQL*Plus. Рассмотрим простой пример:
Как видите, если в команде встречается не определенная явно ранее подставляемая переменная, SQL*Plus запрашивает ее значение. Затем на экран выдается вид команды до и после подстановки всех значений (это можно отключить с помощью команды SET VERIFY OFF ), и команда выполняется. В нашем примере мы подставили в команду конкретную функцию агрегирования, имя столбца, по которому выполняется агрегирование, и имя таблицы.
Обратите внимание, что если необходимо вставить значение подставляемой переменной перед не пробельным символом, необходимо указать точку ( . ) после имени переменной.
В ответ на запрос значения можно ввести любую строку, в том числе с пробелами. Если значение должно быть взято в апострофы и эти апострофы не указаны явно в команде с подставляемой переменной, необходимо будет ввести значение в апострофах.
SQL*Plus читает данные с клавиатуры, даже если входной и выходной потоки терминала перенаправлены в файлы. Если же сценарий запущен в пакетном режиме, данные читаются из соответствующего файла.
Если использовать одну и ту же подставляемую переменную с символом & в одной команде несколько раз, значение будет запрашиваться каждый раз заново:
Чтобы значение переменной запрашивалось только один раз, используется подстановка с двумя амперсантами ( && ):
Подстановка переменных выполняется и для позиционных параметров, переданных при вызове сценария. На эти параметры можно ссылаться как на &1 , &2 и т.д. Если значение для них в командной строке не передано, SQL*Plus запрашивает значения при вызове сценария. Подстановка позиционных параметров выполняется только при вызове сценария командами START (или ее сокращенными формами @ , @@ ).
Подставляемые переменные нельзя использовать в командах редактирования буфера SQL ( APPEND , CHANGE , DEL , INPUT ) и в других командах, где эта подстановка "не имеет смысла", в частности, в комментариях. Команды редактирования буфера считают символ & обычным и используют его буквально (см. пример выше).
В табл. 13 представлены системные установки, влияющие на подстановку пользовательских переменных.
Таблица 13. Системные установки, влияющие на подстановку переменных.
Установка | Описание |
SET DEFINE | Задает символ подстановки (вместо стандартного - & ) и позволяет включать и отключать подстановку. |
SET ESCAPE | Задает символ маскировки, позволяющий маскировать символ подстановки. Стандартным символом маскировки является обратная косая ( \ ). |
SET VERIFY | Позволяет включать и отключать выдачу каждой строки команды до и после подстановки пользовательских переменных. |
SET CONCAT | Задает символ, отделяющий имя подставляемой переменной от следующей непосредственно за ним строки. По умолчанию используется точка ( . ). |
Для выдачи на экран произвольного текста используется команда PROMPT со следующим синтаксисом:
Она выдает указанный текст или пустую строку (при вызове без параметров). Если необходимо выдать несколько строк, для каждой строки выполняется отдельная команда PROMPT .
Считать строку и запомнить ее в указанной пользовательской переменной определенного типа (выдавая, при необходимости, приглашение) позволяет команда ACCEPT со следующим синтаксисом:
Если указанная в команде ACCEPT пользовательская переменная не существует, SQL*Plus создает ее. Опции команды ACCEPT описаны в табл. 14.
Таблица 14. Опции команды ACCEPT.
Рассмотрим простой пример совместного использования команд PROMPT и ACCEPT . Пусть имеется командный файл splus1.sql со следующим содержимым:
Вот что происходит при его выполнении:
Команда PAUSE имеет следующий синтаксис:
Эта команда выдает пустую строку, затем строку текста, если он указан, или еще одну пустую строку, и ждет подтверждения от пользователя. Ввод эта команда ожидает с терминала (при интерактивном запуске), даже если входной и выходной потоки перенаправлены. При работе в пакетном режиме для продолжения необходимо наличие новой строки в файле, откуда берется входной поток.
Рассмотрим простой пример. Изменим файл splus1.sql следующим образом:
Вот что будет выдано при его выполнении:
Для получения результатов пришлось нажать клавишу Enter после вывода соответствующего приглашения.
Связываемые переменные - это создаваемые в SQL*Plus переменные, на которые можно ссылаться (как на хост-переменные) в блоках PL/SQL. Таким переменным можно, например, присваивать значения в блоках PL/SQL или использовать их значения во включенных в блоки SQL-операторах. Значения связываемых переменных можно затем выдавать в SQL*Plus.
Для создания связываемой переменной используется команда VARIABLE со следующим синтаксисом:
При вызове без параметров команда VARIABLE выдает список всех переменных, созданных в сеансе. Если указать только имя переменной, выдается информация только об этой переменной.
Связываемые переменные можно использовать как параметры хранимых процедур или непосредственно, в анонимных PL/SQL-блоках. Их нельзя использовать в команде COPY или присвоить им значение в SQL-операторах, не входящих в PL/SQL-блоки. Вместо связанной переменной, не получившей явно значения, при необходимости подставляется значение NULL .
Рассмотрим простой пример использования связываемых переменных:
Мы использовали связываемую переменную для передачи значения из одного оператора SQL в другой, так и не выдав его на экран. Чтобы значения используемых в команде связываемых переменных выдавались автоматически, необходимо выполнить команду SET AUTOPRINT ON .
Типы данных для связываемых переменных аналогичны соответствующим типам данных PL/SQL. Переменные типа REFCURSOR позволяют работать с курсорными переменными PL/SQL. Утилита SQL*Plus обрабатывает связываемые переменные такого типа особым образом. Курсор, соответствующий курсорной переменной, открывается явно, а закрывается после выдачи значения или при завершении сеанса. При выдаче значения на экран выдается результирующее множество соответствующего запроса. Рассмотрим пример:
Результаты, выдаваемые при показе значений связываемой переменной типа REFCURSOR можно форматировать так же, как и результаты выполнения SQL-оператора SELECT . Значение такой переменной выдается только один раз, - затем результирующее множество надо выбирать повторно.
Для явной выдачи на экран значения связываемой переменной используется команда PRINT .
Команда PRINT имеет следующий синтаксис:
Эта команда выдает текущее значение перечисленных связываемых переменных. При вызове без параметров выдаются значения всех связываемых переменных.
Продолжая предыдущий пример:
Утилита SQL*Plus позволяет автоматически получать отчет о способе выполнения оператора, выбранном оптимизатором SQL, а также статистическую информацию о выполнении. Этот отчет выдается после успешного выполнения операторов SELECT , INSERT , UPDATE и DELETE . Такой отчет полезен для контроля и настройки производительности этих операторов.
Для управления данным отчетом используется команда SET AUTOTRACE . Эта команда имеет пять опций:
OFF | Отчет не выдается. Это стандартный режим работы SQL*Plus. |
ON EXPLAIN | Выдается только выбранный оптимизатором план выполнения оператора. |
ON STATISTICS | Выдается только статистическая информация о выполнении оператора. |
ON | Выдается план выполнения запроса и статистическая информация о выполнении оператора. |
TRACEONLY | Выдается отчет о статистике, но не выдаются результаты выполнения оператора. Используется для трассировки запросов, возвращающих большие объемы данных. |
Для использования этой возможности SQL*Plus необходимо создать в схеме пользователя таблицу PLAN_TABLE и получить роль PLUSTRACE (предоставить ее может только DBA). Выполним следующие действия:
Как видите, по умолчанию эта возможность не поддерживается. Создадим таблицу с помощью сценария $ORACLE_HOME/rdbs/admin/utlxplan.sql :
Затем создадим роль PLUSTRACE, дадим ей необходимые привилегии, а затем предоставим ее роли DBA (с помощью сценария $ORACLE_HOME/sqlplus/admin/plustrce.sql ):
Теперь предоставим роль PLUSTRACE пользователю, который будет использовать трассировку:
Проверяем, что трассировочный отчет теперь выдается:
Подробнее использование возможностей трассировки в SQL*Plus рассмотрено в отдельном модуле, посвященном настройке производительности.
Среда SQL*Plus - очень гибкая и имеет широкие возможности настройки по требованиям пользователя. Эти настройки выполняются, в основном, с помощью команды SET . Мы неоднократно использовали команду SET в примерах данного модуля. В следующем разделе представлено ее формальное описание.
Команда SET позволяет установить системную переменную, изменяющую свойства среды SQL*Plus для текущего сеанса. Она имеет следующий синтаксис:
Имена, описания и возможные значения основных системных переменных для команды SET представлены в табл. 15.
8.5 About SQL*Plus Script Tuning
Most performance benefit comes from tuning SQL queries executed in a script. This is done with tools like SQL*Plus's AUTOTRACE command. It involves restructuring queries to make best use of the Oracle Database SQL optimizer. For information about Tuning SQL statements, see the Oracle Database Performance Tuning Guide .
The performance gains made by tuning SQL*Plus-specific commands are smaller, but could be important for some applications. The following system variables and commands can influence SQL*Plus performance.
8.5.1 COLUMN NOPRINT
COLUMN NOPRINT turns off screen output and printing of the column heading and all values selected for the column.
It is better to remove an unneeded column from a SELECT then it is to use COLUMN NOPRINT to stop it displaying. Removing the column from the query means the SQL engine does not need to process it, or need to transfer the column data back to SQL*Plus.
8.5.2 SET APPINFO OFF
Sets automatic registering of scripts through the DBMS_APPLICATION_INFO package. Setting APPINFO OFF prevents administrators monitoring the performance and resource usage of scripts.
If many SQL scripts are being called, then turning APPINFO OFF stops internal SQL*Plus calls to the database DBMS_APPLICATION_INFO package.
8.5.3 SET ARRAYSIZE
Sets the number of rows that SQL*Plus will fetch from the database at one time. Valid values are 1 to 5000.
The effectiveness of setting ARRAYSIZE depends on how well Oracle Database fills network packets and your network latency and throughput. In recent versions of SQL*Plus and Oracle Database, ARRAYSIZE may have little effect. Overlarge sizes can easily take more SQL*Plus memory which may decrease overall performance.
8.5.4 SET DEFINE OFF
SET DEFINE OFF disables the parsing of commands to replace substitution variables with their values.
8.5.5 SET FLUSH OFF
SET FLUSH OFF enables the operating system to buffer output. ON disables buffering and flushes output to the screen. Any benefit from setting FLUSH either ON or OFF depends on your operating system and data. The gain may be marginal.
Use OFF only when you run a script that does not require user interaction and whose output you do not need to see until the script finishes running.
8.5.6 SET LINESIZE
SET LINESIZE sets the total number of characters that SQL*Plus displays on one line before beginning a new line.
Keep LINESIZE as small as possible to avoid extra memory allocations and memory copying.
However, if LINESIZE is too small, columns that cannot fit next to each other are put on separate lines. This may reduce performance significantly.
8.5.7 SET LONGCHUNKSIZE
SET LONGCHUNKSIZE sets the size of the increments SQL*Plus uses to retrieve a BLOB , BFILE, CLOB , LONG , NCLOB or XMLType value.
Experiment with different sizes if LONGS or LOBs are being fetched.
8.5.8 SET PAGESIZE
Sets the number of lines on each page of output.
Increase PAGESIZE to avoid printing headings frequently, or set it to 0 to prevent headings being displayed.
8.5.9 SET SERVEROUTPUT
SET SERVEROUTPUT OFF suppresses the display output ( DBMS_OUTPUT.PUT_LINE) of stored procedures or PL/SQL blocks in SQL*Plus.
Setting SERVEROUTPUT OFF stops internal SQL*Plus calls to the DBMS_OUTPUT package done after user SQL statements.
8.5.10 SET SQLPROMPT
Sets the SQL*Plus command prompt.
Use the default prompt, "SQL> " to stop variable substitution occurring each time the prompt is displayed.
8.5.11 SET TAB
Determines how SQL*Plus formats white space in terminal output.
Setting TAB ON causes multiple spaces to be compressed in terminal output. Unless this significantly reduces the written data, the processing required may marginally outweigh any benefit.
8.5.12 SET TERMOUT
SET TERMOUT OFF suppresses the display so that you can spool output from a script without seeing it on the screen.
If both spooling to file and writing to terminal are not required, use SET TERMOUT OFF in SQL scripts to disable terminal output.
8.5.13 SET TRIMOUT ON SET TRIMSPOOL ON
SET TRIMOUT ON or SET TRIMSPOOL ON removes trailing blanks at the end of each displayed or spooled line.
Setting these variables ON can reduce the amount of data written. However, if LINESIZE is optimal, it may be faster to set the variables OFF. The SQL*Plus output line is blank filled throughout the query processing routines so removing the spaces could take extra effort.
8.5.14 UNDEFINE
Deletes substitution variables that you defined either explicitly (with the DEFINE command) or implicitly (with an argument to the START command or COLUMN NEW_VAL|OLD_VAL).
Use the UNDEFINE command to remove unnecessary substitution variables. This can reduce the time taken for any operation that uses '&', new_value or old_value variables.
This chapter provides information about how to tune SQL*Plus for better performance. It discusses the following topics:
For information about tuning Oracle Database, see the Oracle Database Performance Tuning Guide .
Параметры, подключение к базе, запуск скриптов
Выполнить несколько строк кода (не передавая отдельный файл со скриптом), unix:
8.2 About Collecting Timing Statistics
Use the SQL*Plus TIMING command to collect and display data on the amount of computer resources used to run one or more commands or blocks. TIMING collects data for an elapsed period of time, saving the data on commands run during the period in a timer.
See the TIMING command, and About Tracing Statements for information about using AUTOTRACE to collect statistics.
To delete all timers, enter CLEAR TIMING.
Выполнение SQL запросов
Запрос может состоять из нескольких строк, содержать комментарии, но не может содержать внутри пустые строки.
Запрос может быть выполнен тремя способами:
- точка с запятой в конце запроса
- строка с слешем "/" после запроса
- пустая строка после запроса (будет помещен в буфер, но не выполнен немедленно)
8.1 About Tracing Statements
You can automatically get a report on the execution path used by the SQL optimizer and the statement execution statistics. The report is generated after successful SQL DML (that is, SELECT, DELETE, UPDATE and INSERT) statements. It is useful for monitoring and tuning the performance of these statements.
SQL*Plus report output may differ for DML if dynamic sampling is in effect.
8.1.1 Controlling the Autotrace Report
You can control the report by setting the AUTOTRACE system variable.
SET AUTOTRACE OFF
No AUTOTRACE report is generated. This is the default.
SET AUTOTRACE ON EXPLAIN
The AUTOTRACE report shows only the optimizer execution path.
SET AUTOTRACE ON STATISTICS
The AUTOTRACE report shows only the SQL statement execution statistics.
SET AUTOTRACE ON
The AUTOTRACE report includes both the optimizer execution path and the SQL statement execution statistics.
SET AUTOTRACE TRACEONLY
Like SET AUTOTRACE ON, but suppresses the printing of the user's query output, if any. If STATISTICS is enabled, query data is still fetched, but not printed.
To use this feature, you must create a PLAN_TABLE table in your schema and then have the PLUSTRACE role granted to you. DBA privileges are required to grant the PLUSTRACE role. For information on how to grant a role and how to create the PLAN_TABLE table, see the Oracle Database SQL Language Reference .
For more information about the roles and the PLAN_TABLE, see the Oracle Database SQL Language Reference and the AUTOTRACE variable of the SET command.
SQL*Plus AUTOTRACE does not support switching containers with the ALTER SESSION SET CONTAINER option. Statistical data gathered in this case may be inconsistent.
Run the following commands from your SQL*Plus session to create the PLAN_TABLE in the HR schema:
Run the following commands from your SQL*Plus session to create the PLUSTRACE role and grant it to the DBA:
Run the following commands from your SQL*Plus session to grant the PLUSTRACE role to the HR user:
8.1.2 Execution Plan
The Execution Plan shows the SQL optimizer's query execution path. Execution Plan output is generated using EXPLAIN PLAN and DBMS_XPLAN.
8.1.3 Statistics
The statistics are recorded by the server when your statement executes and indicate the system resources required to execute your statement. The results include the following statistics.
Number of recursive calls generated at both the user and system level. Oracle Database maintains tables used for internal processing. When Oracle Database needs to make a change to these tables, it internally generates an internal SQL statement, which in turn generates a recursive call.
Number of times a CURRENT block was requested.
Number of times a consistent read was requested for a block
Total number of data blocks read from disk. This number equals the value of "physical reads direct" plus all reads into buffer cache.
Total amount of redo generated in bytes
bytes sent through Oracle Net Services to client
Total number of bytes sent to the client from the foreground processes.
bytes received through Oracle Net Services from client
Total number of bytes received from the client over Oracle Net.
Oracle Net Services round-trips to/from client
Total number of Oracle Net messages sent to and received from the client
Number of sort operations that were performed completely in memory and did not require any disk writes
Number of sort operations that required at least one disk write
Number of rows processed during the operation
The client referred to in the statistics is SQL*Plus. Oracle Net refers to the generic process communication between SQL*Plus and the server, regardless of whether Oracle Net is installed. You cannot change the default format of the statistics report.
For a more complete list of database statistics, see Statistics Descriptions. For more information about the statistics and how to interpret them, see Gathering Database Statistics.
This option is useful when you are tuning a large query, but do not want to see the query report.
Your output may vary depending on the server version and configuration.
If the SQL buffer contains the following statement:
The statement can be automatically traced when it is run:
To trace the same statement without displaying the query data, enter:
8.3 Tracing Parallel and Distributed Queries
When you trace a statement in a parallel or distributed query, the Execution Plan output depends on the statement you use.
If consistent gets or physical reads are high relative to the amount of data returned, it indicates that the query is expensive and needs to be reviewed for optimization. For example, if you are expecting less than 1,000 rows back and consistent gets is 1,000,000 and physical reads is 10,000, further optimization is needed.
You can also monitor disk reads and buffer gets using V$SQL or TKPROF.
To trace a parallel query running the parallel query option:
The following shows typical results:
Читайте также: