10 Replies Latest reply: Jun 18, 2013 6:37 PM by 977635 RSS

    In OEM (DBConsole) performance screen...

    977635

      I have a question regarding Oracle 11.2 DBConsole or OEM performance screen.

       

      In the "Average Active Sessions" graph on the Performance screen, and/or in the Top Activities screen, what does "other" refer to.

       

      Lately I've been noticing a lot of spikes where our normal usage is abour 6 to 8 CPU cycles, but then the spikes jump up to 24 to 30 CPUs.

      The main color is pink, which when I highlight it, it is "other".

       

      So my question is what does "other' refer to, or how can I find this out?

       

      I tried running an ASH report, but no value.

        • 1. Re: In OEM (DBConsole) performance screen...
          L-MachineGun

          If you click on it (other) you will find out.

           

          • 2. Re: In OEM (DBConsole) performance screen...
            977635

            LOL... I guess I deserved that one.   yep, that takes me to the "other" screen and now it shows the processing of other is "latch free" and "null event".

             

            Then, I click on "latch free" and it shows a graph of "Wait Event Occurrences Per Duration Since Instance Startup"  This isn't much help.  So what do I make of all this useless information.   I see the queries that are common to one table.  It is a high use table.  It is fairly small, but is highly used and is usually a Full Table Scan on it.

             

            • 3. Re: In OEM (DBConsole) performance screen...
              L-MachineGun

              Looking at these statistics in a "graphic" form is "nice" but only makes sense if they are significant enough to be noticed.

              This is why you have the AWR reports where you can look at the statistics in the context of a time period related to events and other information.

              • 4. Re: In OEM (DBConsole) performance screen...
                977635

                I ran an ash report to look closer at the statistics and I see the latch free wait in top user wait events and top events p1/p2/p3.

                So, I researched a little bit about Oracle Latch Free Wait event and read that "the latch free wait event occurs when a session needs a latch, tries to get the latch, but fails because someone else has it."  But we are not updating this table, so I'm not sure why we are getting the latch free waits.  Are latches used when just a select statement is issued?

                 

                In our case, the following query is typical when this event occurs:

                 

                SELECT schemaId, fieldId, groupId, permission FROM field_permissions WHERE schemaId>=538 AND schemaId<=544 ORDER BY 1 ASC, 2 ASC, 3 ASC;

                 

                When I ran explain plan, it shows that Oracle is not using an index, even though an index does exist on the schemaId field.

                I'm assuming that is because we are using a filter to grab all values where schemaId is between 538 and 544 (in this case).

                {code}

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

                | Id  | Operation               | Name              | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |

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

                |   0 | SELECT STATEMENT        |                   |  8464 |   148K|   534   (1)| 00:00:07 |        |      |            |

                |   1 |  PX COORDINATOR         |                   |       |       |            |          |        |      |            |

                |   2 |   PX SEND QC (ORDER)    | :TQ10001          |  8464 |   148K|   534   (1)| 00:00:07 |  Q1,01 | P->S | QC (ORDER) |

                |   3 |    SORT ORDER BY        |                   |  8464 |   148K|   534   (1)| 00:00:07 |  Q1,01 | PCWP |            |

                |   4 |     PX RECEIVE          |                   |  8464 |   148K|   533   (1)| 00:00:07 |  Q1,01 | PCWP |            |

                |   5 |      PX SEND RANGE      | :TQ10000          |  8464 |   148K|   533   (1)| 00:00:07 |  Q1,00 | P->P | RANGE      |

                |   6 |       PX BLOCK ITERATOR |                   |  8464 |   148K|   533   (1)| 00:00:07 |  Q1,00 | PCWC |            |

                |*  7 |        TABLE ACCESS FULL| FIELD_PERMISSIONS |  8464 |   148K|   533   (1)| 00:00:07 |  Q1,00 | PCWP |            |

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

                 

                Predicate Information (identified by operation id):

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

                 

                   7 - filter("SCHEMAID"<=579 AND "SCHEMAID">=573)

                {code}

                • 5. Re: In OEM (DBConsole) performance screen...
                  L-MachineGun

                  1) Not always an index is more optimal specially for only 8464 rows

                  2) Not always parallel will reduce response time -- latch free wait may be due to parallel operations

                   

                  Execute your query using sqlplus:

                   

                  SET AUTOT ON
                  select ...your query ...
                  -- You will get the execution plan and statistics --
                  
                  • 6. Re: In OEM (DBConsole) performance screen...
                    977635

                    Good point about the parallel executions.

                    The table is configured with parallelism of 2by default.

                     

                    Using autotrace on, it shows a slightly different execution plan, now using the index.

                    I did nothing different and did not change anything in the database.

                     

                    SELECT schemaId, fieldId, groupId, permission FROM field_permissions WHERE schemaId>=538 AND schemaId<=544 ORDER BY 1 ASC, 2 ASC, 3 ASC;

                     

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

                    | Id  | Operation                    | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |

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

                    |   0 | SELECT STATEMENT             |                       |  3809 | 68562 |   455   (1)| 00:00:06 |

                    |   1 |  SORT ORDER BY               |                       |  3809 | 68562 |   455   (1)| 00:00:06 |

                    |   2 |   TABLE ACCESS BY INDEX ROWID| FIELD_PERMISSIONS     |  3809 | 68562 |   454   (0)| 00:00:06 |

                    |*  3 |    INDEX RANGE SCAN          | FIELD_PERMISSIONS_IND |  3809 |       |    14   (0)| 00:00:01 |

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

                     

                    Predicate Information (identified by operation id):

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

                     

                       3 - access("SCHEMAID">=538 AND "SCHEMAID"<=544)

                     

                     

                    Statistics

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

                              1  recursive calls

                              0  db block gets

                            930  consistent gets

                              0  physical reads

                              0  redo size

                         161020  bytes sent via SQL*Net to client

                           5133  bytes received via SQL*Net from client

                            421  SQL*Net roundtrips to/from client

                              1  sorts (memory)

                              0  sorts (disk)

                           6297  rows processed

                    • 7. Re: In OEM (DBConsole) performance screen...
                      Lubiez Jean-Valentin

                      Hello,

                       

                       

                      When I ran explain plan, it shows that Oracle is not using an index, even though an index does exist on the schemaId field.

                      I'm assuming that is because we are using a filter to grab all values where schemaId is between 538 and 544 (in this case).

                       

                      The use of the Index depends also on the Selectivity of the query. If you have too many words to look for in a book, reading the book may be faster than querying the Index for each word and reading the matching page in the book.

                       

                      About the Latch the following link is interesting:

                       

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

                       

                       

                      Hope this help.

                      Best Regards,

                      Jean-Valentin Lubiez

                      • 8. Re: In OEM (DBConsole) performance screen...
                        977635

                        Well, actually it is the difference of the values of the range of items I was selecting.

                         

                        The first query (I copied the wrong query into my window), was actually using "filter("SCHEMAID"<=579 AND "SCHEMAID">=573)".

                        This will do a full table scan, and takes .89 seconds to execute.

                         

                        The second query uses filter "access("SCHEMAID">=538 AND "SCHEMAID"<=544)" and uses an index and takes .76 seconds.

                         

                        Weird!

                        • 9. Re: In OEM (DBConsole) performance screen...
                          Lubiez Jean-Valentin

                          Hello,

                           

                           

                          The Optimizer makes calculation and choose the execution plan according to it, here:

                           

                          - 1. The Filter Predicate (with TABLE ACCESS FULL) is used when the cardinality of the query is about 8464.

                           

                          - 2. The Access Predicate (with INDEX RANGE SCAN) is used when the cardinality of the query is about 3809.

                           

                          So, with the second range of items, the query is more selective and the Optimizer choose the Index. This may explain the two executions plan you've noticed:

                           

                          http://docs.oracle.com/cd/E11882_01/server.112/e16638/optimops.htm#PFGRF94599

                           

                           

                          Hope this help.

                          Best Regards,

                          Jean-Valentin Lubiez

                          • 10. Re: In OEM (DBConsole) performance screen...
                            977635

                            Thanks Jean.  That makes sense.