This discussion is archived
1 2 3 4 Previous Next 46 Replies Latest reply: Mar 23, 2010 5:39 PM by 733022 Go to original post RSS
  • 30. Re: Tuning a sql using tkprof
    AleC Newbie
    Currently Being Moderated
    You are right!

    But we cannot avoid to count the rows.

    Probably we could estimate the count but, at the moment, we don't have any idea to have a precise estimation.
    Do you have any suggestion?
  • 31. Re: Tuning a sql using tkprof
    Lakmal Rajapakse Expert
    Currently Being Moderated
    I do not understand - if an estimate is fine - why not then completely remove the count.

    If you want an estimate, one option is to create a summary table containing the counts for each operator using materialized views. This view can then be periodically refreshed. Just make sure that the view you create is fast refreshable otherwise it might take a long time to refresh the view. When creating the mview you can remove some joins that I believe do not contribute to the count, such liu_types and phone_book.
  • 32. Re: Tuning a sql using tkprof
    AleC Newbie
    Currently Being Moderated
    We cannot use the summary table because users can add a lot of filter to the base query.
    So the summary table could be useful only for the original query.
  • 33. Re: Tuning a sql using tkprof
    Lakmal Rajapakse Expert
    Currently Being Moderated
    I would still recommend that you remove the whole concept of trying to find how many rows will be returned. It's just a bad idea.

    What filters can be applied on the i_journal table?
  • 34. Re: Tuning a sql using tkprof
    AleC Newbie
    Currently Being Moderated
    Filter by
    - liu_type
    - display_date
    - liu_status
    - ....and other columns in I_JOURNAL.
    - ....and other columns in PHONE_BOOK.
  • 35. Re: Tuning a sql using tkprof
    user503699 Expert
    Currently Being Moderated
    From what you have posted so far, it appears that access to I_JOURNAL table is consuming most of the time.
    What are the results of following?
    show parameter optimizer
    select dbms_metadata.get_ddl('TABLE','I_JOURNAL') from dual
  • 36. Re: Tuning a sql using tkprof
    AleC Newbie
    Currently Being Moderated
    NAME,VALUE
    optimizer_features_enable,10.1.0.5
    optimizer_mode,ALL_ROWS
    optimizer_index_cost_adj,30
    optimizer_index_caching,0
    optimizer_dynamic_sampling,2
    
    
      CREATE TABLE "M"."I_JOURNAL" 
       (     "ID" NUMBER(10,0) NOT NULL ENABLE, 
         "LIU_ID" NUMBER(10,0) NOT NULL ENABLE, 
         "DISPLAY_DATE" DATE NOT NULL ENABLE, 
         "SUMMARY" CLOB, 
         "LENGTH" NUMBER(10,0), 
         "IN_OUT_COMMUNICATION" VARCHAR2(3), 
         "LIU_TYPE" VARCHAR2(6) NOT NULL ENABLE, 
         "LIID" VARCHAR2(28) NOT NULL ENABLE, 
         "TARGET_ID" NUMBER(10,0) NOT NULL ENABLE, 
         "INTRCP_DEV_ID" NUMBER(10,0) NOT NULL ENABLE, 
         "FLOW_STATUS" VARCHAR2(6), 
         "IRI_ASSOC_TYPE" VARCHAR2(12), 
         "RELEVANCE_TYPE" VARCHAR2(6), 
         "ICON_TYPE" VARCHAR2(6), 
         "ID_CALL" VARCHAR2(20), 
         "LEMF" VARCHAR2(25), 
         "SRI_CC_SEQUENCE" VARCHAR2(8), 
         "CP_TECH_DESC" VARCHAR2(128), 
         "IRI_CIN" VARCHAR2(160), 
         "NORM_CIN" VARCHAR2(160), 
         "JOURNAL_OPERATOR" VARCHAR2(64), 
         "JOURNAL_TRANSCRIBER" VARCHAR2(64), 
         "JOURNAL_TRANSLATOR" VARCHAR2(64), 
         "TEXT_TRANSCRIPTION" CLOB, 
         "OBSERVED_IMEI" VARCHAR2(25), 
         "OBSERVED_IMSI" VARCHAR2(25), 
         "CIN_NAME" VARCHAR2(160), 
         "OBSERVED_NUMBER" VARCHAR2(160), 
         "TRANSFERED_TO_NUMBER" VARCHAR2(1024), 
         "NORM_TRANSFERED_TO_NUMBER" VARCHAR2(1024), 
         "PROVIDER_DATE" DATE, 
         "IRI_TYPE_PROVIDER_DATE" CHAR(1), 
         "MAIL_ADDR_TO" VARCHAR2(4000 CHAR), 
         "MAIL_ADDR_FROM" VARCHAR2(512), 
         "MAIL_ADDR_CC" VARCHAR2(4000 CHAR), 
         "MAIL_SUBJECT" VARCHAR2(1024), 
         "CELL_START_INFO" VARCHAR2(1024), 
         "CELL_END_INFO" VARCHAR2(1024), 
         "CIN_NICK_NAME" VARCHAR2(64), 
         "CC_SEQUENCE" VARCHAR2(8 CHAR), 
         "INTRNT_IRI_CIN" VARCHAR2(160 CHAR), 
         "INTRNT_NORM_CIN" VARCHAR2(160 CHAR), 
         "CALL_CONFERENCE_ID" NUMBER(10,0), 
         "COMMENT_TYPE" VARCHAR2(6 CHAR), 
         "ID_CIN_NAME" NUMBER(10,0), 
         "ID_NICK_NAME" NUMBER(10,0), 
         "ID_CT" NUMBER(10,0), 
          CONSTRAINT "IJL_PK" PRIMARY KEY ("LIU_ID")
      USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
      STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
      PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
      TABLESPACE "M_INDX"  ENABLE, 
          CONSTRAINT "IJL_ID_UK" UNIQUE ("ID")
      USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
      STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
      PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
      TABLESPACE "M_INDX"  ENABLE, 
          CONSTRAINT "IJL_RTE_FK" FOREIGN KEY ("RELEVANCE_TYPE")
           REFERENCES "M"."RELEVANCE_TYPES" ("RELEVANCE_TYPE") ENABLE, 
          CONSTRAINT "IJL_FSU_FK" FOREIGN KEY ("FLOW_STATUS")
           REFERENCES "M"."FLOW_STATUS" ("FLOW_STATUS") ENABLE, 
          CONSTRAINT "IJL_ITY_FK" FOREIGN KEY ("ICON_TYPE")
           REFERENCES "M"."ICON_TYPES" ("ICON_TYPE") ENABLE, 
          CONSTRAINT "IJL_LTY_FK" FOREIGN KEY ("LIU_TYPE")
           REFERENCES "M"."LIU_TYPES" ("LIU_TYPE") ENABLE, 
          CONSTRAINT "IJL_LIN_FK" FOREIGN KEY ("LIID")
           REFERENCES "M"."LAWFUL_I" ("LIID") ON DELETE CASCADE ENABLE, 
          CONSTRAINT "IJL_ITE_FK" FOREIGN KEY ("IRI_TYPE_PROVIDER_DATE")
           REFERENCES "M"."IRI_TYPES" ("IRI_TYPE") ENABLE, 
          CONSTRAINT "IJL_COM_FK" FOREIGN KEY ("COMMENT_TYPE")
           REFERENCES "M"."COMMENT_TYPES" ("COMMENT_TYPE") ENABLE
       ) PCTFREE 15 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
      STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
      PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
      TABLESPACE "M_DATA" 
     LOB ("SUMMARY") STORE AS "SUMMARY_LOB"(
      TABLESPACE "M_LOB_DATA" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10
      CACHE 
      STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
      PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)) 
     LOB ("TEXT_TRANSCRIPTION") STORE AS "TEXT_TRANSCRIPTION_LOB"(
      TABLESPACE "M_LOB_DATA" DISABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10
      NOCACHE 
      STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
      PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)) 
     
  • 37. Re: Tuning a sql using tkprof
    user503699 Expert
    Currently Being Moderated
    Any particular reason why you have non-default value (30) for optimizer_index_cost_adj ? That value influences optimizer to use index access instead of table access in the execution plan (and that is not necessarily always right decision).
    Also, the SUMMARY column is of CLOB datatype and has IN ROW storage enabled as well as CACHE.
    By any chance, does the M_DATA tablespace (in which I_JOURNAL table exists) have non-default block size(16k or 32K)?

    Edited by: user503699 on Mar 17, 2010 8:37 PM
  • 38. Re: Tuning a sql using tkprof
    AleC Newbie
    Currently Being Moderated
    Any particular reason why you have non-default value (30) for optimizer_index_cost_adj?
    No, I'm going to change it to 100

    the SUMMARY column is of CLOB datatype and has IN ROW storage enabled as well as CACHE.
    Is there any problem?

    By any chance, does the M_DATA tablespace (in which I_JOURNAL table exists) have non-default block size(16k or 32K)?
    No, it has the default block size: 8K
  • 39. Re: Tuning a sql using tkprof
    user503699 Expert
    Currently Being Moderated
    user600979 wrote:
    Any particular reason why you have non-default value (30) for optimizer_index_cost_adj?
    No, I'm going to change it to 100
    So does that result in change of execution plan or time for the query ?
    the SUMMARY column is of CLOB datatype and has IN ROW storage enabled as well as CACHE.
    Is there any problem?
    No problem. Just an observation, especially since the other CLOB column is defined to be stored outside the table always.
    By any chance, does the M_DATA tablespace (in which I_JOURNAL table exists) have non-default block size(16k or 32K)?
    No, it has the default block size: 8K
    Depending upon the size of data in SUMMARY column (and other column contents), it looks like I_JOURNAL table rows are bigger in size, which means lesser number of rows per data block, which will mean more blocks to access when the table is used to query (But I doubt that accounts for increased CPU time).
    By any chance, what are the TkProf details for following query?
    select count(summary) from i_journal where li_id = <li_id having 800K rows>
    The reasons for this are
    1) I am expecting to get an execution plan that will access I_JOURNAL table using index (so if above query does not generate that plan, feel free to change to the query that will)
    2) How is the query processed by oracle, especially in terms of CPU usage

    p.s. BTW, I would still agree with Timur and Lakmal's arguments earlier. By deciding to use this query, you are effectively saying that you have plenty of resources (CPU, memory etc.) to throw at the database, in order to get good performance. This is important and more so because it is possible that the root cause for this query not performing as expected, might be due to insufficient CPU.

    Edited by: user503699 on Mar 17, 2010 9:07 PM
  • 40. Re: Tuning a sql using tkprof
    AleC Newbie
    Currently Being Moderated
    I cannot execute the suggested query because the error:

    select count(m.summary) from i_journal m
    *
    ERROR at line 1:
    ORA-00932: inconsistent datatypes: expected - got CLOB

    I've tried to remove the summary column from the query for the pagination (the first 100 rows) and the response time is better: from 20 seconds to 15 seconds.

    If I replace the CPU with another faster one, I should have better response time, should I?
  • 41. Re: Tuning a sql using tkprof
    6363 Guru
    Currently Being Moderated
    user600979 wrote:

    But we cannot avoid to count the rows.

    Probably we could estimate the count but, at the moment, we don't have any idea to have a precise estimation.
    Do you have any suggestion?
    Yes, if you want precise you will be slow.

    If you want to be fast like Google, you will use an estimate.

    http://www.google.com/search?q=%2Bchomby+%2Bflaw

    At the moment Google is telling me 10 pages.

    So click on the link, scroll to the bottom and choose page 10.

    Guess what, there were only 7 pages.

    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:127412348064#14136093079164
  • 42. Re: Tuning a sql using tkprof
    user503699 Expert
    Currently Being Moderated
    user600979 wrote:
    I cannot execute the suggested query because the error:

    select count(m.summary) from i_journal m
    *
    ERROR at line 1:
    ORA-00932: inconsistent datatypes: expected - got CLOB

    I've tried to remove the summary column from the query for the pagination (the first 100 rows) and the response time is better: from 20 seconds to 15 seconds.
    I am not sure I understood that. Your query does not include any columns but only count(*), right?
    If I replace the CPU with another faster one, I should have better response time, should I?
    I am afraid I can not say the faster CPU conclusion is correct as I am not aware of your environment.
    As I mentioned earlier, can you post the query (against I_JOURNAL table only) you used and corresponding TkProf details?
  • 43. Re: Tuning a sql using tkprof
    AleC Newbie
    Currently Being Moderated
    We have two kind of queries:
    - count: query to count the number of pages
    - page: query to get the rows.

    I've reduced the query.
    select count(*) from i_journal m
    JOIN
                   LAWFUL_I li
                ON (m.LIID = li.LIID)
    where
              (li.ID IN
                        (
    :"SYS_B_00",
                         :"SYS_B_01",
                         :"SYS_B_02",
                         :"SYS_B_03",
                         :"SYS_B_04",
                         :"SYS_B_05",
                         :"SYS_B_06",
                         :"SYS_B_07",
                         :"SYS_B_08",
                         :"SYS_B_09",
                         :"SYS_B_10",
                         :"SYS_B_11",
                         :"SYS_B_12",
                         :"SYS_B_13",
                         :"SYS_B_14",
                         :"SYS_B_15",
                         :"SYS_B_16",
                         :"SYS_B_17",
    .....
    .....
    .....
    select count(*) from i_journal m
    JOIN
                   LAWFUL_I li
                ON (m.LIID = li.LIID)
    where
              (li.ID IN
                        (:"SYS_B_00",
                         :"SYS_B_01",
                         :"SYS_B_02",
                         :"SYS_B_03",
                         :"SYS_B_04",
                         :"SYS_B_05",
                         :"SYS_B_06",
                         :"SYS_B_07",
                         :"SYS_B_08",
                         :"SYS_B_09",
                         :"SYS_B_10",
                         :"SYS_B_11",
                         :"SYS_B_12",
                         :"SYS_B_13",
                         :"SYS_B_14",
                         :"SYS_B_15",
                         :"SYS_B_16",
                         :"SYS_B_17",
    .....
                         :"SYS_B_59",
                         :"SYS_B_60"
    ))
             AND (li.END_VALID_DATE IS NULL
                  OR m.DISPLAY_DATE <= li.END_VALID_DATE)
             AND li.OPERATOR_ID = :"SYS_B_61"
    
    ---------------------------------------------------------------------------------------------------------------
    | Id  | Operation          | Name                  | E-Rows | A-Rows | Buffers | Reads  | Writes | A-Time     |
    ---------------------------------------------------------------------------------------------------------------
    |   1 | SORT AGGREGATE     |                       |      1 |      1 |     265K|    181K|      0 |00:01:41.72 |
    |*  2 |  HASH JOIN         |                       |  56072 |    914K|     265K|    181K|      0 |00:01:38.78 |
    |*  3 |   TABLE ACCESS FULL| LAWFUL_I  |     25 |     61 |      76 |      0 |      0 |00:00:00.01 |
    |   4 |   TABLE ACCESS FULL| I_JOURNAL |   5042K|   5042K|     265K|    181K|      0 |00:00:35.30 |
    ------------------------------------------------------------------------------- -------------------------------
    
    
    Query Block Name / Object Alias (identified by operation id):
    -------------------------------------------------------------
    
       1 - SEL$58A6D7F6
       3 - SEL$58A6D7F6 / LI@SEL$1
       4 - SEL$58A6D7F6 / M@SEL$1
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - access("M"."LIID"="LI"."LIID")
           filter(("LI"."END_VALID_DATE" IS NULL OR "M"."DISPLAY_DATE"<="LI"."END_VA
    LID_DATE"))
    
       3 - filter(("LI"."OPERATOR_ID"=:SYS_B_61 AND "LI"."ID"))
    The TKPROF output is:
       call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1      0.00       0.00          0          0          0           0
    Execute      1      0.01       0.00          0          0          0           0
    Fetch        2    103.50     101.71     181759     265230          0           1
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total        4    103.51     101.72     181759     265230          0           1
    
    Misses in library cache during parse: 0
    Optimizer mode: ALL_ROWS
    Parsing user id: 50
    
    Rows     Row Source Operation
    -------  ---------------------------------------------------
          1  SORT AGGREGATE (cr=265230 pr=181759 pw=0 time=101719100 us)
     914496   HASH JOIN  (cr=265230 pr=181759 pw=0 time=98777052 us)
         61    TABLE ACCESS FULL LAWFUL_I (cr=76 pr=0 pw=0 time=3590 us)
    5042908    TABLE ACCESS FULL I_JOURNAL (cr=265154 pr=181759 pw=0 time=35302132 us)
    
    
    Elapsed times include waiting on following events:
      Event waited on                             Times   Max. Wait  Total Waited
      ----------------------------------------   Waited  ----------  ------------
      SQL*Net message to client                       2        0.00          0.00
      db file scattered read                      23139        0.00          3.54
      db file sequential read                     12182        0.00          0.43
      read by other session                           4        0.00          0.00
      latch: cache buffers chains                     2        0.01          0.02
      SQL*Net message from client                     2        0.00          0.00
    ********************************************************************************
  • 44. Re: Tuning a sql using tkprof
    user503699 Expert
    Currently Being Moderated
    Well, statistics in the TkProf (for "reduced" query) are consistent with those in the TkProf for original query.
    I am inclined to think that accessing data blocks of I_JOURNAL table from buffer cache is consuming most of the CPU time. But I am struggling to understand the reason for this. The only thing that stands out is the structure of the I_JOURNALE table (especially couple of CLOB columns, where one has been defined to be stored "in row" as well as being available for "caching" and overall large row length). But I can't think of a way to prove that structure of I_JOURNAL table is the "cause" behind large CPU time (the "symptom"), provided there is any link.
    I guess I am running out of ideas here so I would leave it for others to comment.
    Apologies for not being of much help.

Legend

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