Results 1 to 14 of 14
  1. #1
    Join Date
    Jan 2003
    Location
    The Netherlands
    Posts
    149

    Unanswered: Old and new join-syntax

    Hello there,
    When I execute a query with lots of joins with the old syntax my query goed in 200 msec and with the new syntax it takes 4 to5 seconds. it has nothing to do with the cache. the explain plan is also equal. It seems that the parser just takes that many time. can anyone help? these are the two query's: OLD SYNTAX(FAST):
    SELECT CN0.EQMID,CN0.EQMTEXT1,CN0.EQMDEPID,CN0.EQMEQMTID, CN0.EQMSITID,CN0.EQMIMAGEFILE,CN0.EQMDESCR,CN0."_E QMGBSCODE",CN0.EQMPURCHPRICE,CN0."_EQMDEPRYEAR",CN 0.EQMCCRID,CN0."_EQMSVCVDRID", CN0.EQMREPLCOST,CN0."_EQMDEPRRID",CN0."_EQMCSTTID" ,CN0."_EQMVDRSYSCODE",CN0."_EQMMNFID",CN0.EQMVDRID ,CN0."_EQMSKCID",CN0."_EQMCRMID",CN0.EQMMAINTBUDGE T,CN0.EQMBUDGETNY,CN0."_EQMSFTLID",
    CN0."_EQMEMPID",CN0.EQMTYPENR,CN0.EQMSERIALNR,CN0. EQMSTATE,CN0.EQMDATESTATE,CN0.EQMAOCID,CN0.EQMMANU FYEAR,CN0.EQMSETUPDATE,CN0.EQMREMOVALREASON,CN0.EQ MINSTDATE,CN0.EQMWARRENTDATE,
    CN0.EQMLASTPMMODEL,CN0.EQMLASTPMMAINTDATE,CN0.EQMD EPRECIATION,CN0.EQMBUDGETLY,CN0.EQMLOCATION,CN0.EQ MMAINTYEAR,CN0.EQMCOSTLY,CN0.EQMMAINTTOTAL,CN0.EQM SETUPDATE,CN0.EQMSETUPEMPID,CN0."_EQMCONTROL",
    CN0.EQMINSTDATE,CN0.EQMMISSINGDATE,CN0.EQMMISSINGE MPID,CN0.EQMACTIVEDATE,CN0.EQMACTIVEEMPID,CN0.EQMR EMOVALDATE,CN0.EQMREMOVALEMPID,CN0.EQMLASTEQMMID,C N0.EQMLASTEQMMDATE,CN0.EQMID,CN0.EQMRECSTATUS,
    CN0.EQMCONTEXT,CN0.TIME_STAMP, FKEQMDEPID0.DEPID,FKEQMDEPID0.DEPCCRID,FKEQMDEPID0 .DEPDESCR,FKEQMDEPID0.DEPID,FKEQMDEPID0.DEPRECSTAT US,FKEQMDEPID0.DEPCONTEXT,FKEQMVDRID0.VDRDESCR,FKE QMVDRID0.VDRID,
    FKEQMVDRID0.VDRRECSTATUS,FKEQMVDRID0.VDRCONTEXT,FK EQMPARTOFEQMID0.EQMID,FKEQMPARTOFEQMID0.EQMRECSTAT US,FKEQMPARTOFEQMID0.EQMCONTEXT,FKEQMMISSINGEMPID0 .EMPDESCR,FKEQMMISSINGEMPID0.EMPID,
    FKEQMMISSINGEMPID0.EMPRECSTATUS,FKEQMMISSINGEMPID0 .EMPCONTEXT,FKEQMACTIVEEMPID0.EMPDESCR,FKEQMACTIVE EMPID0.EMPID,FKEQMACTIVEEMPID0.EMPRECSTATUS,FKEQMA CTIVEEMPID0.EMPCONTEXT,FKEQMREMOVALEMPID0.EMPDESCR ,
    FKEQMREMOVALEMPID0.EMPID,FKEQMREMOVALEMPID0.EMPREC STATUS,FKEQMREMOVALEMPID0.EMPCONTEXT,FKEQMSITID0.S ITDESCR,FKEQMSITID0.SITID,FKEQMSITID0.SITRECSTATUS ,FKEQMSITID0.SITCONTEXT,FKEQMLASTEQMMID0.EQMMID,
    FKEQMLASTEQMMID0.EQMMRECSTATUS,FKEQMLASTEQMMID0.EQ MMCONTEXT,FK_EQMSFTLID0."_SFTLID",FK_EQMSFTLID0.SF TLRECSTATUS,FK_EQMMNFID0."_MNFDESCR",FK_EQMMNFID0. "_MNFID",FK_EQMMNFID0.MNFRECSTATUS,
    FKEQMAOCID0.AOCDESCR,FKEQMAOCID0.AOCID,FKEQMAOCID0 .AOCRECSTATUS,FKEQMAOCID0.AOCCONTEXT,FK_EQMCSTTID0 .CSTTID,FK_EQMCSTTID0.CSTTDESCR,FK_EQMCSTTID0.CSTT ID,FK_EQMCSTTID0.CSTTRECSTATUS,FK_EQMCSTTID0.CSTTC ONTEXT,
    FK_EQMSKCID0.SKCDESCR1,FK_EQMSKCID0.SKCID,FK_EQMSK CID0.SKCRECSTATUS,FK_EQMSKCID0.SKCCONTEXT,FK_EQMSV CVDRID0.VDRDESCR,FK_EQMSVCVDRID0.VDRID,FK_EQMSVCVD RID0.VDRRECSTATUS,FK_EQMSVCVDRID0.VDRCONTEXT,FKEQM SPCID0.SPCDESCR,
    FKEQMSPCID0.SPCID,FKEQMSPCID0.SPCRECSTATUS,FKEQMSP CID0.SPCCONTEXT,FK_EQMCRMID0.CRMDESCR1,FK_EQMCRMID 0.CRMID,FK_EQMCRMID0.CRMRECSTATUS,FK_EQMCRMID0.CRM CONTEXT,FK_EQMEMPID0.EMPDESCR,
    FK_EQMEMPID0.EMPID,FK_EQMEMPID0.EMPRECSTATUS,FK_EQ MEMPID0.EMPCONTEXT,FK_EQMDEPRRID0."_DEPRRDESCR",FK _EQMDEPRRID0."_DEPRRID",FK_EQMDEPRRID0.DEPRRRECSTA TUS,FKEQMCCRID0.CCRID,
    FKEQMCCRID0.CCRDESCR1,FKEQMCCRID0.CCRID,FKEQMCCRID 0.CCRRECSTATUS,FKEQMCCRID0.CCRCONTEXT,FKEQMSETUPEM PID0.EMPDESCR,FKEQMSETUPEMPID0.EMPID,FKEQMSETUPEMP ID0.EMPRECSTATUS,FKEQMSETUPEMPID0.EMPCONTEXT,
    CN0.EQMEXCHANGEPARTSTATUS
    FROM ISH.EQUIPMENT CN0
    , ISH.DEPARTMENT FKEQMDEPID0
    , ISH.VENDOR FKEQMVDRID0
    , ISH.EQUIPMENT FKEQMPARTOFEQMID0
    , ISH.EMPLOYEE FKEQMMISSINGEMPID0
    , ISH.EMPLOYEE FKEQMACTIVEEMPID0
    , ISH.EMPLOYEE FKEQMREMOVALEMPID0
    , ISH.SITE FKEQMSITID0
    , ISH.EQUIPMENTMOVE FKEQMLASTEQMMID0
    , ISH."_SAFETYLEVEL" FK_EQMSFTLID0
    , ISH."_MANUFACT" FK_EQMMNFID0
    , ISH.AOC FKEQMAOCID0
    , ISH.COSTTYPE FK_EQMCSTTID0
    , ISH.SKILLCAT FK_EQMSKCID0
    , ISH.VENDOR FK_EQMSVCVDRID0
    , ISH.SPACE FKEQMSPCID0
    , ISH.CRAFTSMAN FK_EQMCRMID0
    , ISH.EMPLOYEE FK_EQMEMPID0
    , ISH."_DEPRECIATIONREASON" FK_EQMDEPRRID0
    , ISH.COSTCENTER FKEQMCCRID0
    , ISH.EMPLOYEE FKEQMSETUPEMPID0
    WHERE (CN0.EQMID = '0.65A')
    AND FKEQMDEPID0.DEPID(+) = CN0.EQMDEPID
    AND FKEQMVDRID0.VDRID(+) = CN0.EQMVDRID
    AND FKEQMPARTOFEQMID0.EQMID(+) = CN0.EQMPARTOFEQMID
    AND FKEQMMISSINGEMPID0.EMPID(+) = CN0.EQMMISSINGEMPID
    AND FKEQMACTIVEEMPID0.EMPID(+) = CN0.EQMACTIVEEMPID
    AND FKEQMREMOVALEMPID0.EMPID(+) = CN0.EQMREMOVALEMPID
    AND FKEQMSITID0.SITID(+) = CN0.EQMSITID
    AND FKEQMLASTEQMMID0.EQMMID(+) = CN0.EQMLASTEQMMID
    AND FK_EQMSFTLID0."_SFTLID"(+) = CN0."_EQMSFTLID"
    AND FK_EQMMNFID0."_MNFID"(+) = CN0."_EQMMNFID"
    AND FKEQMAOCID0.AOCID(+) = CN0.EQMAOCID
    AND FK_EQMCSTTID0.CSTTID(+) = CN0."_EQMCSTTID"
    AND FK_EQMSKCID0.SKCID(+) = CN0."_EQMSKCID"
    AND FK_EQMSVCVDRID0.VDRID(+) = CN0."_EQMSVCVDRID"
    AND FKEQMSPCID0.SPCID(+) = CN0.EQMSPCID
    AND FK_EQMCRMID0.CRMID(+) = CN0."_EQMCRMID"
    AND FK_EQMEMPID0.EMPID(+) = CN0."_EQMEMPID"
    AND FK_EQMDEPRRID0."_DEPRRID"(+) = CN0."_EQMDEPRRID"
    AND FKEQMCCRID0.CCRID(+) = CN0.EQMCCRID
    AND FKEQMSETUPEMPID0.EMPID(+) = CN0.EQMSETUPEMPID

    NEW SYNTAX(SLOW): SELECT CN0.EQMID,CN0.EQMTEXT1,CN0.EQMDEPID,CN0.EQMEQMTID, CN0.EQMSITID,CN0.EQMIMAGEFILE,CN0.EQMDESCR,CN0."_E QMGBSCODE",CN0.EQMPURCHPRICE,CN0."_EQMDEPRYEAR",CN 0.EQMCCRID,CN0."_EQMSVCVDRID",CN0.EQMREPLCOST,CN0. "_EQMDEPRRID",CN0."_EQMCSTTID",CN0."_EQMVDRSYSCODE ",CN0."_EQMMNFID",CN0.EQMVDRID,CN0."_EQMSKCID",CN0 ."_EQMCRMID",CN0.EQMMAINTBUDGET,CN0.EQMBUDGETNY,CN 0."_EQMSFTLID",CN0."_EQMEMPID",CN0.EQMSPCID,CN0.EQ MPARTOFEQMID,CN0.EQMTYPENR,CN0.EQMSERIALNR,CN0.EQM STATE,CN0.EQMDATESTATE,CN0.EQMAOCID,CN0.EQMMANUFYE AR,CN0.EQMSETUPDATE,CN0.EQMREMOVALREASON,CN0.EQMIN STDATE,CN0.EQMWARRENTDATE,CN0.EQMLASTPMMODEL,CN0.E QMDEPRECIATION,CN0.EQMBUDGETLY,CN0.EQMLOCATION,CN0 .EQMMAINTYEAR,CN0.EQMCOSTLY,CN0.EQMMAINTTOTAL,CN0. EQMSETUPDATE,CN0.EQMSETUPEMPID,CN0."_EQMCONTROL",
    CN0.EQMINSTDATE,CN0.EQMMISSINGDATE,CN0.EQMMISSINGE MPID,CN0.EQMACTIVEDATE,CN0.EQMACTIVEEMPID,CN0.EQMR EMOVALDATE,CN0.EQMREMOVALEMPID,CN0.EQMLASTEQMMID,C N0.EQMLASTEQMMDATE,CN0.EQMID,CN0.EQMRECSTATUS,
    CN0.EQMCONTEXT,CN0.TIME_STAMP, FKEQMDEPID0.DEPID,FKEQMDEPID0.DEPCCRID,FKEQMDEPID0 .DEPDESCR,FKEQMDEPID0.DEPID,FKEQMDEPID0.DEPRECSTAT US,FKEQMDEPID0.DEPCONTEXT,FKEQMVDRID0.VDRDESCR,FKE QMVDRID0.VDRID,
    FKEQMVDRID0.VDRRECSTATUS,FKEQMVDRID0.VDRCONTEXT,FK EQMPARTOFEQMID0.EQMID,FKEQMPARTOFEQMID0.EQMRECSTAT US,FKEQMPARTOFEQMID0.EQMCONTEXT,FKEQMMISSINGEMPID0 .EMPDESCR,FKEQMMISSINGEMPID0.EMPID,
    FKEQMMISSINGEMPID0.EMPRECSTATUS,FKEQMMISSINGEMPID0 .EMPCONTEXT,FKEQMACTIVEEMPID0.EMPDESCR,FKEQMACTIVE EMPID0.EMPID,FKEQMACTIVEEMPID0.EMPRECSTATUS,FKEQMA CTIVEEMPID0.EMPCONTEXT,FKEQMREMOVALEMPID0.EMPDESCR ,
    FKEQMREMOVALEMPID0.EMPID,FKEQMREMOVALEMPID0.EMPREC STATUS,FKEQMREMOVALEMPID0.EMPCONTEXT,FKEQMSITID0.S ITDESCR,FKEQMSITID0.SITID,FKEQMSITID0.SITRECSTATUS ,FKEQMSITID0.SITCONTEXT,FKEQMLASTEQMMID0.EQMMID,
    FKEQMLASTEQMMID0.EQMMRECSTATUS,FKEQMLASTEQMMID0.EQ MMCONTEXT,FK_EQMSFTLID0."_SFTLID",FK_EQMSFTLID0.SF TLRECSTATUS,FK_EQMMNFID0."_MNFDESCR",FK_EQMMNFID0. "_MNFID",FK_EQMMNFID0.MNFRECSTATUS,
    FKEQMAOCID0.AOCDESCR,FKEQMAOCID0.AOCID,FKEQMAOCID0 .AOCRECSTATUS,FKEQMAOCID0.AOCCONTEXT,FK_EQMCSTTID0 .CSTTID,FK_EQMCSTTID0.CSTTDESCR,FK_EQMCSTTID0.CSTT ID,FK_EQMCSTTID0.CSTTRECSTATUS,FK_EQMCSTTID0.CSTTC ONTEXT,
    FK_EQMSKCID0.SKCDESCR1,FK_EQMSKCID0.SKCID,FK_EQMSK CID0.SKCRECSTATUS,FK_EQMSKCID0.SKCCONTEXT,FK_EQMSV CVDRID0.VDRDESCR,FK_EQMSVCVDRID0.VDRID,FK_EQMSVCVD RID0.VDRRECSTATUS,FK_EQMSVCVDRID0.VDRCONTEXT,FKEQM SPCID0.SPCDESCR,
    FKEQMSPCID0.SPCID,FKEQMSPCID0.SPCRECSTATUS,FKEQMSP CID0.SPCCONTEXT,FK_EQMCRMID0.CRMDESCR1,FK_EQMCRMID 0.CRMID,FK_EQMCRMID0.CRMRECSTATUS,FK_EQMCRMID0.CRM CONTEXT,FK_EQMEMPID0.EMPDESCR,
    FK_EQMEMPID0.EMPID,FK_EQMEMPID0.EMPRECSTATUS,FK_EQ MEMPID0.EMPCONTEXT,FK_EQMDEPRRID0."_DEPRRDESCR",FK _EQMDEPRRID0."_DEPRRID",FK_EQMDEPRRID0.DEPRRRECSTA TUS,FKEQMCCRID0.CCRID,
    FKEQMCCRID0.CCRDESCR1,FKEQMCCRID0.CCRID,FKEQMCCRID 0.CCRRECSTATUS,FKEQMCCRID0.CCRCONTEXT,FKEQMSETUPEM PID0.EMPDESCR,FKEQMSETUPEMPID0.EMPID,FKEQMSETUPEMP ID0.EMPRECSTATUS,FKEQMSETUPEMPID0.EMPCONTEXT,
    CN0.EQMEXCHANGEPARTSTATUS
    FROM ISH.EQUIPMENT CN0
    LEFT OUTER JOIN ISH.DEPARTMENT FKEQMDEPID0 ON FKEQMDEPID0.DEPID = CN0.EQMDEPID
    LEFT OUTER JOIN ISH.VENDOR FKEQMVDRID0 ON FKEQMVDRID0.VDRID = CN0.EQMVDRID
    LEFT OUTER JOIN ISH.EQUIPMENT FKEQMPARTOFEQMID0 ON FKEQMPARTOFEQMID0.EQMID = CN0.EQMPARTOFEQMID
    LEFT OUTER JOIN ISH.EMPLOYEE FKEQMMISSINGEMPID0 ON FKEQMMISSINGEMPID0.EMPID = CN0.EQMMISSINGEMPID
    LEFT OUTER JOIN ISH.EMPLOYEE FKEQMACTIVEEMPID0 ON FKEQMACTIVEEMPID0.EMPID = CN0.EQMACTIVEEMPID
    LEFT OUTER JOIN ISH.EMPLOYEE FKEQMREMOVALEMPID0 ON FKEQMREMOVALEMPID0.EMPID = CN0.EQMREMOVALEMPID
    LEFT OUTER JOIN ISH.SITE FKEQMSITID0 ON FKEQMSITID0.SITID = CN0.EQMSITID
    LEFT OUTER JOIN ISH.EQUIPMENTMOVE FKEQMLASTEQMMID0 ON FKEQMLASTEQMMID0.EQMMID = CN0.EQMLASTEQMMID
    LEFT OUTER JOIN ISH."_SAFETYLEVEL" FK_EQMSFTLID0 ON FK_EQMSFTLID0."_SFTLID" = CN0."_EQMSFTLID"
    LEFT OUTER JOIN ISH."_MANUFACT" FK_EQMMNFID0 ON FK_EQMMNFID0."_MNFID" = CN0."_EQMMNFID"
    LEFT OUTER JOIN ISH.AOC FKEQMAOCID0 ON FKEQMAOCID0.AOCID = CN0.EQMAOCID
    LEFT OUTER JOIN ISH.COSTTYPE FK_EQMCSTTID0 ON FK_EQMCSTTID0.CSTTID = CN0."_EQMCSTTID"
    LEFT OUTER JOIN ISH.SKILLCAT FK_EQMSKCID0 ON FK_EQMSKCID0.SKCID = CN0."_EQMSKCID"
    LEFT OUTER JOIN ISH.VENDOR FK_EQMSVCVDRID0 ON FK_EQMSVCVDRID0.VDRID = CN0."_EQMSVCVDRID"
    LEFT OUTER JOIN ISH.SPACE FKEQMSPCID0 ON FKEQMSPCID0.SPCID = CN0.EQMSPCID
    LEFT OUTER JOIN ISH.CRAFTSMAN FK_EQMCRMID0 ON FK_EQMCRMID0.CRMID = CN0."_EQMCRMID"
    LEFT OUTER JOIN ISH.EMPLOYEE FK_EQMEMPID0 ON FK_EQMEMPID0.EMPID = CN0."_EQMEMPID"
    LEFT OUTER JOIN ISH."_DEPRECIATIONREASON" FK_EQMDEPRRID0 ON FK_EQMDEPRRID0."_DEPRRID" = CN0."_EQMDEPRRID"
    LEFT OUTER JOIN ISH.COSTCENTER FKEQMCCRID0 ON FKEQMCCRID0.CCRID = CN0.EQMCCRID
    LEFT OUTER JOIN ISH.EMPLOYEE FKEQMSETUPEMPID0 ON FKEQMSETUPEMPID0.EMPID = CN0.EQMSETUPEMPID
    WHERE (CN0.EQMID = '0.65A') ORDER BY CN0.EQMID

  2. #2
    Join Date
    Jul 2003
    Location
    US
    Posts
    314

    Arrow God Save You

    Hi
    I would recommend to take out all the extra information, like column names being returned, which are not a part of the join and re-post the query, so it makes it possible to go through the query with a fresh mind.

    It would enable subscribers to catch the meaning/purpose of query in a better manner.
    Thanx and Regards
    Aruneesh

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    yeah, what he said!

    Also, when asking for help, it is considerate to post your question and code in a readable format. I mean, let's be serious for a minute here! There is no way anyone is going to attempt to read through the craziness of what you posted up there.

    Put some line-breaks in there. Your opening line is 318 characters long.

    I am happy to help where I can, but I got a headache attempting to read your post.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Old and new join-syntax

    Originally posted by thepercival
    Hello there,
    When I execute a query with lots of joins with the old syntax my query goed in 200 msec and with the new syntax it takes 4 to5 seconds. it has nothing to do with the cache. the explain plan is also equal. It seems that the parser just takes that many time. can anyone help? these are the two query's:
    <snip>
    That can't be right - I can't believe Oracle takes 4-5 seconds to PARSE the SQL. If that's true, then if you run the new version for a second time it should then only take ~200msec since it won't have to parse again.

    Are you SURE the explain plan outputs are the same?

  5. #5
    Join Date
    Jan 2003
    Location
    The Netherlands
    Posts
    149

    second time faster

    Hello there,

    Yes that's correct. The second time is faster, is this because it is in the cache? Here is my query again, but without all the columns:
    NEW SYNTAX: (slow 4 to 5 second)
    SELECT CN0.EQMID
    FROM ISH.EQUIPMENT CN0
    LEFT OUTER JOIN ISH.DEPARTMENT FKEQMDEPID0 ON FKEQMDEPID0.DEPID = CN0.EQMDEPID
    LEFT OUTER JOIN ISH.VENDOR FKEQMVDRID0 ON FKEQMVDRID0.VDRID = CN0.EQMVDRID
    LEFT OUTER JOIN ISH.EQUIPMENT FKEQMPARTOFEQMID0 ON FKEQMPARTOFEQMID0.EQMID = CN0.EQMPARTOFEQMID
    LEFT OUTER JOIN ISH.EMPLOYEE FKEQMMISSINGEMPID0 ON FKEQMMISSINGEMPID0.EMPID = CN0.EQMMISSINGEMPID
    LEFT OUTER JOIN ISH.EMPLOYEE FKEQMACTIVEEMPID0 ON FKEQMACTIVEEMPID0.EMPID = CN0.EQMACTIVEEMPID
    LEFT OUTER JOIN ISH.EMPLOYEE FKEQMREMOVALEMPID0 ON FKEQMREMOVALEMPID0.EMPID = CN0.EQMREMOVALEMPID
    LEFT OUTER JOIN ISH.SITE FKEQMSITID0 ON FKEQMSITID0.SITID = CN0.EQMSITID
    LEFT OUTER JOIN ISH.EQUIPMENTMOVE FKEQMLASTEQMMID0 ON FKEQMLASTEQMMID0.EQMMID = CN0.EQMLASTEQMMID
    LEFT OUTER JOIN ISH."_SAFETYLEVEL" FK_EQMSFTLID0 ON FK_EQMSFTLID0."_SFTLID" = CN0."_EQMSFTLID"
    LEFT OUTER JOIN ISH."_MANUFACT" FK_EQMMNFID0 ON FK_EQMMNFID0."_MNFID" = CN0."_EQMMNFID"
    LEFT OUTER JOIN ISH.AOC FKEQMAOCID0 ON FKEQMAOCID0.AOCID = CN0.EQMAOCID
    LEFT OUTER JOIN ISH.COSTTYPE FK_EQMCSTTID0 ON FK_EQMCSTTID0.CSTTID = CN0."_EQMCSTTID"
    LEFT OUTER JOIN ISH.SKILLCAT FK_EQMSKCID0 ON FK_EQMSKCID0.SKCID = CN0."_EQMSKCID"
    LEFT OUTER JOIN ISH.VENDOR FK_EQMSVCVDRID0 ON FK_EQMSVCVDRID0.VDRID = CN0."_EQMSVCVDRID"
    LEFT OUTER JOIN ISH.SPACE FKEQMSPCID0 ON FKEQMSPCID0.SPCID = CN0.EQMSPCID
    LEFT OUTER JOIN ISH.CRAFTSMAN FK_EQMCRMID0 ON FK_EQMCRMID0.CRMID = CN0."_EQMCRMID"
    LEFT OUTER JOIN ISH.EMPLOYEE FK_EQMEMPID0 ON FK_EQMEMPID0.EMPID = CN0."_EQMEMPID"
    LEFT OUTER JOIN ISH."_DEPRECIATIONREASON" FK_EQMDEPRRID0 ON FK_EQMDEPRRID0."_DEPRRID" = CN0."_EQMDEPRRID"
    LEFT OUTER JOIN ISH.COSTCENTER FKEQMCCRID0 ON FKEQMCCRID0.CCRID = CN0.EQMCCRID
    LEFT OUTER JOIN ISH.EMPLOYEE FKEQMSETUPEMPID0 ON FKEQMSETUPEMPID0.EMPID = CN0.EQMSETUPEMPID
    WHERE (CN0.EQMID = '0.65A') ORDER BY CN0.EQMID

    OLD SYNTAX:
    SELECT CN0.EQMID
    FROM ISH.EQUIPMENT CN0
    , ISH.DEPARTMENT FKEQMDEPID0
    , ISH.VENDOR FKEQMVDRID0
    , ISH.EQUIPMENT FKEQMPARTOFEQMID0
    , ISH.EMPLOYEE FKEQMMISSINGEMPID0
    , ISH.EMPLOYEE FKEQMACTIVEEMPID0
    , ISH.EMPLOYEE FKEQMREMOVALEMPID0
    , ISH.SITE FKEQMSITID0
    , ISH.EQUIPMENTMOVE FKEQMLASTEQMMID0
    , ISH."_SAFETYLEVEL" FK_EQMSFTLID0
    , ISH."_MANUFACT" FK_EQMMNFID0
    , ISH.AOC FKEQMAOCID0
    , ISH.COSTTYPE FK_EQMCSTTID0
    , ISH.SKILLCAT FK_EQMSKCID0
    , ISH.VENDOR FK_EQMSVCVDRID0
    , ISH.SPACE FKEQMSPCID0
    , ISH.CRAFTSMAN FK_EQMCRMID0
    , ISH.EMPLOYEE FK_EQMEMPID0
    , ISH."_DEPRECIATIONREASON" FK_EQMDEPRRID0
    , ISH.COSTCENTER FKEQMCCRID0
    , ISH.EMPLOYEE FKEQMSETUPEMPID0
    WHERE (CN0.EQMID = '0.65A')
    AND FKEQMDEPID0.DEPID(+) = CN0.EQMDEPID
    AND FKEQMVDRID0.VDRID(+) = CN0.EQMVDRID
    AND FKEQMPARTOFEQMID0.EQMID(+) = CN0.EQMPARTOFEQMID
    AND FKEQMMISSINGEMPID0.EMPID(+) = CN0.EQMMISSINGEMPID
    AND FKEQMACTIVEEMPID0.EMPID(+) = CN0.EQMACTIVEEMPID
    AND FKEQMREMOVALEMPID0.EMPID(+) = CN0.EQMREMOVALEMPID
    AND FKEQMSITID0.SITID(+) = CN0.EQMSITID
    AND FKEQMLASTEQMMID0.EQMMID(+) = CN0.EQMLASTEQMMID
    AND FK_EQMSFTLID0."_SFTLID"(+) = CN0."_EQMSFTLID"
    AND FK_EQMMNFID0."_MNFID"(+) = CN0."_EQMMNFID"
    AND FKEQMAOCID0.AOCID(+) = CN0.EQMAOCID
    AND FK_EQMCSTTID0.CSTTID(+) = CN0."_EQMCSTTID"
    AND FK_EQMSKCID0.SKCID(+) = CN0."_EQMSKCID"
    AND FK_EQMSVCVDRID0.VDRID(+) = CN0."_EQMSVCVDRID"
    AND FKEQMSPCID0.SPCID(+) = CN0.EQMSPCID
    AND FK_EQMCRMID0.CRMID(+) = CN0."_EQMCRMID"
    AND FK_EQMEMPID0.EMPID(+) = CN0."_EQMEMPID"
    AND FK_EQMDEPRRID0."_DEPRRID"(+) = CN0."_EQMDEPRRID"
    AND FKEQMCCRID0.CCRID(+) = CN0.EQMCCRID
    AND FKEQMSETUPEMPID0.EMPID(+) = CN0.EQMSETUPEMPID

  6. #6
    Join Date
    Jan 2003
    Location
    The Netherlands
    Posts
    149

    correction

    In the message above I meant second time instead of first.

  7. #7
    Join Date
    Jan 2003
    Location
    The Netherlands
    Posts
    149

    has anyone a clue already?

    Doe anyone has a clue already?

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: has anyone a clue already?

    Originally posted by thepercival
    Doe anyone has a clue already?
    Try using sql_trace and tkprof, that will show the CPU and elapsed times for parse, execute and fetch. Change both queries slightly to ensure they are not in the cache. You should expect the parse time for the ANSI version to be much higher if that is indeed the issue (I still find it hard to believe it could take Oracle several seconds to PARSE a statement!!!)

  9. #9
    Join Date
    Jan 2003
    Location
    The Netherlands
    Posts
    149

    trace results

    make two query's, with both a lot of joins and both the same functionality. I have one query with the old join-syntax and one with the new ISO/ANSI syntax. hey have the same execution_plan but when i watch it with the following:
    select * from v$sqlarea where or sql_text like '%PPPPP%'
    ( I put '%PPPPP%' in the query to extract it from the table. )

    I see that my shared_memory is 60 time as big my runtime_mem is 7 times as big and the cpu time is 80 times as big. So the new ANSI/ISO goes slomo in comparison with the old syntax, but they use the same execution plan. does anyone give me a hint?

  10. #10
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: trace results

    Originally posted by thepercival
    make two query's, with both a lot of joins and both the same functionality. I have one query with the old join-syntax and one with the new ISO/ANSI syntax. hey have the same execution_plan but when i watch it with the following:
    select * from v$sqlarea where or sql_text like '%PPPPP%'
    ( I put '%PPPPP%' in the query to extract it from the table. )

    I see that my shared_memory is 60 time as big my runtime_mem is 7 times as big and the cpu time is 80 times as big. So the new ANSI/ISO goes slomo in comparison with the old syntax, but they use the same execution plan. does anyone give me a hint?
    I suggest you Ask Tom at asktom.oracle.com. But he will want to see evidence, like output from tkprof for both queries, including the explain plan and statistics.

  11. #11
    Join Date
    Jan 2003
    Location
    The Netherlands
    Posts
    149

    here are the tkstats

    OLD SYNTAX: (FAST)

    call count cpu elapsed disk query current rows
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    Parse 2 0.04 0.03 0 0 0 0
    Execute 2 0.00 0.00 0 0 0 0
    Fetch 2 0.00 0.00 0 62 0 2
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    total 6 0.04 0.04 0 62 0 2

    Misses in library cache during parse: 1
    Optimizer goal: CHOOSE
    Parsing user id: 23

    NEW SYNTAX: (SLOW)
    call count cpu elapsed disk query current rows
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    Parse 1 3.80 3.83 0 0 0 0
    Execute 1 0.00 0.00 0 0 0 0
    Fetch 1 0.00 0.00 0 31 0 1
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    total 3 3.80 3.83 0 31 0 1

    Misses in library cache during parse: 1
    Optimizer goal: CHOOSE
    Parsing user id: 23

  12. #12
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: here are the tkstats

    Originally posted by thepercival
    OLD SYNTAX: (FAST)

    call count cpu elapsed disk query current rows
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    Parse 2 0.04 0.03 0 0 0 0
    Execute 2 0.00 0.00 0 0 0 0
    Fetch 2 0.00 0.00 0 62 0 2
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    total 6 0.04 0.04 0 62 0 2

    Misses in library cache during parse: 1
    Optimizer goal: CHOOSE
    Parsing user id: 23

    NEW SYNTAX: (SLOW)
    call count cpu elapsed disk query current rows
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    Parse 1 3.80 3.83 0 0 0 0
    Execute 1 0.00 0.00 0 0 0 0
    Fetch 1 0.00 0.00 0 31 0 1
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    total 3 3.80 3.83 0 31 0 1

    Misses in library cache during parse: 1
    Optimizer goal: CHOOSE
    Parsing user id: 23
    3.8 seconds to parse with ANSI join syntax - amazing! I'd expect some overhead of converting from ANSI to "native" Oracle syntax, but not anything so noticeable as that. Not sure what you can do about it, apart from consult Metalink or Oracle Support.

  13. #13
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    I can't help noticing that it was the second execution of the first statement and the first execution of the second statement. I doubt it would make that much difference but could you try again with identical parse counts.

    Hth
    Bill

  14. #14
    Join Date
    Jan 2003
    Location
    The Netherlands
    Posts
    149

    tried

    already tried that and didn't make an diff. , thanks although!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •