Slow DB query : Tuning required…

Have been struggling with a slow db query on our Oracle 9i repository.  There are product installations (redwood) and therefore the views provided by them cant be modified (read optimized).

We found out through tracing etc that the optimizer is behaving strangely. Its choosing an explain plan that is not completely optimal.  There’s something funny with statistics as well, since when I rewrote the query to use the rule based optimizer, the query was finishing much much faster. 

So, we had to do some experiments with Oracle traces as well.  Help on Oracle’s tracing mechanisms and ideas came from 

Introduction to Oracle Trace Utulity and Understanding The Fundamental Performance Equation « H.Tonguç YILMAZ Oracle Blog

Working together with our in-house DBA team helped a bit and I moved forward with some more experiments.  Here’s what helped me with Optimizer in Oracle (mostly CBO)

Oracle Optimizer: Moving to and working with CBO

Oracle Optimizer: Moving to and working with CBO – Part 2

Blogged with the Flock Browser

Oracle 10g Installation on Ubuntu

The link problem
The installation process stops midway, complaining about a certain make target having some error message.

Read this post for fixing that :
Here

Thought the post points to an older version of Ubuntu, it works on Gutsy just as good.

Things to do to make it work automatically on each reboot :

1. create a script in init.d, Sample Here

2. Create further links for that script in diff rc.d directories. Remember, the suggested commands in the page referred above are not correct completely. The path needs to be changed. So, check ur box, and then update the path.

3. In the script, there is no reference to the listener start/stop thing, u got to do that urself. So, add ${ORACLE_HOME}/bin/lsnrctl {start|stop} before the dbstart and dbshut respectively.

Here’s my copy for reference…

—————————-
#! /bin/sh -x
#
# Change the value of ORACLE_HOME to specify the correct Oracle home
# directory for your installation.

#ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
ORACLE_HOME=/media/sda8/oracle/product/10.1.0.1
#
# Change the value of ORACLE to the login name of the
# oracle owner at your site.
#
ORACLE=oracle

PATH=${PATH}:$ORACLE_HOME/bin
HOST=`hostname`
PLATFORM=`uname`
export ORACLE_HOME PATH

case $1 in
’start’)
${ORACLE_HOME}/bin/lsnrctl start
$ORACLE_HOME/bin/dbstart $ORACLE_HOME &
;;
’stop’)
${ORACLE_HOME}/bin/lsnrctl stop
$ORACLE_HOME/bin/dbshut &
#$ORACLE_HOME &
;;
*)
echo “usage: $0 {start|stop}”
exit
;;
esac
#
exit

—————————-

4. The files at $ORACLE_HOME/network/logs behave strangely when root tries to start a listener. So, mark them 777 and then the listener control from root works fine.

When i know more, I would post…

Oracle Installation on my Ubuntu

Well, downloading Oracle was the easier part… I guess…

I realized I was in trouble when I launched the runinstaller for Oracle the first time. Apparantly, Oracle linux distributions are meant to be used with Redhat Linux only. Then I had to go over to ubuntu forums to seek help.

Not only there, I had to go elsewhere as well, and finally found a page that really helped me fool the Oracle installation into believing that the system is actually a Redhat and not an Ubuntu. Funny thing…

I would post the link that helped me.. sure…

Then I could actually install the whole thing… following the fooling around instructions.. and some config settings.

Eventually, I managed to install the oracle 10g database. Run some test connections… some test sqls.. using command line.. as well as from Oracle Sql Developer (not a bad tool actually.. huh.. I have to say that since I have always used Toad otherwise).

Then come to blues about configuration. For some reason, oracle installation did not install any nice shortcuts for anything. None for Net Services configuration, for Listener configuration, nothing done for getting the database/listener registered as services.. or to start them up automatically at system boot up. All of that has to be done manually… And I had a hard time realizing that… That there is nothing given for that kind of stuff from Oracle installation. (Having a Windows Oracle Installation experience, this could be tricky..)

Well then, I dont know yet, whether Oracle just dont do it for any linux/unix installation, or it did not do that for mine, since it was being fooled into believing that it was a Redhat installation even though it was actually not.

I’d spend some time doing that myself…

And would come back with more info on to this page.

till then
raghav..

Gather statistics for a given oracle session

Gather statistics for a given oracle session

alter session set tracefile_identifier=''; -- should be replaced with your own identifying name...alter session set events='10046 trace name context forever, level 8';set timing on

Delete Statistics from a Schema

-- delete all statisticsDECLARECURSOR cOwner IS SELECT DISTINCT owner               FROM dba_tables                WHERE owner NOT IN ('SYS','SYSTEM','DBSNMP',);BEGIN -- analyze sOwner,compute FOR rOwner IN cOwner LOOP    DBMS_STATS.DELETE_SCHEMA_STATS    (ownname => rOwner.owner); END LOOP;END;/select 'END_DATE: '||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss') as end_date from dual;exit

Gather statistics for a schema

-- recreate all statisticsDECLARECURSOR cOwner IS SELECT DISTINCT owner               FROM dba_tables               WHERE owner NOT IN ('SYS','SYSTEM','DBSNMP',);BEGIN -- analyze sOwner,compute FOR rOwner IN cOwner LOOP    DBMS_STATS.GATHER_SCHEMA_STATS    (ownname => rOwner.owner,     estimate_percent => 99,     block_sample => FALSE,     method_opt => 'FOR ALL COLUMNS SIZE 150',     degree => NULL,     granularity => 'DEFAULT',     cascade => TRUE); END LOOP;END;/

select 'END_DATE: '||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss') as end_date from dual;exit