SQL*Plus 12.2 (so close)

There’s a few new features in the latest SQL*Plus client that look like they could be super useful, I haven’t seen much mentioning of them so I thought I’d take up the job. The documentation has the changes in https://docs.oracle.com/database/122/SQPUG/release-changes.htm#SQPUG648 so review the docs for the facts.

HISTORY

Syntax
HIST[ORY] [n RUN | EDIT | DEL[ETE]] | [CLEAR | LIST]
Enables users to run, edit, or delete previously used SQL*Plus, SQL, or PL/SQL commands from the history list in the current session

I’m only going to briefly mention history – it’s not something I see myself using that much, I already have F7 in Windows. The one benefit this new option has is that it can work with ed, albeit with some manual work.
ANDY@pdb1>set history 5
ANDY@pdb1>select * from dual;

D
-
X

ANDY@pdb1>ed
Wrote file afiedt.buf

  1* select dummy from dual
ANDY@pdb1>/

D
-
X

ANDY@pdb1>history
  1  select * from dual;
  2  ed
     select dummy from dual
     /
  3  /
Most statements are rerunnable by calling 
History n run
But see how statement 2 has included the ed command? That prevents you from running it directly, you can call
history 2 edit
And then modify it before running, but you can’t just run the command. Not quite there IMO.

Support for input binding by using the VARIABLE command. The VARIABLE command now supports input binding which can be used in SQL and PL/SQL statements

It has long been a gripe of mine that in order to use bind variables in SQL*Plus, you need to declare them and then run a plsql block in order to set them:
var nNumber number
exec :nNumber := 10
My problem here is that in order to save you parsing one statement, you parse another statement instead!
In 12.2, Oracle has extended the variable command to allow you to set the value of the bind as you declare it. So our simple example now becomes
var nNumber number=10
0 parses.
My main problem with the new syntax is that you can’t use defined variables, so if you have a script that allows for input from the user, you can’t use that for your binds without the plsql call.
ANDY@pdb1>var nNumber number=&1
 SP2-0425: "&1" is not a valid NUMBER
Whilst we’re on the subject of parses, the next new feature is statement caching. Statement caching is well known about in general application programming, it basically makes the difference between a soft parse and no parse (the use of shareable sql saves you the hard parse).
A quick demo of the old behaviour (and default 12.2 behaviour)
ANDY@pdb1>get sql_find
 1  select * from (select sql_id, last_active_time, executions, parse_calls, substr(sql_text,1,100) sql_text
 2  from v$sql where sql_text not like '%v$sql%' and upper(sql_text) like UPPER('%&1%') order by last_active_time desc)
 3* where rownum <=15

alter system flush shared_pool;
 var nNumber number
 exec :nNumber := 5
 select * from dual where :nNumber = 10;
 exec :nNumber := 10
 select * from dual where :nNumber = 10;

@sql_find ":nNumber"

SQL_ID        LAST_ACTIVE_TIME    EXECUTIONS PARSE_CALLS SQL_TEXT
 ------------- ------------------- ---------- ----------- ---------------------------------------------
 2azpdtw4dhbbn 29/03/2017 00:54:11          1           1 BEGIN :nNumber := 10; END;
 fhsvcq3xzfmc1 29/03/2017 00:54:11          1           1 BEGIN :nNumber := 5; END;
 2mty35xpvmgph 29/03/2017 00:54:11          2           2 select * from dual where :nNumber = 10

We can see that our parse calls are much more than you'd expect, and we've also got those pesky ones I was talking about earlier. The separate statements for assigning our bind variables were all hard parsed! This is how 12.2 compares:

alter system flush shared_pool;
 set statementcache 50
 var nNumber number=5
 select * from dual where :nNumber = 10;
 var nNumber number=10
 select * from dual where :nNumber = 10;

@sql_find ":nNumber"

SQL_ID        LAST_ACTIVE_TIME    EXECUTIONS PARSE_CALLS SQL_TEXT
 ------------- ------------------- ---------- ----------- -----------------------------------------------------------
 2mty35xpvmgph 29/03/2017 00:56:08          2           1 select * from dual where :nNumber = 10
All of those parse calls are GONE, fantastic! I’m not going to suggest that your actual production DB has a problem caused by SQL*Plus parsing, but this does give you the ability to demonstrate the effects of some typical application performance enhancements. It certainly won’t harm your shared pool.

SET ROWPREFETCH {1 | n}

There’s one more thing that I want to mention, row prefetching. This is something that I wasn’t clear about to start with, the docs describe it as:
Sets the number of rows that SQL*Plus will prefetch from the database at one time.
If we compare this to the arraysize:
Sets the number of rows, called a batch, that SQL*Plus will fetch from the database at one time.
What’s the difference between fetching and prefetching? A demo may help
alter system flush shared_pool;
 set arraysize 10
 set rowprefetch 50
 set pagesize 10
 set pause on
