1 2 Previous Next 19 Replies Latest reply: Mar 25, 2010 11:04 PM by 761587 RSS

    Query Tuning: 11g

    761587
      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
          How to post a SQL statement tuning request:

          HOW TO: Post a SQL statement tuning request - template posting
          • 2. Re: Query Tuning: 11g
            Gaff
            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
              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
                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
                  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
                    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
                      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
                        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
                          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
                            @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
                              @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
                                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
                                  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
                                    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