Search

Top 60 Oracle Blogs

Recent comments

Oakies Blog Aggregator

How Not to Collect Optimizer Statistics in an Application Engine Program

I was told about a PeopleSoft customer experiencing an Oracle error when collecting statistics during an Application Engine.

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 100%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">ORA-06533: Subscript beyond count 
ORA-06512: at "SYS.DBMS_STATS"…

It is possibly a manifestation of a database bug. The workaround was not to use AUTO_SAMPLE_SIZE, so instead, this customer initially coded an explicit call to DBMS_STATS.GATHER_TABLE_STATS with a specific sample size.
This blog is not about the bug, but how to manage the workaround.

DO NOT TRY THIS AT HOME!

I think that there are a number of problems with this approach

  1. Using a fixed sample size rather than AUTO_SAMPLE_SIZE should only be considered as a temporary workaround. The new hash-based number-of-distinct-values (NDV) algorithm in Oracle 12c only works with AUTO_SAMPLE_SIZE, and it produces more accurate statistics and runs faster because saves a large sort operation. Coding a fixed sample size into an Application Engine requires a managed change to be made, tested and released into production, and then when the underlying problem is resolved the customisation needs to be removed by the same managed process. 
  2. DBMS_STATS.GATHER_TABLE_STATS implies a commit. That can lead to problems that PeopleSoft avoids by only calling statistics via the %UpdateStats macro and controlling when that macro does and does not execute. 

Committing and Restart Checkpointing in Application Engine

If a restartable Application Engine program fails it rolls back to the last commit point and it can then be restarted from that point. Committing is managed by Application Engine at section and step levels where the program state record is updated accordingly. If an error occurs in a step after the implicit commit in DBMS_STATS, it can result in the data in the application tables being different to where the state record indicates the program can be restarted. The program may not restart, or it could conceivably execute but produce erroneous results.
Committing inside a do while loop, including any other Application Engine program called from inside the loop is suppressed at Application Engine section/step level and therefore the execution of %UpdateStats macro is also suppressed. Otherwise, you could get rogue ORA-01555 Snapshot Too Old errors. Suppression of %UpdateStats is reported in the Application Engine step trace.

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 100%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">-- 19:07:37.199 .(AEMINITEST.MAIN.LOOP) (Do Select)
%Select(recname) SELECT recname FROM psrecdefn WHERE recname = 'JOB'
/
-- Buffers:
-- 1) JOB
-- 19:07:37.204 Iteration 1 of .(AEMINITEST.MAIN.LOOP) (Do Select) loop

-- 19:07:37.208 .(AEMINITEST.MAIN.LOOP) (SQL)
RECSTATS PS_JOB LOW
/
-- 19:07:37.211 UpdateStats ignored - COMMIT required

-- 19:07:37.212 .(AEMINITEST.MAIN.LOOP) (Do Fetch)

Even a previously uncommitted SQL step can lead to %UpdateStats being suppressed.

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 100%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">-- 19:07:35.205 .(AEMINITEST.MAIN.Step01) (SQL)
UPDATE PS_AETEMPTBLMGR SET AE_DEDICATED = 1 WHERE 0 = 1
/
-- Row(s) affected: 0

-- 19:07:35.213 .(AEMINITEST.MAIN.Step02) (PeopleCode)

-- 19:07:35.220 .(AEMINITEST.MAIN.US1) (SQL)
RECSTATS PS_JOB LOW
/
-- 19:07:35.227 UpdateStats ignored - COMMIT required

If you code DBMS_STATS.GATHER_TABLE_STATS explicitly, Application Engine will not recognise the step as having committed. In the following example, you can see the %UpdateStats on the last step has been suppressed because it Application Engine does not recognise that the update in the first step has been committed by the call to DBMS_STATS.

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 100%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">-- 19:12:06.690 .(AEMINITEST.MAIN.Step01) (SQL)
UPDATE PS_AETEMPTBLMGR SET AE_DEDICATED = 1 WHERE 0 = 1
/
-- Row(s) affected: 0
-- 19:12:06.696 .(AEMINITEST.MAIN.Step02) (PeopleCode)
-- 19:12:06.700 .(AEMINITEST.MAIN.GTS) (SQL)
%Execute(/) BEGIN
dbms_stats.gather_table_stats('SYSADM','PS_JOB',estimate_percent=>1); END; /
/
-- Row(s) affected: 1

-- 19:12:09.871 .(AEMINITEST.MAIN.US1) (SQL)
RECSTATS PS_JOB LOW
/
-- 19:12:09.877 UpdateStats ignored - COMMIT required
/

Perhaps, the safest form of this workaround would be to have the step with DBMS_STATS and the immediately preceding step explicitly commit as in the following example.  I have also made the program restartable.  Now restart data is checkpointed, and the %UpdateStats macro executes at step US1.

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 100%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">Restart Data CheckPointed
/
COMMIT
/

-- 19:20:24.792 .(AEMINITEST.MAIN.GTS) (SQL)
%Execute(/) BEGIN
dbms_stats.gather_table_stats('SYSADM','PS_JOB',estimate_percent=>1); END; /
/

