FAQ
Hi,

I don't have much onhand experience, I thought
list is the appropriate place to get information.
These are related to Oracle Performance Tuning and
Backup & Recovery.

I have a system of Oracle 8i under HP-Unix and with
ARCHIVELOG on. It has been running and stable since
last 2-3 years. This was a brief overview.

Que 1: Assuming that I am using Cost based Optimizer
(CBO); I have a query which is a join of three tables
out of which only one table have been analyzed and in
first case rest two were analyzed but long time back
and after that I have changed the structure of the
table and in second case the other two tables have not
been analyzed. Will Oracle use CBO ? I know it will
but then what will be the behavior of optimizer ? Will
it take the best guess for the other two tables in
both the cases and if yes then those best guesses will
be depending upon on what ?

Que 2: Suppose u have ran EXPLAIN PLAN for a
particular query at sqlplus prompt and there are
around 50 more users running EXPLAIN PLAN at the same
time from the UNIX bos itself. In this case the
OSUSER, TERMINAL, PROGRAM will be same (u know it
better). Now suppose I want to run TKPROF against the
trace file how will I identify which will be my trace
file in USER_DUMP_DEST directory ?

Que 3: Supposing if I have Oracle Report running on
Oracle. I passed 3 parameters first time it took me
around 3 seconds to execute and 4 parameters second
time its hogging the system taking almost 25 minutes
to run. There are no concatenated indexs on the
underlying tables. What might be the cause and how
would u go about identifying it ?

Que 4: A user starts complaining about a particular
part of an appln. What will be my first steps to
optimize it ?

Que 5: Let's say we have a situation where at point
"A" I took a complete db backup. Somewhere after point
"B" I lost an online redo log file. I recovered the db
using the bck taken at point "A" and archived redo log
files and started the new incarnation at point "B". I
took a complete logical backup at point "B" and taking
incremental backup after that. Now again at point "C"
I took the complete bck. If I have dropped a
tablespace somewhere in between point B and C which
are the possible ways to recover it and which one will
be the fastest ? (Of course if possible).

A---------------B---------------C---------------D

If I am not clear on any of the part please let me
know and I'll do the needful.

Thanks

Do You Yahoo!?
Yahoo! - Official partner of 2002 FIFA World Cup
http://fifaworldcup.yahoo.com

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: sam d
INET: sam_orafan_at_yahoo.com

Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).

