Search

Top 60 Oracle Blogs

Recent comments

Oakies Blog Aggregator

When Oracle Statistic Gathering times out.

In a previous post, I explained how to see where the Auto Stats job has been running and timed out:

SYS.STATS_TARGET$

I got a case where it always timed out at the end of the standard maintenance window. One table takes many hours, longer than the largest maintenance window, it will always be killed at the end. And, because it stayed stale, and staler each day, this table was always listed first by the Auto Stat job. And many tables never got their chance to get their stats gathered for … years.

In that case, the priority is to gather statistics. That can be long. Then I run the job manually:

exec dbms_auto_task_immediate.gather_optimizer_stats;

Here, it will never time-out (and the auto job will not start at maintenance window start). This manual gathering can take many days. Of course, this gives time to think about a solution, like reading Nigel Bayliss recommendations:

How to Gather Optimizer Statistics Fast!

If I want to kill the manual job, because one table takes really too long and I decide to skip it for the moment, here is my query to find it:

select 'alter system kill session '''||sid||','||serial#||',@'||inst_id||''' /* '||action||' started on '||logon_time||'*/;' "Kill me with this:" from gv$session where module='DBMS_SCHEDULER' and action like 'ORA$AT^_OS^_MANUAL^_%' escape '^';

Which gives me the kill statement, and the time when I started it:

Before killing, I’ll check the long queries from it with the goal to find a solution for it:

select executions,users_executing,round(elapsed_time/1e6/60/60,1) hours,substr(coalesce(info,sql_text),1,60) info,sql_id from gv$sql natural left outer join (select address,hash_value,sql_id,plan_hash_value,child_address,child_number,id,rtrim(operation||' '||object_owner||' '||object_name) info from gv$sql_plan where object_name is not null) where elapsed_time>1e6*10*60 and action like 'ORA$AT_OS_%' order by last_active_time,id

In this example, I can see that one table is running for 4 days:

Now I kill this statistic gathering job. What I want for the moment is to exclude this table from the automatic statistics gathering. Unfortunately, I cannot change the AUTOSTATS_TARGET at table level, then I lock the stats. And run DBMS_AUTO_TASK_IMMEDIATE.GATHER_OPTIMIZER_STATS again.

This is just to quickly resolve the gap we had on many tables. The few tables locked will need further considerations. I even got a funny case where the statistics gathering was long because… statistics where stale. It was in 11g, an IOT where the CBO decided to with ‘db file sequential reads’. I deleted the statistics and the gathering used an optimized execution plan then. When you have really bad statistics, it may be better to have no statistics (and then do dynamic sampling) rather than completely stale ones.

With and without WITH_PLSQL within a WITH SQL statement

OK, let’s be honest right up front. The motivation for this post is solely to be able to roll out a tongue twisting blog post title Smile. But hopefully there’s some value as well in here for you if you’re hitting the error:

ORA-32034: unsupported use of WITH clause

First some background. A cool little enhancement to the WITH clause came along in 12c that allowed PLSQL functions to be defined within the scope of the executing SQL statement. To see the benefit of this, consider the following example that I have a personal affinity with (given my surname).

Let’s say I’ve allowed mixed-case data in a table that holds names.


SQL> select surname
  2  from   names;

SURNAME
------------------------------
jones
brown
SMITH

There’s nothing inherently wrong here, but in terms of rendering that data in a report or on screen, it would be nice to have some consistency.

“No problem” you think, “I’ll just slap an INITCAP in there”


SQL> select initcap(surname)
  2  from   names;

INITCAP(SURNAME)
------------------------------
Jones
Brown
Smith

That works fine of course until …. yours truly gets added into the mix Smile. After a couple of new rows are added, we can start to see the shortcomings of INITCAP.


SQL> select initcap(surname)
  2  from   names;

INITCAP(SURNAME)
------------------------------
Jones
Brown
Smith
Mcdonald
Johnson'S

I’d like a capital D, and letters that follow apostrophes have some nuances that might need handling. This can be solved without too much fuss – with a little bit of PLSQL I can produce a custom version of INITCAP that will handle these exceptional cases.


SQL> create or replace
  2  function MY_INITCAP(p_string varchar2) return varchar2 is
  3    l_string varchar2(1000) := p_string;
  4  begin
  5    if regexp_like(l_string,'(Mac[A-Z]|Mc[A-Z])') then
  6        null;
  7    elsif l_string like '''%' then
  8        null;
  9    else
 10      l_string := initcap(l_string);
 11      if l_string like '_''S%' then
 12         null;
 13      else
 14         l_string := replace(l_string,'''S','''s');
 15      end if;
 16    end if;
 17
 18    return l_string;
 19  end;
 20  /

SQL> select my_initcap(surname)
  2  from   names;

MY_INITCAP(SURNAME)
--------------------------
Jones
Brown
Smith
McDonald
Johnson's

But perhaps I’d like that functionality inline with the SQL so that a future maintainer can directly see what I’ve done. Yes, I could refactor the code to be 100% SQL with no reliance on PLSQL using something like this:


SQL> select
  2    case
  3      when regexp_like(surname,'(Mac[A-Z]|Mc[A-Z])') then surname
  4      when surname like '''%' then surname
  5      when initcap(surname) like '_''S%' then surname
  6      else replace(initcap(surname),'''S','''s')
  7    end ugh
  8  from names;

UGH
-------------------------------
Jones
Brown
Smith
McDonald
Johnson's

But if I’m doing this to help a future maintainer….well… that convoluted CASE statement probably isn’t such a nice remnant for them Smile. So since 12c, we’ve been able to add that PLSQL code directly within the SQL statement itself.


