On Tue, Jan 22, 2008 at 01:16:26PM +0100, Joop wrote:
> Emmanuel Lacour wrote:
> >SQL> set autotrace on
> >SQL> CREATE INDEX Groups3 ON Groups (LOWER('Type'), LOWER('Domain'),
> >Instance);
> >Index created.
> >SQL> execute dbms_utility.analyze_schema( 'RT', 'estimate');
> >
> Any particular reason to run 'estimate' instead of 'compute' ?
Yes, I follow the rt README.Oracle ;) and I wasn't aware of "compute" ;)
>
> Further your output of Oracle10g looks almost identical/is identical to
> the output of Oracle9. That sounds to me like a copy/paste problem
> because that is not what I expect and contradicts your own statement
> that Oracle10 is faster than Oracle9.
>
No It's just because the explain plan I gave is not about oracle 10g, but about
the request on oracle 9 with the indexes proposed by Ruslan.
Here is the result from oracle 10g, first request took about 25 seconds, if I
run it a second time, less than 1 second:
=====
First
=====
Execution Plan
----------------------------------------------------------
Plan hash value: 1378103328
--------------------------------------------------------------------------------
-------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost
(%CPU)| Time |
--------------------------------------------------------------------------------
-------------------
| 0 | SELECT STATEMENT | | 1699 | 386K| | 718
9 (3)| 00:01:27 |
| 1 | SORT ORDER BY | | 1699 | 386K| 1064K| 718
9 (3)| 00:01:27 |
|* 2 | HASH JOIN | | 1699 | 386K| | 710
0 (3)| 00:01:26 |
| 3 | VIEW | | 1699 | 6796 | | 694
4 (3)| 00:01:24 |
| 4 | HASH UNIQUE | | 1699 | 127K| 65M| 694
4 (3)| 00:01:24 |
|* 5 | HASH JOIN | | 767K| 56M| | 199
2 (4)| 00:00:24 |
| 6 | INLIST ITERATOR | | | | |
| |
|* 7 | INDEX RANGE SCAN | ACL1 | 21 | 609 | |
3 (0)| 00:00:01 |
|* 8 | HASH JOIN | | 185K| 8715K| 4944K| 198
0 (4)| 00:00:24 |
|* 9 | TABLE ACCESS FULL | GROUPS | 136K| 3340K| | 45
5 (3)| 00:00:06 |
|* 10 | HASH JOIN | | 185K| 4176K| | 96
9 (5)| 00:00:12 |
|* 11 | HASH JOIN | | 16950 | 248K| | 27
9 (5)| 00:00:04 |
|* 12 | INDEX FAST FULL SCAN| USERS_KEY | 16950 | 67800 | | 1
1 (0)| 00:00:01 |
|* 13 | TABLE ACCESS FULL | PRINCIPALS | 72207 | 775K| | 26
6 (5)| 00:00:04 |
|* 14 | INDEX FAST FULL SCAN | GROUMEM | 789K| 6164K| | 67
9 (3)| 00:00:09 |
| 15 | TABLE ACCESS FULL | USERS | 16951 | 3790K| | 15
5 (2)| 00:00:02 |
--------------------------------------------------------------------------------
-------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("MAIN"."ID"="DISTINCTQUERY"."ID")
5 - access("ACL_4"."PRINCIPALTYPE"="GROUPS_3"."TYPE")
7 - access("ACL_4"."RIGHTNAME"='OwnTicket' AND ("ACL_4"."OBJECTTYPE"='RT::Que
ue' OR
"ACL_4"."OBJECTTYPE"='RT::System'))
8 - access("GROUPS_3"."ID"="CACHEDGROUPMEMBERS_2"."GROUPID")
9 - filter("GROUPS_3"."DOMAIN"='RT::Queue-Role' OR
"GROUPS_3"."DOMAIN"='RT::System-Role')
10 - access("CACHEDGROUPMEMBERS_2"."MEMBERID"="PRINCIPALS_1"."ID")
11 - access("PRINCIPALS_1"."ID"="MAIN"."ID")
12 - filter("MAIN"."ID"<>1)
13 - filter("PRINCIPALS_1"."DISABLED"=0 AND "PRINCIPALS_1"."PRINCIPALTYPE"='Us
er' AND
"PRINCIPALS_1"."ID"<>1)
14 - filter("CACHEDGROUPMEMBERS_2"."MEMBERID"<>1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5001 consistent gets
2550 physical reads
0 redo size
2379 bytes sent via SQL*Net to client
384 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
0 rows processed
======
Second
======
Execution Plan
----------------------------------------------------------
Plan hash value: 1378103328
--------------------------------------------------------------------------------
-------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost
(%CPU)| Time |
--------------------------------------------------------------------------------
-------------------
| 0 | SELECT STATEMENT | | 1699 | 386K| | 718
9 (3)| 00:01:27 |
| 1 | SORT ORDER BY | | 1699 | 386K| 1064K| 718
9 (3)| 00:01:27 |
|* 2 | HASH JOIN | | 1699 | 386K| | 710
0 (3)| 00:01:26 |
| 3 | VIEW | | 1699 | 6796 | | 694
4 (3)| 00:01:24 |
| 4 | HASH UNIQUE | | 1699 | 127K| 65M| 694
4 (3)| 00:01:24 |
|* 5 | HASH JOIN | | 767K| 56M| | 199
2 (4)| 00:00:24 |
| 6 | INLIST ITERATOR | | | | |
| |
|* 7 | INDEX RANGE SCAN | ACL1 | 21 | 609 | |
3 (0)| 00:00:01 |
|* 8 | HASH JOIN | | 185K| 8715K| 4944K| 198
0 (4)| 00:00:24 |
|* 9 | TABLE ACCESS FULL | GROUPS | 136K| 3340K| | 45
5 (3)| 00:00:06 |
|* 10 | HASH JOIN | | 185K| 4176K| | 96
9 (5)| 00:00:12 |
|* 11 | HASH JOIN | | 16950 | 248K| | 27
9 (5)| 00:00:04 |
|* 12 | INDEX FAST FULL SCAN| USERS_KEY | 16950 | 67800 | | 1
1 (0)| 00:00:01 |
|* 13 | TABLE ACCESS FULL | PRINCIPALS | 72207 | 775K| | 26
6 (5)| 00:00:04 |
|* 14 | INDEX FAST FULL SCAN | GROUMEM | 789K| 6164K| | 67
9 (3)| 00:00:09 |
| 15 | TABLE ACCESS FULL | USERS | 16951 | 3790K| | 15
5 (2)| 00:00:02 |
--------------------------------------------------------------------------------
-------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("MAIN"."ID"="DISTINCTQUERY"."ID")
5 - access("ACL_4"."PRINCIPALTYPE"="GROUPS_3"."TYPE")
7 - access("ACL_4"."RIGHTNAME"='OwnTicket' AND ("ACL_4"."OBJECTTYPE"='RT::Que
ue' OR
"ACL_4"."OBJECTTYPE"='RT::System'))
8 - access("GROUPS_3"."ID"="CACHEDGROUPMEMBERS_2"."GROUPID")
9 - filter("GROUPS_3"."DOMAIN"='RT::Queue-Role' OR
"GROUPS_3"."DOMAIN"='RT::System-Role')
10 - access("CACHEDGROUPMEMBERS_2"."MEMBERID"="PRINCIPALS_1"."ID")
11 - access("PRINCIPALS_1"."ID"="MAIN"."ID")
12 - filter("MAIN"."ID"<>1)
13 - filter("PRINCIPALS_1"."DISABLED"=0 AND "PRINCIPALS_1"."PRINCIPALTYPE"='Us
er' AND
"PRINCIPALS_1"."ID"<>1)
14 - filter("CACHEDGROUPMEMBERS_2"."MEMBERID"<>1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5001 consistent gets
0 physical reads
0 redo size
2379 bytes sent via SQL*Net to client
384 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
0 rows processed
_______________________________________________
http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
Community help: http://wiki.bestpractical.com
Commercial support: [EMAIL PROTECTED]
Discover RT's hidden secrets with RT Essentials from O'Reilly Media.
Buy a copy at http://rtbook.bestpractical.com
