| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Help with sql query speed. Explain plan included
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")
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 |
+-----------------------------------+
1 - filter(COUNT(*)>10)
5 - access("T0"."RESIDENTID"=1486674)
6 - access("T0"."QUESTIONNUMBER"="TX"."QUESTIONNUMBER" AND
"T0"."ANSWER"="TX"."ANSWER")
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
+-----------------------------------+ | 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 |
+-----------------------------------+
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 |
+-----------------------------------+
1 - filter(COUNT(*)>10)
5 - access("RESIDENTID"=1486674)
6 - access("QUESTIONNUMBER"="T1"."QUESTIONNUMBER" AND
"ANSWER"="T1"."ANSWER")
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
 |
 |