SQL> WITH
  2    function my_initcap(p_string varchar2) return varchar2 is
  3      l_string varchar2(1000) := p_string;
  4    begin
  5      if regexp_like(l_string,'(Mac[A-Z]|Mc[A-Z])') then
  6          null;
  7      elsif l_string like '''%' then
       ...
 17
 18      return l_string;
 19    end;
 20  select my_initcap(surname)
 21  from   names;

MY_INITCAP(SURNAME)
-----------------------------------------
Jones
Brown
Smith
McDonald

Hopefully you can now see the benefit of the feature. Now back to the topic at hand, the ORA-32034 error. If you attempt to use the feature within an INSERT, UPDATE or DELETE statement, you’ll get a surprise:


SQL> insert into TARGET_TABLE
  2  WITH
  3    function my_initcap(p_string varchar2) 
  4                return varchar2 is
  5      l_string varchar2(1000) := p_string;
  6    begin
          ...
 20    end;
 21  select my_initcap(surname)
 22  from   names;
 23  /

WITH
*
ERROR at line 2:
ORA-32034: unsupported use of WITH clause

To overcome this, you need to specify the WITH_PLSQL hint


SQL> insert /*+ WITH_PLSQL */ into TARGET_TABLE
  2  WITH
  3    function my_initcap(p_string varchar2) 
  4                return varchar2 is
  5      l_string varchar2(1000) := p_string;
  6    begin
          ...
 20    end;
 21  select my_initcap(surname)
 22  from   names;
 23  /

5 rows inserted.

I’ve never ascertained the reason precisely why the hint is needed (I’m asking around internally within the database group), but I have a hypothesis: Given that you could potentially write anything within an PLSQL function (including independent transactions), my guess is that the hint is a flag to the database to say “Have an extra careful double-check of the function code to make sure it’s not doing anything dangerous, in particular, to the table we doing the DML on”.

I might be 100% wrong here – I’ll update the post if I get more information in future.

Advice on fragmentation and shrinkage

If you have performed some sort of data cleanup or similar on a table, then the deleted space will be reused by future insertions. But if

  • that cleanup was the last task you were performing on that table, ie, you were not expecting a lot of new data to ever come in again, or
  • you are performing a lot of full scan queries on that table and you want to make sure they are as efficient as possible

then there may be benefits to performing a shrink on that table to reclaim that space. One of the cool things about the segment advisor is that it will detect if there are some benefits to be gained by shrinking a segment. Here’s an example of that. I create a large table and then delete every 2nd row.


SQL> create table scott.demo_table as
  2  select d.* from dba_objects d,
  3   ( select 1 from dual connect by level <= 100 ) ;

Table created.

SQL>
SQL> delete from scott.demo_table
  2  where mod(object_id,2) = 0;

4125300 rows deleted.

SQL> commit;

Commit complete.

Now I run the segment advisor and I get a nice report on what can be done to reclaim that space.



SQL>
SQL>
SQL> set serveroutput on size 1000000
SQL> set linesize 200
SQL> set verify off
SQL>
SQL> declare
  2    l_object_id     number;
  3    l_task_name     varchar2(50) := 'TEST_TASK';
  4    l_object_type   varchar2(50) := upper('TABLE');
  5    l_attr1         varchar2(50) := upper('SCOTT');
  6    l_attr2         varchar2(50) := upper('DEMO_TABLE');
  7  begin
  8    begin  DBMS_ADVISOR.delete_task(task_name => l_task_name); exception when others then null; end;
  9
 10    dbms_advisor.create_task (
 11      advisor_name      => 'Segment Advisor',
 12      task_name         => l_task_name);
 13
 14    dbms_advisor.create_object (
 15      task_name   => l_task_name,
 16      object_type => l_object_type,
 17      attr1       => l_attr1,
 18      attr2       => l_attr2,
 19      attr3       => NULL,
 20      attr4       => 'null',
 21      attr5       => NULL,
 22      object_id   => l_object_id);
 23
 24    dbms_advisor.set_task_parameter (
 25      task_name => l_task_name,
 26      parameter => 'RECOMMEND_ALL',
 27      value     => 'TRUE');
 28
 29    dbms_advisor.execute_task(task_name => l_task_name);
 30
 31
 32    for cur_rec in (select f.impact,
 33                           o.type,
 34                           o.attr1,
 35                           o.attr2,
 36                           f.message,
 37                           f.more_info
 38                    from   dba_advisor_findings f
 39                           join dba_advisor_objects o on f.object_id = o.object_id and f.task_name = o.task_name
 40                    where  f.task_name = l_task_name
 41                    order by f.impact desc)
 42    loop
 43      dbms_output.put_line('..');
 44      dbms_output.put_line('Type             : ' || cur_rec.type);
 45      dbms_output.put_line('Attr1            : ' || cur_rec.attr1);
 46      dbms_output.put_line('Attr2            : ' || cur_rec.attr2);
 47      dbms_output.put_line('Message          : ' || cur_rec.message);
 48      dbms_output.put_line('More info        : ' || cur_rec.more_info);
 49    end loop;
 50
 51    dbms_advisor.delete_task(task_name => l_task_name);
 52  end;
 53  /
..
Type             : TABLE
Attr1            : SCOTT
Attr2            : DEMO_TABLE
Message          : Enable row movement of the table SCOTT.DEMO_TABLE and perform shrink, estimated savings is 596868412 bytes.
More info        : Allocated Space:1342177280: Used Space:745308868: Reclaimable Space :596868412:

PL/SQL procedure successfully completed.

But what if that segment sits in a manual segment space managed tablespace? We can see from the above that a shrink-style operation will yield some benefits, but there’s a problem. You cannot perform an ALTER TABLE SHRINK command unless a segment sits in an ASSM tablespace. So is the segment advisor of any use in these cases? Let’s re-run the demo to find out


SQL> select * from dba_tablespaces where tablespace_name = 'NO_ASSM'
  2  @pr
==============================
TABLESPACE_NAME               : NO_ASSM
BLOCK_SIZE                    : 8192
INITIAL_EXTENT                : 65536
NEXT_EXTENT                   :
MIN_EXTENTS                   : 1
MAX_EXTENTS                   : 2147483645
MAX_SIZE                      : 2147483645
PCT_INCREASE                  :
MIN_EXTLEN                    : 65536
STATUS                        : ONLINE
CONTENTS                      : PERMANENT
LOGGING                       : LOGGING
FORCE_LOGGING                 : NO
EXTENT_MANAGEMENT             : LOCAL
ALLOCATION_TYPE               : SYSTEM
PLUGGED_IN                    : NO
SEGMENT_SPACE_MANAGEMENT      : MANUAL
DEF_TAB_COMPRESSION           : DISABLED
RETENTION                     : NOT APPLY
BIGFILE                       : NO
PREDICATE_EVALUATION          : HOST
ENCRYPTED                     : NO
COMPRESS_FOR                  :
DEF_INMEMORY                  : DISABLED
DEF_INMEMORY_PRIORITY         :
DEF_INMEMORY_DISTRIBUTE       :
DEF_INMEMORY_COMPRESSION      :
DEF_INMEMORY_DUPLICATE        :
SHARED                        : SHARED
DEF_INDEX_COMPRESSION         : DISABLED
INDEX_COMPRESS_FOR            :
DEF_CELLMEMORY                :
DEF_INMEMORY_SERVICE          :
DEF_INMEMORY_SERVICE_NAME     :
LOST_WRITE_PROTECT            : OFF
CHUNK_TABLESPACE              : N

PL/SQL procedure successfully completed.

SQL>
SQL> create table scott.demo_table tablespace no_assm as
  2  select d.* from dba_objects d,
  3   ( select 1 from dual connect by level <= 100 ) ;

Table created.

SQL>
SQL> delete from scott.demo_table
  2  where mod(object_id,2) = 0;

4125300 rows deleted.

SQL> commit;

Commit complete.

SQL>
SQL>
SQL> set serveroutput on size 1000000
SQL> set linesize 200
SQL> set verify off
SQL>
SQL> declare
  2    l_object_id     number;
  3    l_task_name     varchar2(50) := 'TEST_TASK';
  4    l_object_type   varchar2(50) := upper('TABLE');
  5    l_attr1         varchar2(50) := upper('SCOTT');
  6    l_attr2         varchar2(50) := upper('DEMO_TABLE');
  7  begin
  8    begin  DBMS_ADVISOR.delete_task(task_name => l_task_name); exception when others then null; end;
  9
 10    dbms_advisor.create_task (
 11      advisor_name      => 'Segment Advisor',
 12      task_name         => l_task_name);
 13
 14    dbms_advisor.create_object (
 15      task_name   => l_task_name,
 16      object_type => l_object_type,
 17      attr1       => l_attr1,
 18      attr2       => l_attr2,
 19      attr3       => NULL,
 20      attr4       => 'null',
 21      attr5       => NULL,
 22      object_id   => l_object_id);
 23
 24    dbms_advisor.set_task_parameter (
 25      task_name => l_task_name,
 26      parameter => 'RECOMMEND_ALL',
 27      value     => 'TRUE');
 28
 29    dbms_advisor.execute_task(task_name => l_task_name);
 30
 31
 32    for cur_rec in (select f.impact,
 33                           o.type,
 34                           o.attr1,
 35                           o.attr2,
 36                           f.message,
 37                           f.more_info
 38                    from   dba_advisor_findings f
 39                           join dba_advisor_objects o on f.object_id = o.object_id and f.task_name = o.task_name
 40                    where  f.task_name = l_task_name
 41                    order by f.impact desc)
 42    loop
 43      dbms_output.put_line('..');
 44      dbms_output.put_line('Type             : ' || cur_rec.type);
 45      dbms_output.put_line('Attr1            : ' || cur_rec.attr1);
 46      dbms_output.put_line('Attr2            : ' || cur_rec.attr2);
 47      dbms_output.put_line('Message          : ' || cur_rec.message);
 48      dbms_output.put_line('More info        : ' || cur_rec.more_info);
 49    end loop;
 50
 51    dbms_advisor.delete_task(task_name => l_task_name);
 52  end;
 53  /
..
Type             : TABLE
Attr1            : SCOTT
Attr2            : DEMO_TABLE
Message          : Perform re-org on the object DEMO_TABLE, estimated savings is 600175966 bytes.
More info        : Allocated Space:1342177280: Used Space:742001314: Reclaimable Space :600175966:

PL/SQL procedure successfully completed.

SQL>
SQL>

As you can see, the segment advisor will take that into account and adjust its recommendations accordingly. And one of the cool things with 12.2 and above, is that tables can be reorganised without an outage!


SQL> alter table scott.demo_table move online;

Table altered.

Nice!

Generic data models … generic applications … ugh

There’s a hesitation to publish this example, because publishing it may be interpreted as an endorsement of this approach and it certainly isn’t. Over the years there have been plenty of articles describing the long term pain that typically comes from generic data models. Here’s a few to whet your appetite.

https://rodgersnotes.wordpress.com/2010/09/21/muck-massively-unified-code-key-generic-three-table-data-model/

https://www.red-gate.com/simple-talk/opinion/opinion-pieces/bad-carma/

https://asktom.oracle.com/pls/asktom/asktom.search?tag=query-on-design

But I’m posting this example because it serves as a nice tutorial for DBMS_SQL, and also, there is perhaps the suggestion that the requestor is moving away from a generic data model to a more well structured one. We’ll go with the benefit of the doubt here Smile

The incoming data for this example was the “classic” generic data model where the table name, column name and column values were not defined in the data dictionary but as values within a table


SQL> CREATE TABLE data_table
  2    (
  3      row_seq      int,
  4      table_name   VARCHAR2(30),
  5      column_name  VARCHAR2(30),
  6      column_value VARCHAR2(30)
  7    );

Table created.

SQL>
SQL>
SQL>
SQL> insert into data_table values ( 1,'TEST_TAB','ORDER_NUMBER','ORD1001');

1 row created.

SQL> insert into data_table values ( 1,'TEST_TAB','CUST_NAME','CUST1001');

1 row created.

SQL> insert into data_table values ( 1,'TEST_TAB','INVOICE_NUMBER','INV001');

1 row created.

SQL> insert into data_table values ( 1,'TEST_TAB','ORDER_DATE','04/11/2018 15:14:00');

1 row created.

SQL> insert into data_table values ( 1,'TEST_TAB','AMOUNT','1001');

1 row created.

SQL>
SQL> insert into data_table values ( 2,'TEST_TAB','ORDER_NUMBER','ORD1002');

1 row created.

SQL> insert into data_table values ( 2,'TEST_TAB','CUST_NAME','CUST1002');

1 row created.

SQL> insert into data_table values ( 2,'TEST_TAB','INVOICE_NUMBER','INV002');

1 row created.

SQL> insert into data_table values ( 2,'TEST_TAB','ORDER_DATE','02/11/2018 15:14:00');

1 row created.

SQL> insert into data_table values ( 2,'TEST_TAB','AMOUNT','1002');

1 row created.

SQL>
SQL> commit;

Commit complete.

SQL> select * from data_table;

   ROW_SEQ TABLE_NAME                     COLUMN_NAME                    COLUMN_VALUE
---------- ------------------------------ ------------------------------ ------------------------------
         1 TEST_TAB                       ORDER_NUMBER                   ORD1001
         1 TEST_TAB                       CUST_NAME                      CUST1001
         1 TEST_TAB                       INVOICE_NUMBER                 INV001
         1 TEST_TAB                       ORDER_DATE                     04/11/2018 15:14:00
         1 TEST_TAB                       AMOUNT                         1001
         2 TEST_TAB                       ORDER_NUMBER                   ORD1002
         2 TEST_TAB                       CUST_NAME                      CUST1002
         2 TEST_TAB                       INVOICE_NUMBER                 INV002
         2 TEST_TAB                       ORDER_DATE                     02/11/2018 15:14:00
         2 TEST_TAB                       AMOUNT                         1002

10 rows selected.

The task here was to take those values and convert into INSERT statements, so with the sample data above, the aim is to insert those rows into a table called TEST_TAB. Given that the DML must generated entirely from metadata, we can use DBMS_SQL to handle it. Constructing the DML is easier than you might think due to some handy analytic SQL functions plus the ever useful LISTAGG.


SQL> select
  2    row_number() over
  3      ( partition by table_name, row_seq order by column_name ) as seq,
  4    count(*) over
  5      ( partition by table_name, row_seq ) as col_cnt,
  6    listagg(column_name,',') within group
  7      ( order by column_name ) over ( partition by table_name, row_seq ) as cols,
  8    listagg(':'||column_name,',') within group
  9      ( order by column_name ) over ( partition by table_name, row_seq ) as bindcols,
 10    column_value
 11  from data_table
 12  order by table_name, row_seq, column_name
 13  @pr
==============================
SEQ                           : 1
COL_CNT                       : 5
COLS                          : AMOUNT,CUST_NAME,INVOICE_NUMBER,ORDER_DATE,ORDER_NUMBER
BINDCOLS                      : :AMOUNT,:CUST_NAME,:INVOICE_NUMBER,:ORDER_DATE,:ORDER_NUMBER
COLUMN_VALUE                  : 1001
==============================
SEQ                           : 2
COL_CNT                       : 5
COLS                          : AMOUNT,CUST_NAME,INVOICE_NUMBER,ORDER_DATE,ORDER_NUMBER
BINDCOLS                      : :AMOUNT,:CUST_NAME,:INVOICE_NUMBER,:ORDER_DATE,:ORDER_NUMBER
COLUMN_VALUE                  : CUST1001
==============================
SEQ                           : 3
COL_CNT                       : 5
COLS                          : AMOUNT,CUST_NAME,INVOICE_NUMBER,ORDER_DATE,ORDER_NUMBER
BINDCOLS                      : :AMOUNT,:CUST_NAME,:INVOICE_NUMBER,:ORDER_DATE,:ORDER_NUMBER
COLUMN_VALUE                  : INV001
==============================
SEQ                           : 4
COL_CNT                       : 5
COLS                          : AMOUNT,CUST_NAME,INVOICE_NUMBER,ORDER_DATE,ORDER_NUMBER
BINDCOLS                      : :AMOUNT,:CUST_NAME,:INVOICE_NUMBER,:ORDER_DATE,:ORDER_NUMBER
COLUMN_VALUE                  : 04/11/2018 15:14:00
==============================
SEQ                           : 5
COL_CNT                       : 5
COLS                          : AMOUNT,CUST_NAME,INVOICE_NUMBER,ORDER_DATE,ORDER_NUMBER
BINDCOLS                      : :AMOUNT,:CUST_NAME,:INVOICE_NUMBER,:ORDER_DATE,:ORDER_NUMBER
COLUMN_VALUE                  : ORD1001
==============================
SEQ                           : 1
COL_CNT                       : 5
COLS                          : AMOUNT,CUST_NAME,INVOICE_NUMBER,ORDER_DATE,ORDER_NUMBER
BINDCOLS                      : :AMOUNT,:CUST_NAME,:INVOICE_NUMBER,:ORDER_DATE,:ORDER_NUMBER
COLUMN_VALUE                  : 1002
==============================
SEQ                           : 2
COL_CNT                       : 5
COLS                          : AMOUNT,CUST_NAME,INVOICE_NUMBER,ORDER_DATE,ORDER_NUMBER
BINDCOLS                      : :AMOUNT,:CUST_NAME,:INVOICE_NUMBER,:ORDER_DATE,:ORDER_NUMBER
COLUMN_VALUE                  : CUST1002
==============================
SEQ                           : 3
COL_CNT                       : 5
COLS                          : AMOUNT,CUST_NAME,INVOICE_NUMBER,ORDER_DATE,ORDER_NUMBER
BINDCOLS                      : :AMOUNT,:CUST_NAME,:INVOICE_NUMBER,:ORDER_DATE,:ORDER_NUMBER
COLUMN_VALUE                  : INV002
==============================
SEQ                           : 4
COL_CNT                       : 5
COLS                          : AMOUNT,CUST_NAME,INVOICE_NUMBER,ORDER_DATE,ORDER_NUMBER
BINDCOLS                      : :AMOUNT,:CUST_NAME,:INVOICE_NUMBER,:ORDER_DATE,:ORDER_NUMBER
COLUMN_VALUE                  : 02/11/2018 15:14:00
==============================
SEQ                           : 5
COL_CNT                       : 5
COLS                          : AMOUNT,CUST_NAME,INVOICE_NUMBER,ORDER_DATE,ORDER_NUMBER
BINDCOLS                      : :AMOUNT,:CUST_NAME,:INVOICE_NUMBER,:ORDER_DATE,:ORDER_NUMBER
COLUMN_VALUE                  : ORD1002

Now that we have each data value, plus all the components for an INSERT statement (including binding values not using any literals), we’re good to go:


SQL> create table test_tab (
  2    order_number varchar2(10),
  3    cust_name varchar2(10),
  4    invoice_number varchar2(10),
  5    order_date varchar2(30),
  6    amount varchar2(10)
  7  );

Table created.


SQL> declare
  2    l_sql varchar2(32000);
  3    l_cur     pls_integer := dbms_sql.open_cursor;
  4    l_execute pls_integer;
  5  begin
  6    for i in (
  7      select   table_name,
  8               column_name,
  9               row_number() over ( partition by table_name, row_seq order by column_name ) as seq,
 10               count(*) over ( partition by table_name, row_seq ) as col_cnt,
 11               listagg(column_name,',') within group ( order by column_name ) over ( partition by table_name, row_seq ) as cols,
 12               listagg(':'||column_name,',') within group ( order by column_name ) over ( partition by table_name, row_seq ) as bindcols,
 13               column_value
 14      from data_table
 15      order by table_name, row_seq, column_name
 16   ) loop
 17       if i.seq = 1 then
 18         l_sql := 'insert into '||i.table_name||'('||i.cols||') values ('||i.bindcols||')';
 19         dbms_sql.parse(l_cur,l_sql,dbms_sql.native);
 20       end if;
 21       dbms_sql.bind_variable(l_cur,i.column_name,i.column_value);
 22       if i.seq = i.col_cnt then
 23         l_execute := dbms_sql.execute(l_cur);
 24       end if;
 25   end loop;
 26   dbms_sql.close_cursor(l_cur);
 27  end;
 28  /

PL/SQL procedure successfully completed.

SQL>
SQL> select * from test_tab;

ORDER_NUMB CUST_NAME  INVOICE_NU ORDER_DATE                     AMOUNT
---------- ---------- ---------- ------------------------------ ----------
ORD1001    CUST1001   INV001     04/11/2018 15:14:00            1001
ORD1002    CUST1002   INV002     02/11/2018 15:14:00            1002

2 rows selected.

SQL>

Timestamp Oddity

[Editorial note: this is something I started writing in 2013, managed to complete in 2017, and still failed to publish. It should have been a follow-on to another posting on the oddities of timestamp manipulation.]

Just as national language support used to be, timestamps and time-related columns are still a bit of a puzzle to the Oracle world – so much so that OEM could cripple a system if it was allowed to do the check for “failed logins over the last 30 minutes”. And, just like NLS, it’s one of those things that you use so rarely that you keep forgetting what went wrong the last time you used it. Here’s one little oddity that I reminded myself about recently:

rem
rem     Script:         timestamp_anomaly.sql
rem     Author:         Jonathan Lewis
rem     Dated:          April 2013
rem
create table t1 (
        ts_tz   timestamp(9) with time zone,
        ts_ltz  timestamp(9) with local time zone
);

insert into t1 values(systimestamp, systimestamp);
commit;

alter table t1 add constraint ts_ltz_uk unique (ts_ltz);
alter table t1 add constraint ts_tz_uk  unique (ts_tz);


Nothing terribly difficult – just a table with two variants on the timestamp data type and a unique constraint on both: except for one problem. Watch what happens as I create the unique constraints:

SQL> alter table t1 add constraint ts_ltz_uk unique (ts_ltz);

Table altered.

SQL> alter table t1 add constraint ts_tz_uk  unique (ts_tz);
alter table t1 add constraint ts_tz_uk  unique (ts_tz)
                                        *
ERROR at line 1:
ORA-02329: column of datatype TIME/TIMESTAMP WITH TIME ZONE cannot be unique or a primary key

Slightly unexpected – unless you’ve memorized the manuals, of course, which I hadn’t. I wonder if you can create a unique index on timestamp with time zone:


SQL> create unique index ts_tz_uk on t1(ts_tz);

Index created.

You can’t have a unique constraint, but you CAN create a unique index! How curious – did that really happen ?

SQL> select index_name, column_name from user_ind_columns where table_name = 'T1';

INDEX_NAME           COLUMN_NAME
-------------------- --------------------
TS_LTZ_UK            TS_LTZ
TS_TZ_UK             SYS_NC00003$

The index is on a column called SYS_NC00003$ – which looks suspiciously like one of those “function-based-index” things:


SQL> select * from user_ind_expressions where table_name = 'T1';

INDEX_NAME           TABLE_NAME           COLUMN_EXPRESSION                        COLUMN_POSITION
-------------------- -------------------- ---------------------------------------- ---------------
TS_TZ_UK             T1                   SYS_EXTRACT_UTC("TS_TZ")                               1

Oracle has silently invoked the sys_extract_utc() function on our (free-floating) timestamp column to normalize it to UTC. This is really not very friendly but it does make sense, of course – it would be rather expensive to enforce uniqueness if there were (at least) 24 different ways of storing the same absolute value – and 24 is a conservative estimate.

 

 

PeopleSoft Adminstrator Podcast: #184 – nVision Performance

I recorded a second podcast with Dan Iverson and Kyle Benson for the PeopleSoft Administrator Podcast, this time about nVision.

(10 May 2019) #184 – nVision Performance

You can listen to the podcast on psadmin.io, or subscribe with your favourite podcast player, or in iTunes.

Linux Scripting, Part III

In the previous blog posts, we learned how to set up the first part of a standard shell script- how to interactively set variables, including how to pass them as part of the script execution. In this next step, we’ll use those to build out Azure resources. If you’re working on-premises, you can use this type of scripting with SQL Server 2019 Linux but will need to use CLI commands and SQLCMD. I will cover this in later posts, but honestly, the cloud makes deployment quicker for any business to get what they need deployed and with the amount of revenue riding on getting to market faster, this should be the first choice of any DBA with vision.

When I started at Microsoft close to a year ago, along with subsequent journey to Azure, it was challenging to locate the Azure commands for BASH, (which is still incorrectly referred to as “the CLI”). I could locate the Powershell commands immediately, but as Microsoft matures its foothold in Linux, it is realizing, just like those of us that were adept in other shell languages- there’s one shell that’s winning the war and that’s BASH. We can love the shell we started with, the one that has served us well, but BASH has been around so long and is so robust, we need to recognize that instead of re-inventing the wheel unnecessarily, we can use what is out there already. I now locate the AZ commands to be used with BASH faster than the Powershell specific commands. I am glad they will continue to support Powershell Azure commands, but highly recommend learning about the standard AZ commands, using them in Powershell and learning BASH for the future.

OK, off my soapbox and let’s get to the learning…</p />
</p></div>

    	  	<div class=

DBMS_JOB – the joy of transactions

This is a followup to yesterdays post on DBMS_JOB and is critical if you’re upgrading to 19c soon. Mike Dietrich wrote a nice piece last week on the “under the covers” migration of the now deprecated DBMS_JOB package to the new Scheduler architecture. You should check it out before reading on here.

Mike’s post concerned mainly what would happen during upgrade (spoiler: the DBMS_JOB jobs become scheduler jobs but you can maintain them using the old API without drama), but immediately Twitter was a buzz with a couple of concerns that I wanted to address:

1) What about new jobs submitted via the old API after the upgrade?

For comparison, here’s what you see currently in 18c – DBMS_JOB is quite separate from the scheduler.


SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production

SQL> declare
  2    j int;
  3  begin
  4    dbms_job.submit(j,'begin dbms_session.sleep(60); end;',sysdate,'sysdate+1');
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> select job, what from user_jobs;

       JOB WHAT
---------- --------------------------------------------------
       181 begin dbms_session.sleep(60); end;

SQL> select job_name, job_action from user_scheduler_jobs;

no rows selected


Now here’s the same in 19c


SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

SQL> declare
  2    j int;
  3  begin
  4    dbms_job.submit(j,'begin dbms_session.sleep(60); end;',sysdate,'sysdate+1');
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> select job, what from user_jobs;

       JOB WHAT
---------- --------------------------------------------------
        22 begin dbms_session.sleep(60); end;

SQL> select job_name, job_action from user_scheduler_jobs;

JOB_NAME             JOB_ACTION
-------------------- ------------------------------------------------------------
DBMS_JOB$_22         begin dbms_session.sleep(60); end;

SQL> select * from user_scheduler_jobs
  2  @pr
==============================
JOB_NAME                      : DBMS_JOB$_22
JOB_SUBNAME                   :
JOB_STYLE                     : REGULAR
JOB_CREATOR                   : MCDONAC
CLIENT_ID                     :
GLOBAL_UID                    :
PROGRAM_OWNER                 :
PROGRAM_NAME                  :
JOB_TYPE                      : PLSQL_BLOCK
JOB_ACTION                    : begin dbms_session.sleep(60); end;
NUMBER_OF_ARGUMENTS           : 0
SCHEDULE_OWNER                :
SCHEDULE_NAME                 :
SCHEDULE_TYPE                 : PLSQL
START_DATE                    : 26-MAY-19 07.12.47.000000 PM +08:00
REPEAT_INTERVAL               : sysdate+1
EVENT_QUEUE_OWNER             :
EVENT_QUEUE_NAME              :
EVENT_QUEUE_AGENT             :
EVENT_CONDITION               :
EVENT_RULE                    :
FILE_WATCHER_OWNER            :
FILE_WATCHER_NAME             :
END_DATE                      :
JOB_CLASS                     : DEFAULT_JOB_CLASS
ENABLED                       : TRUE
AUTO_DROP                     : TRUE
RESTART_ON_RECOVERY           : FALSE
RESTART_ON_FAILURE            : FALSE
STATE                         : SCHEDULED
JOB_PRIORITY                  : 3
RUN_COUNT                     : 0
UPTIME_RUN_COUNT              :
MAX_RUNS                      :
FAILURE_COUNT                 : 0
UPTIME_FAILURE_COUNT          :
MAX_FAILURES                  :
RETRY_COUNT                   : 0
LAST_START_DATE               :
LAST_RUN_DURATION             :
NEXT_RUN_DATE                 : 27-MAY-19 07.12.47.000000 PM -07:00
SCHEDULE_LIMIT                :
MAX_RUN_DURATION              :
LOGGING_LEVEL                 : OFF
STORE_OUTPUT                  : TRUE
STOP_ON_WINDOW_CLOSE          : FALSE
INSTANCE_STICKINESS           : TRUE
RAISE_EVENTS                  :
SYSTEM                        : FALSE
JOB_WEIGHT                    : 1
NLS_ENV                       : NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA'...
SOURCE                        :
NUMBER_OF_DESTINATIONS        : 1
DESTINATION_OWNER             :
DESTINATION                   :
CREDENTIAL_OWNER              :
CREDENTIAL_NAME               :
INSTANCE_ID                   :
DEFERRED_DROP                 : FALSE
ALLOW_RUNS_IN_RESTRICTED_MODE : FALSE
COMMENTS                      :
FLAGS                         : 9007199254872624
RESTARTABLE                   : FALSE
HAS_CONSTRAINTS               : FALSE
CONNECT_CREDENTIAL_OWNER      :
CONNECT_CREDENTIAL_NAME       :
FAIL_ON_SCRIPT_ERROR          : FALSE

PL/SQL procedure successfully completed.

You can see that it will be enabled by default and is classed as a regular job. Even if you submit a one-off job, it will still be classed as regular not lightweight.


SQL> declare
  2    j int;
  3  begin
  4    dbms_job.submit(j,'begin dbms_session.sleep(60); end;',sysdate);
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> select job, what from user_jobs;

       JOB WHAT
---------- --------------------------------------------------
        25 begin dbms_session.sleep(60); end;

SQL> select * from user_scheduler_jobs
  2  @pr
==============================
JOB_NAME                      : DBMS_JOB$_25
JOB_SUBNAME                   :
JOB_STYLE                     : REGULAR
JOB_CREATOR                   : MCDONAC
CLIENT_ID                     :
GLOBAL_UID                    :
PROGRAM_OWNER                 :
PROGRAM_NAME                  :
JOB_TYPE                      : PLSQL_BLOCK
JOB_ACTION                    : begin dbms_session.sleep(60); end;
NUMBER_OF_ARGUMENTS           : 0
SCHEDULE_OWNER                :
SCHEDULE_NAME                 :
SCHEDULE_TYPE                 : ONCE
START_DATE                    : 26-MAY-19 08.37.09.000000 PM +08:00
REPEAT_INTERVAL               :
EVENT_QUEUE_OWNER             :
EVENT_QUEUE_NAME              :
EVENT_QUEUE_AGENT             :
EVENT_CONDITION               :
EVENT_RULE                    :
FILE_WATCHER_OWNER            :
FILE_WATCHER_NAME             :
END_DATE                      :
JOB_CLASS                     : DEFAULT_JOB_CLASS
ENABLED                       : TRUE
AUTO_DROP                     : TRUE
RESTART_ON_RECOVERY           : FALSE
RESTART_ON_FAILURE            : FALSE
STATE                         : SCHEDULED
JOB_PRIORITY                  : 3
RUN_COUNT                     : 0
UPTIME_RUN_COUNT              :
MAX_RUNS                      :
FAILURE_COUNT                 : 0
UPTIME_FAILURE_COUNT          :
MAX_FAILURES                  :
RETRY_COUNT                   : 0
LAST_START_DATE               :
LAST_RUN_DURATION             :
NEXT_RUN_DATE                 : 27-MAY-19 11.37.09.268652 AM +08:00
SCHEDULE_LIMIT                :
MAX_RUN_DURATION              :
LOGGING_LEVEL                 : OFF
STORE_OUTPUT                  : TRUE
STOP_ON_WINDOW_CLOSE          : FALSE
INSTANCE_STICKINESS           : TRUE
RAISE_EVENTS                  :
SYSTEM                        : FALSE
JOB_WEIGHT                    : 1
NLS_ENV                       : NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENC
NLS_NUMERIC_CHARACTERS='.,' NLS_CALENDAR='GREGORIAN' NLS_DATE_FORMAT='DD-MON-RR' NLS_DATE_L
NLS_TIME_FORMAT='HH.MI.SSXFF AM' NLS_TIMESTAMP_FORMAT='DD-MON-RR HH.MI.SSXFF AM' NLS_TIME_T
NLS_TIMESTAMP_TZ_FORMAT='DD-MON-RR HH.MI.SSXFF AM TZR' NLS_DUAL_CURRENCY='$' NLS_COMP='BINA
NLS_NCHAR_CONV_EXCP='FALSE'
SOURCE                        :
NUMBER_OF_DESTINATIONS        : 1
DESTINATION_OWNER             :
DESTINATION                   :
CREDENTIAL_OWNER              :
CREDENTIAL_NAME               :
INSTANCE_ID                   :
DEFERRED_DROP                 : FALSE
ALLOW_RUNS_IN_RESTRICTED_MODE : FALSE
COMMENTS                      :
FLAGS                         : 9007199254880304
RESTARTABLE                   : FALSE
HAS_CONSTRAINTS               : FALSE
CONNECT_CREDENTIAL_OWNER      :
CONNECT_CREDENTIAL_NAME       :
FAIL_ON_SCRIPT_ERROR          : FALSE

PL/SQL procedure successfully completed.

Important Note: There is one critical thing you need to be aware of with this change. DBMS_JOB is an “old-school” public API, hence anyone and everyone pretty much had access to it. Anyone familiar with DBMS_SCHEDULER will know that the components within it are true database objects, which can be protected with privileges. So when you upgrade to 19c, to ensure that you do not get nasty surprises, users that are using DBMS_JOB will need the CREATE JOB privilege otherwise their previous ability to submit jobs will disappear. For example:


SQL> conn scott/tiger@db192_pdb1
Connected.

SQL> declare
  2    j int;
  3  begin
  4    dbms_job.submit(j,'begin dbms_session.sleep(60); end;',sysdate);
  5  end;
  6  /
declare
*
ERROR at line 1:
ORA-27486: insufficient privileges
ORA-06512: at "SYS.DBMS_ISCHED", line 9387
ORA-06512: at "SYS.DBMS_ISCHED", line 9376
ORA-06512: at "SYS.DBMS_ISCHED", line 175
ORA-06512: at "SYS.DBMS_ISCHED", line 9302
ORA-06512: at "SYS.DBMS_IJOB", line 196
ORA-06512: at "SYS.DBMS_JOB", line 168
ORA-06512: at line 4

2) What about the cherished transactional nature of DBMS_JOB?

If the old style jobs are now scheduler jobs, do we lose the transactional element of DBMS_JOB? Nope. Even though we will create a paired scheduler entry, DBMS_JOB is still transactional (which I love!).


SQL> declare
  2    j int;
  3  begin
  4    dbms_job.submit(j,'begin dbms_session.sleep(60); end;',sysdate,'sysdate+1');
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> select job, what from user_jobs;

       JOB WHAT
---------- --------------------------------------------------
        21 begin dbms_session.sleep(60); end;

SQL> select job_name, job_action from user_scheduler_jobs;

JOB_NAME             JOB_ACTION
-------------------- ------------------------------------------------------------
DBMS_JOB$_21         begin dbms_session.sleep(60); end;

SQL> roll;
Rollback complete.
SQL> select job, what from user_jobs;

no rows selected

SQL> select job_name, job_action from user_scheduler_jobs;

no rows selected

SQL>

If you love that transactional capability too, then make sure to visit the database ideas page to vote up this idea. I have no issue with DBMS_SCHEDULER doing commits by default, but it would be cool if (say) for lightweight jobs we had an option to choose whether we commit or not.

Re-partitioning 2

Last week I wrote a note about turning a range-partitioned table into a range/list composite partitioned table using features included in 12.2 of Oracle. But my example was really just an outline of the method and bypassed a number of the little extra problems you’re likely to see in a real-world system, so in this note I’m going to bring in an issue that you might run into – and which I’ve seen appearing a number of times: ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION.

It’s often the case that a system has a partitioned table that’s been around for a long time, and over its lifetime it may have had (real or virtual) columns added, made inivisble, dropped, or mark unused. As a result you may find that the apparent definition of the table is not the same as the real definition of the table – and that’s why Oracle has given us (in 12c) the option to “create table for exchange”.

You might like to read a MoS note giving you one example of a problem with creating an exchange table prior to this new feature. ORA-14097 At Exchange Partition After Adding Column With Default Value (Doc ID 1334763.1) I’ve created a little model by cloning the code from that note.


rem
rem     Script:         pt_exchange_problem.sql
rem     Author:         Jonathan Lewis
rem     Dated:          May 2019
rem

create table mtab (pcol number)
partition by list (pcol) (
        partition p1 values (1),
        partition p2 values (2)
);

alter table mtab add col2 number default 0 not null;

prompt  ========================================
prompt  Traditional creation method => ORA-14097
prompt  ========================================

create table mtab_p2 as select * from mtab where 1=0;
alter table mtab exchange partition P2 with table mtab_p2;

prompt  ===================
prompt  Create for exchange
prompt  ===================

drop table mtab_p2 purge;
create table mtab_p2 for exchange with table mtab;
alter table mtab exchange partition P2 with table mtab_p2;

[/sourcecode}


Here's the output from running this on an instance of 18.3


Table created.

Table altered.

========================================
Traditional creation method => ORA-14097
========================================

Table created.

alter table mtab exchange partition P2 with table mtab_p2
*
ERROR at line 1:
ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION

===================
Create for exchange
===================

Table dropped.


Table created.


Table altered.

So we don’t have to worry about problems creating an exchange table in Oracle 12c or later. But we do still have a problem if we’re trying to convert our range-partitioned table into a range/list composite partitioned table by doing using the “double-exchange” method. In my simple example I used a “create table” statement to create an empty table that we could exchange into; but without another special version of a “create table” command I won’t be able to create a composite partitioned table that is compatible with the simple table that I want to use as my intermediate table.

Here’s the solution to that problem – first in a thumbnail sketch:

  • create a table for exchange (call it table C)
  • alter table C modify to change it to a composite partitioned table with one subpartition per partition
  • create a table for exchange (call it table E)
  • Use table E to exchange partitions from the original table to the (now-partitioned) table C
  • Split each partition of table C into the specific subpartitions required

And now some code to work through the details – first the code to create and populate the partitioned table.


rem
rem     Script:         pt_comp_from_pt_2.sql
rem     Author:         Jonathan Lewis
rem     Dated:          May 2019
rem

drop table t purge;
drop table pt_range purge;
drop table pt_range_list purge;

-- @@setup

create table pt_range (
        id              number(8,0)     not null,
        grp             varchar2(1)     not null,
        small_vc        varchar2(10),
        padding         varchar2(100)
)
partition by range(id) (
        partition p200 values less than (200),
        partition p400 values less than (400),
        partition p600 values less than (600)
)
;

insert into pt_range
select
        rownum-1,
        mod(rownum,2),
        lpad(rownum,10,'0'),
        rpad('x',100,'x')
from
        all_objects
where
	rownum <= 600 -- > comment to avoid WordPress format issue
;

commit;

Then some code to create the beginnings of the target composite partitioned table. We create a simple heap table “for exchange”, then modify it to be a composite partitioned table with a named starting partition and high_value and a template defining a single subpartition then, as a variant on the example from last week, specifying interval partitioning.


prompt	==========================================
prompt	First nice feature - "create for exchange"
prompt	==========================================

create table pt_range_list for exchange with table pt_range;

prompt	============================================
prompt	Now alter the table to composite partitioned
prompt	============================================

alter table pt_range_list modify
partition by range(id) interval (200)
subpartition by list (grp) 
subpartition template (
        subpartition p_def      values(default)
)
(
	partition p200 values less than (200)
)
;

If you want to do the conversion from range partitioning to interval partitioning you will have to check very carefully that your original table will be able to convert safely – which means you’ll need to check that the “high_value” values for the partitions are properly spaced to match the interval you’ve defined and (as a special requirement for the conversion) there are no omissions from the current list of high values. If your original table doesn’t match these requirement exactly you may end up trying to exchange data into a partition where it doesn’t belong; for example, if my original table had partitions with high value of 200, 600, 800 then there may be values in the 200-399 range currently stored in the original “600” range partition which shouldn’t go into the new “600” interval partition. You may find you have to split (and/or merge) a few partitions in your range-partitioned table before you can do the main conversion.

Now we create create the table that we’ll actually use for the exchange and go through each exchange in turn. Because I’ve got an explicitly named starting partition the first exchange takes only two steps – exchange out, exchange in. But because I’m using interval partitioning in the composite partitioned table I’m doing a “lock partition” before the second exchange on all the other partitions as this will bring the required target partition into existence. I’m also using the “[sub]partition for()” syntax to identify the pairs of [sub]partitions – this isn’t necessary for the original range-partitioned table, of course, but it’s the only way I can identify the generated subpartitions that will appear in the composite partitioned table.


create table t for exchange with table pt_range;

prompt	=======================================================================
prompt	Double exchange to move a partition to become a composite subpartition
prompt	Could drive this programatically by picking one row from each partition
prompt	=======================================================================

alter table pt_range exchange partition p200 with table t;
alter table pt_range_list exchange subpartition p200_p_def with table t;

alter table pt_range exchange partition for (399) with table t;
lock  table pt_range_list partition for (399) in exclusive mode;
alter table pt_range_list exchange subpartition for (399,'0') with table t;

alter table pt_range exchange partition for (599) with table t;
lock  table pt_range_list partition for (599) in exclusive mode;
alter table pt_range_list exchange subpartition for (599,'0') with table t;

prompt	=====================================
prompt	Show that we've got the data in place
prompt	=====================================

execute dbms_stats.gather_table_stats(user,'pt_range_list',granularity=>'ALL')

break on partition_name skip 1

select  partition_name, subpartition_name, num_rows 
from    user_tab_subpartitions 
where   table_name = 'PT_RANGE_LIST'
order by
        partition_name, subpartition_name
;

Now that the data is in the target table we can split each default subpartition into the four subpartitions that we want for each partition. To cater for the future, though, I’ve first modified the subpartition template so that each new partition will have four subpartitions (though the naming convention won’t be applied, of course, Oracle will generate system name for all new partitions and subpartitions).


prompt  ================================================
prompt  Change the subpartition template to what we want
prompt  ================================================

alter table pt_range_list
set subpartition template(
        subpartition p_0 values (0),
        subpartition p_1 values (1),
        subpartition p_2 values (2),
        subpartition p_def values (default)
)
;

prompt  ====================================================
prompt  Second nice feature - multiple splits in one command
prompt  Again, first split is fixed name.
prompt  We could do this online after allowing the users in
prompt  ====================================================

alter table pt_range_list split subpartition p200_p_def
        into (
                subpartition p200_p_0 values(0),
                subpartition p200_p_1 values(1),
                subpartition p200_p_2 values(2),
                subpartition p200_p_def
        )
;

alter table pt_range_list split subpartition for (399,'0')
        into (
                subpartition p400_p_0 values(0),
                subpartition p400_p_1 values(1),
                subpartition p400_p_2 values(2),
                subpartition p400_p_def
        )
;

alter table pt_range_list split subpartition for (599,'0')
        into (
                subpartition p600_p_0 values(0),
                subpartition p600_p_1 values(1),
                subpartition p600_p_2 values(2),
                subpartition p600_p_def
        )
;

Finally a little demonstration that we can’t add an explicitly named partition to the interval partitioned table; then we insert a row to generate the partition and show that it has 4 subpartitions.

Finishing off we rename everything (though that’s a fairly pointless exercise).


prompt  ==============================================================
prompt  Could try adding a partition to show it uses the new template
prompt  But that's not allowed for interval partitions: "ORA-14760:"
prompt  ADD PARTITION is not permitted on Interval partitioned objects
prompt  So insert a value that would go into the next (800) partition
prompt  ==============================================================

alter table pt_range_list add partition p800 values less than (800);

insert into pt_range_list (
        id, grp, small_vc, padding
)
values ( 
        799, '0', lpad(799,10,'0'), rpad('x',100,'x')
)
;

commit;

prompt  ===================================================
prompt  Template naming is not used for the subpartitions,
prompt  so we have to use the "subpartition for()" strategy 
prompt  ===================================================

alter table pt_range_list rename subpartition for (799,'0') to p800_p_0;
alter table pt_range_list rename subpartition for (799,'1') to p800_p_1;
alter table pt_range_list rename subpartition for (799,'2') to p800_p_2;
alter table pt_range_list rename subpartition for (799,'3') to p800_p_def;

prompt  ==============================================
prompt  Might as well clean up the partition names too
prompt  ==============================================

alter table pt_range_list rename partition for (399) to p400;
alter table pt_range_list rename partition for (599) to p600;
alter table pt_range_list rename partition for (799) to p800;

prompt  =======================================
prompt  Finish off by listing the subpartitions 
prompt  =======================================

execute dbms_stats.gather_table_stats(user,'pt_range_list',granularity=>'ALL')

select  partition_name, subpartition_name, num_rows 
from    user_tab_subpartitions 
where   table_name = 'PT_RANGE_LIST'
order by
        partition_name, subpartition_name
;

It’s worth pointing out that you could do the exchanges (and the splitting and renaming at the same time) through some sort of simple PL/SQL loop – looping through the named partitions in the original table and using a row from the first exchange to drive the lock and second exchange (and splitting and renaming). For exanple something like the following which doesn’t have any of the error-trapping and defensive mechanisms you’d want to use on a production system:



declare
        m_pt_val number;
begin
        for r in (select partition_name from user_tab_partitions where table_name = 'PT_RANGE' order by partition_position) 
        loop
                execute immediate
                        'alter table pt_range exchange partition ' || r.partition_name ||
                        ' with table t';
        
                select id into m_pt_val from t where rownum = 1;
        
                execute immediate 
                        'lock table pt_range_list partition for (' || m_pt_val || ') in exclusive mode';
        
                execute immediate
                        'alter table pt_range_list exchange subpartition  for (' || m_pt_val || ',0)' ||
                        ' with table t';
        
        end loop;
end;
/

If you do go for a programmed loop you have to be really careful to consider what could go wrong at each step of the loop and how your program is going to report (and possibly attempt to recover) the situation. This is definitely a case where you don’t want code with “when others then null” appearing anywhere, and don’t be tempted to include code to truncate the exchange table.

 

PostgreSQL: measuring query activity(WAL size generated, shared buffer reads, filesystem reads,…)

PostgreSQL: measuring query activity (WAL size generated, shared buffer reads, filesystem reads,…)

When I want to know if my application scales, I need to understand the work done by my queries. No need to run a huge amount of data from many concurrent threads. If I can get the relevant statistics behind a single unit-test, then I can infer how it will scale. For example, reading millions of pages to fetch a few rows will cause shared buffer contention. Or generating dozens of megabytes of WAL for a small update will wait on disk, and penalize the backup RTO, or the replication gap.

I’ll show some examples. From pgsql I’ll collect the statistics (which are cumulative from the start if the instance) before:

select *,pg_current_wal_lsn() from pg_stat_database where datname=current_database() \gset

and calculate the difference to show the delta:

select blks_hit-:blks_hit"blk hit",blks_read-:blks_read"blk read",tup_inserted-:tup_inserted"ins",tup_updated-:tup_updated"upd",tup_deleted-:tup_deleted"del",tup_returned-:tup_returned"tup ret",tup_fetched-:tup_fetched"tup fch",xact_commit-:xact_commit"commit",xact_rollback-:xact_rollback"rbk",pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(),:'pg_current_wal_lsn')) "WAL",pg_size_pretty(temp_bytes-:temp_bytes)"temp" from pg_stat_database where datname=current_database();

Most of the statistics come from pg_stat_database. The WAL size is calculated from the latest WAL write pointer exposed with pg_current_wal_lsn() and size calculated with pg_wal_lsn_diff(). I use \gset to get them as plsql substitution variables before and used them to get the difference after.

Better with examples, I create a DEMO database and a DEMO table:

demo=# create table DEMO(n int primary key,flag char,text varchar(1000));
CREATE TABLE

Insert 1000 rows:

I’ve run the following insert:

insert into DEMO
select generate_series, 'N',lpad('x',1000,'x') from generate_series(1,10000);
INSERT 0 10000

But I’ve run my queries before and after in order to get the statistics:

I’ve inserted about 10MB of data (10000 rows with a 1000 bytes text and some additional small columns). All those new rows bust be logged by the WAL, which is the reason for the 11MB of redo information. The pages where it was written had to be read from disks in order to update them, which is the reason for 1504 block reads (that’s about 11MB in 8k blocks). Actually, we see 30000 additional block hits in buffer cache and this is the index maintenance. Those 10000 rows were inserted one by one, updating the B*Tree index which may have a height of 3 and that’s 30000 pages touched. Fortunately, they stayed in the shared buffers which is why there’s no block read each time.

This matches with the size of my table:

select relkind,relname,relpages,pg_size_pretty(relpages::bigint*8*1024) from pg_class natural join (select oid relnamespace,nspname from pg_namespace) nsp where nspname='public';

11MB of data, nearly 1500 pages — that’s what I got from the statistics.

Count the rows

Now, I’ll check how many blocks have to be read when counting all rows:

select count(*) from DEMO ;

All table blocks were read, fortunately from the shared buffers. And the reason is in the explain plan: full table scan. Actually, I would expect an Index Only scan for this query because all rows can be counted from the index. But MVCC implementation of PostgreSQL is versioning only the table tuples. Not the index entries, and it has to go to the table, unless a recent vacuum has updated the visibility map.

Vacuum

Let’s vacuum to update the visibility map:

vacuum DEMO;

Many block reads, that’s like a full table scan, with minimal updates here because there’s no dead tuples to remove.

Count from vacuumed table

Now, counting the rows again:

select count(*) from DEMO ;

Finally I have a real Index Only scan, accessing only to 45 buffers to count the 10000 index entries. That’s the most important point with PostgreSQL performance: MVCC allows to mix read and write workloads, thanks to non-blocking reads, but ideally, a vacuum should occur between massive write and massive read use-cases. Again, no need to run a massive pgbench concurrent workload to observe this. Small queries are sufficient as long as we look at the right statistics.

Update

I update the ‘flag’ column to set half of the rows to ‘Y’:

update DEMO set flag='Y' where n%2=0;

This is the operation where PostgreSQL MVCC is less efficient. I changed only one byte per row, but all rows were copied to new blocks. As I’m touching half the rows, they fit in half the blocks. This is the 743 read from disk. And the old version had to be marked as so… finally, nearly 30000 buffer access to update 5000 flags. And the worse is the redo generation. 16MB as the new tuples must be logged, as well as the old versions marked. And PostgreSQL must do full page logging even when few rows/columns are modified. More about this:

Full page logging in Postgres and Oracle - Blog dbi services

In this previous blog I was using strace to get the size of WAL written. Now using the delta offset of WAL pointer is see the same figures.

Delete

Now deleting the non-flagged rows

delete from DEMO where flag='N';

Deleting 5000 rows here, has to scan all blocks to find them (that’s the 10000 tuples returned) which is about 1500 buffers accessed. And for the 5000 found, mark them as deleted, which is 5000 additional buffers accessed.

Why?

This post is there mainly to show the simple query I use to get SQL execution statistics, including WAL writes which are probably the most useful, but unfortunately missing from pg_stat_database. I’m also advocating here for small test cases fully understood rather than general benchmarks difficult to analyze. It is, in my opinion, the best way to understand how it works, both for educational purpose and to guarantee scalable applications.