-- Row(s) affected: 1
/
/
Restart Data CheckPointed
/
COMMIT
/

-- 19:20:29.153 .(AEMINITEST.MAIN.US1) (SQL)
RECSTATS PS_JOB LOW
/
-- Row(s) affected: 1
/

However, you have to consider the state the application data after an error, whether you wish to restart or cancel the Application Engine because you can no longer rollback.

Doing the Right Thing

I recommend that:
  • You should only ever collect stats in Application Engine with the %UpdateStats macro that in turn executes the command in the DDL model.
  • From Oracle 11g both PeopleSoft statistics gathering DDL models should be the same and should ultimately call DBMS_STATS without any parameters other than the table name. The default value of ESTIMATE_PERCENT is AUTO_SAMPLE_SIZE.

There are two DDL models in PeopleSoft because %UpdateStats can be invoked with a second parameter to collect the statistics HIGH or LOW. This dates back to Oracle's ANALYZE command that could either compute or estimate statistics (and other database platforms had similar options). Collecting optimizer statistics with ANALYZE has been deprecated for many years, but the command still has other valid uses.  It was superceded by DBMS_STATS in Oracle 8i (released in 1998).
Automatic sample size was introduced in Oracle 9i.  In Oracle 9i and 10g, it was usual to use automatic sample size in the high statistics gathering model and a small fixed sample size in the low model for use on very large tables.  The LOW parameter was specified on %Updatestats in Application Engine programs as necessary.
This approach became redundant from Oracle 11g with the introduction of table preferences. If you need to collect statistics with a specific rather than the automatic sample size or specify any other parameters, then a table preference should be created. Preferences apply wherever statistics are gathered on that table and not overridden in the call of DBMS_STATS., including schema and database-wide operations such as the maintenance window.  If there are multiple places where statistics are collected on a table, a preference assures that the statistics will always be collected will be consistently.
From Oracle 12c, as the new NDV algorithm only works with AUTO_SAMPLE_SIZE, you should always use the default unless you have an overarching reason to the contrary, and then you should use a table preference. This approach does not require any application code change because the preference is an attribute of a table in the database.
I recommend using GFCPSSTATS package, calling it from the DDL model (see previous blog Managing Cost-Based Optimizer Statistics for PeopleSoft). The package also includes a mechanism to specify table preferences in metadata, so that they are automatically instantiated when a table is created or altered by Application Designer, and are applied to every instance of a temporary record.

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 100%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">gfcpsstats11.ps_stats(p_ownname=>[DBNAME], p_tabname=>[TBNAME]);

Alternatively, just call DBMS_STATS with no additional parameters (other than FORCE, in case you lock statistics on temporary tables) but then you must manage table preferences manually.

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 100%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">DBMS_STATS.GATHER_TABLE_STATS(ownname=>[DBNAME], tabname=>[TBNAME], force=>TRUE);

Further Reading

Subquery Order

From time to time I’ve wanted to optimize a query by forcing Oracle to execute existence (or non-existence) subqueries in the correct order because I know which subquery will eliminate most data most efficiently, and it’s always a good idea to look for ways to eliminate early. I’ve only just discovered (which doing some tests on 18c) that Oracle 12.2.0.1 introduced the /*+ order_subq() */ hint that seems to be engineered to do exactly that.

Here’s a very simple (and completely artificial) demonstration of use.


rem
rem     Script:         122_order_subq.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Sep 2018
rem

create table t1 as select * from all_objects;
create table t2 as select * from all_objects;
create table t3 as select * from all_objects;

create index t2_i1 on t2(object_id);
create index t3_i1 on t3(object_id);

prompt  =============================
prompt  order_subq(@main subq2 subq3)
prompt  =============================

explain plan for
select
        /*+
                qb_name(main)
                no_unnest(@subq2)
                no_unnest(@subq3)
                order_subq(@main subq2 subq3)
        */
        t1.object_name, t1.object_type
from
        t1
where
        exists (
                select
                        /*+ qb_name(subq2) */
                        null
                from    t2
                where   t2.object_id = t1.object_id * 5
        )
and     exists (
                select
                        /*+ qb_name(subq3) */
                        null
                from    t3
                where   t3.object_id = t1.object_id * 13
        )
;

select * from table(dbms_xplan.display(null,null,'outline'));

=============================
order_subq(@main subq2 subq3)
=============================

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2585036931

----------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |     1 |    53 | 51090   (1)| 00:00:02 |
|*  1 |  FILTER            |       |       |       |            |          |
|   2 |   TABLE ACCESS FULL| T1    | 61765 |  3196K|   163   (4)| 00:00:01 |
|*  3 |   INDEX RANGE SCAN | T2_I1 |     1 |     5 |     1   (0)| 00:00:01 |
|*  4 |   INDEX RANGE SCAN | T3_I1 |     1 |     5 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------

Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
...
      ORDER_SUBQ(@"MAIN" "SUBQ2" "SUBQ3")
...
  */

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter( EXISTS (SELECT /*+ NO_UNNEST QB_NAME ("SUBQ2") */ 0 FROM
              "T2" "T2" WHERE "T2"."OBJECT_ID"=:B1*5) AND  EXISTS (SELECT /*+
              NO_UNNEST QB_NAME ("SUBQ3") */ 0 FROM "T3" "T3" WHERE
              "T3"."OBJECT_ID"=:B2*13))
   3 - access("T2"."OBJECT_ID"=:B1*5)
   4 - access("T3"."OBJECT_ID"=:B1*13)

I’ve blocked subquery unnesting for the purposes of the demo and given a query block name to the two subqueries (using a name that identifies the associated table). As you can see, the execution plan uses the subqueries as filter subqueries, operating them in the order I’ve specified in my hint. You can also see that the hint is echoed down into the Outline section of the plan.

It’s possible that this is the plan that the optimizer would have chosen without the order_subq hint, so I ought to see if I can also use the hint to make the subqueries filter in the oppostie order. I happen to know that executing the subquery against t3 is likely to eliminate more rows that executing the subquery against t2. (The “* 13” compared to the “* 5” is significant) so I really want the subqueries to be used in the opposite order anyway – so here’s what happens when I reverse the order in the hint:


prompt  =============================
prompt  order_subq(@main subq3 subq2)
prompt  =============================

explain plan for
select
        /*+
                qb_name(main)
                no_unnest(@subq2)
                no_unnest(@subq3)
                order_subq(@main subq3 subq2)
        */
        t1.object_name, t1.object_type
from
        t1
where
        exists (
                select
                        /*+ qb_name(subq2) */
                        null
                from    t2
                where   t2.object_id = t1.object_id * 5
        )
and     exists (
                select
                        /*+ qb_name(subq3) */
                        null
                from    t3
                where   t3.object_id = t1.object_id * 13
        )
;

select * from table(dbms_xplan.display(null,null,'outline'));

=============================
order_subq(@main subq2 subq3)
=============================

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3585049451

----------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |     1 |    53 | 51090   (1)| 00:00:02 |
|*  1 |  FILTER            |       |       |       |            |          |
|   2 |   TABLE ACCESS FULL| T1    | 61765 |  3196K|   163   (4)| 00:00:01 |
|*  3 |   INDEX RANGE SCAN | T3_I1 |     1 |     5 |     1   (0)| 00:00:01 |
|*  4 |   INDEX RANGE SCAN | T2_I1 |     1 |     5 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------

Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
...
      ORDER_SUBQ(@"MAIN" "SUBQ3" "SUBQ2")
...
  */

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter( EXISTS (SELECT /*+ NO_UNNEST QB_NAME ("SUBQ3") */ 0 FROM
              "T3" "T3" WHERE "T3"."OBJECT_ID"=:B1*13) AND  EXISTS (SELECT /*+
              NO_UNNEST QB_NAME ("SUBQ2") */ 0 FROM "T2" "T2" WHERE
              "T2"."OBJECT_ID"=:B2*5))
   3 - access("T3"."OBJECT_ID"=:B1*13)
   4 - access("T2"."OBJECT_ID"=:B1*5)

With the modified hint in place the order of the filter subqueries is reversed. Notice how the Predicate section also echoes the ordering of the subqueries.

Footnote

It should be noted that the order_subq() hint doesn’t get mentioned in the 18c SQL Language Reference “Alphabetical List of Hints”. If it were then one of the little oddities that might get a mention is that the optimizer seems to ignore the hint if you disable CPU costing. (not that anyone should be doing that since 10g).

Complex materialized views? Try a table first

Just a quick post today that arose from an AskTOM question a little while back. Over the years and across the evolution of various versions of the Oracle database, the amount of “power” you could pack into a CREATE TABLE statement as grown. For example, I can do a CREATE-TABLE-AS-SELECT, along with a partitioning clause, including an explicit CREATE INDEX command and add constraints all in one atomic operation.



SQL> create table t
  2    (id1,
  3     id2,
  4     owner,
  5     object_name,
  6     constraint t_pk primary key ( id1) using index
  7        ( create index t_pk on t ( id1, owner) )
  8    )
  9  partition by range ( id2 )
 10  (
 11    partition p1 values less than ( 100000 ),
 12    partition p2 values less than ( 200000 )
 13  )
 14  as
 15    select
 16      object_id id1,
 17      object_id id2,
 18      owner,
 19      object_name
 20  from dba_objects
 21  where object_id is not null;

Table created.

That’s pretty cool, but one of the most common times you will be writing DDL that includes a “select * from” as part of the DDL definition, is during the creation of materialized views, because the view text naturally will contain a select statement. Unfortunately, we are not quite as “generous” when it comes to accepting all of the various physical implementation options when it comes to DDL for materialized views. Trying to create a materialized view that matches our table T above is a struggle.


SQL> create materialized view t_mv
  2    (id1,
  3     id2,
  4     owner,
  5     object_name,
  6     constraint t_mv_pk primary key ( id1) using index
  7        ( create index t_mv_pk on t_mv ( id1, owner) )
  8    )
  9  partition by range ( id2 )
 10  (
 11    partition p1 values less than ( 100000 ),
 12    partition p2 values less than ( 200000 )
 13  )
 14  refresh complete on demand
 15  as
 16    select
 17      id1,
 18      id2,
 19      owner,
 20      object_name
 21  from t;
   constraint t_mv_pk primary key ( id1) using index
              *
ERROR at line 6:
ORA-00907: missing right parenthesis


SQL>
SQL> create materialized view t_mv
  2    (id1,
  3     id2,
  4     owner,
  5     object_name,
  6     constraint t_mv_pk primary key ( id1) using index
  7        ( create index t_mv_pk on t_mv ( id1, owner) )
  8    )
  9  refresh complete on demand
 10  partition by range ( id2 )
 11  (
 12    partition p1 values less than ( 100000 ),
 13    partition p2 values less than ( 200000 )
 14  )
 15  as
 16    select
 17      id1,
 18      id2,
 19      owner,
 20      object_name
 21  from t;
   constraint t_mv_pk primary key ( id1) using index
              *
ERROR at line 6:
ORA-00907: missing right parenthesis

When you encounter this limitation, don’t forget that one of the nice things you can do with materialized view creation is pre-create the underlying table with all of its flexibility:



SQL> create table t_mv
  2    (id1,
  3     id2,
  4     owner,
  5     object_name,
  6     constraint t_mv_pk primary key ( id1) using index
  7        ( create index t_mv_pk on t_mv ( id1, owner) )
  8    )
  9  partition by range ( id2 )
 10  (
 11    partition p1 values less than ( 100000 ),
 12    partition p2 values less than ( 200000 )
 13  )
 14  as
 15    select
 16      id1,
 17      id2,
 18      owner,
 19      object_name
 20  from t;

Table created.

And then once that it done, you can use the PREBUILT TABLE clause to create your materialized view which will now satisfy all of the underlying physical structure elements you desired.



SQL> create materialized view t_mv
  2  on prebuilt table
  3  refresh complete on demand
  4  as select
  5      id1,
  6      id2,
  7      owner,
  8      object_name
  9  from t;

Materialized view created.

SQL>
SQL>

ASM Filter Driver — simple test on filtering

Here is a simple test on ASM Filter Driver showing that when filtering is enabled the disks presented to ASM are protected from external writes.

On a 12.2 Grid Infrastructure installation I have my disks labeled with ASM Filter Driver (AFD):

ASMCMD> afd_state
ASMCMD-9526: The AFD state is 'LOADED' and filtering is 'ENABLED' on host 'VM106'
ASMCMD> afd_lsdsk
----------------------------------------------
Label Filtering Path
==============================================
VDI100M0001 ENABLED /dev/sdh
VDI100M0002 ENABLED /dev/sdg
VDI100M0003 ENABLED /dev/sdi
VDI100M0004 ENABLED /dev/sdj
VDI1G0001 ENABLED /dev/sde
VDI1G0002 ENABLED /dev/sdf
VDI1G0003 ENABLED /dev/sdd
VDI1G0004 ENABLED /dev/sdc
VDI1G0005 ENABLED /dev/sdk
VDI1G0006 ENABLED /dev/sdl
...

lsblk is a good way to see all block devices, those mounted as a filesystem, but also those labeled for ASM:

lsblk --paths -o NAME,KNAME,FSTYPE,LABEL,MOUNTPOINT,SIZE,OWNER,GROUP,MODE,ALIGNMENT,MIN-IO,OPT-IO,PHY-SEC,LOG-SEC,ROTA,SCHED,RQ-SIZE,WSAME

I have created a diskgroup, named FRANCK, with only one disk: /dev/sde labeled as VDI1G0001

ASMCMD> lsdsk -G FRANCK
Path
AFD:VDI1G0001

Then I’ve created a tablespace with its datafile on this diskgroup, and a table in this tablespace, inserting ‘Hello Franck!’ which is a string easy to recognize with no need to check the extents block offset:

create tablespace FRANCK datafile '+FRANCK';
create table FRANCK (x varchar2(30)) tablespace FRANCK;
insert into FRANCK values ('Hello Franck!');
commit;
alter system checkpoint;

Nothing is encrypted, the disk is small (1GB) and then I can simply use grep to find my character string:

[root@VM106 ~]# grep --byte-offset --only-matching --text "Hello Franck" /dev/sde
97574895:Hello Franck

I displayed the offset, and can get more detail about the binary data stored around here with od:

[root@VM106 ~]# od -t c --skip-bytes=$(( 97574895 - 10 )) --read-bytes=40 --width=80  /dev/sde
564157745  \0  \0  \0  \0  \0  \0   , 001 001  \r   H   e   l   l   o       F   r   a   n   c   k   ! 001 006   ) 334 006 242  \0  \0 207  \0 200  \a   ( 334   $  \0  \0

As I have the offset, I can easily corrupt the file with dd, writing 5 chr(0) in the middle:

[root@VM106 ~]# dd of=/dev/sde if=/dev/zero seek=$(( 97574895 + 5 )) bs=1 count=5 conv=notrunc
5+0 records in
5+0 records out
5 bytes (5 B) copied, 0.000164038 s, 30.5 kB/s

No errors here. The five write() calls were successful. However we can see the following in /var/log/messages

[root@VM106]# Aug 31 23:19:43 VM106 kernel: Buffer I/O error on device sde, logical block 23821
Aug 31 23:19:43 VM106 kernel: lost page write due to I/O error on sde
Aug 31 23:19:43 VM106 kernel: sd 4:0:2:0: [sde] Incomplete mode parameter data
Aug 31 23:19:43 VM106 kernel: sd 4:0:2:0: [sde] Assuming drive cache: write through
Aug 31 23:19:43 VM106 kernel: sde: unknown partition table

This is the kernel informing us that the writes were lost. This is where AFD has filtered the writes() which were not recognized as what Oracle processes are expected to do. The 23821 logical block is where my offset is: 97574895/4096=23821.99…

If I check back my file, nothing has changed:

[root@VM106 ~]# od -t c --skip-bytes=$(( 97574895 - 10 )) --read-bytes=40 --width=80  /dev/sde
564157745  \0  \0  \0  \0  \0  \0   , 001 001  \r   H   e   l   l   o       F   r   a   n   c   k   ! 001 006   ) 334 006 242  \0  \0 207  \0 200  \a   ( 334   $  \0  \0
564160015

Here I tested with 5 small writes. I got only one set of messages on /var/log/messages. I get the same if I try to create a partition, or a filesystem, on this disk device. This is the goal of ASM Filter Driver: be sure that nobody thinks that the device is not used because they don’t know about ASM, and tries to format it.

Now I disable the filtering to show the normal behaviour:

ASMCMD> afd_filter -d --all
ASMCMD> afd_state
ASMCMD-9526: The AFD state is 'LOADED' and filtering is 'DISABLED' on host 'VM106'
ASMCMD> afd_lsdsk
----------------------------------------------
Label Filtering Path
==============================================
VDI100M0001 DISABLED /dev/sdh
VDI100M0002 DISABLED /dev/sdg
VDI100M0003 DISABLED /dev/sdi
VDI100M0004 DISABLED /dev/sdj
VDI1G0001 DISABLED /dev/sde
...

and run the same writes:

[root@VM106 ~]# dd of=/dev/sde if=/dev/zero seek=$(( 97574895 + 5 )) bs=1 count=5 conv=notrunc
5+0 records in
5+0 records out
5 bytes (5 B) copied, 0.000124124 s, 40.3 kB/s

Here is what I have on /var/log/messages:

Sep  1 22:03:19 VM106 kernel: sd 4:0:2:0: [sde] Incomplete mode parameter data
Sep 1 22:03:19 VM106 kernel: sd 4:0:2:0: [sde] Assuming drive cache: write through
Sep 1 22:03:19 VM106 kernel: sde: unknown partition table

Nothing about I/O error and page write lost.

Without AFD filtering, the diskgroup is corrupt:

[root@VM106 ~]# od -t c --skip-bytes=$(( 97574895 - 10 )) --read-bytes=40 --width=80  /dev/sde
564157745 \0 \0 \0 \0 \0 \0 , 001 001 \r H e l l o \0 \0 \0 \0 \0 c k ! 001 006 ) 334 006 242 \0 \0 207 \0 200 \a ( 334 $ \0 \0

And, of course, those unexpected \0 are detected as corruption:

SQL> select * from franck;
select * from franck
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 30, block # 133)
ORA-01110: data file 30: '+FRANCK/CDB1/DATAFILE/franck.256.985734495'

Active Session History in PostgreSQL: blocker and wait chain

While the active session history extension for PostgreSQL is still in beta, some information is added to it.

The pg_active_session_history view is currently made of:

                   View "public.pg_active_session_history"
      Column      |           Type           | Collation | Nullable | Default
------------------+--------------------------+-----------+----------+---------
 ash_time         | timestamp with time zone |           |          |
 datid            | oid                      |           |          |
 datname          | text                     |           |          |
 pid              | integer                  |           |          |
 usesysid         | oid                      |           |          |
 usename          | text                     |           |          |
 application_name | text                     |           |          |
 client_addr      | text                     |           |          |
 client_hostname  | text                     |           |          |
 client_port      | integer                  |           |          |
 backend_start    | timestamp with time zone |           |          |
 xact_start       | timestamp with time zone |           |          |
 query_start      | timestamp with time zone |           |          |
 state_change     | timestamp with time zone |           |          |
 wait_event_type  | text                     |           |          |
 wait_event       | text                     |           |          |
 state            | text                     |           |          |
 backend_xid      | xid                      |           |          |
 backend_xmin     | xid                      |           |          |
 top_level_query  | text                     |           |          |
 query            | text                     |           |          |
 cmdtype          | text                     |           |          |
 queryid          | bigint                   |           |          |
 backend_type     | text                     |           |          |
 blockers         | integer                  |           |          |
 blockerpid       | integer                  |           |          |
 blocker_state    | text                     |           |          |

You could see it as samplings of pg_stat_activity providing more information:

  • ash_time: the sampling time
  • top_level_query: the top level statement (in case PL/pgSQL is used)
  • query: the statement being executed (not normalised, as it is in pg_stat_statements, means you see the values)
  • cmdtype: the statement type (SELECT,UPDATE,INSERT,DELETE,UTILITY,UNKNOWN,NOTHING)
  • queryid: the queryid of the statement which links to pg_stat_statements
  • blockers: the number of blockers
  • blockerpid: the pid of the blocker (if blockers = 1), the pid of one blocker (if blockers > 1)
  • blocker_state: state of the blocker (state of the blockerpid)

Thanks to the queryid field you are able to link the session activity with the sql activity.

The information related to the blocking activity (if any) has been added recently. Why? To easily drill down in case of session being blocked.

Let’s see how we could display some interesting information in case of blocked session(s), for examples:

  • The wait chain
  • The seconds in wait in this chain
  • The percentage of the total wait time that this chain represents

As PostgreSQL provides recursive query and window functions, let’s make use of them to write this query:

postgres@pgu:~$ cat pg_ash_wait_chain.sql
WITH RECURSIVE search_wait_chain(ash_time,pid, blockerpid, wait_event_type,wait_event,level, path)
AS (
          SELECT ash_time,pid, blockerpid, wait_event_type,wait_event, 1 AS level,
          'pid:'||pid||' ('||wait_event_type||' : '||wait_event||') ->'||'pid:'||blockerpid AS path
          from pg_active_session_history WHERE blockers > 0
        union ALL
          SELECT p.ash_time,p.pid, p.blockerpid, p.wait_event_type,p.wait_event, swc.level + 1 AS level,
          'pid:'||p.pid||' ('||p.wait_event_type||' : '||p.wait_event||') ->'||swc.path AS path
          FROM pg_active_session_history p, search_wait_chain swc
          WHERE p.blockerpid = swc.pid and p.ash_time = swc.ash_time and p.blockers > 0
)
select round(100 * count(*) / cnt)||'%' as "% of total wait",count(*) as seconds,path as wait_chain  from (
        SELECT  pid,wait_event,path,sum(count) over() as cnt from (
                select ash_time,level,pid,wait_event,path,count(*) as count, max(level) over(partition by ash_time,pid) as max_level
                FROM search_wait_chain where level > 0 group by ash_time,level,pid,wait_event,path
        ) as all_wait_chain
        where level=max_level
) as wait_chain
group by path,cnt
order by count(*) desc;

Let’s launch this query while only one session is being blocked by another one:

postgres@pgu:~$ psql -f pg_ash_wait_chain.sql
 % of total wait | seconds |                 wait_chain
-----------------+---------+--------------------------------------------
 100%            |      23 | pid:1890 (Lock : transactionid) ->pid:1888
(1 row)

It means that the pid 1890 is waiting since 23 seconds on the transactionid wait event, while being blocked by pid 1888. This wait chain represents 100% of the blocking activity time.

Now another session comes into the game, query the active session history view one more time:

postgres@pgu:~$ psql -f pg_ash_wait_chain.sql
 % of total wait | seconds |                                  wait_chain
-----------------+---------+------------------------------------------------------------------------------
 88%             |     208 | pid:1890 (Lock : transactionid) ->pid:1888
 12%             |      29 | pid:1913 (Lock : transactionid) ->pid:1890 (Lock : transactionid) ->pid:1888
(2 rows)

So we still see our first blocking chain. It is now not the only one (so represents 88% of the blocking activity time).

We can see a new chain that represents 12% of the blocking activity time:

  • pid 1913 (waiting on transactionid) is blocked since 29 seconds by pid 1890 (waiting on transactionid) that is also blocked by pid 1888.

Let’s commit the transaction hold by pid 1888 and launch the query again 2 times:

postgres@pgu:~$ psql -f pg_ash_wait_chain.sql
 % of total wait | seconds |                                  wait_chain
-----------------+---------+------------------------------------------------------------------------------
 57%             |     582 | pid:1890 (Lock : transactionid) ->pid:1888
 40%             |     403 | pid:1913 (Lock : transactionid) ->pid:1890 (Lock : transactionid) ->pid:1888
 3%              |      32 | pid:1913 (Lock : transactionid) ->pid:1890
(3 rows)

postgres@pgu:~$ psql -f pg_ash_wait_chain.sql
 % of total wait | seconds |                                  wait_chain
-----------------+---------+------------------------------------------------------------------------------
 57%             |     582 | pid:1890 (Lock : transactionid) ->pid:1888
 40%             |     403 | pid:1913 (Lock : transactionid) ->pid:1890 (Lock : transactionid) ->pid:1888
 3%              |      33 | pid:1913 (Lock : transactionid) ->pid:1890
(3 rows)

As you can see the first two chains are still displayed (as the query does not filter on ash_time) but are not waiting anymore (seconds does not increase) while the last one (new one) is still waiting (seconds increase).

Remarks

Conclusion

We have seen how the blocking information part of the pg_active_session_history view could help to drill down in case of blocking activity.

Download your SR content from MOS

You may want to keep track of your Oracle Service Requests offline. Or simply be able to read them as simple text. Here is a simple way to download all of them to a simple text file.

First, it is easy to get a list of the Service Requests as an Excel file. Just list them on the GUI. You may choose:

  • The Support Identifiers (CSI) on the right
  • Only SR where you are primary contact or all of them, with the ‘person’ icon
  • Include closed SRs with the red check icon
  • The columns: View -> Columns -> Show all

And then View -> Export to XLS

The service_requests.xls is actually in XML format which is easy to parse, but you can also convert it to CSV. Here I have saved it to service_requests.csv

Then with AWK and LYNX installed here is how to get each SR into text:

awk -F";" 'NR>1{gsub("[()/?%*:|. \\" q qq "]","_");print "lynx -dump -accept-all-cookies -auth=: " q "https://support.oracle.com/epmos/faces/SrDetailPrint?srNumber=" $2 "&print=true&sysmsg=true&sortBy=Newest%20on%20Top" q "> " q $9 "_" $2 "_" $1".txt" q '} q="'" qq='"' service_requests.csv | sh -x

