Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Help with sql query speed. Explain plan included

Re: Help with sql query speed. Explain plan included

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 19 Mar 2007 18:00:32 -0700
Message-ID: <1174352432.634490.3810@y66g2000hsf.googlegroups.com>


On Mar 19, 4:58 pm, "Valentin Minzatu" <valentinminz..._at_yahoo.com> wrote:
> On Mar 19, 4:50 pm, "Charles Hooper" <hooperc2..._at_yahoo.com> wrote:
> > It takes a couple hours to add an index to a table with ~ 226,407,657
> > rows, and then to analyze that index. I will take a look at that
> > possibility in a couple hours.
>
> > SELECT
> > BLOCKS
> > FROM
> > DBA_TABLES
> > WHERE
> > TABLE_NAME='T1';
>
> > 551,931
>
> > SELECT
> > INDEX_NAME,
> > LEAF_BLOCKS
> > FROM
> > DBA_INDEXES
> > WHERE
> > INDEX_NAME LIKE 'T1';
>
> > T1_INDEX1 516,467 (RESIDENTID only)
> > T2_INDEX2 347,797 (QUESTIONNUMBER,ANSWER composite, now using key
> > compression)
>
> > Charles Hooper
> > PC Support Specialist
> > K&M Machine-Fabricating, Inc.- Hide quoted text -
>
> > - Show quoted text -
>
> It would be great if you could do it (no matter when) - I do not have
> the physical resources for that kind of testing right now,
> unfortunately, but I am pretty sure it will work.
>

Something that I noticed in the 10053 traces:   Outline Data:
  /*+
    BEGIN_OUTLINE_DATA

      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "T0"@"SEL$1" ("T1"."RESIDENTID"))
      INDEX(@"SEL$1" "TX"@"SEL$1" ("T1"."QUESTIONNUMBER" "T1"."ANSWER"
"T1"."RESIDENTID"))
      LEADING(@"SEL$1" "T0"@"SEL$1" "TX"@"SEL$1")
      USE_NL(@"SEL$1" "TX"@"SEL$1")

    END_OUTLINE_DATA
  */

I wonder if this provides a clue why the optimizer was ignoring hints?

Test results follow.

CREATE INDEX T1_INDEX3 ON T1(QUESTIONNUMBER,ANSWER,RESIDENTID) NOLOGGING; EXEC
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'TABLE_OWNER',TABNAME=>'T1',CASCADE=>TRUE); SELECT
  INDEX_NAME,
  LEAF_BLOCKS
FROM
  DBA_INDEXES
WHERE
  INDEX_NAME LIKE 'T1%';

INDEX_NAME                     LEAF_BLOCKS
------------------------------ -----------
T1_INDEX1                          517,844
T1_INDEX2                          347,977
T1_INDEX3                          713,742

ALTER SESSION SET TRACEFILE_IDENTIFIER = 'TEST201'; select tx.residentid
 from t1 t0,t1 tx
  where t0.residentid!=tx.residentid
   and t0.questionnumber=tx.questionnumber    and t0.answer=tx.answer
   and t0.residentid=1486674
    group by tx.residentid
     having count(*)>10;

Plan Table



+-----------------------------------+
| Id  | Operation                       | Name     | Rows  | Bytes |
Cost  | Time      |
---------------------------------------------------
+-----------------------------------+
| 0   | SELECT STATEMENT                |          |       |       |
15K |           |
| 1   |  FILTER                         |          |       |
|       |           |
| 2   |   SORT GROUP BY                 |          |  102K | 2244K |
15K |  00:04:48 |
| 3   |    NESTED LOOPS                 |          | 2040K |   44M |
6879 |  00:02:46 |
| 4   |     TABLE ACCESS BY INDEX ROWID | T1       |    75 |   825
|    80 |  00:00:02 |
| 5   |      INDEX RANGE SCAN           | T1_INDEX1|    75 |
|     4 |  00:00:01 |
| 6   |     INDEX RANGE SCAN            | T1_INDEX3|   27K |  297K
| 91 | 00:00:02 |
+-----------------------------------+

Predicate Information:
1 - filter(COUNT(*)>10)
5 - access("T0"."RESIDENTID"=1486674)
6 - access("T0"."QUESTIONNUMBER"="TX"."QUESTIONNUMBER" AND
"T0"."ANSWER"="TX"."ANSWER")
6 - filter(("TX"."RESIDENTID"<>1486674 AND "T0"."RESIDENTID"<>"TX"."RESIDENTID"))

ROUP BY cardinality: 2089330.00, TABLE cardinality: 2089330.00 HAVING selectivity: 0.05 -> GROUPS: 104467

    SORT resource      Sort statistics
      Sort width:          88 Area size:      314368 Max Area size:
15728640
      Degree:               1
      Blocks to Sort:    8953 Row size:           35 Total
Rows:        2089330
      Initial runs:         5 Merge passes:        1 IO Cost /
pass:       7382
      Total IO sort cost: 7830      Total CPU sort cost: 2103620313
      Total Temp space used: 70493000

