Skip navigation
1 2 3 4 Previous Next

Dear DBA

50 posts

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.