The ideas is to build the file name from the contact ($9), the SR number ($2), and the subject ($1 after translating some characters to ‘_’). And then download using the url used for the ‘print’ view. The authentication is easy with -auth=ID:PASSWD the argument where you pass your Oracle SSO login username and password. Just replace and with yours.

Note that you should not try to run that in parallel or you will get ‘The user has already reached the maximum allowed number of sessions’.

How to Linux for the SQL DBA Articles on Simple Talk

I’ve started to write a series of articles on Simple Talk from Redgate on Linux for the SQL Server DBA.  Thanks to Kathi Kellenberger, who came to my pre-con in Indianapolis SQL Saturday and then asked if it was something that I’d be up to doing.

The biggest challenge when doing one hour sessions on Linux at SQL Saturdays is that you find out, one hour is just no where, near enough.  The eight hour pre-con we get to dig in deeper, actually work through a number of labs and I feel like students leave with a better grasp on how to work with this new operating system in preparation for managing a database on it.

If you’re curious to learn or just need a refresher, here’s the first article in the series, with more to come!



Tags:  


Del.icio.us



Facebook

TweetThis

Digg

StumbleUpon




Copyright © DBAKevlar [How to Linux for the SQL DBA Articles on Simple Talk], All Right Reserved. 2018.

RTFM ?

