13 Replies Latest reply: Apr 20, 2014 9:28 AM by Lothar Flatz RSS

    Query Performance

    PVM

      Hi All,

       

      I have a query keep doing FTS, but when i look at the execution plan its not showing any FTS. can you please let me know why it is so?

       

       

      Env details:-

       

       

      DB --- 11.2.0.3

      OS --- RHEL 6

       

       

      Query :-

       

       

      select ccagentsta0_.LOGGED_IN as LOGGED1_424_, ccagentsta0_.VZ_ID as VZ2_424_, ccagentsta0_.ACTIVE as ACTIVE424_, ccagentsta0_.AGENT_STATE as AGENT4_424_, ccagentsta0_.APPLICATION_CODE as APPLICAT5_424_, ccagentsta0_.CREATED_ON as CREATED6_424_, ccagentsta0_.CURRENT_ORDER as CURRENT7_424_, ccagentsta0_.CURRENT_TASK as CURRENT8_424_, ccagentsta0_.HELM_ID as HELM9_424_, ccagentsta0_.LAST_UPDATED as LAST10_424_, ccagentsta0_.LOCATION as LOCATION424_, ccagentsta0_.LOGGED_OUT as LOGGED12_424_, ccagentsta0_.SUPERVISOR_VZID as SUPERVISOR13_424_, ccagentsta0_.VENDOR_NAME as VENDOR14_424_ from PRISM.AGENT_STATE ccagentsta0_ where ccagentsta0_.VZ_ID='z489593'  and ccagentsta0_.ACTIVE='Y';
      

       

       

       

       

       

       

      SQL> select count(*) from agent_state;
      
      
        COUNT(*)
      ----------
         3174925
      

       

       

       

       

      Execution Plan :-

       

       

      -----------------------------------------------------------------------------------------------
      | Id  | Operation                   | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
      -----------------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT            |                 |     1 |   105 |   275   (0)| 00:00:04 |
      |*  1 |  TABLE ACCESS BY INDEX ROWID| AGENT_STATE     |     1 |   105 |   275   (0)| 00:00:04 |
      |*  2 |   INDEX RANGE SCAN          | AGENT_STATE_IDX |   273 |       |     4   (0)| 00:00:01 |
      --------------------------------------------------------------------------------
      

       

       

       

       

      Table Scan     PRISM.AGENT_STATE       0.1166666667
      

       

       

      I am using the below query to check the long runners

       

       

      Select OPNAME, TARGET, ELAPSED_SECONDS/60 as ELAPSED_Min, l.USERNAME, n.osuser, n.program, n.machine, n.PROCESS,
             l.SQL_ID, SQL_FULLTEXT
      from gv$session_longops l inner join GV$SQLAREA s on l.INST_ID = s.INST_ID and l.SQL_ID = s.SQL_ID
           left outer join gv$session n on l.inst_id = n.inst_id and l.sid = n.sid
      where time_remaining != 0; 
      

       

       

      SQL> desc agent_state
       Name                                      Null?    Type
       ----------------------------------------- -------- ----------------------------
       VZ_ID                                     NOT NULL VARCHAR2(50)
       HELM_ID                                   NOT NULL NUMBER
       ACTIVE                                    NOT NULL CHAR(1)
       CREATED_ON                                         TIMESTAMP(6)
       LOGGED_IN                                 NOT NULL TIMESTAMP(6)
       LOGGED_OUT                                         TIMESTAMP(6)
       AGENT_STATE                               NOT NULL VARCHAR2(32)
       LAST_UPDATED                              NOT NULL TIMESTAMP(6)
       CURRENT_ORDER                                      VARCHAR2(128)
       CURRENT_TASK                                       NUMBER
       APPLICATION_CODE                                   VARCHAR2(16)
       VENDOR_NAME                               NOT NULL VARCHAR2(50)
       LOCATION                                  NOT NULL VARCHAR2(50)
       SUPERVISOR_VZID                           NOT NULL VARCHAR2(50)
      

       

       

      The table has a composite index on columns VZ_ID and LOGGED_IN.

       

       

      Can you please let me know your suggestion on this.

       

       

      Thanks,

      PV

        • 1. Re: Query Performance
          rp0428
          I have a query keep doing FTS, but when i look at the execution plan its not showing any FTS. can you please let me know why it is so?

          Who knows? You haven't posted the info needed to help with a tuning request. See the FAQ for how to post a tuning request.

           

          Your table has 3174925 rows and for all we know ALL OF THEM have 'VZ_ID='z489593' and are ACTIVE='Y'. If so, why would Oracle use an index to retrieve ALL of the rows?

           

          1. Post the DDL for the table and all indexes.

          2. Confirm that the stats are current

          3. Post the statement used to collect the stats and that show any histograms.

          4. Post the row counts for the two predicates used. How many rows have an id of 'z489593'? How many rows are ACTIVE='Y'? how many have that combination? Post GROUP BY and COUNT queries that show those three values.

          • 2. Re: Query Performance
            PVM

            Sorry for posting without adequate details

             

            Please find the Table DDL

             

             

             

            CREATE TABLE "PRISM"."AGENT_STATE"
               ( "VZ_ID" VARCHAR2(50 BYTE) NOT NULL ENABLE,
              "HELM_ID" NUMBER NOT NULL ENABLE,
              "ACTIVE" CHAR(1 BYTE) NOT NULL ENABLE,
              "CREATED_ON" TIMESTAMP (6),
              "LOGGED_IN" TIMESTAMP (6),
              "LOGGED_OUT" TIMESTAMP (6),
              "AGENT_STATE" VARCHAR2(32 BYTE) NOT NULL ENABLE,
              "LAST_UPDATED" TIMESTAMP (6) NOT NULL ENABLE,
              "CURRENT_ORDER" VARCHAR2(128 BYTE),
              "CURRENT_TASK" NUMBER,
              "APPLICATION_CODE" VARCHAR2(16 BYTE),
              "VENDOR_NAME" VARCHAR2(50 BYTE) NOT NULL ENABLE,
              "LOCATION" VARCHAR2(50 BYTE) NOT NULL ENABLE,
              "SUPERVISOR_VZID" VARCHAR2(50 BYTE) NOT NULL ENABLE,
              CONSTRAINT "AGENT_STATE_PKY" PRIMARY KEY ("VZ_ID", "LOGGED_IN");
            

             

            Composite index on VZ_ID, LOGGED_IN.

             

            Stats:-

             

            NUM_ROWS3219540
            BLOCKS58866
            AVG_ROW_LEN106
            SAMPLE_SIZE3219540
            LAST_ANALYZED17-APR-14
            LAST_ANALYZED_SINCE17-APR-14

             

            Some of the values taken from that table

             

            SELECT VZ_ID, ACTIVE, COUNT(*) FROM agent_state GROUP BY VZ_ID,ACTIVE order by count(*) desc;
            

             

            VZ_ID                                              ACTIVE   COUNT(*)
            -------------------------------------------------- ------ ----------
            r000888                                            N           26385 
            z385727                                            N            6142 
            z544724                                            N            5604 
            z145602                                            N            3497 
            v882676                                            N            3412 
            v143893                                            N            3085 
            z556669                                            N            3004 
            z530633                                            N            2924 
            v113274                                            N            2859 
            z755232                                            N            2824 
            z748468                                            N            2806 
            z412798                                            N            2803 
            v802877                                            N            2664 
            z482392                                            N            2575 
            z419734                                            N            2526 
            z559125                                            N            2491 
            z479066                                            N            2478 
            z588466                                            N            2446 
            

             

            SELECT VZ_ID,COUNT(*) FROM agent_state WHERE VZ_ID='z489593' GROUP BY VZ_ID;
            
            VZ_ID                                                COUNT(*)
            -------------------------------------------------- ----------
            z489593                                                  1191 
            

             

            SELECT ACTIVE,COUNT(*) FROM agent_state WHERE ACTIVE='Y' GROUP BY ACTIVE;
            
            
            ACTIVE   COUNT(*)
            ------ ----------
            Y            2480 
            

             

            Some of the values from output

             

            SELECT VZ_ID, ACTIVE, COUNT(*) FROM agent_state WHERE ACTIVE='Y' GROUP BY VZ_ID,ACTIVE order by count(*) desc;
            
            
            VZ_ID                                              ACTIVE   COUNT(*)
            -------------------------------------------------- ------ ----------
            z655314                                            Y               8 
            z831181                                            Y               7 
            v054784                                            Y               5 
            v102427                                            Y               5 
            z887614                                            Y               5 
            v813533                                            Y               5 
            v311777                                            Y               4 
            v789824                                            Y               4 
            v361364                                            Y               4 
            v817888                                            Y               4 
            v810323                                            Y               4 
            v800125                                            Y               3 
            v798037                                            Y               3 
            v236756                                            Y               3 
            v803774                                            Y               3 
            v414091                                            Y               3 
            v815829                                            Y               3 
            v191792                                            Y               3 
            z833001                                            Y               3 
            v817931                                            Y               3 
            v813856                                            Y               2 
            v075881                                            Y               2 
            

             

            Hope this is fine.

             

            Thanks,

            Mani

            • 3. Re: Query Performance
              PVM

              Adding one more

               

              SELECT VZ_ID, ACTIVE, COUNT(*) FROM agent_state WHERE VZ_ID='z489593' and ACTIVE='Y' GROUP BY VZ_ID, ACTIVE;
              

               

              No rows selected

               

              Thanks,

              PV

              • 4. Re: Query Performance
                Lothar Flatz

                we do need runtime statistcs for the statement.

                 

                set lines 300 pages 4000

                 

                alter session set statistcis_level=all;

                 

                now run your statement

                 

                and right after it issue:

                 

                select * from table (dbms_xplan.display_cursor(null,null, 'RUNSTATS_LAST'));

                 

                let us also double check if we have histogramms on the columns:

                 

                select column_name, num_distinct , num_buckets from dba_tab_columns where table_name='AGENT_STATE';

                 

                if num_buckets is one we do not have histograms, which is bad.

                • 5. Re: Query Performance
                  Lothar Flatz

                  And to add:

                  "The table has a composite index on columns VZ_ID and LOGGED_IN."

                  You need a compressed  composite Index on VZ_ID and Active,

                  That is much more selective than VZ_ID by itself as your count proves

                  • 6. Re: Query Performance
                    Top.Gun

                    'Table Scan     PRISM.AGENT_STATE       0.1166666667'

                    Where did the above line come from?

                     

                    How did you get the execution plan? Can you prove it's not an explain plan?

                    • 7. Re: Query Performance
                      Top.Gun

                      'select ccagentsta0_.LOGGED_IN as LOGGED1_424_, ccagentsta0_.VZ_ID as VZ2_424_, ccagentsta0_.ACTIVE as ACTIVE424'

                      This is not the full sql statement, so you need to post it.

                      • 8. Re: Query Performance
                        Lothar Flatz

                        when you click on the statement you can copy paste to get the full statement.

                        it is:

                        select ccagentsta0_.LOGGED_IN as LOGGED1_424_, ccagentsta0_.VZ_ID as VZ2_424_, ccagentsta0_.ACTIVE as ACTIVE424_, ccagentsta0_.AGENT_STATE as AGENT4_424_, ccagentsta0_.APPLICATION_CODE as APPLICAT5_424_, ccagentsta0_.CREATED_ON as CREATED6_424_, ccagentsta0_.CURRENT_ORDER as CURRENT7_424_, ccagentsta0_.CURRENT_TASK as CURRENT8_424_, ccagentsta0_.HELM_ID as HELM9_424_, ccagentsta0_.LAST_UPDATED as LAST10_424_, ccagentsta0_.LOCATION as LOCATION424_, ccagentsta0_.LOGGED_OUT as LOGGED12_424_, ccagentsta0_.SUPERVISOR_VZID as SUPERVISOR13_424_, ccagentsta0_.VENDOR_NAME as VENDOR14_424_

                          from PRISM.AGENT_STATE ccagentsta0_

                        where ccagentsta0_.VZ_ID='z489593'  and ccagentsta0_.ACTIVE='Y'; 

                         

                        Explain plan should be fine if really literals are used instead of bind variable, which I doubt.

                        However a correct Index might fix this.

                        • 9. Re: Query Performance
                          Top.Gun

                          thanks

                          • 10. Re: Query Performance
                            Top.Gun

                            You need an index on (VZ_ID, ACTIVE) or (ACTIVE, VZ_ID)

                            • 11. Re: Query Performance
                              Lothar Flatz

                              I have seen such issue before. The table contains the historical cases t (active = 'N') therefore an index just on VZ_ID (and an irrelevant column) get increasingly inselective over time. We need both search criteria for a good access plan.

                              • 12. Re: Query Performance
                                Top.Gun

                                An index on (VZ_ID) should also work unless the cluster factor is really bad....

                                • 13. Re: Query Performance
                                  Lothar Flatz

                                  OP has got an index that starts with VZ_ID. Based on above counts VZ_ID is a lot less selective that the combination of VZ_ID and ACTIVE. Since I believe after some processing the active flag is set to 'N' all old entries are featuring active ='N'. You can bet the clustering factor is bad on VZ_ID. Do you understand why this is likely? Think about the processing over time.