The pagesize and pause are there so that we display a few rows at a time in sqlplus, our cursor remains open and we can see what has been fetched from the cursor from another session. I’ve added fetches and rows_processed to my @sql_find script so we can monitor that too.
ANDY@pdb1>select * from dual connect by rownum <=110;
 

At this point we haven't displayed any rows. If we check in our other session

ANDY@pdb1>@sql_find dual%connect

SQL_ID        LAST_ACTIVE_TIME    EXECUTIONS PARSE_CALLS    FETCHES ROWS_PROCESSED SQL_TEXT
 ------------- ------------------- ---------- ----------- ---------- -------------- -------------------------------------------------
 40p6csxf1r8p3 29/03/2017 01:07:27          1           0          1             50 select * from dual connect by rownum <=110
Apparently we’ve already processed 50 rows in 1 fetch, that’s our prefetch setting coming into play. What happens when we release our pause and get the next page?
D
 -
 X
 X
 X
 X
 X
 X
 X

 
SQL_ID        LAST_ACTIVE_TIME    EXECUTIONS PARSE_CALLS    FETCHES ROWS_PROCESSED SQL_TEXT
 ------------- ------------------- ---------- ----------- ---------- -------------- ---------------------------------------------
 40p6csxf1r8p3 29/03/2017 01:07:27          1           0          1             50 select * from dual connect by rownum <=110
We’ve not done anything with the SQL but we’ve got 7 rows back! That’s because the rows have already been prefetched by sqlplus and are just sitting on the client side, no need to request them from the DB. Let’s do a few more pages. Once we’ve read all 50 of our fetched rows, the next call does another fetch
SQL_ID        LAST_ACTIVE_TIME    EXECUTIONS PARSE_CALLS    FETCHES ROWS_PROCESSED SQL_TEXT
 ------------- ------------------- ---------- ----------- ---------- -------------- ---------------------------------------------------
 40p6csxf1r8p3 29/03/2017 01:20:58          1           0          2            110 select * from dual connect by rownum <=110
You’ll notice that the second fetch was responsible for 60 rows being processed – honestly this was a surprise, it’s the total of our arraysize and rowprefetch but it’s also the total number of rows in the query.
SQL_ID        LAST_ACTIVE_TIME    EXECUTIONS PARSE_CALLS    FETCHES ROWS_PROCESSED SQL_TEXT
 ------------- ------------------- ---------- ----------- ---------- -------------- ---------------------------------------------------
 40p6csxf1r8p3 29/03/2017 01:21:19          1           0          3            110 select * from dual connect by rownum <=110
The third fetch didn’t process any rows, this is normal as it was our final fetch to mark the cursor as finished.
What happens when we use an arraysize of 5 and a total query size of 200 rows (keeping our rowprefetch 50)?
Here is the @sql_find results at the point of second fetch:
 SQL_ID        LAST_ACTIVE_TIME    EXECUTIONS PARSE_CALLS    FETCHES ROWS_PROCESSED SQL_TEXT
 ------------- ------------------- ---------- ----------- ---------- -------------- ------------------------------------------------
 3rwacgmnkkabu 29/03/2017 01:33:26          1           1          1             50 select * from dual connect by rownum <=200

ANDY@pdb1>/

SQL_ID        LAST_ACTIVE_TIME    EXECUTIONS PARSE_CALLS    FETCHES ROWS_PROCESSED SQL_TEXT
 ------------- ------------------- ---------- ----------- ---------- -------------- ------------------------------------------------
 3rwacgmnkkabu 29/03/2017 01:34:02          1           1          2            105 select * from dual connect by rownum <=200
Yes, we do a prefetch and a fetch worth of rows for the secondary fetch.
Finally, Oracle has baked this new behaviour into a fastmode for SQL*Plus that can be used simply with:
sqlplus -f

ARRAYSIZE = 100

  • LOBPREFETCH = 16384
  • PAGESIZE = 50000
  • ROWPREFETCH = 2
  • STATEMENTCACHE = 20
This simply sets some parameters to what Oracle believes are best for general performance, I have no idea why you wouldn’t use them by default – perhaps you’d rather use your own mix. In my honest opinion it is a no brainer to enable these options, you don’t need to set any of the parameters particularly high to start seeing the benefits.
As an aside, the pagesize was a surprise to me, I wonder how noticeable a change in pagesize is on real performance.
Perhaps the real improvements would be obvious for those huge reports or extracts of tables. The markup command has also been enabled for csv output, this is something that’s been missing for a long time. I’ll plan to benchmark csv extract generation of a big table (e.g for sqlldr input) using old sqlplus, fastmode sqlplus and sqlCL but that’s another day.
Let me know what you think of the new settings and if you manage to leverage them for substantial improvements.
Advertisements