Query: SELECT --ATRIBUTOS PA.PAIS, DT.MES_NR, RF.TIPO as TIPO_REFERRER, RQ.TIPO as TIPO_REQUEST, TMP.PERIODO, --MEDIDAS COUNT(*) AS TOTAL_SESSÕES, SUM(PEDIDOS) AS PEDIDOS , SUM(M_GETS) AS M_GETS, SUM(M_POST) AS M_POST, SUM(M_HEAD) AS M_HEAD, SUM(M_OUTROS) AS M_OUTROS, SUM(STATUS_INFORMACOES) AS STATUS_INFORMACOES, SUM(STATUS_SUCESSO) AS STATUS_SUCESSO, SUM(STATUS_REDIRECIONADO) AS STATUS_REDIRECIONADO, SUM(STATUS_ERRO_CLIENTE) AS STATUS_ERRO_CLIENTE, SUM(STATUS_ERRO_SERVIDOR) AS STATUS_ERRO_SERVIDOR, SUM(BYTES_ENVIADOS) AS BYTES_ENVIADOS, SUM(BYTES_ENVIADOS)/1024 AS KB_ENVIADOS, SUM(BYTES_ENVIADOS)/1024/1024 AS MB_ENVIADOS, SUM(SESSION_DURATION) AS DURACAO FROM TF_SESSIONS TF, DATA_DIM DT, TEMPO_DIM TMP, REFERRER_DIM RF, AGENTE_DIM AG, COMPUTADORUTILIZADOR_DIM CU, PAIS_SUBDIM PA, TP_SESSION TP, REQUEST_DIM RQ WHERE TF.STARTDATE = DT.DATA_ID AND TF.ENDDATE = DT.DATA_ID AND TF.STARTTIME = TMP.HORA AND TF.ENDTIME = TMP.HORA AND TF.REFERRER = RF.REFERRER_ID AND TF.USERAGENT = AG.AGENTE_ID AND TF.HOST = CU.IPADDRESS AND CU.CODIGOISOPAIS = PA.CODIGOISOPAIS AND TP.SESSIONID = TF.SESSIONID AND TP.REQUEST_ID = RQ.REQUEST_ID AND DT.ANO=2008 AND AG.TIPO IN ('User','Crawler') AND RQ.TIPO_CLASSE = 'G48' GROUP BY PA.PAIS, DT.MES_NR, RF.TIPO, RQ.TIPO, TMP.PERIODO ORDER BY PA.PAIS, DT.MES_NR, RF.TIPO, RQ.TIPO, TMP.PERIODO Plan without indexes -------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 220 | 55748 (2)| 00:11:09 | | 1 | SORT ORDER BY | | 1 | 220 | 55748 (2)| 00:11:09 | | 2 | HASH GROUP BY | | 1 | 220 | 55748 (2)| 00:11:09 | |* 3 | HASH JOIN | | 1 | 220 | 55746 (2)| 00:11:09 | |* 4 | TABLE ACCESS FULL | REQUEST_DIM | 142 | 2698 | 102 (0)| 00:00:02 | |* 5 | HASH JOIN | | 57 | 11457 | 55643 (2)| 00:11:08 | |* 6 | HASH JOIN | | 1 | 190 | 5827 (1)| 00:01:10 | |* 7 | HASH JOIN | | 1 | 157 | 5823 (1)| 00:01:10 | |* 8 | HASH JOIN | | 1 | 140 | 5442 (1)| 00:01:06 | |* 9 | HASH JOIN | | 1 | 128 | 5419 (1)| 00:01:06 | |* 10 | HASH JOIN | | 49 | 5537 | 5248 (1)| 00:01:03 | | 11 | TABLE ACCESS FULL | TEMPO_DIM | 24 | 192 | 3 (0)| 00:00:01 | |* 12 | HASH JOIN | | 1180 | 120K| 5244 (1)| 00:01:03 | |* 13 | TABLE ACCESS FULL| DATA_DIM | 366 | 5490 | 13 (0)| 00:00:01 | | 14 | TABLE ACCESS FULL| TF_SESSIONS | 1207K| 103M| 5225 (1)| 00:01:03 | |* 15 | TABLE ACCESS FULL | AGENTE_DIM | 634 | 9510 | 171 (1)| 00:00:03 | | 16 | TABLE ACCESS FULL | REFERRER_DIM | 3936 | 47232 | 23 (0)| 00:00:01 | | 17 | TABLE ACCESS FULL | COMPUTADORUTILIZADOR_DIM | 398K| 6623K| 379 (2)| 00:00:05 | | 18 | TABLE ACCESS FULL | PAIS_SUBDIM | 100 | 3300 | 3 (0)| 00:00:01 | | 19 | TABLE ACCESS FULL | TP_SESSION | 72M| 759M| 49489 (2)| 00:09:54 | -------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("TP"."REQUEST_ID"="RQ"."REQUEST_ID") 4 - filter("RQ"."TIPO_CLASSE"='G48') 5 - access("TP"."SESSIONID"="TF"."SESSIONID") 6 - access("CU"."CODIGOISOPAIS"="PA"."CODIGOISOPAIS") 7 - access("TF"."HOST"="CU"."IPADDRESS") 8 - access("TF"."REFERRER"="RF"."REFERRER_ID") 9 - access("TF"."USERAGENT"="AG"."AGENTE_ID") 10 - access("TF"."STARTTIME"="TMP"."HORA" AND "TF"."ENDTIME"="TMP"."HORA") 12 - access("TF"."STARTDATE"="DT"."DATA_ID" AND "TF"."ENDDATE"="DT"."DATA_ID") 13 - filter("DT"."ANO"=2008) 15 - filter("AG"."TIPO"='Crawler' OR "AG"."TIPO"='User') Plan without indexes: ------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 198 | 6367 (2)| 00:01:17 | | 1 | TEMP TABLE TRANSFORMATION | | | | | | | 2 | LOAD AS SELECT | SYS_TEMP_0FD9D660A_E1A0D | | | | | | 3 | TABLE ACCESS BY INDEX ROWID | REQUEST_DIM | 142 | 2698 | 86 (0)| 00:00:02 | |* 4 | INDEX RANGE SCAN | TIPO_CLASSE_REQUEST_BTIX | 142 | | 1 (0)| 00:00:01 | | 5 | LOAD AS SELECT | SYS_TEMP_0FD9D660A_E1A0D | | | | | |* 6 | HASH JOIN | | 2 | 210 | 1066 (1)| 00:00:13 | | 7 | TABLE ACCESS BY INDEX ROWID | DATA_DIM | 366 | 5490 | 6 (0)| 00:00:01 | |* 8 | INDEX RANGE SCAN | ANO_BTIX | 366 | | 1 (0)| 00:00:01 | |* 9 | TABLE ACCESS BY INDEX ROWID | TF_SESSIONS | 1956 | 171K| 1060 (1)| 00:00:13 | | 10 | BITMAP CONVERSION TO ROWIDS | | | | | | | 11 | BITMAP AND | | | | | | | 12 | BITMAP MERGE | | | | | | | 13 | BITMAP KEY ITERATION | | | | | | |* 14 | VIEW | index$_join$_041 | 634 | 9510 | 86 (2)| 00:00:02 | |* 15 | HASH JOIN | | | | | | | 16 | INLIST ITERATOR | | | | | | |* 17 | INDEX RANGE SCAN | TIPO_AGENTE_BTIX | 634 | 9510 | 2 (0)| 00:00:01 | | 18 | INDEX FAST FULL SCAN | AGENTE_ID_PK | 634 | 9510 | 85 (0)| 00:00:02 | | 19 | BITMAP CONVERSION FROM ROWIDS | | | | | | |* 20 | INDEX RANGE SCAN | IBT_FK6 | | | 3 (0)| 00:00:01 | | 21 | BITMAP MERGE | | | | | | | 22 | BITMAP KEY ITERATION | | | | | | | 23 | TABLE ACCESS BY INDEX ROWID | DATA_DIM | 366 | 5490 | 6 (0)| 00:00:01 | |* 24 | INDEX RANGE SCAN | ANO_BTIX | 366 | | 1 (0)| 00:00:01 | | 25 | BITMAP CONVERSION FROM ROWIDS | | | | | | |* 26 | INDEX RANGE SCAN | IBT_FK1 | | | 8 (0)| 00:00:01 | | 27 | SORT ORDER BY | | 1 | 198 | 5215 (2)| 00:01:03 | | 28 | HASH GROUP BY | | 1 | 198 | 5215 (2)| 00:01:03 | |* 29 | HASH JOIN | | 1 | 198 | 5213 (2)| 00:01:03 | |* 30 | HASH JOIN | | 1 | 165 | 5209 (2)| 00:01:03 | |* 31 | HASH JOIN | | 1 | 148 | 4828 (2)| 00:00:58 | |* 32 | HASH JOIN | | 1 | 136 | 4805 (2)| 00:00:58 | |* 33 | HASH JOIN | | 1 | 121 | 4798 (2)| 00:00:58 | |* 34 | HASH JOIN | | 1 | 113 | 4795 (2)| 00:00:58 | |* 35 | HASH JOIN | | 1 | 23 | 3734 (2)| 00:00:45 | | 36 | TABLE ACCESS BY INDEX ROWID | TP_SESSION | 1 | 11 | 3732 (2)| 00:00:45 | | 37 | BITMAP CONVERSION TO ROWIDS | | | | | | | 38 | BITMAP AND | | | | | | | 39 | BITMAP MERGE | | | | | | | 40 | BITMAP KEY ITERATION | | | | | | | 41 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660B_E1A0D | 1 | 13 | 2 (0)| 00:00:01 | | 42 | BITMAP CONVERSION FROM ROWIDS| | | | | | |* 43 | INDEX RANGE SCAN | IBT_FK8 | | | 3 (0)| 00:00:01 | | 44 | BITMAP MERGE | | | | | | | 45 | BITMAP KEY ITERATION | | | | | | | 46 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660A_E1A0D | 1 | 13 | 2 (0)| 00:00:01 | | 47 | BITMAP CONVERSION FROM ROWIDS| | | | | | |* 48 | INDEX RANGE SCAN | IBT_FK9 | | | 12 (0)| 00:00:01 | | 49 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660A_E1A0D | 142 | 1704 | 2 (0)| 00:00:01 | | 50 | TABLE ACCESS BY INDEX ROWID | TF_SESSIONS | 1956 | 171K| 1060 (1)| 00:00:13 | | 51 | BITMAP CONVERSION TO ROWIDS | | | | | | | 52 | BITMAP AND | | | | | | | 53 | BITMAP MERGE | | | | | | | 54 | BITMAP KEY ITERATION | | | | | | |* 55 | VIEW | index$_join$_074 | 634 | 9510 | 86 (2)| 00:00:02 | |* 56 | HASH JOIN | | | | | | | 57 | INLIST ITERATOR | | | | | | |* 58 | INDEX RANGE SCAN | TIPO_AGENTE_BTIX | 634 | 9510 | 2 (0)| 00:00:01 | | 59 | INDEX FAST FULL SCAN | AGENTE_ID_PK | 634 | 9510 | 85 (0)| 00:00:02 | | 60 | BITMAP CONVERSION FROM ROWIDS | | | | | | |* 61 | INDEX RANGE SCAN | IBT_FK6 | | | 3 (0)| 00:00:01 | | 62 | BITMAP MERGE | | | | | | | 63 | BITMAP KEY ITERATION | | | | | | | 64 | TABLE ACCESS BY INDEX ROWID | DATA_DIM | 366 | 5490 | 6 (0)| 00:00:01 | |* 65 | INDEX RANGE SCAN | ANO_BTIX | 366 | | 1 (0)| 00:00:01 | | 66 | BITMAP CONVERSION FROM ROWIDS | | | | | | |* 67 | INDEX RANGE SCAN | IBT_FK1 | | | 8 (0)| 00:00:01 | | 68 | TABLE ACCESS FULL | TEMPO_DIM | 24 | 192 | 3 (0)| 00:00:01 | | 69 | TABLE ACCESS BY INDEX ROWID | DATA_DIM | 366 | 5490 | 6 (0)| 00:00:01 | |* 70 | INDEX RANGE SCAN | ANO_BTIX | 366 | | 1 (0)| 00:00:01 | | 71 | TABLE ACCESS FULL | REFERRER_DIM | 3936 | 47232 | 23 (0)| 00:00:01 | | 72 | TABLE ACCESS FULL | COMPUTADORUTILIZADOR_DIM | 398K| 6623K| 379 (2)| 00:00:05 | | 73 | TABLE ACCESS FULL | PAIS_SUBDIM | 100 | 3300 | 3 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("RQ"."TIPO_CLASSE"='G48') 6 - access("TF"."STARTDATE"="DT"."DATA_ID" AND "TF"."ENDDATE"="DT"."DATA_ID") 8 - access("DT"."ANO"=2008) 9 - filter("TF"."ENDTIME"="TF"."STARTTIME" AND "TF"."ENDDATE"="TF"."STARTDATE") 14 - filter("AG"."TIPO"='Crawler' OR "AG"."TIPO"='User') 15 - access(ROWID=ROWID) 17 - access("AG"."TIPO"='Crawler' OR "AG"."TIPO"='User') 20 - access("TF"."USERAGENT"="AG"."AGENTE_ID") 24 - access("DT"."ANO"=2008) 26 - access("TF"."STARTDATE"="DT"."DATA_ID") 29 - access("CU"."CODIGOISOPAIS"="PA"."CODIGOISOPAIS") 30 - access("TF"."HOST"="CU"."IPADDRESS") 31 - access("TF"."REFERRER"="RF"."REFERRER_ID") 32 - access("TF"."STARTDATE"="DT"."DATA_ID" AND "TF"."ENDDATE"="DT"."DATA_ID") 33 - access("TF"."STARTTIME"="TMP"."HORA" AND "TF"."ENDTIME"="TMP"."HORA") 34 - access("TP"."SESSIONID"="TF"."SESSIONID") 35 - access("TP"."REQUEST_ID"="C0") 43 - access("TP"."SESSIONID"="C0") 48 - access("TP"."REQUEST_ID"="C0") 55 - filter("AG"."TIPO"='Crawler' OR "AG"."TIPO"='User') 56 - access(ROWID=ROWID) 58 - access("AG"."TIPO"='Crawler' OR "AG"."TIPO"='User') 61 - access("TF"."USERAGENT"="AG"."AGENTE_ID") 65 - access("DT"."ANO"=2008) 67 - access("TF"."STARTDATE"="DT"."DATA_ID") 70 - access("DT"."ANO"=2008) Note ----- - star transformation used for this statement