Skip navigation
1 2 Previous Next

Dear DBA

26 posts

LIMIT the Duration of your Nightly Batch Job: implement a session maximum duration


The issue at hand

In your production environment, the nightly batch job spilled over the morning activity. How can you limit the duration of your batch job (or any Oracle session for that matter) ? Let's do it with the Oracle Profile CONNECT_TIME feature because it's easy to implement.


The feature we are going to use: CONNECT_TIME in a PROFILE

A profile is an Oracle feature that you ascribe to Oracle users to limit what they can do. It's typically used to manage the fact that an account (ie an Oracle user) will lock after a set number of failed logon attempts.

Here is what a profile can manage:

For passwords:

  • Expire in (days)         
  • Lock (days past expiration)  
  • Number of passwords to keep          
  • Number of days to keep for 
  • Complexity function 
  • No. failed logins       
  • No. days to lock for

For resources:

  • Concurrent Sessions (Per User)        
  • Reads/Session (Blocks)        
  • Reads/Call (Blocks)  
  • Private SGA (KBytes)          
  • Composite Limit (Srvc. Units)
  • Profile CPU/Session (Sec./100)        
  • CPU/Call (Sec./100) 
  • Idle Time (Minutes)
  • Connect Time (Minutes)

DEFPROFILE.PNGlimibatch.JPG

How to setup a PROFILE

To benefit from a profile's features, you must

  1. set the resource_limit parameter to TRUE
  2. create a profile with the desired limitations
  3. ascribe that profile to the relevant Oracle users

 

Example of the implementation of a PROFILE named "limitebatch", with connect_time=180

ALTER SYSTEM SET RESOURCE_LIMIT=TRUE ;  --1

-- DROP PROFILE limitebatch cascade;

show parameter resource_limit ;

CREATE PROFILE limitebatch LIMIT CONNECT_TIME 180 ;  -- 2

alter user utilisateurbatch PROFILE limitebatch;            -- 3

Select username,profile from dba_users where lower(username) ='utilisateurbatch' ;

 

NAME           TYPE    VALUE

-------------- ------- -----

resource_limit boolean TRUE 

 

Profile LIMITEBATCH créé(e).

 

 

User UTILISATEURBATCH modifié(e).

 

 

USERNAME                       PROFILE                     

------------------------------ ------------------------------

UTILISATEURBATCH                      LIMITEBATCH                  

UPROPRIETAIRE                      DEFAULT

 

Once this profile has been ascribed to that user, no session from that user will be able to last more than 3H.  If the 3H limit is reached,here is what you will find in the batch log:

 

ERROR at line 1:
ORA-02399:,temps max. de connexion dépassé, vous êtes déconnecté
ORA-06512: à ligne 2272
ORA-02399: temps max. de connexion dépassé, vous êtes déconnecté
Disconnected from Oracle Database 11g Enterprise Edition Release

To disable that feature, do not drop the profile (so as to keep it for future use), but ascribe the DEFAULT profile to your user:

ALTER USER UTILISATEURBATCH PROFILE DEFAULT ; 

SELECT USERNAME,PROFILE FROM DBA_USERS WHERE lower(username) ='utilisateurbatch';

 

What really happens when a session is disconnected because the CONNECT_TIME limit has been reached?

The ORA-02399 error is generated of course, but in concrete terms, according to Oracle documentation

http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_6010.htm

If a user exceeds the CONNECT_TIME or IDLE_TIME session resource limit, then the database rolls back the current transaction and ends the session. When the user process next issues a call, the database returns an error.

LIMITER LA DUREE d’un BATCH : mise en place d’un temps maximum d’activité


La problématique

Plusieurs fois, en production, des batchs de nuit ont empiété sur le matin. Comment limiter la durée des batchs ? Voyons ensemble la fonctionnalité  CONNECT_TIME des profils car elle est simple à mettre en oeuvre.

 

La fonctionnalité : le CONNECT_TIME du PROFILE

Le profil est une fonctionnalité Oracle, qu’on attribue aux utilisateurs Oracle pour limiter ce qu’ils peuvent faire. C’est notamment utilisé pour gérer le fait que le compte se verrouille après tant d’erreurs de mot de passe.

Voici ce que peut gérer un profil :

Pour les mots de passe :

  • Expire in (days)     
  • Lock (days past expiration)
  • Number of passwords to keep      
  • Number of days to keep for
  • Complexity function
  • No. failed logins   
  • No. days to lock for

Pour les ressources :

  • Concurrent Sessions (Per User)      XKCD+-+1073.png
  • Reads/Session (Blocks)    
  • Reads/Call (Blocks)
  • Private SGA (KBytes)      
  • Composite Limit (Srvc. Units)
  • Profile CPU/Session (Sec./100)    
  • CPU/Call (Sec./100)
  • Idle Time (Minutes)
  • Connect Time (Minutes)

limibatch.JPGDEFPROFILE.PNG

Mode d’emploi du PROFILE

Pour bénéficier des fonctionnalités d’un profil, il faut

  1. changer le paramètre resource_limit pour le passer à TRUE
  2. créer un profil avec les limites voulues
  3. attribuer ce profil aux utilisateurs concernés

 

Exemple de mise en place d'un PROFILE nommé "limitebatch", avec connect_time=180

ALTER SYSTEM SET RESOURCE_LIMIT=TRUE ;  --1

-- DROP PROFILE limitebatch cascade;

show parameter resource_limit ;

CREATE PROFILE limitebatch LIMIT CONNECT_TIME 180 ;  -- 2

alter user utilisateurbatch PROFILE limitebatch;            -- 3

Select username,profile from dba_users where lower(username) ='utilisateurbatch' ;

 

NAME           TYPE    VALUE

-------------- ------- -----

resource_limit boolean TRUE 

 

Profile LIMITEBATCH créé(e).

 

 

User UTILISATEURBATCH modifié(e).

 

 

USERNAME                       PROFILE                     

------------------------------ ------------------------------

UTILISATEURBATCH                      LIMITEBATCH                  

UPROPRIETAIRE                      DEFAULT

 

Un fois le profil attribué à l’utilisateur, aucune session de cet utilisateur ne pourra durer au-delà de 3H.

Si cela se produit, voici ce qu’on trouvera dans la log du batch :

 

ERROR at line 1:
ORA-02399:,temps max. de connexion dépassé, vous êtes déconnecté
ORA-06512: à ligne 2272
ORA-02399: temps max. de connexion dépassé, vous êtes déconnecté
Disconnected from Oracle Database 11g Enterprise Edition Release

Pour désactiver cette fonctionnalité, ne pas supprimer le profil (il pourra resservir), mais attribuer à l’utilisateur le profil DEFAULT :

ALTER USER UTILISATEURBATCH PROFILE DEFAULT ;

SELECT USERNAME,PROFILE FROM DBA_USERS WHERE lower(username) ='utilisateurbatch';

 

Que se passe-t’il quand une session est déconnectée ?

Cela génère l’erreur ORA-02399 bien sûr, mais concrètement, d’après la doc oracle

http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_6010.htm

Si un utilisateur dépasser la limite CONNECT_TIME or IDLE_TIME,  la transaction en cours de l’utilisateur est rollbackée et sa session coupée. Lorsque le processus utilisateur fait un nouvel appel server, la base de données renvoie l’erreur ora-02399.

The following was simulated on 12.1.0.2 but it's similar on older versions (=> 10gR2 though).

When faced with the "buffer busy waits" wait event, it's interesting to find out which blocks are being accessed simultaneously, and thus generating that wait event, so as to determine which part of your code is causing that Concurrency.  Granted, an AWR report will report the most contended for segments (in the "Segments by Buffer Busy Waits" section), but they may not necessarily be the segments behind the "buffer busy waits"  events you want to investigate.

So let's have several concurrent sessions update the same blocks and observe what happens.  Then we will discover how to determine which object those blocks belong to.


I've got a CHANNELS2 table that contains 5 rows scattered over 4 blocks. 

BLOCKS    TABLE_NAME

4    CHANNELS2

 

