Re: Help with sql query speed. Explain plan included

On Mar 19, 9:00 pm, "Charles Hooper" <hooperc2..._at_yahoo.com> wrote:
> 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.- Hide quoted text -
>
> - Show quoted text -

This is good progress: already the cost dropped from 208k to 11k.

The plan shows that when using the 3 columns index (T1_INDEX3), there is no table access at all.
I am not sure what are the columns are indexed by T1_INDEX1 and T1, but try creating a single index with the same columns, in the following order: RESIDENTID, QUESTIONNUMBER, ANSWER - please note that RESIDENTID is first now.
That should eliminate all table accesses from the plan and reduce it to a minimum. If you want, you can compress keys in both indexes (T1_INDEX3 and the one described above) and see if that makes any difference in terms of response time. Received on Tue Mar 20 2007 - 05:33:39 CDT