Search Discussions

  • John.Hallas_at_vodafone.co.uk at Jun 11, 2002 at 4:53 pm
    Here are replies to a couple Sam,

    If optimiser mode is chose and any table in a query has stats then CBO
    will be used. If stats do not exist on any table Oracle has a hard-coded
    default value which is 100 if I recall a note on here correctly.
    If stats do exist but they are very old then they will still be used
    possibly giving a poor path.
    The best bet is to analyze the tables.

    50 user running explain plan at the same time? Some system
    However the best bet is to select something which you can identify with the
    trace. Something like
    'select this is sams session from dual;'
    Then do a grep on the udump directory searching for that phrase.

    Put a trace on that particular session and get the user to run the
    application and then you can view the trace files afterwards. (see my notes
    on www.hcresources.co.uk re tracing sessions)
    Check that performance is bad all the time rather than just at a peak time
    etc

    HTH

    John

    -----Original Message-----
    Sent: 11 June 2002 14:24
    To: Multiple recipients of list ORACLE-L

    Hi,

    I don't have much onhand experience, I thought
    list is the appropriate place to get information.
    These are related to Oracle Performance Tuning and
    Backup & Recovery.

    I have a system of Oracle 8i under HP-Unix and with
    ARCHIVELOG on. It has been running and stable since
    last 2-3 years. This was a brief overview.

    Que 1: Assuming that I am using Cost based Optimizer
    (CBO); I have a query which is a join of three tables
    out of which only one table have been analyzed and in
    first case rest two were analyzed but long time back
    and after that I have changed the structure of the
    table and in second case the other two tables have not
    been analyzed. Will Oracle use CBO ? I know it will
    but then what will be the behavior of optimizer ? Will
    it take the best guess for the other two tables in
    both the cases and if yes then those best guesses will
    be depending upon on what ?

    Que 2: Suppose u have ran EXPLAIN PLAN for a
    particular query at sqlplus prompt and there are
    around 50 more users running EXPLAIN PLAN at the same
    time from the UNIX bos itself. In this case the
    OSUSER, TERMINAL, PROGRAM will be same (u know it
    better). Now suppose I want to run TKPROF against the
    trace file how will I identify which will be my trace
    file in USER_DUMP_DEST directory ?

    Que 3: Supposing if I have Oracle Report running on
    Oracle. I passed 3 parameters first time it took me
    around 3 seconds to execute and 4 parameters second
    time its hogging the system taking almost 25 minutes
    to run. There are no concatenated indexs on the
    underlying tables. What might be the cause and how
    would u go about identifying it ?

    Que 4: A user starts complaining about a particular
    part of an appln. What will be my first steps to
    optimize it ?

    Que 5: Let's say we have a situation where at point
    "A" I took a complete db backup. Somewhere after point
    "B" I lost an online redo log file. I recovered the db
    using the bck taken at point "A" and archived redo log
    files and started the new incarnation at point "B". I
    took a complete logical backup at point "B" and taking
    incremental backup after that. Now again at point "C"
    I took the complete bck. If I have dropped a
    tablespace somewhere in between point B and C which
    are the possible ways to recover it and which one will
    be the fastest ? (Of course if possible).

    A---------------B---------------C---------------D

    If I am not clear on any of the part please let me
    know and I'll do the needful.

    Thanks

    Do You Yahoo!?
    Yahoo! - Official partner of 2002 FIFA World Cup
    http://fifaworldcup.yahoo.com
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: sam d
    INET: sam_orafan_at_yahoo.com

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------

    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author:
    INET: John.Hallas_at_vodafone.co.uk

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------

    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • DENNIS WILLIAMS at Jun 11, 2002 at 5:43 pm
    Sam

    Que 3 - I have no idea how the parameters you are passing affect the
    execution time. That one probably needs more explanation.

    Que 5 - You don't say what you are performing backups with. RMAN? System?
    Hot? I am going to assume that you are doing hot backups (archivelogging)
    with the O.S. (not RMAN) and logical backups with exports.

    The correct answer is that once you change the incarnation that you
    immediately do a full backup because the prior backups won't be of much use.
    If you have done a partial (hot) backup that includes the tablespace that
    was dropped and the needed archive logs, and probably the system tablespace
    and control file, you should be able to recover the tablespace. You would
    probably be doing a TSPITR.

    Whether the logical backup (export) would be better probably depends
    on issues like whether that is your only option (depending on what your
    partial backup backed up), the size of the tablespace, the level of
    activity, the importance of data loss, etc.

    Dennis Williams
    DBA

    Lifetouch, Inc.
    dwilliams_at_lifetouch.com

    -----Original Message-----
    Sent: Tuesday, June 11, 2002 8:24 AM
    To: Multiple recipients of list ORACLE-L

    Hi,

    I don't have much onhand experience, I thought
    list is the appropriate place to get information.
    These are related to Oracle Performance Tuning and
    Backup & Recovery.

    I have a system of Oracle 8i under HP-Unix and with
    ARCHIVELOG on. It has been running and stable since
    last 2-3 years. This was a brief overview.

    Que 1: Assuming that I am using Cost based Optimizer
    (CBO); I have a query which is a join of three tables
    out of which only one table have been analyzed and in
    first case rest two were analyzed but long time back
    and after that I have changed the structure of the
    table and in second case the other two tables have not
    been analyzed. Will Oracle use CBO ? I know it will
    but then what will be the behavior of optimizer ? Will
    it take the best guess for the other two tables in
    both the cases and if yes then those best guesses will
    be depending upon on what ?

    Que 2: Suppose u have ran EXPLAIN PLAN for a
    particular query at sqlplus prompt and there are
    around 50 more users running EXPLAIN PLAN at the same
    time from the UNIX bos itself. In this case the
    OSUSER, TERMINAL, PROGRAM will be same (u know it
    better). Now suppose I want to run TKPROF against the
    trace file how will I identify which will be my trace
    file in USER_DUMP_DEST directory ?

    Que 3: Supposing if I have Oracle Report running on
    Oracle. I passed 3 parameters first time it took me
    around 3 seconds to execute and 4 parameters second
    time its hogging the system taking almost 25 minutes
    to run. There are no concatenated indexs on the
    underlying tables. What might be the cause and how
    would u go about identifying it ?

    Que 4: A user starts complaining about a particular
    part of an appln. What will be my first steps to
    optimize it ?

    Que 5: Let's say we have a situation where at point
    "A" I took a complete db backup. Somewhere after point
    "B" I lost an online redo log file. I recovered the db
    using the bck taken at point "A" and archived redo log
    files and started the new incarnation at point "B". I
    took a complete logical backup at point "B" and taking
    incremental backup after that. Now again at point "C"
    I took the complete bck. If I have dropped a
    tablespace somewhere in between point B and C which
    are the possible ways to recover it and which one will
    be the fastest ? (Of course if possible).

    A---------------B---------------C---------------D

    If I am not clear on any of the part please let me
    know and I'll do the needful.

    Thanks

    Do You Yahoo!?
    Yahoo! - Official partner of 2002 FIFA World Cup
    http://fifaworldcup.yahoo.com
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: sam d
    INET: sam_orafan_at_yahoo.com

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------

    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: DENNIS WILLIAMS
    INET: DWILLIAMS_at_LIFETOUCH.COM

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------

    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Sam d at Jun 25, 2002 at 4:53 am
    Sorry for the late reply,
    thx a lot all of u for the help

    Regards
    Sameer
    --- "Godlewski, Melissa"
    wrote:
    Sam,

    Que1:
    If you have statistics on the table then the cost
    based optimizer is used.
    If you have out of data statistics you could be
    sending bad information to
    the optimizer for join conditions. Using the
    explain plan and plan table
    (utlplan.sql in $oracle_home/rdbms/admin) will show
    you cost information and
    access paths to the data.

    Que2:
    The trace file should have a date time stamp that is
    near the time you ran
    yours. Additionally if you look at the trace file
    it will have the osuer
    information in it.

    Que3:
    It's hard to say the cause. You will need to look
    at v$session and
    v$session_wait to identify what the session in
    waiting for. Is it possible
    there was other jobs executing on the system which
    slowed down your query
    the second time? On NT the perfmon on UNIX top will
    give you some
    information. You need to know what the waits are.
    Additionally you can
    trace or dump the session.

    Que4:
    Run an explain plan and tk_prof on the session to
    find out what objects the
    application is using. Verify all the indexes are
    there and not dropped
    accidentally. You need to find the objects and
    verify the access paths the
    optimizer is using for the application are the best
    ones. Hints or sql
    rewrite may be in order.

    Que5:
    I'm not sure what you mean by logical backup. Maybe
    below suggestions may
    work.
    Are you using RMAN for backups. If so look into
    tablespace recover. If not
    restore your backups to a development/test server
    recover the tablespace and
    associated data. Export/Import to your other
    machine.


    -----Original Message-----
    From: sam d
    Sent: Tuesday, June 11, 2002 9:24 AM
    To: Multiple recipients of list ORACLE-L
    Subject: Performance Tuning and Backup & Recovery


    Hi,

    I don't have much onhand experience, I thought
    list is the appropriate place to get information.
    These are related to Oracle Performance Tuning and
    Backup & Recovery.

    I have a system of Oracle 8i under HP-Unix and with
    ARCHIVELOG on. It has been running and stable since
    last 2-3 years. This was a brief overview.

    Que 1: Assuming that I am using Cost based Optimizer
    (CBO); I have a query which is a join of three
    tables
    out of which only one table have been analyzed and
    in
    first case rest two were analyzed but long time back
    and after that I have changed the structure of the
    table and in second case the other two tables have
    not
    been analyzed. Will Oracle use CBO ? I know it will
    but then what will be the behavior of optimizer ?
    Will
    it take the best guess for the other two tables in
    both the cases and if yes then those best guesses
    will
    be depending upon on what ?

    Que 2: Suppose u have ran EXPLAIN PLAN for a
    particular query at sqlplus prompt and there are
    around 50 more users running EXPLAIN PLAN at the
    same
    time from the UNIX bos itself. In this case the
    OSUSER, TERMINAL, PROGRAM will be same (u know it
    better). Now suppose I want to run TKPROF against
    the
    trace file how will I identify which will be my
    trace
    file in USER_DUMP_DEST directory ?

    Que 3: Supposing if I have Oracle Report running on
    Oracle. I passed 3 parameters first time it took me
    around 3 seconds to execute and 4 parameters second
    time its hogging the system taking almost 25 minutes
    to run. There are no concatenated indexs on the
    underlying tables. What might be the cause and how
    would u go about identifying it ?

    Que 4: A user starts complaining about a particular
    part of an appln. What will be my first steps to
    optimize it ?

    Que 5: Let's say we have a situation where at point
    "A" I took a complete db backup. Somewhere after
    point
    "B" I lost an online redo log file. I recovered the
    db
    using the bck taken at point "A" and archived redo
    log
    files and started the new incarnation at point "B".
    I
    took a complete logical backup at point "B" and
    taking
    incremental backup after that. Now again at point
    "C"
    I took the complete bck. If I have dropped a
    tablespace somewhere in between point B and C which
    are the possible ways to recover it and which one
    will
    be the fastest ? (Of course if possible).

    A---------------B---------------C---------------D

    If I am not clear on any of the part please let me
    know and I'll do the needful.

    Thanks


    __________________________________________________
    Do You Yahoo!?
    Yahoo! - Official partner of 2002 FIFA World Cup
    http://fifaworldcup.yahoo.com
    --
    Please see the official ORACLE-L FAQ:
    http://www.orafaq.com
    --
    Author: sam d
    INET: sam_orafan_at_yahoo.com

    Fat City Network Services -- (858) 538-5051 FAX:
    (858) 538-5051
    San Diego, California -- Public Internet
    access / Mailing Lists
    To REMOVE yourself from this mailing list, send an
    E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of
    'ListGuru') and in
    the message BODY, include a line containing: UNSUB
    ORACLE-L
    (or the name of mailing list you want to be removed
    from). You may
    also send the HELP command for other information
    (like subscribing).
    Do You Yahoo!?
    Yahoo! - Official partner of 2002 FIFA World Cup
    http://fifaworldcup.yahoo.com

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: sam d
    INET: sam_orafan_at_yahoo.com

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedJun 11, '02 at 1:23p
activeJun 25, '02 at 4:53a
posts4
users3
websiteoracle.com

People

Translate

site design / logo © 2021 Grokbase