I am going to update that table heavily with 6 concurrent sessions.  Each session is going to update all of that same CHANNELS2 table 1 million times (nevermind the usefulness of this -- it's strictly for demo purposes). 

BEGIN

FOR i IN 1 .. 100000 loop

UPDATE channels2 SET channel_id=rownum+i;

COMMIT ;

END loop;

END;

/

 

While the 6 concurrent user sessions are running, my SYSTEM session will monitor v$session in real-time to observe those "buffer busy waits" wait events, with that SELECT, run manually at short intervals:

SELECT username,SID,p1,p2,event,status,logon_time,blocking_session,wait_time,ROW_WAIT_OBJ#,ROW_WAIT_ROW# FROM v$session WHERE TYPE='USER' ORDER BY logon_time;

In order to witness that for yourselves, please watch the enclosed short video, which has the added boon to demonstrate how to do it in a nifty way with SQLDeveloper.

The important information reported by that SELECT is the P1 and P2 columns, which, for each buffer busy wait, list the file_id and the block id of the data block that is being contended for.

SH    247    111286803 buffer busy waits                   ACTIVE   04:12:28                        0 964370
SH    249    111286803 buffer busy waits                    ACTIVE   04:14:34                        0 964370
SH    131    111286803 buffer busy waits                   ACTIVE   04:14:40                        0 964370

 

7 rows selected

 

USERNAME   SID    P1    P2 EVENT                               STATUS   LOGON_TIME BLOCKING_SESSION  WAIT_TIME ROW_ ROW_

--------- ---- ---------- ---------- ---------------------------------------- -------- ---------- ---------------- ---------- ---- ----

SYSTEM129 1413697536     1 SQL*Net message to client           ACTIVE   02:58:50                       -1 938870
SH    250 1415053318262167 enq: TX - row lock contention       ACTIVE   03:31:25           247     0 964370
SH    364    111286803 buffer busy waits                   ACTIVE   03:32:46                        0 964370
SH    362 1413697536     1 SQL*Net message from client         INACTIVE 03:33:39                        0   -10
SH    247    111286803 buffer busy waits                   ACTIVE   04:12:28                        0 964370
SH    249    111286803 buffer busy waits                   ACTIVE   04:14:34                        0 964370
SH    131    111286803 buffer busy waits                   ACTIVE   04:14:40                       -1   -10

 

7 rows selected

 

USERNAME   SID    P1    P2 EVENT                               STATUS   LOGON_TIME BLOCKING_SESSION  WAIT_TIME ROW_ ROW_

--------- ---- ---------- ---------- ---------------------------------------- -------- ---------- ---------------- ---------- ---- ----

SYSTEM129 1413697536     1 SQL*Net message to client           ACTIVE   02:58:50                       -1 938870
SH    250 2364282784   354 latch: In memory undo latch         ACTIVE   03:31:25           247     0 964370
SH    364    111286803 buffer busy waits                   ACTIVE   03:32:46                        0 964370
SH    362 1413697536     1 SQL*Net message from client         INACTIVE 03:33:39                        0   -10
SH    247     4   224 buffer busy waits                   ACTIVE   04:12:28                       -1   -10
SH    249    111286803 buffer busy waits                   ACTIVE   04:14:34                        0 964370
SH    131    111286803 buffer busy waits                   ACTIVE   04:14:40                        0 964370

 

So, as the video plainly shows (and the short text excertp here above), the most contended for block is block 1286803 of file#11.  First let's find out what file11 is:

FILE#    NAME

11    /home/oracle/app/oracle/oradata/cdb1/orcl/example01.dbf

which undoubtedly belongs to the EXAMPLE tablespace.  But which object(s) of that tablespace contain(s) block 1286803?  There are several ways to find out, but this one, if you can afford it, might be the quickest.

With my SYSTEM session (SID 129, as identified here above):

ALTER SESSION SET TRACEFILE_IDENTIFIER = mydumpedblock;

alter system dump datafile 11 block 1286803 ;

This ALTER SYSTEM DUMP command dumps the contents of datablock 1286803 into a text file, located in my "Diag Trace" folder.

select * from v$diag_info where name='Diag Trace';

INST_ID    NAME    VALUE    CON_ID

1    Diag Trace    /home/oracle/app/oracle/diag/rdbms/cdb1/cdb1/trace    0

 

Now in a terminal, I can peruse the generated file, which I can easily identify thanks to the TRACEFILE_IDENTIFIER used above:

[oracle@localhost trace]$ ls -lrtR | tail

-rw-r-----. 1 oracle oracle      81 Mar 28 18:25 cdb1_ora_6638.trm

-rw-r-----. 1 oracle oracle   41669 Mar 28 18:25 cdb1_ora_6638.trc

-rw-r-----. 1 oracle oracle     670 Mar 28 18:30 cdb1_mmon_2174.trm

-rw-r-----. 1 oracle oracle    7140 Mar 28 18:30 cdb1_mmon_2174.trc

-rw-r-----. 1 oracle oracle      70 Mar 28 18:31 cdb1_ora_6638_MYDUMPEDBLOCK.trm

-rw-r-----. 1 oracle oracle   39463 Mar 28 18:31 cdb1_ora_6638_MYDUMPEDBLOCK.trc

The header of that trace file confirms that it was generated by my SID 129 session:

*** SESSION ID:(129.13337) 2016-03-28 18:31:56.662

The data to the right-hand side of the dump file shows some of the alphanumeric data from my CHANNELS2 table:

[Direct Sales.Dir]

[ect....Channel t]

[otal...,.....Yd.]

[Tele Sales.Direc number: this

But how do you tell for sure which object this data is coming from?  Keep in mind this object could be an index.  Look for the "seg" character string in that dump file, and you wil find "seg/obj" followed by an hexadecimal: this is the object_id of the object you are looking for.

seg/obj: 0x178b5

  Grab a scientific calculator and convert to decimal this 178b5 heximal value.  That's 96 437 in decimal.

select owner, object_type ||' '||object_name " the long sought-after object" from all_objects  where object_id=96437;

OWNER     the long sought-after object

SH    TABLE CHANNELS2

And voilà!  With a few SELECTs and a block dump, I was able to determine which block of which object was causing my "buffer busy waits" wait events.



Additonal note for version 12c: the object_id appears in plain decimal in the dump file:

BH (0x883dad98) file#: 11 rdba: 0x0293a293 (10/1286803) class: 1 ba: 0x8807e000

  set: 12 pool: 3 bsz: 8192 bsi: 0 sflg: 0 pwc: 0,0

  dbwrid: 0 obj: 96437

But in version 12c, it's even useless to generate a dump file, since the object_id of the "buffer busy waits" event appears in the ROW_WAIT_OBJ# column of v$session.

SH    249    111286803 buffer busy waits                   ACTIVE   04:14:34                        0 964370
SH    131    111286803 buffer busy waits                   ACTIVE   04:14:40                        0 96437

The famous EXPLAIN PLAN FOR command gives you the planned execution plan for a given query.  Here is an example:


explain plan for

  2  select  prod_id,unit_cost,unit_price from costs where unit_price > 700      ;

Plan FOR succeeded.

SQL> SELECT *  FROM   TABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT                                                                                                                                 

------------------------------------------------------------------------------------------------------------------------------------------------------

Plan hash value: 1918128980                                                                                                                        

                                                                                                                                                      ---------------------------------------------------------------------------------------------                                                      

| Id  | Operation           | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |                                                      

---------------------------------------------------------------------------------------------                                                      

|   0 | SELECT STATEMENT    |       | 50050 |   684K|   136   (1)| 00:00:01 |       |       |                                                      

|   1 |  PARTITION RANGE ALL|       | 50050 |   684K|   136   (1)| 00:00:01 |     1 |    28 |                                                      

|*  2 |   TABLE ACCESS FULL | COSTS | 50050 |   684K|   136   (1)| 00:00:01 |     1 |    28 |                                                      

---------------------------------------------------------------------------------------------                                                      

Predicate Information (identified by operation id):                                                                                                

---------------------------------------------------                                                                                                

   2 - filter("UNIT_PRICE">700)

 

This is the planned execution plan for my SELECT -- it is what Oracle intends to do to execute my query, after evaluating, to the best of its knowledge (statistics), several execution plans. When you ask for EXPLAIN PLAN FOR, the query is not actually run -- only the intended execution plan is computed.  This comes in handy for queries that last a long time, the performance of which you need to assess.  The resulting execution plan gives you  an idea of how well that query is going to perform if you actually run it.


If you can afford to run the query (if you can wait for it to complete, if you are not on a production system, if you are allowed to modify data...), do it and ask Oracle to show the plan it actually used.  This is better for 2 reasons:

  1. if you actually run the query, you can time it (SQLDeveloper automatically tells you how long your script/query has run at the top of its result tab; or use set timing on in SQL+ ) and get an accurate idea of how well your query performs (in your current environnment)
  2. you get the plan that was actually used by Oracle to execute your query, and some extra information (see below)


Here is how to do it.  This is in SQLCLI and on Oracle 12.1.0.2 but it's the same in SQL+ and on 11g.

 

create table costs2 as select * from costs;

create index fd_up2 on costs2 (unit_price) ;

select TABLE_NAME, NUM_ROWS,LAST_ANALYZED from USER_TABLES where TABLE_NAME like 'COST%' ;

 

explain plan for

select prod_id,unit_cost,unit_price from costs2 where unit_price > 700;

SELECT *  FROM   TABLE(DBMS_XPLAN.DISPLAY);

 

select /*+ gather_plan_statistics */  prod_id,unit_cost,unit_price from costs2 where unit_price > 700;

SELECT *  FROM   TABLE(DBMS_XPLAN.DISPLAY_CURSOR(null,null,'ALLSTATS LAST')) ;

 

Output:

SQL> create table costs2 as select * from costs;

Table COSTS2 created.

SQL> create index fd_up2 on costs2 (unit_price) ;

Index FD_UP2 created.

SQL> select TABLE_NAME, NUM_ROWS,LAST_ANALYZED from USER_TABLES where TABLE_NAME like 'COST%' ;

TABLE_NAMENUM_ROWS LAST_ANALYZED

------------ ---------- ---------------

COSTS2      82112 29-FEB-16
COSTS       82112 18-OCT-15

 

As as side note, here is demonstrated a nifty 12c feature: table statistics are automatically gathered at creation time.  My COSTS2 table has just been created  and NUM_ROWS is not empty even though I have not run DBMS_STATS.GATHER_TABLE_STATS on it.  Statistics have been gathered for it, as witnessed by today's date in the LAST_ANALYZED column.


explain plan for

  2   select prod_id,unit_cost,unit_price from costs2 where unit_price > 700;

Plan FOR succeeded.

 

SQL> SELECT *  FROM   TABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT                                                                                                                                 

------------------------------------------------------------------------------------------------------------------------------------------------------

Plan hash value: 2840313604                                                                                                                        

----------------------------------------------------------------------------                                                                       

| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |                                                                       

----------------------------------------------------------------------------                                                                       

|   0 | SELECT STATEMENT  |        | 50050 |   684K|   104   (1)| 00:00:01 |                                                                       

|*  1 |  TABLE ACCESS FULL| COSTS2 | 50050 |   684K|   104   (1)| 00:00:01 |                                                                       

----------------------------------------------------------------------------                                                                       

Predicate Information (identified by operation id):                                                                                                

---------------------------------------------------                                                                                                

   1 - filter("UNIT_PRICE">700)

 

select /*+ gather_plan_statistics */  prod_id,unit_cost,unit_price from costs2 where unit_price > 700;

 

14790.93

1190.69

... thousands of result lines here ....

21808.861026.61
14790.931190.69
141001.751219.13

 

6,161 rows selected

Elapsed: 410

 

SELECT *  FROM   TABLE(DBMS_XPLAN.DISPLAY_CURSOR(null,null,'ALLSTATS LAST')) ;

PLAN_TABLE_OUTPUT                                                                                                                                 

------------------------------------------------------------------------------------------------------------------------------------------------------

SQL_ID  74rkrtfzxh4yk, child number 0                                                                                                              

-------------------------------------                                                                                                              

select /*+ gather_plan_statistics */  prod_id,unit_cost,unit_price from                                                                            

costs2 where unit_price > 700                                                                                                                      

                                                                                                                                                   

Plan hash value: 2840313604                                                                                                                        

--------------------------------------------------------------------------------------                                                             

| Id  | Operation         | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |                                                             

--------------------------------------------------------------------------------------                                                             

|   0 | SELECT STATEMENT  |        |      1 |        |   6161 |00:00:00.01 |     777 |                                                             

|*  1 |  TABLE ACCESS FULL| COSTS2 |      1 |  50050 |   6161 |00:00:00.01 |     777 |                                                             

--------------------------------------------------------------------------------------                                                             

Predicate Information (identified by operation id):                                                                                                

---------------------------------------------------                                                                                                

   1 - filter("UNIT_PRICE">700)

 

The top execution plan is the result of our EXPLAIN PLAN FOR command.  It contains, as displayed by our SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY), the id, operation, name, rows, bytes, cost, and time columns.

While the bottom execution plan is the one that was actually executed by Oracle, as it appears after the execution of our query, as displayed by our SELECT *  FROM  TABLE (DBMS_XPLAN.DISPLAY_CURSOR(null,null,'ALLSTATS LAST')).  It contains the Id, operation, name, starts, e-rows, a-rows, a-time, and buffers columns.  The e-rows column contains the same data as the rows column of the 1st SELECT, as it is the estimated rows.  The a-rows column is the actual rows.  So not only do you get the actual plan but you also discover whether your statistics are pertinent.


So how do you get that actual plan?  You must implement 2 things:

  1. as you can see in the example above, you must add the /*+ gather_plan_statistics */ hint to your query.  This tells Oracle to gather extra execution statistics as it executes the query.  It does add a small overhead, so use with caution.
  2. set serverouput must be set to off, as the SELECT *  FROM  TABLE (DBMS_XPLAN.DISPLAY_CURSOR(null,null,'ALLSTATS LAST')) will display the statistics of the query that was last run in that session. And set serveroutput on adds an extra call to to DBMS_OUTPUT.

Simulated in 11.2.0.3 and 12.1.0.2

A type of index does not appear in DBA_INDEXES or simular views.  These are nosegment indexes.  First, let's see what are nosegment indexes.


Let's pretend I must tune the following SELECT because it does a costly full table scan (let's assume the CUSTOMERS table is huge):