My entrance at the Polish Oracle User Group conference 2017 has just resurfaced on Twitter. There is a back-story to this which contains an allegorical lesson in using Oracle. As I said in the opening remarks in the subsequent presentation: “The lesson began before I got to the stage”.

Like all the other speakers at POUG2017 I had received an email asking me to name a tune for a playlist. Having decided that Beethoven, Brahms and Tchaikowski were not the sort of composers the organisers had in mind I nominated (with a touch of irony) “Who wants to live forever?” by Queen (despite Richard Foote’s strenuous efforts to turn the Oracle world to David Bowie).

When the conference started I noticed two things: first, that the tunes for the “playlist” were actually being used to accompany speakers to the stage, secondly that the admin staff were all wearing monk-like gowns and hoods. So I asked for my tune to be changed to “The Imperial March” (Darth Vader’s theme) and borrowed the gown from the tallest admin person, with the results you see in the video clip.

So what’s the Oracle allegory ?

First you read the manuals, then you observe how it really works before you go live.

 

COMMIT

By Franck Pachot

.
COMMIT is the SQL statement that ends a transaction, with two goals: persistence (changes are durable) and sharing (changes are visible to others). That’s a weird title and introduction for the 499th blog post I write on the dbi-services blog. 499 posts in nearly 5 years- roughly two blog posts per week. This activity was mainly motivated by the will to persist and share what I learn every day.

Persistence is primarily for myself: writing a test case with a little explanation is a good way to remember an issue encountered, and Google helps to get back to it when the problem is encountered again later. Sharing is partly for others: I learn a lot from what others are sharing (blogs, forums, articles, mailing lists,…) and it makes sense to also share what I learn. But in addition to that, publishing an idea is also a good way to validate it. If something is partially wrong or badly explained, or just benefits from exchanging ideas, then I’ll get feedbacks, by comments, tweets, e-mails.

This high throughput of things I learn every day gets its source from multiple events. In a consulting company, going from one customer to another means different platforms, versions, editions, different requirements, different approaches. Our added value is our experience. From all the problems seen in all those environments, we have build knowledge, best practices and tools (this is the idea of DMK) to bring a reliable and efficient solution to customers projects. But dbi services also invests a lot in research and training, in order to build this knowledge pro-actively, before encountering the problems at customers. A lot of blog posts were motivated by lab problems only (beta testing, learning new features, setting up a proof of concept before proposing it to a customer). And then encountered later at customers, with faster solutions as this had been investigated before. Dbi services also provides workshops for all technologies and preparing training exercises, as well as giving the workshop, was also a great source of blog posts.

I must say that dbi services is an amazing company in this area. Five years ago, I blogged in French on developpez.com and answered forums such as dba-village.com, and wrote a few articles for SOUG. But as soon as I started at dbi services, I passed the OCM, I presented for the first time in public, at DOAG, and then at many local and international conferences. I attended my first Oracle Open World. I became ACE and later ACE Director. The blogging activity is one aspect only. What the dbi services Technology Organization produces is amazing, for the benefit of the customers and the consultants.

