12 Replies Latest reply: Jan 11, 2013 8:38 AM by 983554 RSS

    How Can I make this query run faster

    983554
      SQL> EXPLAIN PLAN FOR SELECT a.txid, a.methodid, a.serviceid, a.nodename, a.env, a.ts, a.te, a.resultcode, a.resultmessage FROM g2log.txmaster a,
      2 g2log.txlookup b WHERE b.key = 'sbcgnfttxuniquedslamportid' AND b.value = 'MTC3LS733001-1-1-2-13' AND a.txid = b.txid ORDER BY ts desc;

      Explained.

      Elapsed: 00:00:00.01
      SQL> @$ORACLE_HOME/rdbms/admin/utlxpls.sql

      PLAN_TABLE_OUTPUT
      --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      Plan hash value: 3790334907

      ------------------------------------------------------------------------------------------------------------------------------
      | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
      ------------------------------------------------------------------------------------------------------------------------------
      | 0 | SELECT STATEMENT | | 578 | 108K| 1267 (1)| 00:00:16 | | |
      | 1 | PX COORDINATOR | | | | | | | |
      | 2 | PX SEND QC (ORDER) | :TQ10001 | 578 | 108K| 1267 (1)| 00:00:16 | | |
      | 3 | SORT ORDER BY | | 578 | 108K| 1267 (1)| 00:00:16 | | |
      | 4 | PX RECEIVE | | | | | | | |
      | 5 | PX SEND RANGE | :TQ10000 | | | | | | |
      | 6 | NESTED LOOPS | | | | | | | |
      | 7 | NESTED LOOPS | | 578 | 108K| 1266 (1)| 00:00:16 | | |
      | 8 | PX PARTITION RANGE ALL | | 578 | 44506 | 109 (0)| 00:00:02 | 1 | 27 |
      | 9 | TABLE ACCESS BY LOCAL INDEX ROWID| TXLOOKUP | 578 | 44506 | 109 (0)| 00:00:02 | 1 | 27 |
      |* 10 | INDEX RANGE SCAN | TX_KEY_VAL | 578 | | 58 (0)| 00:00:01 | 1 | 27 |
      |* 11 | INDEX UNIQUE SCAN | TXMASTER_TXID_PK | 1 | | 1 (0)| 00:00:01 | | |
      | 12 | TABLE ACCESS BY GLOBAL INDEX ROWID | TXMASTER | 1 | 116 | 2 (0)| 00:00:01 | ROWID | ROWID |
      ------------------------------------------------------------------------------------------------------------------------------

      Predicate Information (identified by operation id):
      ---------------------------------------------------

      10 - access("B"."KEY"='sbcgnfttxuniquedslamportid' AND "B"."VALUE"='MTC3LS733001-1-1-2-13')
      11 - access("A"."TXID"="B"."TXID")

      25 rows selected.


      I have gathered stats on the two tables as well

      SQL> select count(*) from g2log.txmaster;

      COUNT(*)
      ----------
      88812

      SQL> select count(*) from g2log.txlookup;

      COUNT(*)
      ----------
      228883
        • 2. Re: How Can I make this query run faster
          user503635
          Hi,

          Looks the table is set for parallel access and is partitioned.

          However both tables used in your query are small tables (very very small). It needs none of the above. Actually they may worsen your performance instead od improve.

          Just create index on column taxid for table a, b, b.key will do if not yet done.

          Hope it helps

          Edited by: user503635 on Jan 8, 2013 7:07 AM
          • 3. Re: How Can I make this query run faster
            983554
            But they both already have indexes:

            SQL> select column_name, index_name from dba_ind_columns where table_name = 'TXMASTER';

            COLUMN_NAME INDEX_NAME
            --------------- ------------------------------
            TXID TXMASTER_TXID_PK
            TS TX_TS
            NODENAME TX_NODE_CLUSTER
            ENV TX_NODE_CLUSTER
            NODENAME TX_NODENAME
            TXID PK_TXMASTER

            6 rows selected.

            Elapsed: 00:00:00.00
            SQL> select column_name, index_name from dba_ind_columns where table_name = 'TXLOOKUP';

            COLUMN_NAME INDEX_NAME
            --------------- ------------------------------
            TXID COMPND_LOOKUP
            KEY COMPND_LOOKUP
            VALUE COMPND_LOOKUP
            KEY TX_ORDERED_KEY_VAL
            VALUE TX_ORDERED_KEY_VAL
            LOGTIME TX_ORDERED_KEY_VAL
            VALUE TX_KEY_VAL
            KEY TX_KEY_VAL

            8 rows selected.
            • 4. Re: How Can I make this query run faster
              sb92075
              consider testing performance when PARALLEL is disabled & not used.
              • 5. Re: How Can I make this query run faster
                983554
                I bellieve that it is disabled as well:

                SQL> show parameter parallel

                NAME TYPE VALUE
                ------------------------------------ ----------- ------------------------------
                fast_start_parallel_rollback string LOW
                parallel_adaptive_multi_user boolean TRUE
                parallel_automatic_tuning boolean FALSE
                parallel_degree_limit string CPU
                parallel_degree_policy string MANUAL
                parallel_execution_message_size integer 16384
                parallel_force_local boolean FALSE
                parallel_instance_group string
                parallel_io_cap_enabled boolean FALSE
                parallel_max_servers integer 985
                parallel_min_percent integer 0
                parallel_min_servers integer 0
                parallel_min_time_threshold string AUTO
                parallel_server boolean TRUE
                parallel_server_instances integer 2
                parallel_servers_target integer 1024
                parallel_threads_per_cpu integer 2
                recovery_parallelism integer 0
                • 6. Re: How Can I make this query run faster
                  sb92075
                  980551 wrote:
                  I bellieve that it is disabled as well:
                  the posted EXPLAIN PLAN indicates otherwise.
                  see the PX slaves?
                  • 7. Re: How Can I make this query run faster
                    983554
                    Can you tell me how can I disable the parallel feature?

                    is it "alter system set parallel_force_local=true scope =spfile;"
                    • 8. Re: How Can I make this query run faster
                      983554
                      are there specific parameters here that I need to change?
                      • 9. Re: How Can I make this query run faster
                        983554
                        will this have any offect on the DB if I turn off parrelism from the table level?

                        Liike so:
                        ALTER TABLE table_name NOPARALLEL;

                        then I would no longer need to mention noparallel in this statement right?

                        SELECT /*+ noparallel */ a.txid, a.methodid, a.serviceid, a.nodename, a.env, a.ts, a.te, a.resultcode, a.resultmessage FROM g2log.txmaster a,
                        g2log.txlookup b WHERE b.key = 'sbcgnfttxuniquedslamportid' AND b.value = 'MTC3LS733001-1-1-2-13' AND a.txid = b.txid ORDER BY ts desc;

                        Edited by: 980551 on Jan 10, 2013 2:11 PM
                        • 10. Re: How Can I make this query run faster
                          JustinCave
                          980551 wrote:
                          will this have any offect on the DB if I turn off parrelism from the table level?
                          Possibly, sure. Presumably, someone enabled parallelism on the table for some reason at some point in the past. Presumably, that solved some problem by, for example, allowing some other query to make use of parallelism.
                          then I would no longer need to mention noparallel in this statement right?
                          Assuming that both tables were set to NOPARALLEL, yes, you could omit the hint.

                          Justin
                          • 11. Re: How Can I make this query run faster
                            rp0428
                            >
                            But they both already have indexes:
                            >
                            But you aren't showing what columns each index is on.

                            The plan shows that txmaster is being used as the driving table so a full table scan (NO partition pruning) is being used. What is the partition key? Is it TXID?
                            >
                            b.key = 'sbcgnfttxuniquedslamportid' AND b.value = 'MTC3LS733001-1-1-2-13' AND a.txid = b.txid
                            >
                            Is there an index on b.key, b.value, b.txid? How many lookup records match that key and value combination? Did you collect stats on the index? A histogram on the combination of those two columns?

                            To use the lookup table as the driver Oracle has to be able to determine that it will provide fewer records than the full scan of the master table.
                            • 12. Re: How Can I make this query run faster
                              983554
                              Yes txid is the partition index. I did not collect stats on the index.

                              Edited by: 980551 on Jan 11, 2013 6:37 AM