SQL Plan Baselines.

No Comments

SQL plan baselines are a new feature introduced in Version 11g.

SQL execution plans determine how a query is executed and the path that is taken to retrieve the data. Plans can be evolved or improved over executions and should only change when a better plan can be found.

When a query is hard parsed, the cost based optimizer creates an execution plan. In the absence of any other control, this plan would be used. However, circumstances can lead to different plans being generated with some being much more efficient than others.

The purpose of SQL plan baselines is to make sure that a less optimal execution plan is never used and that changes in the database such as statistics being gathered, upgrades or patches will not lead to slower plans being adopted.

In fact, when SQL plan baselines are in use, a new plan will not become ‘Accepted’ and become available for use untill it has been demonstrated to be more efficient than the old plan.

Here are a couple of good articles on SQL plan baselines and SQL plan management

Difference between SQL plan Baselines and SQL profiles.

SQL plan management

 

SQL plan baselines are stored in the SYSAUX tablespace and by default can use up to 10% of that tablespace. That figure can be manually set between 1% and 50% using the command – DBMS_SPM.CONFIGURE(‘space_budget_percent’,30); – to set it to use 30% of SYSAUX.

If the space limit is exceeded a message is generated into the alert log.

The length of time that unused plans are kept can be set by setting the retention period. This setting can be between 5 and 523 weeks.

Set it with the DBMS_SPM package: DBMS_SPM.CONFUGURE(‘plan_retention_weeks’,105)

The current settings for ‘space budget’ and ‘retention time’ can be can be seen in the DBA_SQL_MANAGEMENT_CONFIG view:

select * from DBA_SQL_MANAGEMENT_CONFIG;

So, the basic premise is that changes in the execution plan that are an improvement should be accepted but changes for the worse should be rejected.

It is important that untested plans are not used until proven to be an improvement. This is because it is preferable to use an accepted plan that is suboptimal rather than risk a dramatic drop in performance caused by a poor new plan.

Only plans that are set as ‘ACCEPTED’ within the plan baseline can be used.

The first time a piece of SQL is ran, if it has no prior plan in place, it will automatically have the first generated execution plan used and it will become ‘ACCEPTED’ in the baseline.

Additionally, if a change occurs that appears to invalidate all of the ‘ACCEPTED’ plans, the plan with the lowest cost will be used and set as ‘ACCEPTED’ in the same way as when no plans are included.

Subsequently, if a new plan is generated and appears to be an improvement it will be saved but cannot be used until it is accepted.

An automatic task needs to be created and ran that tests all of the candidate execution plans to verify which is the fastest and that one will be set as ‘ACCEPTED’ and will be used as the current baseline. Note that subsequently, there may be more than one ‘ACCEPTED’ plan, so being ‘ACCEPTED’ does not mean that a plan will be used.

To set the database to automatically capture plans you must change the parameter OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES to TRUE (it is FALSE by default).

For SQL Plan Baselines to be used, you also need to set the parameter: OPTIMIZER_USE_PLAN_BASELINES = true/false (default true)

You can also load plans manually if you want to force a particular plan into a baseline. This can be done from a number of sources, SQL Tuning Sets, AWR snapshots and the Cursor Cache. Manually added plans are set as ACCEPTED immediately.

To load a plan from a SQL set:

First load into SQL set with CREATE_SQLSET and LOAD_SQLSET then

load the plan from the SQL set:

DBMS_SPM.LOAD_PLANS_FROM_SQLSET

For example:

 

DECLARE

l_plans_loaded PLS_INTEGER;

BEGIN

l_plans_loaded := DBMS_SPM.load_plans_from_sqlset(

sqlset_name => ‘my_sqlset’);

END; /

 

Follow a similar procedure to load from the cursor cache:

 

DECLARE

l_plans_loaded PLS_INTEGER;

BEGIN

l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(

sql_id => ‘1fkh93md0802n’);

END; /

 

Evolving SQL Plan Baselines

As mentioned earlier, only ‘ACCEPTED’ plans can be uesd and apart from the first one, new plans are not accepted when they are created even if they appear to be more efficient.

There will be no change the the current accepted plans unless a new one has been tested and found to be an improvement. The process of testing plans is called ‘Evolving the Plan Baseline’.

There is a procedure to evolve baselines that must be ran. The normal method is to create a job which calls the procedure and have that job ran during off peak hours.

The procedure is: DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE

Fixed Plans

It is possible to set a plan as fixed (by setting the fixed attribute to yes). This essentially stops the baseline from evolving. A fixed plan takes precedence over a non fixed plan and no new plans are automatically added where a fixed plan exists, although you can manually add them, but for them to be used, you must manually alter them to FIXED

 

Displaying SQL Plan Baselines.

You can view SQL Plan Baselines with the DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE procedure

 

SELECT *

FROM table( dbms_xplan.display_sql_plan_baseline(

sql_handle = > ‘SYS_SQL_109c10fabeeec639’,

format = > ‘basic’)

);

 

A nice discussion with clear examples can be seen here

Categories: Oracle Basics, Oracle Tuning Tags: Tags: ,

