# HG changeset patch
# User Oleksandr Gavenko <gavenkoa@gmail.com>
# Date 1518283440 -7200
# Node ID 2813a509381c33c0a58ef1f5b5d850f75291d60b
# Parent  9e6ad6607a9e9a04d6af08bbf5af830f5bd17380
Common client options. Sql*Plus client options.

diff -r 9e6ad6607a9e -r 2813a509381c oracle.rst
--- a/oracle.rst	Sat Feb 10 01:49:07 2018 +0200
+++ b/oracle.rst	Sat Feb 10 19:24:00 2018 +0200
@@ -29,10 +29,8 @@
 
 Useful PL/SQL stubs
 ===================
-::
 
-  set autotrace on statistics;
-  set timing on;
+Declare executable block::
 
   declare
   begin
@@ -49,14 +47,6 @@
   end;
   /
 
-Enabling printing::
-
-  set serveroutput on;
-  exec DBMS_OUTPUT.PUT_LINE('Hello');
-  exec DBMS_OUTPUT.DISABLE();
-  exec DBMS_OUTPUT.PUT_LINE('Silence');
-  exec DBMS_OUTPUT.ENABLE();
-
 Call procedure::
 
   create or replace procedure MY_PROC as
@@ -85,6 +75,66 @@
   end;
   /
 
+Common client options
+=====================
+
+Following options supports Sql*Plus, SQLcl, Oracle Developer.
+
+Enabling printing::
+
+  set serveroutput on;
+  exec DBMS_OUTPUT.PUT_LINE('Hello');
+  exec DBMS_OUTPUT.DISABLE();
+  exec DBMS_OUTPUT.PUT_LINE('Silence');
+  exec DBMS_OUTPUT.ENABLE();
+
+Enable printing of query execution time::
+
+  set timing on;
+
+Sql*Plus client options
+=======================
+
+Enable printing execution plan stats::
+
+  set autotrace on;
+  set autotrace off;
+  set autotrace on statistics;
+  set autotrace on explain;
+
+Disable printing of ``select`` result, show only stats::
+
+  set autotrace traceonly;
+  set autotrace traceonly on explain;
+
+Adjust date format.
+===================
+::
+
+  column parameter format a32;
+  column value format a32;
+  select parameter, value from v$nls_parameters;
+
+  alter session set NLS_DATE_FORMAT = 'yyyy-mm-dd HH:MI:SS';
+  alter session set NLS_TIMESTAMP_FORMAT = 'MI:SS.FF6';
+  alter session set NLS_TIME_FORMAT = 'HH24:MI:SS.FF6';
+
+  alter session set TIME_ZONE = '+06:00';
+
+  select sysdate from dual;
+
+Working with SQL/Plus
+=====================
+
+Show error details::
+
+  show errors;
+
+Modify output format::
+
+  set pagesize 40;
+  set linesize 400;
+
 Database info
 =============
 
@@ -319,11 +369,27 @@
 Profiling
 =========
 
+``explain plan for`` can be used to show probable execution plan for query::
+
+  explain plan for select 1 from dual;
+
+``plan_table`` should be examined to display result::
+
+  select * from table(dbms_xplan.display);
+
+To get actual execution plan run following after executing query::
+
+  select * from table(dbms_xplan.display_cursor);
+  select * from table(dbms_xplan.display_cursor(format => 'basic'));
+  select * from table(dbms_xplan.display_cursor(format => 'typical'));
+  select * from table(dbms_xplan.display_cursor(format => 'all'));
+
 Last queries' execution stats are available through ``v$sql`` view::
 
   grant select on v_$sql to BOB;
 
   select * from v$sql;
+  select * from v$sql_plan;
 
   select LAST_LOAD_TIME, ELAPSED_TIME, MODULE, SQL_TEXT elasped from v$sql
     order by LAST_LOAD_TIME desc
@@ -365,7 +431,7 @@
 Alternatively you may wrap you query with (and optionally use hint to disable
 optimizations??)::
 
-  select (*) from ( ... ORIGINAL QUERY ... );
+  select count(*) from ( ... ORIGINAL QUERY ... );
 
 Another option is::
 
@@ -403,7 +469,7 @@
 List of Oracle Reserved Words.
 ==============================
 
- * http://docs.oracle.com/cd/B19306_01/em.102/b40103/app_oracle_reserved_words.htm
+* http://docs.oracle.com/cd/B19306_01/em.102/b40103/app_oracle_reserved_words.htm
 
 Find time zone
 ==============
@@ -439,31 +505,3 @@
 Find time at timezone::
 
   select SYSTIMESTAMP at time zone 'GMT' from dual;
-
-Adjust date format.
-===================
-::
-
-  column parameter format a32;
-  column value format a32;
-  select parameter, value from v$nls_parameters;
-
-  alter session set NLS_DATE_FORMAT = 'yyyy-mm-dd HH:MI:SS';
-  alter session set NLS_TIMESTAMP_FORMAT = 'MI:SS.FF6';
-  alter session set NLS_TIME_FORMAT = 'HH24:MI:SS.FF6';
-
-  alter session set TIME_ZONE = '+06:00';
-
-  select sysdate from dual;
-
-Working with SQL/Plus.
-======================
-
-Show error details::
-
-  show errors;
-
-Modify output format::
-
-  set pagesize 40;
-  set linesize 400;