[rt-users] Oracle 9 and Build.html performance

Emmanuel Lacour elacour at easter-eggs.com
Tue Jan 22 08:49:24 EST 2008


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





More information about the rt-users mailing list