This discussion is archived
1 2 Previous Next 19 Replies Latest reply: Mar 25, 2010 9:04 PM by 761587 RSS

Query Tuning: 11g

761587 Newbie
Currently Being Moderated
How to optimize a select query on a tabe that is range partition on date index.
Insertion is occuring at a very high rate in this table.
Latest 600 records is to be retrived.
The explain plan shows that fetch is taking lot of time in disc IO,s
1- plan is also poor due to full table index.
2- Optimizer is not picking the better plan may be high insertion making the index selection redundant.

Please suggest how to attack on such issues.
  • 1. Re: Query Tuning: 11g
    BluShadow Guru Moderator
    Currently Being Moderated
    How to post a SQL statement tuning request:

    HOW TO: Post a SQL statement tuning request - template posting
  • 2. Re: Query Tuning: 11g
    Gaff Journeyer
    Currently Being Moderated
    Quantify "a very high rate".

    What's your hardware?

    Are the disks RAIDed?

    (or) Are you using ASM with multiple volumes?

    Is SSD an option?

    Is it always just the last 600 records that you need? Maybe you could keep the last 1,000 or 5,000 or whatever records in an in-memory FIFO queue or something and "roll them off" to the DB for longer term storage? It doesn't sound like you are filtering on anything but time of arrival.
  • 3. Re: Query Tuning: 11g
    761587 Newbie
    Currently Being Moderated
    The Alert rate is 500 alerts per sec and it is looging data regularly in the table.
    So the machine has a single hard disk The mentioned features are not avaliable.
    1- The SQL and a short description of its purpose
    
    The SQL::
    
    select almevttbl.AlmEvtId, almevttbl.AlmType, almevtt
    bl.ComponentId, almevttbl.TimeStamp, almevttbl.Severity, almevttbl.State, almevt
    tbl.Category, almevttbl.CauseCode, almevttbl.UnitType, almevttbl.UnitId, almevtt
    bl.UnitName, almevttbl.ServerName, almevttbl.StrParam, almevttbl.ExtraStrParam,
    almevttbl.ExtraStrParam2, almevttbl.ExtraStrParam3, almevttbl.ParentCustId, alme
    vttbl.ExtraParam1, almevttbl.ExtraParam2, almevttbl.ExtraParam3,almevttbl.ExtraP
    aram4,almevttbl.ExtraParam5, almevttbl.SRCIPADDRFAMILY,almevttbl.SrcIPAddress11,
    almevttbl.SrcIPAddress12,almevttbl.SrcIPAddress13,almevttbl.SrcIPAddress14, alme
    vttbl.DESTIPADDRFAMILY,almevttbl.DestIPAddress11,almevttbl.DestIPAddress12,almev
    ttbl.DestIPAddress13,almevttbl.DestIPAddress14,  almevttbl.DestPort, almevttbl.S
    rcPort, almevttbl.SessionDir, almevttbl.CustomerId, almevttbl.ProfileId, almevtt
    bl.ParentProfileId, almevttbl.CustomerName, almevttbl.AttkDir, almevttbl.SubCate
    gory, almevttbl.RiskCategory, almevttbl.AssetValue, almevttbl.IPSAction, almevtt
    bl.l4Protocol,almevttbl.ExtraStrParam4 ,almevttbl.ExtraStrParam5,almevttbl.usern
    ame,almevttbl.ExtraStrParam6,IpAddrFamily1,IPAddrValue11,IPAddrValue12,IPAddrVal
    ue13,IPAddrValue14,IpAddrFamily2,IPAddrValue21,IPAddrValue22,IPAddrValue23,IPAdd
    rValue24 
    
    FROM   
    
    AlmEvtTbl PARTITION(ALMEVTTBLP20100323) 
    WHERE AlmEvtId 
    
    IN 
    ( SELECT  * FROM ( SELECT /*+ FIRST_ROWS(1000) INDEX (AlmEvtTbl AlmEvtTbl_Index) */AlmEvtId FROM AlmEvtTbl PARTITION(ALMEVTTBLP20100323) 
      where ((AlmEvtTbl.Customerid = 0 or AlmEvtTbl.ParentCustId = 0))  ORDER BY AlmEvtTbl.TIMESTAMP DESC
    )
    WHERE ROWNUM  <  602) 
    Order by timestamp desc
    
    1- The Above Sql fetches the latest 600 records from the almevttbl.
    2- This uses the index and as well hint.
    3- Most of the time it works well. but for current partition 
       when insertion high then query takes too much time around 20 minutes to 40 minutes.
    4- The Above table is range partitioned on the date.
       
       
       
    2- The version of my database with 4-digits (Release 11.1.0.6.0)
    
    3- Optimizer related parameters
    SQL> show parameter opt;
    
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ----------------------------
    filesystemio_options                 string
    object_cache_optimal_size            integer     102400
    optimizer_capture_sql_plan_baselines boolean     FALSE
    optimizer_dynamic_sampling           integer     2
    optimizer_features_enable            string      11.1.0.6
    optimizer_index_caching              integer     0
    optimizer_index_cost_adj             integer     100
    optimizer_mode                       string      ALL_ROWS
    optimizer_secure_view_merging        boolean     TRUE
    optimizer_use_invisible_indexes      boolean     FALSE
    optimizer_use_pending_statistics     boolean     FALSE
    
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ----------------------------
    optimizer_use_sql_plan_baselines     boolean     TRUE
    plsql_optimize_level                 integer     2
    
    SQL> show parameter db_file_multi
    
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    db_file_multiblock_read_count        integer     20
    
    
    SQL> show parameter db_block_size
    
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    db_block_size                        integer     8192
    
    
    SQL> show parameter cursor_sharing
    
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- -----------------
    cursor_sharing                       string      EXACT
    
    
    Explain Plain Output::
    
    
    SQL ID : 3w9yb9gna5xbw
    select almevttbl.AlmEvtId, almevttbl.AlmType, almevttbl.ComponentId, 
      almevttbl.TimeStamp, almevttbl.Severity, almevttbl.State, 
      almevttbl.Category, almevttbl.CauseCode, almevttbl.UnitType, 
      almevttbl.UnitId, almevttbl.UnitName, almevttbl.ServerName, 
      almevttbl.StrParam, almevttbl.ExtraStrParam, almevttbl.ExtraStrParam2, 
      almevttbl.ExtraStrParam3, almevttbl.ParentCustId, almevttbl.ExtraParam1, 
      almevttbl.ExtraParam2, almevttbl.ExtraParam3,almevttbl.ExtraParam4,
      almevttbl.ExtraParam5, almevttbl.SRCIPADDRFAMILY,almevttbl.SrcIPAddress11,
      almevttbl.SrcIPAddress12,almevttbl.SrcIPAddress13,almevttbl.SrcIPAddress14, 
      almevttbl.DESTIPADDRFAMILY,almevttbl.DestIPAddress11,
      almevttbl.DestIPAddress12,almevttbl.DestIPAddress13,
      almevttbl.DestIPAddress14,  almevttbl.DestPort, almevttbl.SrcPort, 
      almevttbl.SessionDir, almevttbl.CustomerId, almevttbl.ProfileId, 
      almevttbl.ParentProfileId, almevttbl.CustomerName, almevttbl.AttkDir, 
      almevttbl.SubCategory, almevttbl.RiskCategory, almevttbl.AssetValue, 
      almevttbl.IPSAction, almevttbl.l4Protocol,almevttbl.ExtraStrParam4 ,
      almevttbl.ExtraStrParam5,almevttbl.username,almevttbl.ExtraStrParam6,
      IpAddrFamily1,IPAddrValue11,IPAddrValue12,IPAddrValue13,IPAddrValue14,
      IpAddrFamily2,IPAddrValue21,IPAddrValue22,IPAddrValue23,IPAddrValue24 
    FROM
           AlmEvtTbl PARTITION(ALMEVTTBLP20100223) WHERE AlmEvtId IN ( SELECT  * FROM 
      ( SELECT /*+ FIRST_ROWS(1000) INDEX (AlmEvtTbl AlmEvtTbl_Index2) */AlmEvtId 
      FROM AlmEvtTbl PARTITION(ALMEVTTBLP20100223) where       ((AlmEvtTbl.Customerid 
      = 0 or AlmEvtTbl.ParentCustId = 0))  ORDER BY AlmEvtTbl.TIMESTAMP DESC)  
      WHERE ROWNUM  <  602) order by timestamp desc
    
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1      0.04       0.04          0          0          0           0
    Execute      1      0.00       0.00          0          0          0           0
    Fetch       42      1.84      45.55       9981      10020          0         601
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total       44      1.89      45.60       9981      10020          0         601
    
    Misses in library cache during parse: 1
    Optimizer mode: FIRST_ROWS
    Parsing user id: 82  
    
    Rows     Row Source Operation
    -------  ---------------------------------------------------
        601  SORT ORDER BY (cr=10020 pr=9981 pw=9981 time=40 us cost=16148 size=321535 card=601)
        601   NESTED LOOPS  (cr=10020 pr=9981 pw=9981 time=1426 us)
        601    NESTED LOOPS  (cr=9965 pr=9928 pw=9928 time=111 us cost=16076 size=321535 card=601)
        601     SORT UNIQUE (cr=9924 pr=9923 pw=9923 time=8 us cost=15172 size=3606 card=601)
        601      VIEW  VW_NSO_1 (cr=9924 pr=9923 pw=9923 time=16 us cost=15172 size=3606 card=601)
        601       HASH UNIQUE (cr=9924 pr=9923 pw=9923 time=7 us)
        601        COUNT STOPKEY (cr=9924 pr=9923 pw=9923 time=19 us)
        601         VIEW  (cr=9924 pr=9923 pw=9923 time=13 us cost=15172 size=5215914 card=869319)
        601          SORT ORDER BY STOPKEY (cr=9924 pr=9923 pw=9923 time=8 us cost=15172 size=16517061 card=869319)
     869398           PARTITION RANGE SINGLE PARTITION: 27 27 (cr=9924 pr=9923 pw=9923 time=307098 us cost=10042 size=16517061 card=869319)
     869398            INDEX FULL SCAN ALMEVTTBL_INDEX2 PARTITION: 27 27 (cr=9924 pr=9923 pw=9923 time=279276 us cost=10042 size=16517061 card=869319)(object id 71685)
        601     PARTITION RANGE SINGLE PARTITION: 27 27 (cr=41 pr=5 pw=5 time=0 us cost=2 size=0 card=1)
        601      INDEX RANGE SCAN ALMEVTTBL_PK PARTITION: 27 27 (cr=41 pr=5 pw=5 time=0 us cost=2 size=0 card=1)(object id 70818)
        601    TABLE ACCESS BY LOCAL INDEX ROWID ALMEVTTBL PARTITION: 27 27 (cr=55 pr=53 pw=53 time=0 us cost=3 size=529 card=1)
    
    ********************************************************************************
    
    Statistics
    ----------------------------------------------------------
           1294  recursive calls
              0  db block gets
       70417813  consistent gets
           5868  physical reads
          49924  redo size
          30523  bytes sent via SQL*Net to client
            860  bytes received via SQL*Net from client
             42  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
            601  rows processed
            
    **********************************************************        
  • 4. Re: Query Tuning: 11g
    user503699 Expert
    Currently Being Moderated
    user12834233 wrote:
    2- The version of my database with 4-digits (Release 11.1.0.6.0)
    
    Explain Plain Output::
    
    
    SQL ID : 3w9yb9gna5xbw
    select almevttbl.AlmEvtId, almevttbl.AlmType, almevttbl.ComponentId, 
    almevttbl.TimeStamp, almevttbl.Severity, almevttbl.State, 
    almevttbl.Category, almevttbl.CauseCode, almevttbl.UnitType, 
    almevttbl.UnitId, almevttbl.UnitName, almevttbl.ServerName, 
    almevttbl.StrParam, almevttbl.ExtraStrParam, almevttbl.ExtraStrParam2, 
    almevttbl.ExtraStrParam3, almevttbl.ParentCustId, almevttbl.ExtraParam1, 
    almevttbl.ExtraParam2, almevttbl.ExtraParam3,almevttbl.ExtraParam4,
    almevttbl.ExtraParam5, almevttbl.SRCIPADDRFAMILY,almevttbl.SrcIPAddress11,
    almevttbl.SrcIPAddress12,almevttbl.SrcIPAddress13,almevttbl.SrcIPAddress14, 
    almevttbl.DESTIPADDRFAMILY,almevttbl.DestIPAddress11,
    almevttbl.DestIPAddress12,almevttbl.DestIPAddress13,
    almevttbl.DestIPAddress14,  almevttbl.DestPort, almevttbl.SrcPort, 
    almevttbl.SessionDir, almevttbl.CustomerId, almevttbl.ProfileId, 
    almevttbl.ParentProfileId, almevttbl.CustomerName, almevttbl.AttkDir, 
    almevttbl.SubCategory, almevttbl.RiskCategory, almevttbl.AssetValue, 
    almevttbl.IPSAction, almevttbl.l4Protocol,almevttbl.ExtraStrParam4 ,
    almevttbl.ExtraStrParam5,almevttbl.username,almevttbl.ExtraStrParam6,
    IpAddrFamily1,IPAddrValue11,IPAddrValue12,IPAddrValue13,IPAddrValue14,
    IpAddrFamily2,IPAddrValue21,IPAddrValue22,IPAddrValue23,IPAddrValue24 
    FROM
         AlmEvtTbl PARTITION(ALMEVTTBLP20100223) WHERE AlmEvtId IN ( SELECT  * FROM 
    ( SELECT /*+ FIRST_ROWS(1000) INDEX (AlmEvtTbl AlmEvtTbl_Index2) */AlmEvtId 
    FROM AlmEvtTbl PARTITION(ALMEVTTBLP20100223) where       ((AlmEvtTbl.Customerid 
    = 0 or AlmEvtTbl.ParentCustId = 0))  ORDER BY AlmEvtTbl.TIMESTAMP DESC)  
    WHERE ROWNUM  <  602) order by timestamp desc
    
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1      0.04       0.04          0          0          0           0
    Execute      1      0.00       0.00          0          0          0           0
    Fetch       42      1.84      45.55       9981      10020          0         601
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total       44      1.89      45.60       9981      10020          0         601
    
    Misses in library cache during parse: 1
    Optimizer mode: FIRST_ROWS
    Parsing user id: 82  
    
    Rows     Row Source Operation
    -------  ---------------------------------------------------
    601  SORT ORDER BY (cr=10020 pr=9981 pw=9981 time=40 us cost=16148 size=321535 card=601)
    601   NESTED LOOPS  (cr=10020 pr=9981 pw=9981 time=1426 us)
    601    NESTED LOOPS  (cr=9965 pr=9928 pw=9928 time=111 us cost=16076 size=321535 card=601)
    601     SORT UNIQUE (cr=9924 pr=9923 pw=9923 time=8 us cost=15172 size=3606 card=601)
    601      VIEW  VW_NSO_1 (cr=9924 pr=9923 pw=9923 time=16 us cost=15172 size=3606 card=601)
    601       HASH UNIQUE (cr=9924 pr=9923 pw=9923 time=7 us)
    601        COUNT STOPKEY (cr=9924 pr=9923 pw=9923 time=19 us)
    601         VIEW  (cr=9924 pr=9923 pw=9923 time=13 us cost=15172 size=5215914 card=869319)
    601          SORT ORDER BY STOPKEY (cr=9924 pr=9923 pw=9923 time=8 us cost=15172 size=16517061 card=869319)
    869398           PARTITION RANGE SINGLE PARTITION: 27 27 (cr=9924 pr=9923 pw=9923 time=307098 us cost=10042 size=16517061 card=869319)
    869398            INDEX FULL SCAN ALMEVTTBL_INDEX2 PARTITION: 27 27 (cr=9924 pr=9923 pw=9923 time=279276 us cost=10042 size=16517061 card=869319)(object id 71685)
    601     PARTITION RANGE SINGLE PARTITION: 27 27 (cr=41 pr=5 pw=5 time=0 us cost=2 size=0 card=1)
    601      INDEX RANGE SCAN ALMEVTTBL_PK PARTITION: 27 27 (cr=41 pr=5 pw=5 time=0 us cost=2 size=0 card=1)(object id 70818)
    601    TABLE ACCESS BY LOCAL INDEX ROWID ALMEVTTBL PARTITION: 27 27 (cr=55 pr=53 pw=53 time=0 us cost=3 size=529 card=1)
    
    ********************************************************************************
    
    Statistics
    ----------------------------------------------------------
    1294  recursive calls
    0  db block gets
    70417813  consistent gets
    5868  physical reads
    49924  redo size
    30523  bytes sent via SQL*Net to client
    860  bytes received via SQL*Net from client
    42  SQL*Net roundtrips to/from client
    0  sorts (memory)
    0  sorts (disk)
    601  rows processed
    
    **********************************************************        
    Couple of observations:
    1) The TkProf Row Source operation statistics do not match with autotrace statistics. Autotrace shows 70417813 consistent gets whereas TkProf reports only 10020 consistent gets. Same (but in reverse way) for physical reads. Autotrace shows 5868 physical reads whereas TkProf shows 9981 physical reads. Any chance the Autotrace and TkProf are not for the same query inputs (or at the same time)?
    2) It would be great if you can generate trace with wait events enabled. You can achieve this by either
    a) using level 8 or above if you are using "alter session set events '10046..." to enable tracing OR
    b) using "waits=>true" argument if you are using dbms_monitor.session_trace_enable
    The wait events should explain where the time is being spent.
    3) You have mentioned the query takes 20-40 minutes at times to execute. But your tracing shows the query has completed execution under a minute. You may want to post the trace details (with wait events) when the query takes 20-40 minutes to execute.
  • 5. Re: Query Tuning: 11g
    761587 Newbie
    Currently Being Moderated
    uploading latest run with autotrace and explain plain with waits elapsed time is 1521 secs around 25 minutes.
    please let me know what is bottle neck here.
    ####TKPROF output#########
    
    SQL ID : 2j5w6bv437cak
    select almevttbl.AlmEvtId, almevttbl.AlmType, almevttbl.ComponentId, 
      almevttbl.TimeStamp, almevttbl.Severity, almevttbl.State, 
      almevttbl.Category, almevttbl.CauseCode, almevttbl.UnitType, 
      almevttbl.UnitId, almevttbl.UnitName, almevttbl.ServerName, 
      almevttbl.StrParam, almevttbl.ExtraStrParam, almevttbl.ExtraStrParam2, 
      almevttbl.ExtraStrParam3, almevttbl.ParentCustId, almevttbl.ExtraParam1, 
      almevttbl.ExtraParam2, almevttbl.ExtraParam3,almevttbl.ExtraParam4,
      almevttbl.ExtraParam5, almevttbl.SRCIPADDRFAMILY,almevttbl.SrcIPAddress11,
      almevttbl.SrcIPAddress12,almevttbl.SrcIPAddress13,almevttbl.SrcIPAddress14, 
      almevttbl.DESTIPADDRFAMILY,almevttbl.DestIPAddress11,
      almevttbl.DestIPAddress12,almevttbl.DestIPAddress13,
      almevttbl.DestIPAddress14,  almevttbl.DestPort, almevttbl.SrcPort, 
      almevttbl.SessionDir, almevttbl.CustomerId, almevttbl.ProfileId, 
      almevttbl.ParentProfileId, almevttbl.CustomerName, almevttbl.AttkDir, 
      almevttbl.SubCategory, almevttbl.RiskCategory, almevttbl.AssetValue, 
      almevttbl.IPSAction, almevttbl.l4Protocol,almevttbl.ExtraStrParam4 ,
      almevttbl.ExtraStrParam5,almevttbl.username,almevttbl.ExtraStrParam6,
      IpAddrFamily1,IPAddrValue11,IPAddrValue12,IPAddrValue13,IPAddrValue14,
      IpAddrFamily2,IPAddrValue21,IPAddrValue22,IPAddrValue23,IPAddrValue24 
    FROM
           AlmEvtTbl PARTITION(ALMEVTTBLP20100323) WHERE AlmEvtId IN ( SELECT  * FROM 
      ( SELECT /*+ FIRST_ROWS(1000) INDEX (AlmEvtTbl AlmEvtTbl_Index) */AlmEvtId 
      FROM AlmEvtTbl PARTITION(ALMEVTTBLP20100323) where       ((AlmEvtTbl.Customerid 
      = 0 or AlmEvtTbl.ParentCustId = 0))  ORDER BY AlmEvtTbl.TIMESTAMP DESC)  
      WHERE ROWNUM  <  602) order by timestamp desc
    
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1      0.10       0.17          0          0          0           0
    Execute      1      0.00       0.00          0          0          0           0
    Fetch       42   1348.25    1521.24       1956   39029545          0         601
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total       44   1348.35    1521.41       1956   39029545          0         601
    
    Misses in library cache during parse: 1
    Optimizer mode: FIRST_ROWS
    Parsing user id: 82  
    
    Rows     Row Source Operation
    -------  ---------------------------------------------------
        601  PARTITION RANGE SINGLE PARTITION: 24 24 (cr=39029545 pr=1956 pw=1956 time=11043 us cost=0 size=7426 card=1)
        601   TABLE ACCESS BY LOCAL INDEX ROWID ALMEVTTBL PARTITION: 24 24 (cr=39029545 pr=1956 pw=1956 time=11030 us cost=0 size=7426 card=1)
        601    INDEX FULL SCAN ALMEVTTBL_INDEX PARTITION: 24 24 (cr=39029377 pr=1956 pw=1956 time=11183 us cost=0 size=0 card=1)(object id 72557)
        601     FILTER  (cr=39027139 pr=0 pw=0 time=0 us)
    169965204      COUNT STOPKEY (cr=39027139 pr=0 pw=0 time=24859073 us)
    169965204       VIEW  (cr=39027139 pr=0 pw=0 time=17070717 us cost=0 size=13 card=1)
    169965204        PARTITION RANGE SINGLE PARTITION: 24 24 (cr=39027139 pr=0 pw=0 time=13527031 us cost=0 size=48 card=1)
    169965204         TABLE ACCESS BY LOCAL INDEX ROWID ALMEVTTBL PARTITION: 24 24 (cr=39027139 pr=0 pw=0 time=10299895 us cost=0 size=48 card=1)
    169965204          INDEX FULL SCAN ALMEVTTBL_INDEX PARTITION: 24 24 (cr=1131414 pr=0 pw=0 time=3222624 us cost=0 size=0 card=1)(object id 72557)
    
    
    Elapsed times include waiting on following events:
      Event waited on                             Times   Max. Wait  Total Waited
      ----------------------------------------   Waited  ----------  ------------
      SQL*Net message to client                      42        0.00          0.00
      SQL*Net message from client                    42       11.54        133.54
      db file sequential read                      1956        0.20         28.00
      latch free                                     21        0.00          0.01
      latch: cache buffers chains                     9        0.01          0.02
    ********************************************************************************
    
    SQL ID : 0ushr863b7z39
    SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE 
      NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false') 
      NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),0), NVL(SUM(C2),0) 
    FROM
     (SELECT /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL("PLAN_TABLE") FULL("PLAN_TABLE") 
      NO_PARALLEL_INDEX("PLAN_TABLE") */ 1 AS C1, CASE WHEN 
      "PLAN_TABLE"."STATEMENT_ID"=:B1 THEN 1 ELSE 0 END AS C2 FROM 
      "SYS"."PLAN_TABLE$" "PLAN_TABLE") SAMPLESUB
    
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1      0.00       0.00          0          0          0           0
    Execute      1      0.00       0.00          0          0          0           0
    Fetch        1      0.00       0.01          1          3          0           1
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total        3      0.00       0.01          1          3          0           1
    
    Misses in library cache during parse: 1
    Misses in library cache during execute: 1
    Optimizer mode: ALL_ROWS
    Parsing user id: 82     (recursive depth: 1)
    
    Rows     Row Source Operation
    -------  ---------------------------------------------------
          1  SORT AGGREGATE (cr=3 pr=1 pw=1 time=0 us)
          0   TABLE ACCESS FULL PLAN_TABLE$ (cr=3 pr=1 pw=1 time=0 us cost=29 size=138856 card=8168)
    
    
    Elapsed times include waiting on following events:
      Event waited on                             Times   Max. Wait  Total Waited
      ----------------------------------------   Waited  ----------  ------------
      db file sequential read                         1        0.01          0.01
    ********************************************************************************
    
    SQL ID : bjkdb51at8dnb
    EXPLAIN PLAN SET STATEMENT_ID='PLUS30350011' FOR select almevttbl.AlmEvtId, 
      almevttbl.AlmType, almevttbl.ComponentId, almevttbl.TimeStamp, 
      almevttbl.Severity, almevttbl.State, almevttbl.Category, 
      almevttbl.CauseCode, almevttbl.UnitType, almevttbl.UnitId, 
      almevttbl.UnitName, almevttbl.ServerName, almevttbl.StrParam, 
      almevttbl.ExtraStrParam, almevttbl.ExtraStrParam2, almevttbl.ExtraStrParam3,
       almevttbl.ParentCustId, almevttbl.ExtraParam1, almevttbl.ExtraParam2, 
      almevttbl.ExtraParam3,almevttbl.ExtraParam4,almevttbl.ExtraParam5, 
      almevttbl.SRCIPADDRFAMILY,almevttbl.SrcIPAddress11,almevttbl.SrcIPAddress12,
      almevttbl.SrcIPAddress13,almevttbl.SrcIPAddress14, 
      almevttbl.DESTIPADDRFAMILY,almevttbl.DestIPAddress11,
      almevttbl.DestIPAddress12,almevttbl.DestIPAddress13,
      almevttbl.DestIPAddress14,  almevttbl.DestPort, almevttbl.SrcPort, 
      almevttbl.SessionDir, almevttbl.CustomerId, almevttbl.ProfileId, 
      almevttbl.ParentProfileId, almevttbl.CustomerName, almevttbl.AttkDir, 
      almevttbl.SubCategory, almevttbl.RiskCategory, almevttbl.AssetValue, 
      almevttbl.IPSAction, almevttbl.l4Protocol,almevttbl.ExtraStrParam4 ,
      almevttbl.ExtraStrParam5,almevttbl.username,almevttbl.ExtraStrParam6,
      IpAddrFamily1,IPAddrValue11,IPAddrValue12,IPAddrValue13,IPAddrValue14,
      IpAddrFamily2,IPAddrValue21,IPAddrValue22,IPAddrValue23,IPAddrValue24 FROM  
           AlmEvtTbl PARTITION(ALMEVTTBLP20100323) WHERE AlmEvtId IN ( SELECT  * FROM 
      ( SELECT /*+ FIRST_ROWS(1000) INDEX (AlmEvtTbl AlmEvtTbl_Index) */AlmEvtId 
      FROM AlmEvtTbl PARTITION(ALMEVTTBLP20100323) where       ((AlmEvtTbl.Customerid 
      = 0 or AlmEvtTbl.ParentCustId = 0))  ORDER BY AlmEvtTbl.TIMESTAMP DESC)  
      WHERE ROWNUM  <  602) order by timestamp desc
    
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1      0.28       0.26          0          0          0           0
    Execute      1      0.01       0.00          0          0          0           0
    Fetch        0      0.00       0.00          0          0          0           0
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total        2      0.29       0.27          0          0          0           0
    
    Misses in library cache during parse: 1
    Optimizer mode: FIRST_ROWS
    Parsing user id: 82  
    
    Elapsed times include waiting on following events:
      Event waited on                             Times   Max. Wait  Total Waited
      ----------------------------------------   Waited  ----------  ------------
      SQL*Net message to client                       1        0.00          0.00
      SQL*Net message from client                     1        0.00          0.00
    
    
    
    ********************************************************************************
    
    OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse       13      0.71       0.96          3         10          0           0
    Execute     14      0.20       0.29          4        304         26          21
    Fetch       92   2402.17    2714.85       3819   70033708          0        1255
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total      119   2403.09    2716.10       3826   70034022         26        1276
    
    Misses in library cache during parse: 10
    Misses in library cache during execute: 6
    
    Elapsed times include waiting on following events:
      Event waited on                             Times   Max. Wait  Total Waited
      ----------------------------------------   Waited  ----------  ------------
      SQL*Net message to client                      49        0.00          0.00
      SQL*Net message from client                    48       29.88        163.43
      db file sequential read                      1966        0.20         28.10
      latch free                                     21        0.00          0.01
      latch: cache buffers chains                     9        0.01          0.02
      latch: session allocation                       1        0.00          0.00
    
    
    OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse      940      0.51       0.73          1          2         38           0
    Execute   3263      1.93       2.62          7       1998         43          23
    Fetch     6049      1.32       4.41        214      12858         36       13724
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total    10252      3.78       7.77        222      14858        117       13747
    
    Misses in library cache during parse: 172
    Misses in library cache during execute: 168
    
    Elapsed times include waiting on following events:
      Event waited on                             Times   Max. Wait  Total Waited
      ----------------------------------------   Waited  ----------  ------------
      db file sequential read                        88        0.04          0.62
      latch: shared pool                              8        0.00          0.00
      latch: row cache objects                        2        0.00          0.00
      latch free                                      1        0.00          0.00
      latch: session allocation                       1        0.00          0.00
    
       34  user  SQL statements in session.
     3125  internal SQL statements in session.
     3159  SQL statements in session.
    ********************************************************************************
    Trace file: ora11g_ora_2064.trc
    Trace file compatibility: 11.01.00
    Sort options: default
    
           6  sessions in tracefile.
          98  user  SQL statements in trace file.
        9111  internal SQL statements in trace file.
        3159  SQL statements in trace file.
          89  unique SQL statements in trace file.
       30341  lines in trace file.
        6810  elapsed seconds in trace file.
        
        
     ###################################### AutoTrace Output#################   
        
        
    
     
     Statistics
    ----------------------------------------------------------
           3901  recursive calls
              0  db block gets
       39030275  consistent gets
           1970  physical reads
            140  redo size
         148739  bytes sent via SQL*Net to client
            860  bytes received via SQL*Net from client
             42  SQL*Net roundtrips to/from client
             73  sorts (memory)
              0  sorts (disk)
            601  rows processed
  • 6. Re: Query Tuning: 11g
    Gaff Journeyer
    Currently Being Moderated
    For getting back only 600 rows I see a whole lot of consistent gets. And I assume you are putting in the /*+ FIRST_ROWS */ hint yourslef? Have you tried the query without it? In my experience, it actually makes things worse half the time. Let Oracle do what it wants to do.

    Also, why are you doing a +select *+ in the IN clause when you just want to check for the existence of one column?


    select almevttbl.AlmEvtId, lots_of_other_stuff
    FROM
    AlmEvtTbl PARTITION(ALMEVTTBLP20100323)
    WHERE AlmEvtId IN
    _( SELECT * FROM_
    ( SELECT /*+ FIRST_ROWS(1000) INDEX (AlmEvtTbl AlmEvtTbl_Index) */AlmEvtId
    FROM AlmEvtTbl PARTITION(ALMEVTTBLP20100323)
    where ((AlmEvtTbl.Customerid = 0 or AlmEvtTbl.ParentCustId = 0))
    ORDER BY AlmEvtTbl.TIMESTAMP DESC)
    WHERE ROWNUM < 602) order by timestamp desc



    I wouldn't have either the ORDER BY or FIRST_ROWS as part of the IN query. You might even re-code it to make it a WHERE EXITS query, see if that helps. I wouldn't tell it what index to use either. If you have the proper column indexed and the stats say it makes sense to use it, Oracle will probably use it.

    My guess is that you are "helping" Oracle and driving it down an execution path that is far from optimal.


    How is AlmEvtId generated? Is this a unique ID? Better yet, a sequence generated number? If so, maybe just make a unique index on that and take the largest 602.

    Finally, I've had experience in doing queries on a single disk machine that was getting a large volume of continuous update data, particularly into the table being queried. It takes a toll. More disks and more spindles (and ASM?) might help you out there. SSD drives would help too.
  • 7. Re: Query Tuning: 11g
    user503699 Expert
    Currently Being Moderated
    Well, the TkProf show that most of the time is being spent in accessing index and corresponding table blocks from memory, one block at a time.
    Any particular reason why you have included the index hint in the inner query? How does the query perform when you lose that index hint.
    Also, you had earlier mentioned that your optimizer mode is ALL_ROWS at instance level. But your TkProf shows that your main query was
    executed in FIRST_ROWS mode. Do you happen to run this query in FIRST_ROWS mode (maybe at session level) ?
    In inner as well as outer query, your table partition is being accessed using index full scan. That looks odd especially as optimizer is doing
    1131414 consistent gets to fetch 169965204 rows (in inner query) and 39029377 consistent gets to fetch 601 rows (in outer query).
    As you have mentioned earlier that the optimizer_index* initialization parameters are set to default values, it appears that statistics on the
    index differ a lot from actual data. Is that the case? What is the output of EXPLAIN PLAN?
    You may want to gather stats on index (and table partition) and post the TkProf.

    p.s. Looking at the query, it appears that the query is probably written with the intention to avoid sorting large data but returning the data sorted by TIMESTAMP column in descending order. It looks as if the hints are used to dictate the optimizer to process the query in a particular way. For e.g. retrieve minimum data in inner query sorted by TIMESTAMP in descending order and let the outer query use, say NESTED LOOP join, to retrieve rest of the columns for each AlmEvtId such that final resultset is ordered by TIMESTAMP in descending order, without explicitly using an ORDER BY clause in outer query. If that is the case, you will want to rethink about your approach because this query may not always work as you expect it to. There is only one reliable way to get data in a particular order and that is using an ORDER BY clause explicitly.
  • 8. Re: Query Tuning: 11g
    Dom Brooks Guru
    Currently Being Moderated
    Do you need the double pass at this table?
    SELECT *
    FROM  
    (SELECT almevttbl.AlmEvtId, 
      almevttbl.AlmType, almevttbl.ComponentId, almevttbl.TimeStamp, 
      almevttbl.Severity, almevttbl.State, almevttbl.Category, 
      almevttbl.CauseCode, almevttbl.UnitType, almevttbl.UnitId, 
      almevttbl.UnitName, almevttbl.ServerName, almevttbl.StrParam, 
      almevttbl.ExtraStrParam, almevttbl.ExtraStrParam2, almevttbl.ExtraStrParam3,
       almevttbl.ParentCustId, almevttbl.ExtraParam1, almevttbl.ExtraParam2, 
      almevttbl.ExtraParam3,almevttbl.ExtraParam4,almevttbl.ExtraParam5, 
      almevttbl.SRCIPADDRFAMILY,almevttbl.SrcIPAddress11,almevttbl.SrcIPAddress12,
      almevttbl.SrcIPAddress13,almevttbl.SrcIPAddress14, 
      almevttbl.DESTIPADDRFAMILY,almevttbl.DestIPAddress11,
      almevttbl.DestIPAddress12,almevttbl.DestIPAddress13,
      almevttbl.DestIPAddress14,  almevttbl.DestPort, almevttbl.SrcPort, 
      almevttbl.SessionDir, almevttbl.CustomerId, almevttbl.ProfileId, 
      almevttbl.ParentProfileId, almevttbl.CustomerName, almevttbl.AttkDir, 
      almevttbl.SubCategory, almevttbl.RiskCategory, almevttbl.AssetValue, 
      almevttbl.IPSAction, almevttbl.l4Protocol,almevttbl.ExtraStrParam4 ,
      almevttbl.ExtraStrParam5,almevttbl.username,almevttbl.ExtraStrParam6,
      IpAddrFamily1,IPAddrValue11,IPAddrValue12,IPAddrValue13,IPAddrValue14,
      IpAddrFamily2,IPAddrValue21,IPAddrValue22,IPAddrValue23,IPAddrValue24 
    FROM AlmEvtTbl PARTITION(ALMEVTTBLP20100323) 
    WHERE (AlmEvtTbl.Customerid = 0 
    OR     AlmEvtTbl.ParentCustId = 0)  
    ORDER BY AlmEvtTbl.TIMESTAMP DESC)  
    WHERE ROWNUM  <  602);
    Can you get an execution plan with statistic estimates via dbms_xplan.display_cursor either from memory or by running the sql with a /*+ gather_plan_statistics */ hint?

    What's indexed on this table?



    Edit; Replied to wrong post
  • 9. Re: Query Tuning: 11g
    user503699 Expert
    Currently Being Moderated
    DomBrooks wrote:
    Do you need the double pass at this table?
    I believe you wanted to respond to OP.
    That would work provided AlmEvtId is primary/unique key column (and it probably is in this case).
  • 10. Re: Query Tuning: 11g
    761587 Newbie
    Currently Being Moderated
    @user503699
    First of all thanks for your inputs.

    You Wrote::
    it appears that statistics on the
    index differ a lot from actual data. Is that the case? What is the output of EXPLAIN PLAN?
    You may want to gather stats on index (and table partition) and post the TkProf.

    --Please let me know
    how can i conclude that statistics on theindex differ a lot from actual data.
    how can i gather the stats on index (and table partition) and post the TkProf.

    You Wrote::
    There is only one reliable way to get data in a particular order and that is using an ORDER BY clause explicitly.
    ----
    You are right what ever you wrote in the P.S. Please let me know how can i use an ORDER BY clause explicitly.
    in the mentioned query.
    ----

    Please consider my ignorance.
  • 11. Re: Query Tuning: 11g
    761587 Newbie
    Currently Being Moderated
    @Gaff

    Thanks for you inputs

    1- And I assume you are putting in the /*+ FIRST_ROWS */ hint yourslef?
    --yes

    2- Have you tried the query without it?
    --Not yet

    3- How is AlmEvtId generated? Is this a unique ID
    Yes it is unique and indexed also.
  • 12. Re: Query Tuning: 11g
    Gaff Journeyer
    Currently Being Moderated
    Here's how to gather stats.

    http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_stats.htm#i1036276
  • 13. Re: Query Tuning: 11g
    user503699 Expert
    Currently Being Moderated
    user12834233 wrote:
    @user503699
    First of all thanks for your inputs.

    You Wrote::
    it appears that statistics on the
    index differ a lot from actual data. Is that the case? What is the output of EXPLAIN PLAN?
    You may want to gather stats on index (and table partition) and post the TkProf.

    --Please let me know
    how can i conclude that statistics on theindex differ a lot from actual data.
    how can i gather the stats on index (and table partition) and post the TkProf.
    If you find big difference in cardinalities between EXPLAIN PLAN and Row Source Operation, that would mean the statistics are not correct. You can use procedures in DBMS_STATS package to collect statistics. Speak to your DBA if you are not aware of it.
    You Wrote::
    There is only one reliable way to get data in a particular order and that is using an ORDER BY clause explicitly.
    ----
    You are right what ever you wrote in the P.S. Please let me know how can i use an ORDER BY clause explicitly.
    in the mentioned query.
    ----

    Please consider my ignorance.
    Use the query suggested by DomBrooks above.
  • 14. Re: Query Tuning: 11g
    Dom Brooks Guru
    Currently Being Moderated
    You've got two threads open on the same issue - nasty habit.
    Re: Select Query failing on a  table that has per sec heavy insertions.


    1. A repeat from above: Do you need the double pass on the ALMEVTTBL table PARTITION (ALMEVTTBLP20100323)? Can you not just access this table once?

    2. A repeat from above: Can you pull the execution plans from memory for the good and bad plans using dbms_xplan.display_cursor? I'd like to see the actual rows and estimates for both good and bad.

    3. The heavy inserts would not necessarily cause a problem. Writers don't block readers after all. But, but, but, associated to this high level of activity, you presumably have some management of partitions and statistics going on. Can you describe what's going on there? Are you creating new partitions? What do you do with the stats when you create this new partition? Etc
1 2 Previous Next

Legend

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