When displaying execution plans on SQL*plus, you may notice an information message like so:

-------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                        | Rows  | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                             |     1 |   129 |     2   (0)|
|   1 |  NESTED LOOPS                |                             |     1 |   129 |     2   (0)|
|   2 |   TABLE ACCESS BY INDEX ROWID| ADVERT_MAPPING              |     1 |     8 |     1   (0)|
|*  3 |    INDEX UNIQUE SCAN         | ADVERT_MAPPING_UNIQUE_ALIAS |     1 |       |     1   (0)|
|*  4 |   TABLE ACCESS BY INDEX ROWID| ADVERT_PROFILE              |     1 |   121 |     1   (0)|
|*  5 |    INDEX RANGE SCAN          | ADVERT_PROFILE_FK_MAPPING   |  5549 |       |     1   (0)|
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("ADVERT_MAPPING"."ALIAS"=:V1)
   4 - filter("ADVERT_PROFILE"."ALIAS" IS NOT NULL AND
              ("ADVERT_PROFILE"."ADP_STATUS_CODE"=TO_NUMBER(:V4) OR
              "ADVERT_PROFILE"."ADP_STATUS_CODE"=TO_NUMBER(:V5)) AND
              "ADVERT_PROFILE"."USER_ACCOUNT_ID"=TO_NUMBER(:V2) AND
              "ADVERT_PROFILE"."ADP_TYPE_CODE"=TO_NUMBER(:V3))
   5 - access("ADVERT_MAPPING"."ADVERT_MAPPING_ID"="ADVERT_PROFILE"."ADVERT_MAPPING_ID")
Note
-----
   - 'PLAN_TABLE' is old version

To get rid of this message, and get a better view of your execution plans, simply execute the utlxplan script in the admin folder of the oracle home.

Now you get slightly better-looking execution plans (the Time column is nice, but wrong most of the time) and the message is gone.

Tags:

Forcing ASMM component to shrink

#oracle #database

Here's the way to force the shared pool to shrink dynamically. Documentation states that ASMM can only increase shared pool, and can't shrink. When the automatic shared memory management feature is enabled, the internal tuning algorithm tries to determine an optimal size for the shared pool based on the workload. It usually converges on this va... Read more

20 Mar 2019 - 2 minute read

How to extract Oracle user DDL

#database #oracle

In some cases, you need to duplicate an Oracle user, along with all it's privileges. Doing this manually can be tedious. Hopefully, we can use the data dictionary to extract the data we need and dump it into a SQL file to modify and replay. Here's how to do it: ```sql set head off set pages 0 set long 9999999 spool user_script.sql SELECT DBMS... Read more

20 Feb 2018 - 1 minute read

Restart a hung Oracle database

#oracle

On rare occasions, mostly during a high server load peak or a process failure, an Oracle instance may not accept any connection. Either from regular users as well as SYSDBA. This situation is called a **hung database** and must be quickly resolved as the database isn't accessible for your users anymore. As you cannot connect to the hung database... Read more

20 May 2017 - 1 minute read

Resolve PLAN_TABLE is old version error

Tags:

Social networks

You may also enjoy:

Forcing ASMM component to shrink

How to extract Oracle user DDL

Restart a hung Oracle database