Posted on

Oracle DB: Statistics on Load

There are several changes that arrive with the optimizer when you move to 12c and above. There are changes to histograms, all of the adaptive features etc, but also, one of the simplest but coolest optimizer improvements when it comes to loading data. When you perform a direct-path insert into an empty table, or a create-table-as-select (CTAS) operation, there is no need to perform an additional DBMS_STATS call to gather statistics at the conclusion of the load. The load process itself handles that for you. Here’s a simple example of that in action:

PLSQL


Of course, many people are now aware of this functionality, so why does it warrant a blog post? Mainly to make readers aware of a boundary case. If the table you are creating or loading is an index-organized table (IOT), then you do not get the statistics gathered automatically.

Posted on

SQL Plus: The Sweet Spot

Need to get a bunch from rows out of the database? Most people are aware of the ARRAYSIZE parameter to improve the fetch performance back to the client, but many people are not aware of the newer ROWPREFETCH parameter.

Let's take a look at each to see how quickly we can drag data back to our SQL Plus client. I'll start with table called TX which is approximately 1500MB in size and has just over 10millions.

Posted on

Will a BLOB Eat All My Memory?

Probably the most common usage for large objects (CLOBs and BLOBs) is to store them in a database table. In this circumstance, it feels intuitive that you won't have a lot of concerns about memory, because the database will simply store those objects in datafiles like it would any other kind of data.

But BLOBs and CLOBs can also be declared as local variables in a PL/SQL block. We typically expect local variables to be housed within the memory for that session (the PGA). There are explicit calls in the DBMS_LOB package to create a temporary large object, but what if we do use that API? What if we just start bludgeoning a local variable with more and more data? Is this a threat to the session memory and potentially the database server?

Posted on

Listener Log Data Mining With SQL

If you take a look at the log files created by the listener, there is obviously a nice wealth of information in there. We get service updates, connections, etc., all of which might be useful, particularly in terms of auditing security

However, it also is in a fairly loose text format, which means ideally I’d like to utilize the power of SQL to mine the data.

Posted on

Plugzilla! How to Clone Pluggable Databases

Cloning a pluggable database takes time, and for environments where you'd like to use clones as part of unit testing or other elements of Agile development, it would be nice to be able to bring a clone into operation in the smallest time possible. One mechanism for that is sparse storage clones aka snapshot copy, but depending on your database version and your storage infrastructure, you might hit some limitations.

Enter Plugzilla! This PL/SQL package allows you to clone pluggable databases extremely quickly by having pluggable database pre-cloned in advance.

Posted on

DBMS_JOB — Watching for Failures

I had a friend point this one out to me recently. They use DBMS_JOB to allow some “fire and forget” style functionality for the user, and in their case, the jobs are “best efforts” in that if they fail, it is not a big deal.

While this may sound counter-intuitive, if you rely on jobs submitted via DBMS_JOB to fail, then please read on.

Posted on

PL/SQL — Don’t Mix and Match Scope

In this article, we find out why you shouldn't mix and match scope. Here's a simple little PL/SQL block where we call an inner procedure PARAMETER_TESTER from its parent block. Pay particular attention to the parameter we pass to the procedure and its value throughout the execution of that procedure.

SQL> set serverout on
SQL> declare
  2
  3     glob_var  int := 0;
  4     local_var int;
  5
  6     procedure PARAMETER_TESTER(param int) is
  7     begin
  8         dbms_output.put_line('Param came in as: '||param);
  9         glob_var := glob_var + 1;
 10         dbms_output.put_line('Param left as   : '||param);
 11         dbms_output.put_line('glob_var is now : '||glob_var);
 12     end;
 13
 14  begin
 15     parameter_tester(100);
 16  end;
 17  /
Param came in as: 100
Param left as   : 100
glob_var is now : 1

PL/SQL procedure successfully completed.

Now I'll slowly extend the code, and just by eyeballing it, see if you can predict what the output will be before looking past the end of the PL/SQL block.