There’s a nice little touch to the “set feedback” command in SQL PLus in 12.2. There is a new “only” clause in the SET FEEDBACK command, so you can run queries but the resulting rows are not shown in the display. They are still queried, fetched and “returned”, but just rendered to the screen. Here’s an example
$ sqlplus hr/hr SQL*Plus: Release 126.96.36.199.0 Production on Tue Mar 14 22:59:15 2017 Copyright (c) 1982, 2016, Oracle. All rights reserved. Last Successful login time: Sat Mar 11 2017 01:59:20 -04:00 Connected to: Oracle Database 12c Enterprise Edition Release 188.8.131.52.0 - 64bit Production SQL> select * from regions; REGION_ID REGION_NAME ---------- ------------------------- 1 Europe 2 Americas 3 Asia 4 Middle East and Africa
So that’s normal behaviour. Let’s now use the new ONLY option.
It doesn’t take a rocket scientist to know that even if you have incredibly stringent controls on user authentication, user authorisation etc, that wont save you if your data on disk is not encrypted. All you need is an errant tape, a missing disk, a misplaced flash stick…and kersplat, someone has a copy of your datafiles from your Oracle database.
Data at rest should be encrypted, but that often meant taking applications offline to do so.
I’ve put my Speed Racer hat on and here’s a video on a new 12c Release 2 feature covered in 60 seconds !
What do you do when
Well…naturally, you lure them over with a packet of our famous Australian chocolate biscuits, grab a camera and have a chat !
Maria Colgan and I chew the fat over why you should upgrade to Oracle Database 12c (including Release 2)
Since OpenWorld 2016 when we first saw some of the cool features in Oracle Database 12c Release 2, many IT professionals out there have been exploring the release via our various cloud offerings, but if your organization has not yet embraced the cloud, then March 2017 is a great month for you ! Because you can now download the latest and greatest release of our database from the usual downloads page, and run it on your own servers in your own data centre.
Here’s one of those little improvements in 12c (including 12.1) that will probably end up being described as “little known features” in about 3 years time. Arguably it’s one of those little things that no-one should care about because it’s not the sort of thing you should do on a production system, but that doesn’t mean it won’t be seen in the wild.
Rather than simply state the feature I’m going to demonstrate it, starting with a little code to build a couple of tables with referential integrity:
One of the optimizer enhancements that appeared in 12.2 for SQL is the “band join”. that makes certain types of merge join much more efficient. Consider the following query (I’ll supply the SQL to create the demonstration at the end of the posting) which joins two tables of 10,000 rows each using a “between” predicate on a column which (just to make it easy to understand the size of the result set) happens to be unique with sequential values though there’s no index or constraint in place:
I’ve written a couple of articles in the past about the problems of ASSM spending a lot of time trying to find blocks with usable free space. Without doing a bit of rocket science with some x$ objects, or O/S tracing for the relevant calls, or enabling a couple of nasty events, it’s not easy proving that ASSM might be a significant factor in a performance problem – until you get to 12c Release 2 where a staggering number of related statistics appear in v$sysstat.
I’ve published the full list of statistics (without explanation) at the end of this note, but here’s just a short extract showing the changes in my session’s ASSM stats due to a little PL/SQL loop inserting 10,000 rows, one row at a time into an empty table with a single index:
I will be speaking about the following topics in Rocky Mountain Oracle User group Training days (RMOUG, Denver) February 7-9, 2017.
Come to my presentations and say Hi to me
I don’t know how I missed it but Randolf Geist has been doing writing a series of posts on the performance of Oracle’s DBaaS offering, using a series of long-running tests to capture not only raw performance figures but also an indication of consistency. You can find all of these tests with a search URL on his blog, but I’ve also created a little index here to make it easier for me to access them in order.
From time to time someone comes up with the question about whether or not the order of tables in the from clause of a SQL statement should make a difference to execution plans and performance. Broadly speaking the answer is no, although there are a couple of boundary cases were a difference can appear unexpectedly.