Sunday, January 7, 2018

Oracle Database 12.2 New Features - SQL* Plus Enhancements

I want to start a series of posts about Oracle Database 12.2 new features this new year 2018.
There’s no better start of this series than to start with SQL*Plus.

SQL*Plus is probably one of the most utilized tools by DBA’s (sqlcl is gaining steam) and here I show some really cool new features.

SQL*Plus History


With this command HIST[ORY] and if turned on; you can run, edit, delete or list previously used SQL or PL/SQL commands for the current session.
  

 $ sqlplus sys as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Fri Jan 5 12:28:58 2018

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

Enter password:

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> show history
history is OFF
SQL> set history on
SQL> show history
history is ON and set to "100"
SQL> set history 1000
SQL> show history
history is ON and set to "1000"
SQL> show user
USER is "SYS"
SQL> desc dual
 Name                                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 DUMMY                                                                 VARCHAR2(1)

SQL> select * from dual;

D
-
X

SQL> hist
  1  show history
  2  show user
  3  desc dual
  4  select * from dual;

SQL> hist 4 run

D
-
X

SQL>


SQL*Plus SET MARKUP CSV

This setting is going to present the output is CVS format. 

SQL> set markup csv on
SQL> select * from emp;

"EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO"
7839,"KING","PRESIDENT",,"17-NOV-81",5000,,10
7698,"BLAKE","MANAGER",7839,"01-MAY-81",2850,,30
7782,"CLARK","MANAGER",7839,"09-JUN-81",2450,,10
7566,"JONES","MANAGER",7839,"02-APR-81",2975,,20
7788,"SCOTT","ANALYST",7566,"19-APR-87",3000,,20
7902,"FORD","ANALYST",7566,"03-DEC-81",3000,,20
7369,"SMITH","CLERK",7902,"17-DEC-80",800,,20
7499,"ALLEN","SALESMAN",7698,"20-FEB-81",1600,300,30
7521,"WARD","SALESMAN",7698,"22-FEB-81",1250,500,30
7654,"MARTIN","SALESMAN",7698,"28-SEP-81",1250,1400,30
7844,"TURNER","SALESMAN",7698,"08-SEP-81",1500,0,30
7876,"ADAMS","CLERK",7788,"23-MAY-87",1100,,20
7900,"JAMES","CLERK",7698,"03-DEC-81",950,,30
7934,"MILLER","CLERK",7782,"23-JAN-82",1300,,10

14 rows selected.

SQL*Plus SET FEEDBACK ONLY


This option will display the number of rows selected without displaying the data. Useful to measure fetch time.

SQL> set feedback only
SQL> set timing on
SQL> select * from emp;

14 rows selected.

Elapsed: 00:00:00.01

SQL*Plus Performance Settings


SET ROWPREFECTH (default 1 | 2G max)
This setting pre-fetches rows in a result set. It can reduce the number of round trips between OCI execute calls.
I had an interesting question from a colleague about this setting. What is the difference between this ROWPREFECTH and ARRAYSIZE?
I’m still struggling to find the differences. Here are both definitions extracted from Oracle’s documentation:

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.

SET ROWPREFETCH {1 | n}

Sets the number of rows that SQL*Plus will prefetch from the database at one time.
The default value is 1.


What I can see from here is that ARRAYSIZE takes place during the fetch step and probably ROWPREFECTH is just before the fetch step?
I even tried to identify this by using the autotrace option and by tracing a test session, but unfortunately I was not able to find any differences. Maybe the amount of data queried wasn’t large enough.

$ sqlplus scott/****

SQL*Plus: Release 12.2.0.1.0 Production on Fri Jan 5 13:18:41 2018

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

Last Successful login time: Fri Jan 05 2018 13:15:53 -05:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> set autotrace on
SQL> select * from emp;

14 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time    |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |    14 |  1218 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMP  |    14 |  1218 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


Statistics
----------------------------------------------------------
            0  recursive calls
            0  db block gets
            8  consistent gets
            0  physical reads
            0  redo size
       1537  bytes sent via SQL*Net to client
          608  bytes received via SQL*Net from client
            2  SQL*Net roundtrips to/from client
            0  sorts (memory)
            0  sorts (disk)
           14  rows processed

SQL> set rowprefetch 10
SQL> select * from emp;

14 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time    |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |    14 |  1218 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMP  |    14 |  1218 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


Statistics
----------------------------------------------------------
            0  recursive calls
            0  db block gets
            8  consistent gets
            0  physical reads
            0  redo size
       1534  bytes sent via SQL*Net to client
          608  bytes received via SQL*Net from client
            2  SQL*Net roundtrips to/from client
            0  sorts (memory)
            0  sorts (disk)
           14  rows processed

SQL> set rowprefetch 5
SQL> select * from emp;

14 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time    |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |    14 |  1218 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMP  |    14 |  1218 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


Statistics
----------------------------------------------------------
            0  recursive calls
            0  db block gets
            8  consistent gets
            0  physical reads
            0  redo size
       1534  bytes sent via SQL*Net to client
          608  bytes received via SQL*Net from client
            2  SQL*Net roundtrips to/from client
            0  sorts (memory)
            0  sorts (disk)
           14  rows processed

SQL> set arraysize 5
SQL> select * from emp;

14 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time    |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |    14 |  1218 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMP  |    14 |  1218 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


Statistics
----------------------------------------------------------
            0  recursive calls
            0  db block gets
            8  consistent gets
            0  physical reads
            0  redo size
       1534  bytes sent via SQL*Net to client
          608  bytes received via SQL*Net from client
            2  SQL*Net roundtrips to/from client
            0  sorts (memory)
            0  sorts (disk)
           14  rows processed

SQL> set rowprefetch 1
SQL> select * from emp;

14 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time    |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |    14 |  1218 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMP  |    14 |  1218 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


Statistics
----------------------------------------------------------
            0  recursive calls
            0  db block gets
           10  consistent gets
            0  physical reads
            0  redo size
       1919  bytes sent via SQL*Net to client
          630  bytes received via SQL*Net from client
            4  SQL*Net roundtrips to/from client
            0  sorts (memory)
            0  sorts (disk)
           14  rows processed

As you can see the only visible changes are the SQL*Net roundtrips to/from client of the ARRAYSIZE setting.

SET LOBPREFETCH (default 0 bytes | 2G max)
Similar to ROWPREFECTH but for LOB data.

SET STATEMENTCACHE (default 0 | 32767 max)
This setting will cache similar SQL statements for the current session reducing the amount of necessary parses.

Thanks,

Alfredo

Sunday, September 17, 2017

Upload the OPatch that is of version "13.8.0.0.0" and platform "226" to the Software Library

I was having an issue trying to deploy a patch to an agent due to the version of OPatch available in the software library.

The error I got was “Upload the OPatch that is of version "13.8.0.0.0" and platform "226" to the Software Library”


The solution was to run the “OPatch Update” job available from the job console in OEM.




Choose “OPatch Update” as job type:



Type a name for the job:



And click submit:




After this the agent patching was successful.

Thanks,
Alfredo

“Failed to find the OPatch patch directory” while applying patch 25105555 ( 13.2.0.0.161231) from OEM console

While applying patch 25105555 ( 13.2.0.0.161231) to an agent using the "provisioning and Patching" console from OEM 13.2 and choosing the "Upgrade OPatch" option, got this error:

---------- Error Message ----------
Error: 010
Failed to find the OPatch patch directory "/tmp/p6880880_600000000057471_2000_0/oracle/OPatch".
If you chose "No" for the "StagePatches" option, ensure that you manually place the patch at the required location and retry the operation.
----------- End Message -----------

Tue Aug 27 15:41:48 2017 - Patching aborted.


You have to follow the solution section from below MOS note:

EM 13c: Applying EM-AGENT Bundle Patch 25105555 ( 13.2.0.0.161231) from Enterprise
Manager 13.2 Cloud Control Fails with Message: Error: 010 Failed to find the OPatch patch directory (Doc ID 2229452.1)

It depends on whether you have an online or offline OMS configuration.


After doing this I was able to apply patch 25105555 to the agent.

Hope this helps.


Alfredo