How to tune Oracle Standard Edition without Option Packs


When you monitor a Oracle Standard Edition, you did not have a Tuning or Diagnostic Pack. This Feature must be disabled because they are not allowed in this database edition. There is a small articel about that on my blog how this can be done and can be read here. But however, the dba has also the need to find performance bottlenecks and tune them on a Oracle SE or SEO. I will try to show you same workarounds to get to the same goal.

First you should identified the level where your performance problem is present. pyramid

So how to find the right level where the problem exists? Oracle provides a great Tuning framework to achieve this.

oracle_tuning_framework

In Oracle Standard Edition you did not have this functionally but you can use STATSPACK, which also is very helpfull to identify problems in your database. STATSPACK must be installed on your database. The SQL Scripts you need are in place when you install your database home. Also Oracle update this scripts in every database version with new features. A description how to install and handle STATSPACK can be found here.

So what can we do with STATSPACK? The next Picture  show how many things can be coverd in Oracle SE.

awr_vs_statspack

So we have a good option for the AWR feature in Oracle EE, but we also need the ASH (Active Session History). Unfortunately, there is no „Out of the Box“ solution from Oracle to cover the ASH. But you can create it for your own or look in the internet for some projects. One of this projects is ASH Masters which can be found here. If you want to create a ASH Framework, you should first understand what the ASH is. The ASH is a repository of frequently taken snaphots (about 5 Minutes) from the v$sql and v$session view. The next show a small sample how to build a custom ASH and how you get in the position as DBA to know for what your session are waitng.

create table user_ash as select sysdate record_date,s.* from v$session s where rownum<1;

begin
  for i in 1..59 loop
    dbms_lock.sleep(1);
    execute immediate 'insert into user_ash (select sysdate,s.* from v$session s)';
    commit;
  end loop;
end;
/

So all you now must do is to use a simple SQL and see what your session are doing.

select   to_char(a.record_date,'yyyy-mm-dd hh24:mi') record_time
         ,decode(wait_class,'Idle','CPU',wait_class) wait_class
         ,count(*) sessions
from     user_ash a 
where    a.status='ACTIVE' 
         and a.type!='BACKGROUND'
group by to_char(a.record_date,'yyyy-mm-dd hh24:mi'),wait_class
order by 1,2 desc;

RECORD_TIME     WAIT_CLASS          SESSIONS
---------------- ------------------ ----------
2013-05-01 15:22 User I/O           2
2013-05-01 15:22 Network            1
2013-05-01 15:22 CPU                51
2013-05-01 15:23 CPU                8
2013-05-01 15:26 User I/O           1
2013-05-01 15:26 CPU                4
2013-05-01 15:27 User I/O           1
2013-05-01 15:27 CPU                55

We can see that our session often wating for CPU and the waits for IO and Network are very less. You can also customize the SQL to drill down more information. This is a very helpfull information if your user reporting a Problem in this period.

At this point we have coverd the AWR and the ASH, but what about the great feature Plan Management in Standard Edtion. I am afraid to say, but in SE you have less option. What you can do are Outlines (Yes the also work in 11G), but Outlines are not so flexible as SQL Profiles in EE also the good old SQL tuning with hints can help you here.

The comparsion is a summary of your options in Oracle Standard Edition or Standard Edtion One. Also this things here work for Oracle XE database.

comparsion_feature

Conclusion:
You must not always Upgrade your database to Enterprise Edition and buy the Option Packs. There are also some good options which can be done in Oracle Standard Edition. OK, some of the options are not so smart forward as they are in the Enterprise Edition but a good DBA should be also able to tune a database well without a option pack.

Also check out my blog post about monitoring comparsion here.

Feel free to leave me a comment

Advertisements

3 Gedanken zu “How to tune Oracle Standard Edition without Option Packs

  1. HI Peter,

    Thi is a nice artice, I have 2 questions for you :
    1 – why do you discard the background sessions in your select, what about : SYSTEM IO, BACKGROUND CPU ?
    2 – Do you have an idea of the performance impact involved by querying on v$session every second ?
    and on v$event_name and v$sql ?

    Thank you

    • Hello,
      In my experience, its often enough to analyze the Waits on Session Level to find problems in a database. Of course you can increase the „loglevel“ to also include the background processes that are involved.
      The performance needed depends on several factors (average number of active session, Real Application Clusters, etc.). The example in this article should illustrate the method, depending on the system it can also be 10 or 15 seconds or more (Enterprise Edtion ASH use 10 seconds samples from the In-Memory ASH). The good thing, with the same method the required performance can be measured. Theoretically, the sample time can also vary, depending on the workload of the system.

      regards
      Peter

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden / Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden / Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden / Ändern )

Google+ Foto

Du kommentierst mit Deinem Google+-Konto. Abmelden / Ändern )

Verbinde mit %s