This discussion is archived
12 Replies Latest reply: Jan 11, 2013 6:38 AM by 983554 RSS

How Can I make this query run faster

983554 Newbie
Currently Being Moderated
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 Explorer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    consider testing performance when PARALLEL is disabled & not used.
  • 5. Re: How Can I make this query run faster
    983554 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    are there specific parameters here that I need to change?
  • 9. Re: How Can I make this query run faster
    983554 Newbie
    Currently Being Moderated
    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
    Justin Cave Oracle ACE
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    >
    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 Newbie
    Currently Being Moderated
    Yes txid is the partition index. I did not collect stats on the index.

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

Legend

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