explainPlanCA.txt 05-28-2001 Bikle ------------------- This is a screen dump which attempts to show how the addition of a few indexes enhances the performance of a specific query. My intention is to add the indexes to a test system in CA, see if they do in fact improve performance, and then ask permission to add the indexes to the Production system in Japan (if the performance is improved on the test system in CA). am-bikle2.americas.bikle.com 20 % cat /opt/bikle/oracle/tmp/implmnt2.out SQL*Plus: Release 8.0.5.0.0 - Production on Tue May 29 1:24:21 2001 (c) Copyright 1998 Oracle Corporation. All rights reserved. Connected to: Oracle8 Enterprise Edition Release 8.0.5.2.0 - Production With the Partitioning and Objects options PL/SQL Release 8.0.5.2.0 - Production GLOBAL_NAME ---------------------------------------- JP.bikle.COM 01:24:21 SQL@JP.bikle.COM> set echo on time on 01:24:21 SQL@JP.bikle.COM> 01:24:21 SQL@JP.bikle.COM> spool implmnt.lst 01:24:21 SQL@JP.bikle.COM> 01:24:21 SQL@JP.bikle.COM> --create the indexes 01:24:21 SQL@JP.bikle.COM> 01:24:21 SQL@JP.bikle.COM> CREATE INDEX CREATIVE_N1 ON CREATIVE(ADFMTID); Index created. 01:24:22 SQL@JP.bikle.COM> CREATE INDEX CREATIVE_N2 ON CREATIVE(SZID); Index created. 01:24:22 SQL@JP.bikle.COM> CREATE INDEX CREATIVE_N3 ON CREATIVE(ADVID); Index created. 01:24:22 SQL@JP.bikle.COM> 01:24:22 SQL@JP.bikle.COM> CREATE INDEX CAMPAIGN_N1 ON CAMPAIGN(ADVID); Index created. 01:24:22 SQL@JP.bikle.COM> CREATE INDEX CAMPAIGN_N2 ON CAMPAIGN(AGCYID); Index created. 01:24:22 SQL@JP.bikle.COM> 01:24:22 SQL@JP.bikle.COM> CREATE INDEX CPNCRT_N1 ON CPNCRT(CRTID); Index created. 01:24:23 SQL@JP.bikle.COM> 01:24:23 SQL@JP.bikle.COM> CREATE INDEX USERS_N1 on USERS(SECUREID); Index created. 01:24:23 SQL@JP.bikle.COM> 01:24:23 SQL@JP.bikle.COM> -- tell the kernel about the new indexes 01:24:23 SQL@JP.bikle.COM> analyze table SITE compute statistics; Table analyzed. 01:24:23 SQL@JP.bikle.COM> analyze table CAMPAIGN compute statistics; Table analyzed. 01:24:24 SQL@JP.bikle.COM> analyze table CPNCRT compute statistics; Table analyzed. 01:24:25 SQL@JP.bikle.COM> analyze table CREATIVE compute statistics; Table analyzed. 01:24:25 SQL@JP.bikle.COM> analyze table ADVERTSR compute statistics; Table analyzed. 01:24:25 SQL@JP.bikle.COM> analyze table AGENCY compute statistics; Table analyzed. 01:24:25 SQL@JP.bikle.COM> analyze table AREAS compute statistics; Table analyzed. 01:25:03 SQL@JP.bikle.COM> analyze table FMTTABLE compute statistics; Table analyzed. 01:25:03 SQL@JP.bikle.COM> analyze table SZTABLE compute statistics; Table analyzed. 01:25:03 SQL@JP.bikle.COM> analyze table USERS compute statistics; Table analyzed. 01:25:03 SQL@JP.bikle.COM> analyze table AREASUM estimate statistics sample 20 percent; Table analyzed. 01:42:34 SQL@JP.bikle.COM> 01:42:34 SQL@JP.bikle.COM> truncate table plan_table; Table truncated. 01:42:34 SQL@JP.bikle.COM> 01:42:34 SQL@JP.bikle.COM> explain plan for 01:42:34 2 SELECT 01:42:34 3 USERA.COMPANY 01:42:34 4 ,CREATIVE.CRTID 01:42:34 5 ,ADURL 01:42:34 6 ,ADMAP 01:42:34 7 ,ADDESC 01:42:34 8 ,LENGTH( RTRIM(ADHTML)) 01:42:34 9 ,AREAS.ARID 01:42:34 10 ,AREA 01:42:34 11 ,SITE.SITENAME 01:42:34 12 ,SUM(SERVED) 01:42:34 13 ,SUM(CLICKED) 01:42:34 14 FROM 01:42:34 15 CAMPAIGN 01:42:34 16 ,CPNCRT 01:42:34 17 ,CREATIVE 01:42:34 18 ,FMTTABLE 01:42:34 19 ,SZTABLE 01:42:34 20 ,ADVERTSR 01:42:34 21 ,AGENCY 01:42:34 22 ,USERS USERA 01:42:34 23 ,USERS USERB 01:42:34 24 ,AREAS 01:42:34 25 ,SITE 01:42:34 26 ,AREASUM 01:42:34 27 WHERE CAMPAIGN.CAMPID = CPNCRT.CAMPID 01:42:34 28 AND CAMPAIGN.ADVID = ADVERTSR.ADVID 01:42:34 29 AND CPNCRT.CRTID = CREATIVE.CRTID 01:42:34 30 AND CPNCRT.CCID = AREASUM.CCID 01:42:34 31 AND CREATIVE.ADFMTID = FMTTABLE.ADFMTID 01:42:34 32 AND CREATIVE.SZID = SZTABLE.SZID 01:42:34 33 AND CREATIVE.ADVID = ADVERTSR.ADVID 01:42:34 34 AND ADVERTSR.USERID = USERA.USERID 01:42:34 35 AND CAMPAIGN.AGCYID = AGENCY.AGCYID 01:42:34 36 AND AGENCY.USERID = USERB.USERID 01:42:34 37 AND AREASUM.VALUEID = AREAS.ARID 01:42:34 38 AND AREASUM.SITEID = SITE.SITEID 01:42:34 39 AND CDATE >= '2001-02-20' 01:42:34 40 AND CDATE <= '2001-02-28 00:00:00' 01:42:34 41 GROUP BY 01:42:34 42 USERA.COMPANY 01:42:34 43 ,CREATIVE.CRTID 01:42:34 44 ,ADURL 01:42:34 45 ,ADMAP 01:42:34 46 ,ADDESC 01:42:34 47 ,LENGTH( RTRIM(ADHTML)) 01:42:34 48 ,AREAS.ARID 01:42:34 49 ,AREA 01:42:34 50 ,SITE.SITENAME 01:42:34 51 ORDER BY 1,2,8; Explained. 01:42:45 SQL@JP.bikle.COM> 01:42:45 SQL@JP.bikle.COM> -- look at the explain plan 01:42:45 SQL@JP.bikle.COM> 01:42:45 SQL@JP.bikle.COM> SELECT LPAD(' ', 2*LEVEL)||OPERATION||' '|| OPTIONS||' '||OBJECT_NAME "Execution Plan" 01:42:45 2 FROM PLAN_TABLE 01:42:45 3 CONNECT BY PRIOR ID = PARENT_ID 01:42:45 4 START WITH ID=1; Execution Plan ------------------------------------------------------------------------------------------------------------------------ SORT GROUP BY NESTED LOOPS HASH JOIN TABLE ACCESS FULL SITE NESTED LOOPS HASH JOIN TABLE ACCESS FULL CPNCRT NESTED LOOPS HASH JOIN TABLE ACCESS FULL AGENCY HASH JOIN TABLE ACCESS FULL CAMPAIGN HASH JOIN TABLE ACCESS FULL USERS HASH JOIN TABLE ACCESS FULL ADVERTSR NESTED LOOPS HASH JOIN INDEX FULL SCAN PK_FMTTABLE_ADFMTID TABLE ACCESS FULL CREATIVE INDEX UNIQUE SCAN PK_SZTABLE_SZID INDEX UNIQUE SCAN PK_USERS_USERID TABLE ACCESS BY INDEX ROWID AREASUM INDEX RANGE SCAN ARSUMIX TABLE ACCESS BY INDEX ROWID AREAS INDEX UNIQUE SCAN PK_AREAS_ARID 26 rows selected. 01:42:45 SQL@JP.bikle.COM> 01:42:45 SQL@JP.bikle.COM> exit Disconnected from Oracle8 Enterprise Edition Release 8.0.5.2.0 - Production With the Partitioning and Objects options PL/SQL Release 8.0.5.2.0 - Production am-bikle2.americas.bikle.com 21 %