1 2 3 Previous Next 31 Replies Latest reply: Jan 26, 2014 3:17 AM by AnnPricks E RSS

SQL Tuning

hokoba Newbie
Currently Being Moderated

Hi Techies,

I have a sql query

  select a.rowid    

   from  a, b    

   where A.MARK IS NULL    

     and a.cntry_code = b.cntry_code and b.dir_code='XX' and b.numb_type='XXX'   

     and upper(Trim(replace(replace(replace(replace(replace(replace(replace(a.co_name,'*'),'&'),'-'),'/'),')'),'('),' '))) like    

         upper(Trim(substr(replace(replace(replace(replace(replace(replace(replace(b.e_name,'*'),'&'),'-'),'/'),')'),'('),' '),1,25)))||'%';

It takes lot of time as it goes for Full table scan. No use of functional index. My purpose is to update the Column 'Mark' as '1' for the matching rows.

Table a has around 1.75 lakhs records and b has 5 lakhs records.

Kindly through some lights !!

 

Regards

Sri

  • 1. Re: SQL Tuning
    Suri Pro
    Currently Being Moderated

    Hi,

     

    Can you please provide CREATE TABLE and INSERT statements. Dont forget to mention your desired output as well.

    Is there any index created on column mark, co_name and e_name ?

     

    And also please read FAQ, and see How to post a Tuning related question

    Cheers,

    Suri

  • 2. Re: SQL Tuning
    Karthick_Arp Guru
    Currently Being Moderated

    Can you quantify "Lot of time". Because for some application 10 seconds is lot of time and for some 10 Hours is lot of time.

     

    I would also suggest you read the FAQ Re: 3. How to  improve the performance of my query? / My query is running slow. to know what to look for when tuning a SQL. Also it gives you the list of details that you need to supply here for us to be helpful.

  • 3. Re: SQL Tuning
    hokoba Newbie
    Currently Being Moderated

    Hi Karthik,

    Thanks for the guidance.

    {noformat}

    Plan

    SELECT STATEMENT  CHOOSECost: 333  Bytes: 52,355,940  Cardinality: 608,790 

    3 HASH JOIN  Cost: 333  Bytes: 52,355,940  Cardinality: 608,790 

      1 INDEX FAST FULL SCAN UNIQUE B_PK Cost: 4  Bytes: 503,022  Cardinality: 12,898 

        2 TABLE ACCESS FULL A Cost: 215  Bytes: 3,150,034  Cardinality: 67,022

    -------------------------------------------------------------------------------------------------------------------------------------------------------------

     

     

    TKPROF: Release 9.2.0.1.0 - Production on Tue Jan 21 11:52:29 2014

     

     

    Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

     

     

    Trace file: ora9i_ora_13400.trc

    Sort options: default

     

     

    ********************************************************************************

    count    = number of times OCI procedure was executed

    cpu      = cpu time in seconds executing

    elapsed  = elapsed time in seconds executing

    disk     = number of physical reads of buffers from disk

    query    = number of buffers gotten for consistent read

    current  = number of buffers gotten in current mode (usually for update)

    rows     = number of rows processed by the fetch or execute call

    ********************************************************************************

     

     

    alter session set sql_trace=TRUE

     

     

     

     

    call     count       cpu    elapsed       disk      query    current        rows

    ------- ------  -------- ---------- ---------- ---------- ----------  ----------

    Parse        0      0.00       0.00          0          0          0           0

    Execute      1      0.00       0.00          0          0          0           0

    Fetch        0      0.00       0.00          0          0          0           0

    ------- ------  -------- ---------- ---------- ---------- ----------  ----------

    total        1      0.00       0.00          0          0          0           0

     

     

    Misses in library cache during parse: 0

    Misses in library cache during execute: 1

    Optimizer goal: CHOOSE

    Parsing user id: 74 

    ********************************************************************************

     

     

      select a.rowid

      from a, b

      where A.MARK IS NULL

      and a.cntry_code = b.cntry_code and b.dir_code='GD' and b.numb_type='TEL'

      and

      upper(Trim(replace(replace(replace(replace(replace(replace(replace(a.co_name,'*'),'&'),'-'),'/'),')'),'('),' '))) like

      upper(Trim(substr(replace(replace(replace(replace(replace(replace(replace(b.e_name,'*'),'&'),'-'),'/'),')'),'('),' '),1,25)))||'%'

     

     

    call     count       cpu    elapsed       disk      query    current        rows

    ------- ------  -------- ---------- ---------- ---------- ----------  ----------

    Parse        1      0.01       0.01          0          0          0           0

    Execute      2      0.00       0.00          0          0          0           0

    Fetch        3   3025.53    3260.11       8367       7950          0          31

    ------- ------  -------- ---------- ---------- ---------- ----------  ----------

    total        6   3025.54    3260.13       8367       7950          0          31

     

     

    Misses in library cache during parse: 1

    Optimizer goal: CHOOSE

    Parsing user id: 74 

     

     

    Rows     Row Source Operation

    -------  ---------------------------------------------------

         31  HASH JOIN 

    302790   INDEX FAST FULL SCAN OBJ#(39024) (object id 39024)

      55798   TABLE ACCESS FULL OBJ#(78942)

     

     

     

     

     

     

     

     

    ********************************************************************************

     

     

    OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

     

     

    call     count       cpu    elapsed       disk      query    current        rows

    ------- ------  -------- ---------- ---------- ---------- ----------  ----------

    Parse        1      0.01       0.01          0          0          0           0

    Execute      3      0.00       0.00          0          0          0           0

    Fetch        3   3025.53    3260.11       8367       7950          0          31

    ------- ------  -------- ---------- ---------- ---------- ----------  ----------

    total        7   3025.54    3260.13       8367       7950          0          31

     

     

    Misses in library cache during parse: 1

    Misses in library cache during execute: 1

     

     

     

     

    OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

     

     

    call     count       cpu    elapsed       disk      query    current        rows

    ------- ------  -------- ---------- ---------- ---------- ----------  ----------

    Parse        0      0.00       0.00          0          0          0           0

    Execute      0      0.00       0.00          0          0          0           0

    Fetch        0      0.00       0.00          0          0          0           0

    ------- ------  -------- ---------- ---------- ---------- ----------  ----------

    total        0      0.00       0.00          0          0          0           0

     

     

    Misses in library cache during parse: 0

     

     

        2  user  SQL statements in session.

        0  internal SQL statements in session.

        2  SQL statements in session.

    ********************************************************************************

    Trace file: ora9i_ora_13400.trc

    Trace file compatibility: 9.00.01

    Sort options: default

     

     

           1  session in tracefile.

           2  user  SQL statements in trace file.

           0  internal SQL statements in trace file.

           2  SQL statements in trace file.

           2  unique SQL statements in trace file.

          46  lines in trace file.

     

     

     

    {noformat}

     

    When I run the query after "alter session sql_trace=TRUE", it just hangs. I got this trc file from O/S after closing the sqlplus session abruptly by clicking on the close button 'X'. Kindly assist.

     

    Thanks and Regards

    Sri

  • 4. Re: SQL Tuning
    softsuresh Newbie
    Currently Being Moderated

    Please give me your table object DDL and how the insert will be performed based on what?

  • 5. Re: SQL Tuning
    hokoba Newbie
    Currently Being Moderated

    Hi Suresh,

    The DDL of the 2 tables are as below:

    {noformat}

    CREATE TABLE BELL.MEDIA_ONE_ABU_DHABI_AL_AIN

    (

      CO_NAME      VARCHAR2(150 BYTE),

      LOCATION     VARCHAR2(50 BYTE),

      PO_BOX       VARCHAR2(20 BYTE),

      TELNO        VARCHAR2(20 BYTE),

      FAX          VARCHAR2(20 BYTE),

      EMAIL        VARCHAR2(75 BYTE),

      WEB          VARCHAR2(75 BYTE),

      ACTIVITY     VARCHAR2(100 BYTE),

      UNSUBSCRIBE  VARCHAR2(3 BYTE),

      NSD_CODE     VARCHAR2(10 BYTE),

      CNTRY_CODE   VARCHAR2(3 BYTE),

      CLASS_CODE   VARCHAR2(5 BYTE),

      BOX_NO       VARCHAR2(30 BYTE),

      NUMB         VARCHAR2(30 BYTE),

      NUMB2        VARCHAR2(30 BYTE),

      MARK         VARCHAR2(2 BYTE)

    )

    --------------------------------------------------

    CREATE TABLE B

    (

      NUMB            VARCHAR2(50 BYTE)             NOT NULL,

      NUMB_TYPE       VARCHAR2(3 BYTE)              NOT NULL,

      DIR_CODE        VARCHAR2(3 BYTE)              NOT NULL,

      E_NAME          VARCHAR2(100 BYTE)            NOT NULL,

      BOX_NO          VARCHAR2(30 BYTE),

      ADD1            VARCHAR2(30 BYTE),

      ADD2            VARCHAR2(30 BYTE),

      ADD3            VARCHAR2(30 BYTE),

      INDICATOR       VARCHAR2(1 BYTE)              NOT NULL,

      CNTRY_CODE      VARCHAR2(3 BYTE)              NOT NULL,

      EDITION         NUMBER(4)                     NOT NULL,

      NUMB2           VARCHAR2(50 BYTE),

      NUM3            VARCHAR2(100 BYTE),

      MAIN_TYPE       VARCHAR2(4 BYTE),

      SER_NO          NUMBER(2),

      SMAN_CODE       NUMBER(3),

      CLASS_CODE      VARCHAR2(5 BYTE),

      USER_ID         VARCHAR2(10 BYTE)             NOT NULL,

      CREATED_DATE    DATE                          NOT NULL,

      WEB_ADDRESS     VARCHAR2(50 BYTE),

      EMAIL_ID        VARCHAR2(50 BYTE),

      CONTACT_PERSON  VARCHAR2(100 BYTE),

      DESIG           VARCHAR2(40 BYTE),

      SOURCE          VARCHAR2(10 BYTE),

      COMP_CODE       VARCHAR2(3 BYTE),

      EXDIR           VARCHAR2(2 BYTE),

      MODIFIED_DATE   DATE,

      NSD_CODE        VARCHAR2(10 BYTE),

      MARK            VARCHAR2(2 BYTE),

      MARK1           VARCHAR2(2 BYTE),

      POTENTIAL       VARCHAR2(4 BYTE),

      PKEY            VARCHAR2(12 BYTE),

      NOMAIL          VARCHAR2(5 BYTE),

      SUBTYPE         VARCHAR2(1 BYTE),

      MOBILE          VARCHAR2(50 BYTE),

      WEB_KEY         NUMBER(10),

      OFFICE_NO       VARCHAR2(10 BYTE),

      BUILDING_NO     VARCHAR2(10 BYTE),

      COMPOUND_NO     VARCHAR2(10 BYTE),

      BLOCK_NO        VARCHAR2(10 BYTE),

      ROAD_NO         VARCHAR2(10 BYTE),

      E_ROAD_NAME     VARCHAR2(30 BYTE),

      PERSON_SPOKEN   VARCHAR2(50 BYTE),

      CO_LONGITUDE    VARCHAR2(30 BYTE),

      CO_LATITUDE     VARCHAR2(30 BYTE),

      CITY_CODE       VARCHAR2(5 BYTE),

      LOC_CODE        VARCHAR2(5 BYTE),

      POST_CODE       VARCHAR2(5 BYTE),

      A_ROAD_NAME     VARCHAR2(30 BYTE),

      WORKING_DAY     VARCHAR2(20 BYTE),

      WORKING_HOUR    VARCHAR2(50 BYTE),

      ROAD_CODE       VARCHAR2(5 BYTE),

      BUILDING_NAME   VARCHAR2(100 BYTE),

      LANDMARK        VARCHAR2(100 BYTE)

    )

    {noformat}

    ---------------------------------------------------

     

    The insert to table A is performed through sqlldr. Table B data is populated through an Oracle Forms 6i application.

     

    Thanks and Regards

    Sri

  • 6. Re: SQL Tuning
    Hoek Guru
    Currently Being Moderated

    TKPROF: Release 9.2.0.1.0 - Production on Tue Jan 21 11:52:29 2014

    That's really old.

    What database version are you using? ( the result of: select * from v$version; )

  • 7. Re: SQL Tuning
    hokoba Newbie
    Currently Being Moderated

    Hi Hoek,

    The output of v$version is as below:-

     

        BANNER 

    1 Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production 

    2 PL/SQL Release 9.2.0.1.0 - Production 

    3 CORE 9.2.0.1.0 Production 

    4 TNS for Solaris: Version 9.2.0.1.0 - Production 

    5 NLSRTL Version 9.2.0.1.0 - Production

     

    Thanks and Regards

    Sri

  • 8. Re: SQL Tuning
    Hoek Guru
    Currently Being Moderated

    OK, just to be sure, can you also post the output of:

     

    SQL> show parameter optimizer

  • 9. Re: SQL Tuning
    hokoba Newbie
    Currently Being Moderated

    Hi Hoek,

    SQL> show parameter optimizer

     

     

    NAME                                 TYPE        VALUE

    ------------------------------------ ----------- ------------------

    optimizer_dynamic_sampling           integer     1

    optimizer_features_enable            string      9.2.0

    optimizer_index_caching              integer     0

    optimizer_index_cost_adj             integer     100

    optimizer_max_permutations           integer     2000

    optimizer_mode                       string      CHOOSE

     

    Regards

    Sri

  • 10. Re: SQL Tuning
    Hoek Guru
    Currently Being Moderated

    Thanks.

     

    My purpose is to update the Column 'Mark' as '1' for the matching rows.

    A suggestion (without knowing the amount of data you're actually updating, but assuming it's a small portion): (re)create an index on mark like this:

     

    create index mark_idx on your_table_name (mark, 0);


    that should speed up finding your NULLS that are currently not indexed, let us know if that helps or not.

  • 11. Re: SQL Tuning
    hokoba Newbie
    Currently Being Moderated

    Hi Hoek,

    I created the index as suggested. But still the query is not even executed though I wait for nearly an hour. Table A has 1.75 lakh rows. Table B has around 5 lakhs. Initially the Mark column is NULL in table A. I am trying to mark it as 1,2,3 etc based on matching conditions with table B.

     

    Thanks and Regards

    Sri

  • 12. Re: SQL Tuning
    Manik Expert
    Currently Being Moderated


    Just thinking if your clumsy nested replace tasks can be replaced with a single TRANSLATE:

     

    ---------------------------

     

    you are doing :

     

    select 
                   TRIM (
                     REPLACE (
                        REPLACE (
                           REPLACE (
                              REPLACE (
                                 REPLACE (
                                    REPLACE (REPLACE ('*&-/)( ABC', '*'), '&'),
                                    '-'),
                                 '/'),
                              ')'),
                           '('),
                        ' ')) from dual
    

     

    Can that be replaced in code like this below:

    ----------------------

     

    SELECT TRANSLATE ('*&-/)( ABC', '~*&-/)( ', ' ') FROM DUAL                  
    

     

    Check and let us know.

     

    Not sure if the below query is similar to your query.. as I cannot check the data.. but this can give you some idea:

     

    SELECT a.ROWID
      FROM a, b
     WHERE     A.MARK IS NULL
           AND a.cntry_code = b.cntry_code
           AND b.dir_code = 'XX'
           AND b.numb_type = 'XXX'
           AND UPPER (TRANSLATE (a.co_name, '~*&-/)( ', ' ')) LIKE
                     UPPER (
                        TRIM (
                           SUBSTR (TRANSLATE (b.e_name, '~*&-/)( ', ' '), 1, 25)))
                  || '%';

    Cheers,

    Manik.

  • 13. Re: SQL Tuning
    Hoek Guru
    Currently Being Moderated

    So, all rows are having NULL for mark in table A and thus you need to update all rows? If so, then an index would not be of any help...

    You're probably better off using the CTAS approach then.

    Examples:

    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6407993912330

  • 14. Re: SQL Tuning
    hokoba Newbie
    Currently Being Moderated

    Hi Manik,

    The translate function doesnt work. It is also hanging..

    Thanks and Regards

    Sri

1 2 3 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points