set autotrace on explain only

select count(1) from customers where CUST_STATE_PROVINCE='Utrecht' ;

COUNT(1)

----------

       853

 

Plan hash value: 296924608

--------------------------------------------------------------------------------

| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |           |     1 |    11 |   423   (1)| 00:00:01 |

|   1 |  SORT AGGREGATE    |           |     1 |    11 |            |          |

|*  2 |   TABLE ACCESS FULL| CUSTOMERS |   383 |  4213 |   423   (1)| 00:00:01 |

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

    2 - filter("CUST_STATE_PROVINCE"='Utrecht')

 

Obviously, an index on the "CUST_STA_PROVINCE" column would suffice to avoid the full table scan.  I cannot, however, modify that environnment, or cannot afford the time it would take to create an index on that huge table.

So, I can create a nosegment index, also known as a virtual index.

create index FD_CSP on customers (CUST_STATE_PROVINCE) nosegment ;

alter session set "_use_nosegment_indexes"=true ; -- to tell Oracle to use nosegment indexes

set autotrace on explain only

select count(1) from customers where CUST_STATE_PROVINCE='Utrecht' ;

 

Index FD_CSP created.

Session altered.

Autotrace Enabled

Displays the execution plan only.

  COUNT(1)

----------

       853

 

Plan hash value: 208049494

----------------------------------------------------------------------------

| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |        |     1 |    11 |     1   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE   |        |     1 |    11 |            |          |

|*  2 |   INDEX RANGE SCAN| FD_CSP |   383 |  4213 |     1   (0)| 00:00:01 |

----------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

    2 - access("CUST_STATE_PROVINCE"='Utrecht')

 

This way, my index is created instantly.  A nosegment index does not have a segment associated to it: there's no physical storage for it.  It's just a few lines in the Oracle dictionary tables.