Tablespace Encryption in Oracle 11g

No Comments

Tablespace encryption is a feature introduced to increase security of your database.

The idea is that by encrypting your tablespaces, you will stop anyone who gets a copy of your datafiles outside of your database from being able to access the data on them.

For example someone may be able to get copies of your datafiles from backup tapes etc and tablespace encryption would make it more difficult for them to access th edata contained.

Tablespace encryption must be set up when the tablespace is created. You can not encrypt an existing tablespace with the ALTER TABLE command.

The syntrax for creating an encrypted tablespace is:

CREATE TABLESPACE secure_ts DATAFILE ‘/ u02/ app/ oracle/ oradata/ orcl11g/ secure01. dbf’ SIZE 200M ENCRYPTION DEFAULT STORAGE( ENCRYPT);

So you simply add the ‘ENCRYPTION DEFAULT STORAGE( ENCRYPT)’ option to the end of your create tablespace command.

You need an Oracle Wallet to store the encryption key so this must be set up first.

When recovering a database with encrypted tablespaces you must open the Oracle wallet that contains the encryption key after database mount and before database open, so the recovery process can decrypt data blocks and redo.

Oracle Architecture Part 3

No Comments

[embedplusvideo height=”356″ width=”584″ standard=”http://www.youtube.com/v/L_2TfSHotsY?fs=1″ vars=”ytid=L_2TfSHotsY&width=584&height=356&start=&stop=&rs=w&hd=0&autoplay=0&react=1&chapters=&notes=” id=”ep6822″ /]

Watch the rest of this series:

Part 1

Part 2

Part 4

Notes:

Initilisation params for the Buffer cache:

  • db_cache_size
  • db_block_size
  • It is very helpfull if the buffer block size is a multiple of your operating system block size.

The Buffer cache is controled with a Least Recently Used (LRU) algorithm which determines what stays in and what gets removed from the SGA.

The goal being to have the data that is accesses most frequently kept in the SGA so that it can be accessed fast.

Shared pool.

The purpose of the Shared pool is to reduce parsing

The Shared pool consists of 2 parts:

  • Library Cache
    • text of sql statements
    • meta code – compiled version of above text
    • execution plan that the optimiser will use
    • Views:
      • v$sqlarea
      • v$sql
  • Dictionary Cache
    • names of objects that have been touched recently
    • privalages and roles – access information

The Shared Pool also usesa  LRU algorithm.

Initialization parameter – shared_pool_size controls size of shared pool.

 

Java Pool

  • Initilization parameter – java_pool_size
  • large pool – optional
  • if using RMAN configure a large large pool
  • shared server uses this

 

Watch Part 4

Oracle Architecture Part 4

No Comments

[embedplusvideo height=”328″ width=”584″ standard=”http://www.youtube.com/v/nNGLyWIPPTQ?fs=1″ vars=”ytid=nNGLyWIPPTQ&width=584&height=328&start=&stop=&rs=w&hd=0&autoplay=0&react=1&chapters=&notes=” id=”ep8443″ /]

Watch the earlier parts first:

Part 1

Part 2

Part 3

 

Notes:

Oracle Processes

 

Dbwr

Database Writer

  • Writes from the SGA to the data files
  • Does deffered writes
  • like a hotel room clerk
  • kicks blocks out to disk when new data needs free blocks

 

Lgwr

Log Writer

  • writes info to the current group of redo logs

Smon

System monitor

  • very important process
  • performs instance recovery
  • cleans up after sorts

 

Pmon

Process monitor

  • Monitors Processes
  • detects – deadlock detected for example

 

Ckpt

Checkpoint process

one big reason for checkpoints is that commonly accessed
data may never get written out to disk. A checkpoint forces
this to get written.

Oracle Instance

– is the SGA and background processes

 

Oracle database and the Oracle instance combined is called the Oracle Server.

You can have multiple instances associated with the same database, this is called RAC.

Dedicated server process connects through the PGA.

Categories: Oracle Basics, Video Tags: Tags: , , , , , ,

Oracle Architecture Part 2

No Comments

[embedplusvideo height=”328″ width=”584″ standard=”http://www.youtube.com/v/wmc9uxNW99E?fs=1″ vars=”ytid=wmc9uxNW99E&width=584&height=328&start=&stop=&rs=w&hd=0&autoplay=0&react=1&chapters=&notes=” id=”ep5503″ /]

Check out part 1 first.

Notes:

Online Redo Logs

  • Contains Redo and Undo info
  • DML
  • Inserts/Deletes
  • DDL – Create alter drop
  • Commits
  • Need at least 2 files/groups
  • Should be multiplexed each on different devices and controlers
  • When each file fils, next one is started and old one is archived (if in Archivelogmode)

An Oracle database is comprised of datafiles, control files
and redo logs.

The second part is the memory – the SGA (system global area
or shared global memory)

Database Buffer Cache

Comprised of Oracle data blocks

There are 4 main types of data blocks, which are the same as the 4 types of data mentioned earlier:

  • Data
  • Index
  • Rollback
  • Temporary

Part 3…

Categories: Oracle Basics, Video Tags: Tags: , , , , , ,