Home » RDBMS Server » Performance Tuning » Query run faster without indexes. Why? (Oracle 11g)
| Query run faster without indexes. Why? [message #421029] |
Thu, 03 September 2009 13:32  |
JorgeAbreu
Messages: 10
Registered: September 2009
Location: Braga, Portugal
|
Junior Member |
|
|
Hi,
I'm doing a study of the impact of indexes in databases. However, I am getting strange results, since queries run slower with indexes than without them. Indexes were created according to the Oracle documentation: PK, FK, and attributes used in filtering, Group By and Order By operations.
Can anyone give me any reasonable explanation for this to happen?
Does ORACLE have some mechanism to disable caches and buffers?
I'm using Oracle 11g.
Thanks for the help.
I apologize for my English, it is not very good.
Jorge Abreu
|
|
|
|
|
|
|
|
|
|
| Re: Query run faster without indexes. Why? [message #421045 is a reply to message #421029] |
Thu, 03 September 2009 19:26  |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
They are not that large.
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.
Posting Guideline tell how to format code; like below.
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
|
|
|
|
|
|
|
|
| Re: Query run faster without indexes. Why? [message #421050 is a reply to message #421029] |
Thu, 03 September 2009 22:31 |
JorgeAbreu
Messages: 10
Registered: September 2009
Location: Braga, Portugal
|
Junior Member |
|
|
thanks for the patience 
Yes, all the statistics are current for all tables and indexes.
here is all that i did from the beginning
All this is running in Oracle 11g on WinXP SP3
Cardinality of the attributes of every tables:
TABLE_NAME COLUMN_NAME DATA_TYPE NUM_DISTINCT
------------------------------ ------------------------------ -------------------- -----------------
AGENTE_DIM AGENTE_ID NUMBER 32679
AGENTE_DIM TIPO VARCHAR2 100
AGENTE_DIM TEXTO_LOG LONG
AGENTE_DIM AGENTE_NOME VARCHAR2 32679
COMPUTADORUTILIZADOR_DIM IPADDRESS VARCHAR2 398988
COMPUTADORUTILIZADOR_DIM CODIGOISOPAIS VARCHAR2 100
DATA_DIM DATA_ID DATE 4383
DATA_DIM DIA_SEMANA VARCHAR2 7
DATA_DIM DIA_SEMANA_NR NUMBER 7
DATA_DIM DIA_MES_NR NUMBER 31
DATA_DIM DIA_ANO_NR NUMBER 366
DATA_DIM TIPO_DIA VARCHAR2 2
DATA_DIM SEMANA_NR NUMBER 53
DATA_DIM MES VARCHAR2 12
DATA_DIM MES_NR NUMBER 12
DATA_DIM QUARTER NUMBER 4
DATA_DIM SEMESTRE NUMBER 2
DATA_DIM ANO NUMBER 12
PAIS_SUBDIM CODIGOISOPAIS VARCHAR2 100
PAIS_SUBDIM PAIS VARCHAR2 100
REFERRER_DIM REFERRER_ID NUMBER 3936
REFERRER_DIM TIPO VARCHAR2 2
REFERRER_DIM URI VARCHAR2 3936
REFERRER_DIM MOTORPESQUISA CHAR 2
REQUEST_DIM REQUEST_ID NUMBER 16287
REQUEST_DIM REQUEST VARCHAR2 14415
REQUEST_DIM TIPO VARCHAR2 100
REQUEST_DIM TIPO_CLASSE VARCHAR2 50
TEMPO_DIM HORA NUMBER 24
TEMPO_DIM PERIODO VARCHAR2 3
TF_SESSIONS SESSIONID NUMBER 1207600
TF_SESSIONS START1 TIMESTAMP(6) 1194004
TF_SESSIONS END1 TIMESTAMP(6) 1193967
TF_SESSIONS STARTDATE DATE 612
TF_SESSIONS ENDDATE DATE 612
TF_SESSIONS STARTTIME NUMBER 24
TF_SESSIONS ENDTIME NUMBER 24
TF_SESSIONS REFERRER NUMBER 3936
TF_SESSIONS USERAGENT NUMBER 32679
TF_SESSIONS HOST VARCHAR2 379605
TF_SESSIONS PEDIDOS NUMBER 61
TF_SESSIONS M_GETS NUMBER 100
TF_SESSIONS M_POST NUMBER 100
TF_SESSIONS M_HEAD NUMBER 100
TF_SESSIONS M_OUTROS NUMBER 100
TF_SESSIONS STATUS_INFORMACOES NUMBER 100
TF_SESSIONS STATUS_SUCESSO NUMBER 100
TF_SESSIONS STATUS_REDIRECIONADO NUMBER 100
TF_SESSIONS STATUS_ERRO_CLIENTE NUMBER 100
TF_SESSIONS STATUS_ERRO_SERVIDOR NUMBER 100
TF_SESSIONS BYTES_ENVIADOS NUMBER 10000
TF_SESSIONS SESSION_DURATION NUMBER 1800
TP_SESSION SESSIONID NUMBER 1207600
TP_SESSION REQUEST_ID NUMBER 16287
TP_SESSION SEQUENTIAL_NUMBER NUMBER 90
Data Base Script:
-- CREATE TABLES
CREATE TABLE DATA_DIM
(
DATA_ID DATE,
DIA_SEMANA VARCHAR (25),
DIA_SEMANA_NR INTEGER,
DIA_MES_NR INTEGER,
DIA_ANO_NR INTEGER,
TIPO_DIA VARCHAR (15),
SEMANA_NR INTEGER,
MES VARCHAR (15),
MES_NR INTEGER,
QUARTER INTEGER,
SEMESTRE INTEGER,
ANO INTEGER
);
CREATE TABLE TEMPO_DIM (HORA INTEGER, PERIODO VARCHAR (20));
CREATE TABLE AGENTE_DIM
(
AGENTE_ID INTEGER,
TIPO VARCHAR (15),
TEXTO_LOG LONG,
AGENTE_NOME VARCHAR (30)
);
CREATE TABLE REQUEST_DIM
(
REQUEST_ID INTEGER,
REQUEST VARCHAR (200),
TIPO VARCHAR (15),
TIPO_CLASSE VARCHAR (15)
);
CREATE TABLE REFERRER_DIM
(
REFERRER_ID INTEGER,
TIPO VARCHAR (10),
URI VARCHAR (200),
MOTORPESQUISA CHAR (3)
);
CREATE TABLE COMPUTADORUTILIZADOR_DIM
(
IPADDRESS VARCHAR (15),
CODIGOISOPAIS VARCHAR (3)
);
CREATE TABLE PAIS_SUBDIM
(
CODIGOISOPAIS VARCHAR (3),
PAIS VARCHAR (50)
);
CREATE TABLE TF_SESSIONS
(
SESSIONID INT,
START1 TIMESTAMP (6),
END1 TIMESTAMP (6),
STARTDATE DATE,
ENDDATE DATE,
STARTTIME INT,
ENDTIME INT,
REFERRER INT,
USERAGENT INT,
HOST VARCHAR (16),
PEDIDOS INT,
M_GETS INT,
M_POST INT,
M_HEAD INT,
M_OUTROS INT,
STATUS_INFORMACOES INT,
STATUS_SUCESSO INT,
STATUS_REDIRECIONADO INT,
STATUS_ERRO_CLIENTE INT,
STATUS_ERRO_SERVIDOR INT,
BYTES_ENVIADOS INT,
SESSION_DURATION INT
);
CREATE TABLE TP_SESSION
(
SESSIONID INT,
REQUEST_ID INT,
SEQUENTIAL_NUMBER INT
);
after the creation of the DB and populating with data, i have gathered the statistics of the tables with:
BEGIN
DBMS_STATS.GATHER_TABLE_STATS ('JORGE','AGENTE_DIM',ESTIMATE_PERCENT => 100);
END;
BEGIN
DBMS_STATS.GATHER_TABLE_STATS ('JORGE','COMPUTADORUTILIZADOR_DIM',ESTIMATE_PERCENT => 100);
END;
BEGIN
DBMS_STATS.GATHER_TABLE_STATS ('JORGE','DATA_DIM',ESTIMATE_PERCENT => 100);
END;
BEGIN
DBMS_STATS.GATHER_TABLE_STATS ('JORGE','PAIS_SUBDIM',ESTIMATE_PERCENT => 100);
END;
BEGIN
DBMS_STATS.GATHER_TABLE_STATS ('JORGE','REFERRER_DIM',ESTIMATE_PERCENT => 100);
END;
BEGIN
DBMS_STATS.GATHER_TABLE_STATS ('JORGE','REQUEST_DIM',ESTIMATE_PERCENT => 100);
END;
BEGIN
DBMS_STATS.GATHER_TABLE_STATS ('JORGE','TEMPO_DIM',ESTIMATE_PERCENT => 100);
END;
BEGIN
DBMS_STATS.GATHER_TABLE_STATS ('JORGE','TF_SESSIONS',ESTIMATE_PERCENT => 100);
END;
BEGIN
DBMS_STATS.GATHER_TABLE_STATS ('JORGE','TP_SESSION',ESTIMATE_PERCENT => 100);
END;
After all this, i run the previous query.
Explain 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 | HASH JOIN | | 1 | 208 | 55723 (2)| 00:11:09 |
|* 5 | HASH JOIN | | 1 | 175 | 55719 (2)| 00:11:09 |
|* 6 | HASH JOIN | | 1 | 158 | 55339 (2)| 00:11:05 |
|* 7 | HASH JOIN | | 61 | 8723 | 55167 (2)| 00:11:03 |
|* 8 | TABLE ACCESS FULL | REQUEST_DIM | 338 | 6422 | 102 (0)| 00:00:02 |
|* 9 | HASH JOIN | | 2950 | 357K| 55065 (2)| 00:11:01 |
|* 10 | HASH JOIN | | 49 | 5537 | 5248 (1)| 00:01:03 |
|* 11 | TABLE ACCESS FULL | DATA_DIM | 366 | 5490 | 13 (0)| 00:00:01 |
|* 12 | HASH JOIN | | 50317 | 4815K| 5234 (1)| 00:01:03 |
| 13 | TABLE ACCESS FULL| TEMPO_DIM | 24 | 192 | 3 (0)| 00:00:01 |
| 14 | TABLE ACCESS FULL| TF_SESSIONS | 1207K| 103M| 5225 (1)| 00:01:03 |
| 15 | TABLE ACCESS FULL | TP_SESSION | 72M| 759M| 49489 (2)| 00:09:54 |
|* 16 | TABLE ACCESS FULL | AGENTE_DIM | 634 | 9510 | 171 (1)| 00:00:03 |
| 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 | REFERRER_DIM | 3936 | 47232 | 23 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("TF"."REFERRER"="RF"."REFERRER_ID")
4 - access("CU"."CODIGOISOPAIS"="PA"."CODIGOISOPAIS")
5 - access("TF"."HOST"="CU"."IPADDRESS")
6 - access("TF"."USERAGENT"="AG"."AGENTE_ID")
7 - access("TP"."REQUEST_ID"="RQ"."REQUEST_ID")
8 - filter("RQ"."TIPO_CLASSE"='C39')
9 - access("TP"."SESSIONID"="TF"."SESSIONID")
10 - access("TF"."STARTDATE"="DT"."DATA_ID" AND "TF"."ENDDATE"="DT"."DATA_ID")
11 - filter("DT"."ANO"=2008)
12 - access("TF"."STARTTIME"="TMP"."HORA" AND "TF"."ENDTIME"="TMP"."HORA")
16 - filter("AG"."TIPO"='Crawler' OR "AG"."TIPO"='User')
After that i create all PK, FK and indexes:
PK & FK:
-- ADD PRIMARY KEYS
ALTER TABLE "DATA_DIM" ADD CONSTRAINT DATA_ID_PK PRIMARY KEY("DATA_ID");
ALTER TABLE "TEMPO_DIM" ADD CONSTRAINT HORA_PK PRIMARY KEY("HORA");
ALTER TABLE "AGENTE_DIM" ADD CONSTRAINT AGENTE_ID_PK PRIMARY KEY("AGENTE_ID");
ALTER TABLE "REQUEST_DIM" ADD CONSTRAINT REQUEST_ID_PK PRIMARY KEY("REQUEST_ID");
ALTER TABLE "REFERRER_DIM" ADD CONSTRAINT REFERRER_ID_PK PRIMARY KEY("REFERRER_ID");
ALTER TABLE "COMPUTADORUTILIZADOR_DIM" ADD CONSTRAINT IPADDRESS_PK PRIMARY KEY("IPADDRESS");
ALTER TABLE "TF_SESSIONS" ADD CONSTRAINT SESSIONID_PK PRIMARY KEY("SESSIONID");
ALTER TABLE "TP_SESSION" ADD CONSTRAINT SESSIONID_SEQUENTIAL_NUMBER_PK PRIMARY KEY("SESSIONID", "SEQUENTIAL_NUMBER");
ALTER TABLE "PAIS_SUBDIM" ADD CONSTRAINT IPADDRESS_PK PRIMARY KEY("CODPAIS");
-- ADD FOREIGN KEYS
ALTER TABLE "TF_SESSIONS" ADD CONSTRAINT STARTDATE_FK FOREIGN KEY (STARTDATE) REFERENCES DATA_DIM(DATA_ID);
ALTER TABLE "TF_SESSIONS" ADD CONSTRAINT ENDDATE_FK FOREIGN KEY (ENDDATE) REFERENCES DATA_DIM(DATA_ID);
ALTER TABLE "TF_SESSIONS" ADD CONSTRAINT STARTTIME_FK FOREIGN KEY (STARTTIME) REFERENCES TEMPO_DIM(HORA);
ALTER TABLE "TF_SESSIONS" ADD CONSTRAINT ENDTIME_FK FOREIGN KEY (ENDTIME )REFERENCES TEMPO_DIM(HORA);
ALTER TABLE "TF_SESSIONS" ADD CONSTRAINT REFERRER_FK FOREIGN KEY (REFERRER) REFERENCES REFERRER_DIM(REFERRER_ID);
ALTER TABLE "TF_SESSIONS" ADD CONSTRAINT USERAGENT_FK FOREIGN KEY (USERAGENT) REFERENCES AGENTE_DIM(AGENTE_ID);
ALTER TABLE "TF_SESSIONS" ADD CONSTRAINT HOST_FK FOREIGN KEY (HOST) REFERENCES COMPUTADORUTILIZADOR_DIM(IPADDRESS);
ALTER TABLE "TP_SESSION" ADD CONSTRAINT SESSIONID_FK FOREIGN KEY (SESSIONID) REFERENCES TF_SESSIONS(SESSIONID);
ALTER TABLE "TP_SESSION" ADD CONSTRAINT REQUESTID_FK FOREIGN KEY (REQUESTID) REFERENCES REQUEST_DIM(REQUEST_ID);
ALTER TABLE "COMPUTADORUTILIZADOR_DIM" ADD CONSTRAINT CODPAIS_FK FOREIGN KEY (CODPAIS) REFERENCES PAIS_SUBDIM(CODPAIS);
Indexes on the FKs:
CREATE INDEX IBT_FK1 ON TF_SESSIONS (STARTDATE)
CREATE INDEX IBT_FK2 ON TF_SESSIONS (ENDDATE)
CREATE INDEX IBT_FK3 ON TF_SESSIONS (STARTTIME)
CREATE INDEX IBT_FK4 ON TF_SESSIONS (ENDTIME)
CREATE INDEX IBT_FK5 ON TF_SESSIONS (REFERRER)
CREATE INDEX IBT_FK6 ON TF_SESSIONS (USERAGENT)
CREATE INDEX IBT_FK7 ON TF_SESSIONS (HOST)
CREATE INDEX IBT_FK8 ON TP_SESSION (SESSIONID)
CREATE INDEX IBT_FK9 ON TP_SESSION (REQUEST_ID)
CREATE INDEX IBT_FK10 ON COMPUTADORUTILIZADOR_DIM (CODIGOISOPAIS)
Indexes on the other attributes:
CREATE INDEX DURACAO_BTIX ON TF_SESSIONS (SESSION_DURATION)
CREATE INDEX PEDIDOS_BTIX ON TF_SESSIONS (PEDIDOS DESC)
CREATE INDEX ANO_BTIX ON DATA_DIM (ANO ASC)
CREATE INDEX TIPO_AGENTE_BTIX ON AGENTE_DIM (TIPO)
CREATE INDEX TIPO_CLASSE_REQUEST_BTIX ON REQUEST_DIM (TIPO_CLASSE)
CREATE INDEX MES_NR_BTIX ON DATA_DIM (MES_NR)
CREATE INDEX DIA_MES_NR_BTIX ON DATA_DIM (DIA_MES_NR)
CREATE INDEX PERIODO_BTIX ON TEMPO_DIM (PERIODO)
CREATE INDEX TIPO_REFERRER_BTIX ON REFERRER_DIM (TIPO)
CREATE INDEX MOTORPESQUISA_BTIX ON REFERRER_DIM (MOTORPESQUISA)
CREATE INDEX PAIS_BTIX ON PAIS_SUBDIM (PAIS)
CREATE INDEX TIPO_REQUEST_BTIX ON REQUEST_DIM (TIPO)
Here is a table with all indexes:
TABLE_NAME COLUMN_NAME INDEX_NAME INDEX_TYPE
------------------------------ ------------------- ------------------------------ ---------------------------
AGENTE_DIM TIPO TIPO_AGENTE_BTIX NORMAL
AGENTE_DIM AGENTE_ID AGENTE_ID_PK NORMAL
COMPUTADORUTILIZADOR_DIM IPADDRESS IPADDRESS_PK NORMAL
COMPUTADORUTILIZADOR_DIM CODIGOISOPAIS IBT_FK10 NORMAL
DATA_DIM DIA_MES_NR DIA_MES_NR_BTIX NORMAL
DATA_DIM MES_NR MES_NR_BTIX NORMAL
DATA_DIM ANO ANO_BTIX NORMAL
DATA_DIM DATA_ID DATA_ID_PK NORMAL
PAIS_SUBDIM CODIGOISOPAIS CODIGOISOPAIS_PK NORMAL
PAIS_SUBDIM PAIS PAIS_BTIX NORMAL
REFERRER_DIM MOTORPESQUISA MOTORPESQUISA_BTIX NORMAL
REFERRER_DIM REFERRER_ID REFERRER_ID_PK NORMAL
REFERRER_DIM TIPO TIPO_REFERRER_BTIX NORMAL
REQUEST_DIM TIPO_CLASSE TIPO_CLASSE_REQUEST_BTIX NORMAL
REQUEST_DIM REQUEST_ID REQUEST_ID_PK NORMAL
REQUEST_DIM TIPO TIPO_REQUEST_BTIX NORMAL
TEMPO_DIM HORA HORA_PK NORMAL
TEMPO_DIM PERIODO PERIODO_BTIX NORMAL
TF_SESSIONS STARTTIME IBT_FK3 NORMAL
TF_SESSIONS SESSIONID SESSIONID_PK NORMAL
TF_SESSIONS STARTDATE IBT_FK1 NORMAL
TF_SESSIONS ENDDATE IBT_FK2 NORMAL
TF_SESSIONS USERAGENT IBT_FK6 NORMAL
TF_SESSIONS SESSION_DURATION DURACAO_BTIX NORMAL
TF_SESSIONS SYS_NC00023$ PEDIDOS_BTIX FUNCTION-BASED NORMAL
TF_SESSIONS REFERRER IBT_FK5 NORMAL
TF_SESSIONS ENDTIME IBT_FK4 NORMAL
TF_SESSIONS HOST IBT_FK7 NORMAL
TP_SESSION SESSIONID IBT_FK8 NORMAL
TP_SESSION SESSIONID SESSIONID_SEQUENTIAL_NUMBER_PK NORMAL
TP_SESSION SEQUENTIAL_NUMBER SESSIONID_SEQUENTIAL_NUMBER_PK NORMAL
TP_SESSION REQUEST_ID IBT_FK9 NORMAL
after the creation of all indexes, i gathered the statistics of all of them with:
BEGIN
DBMS_STATS.GATHER_INDEX_STATS ('JORGE','AGENTE_ID_PK',ESTIMATE_PERCENT => 100);
END;
BEGIN
DBMS_STATS.GATHER_INDEX_STATS ('JORGE','TIPO_AGENTE_BTIX',ESTIMATE_PERCENT => 100);
END;
BEGIN
DBMS_STATS.GATHER_INDEX_STATS ('JORGE','IBT_FK10',ESTIMATE_PERCENT => 100);
END;
BEGIN
DBMS_STATS.GATHER_INDEX_STATS ('JORGE','IPADDRESS_PK',ESTIMATE_PERCENT => 100);
END;
BEGIN
DBMS_STATS.GATHER_INDEX_STATS ('JORGE','ANO_BTIX',ESTIMATE_PERCENT => 100);
END;
BEGIN
DBMS_STATS.GATHER_INDEX_STATS ('JORGE','DATA_ID_PK',ESTIMATE_PERCENT => 100);
END;
BEGIN
DBMS_STATS.GATHER_INDEX_STATS ('JORGE','DIA_MES_NR_BTIX',ESTIMATE_PERCENT => 100);
END;
BEGIN
DBMS_STATS.GATHER_INDEX_STATS ('JORGE','MES_NR_BTIX',ESTIMATE_PERCENT => 100);
END;
BEGIN
DBMS_STATS.GATHER_INDEX_STATS ('JORGE','CODIGOISOPAIS_PK',ESTIMATE_PERCENT => 100);
END;
BEGIN
DBMS_STATS.GATHER_INDEX_STATS ('JORGE','PAIS_BTIX',ESTIMATE_PERCENT => 100);
END;
BEGIN
DBMS_STATS.GATHER_INDEX_STATS ('JORGE','MOTORPESQUISA_BTIX',ESTIMATE_PERCENT => 100);
END;
BEGIN
DBMS_STATS.GATHER_INDEX_STATS ('JORGE','REFERRER_ID_PK',ESTIMATE_PERCENT => 100);
END;
BEGIN
DBMS_STATS.GATHER_INDEX_STATS ('JORGE','TIPO_REFERRER_BTIX',ESTIMATE_PERCENT => 100);
END;
BEGIN
DBMS_STATS.GATHER_INDEX_STATS ('JORGE','REQUEST_ID_PK',ESTIMATE_PERCENT => 100);
END;
BEGIN
DBMS_STATS.GATHER_INDEX_STATS ('JORGE','TIPO_CLASSE_REQUEST_BTIX',ESTIMATE_PERCENT => 100);
END;
BEGIN
DBMS_STATS.GATHER_INDEX_STATS ('JORGE','TIPO_REQUEST_BTIX',ESTIMATE_PERCENT => 100);
END;
BEGIN
DBMS_STATS.GATHER_INDEX_STATS ('JORGE', 'HORA_PK', ESTIMATE_PERCENT => 100);
END;
BEGIN
DBMS_STATS.GATHER_INDEX_STATS ('JORGE','PERIODO_BTIX',ESTIMATE_PERCENT => 100);
END;
BEGIN
DBMS_STATS.GATHER_INDEX_STATS ('JORGE','DURACAO_BTIX',ESTIMATE_PERCENT => 100);
END;
BEGIN
DBMS_STATS.GATHER_INDEX_STATS ('JORGE', 'IBT_FK1', ESTIMATE_PERCENT => 100);
END;
BEGIN
DBMS_STATS.GATHER_INDEX_STATS ('JORGE', 'IBT_FK2', ESTIMATE_PERCENT => 100);
END;
BEGIN
DBMS_STATS.GATHER_INDEX_STATS ('JORGE', 'IBT_FK3', ESTIMATE_PERCENT => 100);
END;
BEGIN
DBMS_STATS.GATHER_INDEX_STATS ('JORGE', 'IBT_FK4', ESTIMATE_PERCENT => 100);
END;
BEGIN
DBMS_STATS.GATHER_INDEX_STATS ('JORGE', 'IBT_FK5', ESTIMATE_PERCENT => 100);
END;
BEGIN
DBMS_STATS.GATHER_INDEX_STATS ('JORGE', 'IBT_FK6', ESTIMATE_PERCENT => 100);
END;
BEGIN
DBMS_STATS.GATHER_INDEX_STATS ('JORGE', 'IBT_FK7', ESTIMATE_PERCENT => 100);
END;
BEGIN
DBMS_STATS.GATHER_INDEX_STATS ('JORGE','PEDIDOS_BTIX',ESTIMATE_PERCENT => 100);
END;
BEGIN
DBMS_STATS.GATHER_INDEX_STATS ('JORGE','SESSIONID_PK',ESTIMATE_PERCENT => 100);
END;
BEGIN
DBMS_STATS.GATHER_INDEX_STATS ('JORGE', 'IBT_FK8', ESTIMATE_PERCENT => 100);
END;
BEGIN
DBMS_STATS.GATHER_INDEX_STATS ('JORGE', 'IBT_FK9', ESTIMATE_PERCENT => 100);
END;
BEGIN
DBMS_STATS.GATHER_INDEX_STATS ('JORGE','SESSIONID_SEQUENTIAL_NUMBER_PK',ESTIMATE_PERCENT => 100);
END;
After all this, i run the previous query again.
Explain Plan with indexes:
-------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 198 | 9999 (2)| 00:02:00 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT | SYS_TEMP_0FD9D661E_E1A0D | | | | |
|* 3 | TABLE ACCESS FULL | REQUEST_DIM | 338 | 6422 | 102 (0)| 00:00:02 |
| 4 | LOAD AS SELECT | SYS_TEMP_0FD9D661E_E1A0D | | | | |
|* 5 | HASH JOIN | | 2 | 210 | 1066 (1)| 00:00:13 |
| 6 | TABLE ACCESS BY INDEX ROWID | DATA_DIM | 366 | 5490 | 6 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | ANO_BTIX | 366 | | 1 (0)| 00:00:01 |
|* 8 | TABLE ACCESS BY INDEX ROWID | TF_SESSIONS | 1956 | 171K| 1060 (1)| 00:00:13 |
| 9 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 10 | BITMAP AND | | | | | |
| 11 | BITMAP MERGE | | | | | |
| 12 | BITMAP KEY ITERATION | | | | | |
|* 13 | VIEW | index$_join$_041 | 634 | 9510 | 86 (2)| 00:00:02 |
|* 14 | HASH JOIN | | | | | |
| 15 | INLIST ITERATOR | | | | | |
|* 16 | INDEX RANGE SCAN | TIPO_AGENTE_BTIX | 634 | 9510 | 2 (0)| 00:00:01 |
| 17 | INDEX FAST FULL SCAN | AGENTE_ID_PK | 634 | 9510 | 85 (0)| 00:00:02 |
| 18 | BITMAP CONVERSION FROM ROWIDS | | | | | |
|* 19 | INDEX RANGE SCAN | IBT_FK6 | | | 3 (0)| 00:00:01 |
| 20 | BITMAP MERGE | | | | | |
| 21 | BITMAP KEY ITERATION | | | | | |
| 22 | TABLE ACCESS BY INDEX ROWID | DATA_DIM | 366 | 5490 | 6 (0)| 00:00:01 |
|* 23 | INDEX RANGE SCAN | ANO_BTIX | 366 | | 1 (0)| 00:00:01 |
| 24 | BITMAP CONVERSION FROM ROWIDS | | | | | |
|* 25 | INDEX RANGE SCAN | IBT_FK1 | | | 8 (0)| 00:00:01 |
| 26 | SORT ORDER BY | | 1 | 198 | 8830 (2)| 00:01:46 |
| 27 | HASH GROUP BY | | 1 | 198 | 8830 (2)| 00:01:46 |
|* 28 | HASH JOIN | | 1 | 198 | 8828 (2)| 00:01:46 |
|* 29 | HASH JOIN | | 1 | 165 | 8825 (2)| 00:01:46 |
|* 30 | HASH JOIN | | 1 | 148 | 8444 (2)| 00:01:42 |
|* 31 | HASH JOIN | | 1 | 136 | 8420 (2)| 00:01:42 |
|* 32 | HASH JOIN | | 1 | 121 | 8414 (2)| 00:01:41 |
|* 33 | HASH JOIN | | 1 | 113 | 8410 (2)| 00:01:41 |
|* 34 | HASH JOIN | | 1 | 23 | 7350 (2)| 00:01:29 |
| 35 | TABLE ACCESS BY INDEX ROWID | TP_SESSION | 2 | 27 | 7347 (2)| 00:01:29 |
| 36 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 37 | BITMAP AND | | | | | |
| 38 | BITMAP MERGE | | | | | |
| 39 | BITMAP KEY ITERATION | | | | | |
| 40 | TABLE ACCESS FULL | SYS_TEMP_0FD9D661F_E1A0D | 1 | 13 | 2 (0)| 00:00:01 |
| 41 | BITMAP CONVERSION FROM ROWIDS| | | | | |
|* 42 | INDEX RANGE SCAN | IBT_FK8 | | | 3 (0)| 00:00:01 |
| 43 | BITMAP MERGE | | | | | |
| 44 | BITMAP KEY ITERATION | | | | | |
| 45 | TABLE ACCESS FULL | SYS_TEMP_0FD9D661E_E1A0D | 1 | 13 | 2 (0)| 00:00:01 |
| 46 | BITMAP CONVERSION FROM ROWIDS| | | | | |
|* 47 | INDEX RANGE SCAN | IBT_FK9 | | | 12 (0)| 00:00:01 |
| 48 | TABLE ACCESS FULL | SYS_TEMP_0FD9D661E_E1A0D | 338 | 4056 | 2 (0)| 00:00:01 |
| 49 | TABLE ACCESS BY INDEX ROWID | TF_SESSIONS | 1956 | 171K| 1060 (1)| 00:00:13 |
| 50 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 51 | BITMAP AND | | | | | |
| 52 | BITMAP MERGE | | | | | |
| 53 | BITMAP KEY ITERATION | | | | | |
|* 54 | VIEW | index$_join$_074 | 634 | 9510 | 86 (2)| 00:00:02 |
|* 55 | HASH JOIN | | | | | |
| 56 | INLIST ITERATOR | | | | | |
|* 57 | INDEX RANGE SCAN | TIPO_AGENTE_BTIX | 634 | 9510 | 2 (0)| 00:00:01 |
| 58 | INDEX FAST FULL SCAN | AGENTE_ID_PK | 634 | 9510 | 85 (0)| 00:00:02 |
| 59 | BITMAP CONVERSION FROM ROWIDS | | | | | |
|* 60 | INDEX RANGE SCAN | IBT_FK6 | | | 3 (0)| 00:00:01 |
| 61 | BITMAP MERGE | | | | | |
| 62 | BITMAP KEY ITERATION | | | | | |
| 63 | TABLE ACCESS BY INDEX ROWID | DATA_DIM | 366 | 5490 | 6 (0)| 00:00:01 |
|* 64 | INDEX RANGE SCAN | ANO_BTIX | 366 | | 1 (0)| 00:00:01 |
| 65 | BITMAP CONVERSION FROM ROWIDS | | | | | |
|* 66 | INDEX RANGE SCAN | IBT_FK1 | | | 8 (0)| 00:00:01 |
| 67 | TABLE ACCESS FULL | TEMPO_DIM | 24 | 192 | 3 (0)| 00:00:01 |
| 68 | TABLE ACCESS BY INDEX ROWID | DATA_DIM | 366 | 5490 | 6 (0)| 00:00:01 |
|* 69 | INDEX RANGE SCAN | ANO_BTIX | 366 | | 1 (0)| 00:00:01 |
| 70 | TABLE ACCESS FULL | REFERRER_DIM | 3936 | 47232 | 23 (0)| 00:00:01 |
| 71 | TABLE ACCESS FULL | COMPUTADORUTILIZADOR_DIM | 398K| 6623K| 379 (2)| 00:00:05 |
| 72 | TABLE ACCESS FULL | PAIS_SUBDIM | 100 | 3300 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("RQ"."TIPO_CLASSE"='C39')
5 - access("TF"."STARTDATE"="DT"."DATA_ID" AND "TF"."ENDDATE"="DT"."DATA_ID")
7 - access("DT"."ANO"=2008)
8 - filter("TF"."ENDTIME"="TF"."STARTTIME" AND "TF"."ENDDATE"="TF"."STARTDATE")
13 - filter("AG"."TIPO"='Crawler' OR "AG"."TIPO"='User')
14 - access(ROWID=ROWID)
16 - access("AG"."TIPO"='Crawler' OR "AG"."TIPO"='User')
19 - access("TF"."USERAGENT"="AG"."AGENTE_ID")
23 - access("DT"."ANO"=2008)
25 - access("TF"."STARTDATE"="DT"."DATA_ID")
28 - access("CU"."CODIGOISOPAIS"="PA"."CODIGOISOPAIS")
29 - access("TF"."HOST"="CU"."IPADDRESS")
30 - access("TF"."REFERRER"="RF"."REFERRER_ID")
31 - access("TF"."STARTDATE"="DT"."DATA_ID" AND "TF"."ENDDATE"="DT"."DATA_ID")
32 - access("TF"."STARTTIME"="TMP"."HORA" AND "TF"."ENDTIME"="TMP"."HORA")
33 - access("TP"."SESSIONID"="TF"."SESSIONID")
34 - access("TP"."REQUEST_ID"="C0")
42 - access("TP"."SESSIONID"="C0")
47 - access("TP"."REQUEST_ID"="C0")
54 - filter("AG"."TIPO"='Crawler' OR "AG"."TIPO"='User')
55 - access(ROWID=ROWID)
57 - access("AG"."TIPO"='Crawler' OR "AG"."TIPO"='User')
60 - access("TF"."USERAGENT"="AG"."AGENTE_ID")
64 - access("DT"."ANO"=2008)
66 - access("TF"."STARTDATE"="DT"."DATA_ID")
69 - access("DT"."ANO"=2008)
Note
-----
- star transformation used for this statement
Now, the problem is that the query run slower with the indexes.
without indexes => Time: 00:01:39:813
with indexes => Time: 00:02:26:985
Can anyone give me any reasonable explanation for this to happen?
Thanks for the help.
Jorge Abreu
|
|
|
|
|
|
| Re: Query run faster without indexes. Why? [message #421052 is a reply to message #421029] |
Thu, 03 September 2009 23:12 |
JorgeAbreu
Messages: 10
Registered: September 2009
Location: Braga, Portugal
|
Junior Member |
|
|
yes.
I also tried to create composite indexes, but the Oracle does not use them. If i force the Oracle to use them with hint on the queries, the time gets worse.
My guess is that Oracle loses more time in loading and searching the indexes and then access the tables, than do the loading and searching directly on the data tables.
thanks for the patience and help.
Jorge Abreu
|
|
|
|
|
|
| Re: Query run faster without indexes. Why? [message #421097 is a reply to message #421052] |
Fri, 04 September 2009 03:58 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
My guess would be that Oracle thinks that some of your indexes are much more selective than they actually are.
Try recomputing the stats on the three tables that actually have column values specified for them in the query:begin
dbms_stats.gather_table_Stats(ownname => null
,tabname => 'DATA_DIM'
,estimate_percent => 100
,method_opt => 'FOR ALL INDEXES COLUMNS'
,cascade => true);
dbms_stats.gather_table_Stats(ownname => null
,tabname => 'REQUEST_DIM'
,estimate_percent => 100
,method_opt => 'FOR ALL INDEXES COLUMNS'
,cascade => true);
dbms_stats.gather_table_Stats(ownname => null
,tabname => 'AGENETE_DIM'
,estimate_percent => 100
,method_opt => 'FOR ALL INDEXES COLUMNS'
,cascade => true);
|
|
|
|
| Re: Query run faster without indexes. Why? [message #421168 is a reply to message #421029] |
Fri, 04 September 2009 12:56 |
JorgeAbreu
Messages: 10
Registered: September 2009
Location: Braga, Portugal
|
Junior Member |
|
|
Hi Michel Cadot,
Yes, I have read the links that you and rleishman provided me.
The links that rleishman provided me are very good, but in my case, i don't have problems with multi-users. I just have one user running this query.
About your links. The information is very good, but my query doesn't have Correlated sub-query expressions in the select clause or in any other place. But in fact both your information and rleishman, refers that this kind of results could happen.
that's why i said before:
"My guess is that Oracle loses more time in loading and searching the indexes and then access the tables, than do the loading and searching directly on the data tables."
thanks,
Jorge Abreu
|
|
|
|
| Re: Query run faster without indexes. Why? [message #421169 is a reply to message #421029] |
Fri, 04 September 2009 13:00 |
JorgeAbreu
Messages: 10
Registered: September 2009
Location: Braga, Portugal
|
Junior Member |
|
|
JRowbottom, i tried recomputing the stats with the commands that you have posted and i still have the same results.
By the way, i forgot to mention before, but this is not a OLTP data base, it's a Data Warehouse data base.
This work is for my master thesis. Now i have an possible explanation for this results.
Just for the record. If i switch every B-Tree index to Bitmap indexes, the results are much better. With bitmap indexes the query time is similar or a little bit better than the query without indexes.
|
|
|
|
|
|
| Re: Query run faster without indexes. Why? [message #421172 is a reply to message #421029] |
Fri, 04 September 2009 16:48 |
JorgeAbreu
Messages: 10
Registered: September 2009
Location: Braga, Portugal
|
Junior Member |
|
|
********************************************************************************
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'
AND RQ.TIPO_CLASSE = 'C39'
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
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 1.29 1.32 0 0 4 0
Execute 2 2.10 26.89 12679 28286 244 0
Fetch 10 12.42 135.24 25254 3099354 4 100
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 14 15.82 163.46 37933 3127640 252 100
Misses in library cache during parse: 2
Optimizer mode: ALL_ROWS
Parsing user id: 82 (JORGE)
Rows Row Source Operation
------- ---------------------------------------------------
50 TEMP TABLE TRANSFORMATION (cr=1563820 pr=485 pw=485 time=9 us)
1 LOAD AS SELECT (cr=329 pr=0 pw=0 time=0 us)
338 TABLE ACCESS FULL REQUEST_DIM (cr=329 pr=0 pw=0 time=27 us cost=102 size=6422 card=338)
1 LOAD AS SELECT (cr=13814 pr=0 pw=0 time=0 us)
10399 HASH JOIN (cr=13814 pr=0 pw=0 time=1117 us cost=1084 size=210 card=2)
366 TABLE ACCESS BY INDEX ROWID DATA_DIM (cr=6 pr=0 pw=0 time=32 us cost=6 size=5490 card=366)
366 INDEX RANGE SCAN ANO_BTIX (cr=2 pr=0 pw=0 time=7 us cost=1 size=0 card=366)(object id 69937)
10399 TABLE ACCESS BY INDEX ROWID TF_SESSIONS (cr=13808 pr=0 pw=0 time=422 us cost=1078 size=181620 card=2018)
13921 BITMAP CONVERSION TO ROWIDS (cr=4060 pr=0 pw=0 time=248 us)
3 BITMAP AND (cr=4060 pr=0 pw=0 time=20973 us)
4 BITMAP MERGE (cr=1408 pr=0 pw=0 time=3794 us)
634 BITMAP KEY ITERATION (cr=1408 pr=0 pw=0 time=272 us)
634 VIEW index$_join$_041 (cr=77 pr=0 pw=0 time=120 us cost=86 size=9810 card=654)
634 HASH JOIN (cr=77 pr=0 pw=0 time=33 us)
634 INLIST ITERATOR (cr=5 pr=0 pw=0 time=31 us)
634 INDEX RANGE SCAN TIPO_AGENTE_BTIX (cr=5 pr=0 pw=0 time=14 us cost=2 size=9810 card=654)(object id 69938)
32679 INDEX FAST FULL SCAN AGENTE_ID_PK (cr=72 pr=0 pw=0 time=541 us cost=85 size=9810 card=654)(object id 69894)
634 BITMAP CONVERSION FROM ROWIDS (cr=1331 pr=0 pw=0 time=0 us)
23231 INDEX RANGE SCAN IBT_FK6 (cr=1331 pr=0 pw=0 time=1777 us cost=3 size=0 card=0)(object id 69930)
14 BITMAP MERGE (cr=2652 pr=0 pw=0 time=2486 us)
366 BITMAP KEY ITERATION (cr=2652 pr=0 pw=0 time=1898 us)
366 TABLE ACCESS BY INDEX ROWID DATA_DIM (cr=6 pr=0 pw=0 time=24 us cost=6 size=5490 card=366)
366 INDEX RANGE SCAN ANO_BTIX (cr=2 pr=0 pw=0 time=6 us cost=1 size=0 card=366)(object id 69937)
366 BITMAP CONVERSION FROM ROWIDS (cr=2646 pr=0 pw=0 time=0 us)
722283 INDEX RANGE SCAN IBT_FK1 (cr=2646 pr=0 pw=0 time=13380 us cost=8 size=0 card=0)(object id 69925)
50 SORT ORDER BY (cr=1549677 pr=485 pw=485 time=5 us cost=8866 size=198 card=1)
12641 HASH GROUP BY (cr=1549677 pr=485 pw=485 time=311 us cost=8866 size=198 card=1)
12937 HASH JOIN (cr=1549677 pr=110 pw=110 time=308 us cost=8864 size=198 card=1)
12937 HASH JOIN (cr=1549670 pr=110 pw=110 time=1534 us cost=8860 size=165 card=1)
12937 HASH JOIN (cr=1548393 pr=110 pw=110 time=319 us cost=8479 size=148 card=1)
12937 HASH JOIN (cr=1548309 pr=110 pw=110 time=308 us cost=8456 size=136 card=1)
12937 HASH JOIN (cr=1548303 pr=110 pw=110 time=278 us cost=8449 size=121 card=1)
12937 HASH JOIN (cr=1548296 pr=110 pw=110 time=644 us cost=8446 size=113 card=1)
12937 HASH JOIN (cr=1534488 pr=110 pw=110 time=228 us cost=7368 size=23 card=1)
12937 TABLE ACCESS BY INDEX ROWID TP_SESSION (cr=1534485 pr=110 pw=110 time=818 us cost=7365 size=27 card=2)
12937 BITMAP CONVERSION TO ROWIDS (cr=1527102 pr=110 pw=110 time=215 us)
3 BITMAP AND (cr=1527102 pr=110 pw=110 time=186180 us)
8 BITMAP MERGE (cr=22330 pr=109 pw=109 time=8380 us)
10399 BITMAP KEY ITERATION (cr=22330 pr=109 pw=109 time=5046 us)
10399 TABLE ACCESS FULL SYS_TEMP_0FD9D664F_E1A0D (cr=113 pr=109 pw=109 time=246 us cost=2 size=13 card=1)
10399 BITMAP CONVERSION FROM ROWIDS (cr=22217 pr=0 pw=0 time=0 us)
624757 INDEX RANGE SCAN IBT_FK8 (cr=22217 pr=0 pw=0 time=33598 us cost=3 size=0 card=0)(object id 69932)
149 BITMAP MERGE (cr=1504772 pr=1 pw=1 time=3534 us)
338 BITMAP KEY ITERATION (cr=1504772 pr=1 pw=1 time=16041 us)
338 TABLE ACCESS FULL SYS_TEMP_0FD9D664E_E1A0D (cr=5 pr=1 pw=1 time=12 us cost=2 size=13 card=1)
338 BITMAP CONVERSION FROM ROWIDS (cr=1504767 pr=0 pw=0 time=0 us)
1503753 INDEX RANGE SCAN IBT_FK9 (cr=1504767 pr=0 pw=0 time=43990 us cost=12 size=0 card=0)(object id 69933)
338 TABLE ACCESS FULL SYS_TEMP_0FD9D664E_E1A0D (cr=3 pr=0 pw=0 time=10 us cost=2 size=4056 card=338)
13921 TABLE ACCESS BY INDEX ROWID TF_SESSIONS (cr=13808 pr=0 pw=0 time=913 us cost=1078 size=181620 card=2018)
13921 BITMAP CONVERSION TO ROWIDS (cr=4060 pr=0 pw=0 time=226 us)
3 BITMAP AND (cr=4060 pr=0 pw=0 time=21549 us)
4 BITMAP MERGE (cr=1408 pr=0 pw=0 time=3905 us)
634 BITMAP KEY ITERATION (cr=1408 pr=0 pw=0 time=288 us)
634 VIEW index$_join$_074 (cr=77 pr=0 pw=0 time=137 us cost=86 size=9810 card=654)
634 HASH JOIN (cr=77 pr=0 pw=0 time=40 us)
634 INLIST ITERATOR (cr=5 pr=0 pw=0 time=31 us)
634 INDEX RANGE SCAN TIPO_AGENTE_BTIX (cr=5 pr=0 pw=0 time=13 us cost=2 size=9810 card=654)(object id 69938)
32679 INDEX FAST FULL SCAN AGENTE_ID_PK (cr=72 pr=0 pw=0 time=527 us cost=85 size=9810 card=654)(object id 69894)
634 BITMAP CONVERSION FROM ROWIDS (cr=1331 pr=0 pw=0 time=0 us)
23231 INDEX RANGE SCAN IBT_FK6 (cr=1331 pr=0 pw=0 time=1892 us cost=3 size=0 card=0)(object id 69930)
14 BITMAP MERGE (cr=2652 pr=0 pw=0 time=2501 us)
366 BITMAP KEY ITERATION (cr=2652 pr=0 pw=0 time=1844 us)
366 TABLE ACCESS BY INDEX ROWID DATA_DIM (cr=6 pr=0 pw=0 time=23 us cost=6 size=5490 card=366)
366 INDEX RANGE SCAN ANO_BTIX (cr=2 pr=0 pw=0 time=7 us cost=1 size=0 card=366)(object id 69937)
366 BITMAP CONVERSION FROM ROWIDS (cr=2646 pr=0 pw=0 time=0 us)
722283 INDEX RANGE SCAN IBT_FK1 (cr=2646 pr=0 pw=0 time=13387 us cost=8 size=0 card=0)(object id 69925)
24 TABLE ACCESS FULL TEMPO_DIM (cr=7 pr=0 pw=0 time=4 us cost=3 size=192 card=24)
366 TABLE ACCESS BY INDEX ROWID DATA_DIM (cr=6 pr=0 pw=0 time=42 us cost=6 size=5490 card=366)
366 INDEX RANGE SCAN ANO_BTIX (cr=2 pr=0 pw=0 time=12 us cost=1 size=0 card=366)(object id 69937)
3936 TABLE ACCESS FULL REFERRER_DIM (cr=84 pr=0 pw=0 time=69 us cost=23 size=47232 card=3936)
398988 TABLE ACCESS FULL COMPUTADORUTILIZADOR_DIM (cr=1277 pr=0 pw=0 time=6340 us cost=379 size=6782796 card=398988)
100 TABLE ACCESS FULL PAIS_SUBDIM (cr=7 pr=0 pw=0 time=4 us cost=3 size=3300 card=100)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
50 TEMP TABLE TRANSFORMATION
1 LOAD AS SELECT OF 'SYS_TEMP_0FD9D6652_E1A0D'
338 TABLE ACCESS MODE: ANALYZED (FULL) OF 'REQUEST_DIM' (TABLE)
1 LOAD AS SELECT OF 'SYS_TEMP_0FD9D6652_E1A0D'
10399 HASH JOIN
366 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'DATA_DIM' (TABLE)
366 INDEX MODE: ANALYZED (RANGE SCAN) OF 'ANO_BTIX' (INDEX)
10399 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'TF_SESSIONS' (TABLE)
13921 BITMAP CONVERSION (TO ROWIDS)
3 BITMAP AND
4 BITMAP MERGE
634 BITMAP KEY ITERATION
634 VIEW OF 'index$_join$_041' (VIEW)
634 HASH JOIN
634 INLIST ITERATOR
634 INDEX MODE: ANALYZED (RANGE SCAN) OF
'TIPO_AGENTE_BTIX' (INDEX)
32679 INDEX MODE: ANALYZED (FAST FULL SCAN) OF
'AGENTE_ID_PK' (INDEX (UNIQUE))
634 BITMAP CONVERSION (FROM ROWIDS)
23231 INDEX MODE: ANALYZED (RANGE SCAN) OF
'IBT_FK6' (INDEX)
14 BITMAP MERGE
366 BITMAP KEY ITERATION
366 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID)
OF 'DATA_DIM' (TABLE)
366 INDEX MODE: ANALYZED (RANGE SCAN) OF
'ANO_BTIX' (INDEX)
366 BITMAP CONVERSION (FROM ROWIDS)
722283 INDEX MODE: ANALYZED (RANGE SCAN) OF
'IBT_FK1' (INDEX)
50 SORT (ORDER BY)
12641 HASH (GROUP BY)
12937 HASH JOIN
12937 HASH JOIN
12937 HASH JOIN
12937 HASH JOIN
12937 HASH JOIN
12937 HASH JOIN
12937 HASH JOIN
12937 TABLE ACCESS MODE: ANALYZED (BY INDEX
ROWID) OF 'TP_SESSION' (TABLE)
12937 BITMAP CONVERSION (TO ROWIDS)
3 BITMAP AND
8 BITMAP MERGE
10399 BITMAP KEY ITERATION
10399 TABLE ACCESS (FULL) OF
'SYS_TEMP_0FD9D6653_E1A0D' (TABLE (TEMP))
10399 BITMAP CONVERSION (FROM ROWIDS)
624757 INDEX MODE: ANALYZED (RANGE
SCAN) OF 'IBT_FK8' (INDEX)
149 BITMAP MERGE
338 BITMAP KEY ITERATION
338 TABLE ACCESS (FULL) OF
'SYS_TEMP_0FD9D6652_E1A0D' (TABLE (TEMP))
338 BITMAP CONVERSION (FROM ROWIDS)
1503753 INDEX MODE: ANALYZED (RANGE
SCAN) OF 'IBT_FK9' (INDEX)
338 TABLE ACCESS (FULL) OF
'SYS_TEMP_0FD9D6652_E1A0D' (TABLE (TEMP))
13921 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID)
OF 'TF_SESSIONS' (TABLE)
13921 BITMAP CONVERSION (TO ROWIDS)
3 BITMAP AND
4 BITMAP MERGE
634 BITMAP KEY ITERATION
634 VIEW OF 'index$_join$_074' (VIEW)
634 HASH JOIN
634 INLIST ITERATOR
634 INDEX MODE: ANALYZED (RANGE
SCAN) OF 'TIPO_AGENTE_BTIX' (INDEX)
32679 INDEX MODE: ANALYZED (FAST
FULL SCAN) OF 'AGENTE_ID_PK' (INDEX (UNIQUE))
634 BITMAP CONVERSION (FROM ROWIDS)
23231 INDEX MODE: ANALYZED (RANGE SCAN)
OF 'IBT_FK6' (INDEX)
14 BITMAP MERGE
366 BITMAP KEY ITERATION
366 TABLE ACCESS MODE: ANALYZED (BY
INDEX ROWID) OF 'DATA_DIM' (TABLE)
366 INDEX MODE: ANALYZED (RANGE SCAN)
OF 'ANO_BTIX' (INDEX)
366 BITMAP CONVERSION (FROM ROWIDS)
722283 INDEX MODE: ANALYZED (RANGE SCAN)
OF 'IBT_FK1' (INDEX)
24 TABLE ACCESS MODE: ANALYZED (FULL) OF
'TEMPO_DIM' (TABLE)
366 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'DATA_DIM' (TABLE)
366 INDEX MODE: ANALYZED (RANGE SCAN) OF
'ANO_BTIX' (INDEX)
3936 TABLE ACCESS MODE: ANALYZED (FULL) OF
'REFERRER_DIM' (TABLE)
398988 TABLE ACCESS MODE: ANALYZED (FULL) OF
'COMPUTADORUTILIZADOR_DIM' (TABLE)
100 TABLE ACCESS MODE: ANALYZED (FULL) OF 'PAIS_SUBDIM'
(TABLE)
********************************************************************************
|
|
|
|
| Re: Query run faster without indexes. Why? [message #421173 is a reply to message #421029] |
Fri, 04 September 2009 18:04 |
JorgeAbreu
Messages: 10
Registered: September 2009
Location: Braga, Portugal
|
Junior Member |
|
|
PLAN_TABLE_OUTPUT
SQL_ID f5jb3ntj0btux, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ --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.REF
Plan hash value: 1390983175
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem | Used-Tmp|
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 1 | TEMP TABLE TRANSFORMATION | | 1 | | 12641 |00:02:31.19 | 1563K| 37450 | 485 | | | | |
| 2 | LOAD AS SELECT | | 1 | | 1 |00:00:00.30 | 334 | 328 | 1 | 261K| 261K| 261K (0)| |
|* 3 | TABLE ACCESS FULL | REQUEST_DIM | 1 | 338 | 338 |00:00:00.22 | 329 | 326 | 0 | | | | |
| 4 | LOAD AS SELECT | | 1 | | 1 |00:00:15.94 | 13926 | 12353 | 109 | 518K| 518K| 518K (0)| |
|* 5 | HASH JOIN | | 1 | 2 | 10399 |00:00:13.35 | 13814 | 12353 | 0 | 771K| 771K| 1199K (0)| |
| 6 | TABLE ACCESS BY INDEX ROWID | DATA_DIM | 1 | 366 | 366 |00:00:00.04 | 6 | 6 | 0 | | | | |
|* 7 | INDEX RANGE SCAN | ANO_BTIX | 1 | 366 | 366 |00:00:00.02 | 2 | 2 | 0 | | | | |
|* 8 | TABLE ACCESS BY INDEX ROWID | TF_SESSIONS | 1 | 2018 | 10399 |00:00:13.19 | 13808 | 12347 | 0 | | | | |
| 9 | BITMAP CONVERSION TO ROWIDS | | 1 | | 13921 |00:00:02.63 | 4060 | 2599 | 0 | | | | |
| 10 | BITMAP AND | | 1 | | 3 |00:00:02.65 | 4060 | 2599 | 0 | | | | |
| 11 | BITMAP MERGE | | 1 | | 4 |00:00:01.34 | 1408 | 682 | 0 | 1024K| 512K| 131K (0)| |
| 12 | BITMAP KEY ITERATION | | 1 | | 634 |00:00:00.47 | 1408 | 682 | 0 | | | | |
|* 13 | VIEW | index$_join$_041 | 1 | 654 | 634 |00:00:00.06 | 77 | 72 | 0 | | | | |
|* 14 | HASH JOIN | | 1 | | 634 |00:00:00.05 | 77 | 72 | 0 | 848K| 848K| 1180K (0)| |
| 15 | INLIST ITERATOR | | 1 | | 634 |00:00:00.03 | 5 | 3 | 0 | | | | |
|* 16 | INDEX RANGE SCAN | TIPO_AGENTE_BTIX | 2 | 654 | 634 |00:00:00.03 | 5 | 3 | 0 | | | | |
| 17 | INDEX FAST FULL SCAN | AGENTE_ID_PK | 1 | 654 | 32679 |00:00:00.08 | 72 | 69 | 0 | | | | |
| 18 | BITMAP CONVERSION FROM ROWIDS | | 634 | | 634 |00:00:01.16 | 1331 | 610 | 0 | | | | |
|* 19 | INDEX RANGE SCAN | IBT_FK6 | 634 | | 23231 |00:00:01.15 | 1331 | 610 | 0 | | | | |
| 20 | BITMAP MERGE | | 1 | | 14 |00:00:01.28 | 2652 | 1917 | 0 | 1024K| 512K| 2254K (0)| |
| 21 | BITMAP KEY ITERATION | | 1 | | 366 |00:00:06.27 | 2652 | 1917 | 0 | | | | |
| 22 | TABLE ACCESS BY INDEX ROWID | DATA_DIM | 1 | 366 | 366 |00:00:00.01 | 6 | 0 | 0 | | | | |
|* 23 | INDEX RANGE SCAN | ANO_BTIX | 1 | 366 | 366 |00:00:00.01 | 2 | 0 | 0 | | | | |
| 24 | BITMAP CONVERSION FROM ROWIDS | | 366 | | 366 |00:00:01.23 | 2646 | 1917 | 0 | | | | |
|* 25 | INDEX RANGE SCAN | IBT_FK1 | 366 | | 722K|00:00:02.28 | 2646 | 1917 | 0 | | | | |
| 26 | SORT ORDER BY | | 1 | 1 | 12641 |00:02:14.90 | 1549K| 24769 | 375 | 3951K| 851K| 3511K (0)| |
| 27 | HASH GROUP BY | | 1 | 1 | 12641 |00:02:14.77 | 1549K| 24769 | 375 | 5599K| 1829K| 3116K (1)| 4096 |
|* 28 | HASH JOIN | | 1 | 1 | 12937 |00:02:14.72 | 1549K| 24394 | 0 | 4380K| 1845K| 4331K (0)| |
|* 29 | HASH JOIN | | 1 | 1 | 12937 |00:02:14.18 | 1549K| 24388 | 0 | 3639K| 924K| 4353K (0)| |
|* 30 | HASH JOIN | | 1 | 1 | 12937 |00:02:14.08 | 1548K| 23115 | 0 | 3472K| 926K| 3642K (0)| |
|* 31 | HASH JOIN | | 1 | 1 | 12937 |00:02:13.90 | 1548K| 23034 | 0 | 2971K| 932K| 3526K (0)| |
|* 32 | HASH JOIN | | 1 | 1 | 12937 |00:02:13.87 | 1548K| 23034 | 0 | 2747K| 936K| 3166K (0)| |
|* 33 | HASH JOIN | | 1 | 1 | 12937 |00:02:13.80 | 1548K| 23028 | 0 | 1296K| 1037K| 1553K (0)| |
|* 34 | HASH JOIN | | 1 | 1 | 12937 |00:02:13.30 | 1534K| 23028 | 0 | 1100K| 1100K| 1588K (0)| |
| 35 | TABLE ACCESS BY INDEX ROWID | TP_SESSION | 1 | 2 | 12937 |00:02:03.34 | 1534K| 23028 | 0 | | | | |
| 36 | BITMAP CONVERSION TO ROWIDS | | 1 | | 12937 |00:01:23.87 | 1527K| 15645 | 0 | | | | |
| 37 | BITMAP AND | | 1 | | 3 |00:01:24.20 | 1527K| 15645 | 0 | | | | |
| 38 | BITMAP MERGE | | 1 | | 8 |00:01:13.79 | 22331 | 11907 | 0 | 1185K| 512K| 1194K (0)| |
| 39 | BITMAP KEY ITERATION | | 1 | | 10399 |00:01:19.97 | 22331 | 11907 | 0 | | | | |
| 40 | TABLE ACCESS FULL | SYS_TEMP_0FD9D665D_E1A0D | 1 | 1 | 10399 |00:00:00.16 | 114 | 109 | 0 | | | | |
| 41 | BITMAP CONVERSION FROM ROWIDS| | 10399 | | 10399 |00:01:13.45 | 22217 | 11798 | 0 | | | | |
|* 42 | INDEX RANGE SCAN | IBT_FK8 | 10399 | | 624K|00:01:14.40 | 22217 | 11798 | 0 | | | | |
| 43 | BITMAP MERGE | | 1 | | 149 |00:00:10.18 | 1504K| 3738 | 0 | 1024K| 512K| 4957K (0)| |
| 44 | BITMAP KEY ITERATION | | 1 | | 338 |00:00:15.85 | 1504K| 3738 | 0 | | | | |
| 45 | TABLE ACCESS FULL | SYS_TEMP_0FD9D665C_E1A0D | 1 | 1 | 338 |00:00:00.02 | 6 | 1 | 0 | | | | |
| 46 | BITMAP CONVERSION FROM ROWIDS| | 338 | | 338 |00:00:09.85 | 1504K| 3737 | 0 | | | | |
|* 47 | INDEX RANGE SCAN | IBT_FK9 | 338 | | 1503K|00:00:12.54 | 1504K| 3737 | 0 | | | | |
| 48 | TABLE ACCESS FULL | SYS_TEMP_0FD9D665C_E1A0D | 1 | 338 | 338 |00:00:00.01 | 3 | 0 | 0 | | | | |
| 49 | TABLE ACCESS BY INDEX ROWID | TF_SESSIONS | 1 | 2018 | 13921 |00:00:00.54 | 13808 | 0 | 0 | | | | |
| 50 | BITMAP CONVERSION TO ROWIDS | | 1 | | 13921 |00:00:00.46 | 4060 | 0 | 0 | | | | |
| 51 | BITMAP AND | | 1 | | 3 |00:00:00.48 | 4060 | 0 | 0 | | | | |
| 52 | BITMAP MERGE | | 1 | | 4 |00:00:00.07 | 1408 | 0 | 0 | 1024K| 512K| 131K (0)| |
| 53 | BITMAP KEY ITERATION | | 1 | | 634 |00:00:00.05 | 1408 | 0 | 0 | | | | |
|* 54 | VIEW | index$_join$_074 | 1 | 654 | 634 |00:00:00.02 | 77 | 0 | 0 | | | | |
|* 55 | HASH JOIN | | 1 | | 634 |00:00:00.01 | 77 | 0 | 0 | 848K| 848K| 1203K (0)| |
| 56 | INLIST ITERATOR | | 1 | | 634 |00:00:00.01 | 5 | 0 | 0 | | | | |
|* 57 | INDEX RANGE SCAN | TIPO_AGENTE_BTIX | 2 | 654 | 634 |00:00:00.01 | 5 | 0 | 0 | | | | |
| 58 | INDEX FAST FULL SCAN | AGENTE_ID_PK | 1 | 654 | 32679 |00:00:00.07 | 72 | 0 | 0 | | | | |
| 59 | BITMAP CONVERSION FROM ROWIDS | | 634 | | 634 |00:00:00.03 | 1331 | 0 | 0 | | | | |
|* 60 | INDEX RANGE SCAN | IBT_FK6 | 634 | | 23231 |00:00:00.05 | 1331 | 0 | 0 | | | | |
| 61 | BITMAP MERGE | | 1 | | 14 |00:00:00.39 | 2652 | 0 | 0 | 1024K| 512K| 2254K (0)| |
| 62 | BITMAP KEY ITERATION | | 1 | | 366 |00:00:00.34 | 2652 | 0 | 0 | | | | |
| 63 | TABLE ACCESS BY INDEX ROWID | DATA_DIM | 1 | 366 | 366 |00:00:00.01 | 6 | 0 | 0 | | | | |
|* 64 | INDEX RANGE SCAN | ANO_BTIX | 1 | 366 | 366 |00:00:00.01 | 2 | 0 | 0 | | | | |
| 65 | BITMAP CONVERSION FROM ROWIDS | | 366 | | 366 |00:00:00.33 | 2646 | 0 | 0 | | | | |
|* 66 | INDEX RANGE SCAN | IBT_FK1 | 366 | | 722K|00:00:01.45 | 2646 | 0 | 0 | | | | |
| 67 | TABLE ACCESS FULL | TEMPO_DIM | 1 | 24 | 24 |00:00:00.02 | 7 | 6 | 0 | | | | |
| 68 | TABLE ACCESS BY INDEX ROWID | DATA_DIM | 1 | 366 | 366 |00:00:00.01 | 6 | 0 | 0 | | | | |
|* 69 | INDEX RANGE SCAN | ANO_BTIX | 1 | 366 | 366 |00:00:00.01 | 2 | 0 | 0 | | | | |
| 70 | TABLE ACCESS FULL | REFERRER_DIM | 1 | 3936 | 3936 |00:00:00.12 | 84 | 81 | 0 | | | | |
| 71 | TABLE ACCESS FULL | COMPUTADORUTILIZADOR_DIM | 1 | 398K| 398K|00:00:00.80 | 1277 | 1273 | 0 | | | | |
| 72 | TABLE ACCESS FULL | PAIS_SUBDIM | 1 | 100 | 100 |00:00:00.02 | 7 | 6 | 0 | | | | |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("RQ"."TIPO_CLASSE"='C39')
5 - access("TF"."STARTDATE"="DT"."DATA_ID" AND "TF"."ENDDATE"="DT"."DATA_ID")
7 - access("DT"."ANO"=2008)
8 - filter(("TF"."ENDTIME"="TF"."STARTTIME" AND "TF"."ENDDATE"="TF"."STARTDATE"))
13 - filter(("AG"."TIPO"='Crawler' OR "AG"."TIPO"='User'))
14 - access(ROWID=ROWID)
16 - access(("AG"."TIPO"='Crawler' OR "AG"."TIPO"='User'))
19 - access("TF"."USERAGENT"="AG"."AGENTE_ID")
23 - access("DT"."ANO"=2008)
25 - access("TF"."STARTDATE"="DT"."DATA_ID")
28 - access("CU"."CODIGOISOPAIS"="PA"."CODIGOISOPAIS")
29 - access("TF"."HOST"="CU"."IPADDRESS")
30 - access("TF"."REFERRER"="RF"."REFERRER_ID")
31 - access("TF"."STARTDATE"="DT"."DATA_ID" AND "TF"."ENDDATE"="DT"."DATA_ID")
32 - access("TF"."STARTTIME"="TMP"."HORA" AND "TF"."ENDTIME"="TMP"."HORA")
33 - access("TP"."SESSIONID"="TF"."SESSIONID")
34 - access("TP"."REQUEST_ID"="C0")
42 - access("TP"."SESSIONID"="C0")
47 - access("TP"."REQUEST_ID"="C0")
54 - filter(("AG"."TIPO"='Crawler' OR "AG"."TIPO"='User'))
55 - access(ROWID=ROWID)
57 - access(("AG"."TIPO"='Crawler' OR "AG"."TIPO"='User'))
60 - access("TF"."USERAGENT"="AG"."AGENTE_ID")
64 - access("DT"."ANO"=2008)
66 - access("TF"."STARTDATE"="DT"."DATA_ID")
69 - access("DT"."ANO"=2008)
Note
-----
- star transformation used for this statement
|
|
|
|
| Re: Query run faster without indexes. Why? [message #421174 is a reply to message #421029] |
Fri, 04 September 2009 19:51 |
JorgeAbreu
Messages: 10
Registered: September 2009
Location: Braga, Portugal
|
Junior Member |
|
|
The two previous posts are from the query with indexes.
Next is the more detailed explain plan for the query without indexes.
PLAN_TABLE_OUTPUT
SQL_ID f5jb3ntj0btux, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ --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.REF
Plan hash value: 140656490
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem | Used-Tmp|
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 1 | SORT ORDER BY | | 1 | 1 | 12641 |00:01:33.41 | 196K| 199K| 3135 | 3880K| 845K| 3448K (0)| |
| 2 | HASH GROUP BY | | 1 | 1 | 12641 |00:01:32.82 | 196K| 199K| 3135 | 5599K| 1829K| 3130K (1)| 4096 |
|* 3 | HASH JOIN | | 1 | 1 | 12937 |00:01:32.48 | 196K| 198K| 2760 | 2084K| 953K| 2535K (0)| |
|* 4 | HASH JOIN | | 1 | 1 | 12937 |00:01:32.31 | 196K| 198K| 2760 | 1759K| 971K| 2196K (0)| |
|* 5 | HASH JOIN | | 1 | 1 | 12937 |00:01:30.85 | 196K| 198K| 2760 | 1924K| 962K| 2513K (0)| |
|* 6 | HASH JOIN | | 1 | 1 | 12937 |00:01:26.23 | 194K| 197K| 2760 | 75M| 7668K| 62M (1)| 24576 |
|* 7 | HASH JOIN | | 1 | 61 | 674K|00:01:09.95 | 194K| 194K| 0 | 898K| 898K| 1164K (0)| |
|* 8 | TABLE ACCESS FULL | REQUEST_DIM | 1 | 338 | 338 |00:00:00.27 | 329 | 326 | 0 | | | | |
|* 9 | HASH JOIN | | 1 | 2950 | 32M|00:01:46.42 | 193K| 193K| 0 | 58M| 3836K| 61M (0)| |
|* 10 | HASH JOIN | | 1 | 49 | 541K|00:00:17.92 | 18464 | 18458 | 0 | 825K| 825K| 1165K (0)| |
|* 11 | TABLE ACCESS FULL | DATA_DIM | 1 | 366 | 366 |00:00:00.02 | 46 | 44 | 0 | | | | |
|* 12 | HASH JOIN | | 1 | 50317 | 904K|00:00:08.15 | 18418 | 18414 | 0 | 878K| 878K| 1162K (0)| |
| 13 | TABLE ACCESS FULL| TEMPO_DIM | 1 | 24 | 24 |00:00:00.01 | 7 | 6 | 0 | | | | |
| 14 | TABLE ACCESS FULL| TF_SESSIONS | 1 | 1207K| 1207K|00:00:02.43 | 18411 | 18408 | 0 | | | | |
| 15 | TABLE ACCESS FULL | TP_SESSION | 1 | 72M| 72M|00:02:24.91 | 175K| 175K| 0 | | | | |
|* 16 | TABLE ACCESS FULL | AGENTE_DIM | 1 | 654 | 634 |00:00:00.22 | 552 | 549 | 0 | | | | |
| 17 | TABLE ACCESS FULL | COMPUTADORUTILIZADOR_DIM | 1 | 398K| 398K|00:00:00.88 | 1277 | 1273 | 0 | | | | |
| 18 | TABLE ACCESS FULL | PAIS_SUBDIM | 1 | 100 | 100 |00:00:00.07 | 7 | 6 | 0 | | | | |
| 19 | TABLE ACCESS FULL | REFERRER_DIM | 1 | 3936 | 3936 |00:00:00.13 | 84 | 81 | 0 | | | | |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("TF"."REFERRER"="RF"."REFERRER_ID")
4 - access("CU"."CODIGOISOPAIS"="PA"."CODIGOISOPAIS")
5 - access("TF"."HOST"="CU"."IPADDRESS")
6 - access("TF"."USERAGENT"="AG"."AGENTE_ID")
7 - access("TP"."REQUEST_ID"="RQ"."REQUEST_ID")
8 - filter("RQ"."TIPO_CLASSE"='C39')
9 - access("TP"."SESSIONID"="TF"."SESSIONID")
10 - access("TF"."STARTDATE"="DT"."DATA_ID" AND "TF"."ENDDATE"="DT"."DATA_ID")
11 - filter("DT"."ANO"=2008)
12 - access("TF"."STARTTIME"="TMP"."HORA" AND "TF"."ENDTIME"="TMP"."HORA")
16 - filter(("AG"."TIPO"='Crawler' OR "AG"."TIPO"='User'))
|
|
|
|
| Re: Query run faster without indexes. Why? [message #421175 is a reply to message #421029] |
Fri, 04 September 2009 20:01 |
JorgeAbreu
Messages: 10
Registered: September 2009
Location: Braga, Portugal
|
Junior Member |
|
|
Well,
This new explain plans shows a big difference between the number of estimated and actually rows used, in both cases. But as we can see, the query with indexes have much more rows access. Rigth?
Can this be the possible explanation for the reason why the query is slower with indexes than without them?
thanks,
Jorge Abreu
|
|
|
|
Goto Forum:
Current Time: Sat May 18 04:19:20 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