I must, however, tell the database to avail itself of that type of index by issueing "alter session set "_use_nosegment_indexes"=true ;".  And as you can see in the EXPLAIN PLAN above, the optimizer chose that index.  Of course, If I actually run my SELECT, the database will not use my nosegment index, since it does not exist physically.

So to conclude that first part, nosegment indexes are handy to test the usefulnes of indexing columns when you cannot afford to do that for real.


Now, I have been using nosegment indexes for quite a while, but only recently have I discovered you will not find them in any of the _INDEXES views.  I thought it was weird enough to be the subject of this blog post (hence the title)!

select index_name from user_indexes where index_name='FD_CSP' ;

select index_name from all_indexes where index_name='FD_CSP' ;

select index_name from dba_indexes where index_name='FD_CSP' ;

select object_name,status, OBJECT_TYPE from user_objects where object_name='FD_CSP' ;

 

no rows selected

no rows selected

no rows selected

OBJECT_NAME  STATUS  OBJECT_TYPE

------------ ------- ------------

FD_CSP       VALID   INDEX

 

But they do appear in USER_OBJECTS (and DBA_OBJECTS and ALL_OBJECTS), and are listed as indexes!  At least, there is one view I can use to keep track of them.  But typically, nosegment indexes are used as try-outs before the eventual real index, so are short-lived (DROP INDEX is enough to get rid of them).

Just to be thorough, here is a screenshot of the "INFO" command of SQLCLI (SQLDeveloper's command line version), which gives all information about a table, including its indexes:

160131INFOonCUSTOMERS.png

And our nosegment index is not listed in there either.  So remember, nosegment indexes cannot be listed except with the _OBJECTS views.

Hello

I am launching a new online project, to help DBAs out there (which is the whole purpose of this blog as well): Dear DBA's Oracle Ref Desk

Title.jpg

It's a web page that's designed to be used as the home page of your browser.  The focus of the available information is the Oracle database. The idea is to have handy:

  1. relevant blog posts
  2. tweets (from Twitter) relevant to the work of a DBA
  3. bookmarks to DBA blogs, SQL resources, scripts
  4. links to relevant documentation


That start page can also be used to keep track of some Oracle community activity.  There's a "Social/OUG/Communities" tab for that: the tabs.jpg

In that tab on the far right, you will find community resources from Oracle corp (again, what is useful to DBAs): tweets from the Oracle Support Team, links to OTN threads.  There are also links to Oracle user group events (updates coming up) and more to come.

Here is what it looks like at launch time:

Oracle DB tab.jpg

The search field at the top lef-hand corner allows you to use the search engines listed to the righ of it.  So instead of rushing to your favorite search engine's home page, use Dear DBA's Oracle Ref Desk instead!

You will see that there is room left for more resources.  I cannot possibly know all the good resources for DBAs that are available on the Internet.  So please help me out, and our community of DBAs: tell me (use the comments on this blog post) which resources should appear on that start page.  I am willing to consider any suggestion.  So an added boon of that page is that you can have its author tailor it to your needs!

Sincerely hoping to better your everyday experience as an Oracle DBA, I look forward to your feedback.


Frank (a.k.a. Dear DBA)

Note:  I have simulated the following behaviors in both 11.2.0.3 and 12.1.0.2.


The OE user needs to access objects in the SH schema. So I create synonyms for the SALES2 table and for the PROFITS view. 

grant create any synonym to sh ; -- run with SYS

create synonym oe.profits FOR sh.profits ;

create synonym oe.sales2 FOR sh.sales2 ;

 

User OE also happens to have the following 2 system privileges:

grant exp_full_database,imp_full_database to oe ; -- run with SYS

Then I use the followings commands to verify that OE can access those SH objects

conn oe/oracle

sho user

delete profits ;

rollback ;

select count(*)  countProfits from profits ;

select count(*) countSAles2  from sales2 ;

select count(*) "# of Privileges" from SESSION_PRIVS ;


TEST 1:

Connected

 

USER is "OE"

 

Error starting at line : 10 in command -

delete profits

Error at Command Line : 10 Column : 8

Error report -

SQL Error: ORA-01752: cannot delete from view without exactly one key-preserved table

01752. 00000 -  "cannot delete from view without exactly one key-preserved table"

*Cause:    The deleted table had

           - no key-preserved tables,

           - more than one key-preserved table, or

           - the key-preserved table was an unmerged view.

*Action:   Redefine the view or delete it from the underlying base tables.

 

Rollback complete.

 

                           COUNTPROFITS

---------------------------------------

                                 916039

 

                            COUNTSALES2

---------------------------------------

                               10107273

 

                        # of Privileges

---------------------------------------

                                     99

 

That was my original situation.   Everything back then was OK: I could SELECT from my SALES2 table, I could SELECT from my PROFITS view.  The DELETE went awry but not because of a rights problem.


TEST 2:  later, it was decided that user OE should no longer do datapump jobs, so

revoke imp_full_database ,exp_full_database from oe ;  -- with SYS


Connected

 

USER is "OE"

 

Error starting at line : 10 in command -

delete profits

Error at Command Line : 10 Column : 8

Error report -

SQL Error: ORA-00942: table or view does not exist

00942. 00000 -  "table or view does not exist"

*Cause:

*Action:

 

Rollback complete.

 

 

Error starting at line : 12 in command -

select count(*)  countProfits from profits

Error at Command Line : 12 Column : 36

Error report -

SQL Error: ORA-00942: table or view does not exist

00942. 00000 -  "table or view does not exist"

*Cause:

*Action:

 

Error starting at line : 13 in command -

select count(*) countSAles2  from sales2

Error at Command Line : 13 Column : 35

Error report -

SQL Error: ORA-00942: table or view does not exist

00942. 00000 -  "table or view does not exist"

*Cause:

*Action:

                        # of Privileges

---------------------------------------

                                     19

 

All of a sudden, I could not SELECT from my table nor from my view, much less DELETE from my view.  I got these ORA-00942 errors instead.


TEST 3: with exp_full_database only

grant exp_full_database  to oe ;  -- with SYS


Connected

 

USER is "OE"

 

Error starting at line : 6 in command -

delete profits

Error at Command Line : 6 Column : 8

Error report -

SQL Error: ORA-01031: insufficient privileges

01031. 00000 -  "insufficient privileges"

*Cause:    An attempt was made to perform a database operation without

           the necessary privileges.

*Action:   Ask your database administrator or designated security

           administrator to grant you the necessary privileges

 

Rollback complete.

 

                           COUNTPROFITS

---------------------------------------

                                 916039

 

                            COUNTSALES2

---------------------------------------

                               10107273

 

 

                        # of Privileges

---------------------------------------

                                     29


With exp_full_database only, the situation is close to the original one, except that I do not have the right to modify (DELETE) my view data, but I can SELECT from it.



TEST 4: with imp_full_database only

Connected

 

USER is "OE"

Error starting at line : 5 in command -

delete profits

Error at Command Line : 5 Column : 8

 

Error report -

SQL Error: ORA-01752: cannot delete from view without exactly one key-preserved table

01752. 00000 -  "cannot delete from view without exactly one key-preserved table"

*Cause:    The deleted table had

           - no key-preserved tables,

           - more than one key-preserved table, or

           - the key-preserved table was an unmerged view.

 

*Action:   Redefine the view or delete it from the underlying base tables.

 

Rollback complete.

 

                           COUNTPROFITS

---------------------------------------

                                 916039

 

                            COUNTSALES2

---------------------------------------

                               10107273

 

 

                        # of Privileges

---------------------------------------

                                     95

 

With imp_full_database only, the situation is  as the original one.  So, having gone full circle, we can safely say that what allowed us, in the original situation, to view and modify data from a view that did not belong to us, was imp_full_database (hence the title of this post).

You will have noticed that at the end of each test, there's the number of session privileges.  And the bigger that number, the more rights I had.  In fact, exp_full_database and imp_full_database are roles, which each contain a set of privileges.  As you can see in the enclosed pictures (taken from the DBA part of SQLDeveloper), imp_full_database has many more privileges  than exp_full_database.   And what I did not know was that one of those many privileges is the "SELECT ANY TABLE" privilege, which allows you to SELECT from a view!


The other day, a SELECT COUNT(*) of mine did a full-table scan when I knew it could use an index.  This is a big (2.5M rows), partitioned table.

BLOCKS TABLE_NAME NUM_ROWS AVG_ROW_LEN

---------- ------------------------------ ---------- ---------------------------------------

     49932 BIGTABLE                    2484741                                     13

A closer look revealed that the SELECT went parallel:

4B1.bmp

Indeed, the table had a parallel degree of 12.  Oracle figured a parallelized full-table scan would be more efficient than using the PK index.

Indeed, I altered the table to a parallel degree of 1, and now Oracle chose the PK index:

------------------------------------------------------------------| Id  | Operation              | Name      | Rows | Cost (%CPU)| Time     | Pstart| Pstop |

--------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT       |           | 1 |  2035   (2)| 00:00:25 |       | |

|   1 | SORT AGGREGATE        |           | 1 |            |          |       | |

|   2 | PARTITION LIST ALL   |           | 2484K|  2035   (2)| 00:00:25 |     1 | 35 |

|   3 |    INDEX FAST FULL SCAN| MYPKINDEX |  2484K| 2035   (2)| 00:00:25 |     1 | 35 |

 

At some parallel degree, Oracle figures an FTS (Full-Table Scan, which is an operation that can be parallelized and take advantage of db_file_multiblock_read_count) is more efficient than using the PK index.  Let’s see that in action.

 

Degree 11:

----------------------------------------------------------------------------------------------------

| Id  | Operation              | Name           | Rows | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |

------------------------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT       |                | 1 |  1369   (1)| 00:00:17 |       | |        |      |            |

|   1 | SORT AGGREGATE        |                |     1 |            |          |       | |        |      |            |

|   2 | PX COORDINATOR       |                | |            |          |       | |        |      |            |

|   3 | PX SEND QC (RANDOM) | :TQ10000 |     1 |            |          |       | |  Q1,00 | P->S | QC (RAND)  |

|   4 | SORT AGGREGATE     |                |     1 |            |          |       | |  Q1,00 | PCWP |            |

|   5 | PX BLOCK ITERATOR | |  2484K|  1369 (1)| 00:00:17 |     1 |    35 | Q1,00 | PCWC |            |

|   6 | TABLE ACCESS FULL| BIGTABLE | 2484K|  1369   (1)| 00:00:17 |     1 | 35 |  Q1,00 | PCWP |            |

 

Degree 10: same as above (FTS)

Degree 9: same as above (FTS)

Degree 8: same as above (FTS)

Degree 7:

--------------------------------------------------------------------------------------------

| Id  | Operation              | Name      | Rows | Cost (%CPU)| Time     | Pstart| Pstop |

--------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT       |           | 1 |  2035   (2)| 00:00:25 |       | |

|   1 | SORT AGGREGATE        |           | 1 |            |          |       | |

|   2 | PARTITION LIST ALL   |           | 2484K|  2035   (2)| 00:00:25 |     1 | 35 |

|   3 | INDEX FAST FULL SCAN| MYPKINDEX | 2484K|  2035   (2)| 00:00:25 |     1 | 35 |

 

Degree 6: same as above (PK)

Degree 2: same as above (PK)

So, on that 11.2.0.3 database (blocksize 8192), with db_file_multiblock_read_count at 45, Oracle considers that at degree 7, parallelism will not be as efficient as an index fast full scan.  While at degree 8, it considers the full table scan (parallelized of course) the most efficient.

 

I tried the same thing on a 12.1.0.2 database (blocksize 8192).  This time the BIGTABLE is 10M-record strong, and db_file_multiblock_read_count is at 66.

BLOCKS    TABLE_NAME    NUM_ROWS AVG_ROW_LEN
56107    BIGTABLE 10107273    35

 

An added boon of version 12c is that EXPLAIN PLAN mentions the degree of parallelism:

--------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name     | Rows | Cost (%CPU)| Time     |    TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |          | 1 |  5643   (1)| 00:00:01 |        | |            |
|   1 | SORT AGGREGATE        |          |     1 |            |          |        | |            |
|   2 | PX COORDINATOR       |          |       |            |          |        | |            |
|   3 | PX SEND QC (RANDOM) | :TQ10000 | 1 |            |          | Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE     | |     1 |            |          | Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR |          | 10M|  5643   (1)| 00:00:01 |  Q1,00 | PCWC |            |
|   6 |       TABLE ACCESS FULL| BIGTABLE    | 10M|  5643   (1)| 00:00:01 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------------------
Note
-----
- Degree of Parallelism is 3 because of table property

Here are a few screenshots of my various result tabs in SQLDeveloper, showing the results of my various runs:

4B2.bmp

”d” stands for degree of parallelism

“c” stands for cost

“PK” means that the execution plan used the Primary Key index

“FTS” means that the execution plan used a parallelized full table scan

 

Not only does Oracle, at some point, switch from an index fast full scan to a full table scan, but the cost also decreases as the degree of parallelism increases.  Here the watershed is at degree 3 (full table scan), while with degree 2, Oracle chooses to use the index.

4B3.bmp

Oh, in case you were wondering how I carried out my various tests, here is my code:

set autotrace off

alter table BIGTABLE PARALLEL 2 ; -- change to the desired degree of parallelism here

select degree,table_name,owner from dba_tables where table_name like 'BIGTABLE%' ;

set autotrace on explain only

set timing on

select count(*) from BIGTABLE ;

set timing off

 

Then, using SQLDeveloper’s pin button, I get my results into different tabs, which I can rename with the relevant information:

4B4.bmp

It’s happened a couple times, so I will document it here for the benefit of the dear DBAs out there.

An impdp job, importing about 20 GBs, has been running for 24 hours.  Whatever your hardware, that’s too much time.  No error message in the impdp log (which is stuck on SCHEMA_EXPORT/TABLE/INDEX/INDEX), and there is still plenty of free space in the tablespaces being filled by the import.  What gives?

 

So no error in the impdp log, no error in the alert.log, tablespaces have free space, so where to find anymore clues? Let’s find the impdp Oracle sessions and find out what they are up to.

First, in order to identify the Oracle sessions created by datapump, let’s run

SELECT TO_CHAR(sysdate,'DD MON, yyyy hh24:mi:ss') now, STATE,DATAPUMP_SESSIONS,JOB_NAME,OWNER_NAME,OPERATION FROM DBA_DATAPUMP_JOBS WHERE STATE='EXECUTING' ;

 

NOW STATE DATAPUMP_SESSIONS JOB_NAME                       OWNER_NAME                     OPERATION         

-------------------------------------- ------------------------------ --------------------------------------- ------------------------------ ------------------------------

15 JUL., 2015 13:37:50 EXECUTING 3 PRODINTOBENCH IMPDPuser IMPORT

 

The DATAPUMP_SESSIONS column tells us how many sessions Oracle had to create for that datapump job (if you parallelize your import, that number will increase accordingly).

 

Then let’s SELECT OWNER_NAME , JOB_NAME ,SESSION_TYPE from DBA_DATAPUMP_SESSIONS

 

OWNER_NAME     JOB_NAME             SESSION_TYPE

-------------- -------------------- --------------

IMPDPuser     PRODINTOBENCH            DBMS_DATAPUMP

IMPDPuser     PRODINTOBENCH            MASTER       

IMPDPuser     PRODINTOBENCH            WORKER

 

When no datapump jobs are running, a SELECT FROM DBA_DATAPUMP_SESSIONS will return no lines.  When one is running, you will get a minimum of 3 lines (the 3 lines above). There can be more “WORKERS” if you parallelize your datapump job (with DEGREE=8, for example, you will have 8 “workers”).  DBA_DATAPUMP_SESSIONS has a SADDR column, that you can link to the same column in V$SESSION -- goody!

 

SELECT V.STATUS, V.SID,V.SERIAL#,IO.BLOCK_CHANGES,EVENT, MODULE FROM V$SESS_IO IO,V$SESSION V WHERE IO.SID=V.SID AND V.SADDR IN (SELECT SADDR FROM DBA_DATAPUMP_SESSIONS) ORDER BY SID;

 

STATUS

SID

SERIAL#

BLOCK
CHANGES

EVENT

MODULE

ACTIVE

197

337

5396

wait for unread message on broadcast channel

udi@remserv (TNS V1-V3)

ACTIVE

579

101

8253

wait for unread message on broadcast channel

Data Pump Master

ACTIVE

768

22709

2240201

direct path write temp

Data Pump Worker

I add the BLOCK_CHANGES column from the V$SESS_IO view to see which session is doing the IOs (which one is doing the real work).  If I re-run my SELECT periodically, I see BLOCK_CHANGES increase, for one session:

STATUS

SID

SERIAL#

BLOCK
CHANGES

EVENT

MODULE

ACTIVE

197

337

5396

wait for unread message on broadcast channel

udi@remserv (TNS V1-V3)

ACTIVE

579

101

8253

wait for unread message on broadcast channel

Data Pump Master

ACTIVE

768

22709

2242588

direct path read temp

Data Pump Worker

 

ACTIVE

197

337

5396

wait for unread message on broadcast channel

udi@remserv (TNS V1-V3)

ACTIVE

579

101

8253

wait for unread message on broadcast channel

Data Pump Master

ACTIVE

768

22709

2263775

direct path read

Data Pump Worker

 

As long as one session is doing the dirty work, my datapump job is progressing. The other 2 sessions (“Data Pump Master “ and “udi”) spend most of their time on the “wait for unread message on broadcast channel” event.

So in the examples, above, the impdp job is not stalled.  But that SELECT is the key to identifying the roadblock stalling my job.

 

SELECT v.status, v.SID,v.serial#,io.block_changes,event, module,v.sql_id FROM v$sess_io io,v$session v WHERE io.SID=v.SID AND v.saddr IN (SELECT saddr FROM dba_datapump_sessions) ORDER BY io.BLOCK_CHANGES

 

STATUS

SID

SERIAL#

BLOCK
CHANGES

EVENT

MODULE

ACTIVE

197

337

5396

wait for unread message on broadcast channel

udi@remserv(TNS V1-V3)

ACTIVE

579

101

8253

wait for unread message on broadcast channel

Data Pump Master

ACTIVE

768

22709

2206601

statement suspended, wait error to be cleared

Data Pump Worker

 

Now we’ve got something: “statement suspended, wait error to be cleared” means that session is stuck on some issue and is resumable.  It’s the same event as when a tablespace has been maxed out.  But as stated previously, my tablespaces are in fine shape.  What else can prevent my import from progressing?  The storage!

 

In my case, it turns out that one of the filesystems hosting my index files filled up. Oracle can tell when its tablespaces are filling up, but not that when the FS is filled up, hence the absence of error everywhere.

 

/dev/dbserv_bench     76.00      0.00 100%

 

What is awesome about resumable errors, and AUTOEXTEND tablespaces, is that once the sysadmins add more storage, my impdp job resumes where it left off with no human interaction:

 

/dev/dbserv_bench     84.00      7.03 92%

 

STATUS

SID

SERIAL#

BLOCK
CHANGES

EVENT

MODULE

ACTIVE

197

337

5396

wait for unread message on broadcast channel

udi@remserv (TNS V1-V3)

ACTIVE

579

101

8253

wait for unread message on broadcast channel

Data Pump Master

ACTIVE

768

22709

2244006

direct path read

Data Pump Worker

 

SCHEMA_EXPORT/TABLE/INDEX/INDEX

SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Job " IMPDPuser "." PRODINTOBENCH" completed with 1 error at 15:01:56.

 

 

Appendix A:  here’s an example of a PARALLEL=4 expdp in progress:

STATUS          SID BLOCK_CHANGES EVENT MODULE

-------- ---------- ------------- -------------------------------------------------- ----------------------

ACTIVE          200           595 Datapump dump file I/O                             Data Pump Worker

ACTIVE            7           683 direct path read                                   Data Pump Worker

ACTIVE          398           700 direct path read                                   Data Pump Worker

ACTIVE          774          3299 wait for unread message on broadcast channel       Data Pump Master

ACTIVE          972          5558 wait for unread message on broadcast channel       ude@remserv (TNS V1-V3)

ACTIVE         1176         34381 direct path read                                   Data Pump Worker

 

17-07-15 18:38:42 MYADMIN > /

 

STATUS          SID BLOCK_CHANGES EVENT MODULE

-------- ---------- ------------- -------------------------------------------------- ----------------------

ACTIVE          200           714 direct path read                                   Data Pump Worker

ACTIVE            7           719 Datapump dump file I/O                             Data Pump Worker

ACTIVE          398           736 direct path read                                   Data Pump Worker

ACTIVE          774          3299 wait for unread message on broadcast channel       Data Pump Master

ACTIVE          972          5558 wait for unread message on broadcast channel       ude@remserv (TNS V1-V3)

ACTIVE         1176         34417 direct path read                                   Data Pump Worker

 

17-07-15 18:40:01 MYADMIN > /

 

STATUS          SID BLOCK_CHANGES EVENT MODULE

-------- ---------- ------------- -------------------------------------------------- ----------------------

ACTIVE          398          1482 direct path read                                   Data Pump Worker

ACTIVE            7          1588 direct path read                                   Data Pump Worker

ACTIVE          200          1597 direct path read                                   Data Pump Worker

ACTIVE          774          3299 wait for unread message on broadcast channel       Data Pump Master

ACTIVE          972 5558 wait for unread message on broadcast channel       ude@remserv (TNS V1-V3)

ACTIVE         1176         35391 direct path read                                   Data Pump Worker

 

 

Appendix B:  here’s an example of a PARALLEL=4 impdp in progress.  This one runs into a typical “full tablespace” issue:

STATUS   THESESSION BLOCK_CHANGES MODULE                       EVENT                                         

-------- ---------- ------------- ---------------------------- -----------------------------------------------

ACTIVE   598,40311          5270 udi@dbserv (TNS V1-V3) wait for unread message on broadcast channel  

ACTIVE   582,8077          15080 Data Pump Worker             wait for unread message on broadcast channel  

ACTIVE   634,50998         18786 Data Pump Master             wait for unread message on broadcast channel  

ACTIVE   599,14675         22362 Data Pump Worker             wait for unread message on broadcast channel  

ACTIVE   607,49998        100170 Data Pump Worker             wait for unread message on broadcast channel  

ACTIVE   631,24303      10231592 Data Pump Worker             statement suspended, wait error to be cleared 

In that case, a telling error appears in your impdp log:

ORA-39171: Job is experiencing a resumable wait.

ORA-01691: unable to extend lob segment QA.SYS_LOB0000374957C00039$$ by 16 in tablespace QADATA

Worker             statement suspended, wait error to be cleared.bmp

After increasing the size of the said tablespace, the import automatically resumes:

STATUS   THESESSION BLOCK_CHANGES MODULE                       EVENT                                       

-------- ---------- -------------- ---------------------------- ---------------------------------------------

ACTIVE   598,40311           5270 udi@dbserv (TNS V1-V3) wait for unread message on broadcast channel 

ACTIVE   582,8077           15080 Data Pump Worker             wait for unread message on broadcast channel 

ACTIVE   634,50998          18786 Data Pump Master             wait for unread message on broadcast channel 

ACTIVE   599,14675          22362 Data Pump Worker             wait for unread message on broadcast channel 

ACTIVE   607,49998         100170 Data Pump Worker             wait for unread message on broadcast channel 

ACTIVE   631,24303       10855394 Data Pump Worker             i/o slave wait                               

 

STATUS   THESESSION BLOCK_CHANGES MODULE                       EVENT                                       

-------- ---------- -------------- ---------------------------- ---------------------------------------------

ACTIVE   598,40311           5270 udi@dbserv (TNS V1-V3) wait for unread message on broadcast channel 

ACTIVE   582,8077 15080 Data Pump Worker             wait for unread message on broadcast channel 

ACTIVE   634,50998          18792 Data Pump Master             wait for unread message on broadcast channel 

ACTIVE   599,14675          22362 Data Pump Worker             wait for unread message on broadcast channel 

ACTIVE   607,49998         238646 Data Pump Worker             i/o slave wait                               

ACTIVE   631,24303       11832492 Data Pump Worker             wait for unread message on broadcast channel 

Here are 3 occurrences of the same SELECT DISTINCT, with durations that range from 15 seconds to 966 seconds!

 

SQL_ID

Cost

Duration (s)

End Time

SQL TEXT

1hdzqy0f9ukcc

2046761

21

24-06-15 02H

SELECT DISTINCT IRI, CT FROM BIGTABLETHATALWAYSGROWS WHERE INI IS NULL AND CT = 012

35vbdxu0xucjh

2046761

15

24-06-15 02H

SELECT DISTINCT IRI, CT FROM BIGTABLETHATALWAYSGROWS WHERE INI IS NULL AND CT = 034

aa316uybjk84s

2046761

966,14

24-06-15 03H

SELECT DISTINCT IRI, CT FROM BIGTABLETHATALWAYSGROWS WHERE INI IS NULL AND CT = 027

 

This SELECT DISTINCT selects the records to be modified by a nightly batch job, and runs for too long.  This partitioned table has 357M rows and weighs over 50Go.  I am tasked to optimize this SELECT DISTINCT.

 

First and foremost, a detail in the SQL code screams for attention – did you spot it?  The Explain Plan mentions it:

SQL> explain plan for SELECT DISTINCT IRI, CT FROM BIGTABLETHATALWAYSGROWS WHERE INI IS NULL AND CT = 040;

Explained.

SQL> SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

PLAN_TABLE_OUTPUT

Plan hash value: 2894093667

--------------------------------------------------------------------------------| Id  | Operation           | Name           | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |

------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT | |     1 |    20 |  2036K  (1)| 06:47:17 |       | |

|   1 | PARTITION LIST ALL | |     1 |    20 |  2036K  (1)| 06:47:17 |     1 |    35 |

|   2 | HASH UNIQUE | |     1 |    20 |  2036K  (1)| 06:47:17 | |       |

|*  3 |    TABLE ACCESS FULL| BIGTABLETHATALWAYSGROWS |     1 |    20 |  2036K  (1)| 06:47:17 |     1 |    35 |

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

3 - filter("INI" IS NULL AND TO_NUMBER("CT")=040)

 

Yes, an implicit conversion (040 being converted to a number, because of the lack of single quotes around it) will prevent the use of any index (except for the appropriate function-based index, which, of course, has not been created) because the CT column is VARCHAR.

 

So an extremely simple, yet highly effective, tweak is to add single quotes:

EXPLAIN PLAN FOR

SELECT DISTINCT IRI, CT FROM BIGTABLETHATALWAYSGROWS WHERE INI IS NULL AND CT = '040' ;

---------------------------------------------------------------------------------------------------------

| Id  | Operation              | Name           | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |                                                                                                                                                                                                  

---------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT       |                |   711 | 14220 | 42066   (1)| 00:08:25 |       | |

|   1 | HASH UNIQUE           |                |   711 | 14220 | 42066   (1)| 00:08:25 |       | |

|   2 |   PARTITION LIST SINGLE|                |  8496 | 165K| 42065   (1)| 00:08:25 |   KEY | KEY |

|*  3 |    TABLE ACCESS FULL   | BIGTABLETHATALWAYSGROWS |  8496 | 165K| 42065   (1)| 00:08:25 |    16 | 16 |

---------------------------------------------------------------------------------------------------------                                                                                                                                                                                                  

Predicate Information (identified by operation id):

---------------------------------------------------

   3 - filter("INI" IS NULL)

 

From a cost of over 2M, we’re down to 42K.  Good, but not good enough for that SELECT that will return only a fraction of that table’s records.  A Full Table Scan is probably overkill.  Let’s see which index will give us the best performance.

 

There are a few indexes on that big table.  Let’s tell the optimizer to use them with a hint:

EXPLAIN PLAN FOR

SELECT  /*+ INDEX */  DISTINCT…

No can do:

0 | SELECT STATEMENT       |                |     1 | 20 | 41718   (1)| 00:08:21 |       | |

|   1 |  HASH UNIQUE           |                |     1 | 20 | 41718   (1)| 00:08:21 |       | |

|   2 |   PARTITION LIST SINGLE|                |     5 | 100 | 41717   (1)| 00:08:21 |   KEY | KEY |                                                                                                                                                                                                  

|*  3 |    TABLE ACCESS FULL   | BIGTABLETHATALWAYSGROWS |     5 |   100 | 41717 (1)| 00:08:21 |    16 | 16

 

The CBO considers the full table scan more effective than using any of the existing indexes. I tested each of them and each time EXPLAIN PLAN gave me a cost higher than that of the full table scan.

 

So let’s try of few indexes of our own.

 

CREATE INDEX fd4_INI_IRI_CT ON BIGTABLETHATALWAYSGROWS (INI,IRI,CT) LOCAL;

CREATE INDEX fd3_IRI_INI_CT ON BIGTABLETHATALWAYSGROWS (IRI,INI,CT) LOCAL;

CREATE INDEX fd2_INI_CT ON BIGTABLETHATALWAYSGROWS (INI,CT) LOCAL;

CREATE INDEX fd1_CT_INI ON BIGTABLETHATALWAYSGROWS (CT,INI) LOCAL;

 

With index #1 (index on all the columns mentioned in the WHERE clause):

----------------------------------------------------------------------------------------------------------------------

| Id  | Operation                           | Name           | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |

----------------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                    |                |     1 | 20 |     3  (34)| 00:00:01 |       | |

|   1 |  HASH UNIQUE                        |                |     1 | 20 |     3  (34)| 00:00:01 |       | |

|   2 |   PARTITION LIST SINGLE             |                |     5 | 100 |     2   (0)| 00:00:01 |   KEY | KEY |                                                                                                                                                                                     

|   3 |    TABLE ACCESS BY LOCAL INDEX ROWID| BIGTABLETHATALWAYSGROWS |     5 |   100 | 2   (0)| 00:00:01 |    16 | 16 |

|*  4 | INDEX RANGE SCAN                | FD1_CT_INI     | 1 |       |     1 (0)| 00:00:01 |    16 |    16 |

Index Range Scan, good, but the table still has to be accessed because a column of the SELECT is not in that index.  Wow! Cost of 3.  Can’t get it any better, right?  Let’s find out. 

 

With index #2 (index on all the columns mentioned in the WHERE clause, but the order of columns is reversed):

----------------------------------------------------------------------------------------------------------------------

| Id  | Operation                           | Name           | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |                                                                                                                                                                                     

----------------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                    |                |     1 | 20 |     3 (34)| 00:00:01 |       |       |

|   1 |  HASH UNIQUE                        |                |     1 | 20 |     3  (34)| 00:00:01 |       | |

|   2 |   PARTITION LIST SINGLE             |                |     5 | 100 |     2   (0)| 00:00:01 |   KEY | KEY |

|   3 |    TABLE ACCESS BY LOCAL INDEX ROWID| BIGTABLETHATALWAYSGROWS |     5 |   100 | 2   (0)| 00:00:01 |    16 | 16 |

|*  4 |     INDEX RANGE SCAN                | FD2_INI_CT     | 1 |       |     1 (0)| 00:00:01 |    16 |    16 |

 

No difference, so no better.

 

With index # 3 (index on all the columns mentioned in that SELECT, the least selective last):

---------------------------------------------------------------------------------------------------------                                                                                                                                                                                                  

| Id  | Operation              | Name           | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |                                                                                                                                                                                                  

---------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT       |                |     1 | 20 |    27   (4)| 00:00:01 |       | |

|   1 |  SORT UNIQUE NOSORT    |                |     1 | 20 |    27   (4)| 00:00:01 |       | |

|   2 |   PARTITION LIST SINGLE|                |     5 | 100 |    26   (0)| 00:00:01 |   KEY | KEY |

|*  3 |    INDEX SKIP SCAN     | FD3_IRI_INI_CT |     5 | 100 |    26   (0)| 00:00:01 |    16 | 16 |

Interesting, because since all the columns mentioned in our SELECT are in that index, no need to access the table.  Still the cost is higher than our previous attempts, which did include a table access by rowid.

So since that INDEX SKIP SCAN seems too expensive, let’s shuffle the order of columns in our index …

 

With index # 4 (index on all the columns mentioned in that SELECT, the least selective last, and the other 2 columns swapped):

---------------------------------------------------------------------------------------------------------

| Id  | Operation              | Name           | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |

---------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT       |                |     1 | 20 |     3  (34)| 00:00:01 |       | |

|   1 |  HASH UNIQUE           |                |     1 | 20 |     3  (34)| 00:00:01 |       | |

|   2 |   PARTITION LIST SINGLE|                |     5 | 100 |     2   (0)| 00:00:01 |   KEY | KEY |                                                                                                                                                                                                  

|*  3 |    INDEX RANGE SCAN    | FD4_INI_IRI_CT |     5 | 100 |     2   (0)| 00:00:01 |    16 | 16 |

---------------------------------------------------------------------------------------------------------  

Hmmh, cost of 3, again.  And with no table access.

 

So, we have 3 possibilities with a CBO cost of 3 (indexes fd1, fd2, and fd4).  Which one really is the best?  I’d rather go for the one with the least steps (there are 5 steps in plans 1 and 2, 4 steps in plan 4).  If we ask the CBO to decide, which will it be?

 

EXPLAIN PLAN FOR

SELECT  DISTINCT IRI…

 

----------------------------------------------------------------------------------------------------------------------

| Id  | Operation                           | Name           | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |

----------------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                    |                |     1 | 20 |     3  (34)| 00:00:01 |       | |

|   1 |  HASH UNIQUE                        |                |     1 | 20 |     3  (34)| 00:00:01 |       | |

|   2 |   PARTITION LIST SINGLE             |                |     5 | 100 |     2   (0)| 00:00:01 |   KEY | KEY |                                                                                                                                                                                     

|   3 |    TABLE ACCESS BY LOCAL INDEX ROWID| BIGTABLETHATALWAYSGROWS |     5 |   100 | 2   (0)| 00:00:01 |    16 | 16 |

|*  4 | INDEX RANGE SCAN                | FD1_CT_INI     | 1 |       |     1 (0)| 00:00:01 |    16 |    16 |

 

Index #1 seems to be the CBO’s favorite.   Let’s double-check with different values for our bind variable (big partitions, small partitions), and yes, the CBO constantly chooses index #1.


To keep in mind:

  • let's be wary of literals and implicit conversions
  • partitioned (LOCAL) indexes on partitioned tables are a great way to access data when a sufficiently small subset of data is requested.

I am listing the differences as they appear from top to bottom of an AWR report.  The differences are of 3 kinds:

  1. difference in location in the report
  2. new information
  3. information gone


In 11.2.0.3, the "Cache Sizes" lines appear first:

Cache Sizes

 

Begin

End

 

 

Buffer Cache:

304M

304M

Std Block Size:

8K

Shared Pool Size:

608M

608M

Log Buffer:

24,588K

While in 11.2.0.3, you find them further down (after "Memory Statistics).

 

"Load Profile" has 15 lines in 11.2.0.3 and 19 lines in 11.2.0.4, because there are 5 new lines in 11.2.0.4:

SQL Work Area (MB):

Read IO requests

Read IO (MB)

Write IO requests

Write IO (MB)

And W/A MB processed  disappeared in 11.2.0.4.

 

"Shared Pool Statistics" changed location: it is between "Instance Efficiency Percentages" and "Top 5 Timed Foreground Events" in 11.2.0.3, while in 11.2.0.4, it has moved to the bottom of the "Report Summary", right before the "Main Report" section.


The "Top 5 Timed Foreground Events" table of 11.2.0.3 has been replaced with 2 tables:

 

« Top 10 Foreground Events by Total Wait Time”:

Event

Waits

Total Wait Time (sec)

Wait Avg(ms)

% DB time

Wait Class

db file sequential read

178,619

281,8

2

41.8

User I/O

db file scattered read

68,622

168,4

2

25.0

User I/O

DB CPU

 

109,4

 

16.2

 

direct path read

3,885

16,1

4

2.4

User I/O

log file switch (checkpoint incomplete)

33

14,2

430

2.1

Configuration

log file switch completion

147

4,2

29

.6

Configuration

read by other session

89

1,5

17

.2

User I/O

cursor: pin S wait on X

37

1,3

35

.2

Concurrency

db file parallel read

26

,8

31

.1

User I/O

log file sync

187

,7

4

.1

Commit

 

And “Wait Classes by Total Wait Time” :

Wait Class

Waits

Total Wait Time (sec)

Avg Wait (ms)

% DB time

Avg Active Sessions

User I/O

254,484

488

2

72.4

0.1

DB CPU

 

109

 

16.2

0.0

System I/O

43,396

49

1

7.2

0.0

Configuration

184

19

102

2.8

0.0

Concurrency

361

2

6

.3

0.0

Commit

189

1

4

.1

0.0

Application

25

0

14

.1

0.0

Network

27,064

0

0

.1

0.0

Other

344

0

1

.0

0.0

Scheduler

1

0

3

.0

0.0

 

After the “Host CPU” and “Instance CPU” sections, which are identical in both versions, we have in 11.2.0.4  a new section, called "IO Profile":

IO Profile

 

Read+Write Per Second

Read per Second

Write Per Second

Total Requests:

156.3

86.2

70.1

Database Requests:

142.7

76.3

66.4

Optimized Requests:

0.0

0.0

0.0

Redo Requests:

1.5

0.0

1.5

Total (MB):

3.3

2.11.2

Database (MB):

2.6

1.90.7

Optimized Total (MB):

0.0

0.00.0

Redo (MB):

0.5

0.00.5

Database (blocks):

334.2

245.2

89.0

Via Buffer Cache (blocks):

290.1

201.5

88.6

Direct (blocks):

44.1

43.7

0.4

 

For the "Time Model Statistics" table, its number of lines varies, but I cannot determine if it varies because of the change from 11.2.0.3 to 11.2.0.4 or because of the activity recorded by each AWR report.

 

The whole SQL section seems to me identical from one version to the other.

 

Then the " Instance Activity Statistics" has been revamped.  In 11.2.0.3, we have:

While in 11.2.0.4 we have:

This new "Key Instance Activity Stats" section of 11.2.0.4 is a selection of statistics that are key according to Oracle:

Statistic

Total

per Second

per Trans

db block changes

11,620,235

3,225.92

12,882.74

execute count

42,189

11.71

46.77

logons cumulative

100

0.03

0.11

opened cursors cumulative

28,169

7.82

31.23

parse count (total)

14,413

4.00

15.98

parse time elapsed

2,316

0.64

2.57

physical reads

883,276

245.21

979.24

physical writes

320,511

88.98

355.33

redo size

1,847,700,304

512,943.60

2,048,448.23

session cursor cache hits

23,893

6.63

26.49

session logical reads

24,834,290

6,894.29

27,532.47

user calls

28,797

7.99

31.93

user commits

902

0.25

1.00

user rollbacks

0

0.00

0.00

Workarea executions - onepass

0

0.00

0.00

Workarea executions - optimal

8,125

2.26

9.01

 

 

The "Tablespace IO Stats"  table in 11.2.0.4 has 2 extra columns:

 

Tablespace

Reads

Av Rds/s

Av Rd(ms)

Av Blks/Rd

1-bk Rds/s

Av 1-bk Rd(ms)

Writes

Writes avg/s

Buffer Waits

Av Buf Wt(ms)

IxxxxT

137,197

38

1.91

4.43

54,507

19.31

1

15

25

27.20

DxxxxT

89,046

25

1.25

1

62,224

24.72

1

17

40

6.25

 

The "FileIO Stats"  table in 11.2.0.4 has 2 extra columns: "1-bk Rds/s" and "Av 1-bk Rd(ms)":

Tablespace

Filename

Reads

Av Rds/s

Av Rd(ms)

Av Blks/Rd

1-bk Rds/s

Av 1-bk Rd(ms)

Writes

Writes avg/s

Buffer Waits

Av Buf Wt(ms)

DxxxxR

/dxxxx1.dbf

181

0

2.65

1.34

0

2.47

14

0

0

0.00

DxxxxX

/dxxxx1.dbf

29

0

0.34

1.00

0

0.34

14

0

0

0.00

 

And that is all I could spot.  Do not hesitate to point out if something is missing or wrong.