ALTER SESSION SET TRACEFILE_IDENTIFIER = 'TEST202'; SELECT residentId, count(1)
  FROM t1
 WHERE ( questionNumber, answer ) IN ( SELECT questionNumber, answer

                                         FROM t1
                                        WHERE residentId = 1486674 )
  AND RESIDENTID<>1486674
 GROUP BY residentId
 HAVING COUNT(1) > 10; Plan Table

+-----------------------------------+
| Id  | Operation                        | Name     | Rows  | Bytes |
Cost  | Time      |
----------------------------------------------------
+-----------------------------------+
| 0   | SELECT STATEMENT                 |          |       |
|   11K |           |
| 1   |  FILTER                          |          |       |
|       |           |
| 2   |   SORT GROUP BY                  |          |  102K | 2244K
|   11K |  00:03:57 |
| 3   |    NESTED LOOPS                  |          | 2040K |   44M |
3526 |  00:00:54 |
| 4   |     SORT UNIQUE                  |          |    75 |   825
|    80 |  00:00:02 |
| 5   |      TABLE ACCESS BY INDEX ROWID | T1       |    75 |   825
|    80 |  00:00:02 |
| 6   |       INDEX RANGE SCAN           | T1_INDEX1|    75 |
|     4 |  00:00:01 |
| 7   |     INDEX RANGE SCAN             | T1_INDEX3|   27K |  297K
| 91 | 00:00:02 |
+-----------------------------------+

Predicate Information:
1 - filter(COUNT(*)>10)
6 - access("RESIDENTID"=1486674)
7 - access("QUESTIONNUMBER"="QUESTIONNUMBER" AND "ANSWER"="ANSWER")
7 - filter("RESIDENTID"<>1486674)

GROUP BY cardinality: 2089330.00, TABLE cardinality: 2089330.00 HAVING selectivity: 0.05 -> GROUPS: 104467

    SORT resource      Sort statistics
      Sort width:          88 Area size:      314368 Max Area size:
15728640
      Degree:               1
      Blocks to Sort:    8953 Row size:           35 Total
Rows:        2089330
      Initial runs:         5 Merge passes:        1 IO Cost /
pass:       7382
      Total IO sort cost: 7830      Total CPU sort cost: 2103620313
      Total Temp space used: 70493000

Another attempt:
SELECT /*+ ORDERED */
  T1.RESIDENTID
FROM
  (SELECT
    RESIDENTID,
    QUESTIONNUMBER,
    ANSWER
  FROM
    T1
  WHERE
    RESIDENTID=1486674) MT1,
  T1
WHERE
  MT1.QUESTIONNUMBER=T1.QUESTIONNUMBER
  AND MT1.ANSWER=T1.ANSWER
  AND MT1.RESIDENTID<>T1.RESIDENTID
GROUP BY
  T1.RESIDENTID
HAVING
  COUNT(*)>10;


+-----------------------------------+
| Id  | Operation                       | Name     | Rows  | Bytes |
Cost  | Time      |
---------------------------------------------------
+-----------------------------------+
| 0   | SELECT STATEMENT                |          |       |       |
15K |           |
| 1   |  FILTER                         |          |       |
|       |           |
| 2   |   HASH GROUP BY                 |          |  102K | 2244K |
15K |  00:04:48 |
| 3   |    NESTED LOOPS                 |          | 2040K |   44M |
6870 |  00:02:46 |
| 4   |     TABLE ACCESS BY INDEX ROWID | T1       |    75 |   825
|    80 |  00:00:02 |
| 5   |      INDEX RANGE SCAN           | T1_INDEX1|    75 |
|     4 |  00:00:01 |
| 6   |     INDEX RANGE SCAN            | T1_INDEX3|   27K |  297K
| 91 | 00:00:02 |
+-----------------------------------+

Predicate Information:
1 - filter(COUNT(*)>10)
5 - access("RESIDENTID"=1486674)
6 - access("QUESTIONNUMBER"="T1"."QUESTIONNUMBER" AND
"ANSWER"="T1"."ANSWER")
6 - filter(("RESIDENTID"<>"T1"."RESIDENTID" AND "T1"."RESIDENTID"<>1486674))

GROUP BY cardinality: 2089330.00, TABLE cardinality: 2089330.00 HAVING selectivity: 0.05 -> GROUPS: 104467

    SORT resource      Sort statistics
      Sort width:         358 Area size:      314368 Max Area size:
62914560
      Degree:               1
      Blocks to Sort:    8953 Row size:           35 Total
Rows:        2089330
      Initial runs:         2 Merge passes:        1 IO Cost /
pass:       7382
      Total IO sort cost: 7830      Total CPU sort cost: 2103620313
      Total Temp space used: 70493000

ALTER SESSION SET SORT_AREA_SIZE=209715200; ALTER SESSION SET HASH_AREA_SIZE=209715200; Same results as the above.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Mon Mar 19 2007 - 20:00:32 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US