You may have heard that I’m going to work in the database team at CERN, which means quiescing my consulting and blogging activity here. For sure I’ll continue to share, but probably differently. Maybe on the Databases at CERN blog, and probably posting on Medium. Blogs will be also replicated to http://www.oaktable.net/ of course. Anyway, it is easy to find me on LinkedIn or Twitter. For sure I’ll be at conferences and probably not only Oracle ones.

Database transparent_1000pxOracle_100_1000pxI encourage you to continue to follow the dbi services blog, as I’ll do. Many colleagues are already sharing on all technologies. And new ones are coming. Even if my goal was the opposite, I’m aware that publishing so often may have throttled other authors to do so. I’m now releasing some bandwidth to them. The dbi services blog is in the 9th position in the Top-100 Oracle blogs and 27th position in the Top-60 Database blogs with 6 blog posts a week on average. And there’s also a lot non-database topics covered as well. So stay tuned on https://blog.dbi-services.com/.

 

Cet article COMMIT est apparu en premier sur Blog dbi services.

18c database creation on Windows

Hopefully you’ve followed my very simple and easy guide to downloading the 18c database software for Windows. But of course, software on its own is not much use – we need a database! So let’s get cracking and create one. Using the Start menu like I’ve done below, or using the Windows panels, locate the Database Configuration assistant and start it.

image

 

After a few seconds the initial screen will ask what you want to do.  Choose “Create Database”.

image

 

If you like you could just go with “Typical Configuration” and you’ll be done in just a couple of clicks, but I always prefer to opt for the “Advanced Configuration” for two reasons. Firstly, even if you accept all of the defaults, it gives you a better idea of what options are going to be installed, where the files will be stored etc.  And secondly…well…we all like to think of ourselves as advanced don’t we Smile

image

 

For just research and exploration on your own Windows machine, you’ll probably want to opt for just a single instance database.  RAC takes a bit more setup and complexity. For a faster install, choose one of the options that has the datafiles included.  This way, the installer will just copy some existing files and seed them as your database, rather then building the entire instance from scratch.

image

 

Now choose a unique and memorable name for your database. I’ve reached into the depths of my imagination and come up with “db18” for my version 18 database. Go figure Smile. I’d also recommend you go with a container database configuration, because that is the strategic direction for Oracle going forward, so if you are going to have a database to skill up on, it makes sense for that database to be a container database.

image

 

To keep things simple, I’m just nominating 1 single location for all of my database files. It can be anywhere but a common convention is that wherever you house them, you’ll have a folder called “oradata” and then a folder for each database you create under that.

image

 

I’m skipping the fast recovery area and archiving at this stage. If I start to get serious with testing things like backup and recovery, then I would revisit this after database creation to enable at least archiving so that I can explore all the goodies that RMAN has to offer.

image

 

On a brand new installation, it is likely you will not have a pre-existing listener to choose from. (If you had a previous installation, or had run the Network Configuration Assistant already, then you would see a listener to use).

I’m creating one called LISTENER18. The default port is normally 1521, but I’ve opted for 1518 just to align it with the version I’m using.

image

 

I’m skipping Data Vault and Label Security, but hopefully you can now see why it’s cool to go with the “Advanced Configuration” – you get to see all the potential functionality areas of the database that you might want to explore.

image

 

Now you choose how much of your server/desktop/laptop you’re going to allow this database to grab. My machine has plenty of RAM, but it also has a stack of other database versions running on it to handle my AskTOM daily tasks. So I’ll keep this dude at around 6G.

image

 

Now we’ll flick across the other tabs on this screen to see if there anything of note. The default for processes seems to have gone up in this version (I think it used to be around 300 in 12c) but in any event, that’s more than enough for me on this machine.

image

 

I have simple rule for character sets – UTF all the way. Single byte charactersets are soooo last century. We live in a global village, so you should be able to handle characters from all over the world!

image

 

And for the last tab, I’m going to opt for the sample schemas, so that when my database is created I’m not just left with an empty database shell. I want some sample data there so I can jump straight in and start experimenting with this release.

image

 

If I’m a DBA, I might go for some EM management to see how that all works, but for now, I’m skipping that.

image

 

Being a sandbox for experimenting, I’m setting all the passwords to a common value. Naturally this will not what you’ll be doing for your production databases!

image

 

So now I’m ready to go. I always tick the “Generate Scripts” option because it lets me see what is actually occurring when the creation assistant is doing its job. Even so, the scripts are probably not what I would use to automate a database install, since the ‘dbca’ command has nice command line option nowadays, along with the ‘-silent’ option so you can create an entire database with just a single command.

image

 

I finally get a summary of what is about to occur, and we’re off! Database creation is go for launch!

image

 

The total time to create your database will depending on your hardware, in particular how fast your storage is. I’ve done a few creations now using both flash storage and conventional hard drives, and unsurprisingly the flash storage is faster. You’re probably looking at around 10 minutes to complete.

image

 

When your database creation is complete, you’ll get the standard summary screen and you are ready to go.

image

 

And here it is – the finished product! My 18c database on Windows is ready to go. By default, the underlying Windows service will have a Start status of “Automatic” which means your database will start every time Windows starts. If you are short on memory, or do not want the database started unless you explicitly want it to, you can set this to Manual via “services.msc”

image

 

You can watch the video version of this installation here

Enjoy your 18c Windows database !