FAQ
I had one FOrms application running using Oracle 9iAS Releas1 and 9i Rel 1DB. I got user complaining that one forms is very slow and after tracing I got the following kind of TKPROF output for lot of queries


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse 3262 0.32 0.41 0 0 0 0
Execute 3262 0.97 1.19 0 0 0 0
Fetch 3262 0.84 2.17 120 26472 0 3262
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 9786 2.13 3.78 120 26472 0 3262

I don't understand as why there is so much of Parsing. Can somebody advice as what is this and why it is so.


TIA



Do you Yahoo!?
vote.yahoo.com - Register online to vote today!

Search Discussions

  • Jared Still at Oct 13, 2004 at 11:41 am
    Yup, lots of parsing.

    If you eliminate all the parsing, you will have saved
    .32 seconds on a 3.78 second query.

    Do you think the user will notice?

    The query is spending 2.17 seconds retrieving 26472 rows
    of data, only 3262 of which you are using.

    26472 rows fetched / 120 fetches = 220 rows per block.

    3262 rows retrieved / 120 fetches = 27 rows per block

    The data being retrieved is fairly well scattered across the table.

    Perhaps a different index is in order?

    Or maybe the query is limited by the design of the table?

    Or the query is malformed?

    Anyway, reducing IO would be in order here if possible.

    Lots of maybes.

    You might consider running a 10046 trace on the user running
    this form, and find out where and why the time is being used.

    Tkprof does not provided sufficient detail other than what is
    needed to drive lots of speculation. :)

    On Tue, 12 Oct 2004 11:42:38 -0700 (PDT), Sanjay Mishra
    wrote:
    I had one FOrms application running using Oracle 9iAS Releas1 and 9i Rel 1DB. I got user complaining that one forms is very slow and after tracing I got the following kind of TKPROF output for lot of queries

    call count cpu elapsed disk query current rows
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    Parse 3262 0.32 0.41 0 0 0 0
    Execute 3262 0.97 1.19 0 0 0 0
    Fetch 3262 0.84 2.17 120 26472 0 3262
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    total 9786 2.13 3.78 120 26472 0 3262
    I don't understand as why there is so much of Parsing. Can somebody advice as what is this and why it is so.

    TIA

    ---------------------------------
    Do you Yahoo!?
    vote.yahoo.com - Register online to vote today!

    --
    http://www.freelists.org/webpage/oracle-l
    --
    Jared Still
    Certifiable Oracle DBA and Part Time Perl Evangelist
    --
    http://www.freelists.org/webpage/oracle-l
  • Wolfgang Breitling at Oct 13, 2004 at 12:12 pm
    Actually it looks as if 1 row is returned per execution: 3262 parses, 3262
    executes, 3262 fetches and 3262 rows returned. If it is correct that every
    execute of this sql returns 1 row then maybe there is something that can be
    done with an index to target that row better. Provided the performance of
    that sql is enough of a problem to warrant an index just for it.
    At 10:45 AM 10/13/2004, Jared Still wrote:
    Yup, lots of parsing.

    If you eliminate all the parsing, you will have saved
    .32 seconds on a 3.78 second query.

    Do you think the user will notice?

    The query is spending 2.17 seconds retrieving 26472 rows
    of data, only 3262 of which you are using.

    26472 rows fetched / 120 fetches = 220 rows per block.

    3262 rows retrieved / 120 fetches = 27 rows per block

    The data being retrieved is fairly well scattered across the table.

    Perhaps a different index is in order?

    Or maybe the query is limited by the design of the table?

    Or the query is malformed?

    Anyway, reducing IO would be in order here if possible.

    Lots of maybes.

    You might consider running a 10046 trace on the user running
    this form, and find out where and why the time is being used.

    Tkprof does not provided sufficient detail other than what is
    needed to drive lots of speculation. :)
    Wolfgang Breitling
    Oracle7, 8, 8i, 9i OCP DBA
    Centrex Consulting Corporation
    http://www.centrexcc.com
  • Jared Still at Oct 13, 2004 at 12:39 pm

    On Wed, 13 Oct 2004 11:14:52 -0600, Wolfgang Breitling wrote:
    Actually it looks as if 1 row is returned per execution: 3262 parses, 3262
    executes, 3262 fetches and 3262 rows returned. If it is correct that every
    D'oh!

    Yup, that was pretty obvious.

    Jared
    At 10:45 AM 10/13/2004, Jared Still wrote:
    Yup, lots of parsing.

    If you eliminate all the parsing, you will have saved
    .32 seconds on a 3.78 second query.

    Do you think the user will notice?

    The query is spending 2.17 seconds retrieving 26472 rows
    of data, only 3262 of which you are using.

    26472 rows fetched / 120 fetches = 220 rows per block.

    3262 rows retrieved / 120 fetches = 27 rows per block

    The data being retrieved is fairly well scattered across the table.

    Perhaps a different index is in order?

    Or maybe the query is limited by the design of the table?

    Or the query is malformed?

    Anyway, reducing IO would be in order here if possible.

    Lots of maybes.

    You might consider running a 10046 trace on the user running
    this form, and find out where and why the time is being used.

    Tkprof does not provided sufficient detail other than what is
    needed to drive lots of speculation. :)
    Wolfgang Breitling
    Oracle7, 8, 8i, 9i OCP DBA
    Centrex Consulting Corporation
    http://www.centrexcc.com



    --
    http://www.freelists.org/webpage/oracle-l
    --
    Jared Still
    Certifiable Oracle DBA and Part Time Perl Evangelist
    --
    http://www.freelists.org/webpage/oracle-l
  • Web Stalkers at Oct 13, 2004 at 1:04 pm
    Our Development DBA's have been asked to look into a product for
    Oracle called ClusterGuard. We can't find anything about it.

    I know you guys know all things (at least about Oracle) so I'm hoping
    someone can give me some information.

    TIA

    --
    Stephen Andert
    author of Web Stalkers, Protect Yourself from Internet Criminals and Psychopaths
    http://www.rampant-books.com/book_2004_2_stalkers.htm
    --
    http://www.freelists.org/webpage/oracle-l
  • Ganesh Raja at Oct 13, 2004 at 12:49 pm
    Okay I am Going to Piggy Back on this ..

    I have an ANSI SQL which when Rewriiten Using Oracle Syntax it seems
    to take a Better plan to execute it .. Any ideas why this is
    happening.

    There is a Total Change in the plan and it works much faster than the
    ANSI Counterpart

    Any help is appreciated.

    Thanks.

    Cheers
    Ganesh R
    On Wed, 13 Oct 2004 09:45:16 -0700, Jared Still wrote:
    Yup, lots of parsing.

    If you eliminate all the parsing, you will have saved
    .32 seconds on a 3.78 second query.

    Do you think the user will notice?

    The query is spending 2.17 seconds retrieving 26472 rows
    of data, only 3262 of which you are using.

    26472 rows fetched / 120 fetches = 220 rows per block.

    3262 rows retrieved / 120 fetches = 27 rows per block

    The data being retrieved is fairly well scattered across the table.

    Perhaps a different index is in order?

    Or maybe the query is limited by the design of the table?

    Or the query is malformed?

    Anyway, reducing IO would be in order here if possible.

    Lots of maybes.

    You might consider running a 10046 trace on the user running
    this form, and find out where and why the time is being used.

    Tkprof does not provided sufficient detail other than what is
    needed to drive lots of speculation. :)


    On Tue, 12 Oct 2004 11:42:38 -0700 (PDT), Sanjay Mishra


    wrote:
    I had one FOrms application running using Oracle 9iAS Releas1 and 9i Rel 1DB. I got user complaining that one forms is very slow and after tracing I got the following kind of TKPROF output for lot of queries

    call count cpu elapsed disk query current rows
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    Parse 3262 0.32 0.41 0 0 0 0
    Execute 3262 0.97 1.19 0 0 0 0
    Fetch 3262 0.84 2.17 120 26472 0 3262
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    total 9786 2.13 3.78 120 26472 0 3262
    I don't understand as why there is so much of Parsing. Can somebody advice as what is this and why it is so.

    TIA

    ---------------------------------
    Do you Yahoo!?
    vote.yahoo.com - Register online to vote today!

    --
    http://www.freelists.org/webpage/oracle-l

    --
    Jared Still
    Certifiable Oracle DBA and Part Time Perl Evangelist


    --
    http://www.freelists.org/webpage/oracle-l
    --
    http://www.freelists.org/webpage/oracle-l
  • Lex de Haan at Oct 13, 2004 at 12:54 pm
    you might want to provide the two statements you are talking about?
    and maybe even the two corresponding execution plans?


    Kind regards,
    Lex.


    visit http://www.naturaljoin.nl

    skype me
    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Ganesh Raja
    Sent: Wednesday, October 13, 2004 18:54
    To: jkstill_at_gmail.com
    Cc: smishra_97_at_yahoo.com; oracle-l_at_freelists.org
    Subject: Re: Sincere Advice on Sql Plan - Thanks

    Okay I am Going to Piggy Back on this ..

    I have an ANSI SQL which when Rewriiten Using Oracle Syntax it seems
    to take a Better plan to execute it .. Any ideas why this is
    happening.

    There is a Total Change in the plan and it works much faster than the
    ANSI Counterpart

    Any help is appreciated.

    Thanks.

    Cheers
    Ganesh R

    --
    http://www.freelists.org/webpage/oracle-l
  • Ganesh Raja at Oct 13, 2004 at 1:24 pm
    Lex,
    Not sure If Oracle-L Accepts Attachments ..

    Here are the Queries and the Plan.

    Thanks for the help.

    Rgds,
    Ganesh

    On Wed, 13 Oct 2004 19:58:55 +0200, Lex de Haan
    wrote:
    you might want to provide the two statements you are talking about?
    and maybe even the two corresponding execution plans?

    Kind regards,
    Lex.

    -------------------------------
    visit http://www.naturaljoin.nl
    -------------------------------
    skype me


    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Ganesh Raja
    Sent: Wednesday, October 13, 2004 18:54
    To: jkstill_at_gmail.com
    Cc: smishra_97_at_yahoo.com; oracle-l_at_freelists.org
    Subject: Re: Sincere Advice on Sql Plan - Thanks

    Okay I am Going to Piggy Back on this ..

    I have an ANSI SQL which when Rewriiten Using Oracle Syntax it seems
    to take a Better plan to execute it .. Any ideas why this is
    happening.

    There is a Total Change in the plan and it works much faster than the
    ANSI Counterpart

    Any help is appreciated.

    Thanks.

    Cheers
    Ganesh R

    --
    http://www.freelists.org/webpage/oracle-l
    Attached file included as plaintext by Ecartis --
    File: plan.txt

    Explain Plan for
    SELECT REVIEW_ID rid
    FROM KDD_REVIEW_STATUS_DESC left outer join KDD_CENTRICITY
    left outer join KDD_SCNRO inner join (KDD_REVIEW_SCNRO
    left outer join (KDD_REVIEW
    left outer join KDD_REVIEW_OWNER using (OWNER_SEQ_ID)) using (REVIEW_ID)
    left outer join KDD_REVIEW_FINANCIAL using (REVIEW_ID)) on (KDD_REVIEW_SCNRO.SCNRO_ID = KDD_SCNRO.SCNRO_ID) on (KDD_REVIEW.CNTRY_ID = KDD_CENTRICITY.CNTRY_ID) on (KDD_REVIEW.STATUS_CD = KDD_REVIEW_STATUS_DESC.STATUS_CD)
    WHERE KDD_REVIEW.PRCSNG_BATCH_CMPLT_FL != 'N'

    AND OVERLAYS('abf', KDD_REVIEW.BUS_DMN_ST) != 'N'

    AND KDD_REVIEW.STATUS_CD in ('OP', 'NW', 'RO', 'FL', 'RA')
    AND KDD_REVIEW.SCNRO_CLASS_CD in ('TC', 'BEX', 'IML')
    AND KDD_REVIEW_SCNRO.SCNRO_ID in (114000022)
    AND KDD_REVIEW.CNTRY_ID in (113000011,113000009,113000006,113000004,113000002,113000001)
    AND OWNER_SEQ_ID in (50,39,34,32,19,49,48,47,16,15,46,14,45,13,12,43,11,42,10,40,9,8,7,6,5,4,3,24,2,23,22,52,21,51)

    AND KDD_REVIEW.JRSDCN_CD in ('DFLT','MUM','SNG','BOK','SEO','LDN','APC','SYD','NYK','TOK','HKG')
    ORDER BY KDD_REVIEW.SCORE_CT DESC, KDD_REVIEW.CREAT_TS DESC, REVIEW_ID DESC

    /

    SQL> @plan
    Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)|
    ---------------------------------------------------------------------------------------------------------
    0 | SELECT STATEMENT | | 297K| 36M| | 720K (1)|
    1 | SORT ORDER BY | | 297K| 36M| 77M| 720K (1)|
    2 | NESTED LOOPS | | 297K| 36M| | 714K (1)|
    * 3 | FILTER | | | | | |
    4 | NESTED LOOPS OUTER | | | | | |
    5 | INDEX FULL SCAN | PK_KDD_CENTRICITY | 38 | 266 | | 1 (0)|
    6 | VIEW | | 9395 | 1100K| | |
    * 7 | FILTER | | | | | |
    8 | NESTED LOOPS OUTER | | | | | |
    9 | NESTED LOOPS OUTER | | 9395 | 211K| | 6 (17)|
    10 | NESTED LOOPS | | 9395 | 174K| | 5 (20)|
    11 | TABLE ACCESS FULL | KDD_REVIEW_SCNRO | 9395 | 110K| | 3 (0)|
    * 12 | INDEX UNIQUE SCAN | PK_KDD_SCNRO | 1 | 7 | | |
    * 13 | INDEX UNIQUE SCAN | PK_KDD_REVIEW_FNCL | 1 | 4 | | |
    14 | VIEW | | 1 | 94 | | |
    15 | NESTED LOOPS OUTER | | 1 | 46 | | 3 (34)|
    16 | TABLE ACCESS BY INDEX ROWID| KDD_REVIEW | 1 | 43 | | 2 (50)|
    * 17 | INDEX UNIQUE SCAN | PK_KDD_REVIEW | 1 | | | 1 (0)|
    * 18 | INDEX UNIQUE SCAN | PK_K_RVW_OWNER | 1 | 3 | | |
    * 19 | INDEX RANGE SCAN | IFK3_K_RVW_STUS_CD | 1 | 3 | | |
    ---------------------------------------------------------------------------------------------------------

    Predicate Information (identified by operation id):

    3 - filter("from$_subquery$_017"."PRCSNG_BATCH_CMPLT_FL_60"<>'N' AND

    ("from$_subquery$_017"."STATUS_CD_18"='FL' OR "from$_subquery$_017"."STATUS_CD_18"='NW' OR
    "from$_subquery$_017"."STATUS_CD_18"='OP' OR "from$_subquery$_017"."STATUS_CD_18"='RA' OR
    "from$_subquery$_017"."STATUS_CD_18"='RO') AND ("from$_subquery$_017"."SCNRO_CLASS_CD_24"='BEX' OR
    "from$_subquery$_017"."SCNRO_CLASS_CD_24"='IML' OR "from$_subquery$_017"."SCNRO_CLASS_CD_24"='TC') AND
    "from$_subquery$_017"."SCNRO_ID_11"=114000022 AND ("from$_subquery$_017"."CNTRY_ID_20"=113000001 OR
    "from$_subquery$_017"."CNTRY_ID_20"=113000002 OR "from$_subquery$_017"."CNTRY_ID_20"=113000004 OR
    "from$_subquery$_017"."CNTRY_ID_20"=113000006 OR "from$_subquery$_017"."CNTRY_ID_20"=113000009 OR
    "from$_subquery$_017"."CNTRY_ID_20"=113000011) AND ("from$_subquery$_017"."OWNER_SEQ_ID_13"=2 OR
    "from$_subquery$_017"."OWNER_SEQ_ID_13"=3 OR "from$_subquery$_017"."OWNER_SEQ_ID_13"=4 OR
    "from$_subquery$_017"."OWNER_SEQ_ID_13"=5 OR "from$_subquery$_017"."OWNER_SEQ_ID_13"=6 OR
    "from$_subquery$_017"."OWNER_SEQ_ID_13"=7 OR "from$_subquery$_017"."OWNER_SEQ_ID_13"=8 OR
    "from$_subquery$_017"."OWNER_SEQ_ID_13"=9 OR "from$_subquery$_017"."OWNER_SEQ_ID_13"=10 OR
    "from$_subquery$_017"."OWNER_SEQ_ID_13"=11 OR "from$_subquery$_017"."OWNER_SEQ_ID_13"=12 OR
    "from$_subquery$_017"."OWNER_SEQ_ID_13"=13 OR "from$_subquery$_017"."OWNER_SEQ_ID_13"=14 OR
    "from$_subquery$_017"."OWNER_SEQ_ID_13"=15 OR "from$_subquery$_017"."OWNER_SEQ_ID_13"=16 OR
    "from$_subquery$_017"."OWNER_SEQ_ID_13"=19 OR "from$_subquery$_017"."OWNER_SEQ_ID_13"=21 OR
    "from$_subquery$_017"."OWNER_SEQ_ID_13"=22 OR "from$_subquery$_017"."OWNER_SEQ_ID_13"=23 OR
    "from$_subquery$_017"."OWNER_SEQ_ID_13"=24 OR "from$_subquery$_017"."OWNER_SEQ_ID_13"=32 OR
    "from$_subquery$_017"."OWNER_SEQ_ID_13"=34 OR "from$_subquery$_017"."OWNER_SEQ_ID_13"=39 OR
    "from$_subquery$_017"."OWNER_SEQ_ID_13"=40 OR "from$_subquery$_017"."OWNER_SEQ_ID_13"=42 OR
    "from$_subquery$_017"."OWNER_SEQ_ID_13"=43 OR "from$_subquery$_017"."OWNER_SEQ_ID_13"=45 OR
    "from$_subquery$_017"."OWNER_SEQ_ID_13"=46 OR "from$_subquery$_017"."OWNER_SEQ_ID_13"=47 OR
    "from$_subquery$_017"."OWNER_SEQ_ID_13"=48 OR "from$_subquery$_017"."OWNER_SEQ_ID_13"=49 OR
    "from$_subquery$_017"."OWNER_SEQ_ID_13"=50 OR "from$_subquery$_017"."OWNER_SEQ_ID_13"=51 OR
    "from$_subquery$_017"."OWNER_SEQ_ID_13"=52) AND ("from$_subquery$_017"."JRSDCN_CD_58"='APC' OR
    "from$_subquery$_017"."JRSDCN_CD_58"='BOK' OR "from$_subquery$_017"."JRSDCN_CD_58"='DFLT' OR
    "from$_subquery$_017"."JRSDCN_CD_58"='HKG' OR "from$_subquery$_017"."JRSDCN_CD_58"='LDN' OR
    "from$_subquery$_017"."JRSDCN_CD_58"='MUM' OR "from$_subquery$_017"."JRSDCN_CD_58"='NYK' OR
    "from$_subquery$_017"."JRSDCN_CD_58"='SEO' OR "from$_subquery$_017"."JRSDCN_CD_58"='SNG' OR
    "from$_subquery$_017"."JRSDCN_CD_58"='SYD' OR "from$_subquery$_017"."JRSDCN_CD_58"='TOK') AND
    "JOE"."OVERLAYS"('abf',"from$_subquery$_017"."BUS_DMN_ST_56")<>'N')
    7 - filter("from$_subquery$_015"."CNTRY_ID_8"="KDD_CENTRICITY"."CNTRY_ID")
    12 - access("KDD_REVIEW_SCNRO"."SCNRO_ID"="KDD_SCNRO"."SCNRO_ID")
    13 - access("KDD_REVIEW_SCNRO"."REVIEW_ID"="KDD_REVIEW_FINANCIAL"."REVIEW_ID"(+))
    17 - access("KDD_REVIEW_SCNRO"."REVIEW_ID"="KDD_REVIEW"."REVIEW_ID")
    18 - access("KDD_REVIEW"."OWNER_SEQ_ID"="KDD_REVIEW_OWNER"."OWNER_SEQ_ID"(+))
    19 - access("from$_subquery$_017"."STATUS_CD_18"="KDD_REVIEW_STATUS_DESC"."STATUS_CD")
    filter("KDD_REVIEW_STATUS_DESC"."STATUS_CD"='FL' OR "KDD_REVIEW_STATUS_DESC"."STATUS_CD"='NW' OR
    "KDD_REVIEW_STATUS_DESC"."STATUS_CD"='OP' OR "KDD_REVIEW_STATUS_DESC"."STATUS_CD"='RA' OR
    "KDD_REVIEW_STATUS_DESC"."STATUS_CD"='RO')

    70 rows selected.

    Explain Plan for
    Select KR.Review_Id
    From

    KDD_REVIEW KR,
    KDD_REVIEW_OWNER KRO,
    KDD_REVIEW_FINANCIAL KRF,
    KDD_REVIEW_STATUS_DESC KRS,
    KDD_REVIEW_SCNRO KRC,
    KDD_CENTRICITY KC,
    KDD_SCNRO KS
    Where KR.Owner_Seq_Id = KRO.Owner_Seq_Id (+)
    And KR.Review_Id = KRF.REVIEW_ID (+)
    And KR.Status_Cd = KRS.Status_Cd (+)
    And KR.Review_Id = KRC.REVIEW_ID(+)

    And KR.Cntry_Id = KC.CNTRY_ID (+)
    And KR.Scnro_Id = KS.Scnro_Id (+)
    AND KR.PRCSNG_BATCH_CMPLT_FL != 'N'

    AND OVERLAYS('abf', KR.BUS_DMN_ST) != 'N'
    AND KR.STATUS_CD in ('OP', 'NW', 'RO', 'FL', 'RA')
    AND KR.SCNRO_CLASS_CD in ('TC', 'BEX', 'IML')
    AND KRC.SCNRO_ID in (114000022)

    AND KR.CNTRY_ID in (113000011,113000009,113000006,113000004,113000002,113000001)
    AND KR.OWNER_SEQ_ID in (50,39,34,32,19,49,48,47,16,15,46,14,45,13,12,43,11,42,10,40,9,8,7,6,5,4,3,24,2,23,22,52,21,51)
    AND KR.JRSDCN_CD in ('DFLT','MUM','SNG','BOK','SEO','LDN','APC','SYD','NYK','TOK','HKG')

    /

    SQL> @plan
    Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
    0 | SELECT STATEMENT | | 391 | 29325 | 38 (3)|
    1 | NESTED LOOPS OUTER | | 391 | 29325 | 38 (3)|
    2 | NESTED LOOPS OUTER | | 391 | 27761 | 37 (3)|
    3 | NESTED LOOPS OUTER | | 391 | 26588 | 36 (3)|
    * 4 | HASH JOIN | | 391 | 23851 | 35 (3)|
    5 | NESTED LOOPS OUTER | | 391 | 19159 | 30 (4)|
    6 | NESTED LOOPS OUTER| | 391 | 16422 | 29 (4)|
    * 7 | TABLE ACCESS FULL| KDD_REVIEW | 391 | 15249 | 27 (0)|
    * 8 | INDEX RANGE SCAN | IFK3_K_RVW_STUS_CD | 1 | 3 | |
    * 9 | INDEX UNIQUE SCAN | PK_KDD_SCNRO | 1 | 7 | |
    * 10 | TABLE ACCESS FULL | KDD_REVIEW_SCNRO | 1342 | 16104 | 3 (0)|
    * 11 | INDEX UNIQUE SCAN | PK_KDD_CENTRICITY | 1 | 7 | |
    * 12 | INDEX UNIQUE SCAN | PK_K_RVW_OWNER | 1 | 3 | |
    * 13 | INDEX UNIQUE SCAN | PK_KDD_REVIEW_FNCL | 1 | 4 | |
    ------------------------------------------------------------------------------------

    Predicate Information (identified by operation id):

    4 - access("KR"."REVIEW_ID"="KRC"."REVIEW_ID")
    7 - filter("KR"."PRCSNG_BATCH_CMPLT_FL"<>'N' AND ("KR"."STATUS_CD"='FL' OR
    "KR"."STATUS_CD"='NW' OR "KR"."STATUS_CD"='OP' OR "KR"."STATUS_CD"='RA' OR
    "KR"."STATUS_CD"='RO') AND ("KR"."SCNRO_CLASS_CD"='BEX' OR
    "KR"."SCNRO_CLASS_CD"='IML' OR "KR"."SCNRO_CLASS_CD"='TC') AND
    ("KR"."CNTRY_ID"=113000001 OR "KR"."CNTRY_ID"=113000002 OR
    "KR"."CNTRY_ID"=113000004 OR "KR"."CNTRY_ID"=113000006 OR "KR"."CNTRY_ID"=113000009
    OR "KR"."CNTRY_ID"=113000011) AND ("KR"."OWNER_SEQ_ID"=2 OR "KR"."OWNER_SEQ_ID"=3
    OR "KR"."OWNER_SEQ_ID"=4 OR "KR"."OWNER_SEQ_ID"=5 OR "KR"."OWNER_SEQ_ID"=6 OR
    "KR"."OWNER_SEQ_ID"=7 OR "KR"."OWNER_SEQ_ID"=8 OR "KR"."OWNER_SEQ_ID"=9 OR
    "KR"."OWNER_SEQ_ID"=10 OR "KR"."OWNER_SEQ_ID"=11 OR "KR"."OWNER_SEQ_ID"=12 OR
    "KR"."OWNER_SEQ_ID"=13 OR "KR"."OWNER_SEQ_ID"=14 OR "KR"."OWNER_SEQ_ID"=15 OR
    "KR"."OWNER_SEQ_ID"=16 OR "KR"."OWNER_SEQ_ID"=19 OR "KR"."OWNER_SEQ_ID"=21 OR
    "KR"."OWNER_SEQ_ID"=22 OR "KR"."OWNER_SEQ_ID"=23 OR "KR"."OWNER_SEQ_ID"=24 OR
    "KR"."OWNER_SEQ_ID"=32 OR "KR"."OWNER_SEQ_ID"=34 OR "KR"."OWNER_SEQ_ID"=39 OR
    "KR"."OWNER_SEQ_ID"=40 OR "KR"."OWNER_SEQ_ID"=42 OR "KR"."OWNER_SEQ_ID"=43 OR
    "KR"."OWNER_SEQ_ID"=45 OR "KR"."OWNER_SEQ_ID"=46 OR "KR"."OWNER_SEQ_ID"=47 OR
    "KR"."OWNER_SEQ_ID"=48 OR "KR"."OWNER_SEQ_ID"=49 OR "KR"."OWNER_SEQ_ID"=50 OR
    "KR"."OWNER_SEQ_ID"=51 OR "KR"."OWNER_SEQ_ID"=52) AND ("KR"."JRSDCN_CD"='APC' OR
    "KR"."JRSDCN_CD"='BOK' OR "KR"."JRSDCN_CD"='DFLT' OR "KR"."JRSDCN_CD"='HKG' OR
    "KR"."JRSDCN_CD"='LDN' OR "KR"."JRSDCN_CD"='MUM' OR "KR"."JRSDCN_CD"='NYK' OR
    "KR"."JRSDCN_CD"='SEO' OR "KR"."JRSDCN_CD"='SNG' OR "KR"."JRSDCN_CD"='SYD' OR
    "KR"."JRSDCN_CD"='TOK') AND "JOE"."OVERLAYS"('abf',"KR"."BUS_DMN_ST")<>'N')
    8 - access("KR"."STATUS_CD"="KRS"."STATUS_CD"(+))
    9 - access("KR"."SCNRO_ID"="KS"."SCNRO_ID"(+))
    10 - filter("KRC"."SCNRO_ID"=114000022)
    11 - access("KR"."CNTRY_ID"="KC"."CNTRY_ID"(+))

    12 - access("KR"."OWNER_SEQ_ID"="KRO"."OWNER_SEQ_ID"(+))
    13 - access("KR"."REVIEW_ID"="KRF"."REVIEW_ID"(+))

    52 rows selected.
  • Stephane Faroult at Oct 13, 2004 at 4:10 pm
    (Sorry, had to remove the execution plans to avoid overquoting) That's
    an interesting one, and very illustrative of what I dislike in the ANSI
    syntax. In the old syntax, all tables in the FROM clause are (roughly)
    equal. With the ANSI syntax, you SELECT from *one* table, which is
    joined to others - primum inter pares (in plain English, more equal than
    the others). You totally lose the symmetry. The ANSI syntax takes it
    (pretty obviously) as the driving table - not the Oracle syntax. I guess
    that finding the proper driving table with the ANSI syntax musn't be a
    piece of cake for the optimizer, because you put it, from the very
    start, on some rails. If the wrong table was specified in the FROM
    clause, tough luck.
    Stephane Faroult

    Ganesh Raja wrote:
    Lex,
    Not sure If Oracle-L Accepts Attachments ..

    Here are the Queries and the Plan.

    Thanks for the help.

    Rgds,
    Ganesh


    On Wed, 13 Oct 2004 19:58:55 +0200, Lex de Haan
    wrote:

    you might want to provide the two statements you are talking about?
    and maybe even the two corresponding execution plans?

    Kind regards,
    Lex.

    -------------------------------
    visit http://www.naturaljoin.nl
    -------------------------------
    skype me


    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Ganesh Raja
    Sent: Wednesday, October 13, 2004 18:54
    To: jkstill_at_gmail.com
    Cc: smishra_97_at_yahoo.com; oracle-l_at_freelists.org
    Subject: Re: Sincere Advice on Sql Plan - Thanks

    Okay I am Going to Piggy Back on this ..

    I have an ANSI SQL which when Rewriiten Using Oracle Syntax it seems
    to take a Better plan to execute it .. Any ideas why this is
    happening.

    There is a Total Change in the plan and it works much faster than the
    ANSI Counterpart

    Any help is appreciated.

    Thanks.

    Cheers
    Ganesh R

    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l
  • Ganesh Raja at Oct 13, 2004 at 4:22 pm
    The ANSI Query runs for 20 Secs to get 20 Rows !!!! - The users are
    shouting Unacceptable !!! (i will do the same) .. If somebody can make
    sense of this . It will be real useful.

    Cheers
    Ganesh

    On Wed, 13 Oct 2004 23:16:30 +0200, Stephane Faroult
    wrote:
    (Sorry, had to remove the execution plans to avoid overquoting) That's an
    interesting one, and very illustrative of what I dislike in the ANSI syntax.
    In the old syntax, all tables in the FROM clause are (roughly) equal. With
    the ANSI syntax, you SELECT from *one* table, which is joined to others -
    primum inter pares (in plain English, more equal than the others). You
    totally lose the symmetry. The ANSI syntax takes it (pretty obviously) as
    the driving table - not the Oracle syntax. I guess that finding the proper
    driving table with the ANSI syntax musn't be a piece of cake for the
    optimizer, because you put it, from the very start, on some rails. If the
    wrong table was specified in the FROM clause, tough luck.

    Stephane Faroult



    Ganesh Raja wrote:
    Lex,
    Not sure If Oracle-L Accepts Attachments ..

    Here are the Queries and
    the Plan.
    Thanks for the help.

    Rgds,
    Ganesh

    On Wed, 13 Oct 2004 19:58:55
    +0200, Lex de Haan
    wrote:

    you might want to provide the two statements you are talking about? and
    maybe even the two corresponding execution plans? Kind
    regards,
    Lex.

    visit
    skype me
    >

    -----Original Message-----
    From:
    oracle-l-bounce_at_freelists.org On
    Behalf Of Ganesh Raja
    Sent: Wednesday, October 13, 2004 18:54
    To:
    jkstill_at_gmail.com
    Cc: smishra_97_at_yahoo.com; oracle-l_at_freelists.org
    Subject:
    Re: Sincere Advice on Sql Plan - Thanks
    Okay I am Going to Piggy Back on
    this ..
    I have an ANSI SQL which when Rewriiten Using Oracle Syntax it
    seems
    to take a Better plan to execute it .. Any ideas why this
    is
    happening.

    There is a Total Change in the plan and it works much faster
    than the
    ANSI Counterpart

    Any help is appreciated.

    Thanks.

    Cheers
    Ganesh
    R
    --
    http://www.freelists.org/webpage/oracle-l

    >
    --
    http://www.freelists.org/webpage/oracle-l
  • Mark W. Farnham at Oct 13, 2004 at 4:52 pm
    A wild shot in the dark:

    Guessing you have indexes on the three "in" clauses that generate filter
    operations in ANSI.

    Add <= =>'s using the lowest of the in values and the greatest of the in
    values for bounds.
    (I can't remember if ANSI has between and I'm not looking it up).

    Of course this implies you know the constants you're tossing in there, but
    if you have them for the in clause
    you should have them for the greater-than-equal and less-than-equal.

    Oh and the one with a single in value could be an ='s (unless that varies
    from time to time).

    Leave out any of the in's that you don't have a usable index for.

    I'm not sure it will help, because maybe, for example, your 2 through 51
    thingys is almost all the values.
    Can't tell without a histogram type thing.

    g'luck

    mwf

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Ganesh Raja
    Sent: Wednesday, October 13, 2004 5:27 PM
    To: Stephane Faroult
    Cc: lex.de.haan_at_naturaljoin.nl; oracle-l_at_freelists.org
    Subject: Re: Sincere Advice on Sql Plan - Thanks

    The ANSI Query runs for 20 Secs to get 20 Rows !!!! - The users are
    shouting Unacceptable !!! (i will do the same) .. If somebody can make
    sense of this . It will be real useful.

    Cheers
    Ganesh

    On Wed, 13 Oct 2004 23:16:30 +0200, Stephane Faroult
    wrote:
    (Sorry, had to remove the execution plans to avoid overquoting) That's an
    interesting one, and very illustrative of what I dislike in the ANSI syntax.
    In the old syntax, all tables in the FROM clause are (roughly) equal. With
    the ANSI syntax, you SELECT from *one* table, which is joined to others -
    primum inter pares (in plain English, more equal than the others). You
    totally lose the symmetry. The ANSI syntax takes it (pretty obviously) as
    the driving table - not the Oracle syntax. I guess that finding the proper
    driving table with the ANSI syntax musn't be a piece of cake for the
    optimizer, because you put it, from the very start, on some rails. If the
    wrong table was specified in the FROM clause, tough luck.

    Stephane Faroult



    Ganesh Raja wrote:
    Lex,
    Not sure If Oracle-L Accepts Attachments ..

    Here are the Queries and
    the Plan.
    Thanks for the help.

    Rgds,
    Ganesh

    On Wed, 13 Oct 2004 19:58:55
    +0200, Lex de Haan wrote:
    you might want to provide the two statements you are talking about? and
    maybe even the two corresponding execution plans? Kind
    regards,
    Lex.

    visit
    skype me
    >

    -----Original Message-----
    From:
    oracle-l-bounce_at_freelists.org On
    Behalf Of Ganesh Raja
    Sent: Wednesday, October 13, 2004 18:54
    To:
    jkstill_at_gmail.com
    Cc: smishra_97_at_yahoo.com; oracle-l_at_freelists.org
    Subject:
    Re: Sincere Advice on Sql Plan - Thanks
    Okay I am Going to Piggy Back on
    this ..
    I have an ANSI SQL which when Rewriiten Using Oracle Syntax it
    seems
    to take a Better plan to execute it .. Any ideas why this
    is
    happening.

    There is a Total Change in the plan and it works much faster
    than the
    ANSI Counterpart

    Any help is appreciated.

    Thanks.

    Cheers
    Ganesh
    R
    --
    http://www.freelists.org/webpage/oracle-l

    >
    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l
  • Rivaldi Bahar at Oct 14, 2004 at 12:24 am
    Hi Listers,

    I'm using Oracle 9.1, Solaris, SAP application.

    Damagement asked me to make recommendation for future system based on evalution of current system i.e :

    Do we need to buy a new more powerful machine, more disks, cpu, etc.

    I'm just wondering what aspects to monitor, what kind of report should I submit.

    Sounds like capacity management or planning.

    If you have a checklist about what to do (and I think unix command to do so) or example of report, it would help.

    Thanks.

    Do You Yahoo!?
    Tired of spam? Yahoo! Mail has the best spam protection around
    http://mail.yahoo.com

    --
    http://www.freelists.org/webpage/oracle-l
  • Lex de Haan at Oct 14, 2004 at 3:04 am
    well, if I look at the two SQL statements, the first thing I see is that
    they are *not* equivalent:
    one has an ORDER BY, the other one doesn't. that can make a huge difference,
    and it does in this case.
    the expensive plan is using two temporary tables (see the VIEW steps) and
    77M temp space for the sorting (see the SORT ORDER BY step) so what happens
    if you remove the ORDER BY?

    last but not least, if you are in the position to change the data model,
    spend some time on it. if I see SQL statements like this, with many *long*
    IN lists, I always get the feeling that there is room for improvement
    somewhere :-)

    by the way, I know it is common terminology -- but it is unfair/confusing to
    talk about Oracle syntax vs. ANSI syntax --
    because *both* join expressions are *both* ANSI/ISO syntax and Oracle
    syntax... I rather prefer to refer to "old" and "new" syntax, although I
    must admit that it took Oracle a very long time to implement the "new"
    ANSI/ISO join syntax.

    Kind regards,
    Lex.

    visit http://www.naturaljoin.nl <http://www.naturaljoin.nl>

    skype me

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Ganesh Raja
    Sent: Wednesday, October 13, 2004 19:29
    To: lex.de.haan_at_naturaljoin.nl
    Cc: oracle-l_at_freelists.org
    Subject: Re: Sincere Advice on Sql Plan - Thanks

    Lex,
    Not sure If Oracle-L Accepts Attachments ..

    Here are the Queries and the Plan.

    Thanks for the help.

    Rgds,
    Ganesh

    On Wed, 13 Oct 2004 19:58:55 +0200, Lex de Haan
    wrote:
    you might want to provide the two statements you are talking about?
    and maybe even the two corresponding execution plans?

    Kind regards,
    Lex.

    -------------------------------
    visit http://www.naturaljoin.nl
    -------------------------------
    skype me


    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Ganesh Raja
    Sent: Wednesday, October 13, 2004 18:54
    To: jkstill_at_gmail.com
    Cc: smishra_97_at_yahoo.com; oracle-l_at_freelists.org
    Subject: Re: Sincere Advice on Sql Plan - Thanks

    Okay I am Going to Piggy Back on this ..

    I have an ANSI SQL which when Rewriiten Using Oracle Syntax it seems
    to take a Better plan to execute it .. Any ideas why this is
    happening.

    There is a Total Change in the plan and it works much faster than the
    ANSI Counterpart

    Any help is appreciated.

    Thanks.

    Cheers
    Ganesh R

    --
    http://www.freelists.org/webpage/oracle-l
    --
    http://www.freelists.org/webpage/oracle-l
  • Ganesh Raja at Oct 14, 2004 at 5:31 am
    Lex,

    Thanks for that ... I did include the order By On It and There is No
    Change in the plan or the Execution Time. The Only thing added to my
    plan is an Sort Order By stmt..

    As regards the In List ... (I am answering to Marks Comments also)

    This is Part of a Java Program and the Where clause is Built on the
    fly ... That is the Reason u see so many Tables there but none being
    used in the Where or Select List.

    The IN List needs to be there since it also Dynamic in the sense the
    User May select more than one.

    Thanks as always

    Cheers
    Ganesh

    PS : WIll start following the Old and New Paradigm

    On Thu, 14 Oct 2004 10:09:09 +0200, Lex de Haan
    wrote:
    well, if I look at the two SQL statements, the first thing I see is that
    they are *not* equivalent:
    one has an ORDER BY, the other one doesn't. that can make a huge difference,
    and it does in this case.

    the expensive plan is using two temporary tables (see the VIEW steps) and
    77M temp space for the sorting (see the SORT ORDER BY step) so what happens
    if you remove the ORDER BY?

    last but not least, if you are in the position to change the data model,
    spend some time on it. if I see SQL statements like this, with many *long*
    IN lists, I always get the feeling that there is room for improvement
    somewhere :-)

    by the way, I know it is common terminology -- but it is unfair/confusing to
    talk about Oracle syntax vs. ANSI syntax --
    because *both* join expressions are *both* ANSI/ISO syntax and Oracle
    syntax... I rather prefer to refer to "old" and "new" syntax, although I
    must admit that it took Oracle a very long time to implement the "new"
    ANSI/ISO join syntax.

    Kind regards,
    Lex.

    -------------------------------
    visit http://www.naturaljoin.nl <http://www.naturaljoin.nl>
    -------------------------------
    skype me




    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Ganesh Raja
    Sent: Wednesday, October 13, 2004 19:29
    To: lex.de.haan_at_naturaljoin.nl
    Cc: oracle-l_at_freelists.org
    Subject: Re: Sincere Advice on Sql Plan - Thanks

    Lex,
    Not sure If Oracle-L Accepts Attachments ..

    Here are the Queries and the Plan.

    Thanks for the help.

    Rgds,
    Ganesh

    On Wed, 13 Oct 2004 19:58:55 +0200, Lex de Haan
    wrote:
    you might want to provide the two statements you are talking about?
    and maybe even the two corresponding execution plans?

    Kind regards,
    Lex.

    -------------------------------
    visit http://www.naturaljoin.nl
    -------------------------------
    skype me


    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Ganesh Raja
    Sent: Wednesday, October 13, 2004 18:54
    To: jkstill_at_gmail.com
    Cc: smishra_97_at_yahoo.com; oracle-l_at_freelists.org
    Subject: Re: Sincere Advice on Sql Plan - Thanks

    Okay I am Going to Piggy Back on this ..

    I have an ANSI SQL which when Rewriiten Using Oracle Syntax it seems
    to take a Better plan to execute it .. Any ideas why this is
    happening.

    There is a Total Change in the plan and it works much faster than the
    ANSI Counterpart

    Any help is appreciated.

    Thanks.

    Cheers
    Ganesh R

    --
    http://www.freelists.org/webpage/oracle-l
    --
    http://www.freelists.org/webpage/oracle-l
  • Lex de Haan at Oct 14, 2004 at 6:44 am
    sounds like a wonderful new paradigm, this Java approach :-)
    generating an enourmous FROM list with many outer joins, just in case...
    a real generator would clean the statement up before sending it to the
    database?
    and even if you want to generate WHERE clauses dynamically,
    in my humble opinion long IN-lists often point to missing attributes in the
    data model.
    an IN-list with 34 values is weird and should be avoided when possible;
    especially if you see that the 34 values are all integers between 2 and 52
    ...

    the more I look at the two statements, the more I doubt that they are
    logically equivalent.
    one plan is showing 5 outer joins and 1 inner join, while the other plan
    shows 4 and 2.

    one more thing I noticed, looking at the execution plans again:
    how is it possible that one plan expects to retrieve 297K rows,
    and the other one 391? There is roughly a factor 1000 involved here...

    are you testing these two statements against exactly the same objects,
    same database, same statistics, same session settings?

    last but not least, looking at all the predicates against the fact table,
    why don't you replace the indexes with bitmap indexes? that would probably
    result
    in a much more efficient access path to that table, in spite of all those
    nasty IN-lists.

    Kind regards,
    Lex.

    visit http://www.naturaljoin.nl <http://www.naturaljoin.nl>

    skype me

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Ganesh Raja
    Sent: Thursday, October 14, 2004 11:36
    To: Lex de Haan
    Cc: oracle-l_at_freelists.org
    Subject: Re: Sincere Advice on Sql Plan - Thanks

    Lex,

    Thanks for that ... I did include the order By On It and There is No
    Change in the plan or the Execution Time. The Only thing added to my
    plan is an Sort Order By stmt..

    As regards the In List ... (I am answering to Marks Comments also)

    This is Part of a Java Program and the Where clause is Built on the
    fly ... That is the Reason u see so many Tables there but none being
    used in the Where or Select List.

    The IN List needs to be there since it also Dynamic in the sense the
    User May select more than one.

    Thanks as always

    Cheers
    Ganesh

    PS : WIll start following the Old and New Paradigm

    On Thu, 14 Oct 2004 10:09:09 +0200, Lex de Haan
    wrote:
    well, if I look at the two SQL statements, the first thing I see is that
    they are *not* equivalent:
    one has an ORDER BY, the other one doesn't. that can make a huge
    difference,
    and it does in this case.

    the expensive plan is using two temporary tables (see the VIEW steps) and
    77M temp space for the sorting (see the SORT ORDER BY step) so what happens
    if you remove the ORDER BY?

    last but not least, if you are in the position to change the data model,
    spend some time on it. if I see SQL statements like this, with many *long*
    IN lists, I always get the feeling that there is room for improvement
    somewhere :-)

    by the way, I know it is common terminology -- but it is unfair/confusing to
    talk about Oracle syntax vs. ANSI syntax --
    because *both* join expressions are *both* ANSI/ISO syntax and Oracle
    syntax... I rather prefer to refer to "old" and "new" syntax, although I
    must admit that it took Oracle a very long time to implement the "new"
    ANSI/ISO join syntax.

    Kind regards,
    Lex.

    -------------------------------
    visit http://www.naturaljoin.nl <http://www.naturaljoin.nl>
    -------------------------------
    skype me




    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Ganesh Raja
    Sent: Wednesday, October 13, 2004 19:29
    To: lex.de.haan_at_naturaljoin.nl
    Cc: oracle-l_at_freelists.org
    Subject: Re: Sincere Advice on Sql Plan - Thanks

    Lex,
    Not sure If Oracle-L Accepts Attachments ..

    Here are the Queries and the Plan.

    Thanks for the help.

    Rgds,
    Ganesh

    On Wed, 13 Oct 2004 19:58:55 +0200, Lex de Haan
    wrote:
    you might want to provide the two statements you are talking about?
    and maybe even the two corresponding execution plans?

    Kind regards,
    Lex.

    -------------------------------
    visit http://www.naturaljoin.nl
    -------------------------------
    skype me


    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Ganesh Raja
    Sent: Wednesday, October 13, 2004 18:54
    To: jkstill_at_gmail.com
    Cc: smishra_97_at_yahoo.com; oracle-l_at_freelists.org
    Subject: Re: Sincere Advice on Sql Plan - Thanks

    Okay I am Going to Piggy Back on this ..

    I have an ANSI SQL which when Rewriiten Using Oracle Syntax it seems
    to take a Better plan to execute it .. Any ideas why this is
    happening.

    There is a Total Change in the plan and it works much faster than the
    ANSI Counterpart

    Any help is appreciated.

    Thanks.

    Cheers
    Ganesh R

    --
    http://www.freelists.org/webpage/oracle-l
    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l
  • Mark W. Farnham at Oct 14, 2004 at 7:14 am

    As regards the In List ... (I am answering to Marks Comments also)
    This is Part of a Java Program and the Where clause is Built on the
    fly ... That is the Reason u see so many Tables there but none being
    used in the Where or Select List.
    The IN List needs to be there since it also Dynamic in the sense the
    User May select more than one.
    All of Lex's stuff, as usual, made sense.

    I want to be clear that I expected that this was a generated statement and
    my point was that if you have the high and low bounds of those in lists in
    hand you can also generate the range restricting predicates. The ins are
    required filters, but the ranges may trigger the use of better plans and if
    correctly generated do not override the filtering provided by the in lists.
    Add the ranges where usable indexes exist and it *MIGHT* give you a better
    plan. You should be able to test this by hand before you alter your
    generator.

    The usual advice about figuring out a way to generate this with bind
    variables should also be injected, though I don't know the rules of
    possibility of doing that in the "NEW" syntax format, and I don't think that
    is the problem you're trying to solve at this moment.

    As for deciphering your two queries in detail for (lack of) equivalence as
    Lex suggested, I wish you good luck. While I find it easy to make changes to
    a query that I can guarantee are isofunctional, I find it difficult, mind
    numbing, and boring to analyze whether two similar complex queries someone
    else has generated are equivalent.

    Regards,

    mwf
  • Ganesh Raja at Oct 14, 2004 at 7:47 am
    Mark,

    Yes ANSI seems a Bit off to me ... ANyways i am going to evaluvale
    what they have done .. It is a common APP which can also have DB2 has
    the database .. [HAte it when they do this .. End of the day u dont
    use the Best Features available on Both]

    So Lex no Bitmaps ....

    Cheers
    Ganesh
    On Thu, 14 Oct 2004 08:17:51 -0400, Mark W. Farnham wrote:
    As regards the In List ... (I am answering to Marks Comments also)
    This is Part of a Java Program and the Where clause is Built on the
    fly ... That is the Reason u see so many Tables there but none being
    used in the Where or Select List.
    The IN List needs to be there since it also Dynamic in the sense the
    User May select more than one.


    All of Lex's stuff, as usual, made sense.

    I want to be clear that I expected that this was a generated statement and
    my point was that if you have the high and low bounds of those in lists in
    hand you can also generate the range restricting predicates. The ins are
    required filters, but the ranges may trigger the use of better plans and if
    correctly generated do not override the filtering provided by the in lists.
    Add the ranges where usable indexes exist and it *MIGHT* give you a better
    plan. You should be able to test this by hand before you alter your
    generator.

    The usual advice about figuring out a way to generate this with bind
    variables should also be injected, though I don't know the rules of
    possibility of doing that in the "NEW" syntax format, and I don't think that
    is the problem you're trying to solve at this moment.

    As for deciphering your two queries in detail for (lack of) equivalence as
    Lex suggested, I wish you good luck. While I find it easy to make changes to
    a query that I can guarantee are isofunctional, I find it difficult, mind
    numbing, and boring to analyze whether two similar complex queries someone
    else has generated are equivalent.

    Regards,

    mwf
    --
    http://www.freelists.org/webpage/oracle-l
  • Ganesh Raja at Oct 14, 2004 at 7:43 am
    Lex,

    Thanks for that .. Yes The New java Pardigm... Getting a Bit
    furstrated with that .

    Yes i did notice that Discrepancy in the rows no ideas where he gets
    that huge count from .

    Thanks for Pointing out the Outer Join Differences .. I rewrote th
    Queryin the new Way and yes it performs better now .. Will again Look
    into this SQL ... to see what they are doing ..

    to be very frank i am not very comfortable with the new way;)

    Thanks as always

    Rgds
    Ganesh
  • Bill thater at Oct 13, 2004 at 1:03 pm

    Okay I am Going to Piggy Back on this ..=20
    =20
    I have an ANSI SQL which when Rewriiten Using Oracle Syntax it seems
    to take a Better plan to execute it .. Any ideas why this is
    happening.
    =20
    There is a Total Change in the plan and it works much faster than the
    ANSI Counterpart
    could it be because the optimizer is designed to use Oracle syntax?

    --
    Bill "Shrek" Thater ORACLE DBA=20=20=20=20=20
    shrek_at_consulant.com=20
    ------------------------------------------------------------------------
    I am my beloved's, and my beloved is mine (Song of
    Solomon 6:3)

    --=20
    ___________________________________________________________
    Sign-up for Ads Free at Mail.com
    http://promo.mail.com/adsfreejump.htm

    --
    http://www.freelists.org/webpage/oracle-l
  • Web Stalkers at Oct 13, 2004 at 3:22 pm
    They couldn't even tell me for sure that it was from Oracle. The "HA
    committee" (all sr damagement) said to "investigate it" which I guess
    means they heard a sales critter say something. Based on my google
    and what they are talking about, I'm guessing they are referring to
    RACGuard. Is that just part of RAC or something to make RAC work
    better?

    Stephen
    try google on Cluster Guard

    Two possible options here. Maybe they're talking about something from an OS vendor (like HP's ServiceGuard), the other is they're talking about RACGuard. More details?

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Web Stalkers
    Sent: Wednesday, October 13, 2004 12:09 PM
    To: oracle-l_at_freelists.org
    Subject: Anyone know anything about ClusterGuard?

    Our Development DBA's have been asked to look into a product for
    Oracle called ClusterGuard. We can't find anything about it.

    I know you guys know all things (at least about Oracle) so I'm hoping
    someone can give me some information.

    TIA
    --
    Stephen Andert
    author of Web Stalkers, Protect Yourself from Internet Criminals and
    Psychopaths
    http://www.rampant-books.com/book_2004_2_stalkers.htm
    --
    http://www.freelists.org/webpage/oracle-l
    --
    Stephen Andert
    author of Web Stalkers, Protect Yourself from Internet Criminals and Psychopaths
    http://www.rampant-books.com/book_2004_2_stalkers.htm
    --
    http://www.freelists.org/webpage/oracle-l
  • Pete Sharman at Oct 13, 2004 at 3:31 pm
    Going from memory now, and 9i memory is a thing of the long lost past for m=
    e. :)

    RAC Guard was an add-on (in some ways) to RAC that allowed you to have an a=
    ctive - standby configuration set up and managed pretty easily. It's all p=
    art of RAC in 10g, so it doesn't exist any more. It might even have gone i=

    n 9iR2 - told you those brain cells were gone. =

    Check the RAC doc on Tahiti. The RAC doc is some of the best doc I've seen=
    from Oracle, primarily because the main author really knows his stuff well=
    . Not that the others don't, but Mark is just outstanding. I still use hi=

    m as a technical resource myself. :)

    =

    Pete
    =

    "Controlling developers is like herding cats."
    Kevin Loney, Oracle DBA Handbook
    =

    "Oh no, it's not. It's much harder than that!"
    Bruce Pihlamae, long-term Oracle DBA

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org =
    On Behalf Of Web Stalkers
    Sent: Thursday, 14 October 2004 6:27 AM
    To: oracle-l_at_freelists.org
    Subject: Re: Anyone know anything about ClusterGuard?

    They couldn't even tell me for sure that it was from Oracle. The "HA
    committee" (all sr damagement) said to "investigate it" which I guess
    means they heard a sales critter say something. Based on my google
    and what they are talking about, I'm guessing they are referring to
    RACGuard. Is that just part of RAC or something to make RAC work
    better?

    Stephen
    try google on Cluster Guard
    =
    Two possible options here. Maybe they're talking about something from an=
    OS vendor (like HP's ServiceGuard), the other is they're talking about RAC=
    Guard. More details?
    =
    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Web Stalkers
    Sent: Wednesday, October 13, 2004 12:09 PM
    To: oracle-l_at_freelists.org
    Subject: Anyone know anything about ClusterGuard?
    =
    Our Development DBA's have been asked to look into a product for
    Oracle called ClusterGuard. We can't find anything about it.
    =
    I know you guys know all things (at least about Oracle) so I'm hoping
    someone can give me some information.
    =
    TIA
    --
    Stephen Andert
    author of Web Stalkers, Protect Yourself from Internet Criminals and
    Psychopaths
    http://www.rampant-books.com/book_2004_2_stalkers.htm
    --
    http://www.freelists.org/webpage/oracle-l
    =
    =

    Stephen Andert
    author of Web Stalkers, Protect Yourself from Internet Criminals and Psycho=
    paths
    http://www.rampant-books.com/book_2004_2_stalkers.htm
  • Powell, Mark D at Oct 13, 2004 at 3:47 pm
    I found the following manuals via searching through my PC based 9.2
    documentation

    Real Application Clusters Real Application Clusters Guard I - Concepts and
    Administration

    Real Application Clusters Real Application Clusters Guard I Configuration
    Guide Release 2 (9.2.0.1.0) for UNIX Systems: AIX-Based Systems, Compaq
    Tru64 UNIX, HP 9000 Series HP-UX, and Sun Solaris

    The program name appears to be pfsctl. I found it on our system in
    $ORACLE_HOME/pfs/bin, but it would appear to be unconfigured. Something
    else to check into. The list just grows and grows.

    Mark D Powell --

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Web Stalkers
    Sent: Wednesday, October 13, 2004 4:27 PM
    To: oracle-l_at_freelists.org
    Subject: Re: Anyone know anything about ClusterGuard?

    They couldn't even tell me for sure that it was from Oracle. The "HA
    committee" (all sr damagement) said to "investigate it" which I guess
    means they heard a sales critter say something. Based on my google
    and what they are talking about, I'm guessing they are referring to
    RACGuard. Is that just part of RAC or something to make RAC work
    better?

    Stephen
    try google on Cluster Guard

    Two possible options here. Maybe they're talking about something from an
    OS vendor (like HP's ServiceGuard), the other is they're talking about
    RACGuard. More details?
    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Web Stalkers
    Sent: Wednesday, October 13, 2004 12:09 PM
    To: oracle-l_at_freelists.org
    Subject: Anyone know anything about ClusterGuard?

    Our Development DBA's have been asked to look into a product for
    Oracle called ClusterGuard. We can't find anything about it.
    --
    http://www.freelists.org/webpage/oracle-l
  • Leonard, George at Oct 14, 2004 at 12:29 am
    Hi there

    Well first you need to know how you current system is coping with the
    available resources,

    Memory, cpu, IO on disks.

    Having pretty much always used SUN for the last 7 yrs in one form or the
    other I love the equipment but a lot have to be said for the new Intel
    Itanium 2 technology out there, I am getting pretty partial to the HP
    offerings considering the same chip/configuration can run HP-UX and
    Linux (Red Hat Advance Server the OS of choice here).

    Add this to some nice internal DAS storage or a Hitachi San and you have
    a pretty sweet solution,

    But getting back to reality, know how you current environment is doing
    under normal and load conditions before looking to size something new,

    Then also ask the considerations/requirements management want next,
    redundancy etc.

    George
    =20________________________________________________
    George Leonard
    Oracle Database Administrator
    New Dawn Technologies @ Wesbank
    E-mail:gleonard_at_wesbank.co.za
    =20
    You Have The Obligation to Inform One Honestly of the risk, And As a
    Person
    You Are Committed to Educate Yourself to the Total Risk In Any Activity!
    Once Informed & Totally Aware of the Risk,
    Every Fool Has the Right to Kill or Injure Themselves as They See Fit!
    =20

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Rivaldi Bahar
    Sent: 14 October 2004 7:29 AM
    To: oracle-l_at_freelists.org
    Subject: OT : Hardware evaluation

    Hi Listers,

    I'm using Oracle 9.1, Solaris, SAP application.

    Damagement asked me to make recommendation for future system based on
    evalution of current system i.e :

    Do we need to buy a new more powerful machine, more disks, cpu, etc.

    I'm just wondering what aspects to monitor, what kind of report should I
    submit.

    Sounds like capacity management or planning.

    If you have a checklist about what to do (and I think unix command to do
    so) or example of report, it would help.

    Thanks.

    Do You Yahoo!?
    Tired of spam? Yahoo! Mail has the best spam protection around=20
    http://mail.yahoo.com=20

    --
    http://www.freelists.org/webpage/oracle-l
    _________________________________________________________________________=
    __________________________

    The views expressed in this email are, unless otherwise stated, those of =
    the author and not those
    of the FirstRand Banking Group an Authorised Financial Service Provider o=
    r its management.
    The information in this e-mail is confidential and is intended solely for=
    =20the addressee.
    Access to this e-mail by anyone else is unauthorised.
    If you are not the intended recipient, any disclosure, copying, distribut=
    ion or any action taken or=20
    omitted in reliance on this, is prohibited and may be unlawful.
    Whilst all reasonable steps are taken to ensure the accuracy and integrit=
    y of information and data=20
    transmitted electronically and to preserve the confidentiality thereof, n=
    o liability or=20
    responsibility whatsoever is accepted if information or data is, for what=
    ever reason, corrupted=20
    or does not reach its intended destination.

    =20 ________________________________
    --
    http://www.freelists.org/webpage/oracle-l

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedOct 12, '04 at 1:38p
activeOct 14, '04 at 7:47a
posts23
users13
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase