1 2 Previous Next 18 Replies Latest reply: Mar 6, 2013 1:52 PM by Rahul_India RSS

    Tuning a query -First time

    Rahul_India
      How can i write this query in a better way
      DB-11GR2
      Please do let me know what additional data you need from me.
      with t as
      (
      select this_.ORG_ID as ORG1_67_1_, this_.ORGANIZATION_NAME as ORGANIZA2_67_1_, this_.DISTRICT_ID as DISTRICT3_67_1_, 
      this_.SUB_DISTRICT_ID as SUB4_67_1_, this_.CREATE_DATE as CREATE5_67_1_, this_.CREATE_BY as CREATE6_67_1_, this_.UPDATE_DATE 
      as UPDATE7_67_1_, this_.UPDATE_BY as UPDATE8_67_1_, this_.PID as PID67_1_, this_.OPCO_GEO_ID as OPCO10_67_1_, this_.SOURCE_SYSTEM_ID 
      as SOURCE11_67_1_, this_.LOGICAL_DELETE_INDICATOR as LOGICAL12_67_1_, this_.ORG_TYPE_ID as ORG13_67_1_, this_.PARENT_ORG_ID as 
      PARENT14_67_1_, addressali1_.ADDRESS_ID as ADDRESS1_62_0_, addressali1_.SALUTATION as SALUTATION62_0_, addressali1_.MIDDLE_NAME 
      as MIDDLE3_62_0_, addressali1_.CREATE_DATE as CREATE4_62_0_, addressali1_.CREATE_BY as CREATE5_62_0_, addressali1_.UPDATE_DATE 
      as UPDATE6_62_0_, addressali1_.UPDATE_BY as UPDATE7_62_0_, addressali1_.LAST_NAME as LAST8_62_0_, addressali1_.MOBILE as MOBILE62_0_, 
      addressali1_.HOME_PHONE as HOME10_62_0_, addressali1_.OFFICE_PHONE as OFFICE11_62_0_, addressali1_.SUFFIX as SUFFIX62_0_, 
      addressali1_.ADDRESS_LINE1 as ADDRESS13_62_0_, addressali1_.ADDRESS_LINE2 as ADDRESS14_62_0_, addressali1_.FIRST_NAME as FIRST15_62_0_, 
      addressali1_.ADDRESS_LINE3 as ADDRESS16_62_0_, addressali1_.CITY as CITY62_0_, addressali1_.COUNTY as COUNTY62_0_, addressali1_.STATE 
      as STATE62_0_, addressali1_.ZIP as ZIP62_0_, addressali1_.COUNTRY as COUNTRY62_0_, addressali1_.FAX as FAX62_0_, addressali1_.JOB_TITLE 
      as JOB23_62_0_, addressali1_.ADDRESS_NAME as ADDRESS24_62_0_, addressali1_.DEPARTMENT as DEPARTMENT62_0_, addressali1_.ADDRESS_ORGN_NAME 
      as ADDRESS26_62_0_, addressali1_.PRIMARY_ADDRESS as PRIMARY27_62_0_, addressali1_.CONTACT_ID as CONTACT28_62_0_, addressali1_.ORG_ID
      as ORG29_62_0_, addressali1_.ADDRESS_TYPE_ID as ADDRESS30_62_0_ from TBL_ORGANIZATION this_, TBL_ADDRESS addressali1_ 
      where this_.ORG_ID=addressali1_.ORG_ID and lower(addressali1_.COUNTRY) like 'irn' and this_.ORG_ID in 
      (select this_.PARENT_ORG_ID as y0_ from TBL_ORGANIZATION this_ where this_.ORG_ID in 
      (select this_.ORG_ID as y0_ from TBL_SALESREP_ORG this_) and this_.ORG_TYPE_ID='52') 
      and this_.ORG_TYPE_ID='53' and this_.OPCO_GEO_ID in ('410000006', '410000007', '410000020', '410000008', '410000009', '410000021') and
      (this_.LOGICAL_DELETE_INDICATOR='N' 
      or this_.LOGICAL_DELETE_INDICATOR is null) 
      
      order by this_.ORGANIZATION_NAME asc ) 
      
      
      select * from  t
      
      where rownum <= 1 ;
        • 1. Re: Tuning a query -First time
          jeneesh
          Rahul India wrote:
          How can i write this query in a better way
          DB-11GR2
          Please do let me know what additional data you need from me.


          It is explained in the FAQ: {message:id=9360003}
          • 2. Re: Tuning a query -First time
            Rahul_India
            Hi jeenesh
            Thanks for the link

            When i did explain  plan for for my query i got
             
            select * from table(dbms_xplan.display);
            
            output
               - 'PLAN_TABLE' is old version
            And the list of tables that it shows are ones the ones not in the SQL.



            edited the Output

            Edited by: Rahul India on Mar 6, 2013 5:10 PM
            • 3. Re: Tuning a query -First time
              jeneesh
              Dear Rahul,

              It is very sad that, after being a regular member in this forum also, you are not formatting your post properly..

              I will format your code for you, this time..
              WITH t AS
                (SELECT this_.ORG_ID             AS ORG1_67_1_,
                  this_.ORGANIZATION_NAME        AS ORGANIZA2_67_1_,
                  this_.DISTRICT_ID              AS DISTRICT3_67_1_,
                  this_.SUB_DISTRICT_ID          AS SUB4_67_1_,
                  this_.CREATE_DATE              AS CREATE5_67_1_,
                  this_.CREATE_BY                AS CREATE6_67_1_,
                  this_.UPDATE_DATE              AS UPDATE7_67_1_,
                  this_.UPDATE_BY                AS UPDATE8_67_1_,
                  this_.PID                      AS PID67_1_,
                  this_.OPCO_GEO_ID              AS OPCO10_67_1_,
                  this_.SOURCE_SYSTEM_ID         AS SOURCE11_67_1_,
                  this_.LOGICAL_DELETE_INDICATOR AS LOGICAL12_67_1_,
                  this_.ORG_TYPE_ID              AS ORG13_67_1_,
                  this_.PARENT_ORG_ID            AS PARENT14_67_1_,
                  addressali1_.ADDRESS_ID        AS ADDRESS1_62_0_,
                  addressali1_.SALUTATION        AS SALUTATION62_0_,
                  addressali1_.MIDDLE_NAME       AS MIDDLE3_62_0_,
                  addressali1_.CREATE_DATE       AS CREATE4_62_0_,
                  addressali1_.CREATE_BY         AS CREATE5_62_0_,
                  addressali1_.UPDATE_DATE       AS UPDATE6_62_0_,
                  addressali1_.UPDATE_BY         AS UPDATE7_62_0_,
                  addressali1_.LAST_NAME         AS LAST8_62_0_,
                  addressali1_.MOBILE            AS MOBILE62_0_,
                  addressali1_.HOME_PHONE        AS HOME10_62_0_,
                  addressali1_.OFFICE_PHONE      AS OFFICE11_62_0_,
                  addressali1_.SUFFIX            AS SUFFIX62_0_,
                  addressali1_.ADDRESS_LINE1     AS ADDRESS13_62_0_,
                  addressali1_.ADDRESS_LINE2     AS ADDRESS14_62_0_,
                  addressali1_.FIRST_NAME        AS FIRST15_62_0_,
                  addressali1_.ADDRESS_LINE3     AS ADDRESS16_62_0_,
                  addressali1_.CITY              AS CITY62_0_,
                  addressali1_.COUNTY            AS COUNTY62_0_,
                  addressali1_.STATE             AS STATE62_0_,
                  addressali1_.ZIP               AS ZIP62_0_,
                  addressali1_.COUNTRY           AS COUNTRY62_0_,
                  addressali1_.FAX               AS FAX62_0_,
                  addressali1_.JOB_TITLE         AS JOB23_62_0_,
                  addressali1_.ADDRESS_NAME      AS ADDRESS24_62_0_,
                  addressali1_.DEPARTMENT        AS DEPARTMENT62_0_,
                  addressali1_.ADDRESS_ORGN_NAME AS ADDRESS26_62_0_,
                  addressali1_.PRIMARY_ADDRESS   AS PRIMARY27_62_0_,
                  addressali1_.CONTACT_ID        AS CONTACT28_62_0_,
                  addressali1_.ORG_ID            AS ORG29_62_0_,
                  addressali1_.ADDRESS_TYPE_ID   AS ADDRESS30_62_0_
                FROM TBL_ORGANIZATION this_,
                  TBL_ADDRESS addressali1_
                WHERE this_.ORG_ID=addressali1_.ORG_ID
                AND lower(addressali1_.COUNTRY) LIKE 'irn'
                AND this_.ORG_ID IN
                  (SELECT this_.PARENT_ORG_ID AS y0_
                  FROM TBL_ORGANIZATION this_
                  WHERE this_.ORG_ID IN
                    (SELECT this_.ORG_ID AS y0_ FROM TBL_SALESREP_ORG this_
                    )
                  AND this_.ORG_TYPE_ID='52'
                  )
                AND this_.ORG_TYPE_ID              ='53'
                AND this_.OPCO_GEO_ID             IN ('410000006', '410000007', '410000020', '410000008', '410000009', '410000021')
                AND (this_.LOGICAL_DELETE_INDICATOR='N'
                OR this_.LOGICAL_DELETE_INDICATOR IS NULL)
                ORDER BY this_.ORGANIZATION_NAME ASC
                )
              • 4. Re: Tuning a query -First time
                Karthick_Arp
                Rahul India wrote:
                Hi jeenesh
                Thanks for the link

                When i did explain  plan for for my query i got
                explain  plan for
                And the list of tables that it shows are ones the ones not in the SQL.
                After that you need to run this
                select * from table(dbms_xplan.display);
                This will get you the output from plan_table.
                • 5. Re: Tuning a query -First time
                  Rahul_India
                  Hi Jeenesh,Karthick

                  im getting this
                     - 'PLAN_TABLE' is old version
                  to resolve this i went to
                  D:\oracle\product\10.2.0\client_2\RDBMS\ADMIN
                  but there is no sql files but only .sym file
                  • 6. Re: Tuning a query -First time
                    jeneesh
                    Rahul India wrote:
                    - 'PLAN_TABLE' is old version
                    Run,
                    drop table plan_table;
                    @?/rdbms/admin/utlxplan 
                    And generate the plan again and post..
                    • 7. Re: Tuning a query -First time
                      jeneesh
                      Rahul India wrote:
                      Hi Jeenesh,Karthick

                      im getting this
                      - 'PLAN_TABLE' is old version
                      to resolve this i went to
                      D:\oracle\product\10.2.0\client_2\RDBMS\ADMIN
                      but there is no sql files but only .sym file
                      Check it in the DB Server, ORACLE_HOME\RDBMS\ADMIN
                      • 8. Re: Tuning a query -First time
                        Rahul_India
                        There is no SQL file but only .sym file in
                         
                        D:\oracle\product\10.2.0\client_2\RDBMS\ADMIN
                        • 9. Re: Tuning a query -First time
                          Rahul_India
                          jeneesh wrote:
                          Rahul India wrote:
                          Hi Jeenesh,Karthick



                          but there is no sql files but only .sym file
                          Check it in the DB Server, ORACLE_HOME\RDBMS\ADMIN
                          I have only Oracle client installed
                          • 10. Re: Tuning a query -First time
                            jeneesh
                            create table PLAN_TABLE (
                                    statement_id       varchar2(30),
                                    plan_id            number,
                                    timestamp          date,
                                    remarks            varchar2(4000),
                                    operation          varchar2(30),
                                    options            varchar2(255),
                                    object_node        varchar2(128),
                                    object_owner       varchar2(30),
                                    object_name        varchar2(30),
                                    object_alias       varchar2(65),
                                    object_instance    numeric,
                                    object_type        varchar2(30),
                                    optimizer          varchar2(255),
                                    search_columns     number,
                                    id                 numeric,
                                    parent_id          numeric,
                                    depth              numeric,
                                    position           numeric,
                                    cost               numeric,
                                    cardinality        numeric,
                                    bytes              numeric,
                                    other_tag          varchar2(255),
                                    partition_start    varchar2(255),
                                    partition_stop     varchar2(255),
                                    partition_id       numeric,
                                    other              long,
                                    distribution       varchar2(30),
                                    cpu_cost           numeric,
                                    io_cost            numeric,
                                    temp_space         numeric,
                                    access_predicates  varchar2(4000),
                                    filter_predicates  varchar2(4000),
                                    projection         varchar2(4000),
                                    time               numeric,
                                    qblock_name        varchar2(30),
                                    other_xml          clob
                            );
                            • 11. Re: Tuning a query -First time
                              Karthick_Arp
                              Rahul India wrote:
                              There is no SQL file but only .sym file in
                               
                              D:\oracle\product\10.2.0\client_2\RDBMS\ADMIN
                              You are looking in the client machine. Look for it in the DB server.
                              • 12. Re: Tuning a query -First time
                                Rahul_India
                                jeenesh should i create that table PLAN_TABLE
                                • 13. Re: Tuning a query -First time
                                  jeneesh
                                  You can..

                                  That is what utlxplan.sql will do..
                                  • 14. Re: Tuning a query -First time
                                    SomeoneElse
                                    jeenesh should i create that table PLAN_TABLE
                                    If you are on 11.x there is a PLAN_TABLE owned by sys that has been granted to everyone so you shouldn't need to create one.

                                    My guess is that you have an old plan_table in your own schema. If you do, you should drop it and use the global one.
                                    1 2 Previous Next