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

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;

SQL> hist

1 show history

2 show user

3 desc dual

4 select * from dual;

SQL> hist 4 run

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

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

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 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

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 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

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 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

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 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

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 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

Labels: 12.2 features, Database

Wednesday, April 1, 2015

Free Database Seminar in Timisoara, Romania

Today I want to talk about a free Database Seminar that ACI is sponsoring in Timisoara, Romania. A friend of mine and former co-worker asked me to speak in the seminar about Oracle Enterprise Manager Administration.

Because of the Time zone’s differences, I’m going to make a recording of the presentation and upload it to the blog, so he can show it there.

If you are nearby Timisoara, don’t loose the opportunity to assist and learn little bit more about databases.

The event is going to take place April, 8th at 5 pm @ City Business Centre, building D, Timisoara, RO.

Did I forget to mention that the event is for free?

For more information go to:

https://www.facebook.com/events/1035993103095436/

Thanks,

Alfredo

Labels: Database, Seminar

Sunday, July 27, 2014

Oracle Enterprise Manager – Reducing the noise, Part 1

Enterprise Manager 12c is a great monitoring tool, with it you can monitor a wide range of target types from databases to middleware; although out-of-the-box metrics can suit your monitoring requirements they can generate a considerable amount of white noise. In order to reduce this noise first you have to identify which are the top alerts in your system; Cloud Control comes with several predefined reports that help you to dig into multiple areas of your system, there’s a report “20 Most Common Alerts” which shows you the incidence of common alerts.

In the picture above, you can clearly see that metric “Database Time Spent Waiting (%)” appears twice in my Top 3, let’s find out our metric setting for my DB targets; in order to do this we must go to a DB home page then Oracle Database -> Monitoring -> Metrics and Collection Settings.

Wait a minute! Why I’m receiving alerts if there are no thresholds setup for any of those metrics?, this behavior is clearly explained in MOS note 1500074.1 about a default warning threshold of 30% inside the database configuration. Let’s take a look to dba_threshold to confirm.

set lines 300

column METRICS_NAME format a30

column WARNING_OPERATOR format a30

column WARNING_VALUE format a30

column CRITICAL_OPERATOR format a30

column CRITICAL_VALUE format a30

SELECT METRICS_NAME,WARNING_OPERATOR ,WARNING_VALUE,CRITICAL_OPERATOR ,CRITICAL_VALUE FROM DBA_THRESHOLDS;

METRICS_NAME WARNING_OPERATOR WARNING_VALUE CRITICAL_OPERATOR CRITICAL_VALUE

----------------------------------- ------------------------------ ------------------------------ ------------------------------ ------------------------------

Average Users Waiting Counts GT 10 NONE

Average Users Waiting Counts GT 30 NONE

Blocked User Session Count GT 0 NONE

Current Open Cursors Count GT 1200 NONE

Database Time Spent Waiting (%) GT 30 NONE

Database Time Spent Waiting (%) GT 50 NONE

Logons Per Sec GE 100 NONE

Session Limit % GT 90 GT 97

Tablespace Bytes Space Usage DO NOT CHECK 0 DO_NOT_CHECK 0

Tablespace Space Usage GE 85 GE 97

22 rows selected.

There you go!, all metrics for “Database Time Spent Waiting (%)” are set to 30% or 50% values, now the trick to disable these metrics is to set them to a different value like 99%; this will override the default value as follows:

Let’s look at the database setting again:

set lines 300

column METRICS_NAME format a30

column WARNING_OPERATOR format a30

column WARNING_VALUE format a30

column CRITICAL_OPERATOR format a30

column CRITICAL_VALUE format a30

METRICS_NAME WARNING_OPERATOR WARNING_VALUE CRITICAL_OPERATOR CRITICAL_VALUE

----------------------------------- ------------------------------ ------------------------------ ------------------------------ ------------------------------

Average Users Waiting Counts GT 10 NONE

Average Users Waiting Counts GT 30 NONE

Blocked User Session Count GT 0 NONE

Current Open Cursors Count GT 1200 NONE

Database Time Spent Waiting (%) GT 99 NONE

Logons Per Sec GE 100 NONE

Session Limit % GT 90 GT 97

Tablespace Bytes Space Usage DO NOT CHECK 0 DO_NOT_CHECK 0

Tablespace Space Usage GE 85 GE 97

25 rows selected.

We successfully modified these metrics to a very high value; at this point you can decide to stay at 99% or you can remove that threshold in order to completely disable them.

Now let’s confirm those settings in the database:

set lines 300

column METRICS_NAME format a30

column WARNING_OPERATOR format a30

column WARNING_VALUE format a30

column CRITICAL_OPERATOR format a30

column CRITICAL_VALUE format a30

METRICS_NAME WARNING_OPERATOR WARNING_VALUE CRITICAL_OPERATOR CRITICAL_VALUE

----------------------------------- ------------------------------ ------------------------------ ------------------------------ ------------------------------

Average Users Waiting Counts GT 10 NONE

Average Users Waiting Counts GT 30 NONE

Blocked User Session Count GT 0 NONE

Current Open Cursors Count GT 1200 NONE

Logons Per Sec GE 100 NONE

Session Limit % GT 90 GT 97

Tablespace Bytes Space Usage DO NOT CHECK 0 DO_NOT_CHECK 0

Tablespace Space Usage GE 85 GE 97

14 rows selected.

The metrics are not there anymore and hopefully the alerts neither. This behavior is also noted for “Average Users Waiting Counts” metric, if you are receiving considerable white noise for this metric you can disable as well following the same procedure. A good practice is to create a Monitoring template to help you modify these thresholds for multiple targets at once.

Stay tuned for my next post about reducing OEM 12c noise.

Thanks,

Alfredo

Labels: alerts, Database, metrics, OEM 12c, Oracle

Alfredo Krieg's DBA blog - Oracle ACE

Sunday, January 7, 2018

Oracle Database 12.2 New Features - SQL* Plus Enhancements

SQL*Plus History

SQL*Plus SET MARKUP CSV

SQL*Plus SET FEEDBACK ONLY

SQL*Plus Performance Settings

SET ROWPREFETCH {1 | n}

Wednesday, April 1, 2015

Free Database Seminar in Timisoara, Romania

Sunday, July 27, 2014

Oracle Enterprise Manager – Reducing the noise, Part 1

About Me

Links

Previous Posts

Archives