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. 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.
So how to find the right level where the problem exists? Oracle provides a great Tuning framework to achieve this.
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.
So what can we do with STATSPACK? The next Picture show how many things can be coverd in Oracle SE.
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.
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.
