Search

Top 60 Oracle Blogs

Recent comments

Oakies Blog Aggregator

Parse Puzzle

Here are some details from an AWR report covering a few minutes in the lifetime of an instance of 18.3. It’s a carefully constructed demonstration and all I’ve done is take an AWR snapshot, execute a single SQL statement, then take another snapshot, so the only thing captured by the report is the work done in that brief time interval. The purpose of the exercise is to demonstrate how some Oracle features can make a complete nonsense of the AWR. (I have, as I often do, produced a model that reproduces an affect that can appear in production but exaggerates the effect to make it more clearly visible.)

First the Time Model statistics:

                                                                % of  % of Total
Statistic Name                                       Time (s) DB Time   CPU Time
------------------------------------------ ------------------ ------- ----------
sql execute elapsed time                                157.8   100.0
DB CPU                                                  157.5    99.8       97.3
parse time elapsed                                       13.6     8.6
hard parse elapsed time                                  13.6     8.6
PL/SQL execution elapsed time                             0.3      .2
PL/SQL compilation elapsed time                           0.0      .0
hard parse (sharing criteria) elapsed time                0.0      .0
repeated bind elapsed time                                0.0      .0
DB time                                                 157.8
background elapsed time                                   7.9
background cpu time                                       4.4                2.7
total CPU time                                          161.9
                          ------------------------------------------------------


Note particularly the parse times – the Time Model show 13.6 seconds spent in (hard) parsing.

Note also that (with a small error) DB time = DB CPU = SQL execute elapsed time, and the background time is very small (in particular it’s less than the parse time). This background time, by the way, is probably related to things that Oracle does behind the scenes when you take an AWR snapshot or run an AWR report.

Given the significant amount of time spent in hard parsing let’s take a look at the Instance Activity statistics – picking only the statistics relating to parse calls:


Statistic                                     Total     per Second     per Trans
-------------------------------- ------------------ -------------- -------------
parse count (describe)                            0            0.0           0.0
parse count (failures)                            0            0.0           0.0
parse count (hard)                              325            2.1         325.0
parse count (total)                           1,662           10.5       1,662.0
parse time cpu                                   39            0.3          39.0
parse time elapsed                               42            0.3          42.0

Although the Time Model thinks Oracle has spent 13.6 seconds in (hard) parse time, the Instance Activity Statistics says it has only spent 0.42 seconds (the statistic is repored in hundredths) That a fairly significant difference of opinion. So let’s see if we can find out more from the “SQL ordered by …”, and I’m only going to show you one heading as a teaser for the rest of the weekend:


SQL ordered by Elapsed Time               DB/Inst: OR18/or18  Snaps: 2059-2060
-> Resources reported for PL/SQL code includes the resources used by all SQL
   statements called by the code.
-> % Total DB Time is the Elapsed Time of the SQL statement divided
   into the Total Database Time multiplied by 100
-> %Total - Elapsed Time  as a percentage of Total DB time
-> %CPU   - CPU Time      as a percentage of Elapsed Time
-> %IO    - User I/O Time as a percentage of Elapsed Time
-> Captured SQL account for  302.6% of Total DB Time (s):             158
-> Captured PL/SQL account for  101.7% of Total DB Time (s):             158

How do you squeeze 400% of the elapsed time into SQL and PL/SQL executions? (Observation: it’s not an IBM P9 taking advantage of SMT/4)

One last set of stats – which will have some room for statistical error since they come from v$active_session_history:


select
        in_parse, in_hard_parse, sql_id, sql_exec_id, count(*)
from
        v$active_session_history  ash
where
        session_id = &m_session_id
and     sample_id between &m_start_sample_id and &m_end_sample_id
group by
        in_parse, in_hard_parse, sql_id, sql_exec_id
order by
        count(*)
/

I I SQL_ID	  SQL_EXEC_ID	COUNT(*)
- - ------------- ----------- ----------
Y Y 964u0zv0rwpw1		       3
Y Y awk070fhzd4vs		       4
Y Y 5531kmrvrzcxy		       5

N N 5531kmrvrzcxy		      42
N N 964u0zv0rwpw1		      42
N N awk070fhzd4vs		      51

6 rows selected.

So the ASH information seems to agree (reasonably closely) with the Time Model statistics – in the interval of the snapshot it’s noted 12 samples (which statistically represents 12 seconds) of hard parse time (and though my query doesn’t show it, the “event” is  null, i.e. “on CPU”).

 

To be continued, some time later this week …

Transaction management in PostgreSQL and what is different from Oracle

TL;DR: Everything is different about transaction behavior. This may also change your ideas about “database independent” applications.

I like to explain some PostgreSQL concepts from an oracle DBA point of view. There are many things that are different in the two RDBMS and it is important to understand them.

Auto commit

Here is a short example where I create a table, insert one row and rollback:

psql -U postgres demo ### PostgreSQL 11.4
 drop table if exists DEMO;
create table DEMO as select 1 n,current_timestamp t;
select * from DEMO;
insert into DEMO values (2,current_timestamp);
select * from DEMO;
rollback;
select * from DEMO;
insert into DEMO values (3,current_timestamp);
\q

I never executed any commit but my changes are saved and published. This is auto-commit by default in psql. I think I prefer the Oracle default where the commit is my decision, and only what I’ve committed is visible:

sqlplus demo/demo@//localhost/PDB1 ### Oracle Database 19.4
 column t format a35
exec for i in (select table_name from user_tables where table_name='DEMO') loop execute immediate 'drop table "'||i.table_name||'" cascade constraints'; end loop;
create table DEMO as select 1 n,current_timestamp t from dual;
select * from DEMO;
insert into DEMO values (2,current_timestamp);
select * from DEMO;
rollback;
select * from DEMO;
insert into DEMO values (3,current_timestamp);
quit

So, is Oracle the best for transaction management?

Well… autocommit default is only one thing. Let’s go further.

First, this is only a client setting. SQL*Plus has it set to off:

SQL> show autocommit
autocommit OFF

But JDBC drivers may have it set to on. So, rather than a nice thing about the database, it is just a nice default of SQL*Plus.

Commit on exit

And wait… are all sqlplus defaults so smart? Let’s come back to my Oracle database where I committed nothing:

sqlplus demo/demo@//localhost/PDB1 ### Oracle Database 19.4
 column t format a35
select * from DEMO;
quit

Are sqlplus defaults so smart? I didn’t commit the last insert but it was committed by default. Not because of auto-commit but because of exit-commit:

SQL> show exitcommit
exitcommit ON

That’s not a nice default. If I quit without saying ‘commit’ I want a rollback. It is highly recommended to set exit-commit off to avoid any unexpected commit. (also recommendedvfor scripts having a WHENEVER SQLERROR EXIT that mentions ROLLBACK because COMMIT is the default).

DDL auto-commit

And that’s not all… The first row (n=1) was never explicitly committed. It was inserted with DDL (CREATE TABLE) and DDL are always auto-committed. That’s the Oracle Database, nothing to do with the client: you cannot be transactional with DDL.

Start transaction

Back to PostgreSQL, the default is auto-commit but I have the choice. I can explicitly start a transaction and then I’ll have to explicitly commit it.

psql -U postgres demo ### PostgreSQL 11.4
start transaction;
drop table if exists DEMO;
create table DEMO as select 1 n,current_timestamp t;
select * from DEMO;
insert into DEMO values (2,current_timestamp);
select * from DEMO;
rollback;
select * from DEMO;
start transaction;
insert into DEMO values (3,current_timestamp);
\q
psql -U postgres demo ### PostgreSQL 11.4
select * from DEMO;
\q

See? I have the 3 rows from the first run which were all auto-committed. But now that I explicitly started a transaction, everything was transactional, even the DDL: the DROP TABLE, the CREATE TABLE, the INSERT were explicitly rolled-back. And even the last INSERT was implicitly rolled-back on exit.

Now, who is the winner in transaction management? There’s even more: you can send a multi-statement command to the backend and it will be processed as an implicit transaction.

Note that START TRANSACTION is the ANSI SQL syntax, but PostgreSQL accepts also BEGIN, BEGIN TRANSACTION and BEGIN WORK.

Set Transaction name

Don’t think that there are no “begin transaction” in Oracle. The SET TRANSACTION starts it, in order to define the isolation level, or simply to put a name to the transaction.

This example looks at the transaction address in V$SESSION and V$TRANSACTION

sqlplus demo/demo@//localhost/PDB1 ### Oracle Database 19.4
select saddr,taddr from v$session 
where sid=sys_context('userenv','sid');
select count(*) from DEMO;
select saddr,taddr from v$session
where sid=sys_context('userenv','sid');
update DEMO set t=current_timestamp;
select saddr,taddr from v$session
where sid=sys_context('userenv','sid');
rollback;
select saddr,taddr from v$session
where sid=sys_context('userenv','sid');
set transaction name 'my_transaction';
select saddr,taddr from v$session
where sid=sys_context('userenv','sid');
quit

This illustrates when a transaction starts (visible with a TADDR in V$SESSION and a row in V$TRANSACTION): the first INSERT/DELETE/UPDATE/MERGE/SELECT FOR UPDATE or a SET TRANSACTION

Autonomous Transaction

Not available in PostgreSQL but possible in Oracle: we can have nested transactions. This is very convenient in some limited cases, like logging the error in the database (and commit this insert) before the rollback of the transaction.

sqlplus demo/demo@//localhost/PDB1 ### Oracle Database 19.4
 set serveroutput on
create table DEMO2 as select * from DEMO;
select saddr,taddr from v$session
where sid=sys_context('userenv','sid');
select count(*) from DEMO;
select saddr,taddr from v$session
where sid=sys_context('userenv','sid');
update DEMO set t=current_timestamp;
select saddr,taddr from v$session
where sid=sys_context('userenv','sid');
select addr,ses_addr from v$transaction;
declare
pragma autonomous_transaction;
begin
update DEMO2 set t=current_timestamp;
for i in ( select addr,ses_addr from v$transaction) loop
dbms_output.put_line(
'Transaction: ADDR: '||i.addr||' SES_ADDR: '||i.ses_addr
);
end loop;
rollback;
end;
/
rollback;
quit

This shows that there can be multiple transactions for the same session. The PADDR is only the address or the top-level one.

Statement-level rollback

Still in Oracle, when a statement fails, the modifications done by this statement are rolled back, but not the previous modifications. The transaction can continue (like re-try, or do an alternative change):

sqlplus demo/demo@//localhost/PDB1 ### Oracle Database 19.4
column t format a35
exec for i in (select table_name from user_tables where table_name='DEMO') loop execute immediate 'drop table "'||i.table_name||'" cascade constraints'; end loop;
create table DEMO as select 1 n,current_timestamp t from dual;
alter table DEMO add unique(n);
select * from DEMO;
insert into DEMO values (2,current_timestamp);
select * from DEMO;
insert into DEMO values (1,current_timestamp);
select * from DEMO;
quit


ERROR: current transaction is aborted

That’s different in PostgreSQL where the transaction cannot continue when you encounter an error:

psql -U postgres demo ### PostgreSQL 11.4
drop table if exists DEMO;
start transaction;
create table DEMO(n,t) as select 1 n,current_timestamp t;
alter table DEMO add unique(n);
select * from DEMO;
insert into DEMO values (2,current_timestamp);
select * from DEMO;
insert into DEMO values (1,current_timestamp);
select * from DEMO;
rollback;
select * from DEMO;
\q

ERROR: current transaction is aborted, commands ignored until end of transaction block

Here I rolled back. But I can also commit to terminate the transaction, but it will rollback anyway:

# commit;
ROLLBACK

Savepoint

Actually, we can achieve statement-level rollback even in PostgreSQL, using savepoints:

psql -U postgres demo ### PostgreSQL 11.4
start transaction;
drop table if exists DEMO;
create table DEMO(n,t) as select 1 n,current_timestamp t;
alter table DEMO add unique(n);
select * from DEMO;
insert into DEMO values (2,current_timestamp);
select * from DEMO;
savepoint my_before_insert_savepoint;
insert into DEMO values (1,current_timestamp);
select * from DEMO;
rollback to my_before_insert_savepoint;
select * from DEMO;
\q

With Oracle, you don’t need to because there is an implicit savepoint before each execution.

Well, the PostgreSQL client psql do the same:

\set ON_ERROR_ROLLBACK on

and then psql will automatically create a “pg_psql_temporary_savepoint”.

PostgreSQL 11 Procedures

Before version 11 all commands (which can be one statement, or multiple ones, or PL/pgSQL anonymous or stored procedures) were run in an atomic context, with no intermediate commits possible without a roundtrip with the client. Like this:

psql -U postgres demo ### PostgreSQL 11.4
drop table if exists DEMO;
create table DEMO(n,t) as select 1 n,current_timestamp t;
alter table DEMO add unique(n);
create or replace procedure my_inserts(n1 int, n2 int) as
$$
begin
for i in n1..n2 loop
insert into DEMO values (i,current_timestamp);
end loop;
end;
$$ language plpgsql;
call my_inserts(10,12);
call my_inserts(5,15);
select * from DEMO;
\q

The first call has inserted values 10 and 12 and they were committed because I run in the default AUTOCOMMIT. But the second call has encountered a duplicate key and the whole was rolled-back. But in PostgreSQL 11 I can add an intermediate commit so that the first rows are committed before I encounter the error.

psql -U postgres demo ### PostgreSQL 11.4
drop table if exists DEMO;
create table DEMO(n,t) as select 1 n,current_timestamp t;
alter table DEMO add unique(n);
create or replace procedure my_inserts(n1 int, n2 int) as
$$
begin
for i in n1..n2 loop
insert into DEMO values (i,current_timestamp);
commit;
end loop;
end;
$$ language plpgsql;
call my_inserts(10,12);
call my_inserts(5,15);
select * from DEMO;
\q

Here the rows 5 to 9 have been committed before encountering the exception for row 10.

Still, no need for an explicit BEGIN here. The COMMIT in the loop will end the transaction (started implicitly on the server as I did no BEGIN before) and start a new one. The last transaction started will be committed implicitly.

Additionally, you can look at the timestamps in the two previous demos. In PostgreSQL, current_timestamp is consistent in the transaction.

Invalid transaction termination

Here is the same, but with AUTOCOMMIT off:

psql -U postgres demo ### PostgreSQL 11.4
drop table if exists DEMO;
create table DEMO(n,t) as select 1 n,current_timestamp t;
alter table DEMO add unique(n);
create or replace procedure my_inserts(n1 int, n2 int) as
$$
begin
for i in n1..n2 loop
insert into DEMO values (i,current_timestamp);
commit;
end loop;
end;
$$ language plpgsql;
\set AUTOCOMMIT off
call my_inserts(10,12);
call my_inserts(5,15);
select * from DEMO;
rollback;
select * from DEMO;
\q

Actually, this is what made me start this blog post. But that’s already a lot, and further investigation on this will be on the next post:
AUTOCOMMIT and PostgreSQL transaction management in procedures

Implicit transactions

Ok, just one more on this. The error above is not really caused by AUTOCOMMIT but by the way psql handles AUTOCOMMIT. Actually, with PostgreSQL, the AUTOCOMMIT is not a BEGIN statement added by the psql client, but the backend server creating an implicit transaction when there is not already one. It is even the opposite: when AUTOCOMMIT is off, the client adds a “BEGIN” so that the server does not create an implicit one. Which means that the “invalid transaction termination” occurs also without auto-commit when we CALL the procedure from an explicit transaction.

ERROR: invalid transaction termination

\echo :AUTOCOMMIT
begin transaction;
do $$
begin
update DEMO set t=null;
rollback;
end
$$;
rollback;
\set AUTOCOMMIT off
\echo :AUTOCOMMIT
do $$
begin
update DEMO set t=current_timestamp;
commit;
end
$$;

This long post is only a short sample of the many differences between Oracle and PostgreSQL transaction management. And I’ve only used psql and sqlplus here. Do you want to test the same from Java JDBC and Python psycopg2?

Comments welcome on Twitter:

PostgreSQL transaction management in procedures

TL;DR: AUTOCOMMIT is required when calling a procedure which has some COMMIT inside.

In version 11 PostgreSQL has introduced the possibility to start, commit or rollback transactions in PL/pgSQL procedures (stored or anonymous). Most of the demos have been run from the psql default AUTOCOMMIT on, like 2ndQuadrant and dbi-services blogs. But Bryn Llewellyn (YugaByte) raised an issue when running without AUTOCOMMIT OFF (which, coming from Oracle, looks like the right choice). Here is my investigation on this.

You should read my last post Transaction management in PostgreSQL and what is different from Oracle if you are not totally familiar with PostgreSQL transaction management and auto-commit, as I wrote it as an introduction to this analysis.

Tracing with GDB

Here is what I run (I’m using the function from the previous post):

psql
\set PROMPT1 '\t\t\t\t\t\t>>>%`date +%H:%M:%S`<<<\n%/%R%# '
select pg_backend_pid();
-- attach gdb to backend and set breakpoint on exec_simple_query
\echo :AUTOCOMMIT
call my_inserts(1111,1111);
-- gdb stops on breakpoint and continue
\set AUTOCOMMIT off
call my_inserts(1112,1112);
-- gdb stops on breakpoint and continue

Here is my gdb session on a second terminal:

gdb -p $(pgrep -nl postgres)
define hook-stop
shell echo -e "\t\t\t\t\t\t>>>`date +%H:%M:%S`<<<"
end
print getpid()
break exec_simple_query
cont
## back to psql to call the procedure
print query
cont
## back to plsql to set AUTOCOMMIT off and run again

I have added timestamps in both prompts in order to show the sequence in one screenshot. Here is the result. The first call succeeded (in AUTOCOMMIT on) but the second call failed (with AUTOCOMMIT off) because psql has issued a BEGIN before the CALL:

I have 2 questions here:

  • Why does psql initiates a transaction before the call when it is not in AUTOCOMMIT?
  • Why does the procedure’s COMMIT fail when in a transaction opened outside of the procedure?

Why does the COMMIT fail when in a transaction opened outside?

From the previous step, I rollback (the transaction, initiated by the client when in AUTOCOMIT off, was aborted). And call the procedure again after having set the following breakpoints:

 break SPI_start_transaction
break SPI_commit
print _SPI_current->atomic
cont

I’ve set those and displayed “atomic” because the error message comes from the following PostgreSQL code:

https://github.com/YugaByte/yugabyte-db/blob/c02d4cb39c738991f93f45f4895f588a6d9ed716/src/postgres/src/backend/executor/spi.c#L226

 set pagination off
print _SPI_current->atomic
backtrace

I can see atomic=true in the call to the following function

ExecuteCallStmt(CallStmt *stmt, ParamListInfo params, bool atomic, DestReceiver *dest)

and the comments in functioncmds.c explains the idea of “atomic commands” — those were transaction control commands are disallowed. Here is the postgres source code and the explanation:

YugaByte/yugabyte-db

* Inside a top-level CALL statement, transaction-terminating commands such as COMMIT or a PL-specific equivalent are allowed. The terminology in the SQL standard is that CALL establishes a non-atomic execution context. Most other commands establish an atomic execution context, in which transaction control actions are not allowed.

This makes sense. But I am in a “top-level CALL statement”, so why is atomic set to true? The parent in the stack is standard_ProcessUtility and here is how atomic is defined:

bool isAtomicContext = (!(context == PROCESS_UTILITY_TOPLEVEL || context == PROCESS_UTILITY_QUERY_NONATOMIC) || IsTransactionBlock());

Ok, I think I got it. There’s another reason to set atomic=true: I am in already in a transaction block. Just confirmed by running the same

with those additional breakpoints:

break standard_ProcessUtility
continue
print context
print IsTransactionBlock()
continue
print context
print IsTransactionBlock()

So, I am already in a transaction when executing the CALL and documentation says that:

If CALL is executed in a transaction block, then the called procedure cannot execute transaction control statements

Ok, documentation is perfect. Why did I need to gdb in order to get this? It’s more fun

Is a datafile OMF or not?

TL;DR: there’s no flag (only the name tells it), but a function

You want to know which files are Oracle Managed Files or not? Maybe because you like OMF (like I do as I show no interest for file names when I have tablespaces) and you have non-OMF files that you want to move to OMF ones.

There’s no flag in V$DATABASE or DBA_DATA_FILES. Only the name of the file (starting with ‘o1_mf’, like Oracle Managed Files, and ending with ‘.dbf’, like Database File) makes it OMF or not. But I don’t like to rely on name convention so I searched how Oracle is doing it. There’s an isFileNameOMF procedure in the RMAN packages.

dbms_Backup_Restore.isFileNameOmf

Here is a procedure that displays which files are ASM, and which ones are regular user-managed ones:

declare
isomf boolean;
isasm boolean;
begin
for c in (select name from v$datafile)
loop
dbms_backup_restore.isfilenameomf(c.name,isomf,isasm);
if isasm then dbms_output.put('ASM '); else
if isomf then dbms_output.put('OMF '); else dbms_output.put('reg '); end if;
end if;
dbms_output.put_line(c.name);
end loop;
end;

Output on non-ASM with some OMF and some regular files

The procedure has another boolean which I guess is for controlfile autobackup names, but I don’t need it.

‘Rename’ OMF file names

In 12c, the procedure can be called from an in-line function in a SQL query. Here is how I generate all the ‘ALTER DATABASE MOVE DATAFILE’ commands for non-OMF files:

with function isomf( name v$datafile.name%type) return char as
isomf boolean;
isasm boolean;
begin
dbms_backup_restore.isfilenameomf(name,isomf,isasm);
if isomf then return 'Y'; else return 'N'; end if;
end;
select 'alter database move datafile '''||file_name||''';'
from dba_data_files
where isomf(file_name)='N';
/

Then, the files will go to the db_create_file_dest with an OMF name that you can change at session level.

Side comment about online “rename”

The previous script uses the 12c Online datafile move to rename them. But I’ve put “rename” in quotes because it copies the file even when it remains within the same filesystem. That’s different from the Linux “mv” which changes only the name when in the same filesystem. Here is an example:

host pkill -9 strace
connect / as sysdba
create tablespace franck datafile size 5M;
column spid new_value pid
select spid from v$process join v$session on v$session.paddr=v$process.addr where sid=sys_context('userenv','sid');
column spid clear
column file_name new_value file_name
select file_name from dba_data_files order by bytes fetch first 1 rows only;
column file_name clear
set escape on
host strace -fy -e trace=file,desc -p &pid 2>\&1 | grep '&file_name'\& :
alter database move datafile '&file_name';
disconnect
host grep dbf strace.log
drop tablespace franck including datafiles;

Update 02-AUG-2019

Thanks to @Mikhail Velikikh here is a function that is probably a better solution: DBMS_METADATA_UTIL.IS_OMF

dbmsmetu.sql

It actually calls the UTL_XML.ISNAMEOMF but has the advantage to be a function, returning 1 for OMF file names or 0 otherwise.

LOB reads

This is a blog note that started life in September 2001 (which was before I started blogging, I think), got drafted as a blog in January 2014 because of a question on the Oracle-L list server, and has finally got to publication following a question on the ODC database forum. (But the comments in the blog don’t look as if they are relevant to the question.)

The question on Oracle-l was:  Why is the same CLOB datablock read multiple times?

The answer is basically: client programs tend to set a “sensible” fetch buffer size for LOBs and if the buffer size is less than the size of the LOB the block(s) the LOB is in have to be visited multiple times and for nocache LOBs that means the block(s) will be read multiple times.

This can be seen quite easily in SQL*Plus which has two helpful parameters (one dating back to at least v5):

set long N
set longchunksize M

The “long” setting tells SQL*Plus how much of the LOB to display on screen, the “longchunksize” tells SQL*Plus how much of the LOB to fetch from the database on each call. The default value is 80 for both settings. So let’s set up some data and do a few simple demonstrations using nothing more subtle than autotrace.


rem
rem     Script:         lob_fetch_2.sql
rem     Author:         Jonathan Lewis
rem     Dated:          July 2012
rem     Purpose:        
rem
rem     Last tested 
rem             18.3.0.0
rem             12.1.0.2
rem             11.2.0.2
rem

create table t1 (
        id      number , 
        v1      varchar2(60),
        c1      clob, 
        c2      clob
)
lob(c1) store as (enable storage in row cache)
lob(c2) store as (enable storage in row cache)
;

begin
        for i in 1..1000 loop
                insert into t1 values(i, lpad(i,60), lpad(i,60), empty_clob());
        end loop;
        commit;
end;
/

execute dbms_stats.gather_table_stats(null,'t1')

I’ve got a table with two CLOB columns, both declared to enable storage in row and cache. I’ve also got a varchar2() column, and I’ve loaded one of the CLOB columns and the varchar2() column with a 60 character string, setting the other CLOB column explicitly to the empty_clob() value – which is not the same as setting it to NULL.

Now I’ll do 3 selects, fetching the id column and one other column, showing only the autotrace statistics:


set long 80
set longchunksize 80

set arraysize 5000

set autotrace traceonly statistics

prompt  ==============
prompt  Varchar2 fetch
prompt  ==============

select id, v1 from t1;

prompt  ================
prompt  Small CLOB fetch
prompt  ================

select id, c1 from t1;

prompt  ================
prompt  Empty CLOB fetch
prompt  ================

select id, c2 from t1;

set autotrace off

I’ve explicitly set long and longchunksize to 80 (the defaults) just to make it clear that that’s what they are for this test; and I’ve set the arraysize to 5000 so that I can get the 1,000 rows of data in the smallest number of fetches. Here are the three sets of autotrace statistics:


==============
Varchar2 fetch
==============

1000 rows selected.


Statistics
----------------------------------------------------------
         11  recursive calls
         11  db block gets
         75  consistent gets
          0  physical reads
       2040  redo size
      70545  bytes sent via SQL*Net to client
        624  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1000  rows processed

================
Small CLOB fetch
================

1000 rows selected.


Statistics
----------------------------------------------------------
          4  recursive calls
          5  db block gets
       2036  consistent gets
          0  physical reads
        992  redo size
     707454  bytes sent via SQL*Net to client
     296624  bytes received via SQL*Net from client
       2002  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1000  rows processed

================
Enpty CLOB fetch
================

1000 rows selected.


Statistics
----------------------------------------------------------
          3  recursive calls
          5  db block gets
       2036  consistent gets
          0  physical reads
       1048  redo size
     585454  bytes sent via SQL*Net to client
     296624  bytes received via SQL*Net from client
       2002  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1000  rows processed

Key points to note (ignoring minor details like the recursive calls to parse the statements):

  • The query for the the varchar2() column did two SQL*Net roundtrips and 75 consistent gets.
  • The query for the small, inline, CLOB did 2,002 SQL*Net roundtrips and 2,036 consistent gets
  • The query for the empty CLOB did exactly the same number of SQL*Net roundtrips and consistent gets as for the small CLOB

Because we were selecting a LOB column SQL*Plus switched from array fetches to single row fetches. In the first fetch of each pair of fetches it fetched the non-CLOB columns and the “LOB locator”; then in the second fetch of each pair it fetched the CLOB data – and it did this even when the LOB locator could (in principle) have told it that there was no data to fetch. (If you select both clob columns in the same query the number of SQL*Net roundtrips will go up to 3 per row in this test.)

Remember I said there was a difference between empty_clob() and NULL ? If you update the table to set c2 to null before running the query to select c2 you’ll see only 1,000 (and a few) SQL*Net roundtrips – you still get single row processing because you’re selecting a LOB column but when the NULL  arrives at SQL*Plus the code will know that there is no LOB data to retrieve.

Now try this:


truncate table t1;

begin
        for i in 1..1000 loop
                insert into t1 values(i, lpad(i,60), lpad(i,180), empty_clob());
        end loop;
        commit;
end;
/

execute dbms_stats.gather_table_stats(null,'t1')

set long 170
select id, c1 from t1;

Pause for thought as you work out what the stats will look like
.
.
.
.
.
The longchunksize (still at the default of 80) is now too small to collect all the data that should be displayed in one SQL*Net roundtrip – it’s going to take 3 roundtrips to get 170 bytes, so we might expect to see about 4,000 SQL*Net roundtrips and a similar number of consistent gets. This is what the stats look like:


Statistics
----------------------------------------------------------
          2  recursive calls
          0  db block gets
       4030  consistent gets
          0  physical reads
          0  redo size
    1485454  bytes sent via SQL*Net to client
     866624  bytes received via SQL*Net from client
       4002  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1000  rows processed

Sure enough we do single row fetches, one SQL*Net roundtrip for each row with its LOB locator, then three more roundtrips for each LOB for a total of 4,000 round trips and (approximately) 4,000 consistent gets.

One last test – let’s change the c1 clob to nocache and disable storage in row then repeat the last experiment where the long setting is larger than the longchunksize setting.


alter table t1 move lob(c1) store as (disable storage in row nocache);
execute dbms_stats.gather_table_stats(null,'t1')
select id, c1 from t1;

Statistics
----------------------------------------------------------
          2  recursive calls
          0  db block gets
       4001  consistent gets
       6000  physical reads
          0  redo size
    1485454  bytes sent via SQL*Net to client
     866624  bytes received via SQL*Net from client
       4002  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1000  rows processed

Now there’s a surprise!  I was expecting to see 3000 physical reads as each “out of row, nocache” LOB was accessed three times to pick up the three pieces of longchunksize using direct path reads. So why have I got 6,000 physical reads? Time to enable extended tracing (event 10046, level 8) and repeat.

Here’s a sample from the trace file – this trace is from 18.3, but the LOBREAD lines are a feature that appeared some time around 11.2.0.2.

FETCH #140355181475400:c=58,e=57,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,plh=3617692013,tim=5288719901
WAIT #140355181475400: nam='SQL*Net message from client' ela= 78 driver id=1650815232 #bytes=1 p3=0 obj#=132008 tim=5288720015

WAIT #0: nam='direct path read' ela= 12 file number=2 first dba=2867 block cnt=1 obj#=132008 tim=5288720198
WAIT #0: nam='direct path read' ela= 8 file number=2 first dba=2867 block cnt=1 obj#=132008 tim=5288720288
WAIT #0: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=132008 tim=5288720326
LOBREAD: type=PERSISTENT LOB,bytes=80,c=247,e=246,p=2,cr=1,cu=0,tim=5288720350

WAIT #0: nam='SQL*Net message from client' ela= 82 driver id=1650815232 #bytes=1 p3=0 obj#=132008 tim=5288720483
WAIT #0: nam='direct path read' ela= 12 file number=2 first dba=2867 block cnt=1 obj#=132008 tim=5288720733
WAIT #0: nam='direct path read' ela= 8 file number=2 first dba=2867 block cnt=1 obj#=132008 tim=5288720836
WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=132008 tim=5288720873
LOBREAD: type=PERSISTENT LOB,bytes=80,c=0,e=261,p=2,cr=1,cu=0,tim=5288720898

WAIT #0: nam='SQL*Net message from client' ela= 121 driver id=1650815232 #bytes=1 p3=0 obj#=132008 tim=5288721071
WAIT #0: nam='direct path read' ela= 11 file number=2 first dba=2867 block cnt=1 obj#=132008 tim=5288721216
WAIT #0: nam='direct path read' ela= 8 file number=2 first dba=2867 block cnt=1 obj#=132008 tim=5288721300
WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=132008 tim=5288721335
LOBREAD: type=PERSISTENT LOB,bytes=20,c=0,e=236,p=2,cr=1,cu=0,tim=5288721359

WAIT #0: nam='SQL*Net message from client' ela= 73 driver id=1650815232 #bytes=1 p3=0 obj#=132008 tim=5288721506
WAIT #140355181475400: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=132008 tim=5288721664
FETCH #140355181475400:c=51,e=51,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,plh=3617692013,tim=5288721695
WAIT #140355181475400: nam='SQL*Net message from client' ela= 74 driver id=1650815232 #bytes=1 p3=0 obj#=132008 tim=5288721801

WAIT #0: nam='direct path read' ela= 12 file number=2 first dba=2868 block cnt=1 obj#=132008 tim=5288721939
WAIT #0: nam='direct path read' ela= 47 file number=2 first dba=2868 block cnt=1 obj#=132008 tim=5288722065
WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=132008 tim=5288722122
LOBREAD: type=PERSISTENT LOB,bytes=80,c=357,e=357,p=2,cr=1,cu=0,tim=5288722204

WAIT #0: nam='SQL*Net message from client' ela= 81 driver id=1650815232 #bytes=1 p3=0 obj#=132008 tim=5288722351
WAIT #0: nam='direct path read' ela= 10 file number=2 first dba=2868 block cnt=1 obj#=132008 tim=5288722489
WAIT #0: nam='direct path read' ela= 11 file number=2 first dba=2868 block cnt=1 obj#=132008 tim=5288722671
WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=132008 tim=5288722720
LOBREAD: type=PERSISTENT LOB,bytes=80,c=0,e=349,p=2,cr=1,cu=0,tim=5288722746

WAIT #0: nam='SQL*Net message from client' ela= 76 driver id=1650815232 #bytes=1 p3=0 obj#=132008 tim=5288722874
WAIT #0: nam='direct path read' ela= 11 file number=2 first dba=2868 block cnt=1 obj#=132008 tim=5288723013
WAIT #0: nam='direct path read' ela= 10 file number=2 first dba=2868 block cnt=1 obj#=132008 tim=5288723160
WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=132008 tim=5288723199
LOBREAD: type=PERSISTENT LOB,bytes=20,c=0,e=302,p=2,cr=1,cu=0,tim=5288723224

As you can see, the client seems to FETCH one row then do two direct path reads to read 80 bytes from a LOB, then another two direct path reads for the next 80 bytes, then another two direct path reads for the last 20 bytes. So the 6,000 reads is (strangely) doubling the 3,000 I was expecting to see and the trace file seems to say it’s really happening that way.

Unfortunately I decided to take snapshots of the session stats at this point – and that made things even worse (claiming, for example, 4,000 “consistent gets” but 7,000 “no work consistent gets”), the stats seem to be rather messed up and the whole thing looked very different when I ran this particular test on 12.1.0.2 (which claimed only one direct path read per CLOB rather than the three I was expecting).

Bottom line, though: notwithstanding any oddities of reporting, when your client progam is fetching LOBs from the database, you probably have to put up with:
single row fetches – that get the LOB locator then multiple fetches for each LOB, with the number of LOBREAD calls dependent on the buffer size the client allocates for pulling LOB data from the database.

So when handling LOBs make sure you know how the client can be configured to minimise SQL*Net roundtrips, and check a few simple examples looking at the the trace files, session stats and session events before you start loading data at production volumes.

 

Linux Scripting, Part IV- Scripting for Longevity

We’ve learned a lot about commands, utilities and how to create a script, but we need to discuss the importance of scripting for longevity.

What is Scripting for Longevity?  We have a tendency to focus on scripting to automate something WE might not want to perform manually, but avoid what we think might void our value.  We may try to ensure there is necessity for our role or our knowledge as we create scripts.  This can be built into the execution process, scheduling, arguments, pre-run or post-run steps.  This doesn’t make us an asset, but a liability and against what I call, “the Code of Conduct” when automating.

Questions

The questions you have to ask yourself as you script out solutions are:

  • Am I automating what users need automated or just what I find tedious?
  • Am I building out scripts that have hardcoded values in them vs. dynamically provided, ensuring ongoing support from me?
  • Will I need to update this script in 5 weeks, 6 months, 1 year?

I’m going to admit, that the reason I didn’t embrace Powershell at first, was most of the examples I found were of full of hardcoded values.  I found it incredibly obtuse, but I started to realize that it came from many sources who might not have the scripting history that those of other shells, (this was just my theory, not a lot of evidence to prove on this one, so keep that in mind…)  As Powershell scripts have matured, I’ve noticed how many are starting to build them with more dynamic values and advance scripting options, and with this, I’ve become more comfortable with Powershell.

I think the best way to learn is to see real examples, so let’s demonstrate.

Environment

When you set environment variables as part of the script user’s login, this can eliminate extra coding.  Added to the .bashrc or a .profile will ensure that these values are already known to the script and are available to it. Instead of entering paths to directories or setting up the environment, it can be part of the environment as soon as the user logs in. Example of .profile-  in our example, we’ll call the file .profile_sql, as we can create a couple different ones, then maintain those couple instead of tons of variables in the individual scripts:

export SQL_HOME=/opt/app/mssql
export SQLDB=finance1
export myip=$(curl http://ifconfig.me)
export subscriptionID=$(az account show | grep id |awk  '{print $2}'| tr -d \"\,)

When writing a script, you can then use this to set the values from the profile as part of your script and eliminating the need to set this in the script.  It also allows you to have environment profile to maintain.  In the above example, we’ve set the following:

  • The installation home for SQL Server
  • The name of the default database that will be used when logging in with SQLCMD
  • The IP Address for the user’s workstation
  • The Azure subscription ID for the User

The Script

In our shell script, we’ll begin by setting our shell, then executing the .profile_sql, setting everything we just discussed:

#/bin/bash
. ~/.profile_sql

The profile can have everything needed for this script and any others required for the support of other scripts, allowing for recycling, even if you only need a few for a unique script, this profile can support everything or you can break it up by database or application environment, creating a unique profile for each.

After this, we can then set any unique variables for the script that can’t be or shouldn’t be set at the profile level and begin scripting.  Notice that we can use the variables from the profile inside other variables:

set -euo pipefail
IFS=$'\n\t'
export DTA_DIR=$SQL_HOME/data/
export startip=$myip
export endip=$myip
export servername=$holname
export vnet=$servername"_vnet"l
export snet=$vnet"_snet"

The DTA_DIR value is partially hardcoded, so if we moved the data files, we’d need to update this script.  Think about how we could query the database to gather these values in the profile instead, (an enhancement).  There are often options to pull values dynamically, which will remove the need for ongoing support, allowing for scripts to automatically update as changes are made behind the scenes.

As an example, I capture my IP Address, then set this value in my script after calling the .profile_sql as part of the script, updating the IP Address beforehand.  You can set values, built up from other values-  Note the vnet is a combination of the server name with a naming extension and the subnet, (snet) is just the vnet name with an extension on top of this.  The user will need an identifying name-  if they create one, why would we not want to reuse it and append it to simply locking into the one?

Recycle, Generate, Reuse

This can also be done by using utilities/commands like awk/grep/sed to capture values or queries or commands to pull data that will populate values.  If the data comes from a command and populates to the screen in a way that isn’t easily manipulated, you can output the data to a file that’s easier to work with or can be captured multiple ways as you work through the steps in your script.

Here’s two examples:

az vm image list --offer Oracle --all --publisher Oracle --output table >db.lst

The above Azure command takes the list of images for Oracle offerings, ouptus it in a table formate to a file named db.lst.  Every time I rerun it, it rewrites over the file, recycling the file and ensuring only the latest information.

I can then use this data to fulfill different scripts in different ways.

For my Oracle VM creation, I can capture the fourth column in the table output and print it for options using the AWK utility:

cat db.lst | awk  '{print $4}'

You can also do this dynamically to capture values that you want to return to the screen for the customer to choose from so they always have the latest available:

az account list-locations | grep name | awk  '{print $2}'| tr -d \"\, | grep us | grep -v australia

The above Azure command lists all the location names, (grep name) printing the second word when it contains “us”, but not when it contains, “Australia”, (which also contains “us”.  I’ve also asked it to trim out the special characters from the output, (tr -d).

Notice again, each of the commands are separated by a “|”, allowing me to connect different commands and utilities.

Building this type of features into scripts create longevity and sustainability that hard coding and manual intervention can’t.  Always ask yourself:

  • Is there a way to dynamically generate this value?
  • Have I entered anything that will require me to revisit this script sooner than later?
  • Are there utilities that can replace what I’m trying to script out manually?
  • Should I be placing this automation in the specific script or have a wrapper script or profile handle the variable setting?

These are all questions you should be asking yourself as you write a script or if you’re enhancing one.  Scripting is not a one-shot deal.  You should be working to enhance and built out scripts to create more value for you, the user and the business ongoing.

 



Tags:  ,


Del.icio.us



Facebook

TweetThis

Digg

StumbleUpon




Copyright ©  [Linux Scripting, Part IV- Scripting for Longevity], All Right Reserved. 2019.

Oracle RMAN Backup Optimization ON and backup archivelog command.

TL;DR: BACKUP OPTIMIZATION ON acts as a NOT BACKED UP 1 TIMES for the BACKUP ARCHIVELOG ALL command.

I always mention “not backed up n times” in my “backup archive log” commands because I think it makes sense to precise exactly how many copies of archived log we want to have available to restore from each backup device

I often configure “backup optimization on” when thinking about the tablespaces I put read-only so that they are not backed up each time(given that there is no suppression of old backups that are unknown to RMAN).

But I’ve never realized that BACKUP OPTIMIZATION and NOT BACKED UP actually both work on datafiles, archivelogs, and backupsets. And then the following is redundant:

configure backup optimization on;
backup archivelog all not backed up 1 times;

It is not a problem at all and I prefer to keep this redundancy, especially because of the name: BACKUP OPTIMIZATION looks like something which concerns only the performance, but NOT BACKED UP 1 TIMES is explicit about the availability.

As I did a quick test to verify the behavior, I share it here. Of course, all this is documented in backup optimization and notBackedUpSpec.

I start to delete all archived logs, and backups of them, in a lab database, and set the default for BACKUP OPTIMIZATION:

set nocfau;
delete noprompt backup of archivelog all;
delete noprompt archivelog all;
configure backup optimization clear;
show backup optimization;

Note that I’ve disabled controlfile autobackup (with NOCFAU) to keep the screenshots small.

I run two backups of archivelog:

backup archivelog all;
backup archivelog all;

I have two copies of backups for sequence 280:

list backup of archivelog all;

This is not usually what we want. We try to backup archivelogs frequently, for availability reasons, but also want to keep backup storage low, for retention reasons.

Not Backed Up n Times

If I don’t want a 3rd copy:

backup archivelog all not backed up 2 times;

The sequence 280 has been skipped because “already backed up” 2 times. Sequence 281 has only one backup and 282 had no backup yet.

Usually, one backup is sufficient, and this is what I schedule:

backup archivelog all not backed up 1 times;


Backup Optimization

The point of this post is to show what happens when BACKUP OPTIMIZATION is ON and we do not specify “not backed up n times”

configure backup optimization on;
backup archivelog all;

Here, the sequences that already have been backed up one time have been skipped. Only the last one (generated by “backup archivelog all” which always archives the current log) is backed up.

Archivelog range

When I don’t want to archive the current log before, I run with an UNTIL TIME ’SYSDATE’:

backup archivelog until time 'sysdate';
backup archivelog until time 'sysdate';

In this case, even with BACKUP OPTIMIZATION to ON all archived logs are backed-up again. If I don’t want to, I need to explicitly mention it:

backup archivelog until time 'sysdate' not backed up 1 times;

This is because BACKUP OPTIMIZATION ON is applied to BACKUP ARCHIVELOG only when no range is provided (FROM/UNTIL/BETWEEN SCN/TIME/SEQUENCE). This only with ALL (or LIKE).

Announcement: “Oracle Performance Diagnostics and Tuning” Webinar – 20-23 August 2019.

I have just scheduled a new “Oracle Performance Diagnostics and Tuning” webinar to run between 20-23 August 2019, specifically for my friends in New Zealand, but is open to anyone. It will run between 7am-11am AEST each day, which is perfect for those in NZ, but also other parts of the world such as Western […]

Statistics on Load

One of the talks I gave recently on the Groundbreaker Yatra tour was on the changes that arrive with the optimizer when you move to 12c and above. We talked about 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


SQL> create table t (id primary key, tag)
  2  as select rownum id, to_char(rownum) tag
  3  from dual connect by level <= 50000;

Table created.

SQL>
SQL> select num_rows from user_tables
  2  where table_name = 'T';

  NUM_ROWS
----------
     50000

1 row selected.

SQL>
SQL> select leaf_blocks
  2  from user_indexes
  3  where table_name = 'T';

LEAF_BLOCKS
-----------
        103

1 row selected.

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.


SQL> drop table t purge;

Table dropped.

SQL>
SQL> create table t (id primary key, tag)
  2  organization index
  3  as select rownum id, to_char(rownum) tag
  4  from dual connect by level <= 50000;

Table created.

SQL>
SQL> select num_rows from user_tables
  2  where table_name = 'T';

  NUM_ROWS
----------


1 row selected.

SQL>
SQL> select leaf_blocks
  2  from user_indexes
  3  where table_name = 'T';

LEAF_BLOCKS
-----------


1 row selected.

Initially I suspected that the reason for this is that since the statistics that are being picked up on the fly are being collected on the source data, it only becomes possible for those statistics to be transposed if the target table structure is the same as the source. But if that were (strictly) the case, then other examples would exhibit a similar restriction, such as going from compressed data to uncompressed or vice versa. But you can see that the CTAS on load statistics are fine in this circumstance:


SQL> create table t1 (id primary key, tag)
  2  compress
  3  as select * from t;

Table created.

SQL>
SQL> select num_rows from user_tables
  2  where table_name = 'T1';

  NUM_ROWS
----------
     99999

SQL>
SQL> select leaf_blocks
  2  from user_indexes
  3  where table_name = 'T1';

LEAF_BLOCKS
-----------
        208

Similarly, even if I am copying from an identical index-organized table, the statistics will not be collected.


SQL> create table t2 (id primary key, tag)
  2  organization index
  3  as select * from t;

Table created.

SQL> select num_rows from user_tables
  2  where table_name = 'T2';

  NUM_ROWS
----------


SQL>
SQL> select leaf_blocks
  2  from user_indexes
  3  where table_name = 'T2';

LEAF_BLOCKS
-----------

Whatever the root cause is, just be aware, that if you are direct-path loading IOTs, then make sure you take the time to to also collect statistics on that table before you start querying it in earnest.

Oracle 19c Automatic Indexing: Configuration (All I Need)

In this post, I’ll discuss how to configure the new Automatic Indexing capability introduced in Oracle Database 19c. The intent of Oracle here is to make the configuration of Automatic Indexing as simplistic as possible, with as few levers for DBAs to potentially stuff up as possible. The ultimate goal would be to have a […]