This forum is currently locked. You can't register or post questions at this time. (read more)

Performance tuning in 11g R2 SE

All posts relating to Oracle database administration.

Moderator: Tim...

patrick196901
Member
Posts: 13
Joined: Thu Oct 27, 2011 10:53 am

Performance tuning in 11g R2 SE

Postby patrick196901 » Mon Feb 04, 2013 2:57 pm

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...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Performance tuning in 11g R2 SE

Postby Tim... » Mon Feb 04, 2013 4:36 pm

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...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

patrick196901
Member
Posts: 13
Joined: Thu Oct 27, 2011 10:53 am

Re: Performance tuning in 11g R2 SE

Postby patrick196901 » Tue Feb 05, 2013 10:55 am

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...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Performance tuning in 11g R2 SE

Postby Tim... » Tue Feb 05, 2013 11:23 am

:)
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

peterx
Senior Member
Posts: 208
Joined: Wed Feb 09, 2011 7:07 am

Re: Performance tuning in 11g R2 SE

Postby peterx » Thu Feb 07, 2013 4:05 am

hi Tim,

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

only can install on the local oracle instance machine ?

thanks.

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Performance tuning in 11g R2 SE

Postby Tim... » Thu Feb 07, 2013 8:05 am

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...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

peterx
Senior Member
Posts: 208
Joined: Wed Feb 09, 2011 7:07 am

Re: Performance tuning in 11g R2 SE

Postby peterx » Thu Feb 07, 2013 12:27 pm

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...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Performance tuning in 11g R2 SE

Postby Tim... » Thu Feb 07, 2013 3:19 pm

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...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog


Return to “Oracle Database Administration”

Who is online

Users browsing this forum: No registered users and 7 guests

cron