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 Go to next message
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 #421030 is a reply to message #421029] Thu, 03 September 2009 13:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64137
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can read Performances Tuning sticky and follow the links.

Regards
Michel
Re: Query run faster without indexes. Why? [message #421031 is a reply to message #421029] Thu, 03 September 2009 14:35 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
post EXPLAIN PLAN for both; with & without indexes
Re: Query run faster without indexes. Why? [message #421044 is a reply to message #421029] Thu, 03 September 2009 19:15 Go to previous messageGo to next message
JorgeAbreu
Messages: 10
Registered: September 2009
Location: Braga, Portugal
Junior Member
hi BlackSwan,

The explain plans are very large.
When I tried to post them, they appeared unformatted Confused

I put them on a TXT file as an attachment.

Thanks for the help.
One more time, my apolagizes for my English Embarassed

Jorge Abreu
Re: Query run faster without indexes. Why? [message #421045 is a reply to message #421029] Thu, 03 September 2009 19:26 Go to previous messageGo to next message
BlackSwan
Messages: 25046
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 #421046 is a reply to message #421029] Thu, 03 September 2009 19:30 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
I/we don't know which columns belong to which tables.
I/we don't know which columns are indexed, before, during, after.

Are statistics current for all tables along with any & all indexes?
Re: Query run faster without indexes. Why? [message #421049 is a reply to message #421046] Thu, 03 September 2009 22:08 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
You might benefit from these articles:
Understanding Indexes - Tells you how indexes work and why a SQL might be faster without indexes
Tuning High Volume SQL - Explains how to avoid pitfalls and get the best performance when processing large data volumes.

Ross Leishman
Re: Query run faster without indexes. Why? [message #421050 is a reply to message #421029] Thu, 03 September 2009 22:31 Go to previous messageGo to next message
JorgeAbreu
Messages: 10
Registered: September 2009
Location: Braga, Portugal
Junior Member
thanks for the patience Smile

Yes, all the statistics are current for all tables and indexes.

here is all that i did from the beginning Smile

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 #421051 is a reply to message #421029] Thu, 03 September 2009 22:44 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
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' 


You did a GREAT job posting many details, but I am not motivated to stare at your details tonight.

Does every column listed above have an index on it?



Re: Query run faster without indexes. Why? [message #421052 is a reply to message #421029] Thu, 03 September 2009 23:12 Go to previous messageGo to next message
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 #421064 is a reply to message #421052] Fri, 04 September 2009 00:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64137
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Did you read the links we provided you or are you just waiting we'll give you a complete answer?

Regards
Michel

[Updated on: Fri, 04 September 2009 00:53]

Report message to a moderator

Re: Query run faster without indexes. Why? [message #421097 is a reply to message #421052] Fri, 04 September 2009 03:58 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #421171 is a reply to message #421169] Fri, 04 September 2009 14:19 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Could you please go through this link and post the explain plan as described in this. It will help us a lot to understand what it is estimating and what actually it gets. Also turning on the 10046 trace event and do a tkprof on the output will give you the actuals rather than the estimate.

http://structureddata.org/2007/11/21/troubleshooting-bad-execution-plans/

Regards

Raj
Re: Query run faster without indexes. Why? [message #421172 is a reply to message #421029] Fri, 04 September 2009 16:48 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous message
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
Previous Topic: "Database Time Spent Waiting (%)" are at 100 for event class "Concurrency"
Next Topic: Wait event problem
Goto Forum:
  


Current Time: Thu Dec 08 06:24:39 CST 2016

Total time taken to generate the page: 0.09917 seconds