Performance tuning in 11g R2 SE

[patrick196901]

Hi Tim,

I am writing to you today as I have a little issue. At the present time we are on oracle 10g R2 EE and so when I perform SQL tuning I use the packs we are licensed for, SQL Tuning Adviser and the results are usually good.

But we are going to migrate our databases to 11G R2 Standard Edition within a couple of months. The main reasons are financial reasons and the fact that we don't use any options of Oracle like partitioning and so on. Also in 11g R2 like in 10g R2 RAC is free, also another big reason.

My question is simple. Now I have the packs to help me tune some queries when they are performance issues, and I am very happy with them. But what could I use in 11g R2 SE that won't make us break our Oracle license and that will keep us in line with what we are licensed for (which means nothing)? I know that i can use hints in some queries, but what else? Are there some tools that can be used either in SE and EE and that could help in performance tuning?

Thanks in advance for your answer.

Regards

patrick

[Tim...]

Hi.

Tools that I think are pretty cool are:

GUI:

ASH Viewer: This is awesome. Just like the performance page in EM. No EE D&T license required provided to use the standard connection, that doesn't actually use ASH. it kinda fakes ASH.
http://sourceforge.net/projects/ashv/

Statspack: I guess you will be using this rather than AWR.
http://www.oracle-base.com/articles/8i/statspack-8i.php

SQL*Plus stuff:
MOATS: http://www.oracle-developer.net/utilities.php
Snapper: http://tech.e2sn.com/oracle-scripts-and ... on-snapper

Cheers

Tim...

[patrick196901]

Hi Tim,

Thanks very much for your answer. I will start looking at all the links and will also start using the different tools.

Best regards

Patrick

[Tim...]

[peterx]

hi Tim,

The ASH Viewer
http://sourceforge.net/projects/ashv/

only can install on the local oracle instance machine ?

thanks.

[Tim...]

Hi.

No. I run it on my PC at work. You just put connection details to server. Remember to run a standard connection if you don't want to break your Oracle license agreement.

Cheers

Tim...

[peterx]

hi Tim,

thanks, i am trying.

In my linux box, i am running "swingbench" at before (so the java and oracle client is working).

I try to run the ASHViewer as:

Code: Select all

[oracle@mylinux ashv-3.4-bin]$ ./run.sh


The GUI can popup and enter the connection details, when I try to make connection, error prompt:

Code: Select all

java.sql.SQLException: Can't find class for driver: oracle.jdbc.pool.OracleDataSource


Do you know why?


I try to fix this by changed:

Code: Select all


[oracle@mylinux ashv-3.4-bin]$ cat run.sh
#!/bin/sh
# ----------------------------------------------------------------------------
# Licensed to the GNU GENERAL PUBLIC LICENSE Version 3
# ----------------------------------------------------------------------------

# ----------------------------------------------------------------------------
# ASH Viewer start up batch script
#
# Required ENV vars:
# JAVA_HOME - location of a JDK home dir
#

# export JAVA_HOME=/usr/bin/java
export JAVA_HOME=/usr/local/java

export JAVA_EXE=$JAVA_HOME/bin/java

export ORACLE_HOME=/home/oracle
export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:$ORACLE_HOME/lib:$TTHOME/lib
export CLASSPATH=$JAVAHOME/lib/rt.jar:$JAVAHOME/lib/tools.jar:$SWINGHOME/lib/mytransactions.jar:${SWINGHOME}/lib/swingbench.jar:$ANTHOME/ant.jar:
export CLASSPATH=$CLASSPATH:/usr/lib/oracle/11.2/client64/lib/ojdbc5.jar
export CLASSPATH=$CLASSPATH:$TTHOME/lib/classes15.jar

$JAVA_EXE -Xmx128m -jar ASHV.jar
[oracle@mylinux ashv-3.4-bin]$




thanks.

[Tim...]

Hi.

I'm running it on a Windows desktop at work and it works fine without any of the additional environment variables. What happens if you remove the extra stuff you've added, start a new shell, and try again?

Cheers

Tim...