RE: performance on intermedia query with order by, help!!

From: Carlos Hernandez <infoactiva_at_manquehuet.net>
Date: Thu, 4 Jul 2002 12:45:44 -0400
Message-ID: <ag1uff$s9r$1@spiderman.nap.telefonicamundo.cl>

Well, this will be a little extensive....

Thanx for your help

TEMP TABLESPACE
DATAFILE
    'F:\ORACLE\ORADATA\ALERTA\TEMP01.DBF',     'F:\ORACLE\ORADATA\ALERTA\TEMP02.DBF'   DEFAULT STORAGE (INITIAL 10240
    NEXT 10240
    MINEXTENTS 1
    MAXEXTENTS 121
    PCTINCREASE 50) This are both plans for both querys:

QUERY1 (without intermedia): select * from busqueda_articulos where cod_titulo_revista=336 or cod_titulo_revista=2444 order by titulo_articulo

operation                             options
object_name                         optimizer             cost
SELECT STATEMENT
CHOOSE            177
SORT                                 ORDER BY

177
INLIST ITERATOR
TABLE ACCESS BY INDEX ROWID BUSQUEDA_ARTICULOS ANALYZED 96

INDEX                             RANGE SCAN         INDEX_BA1
ANALYZED         7

QUERY2 (with intermedia): select * from busqueda_articulos where contains(titulo_articulo,'study')>0 order by titulo_articulo

operation                             options
object_name                         optimizer             cost
SELECT STATEMENT
CHOOSE         4361
SORT                                 ORDER BY
4361
TABLE ACCESS                 BY INDEX ROWID     BUSQUEDA_ARTICULOS ANALYZED

3014
DOMAIN INDEX

INDEX_TITULOS_BA                                  72

The trace results are as follows:

• START QUERY WITHOUT INTERMEDIA******************** alter session set sql_trace=true

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ------

---

Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ------

---

total 1 0.00 0.00 0 0 0 0

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 40

---

---

select * from busqueda_articulos
where cod_titulo_revista=336 or cod_titulo_revista=2444 order by titulo_articulo

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ------

---

Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 12 0.00 0.00 0 1862 0 23
------- ------ -------- ---------- ---------- ---------- ---------- ------

---

total 14 0.00 0.00 0 1862 0 23

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 40

Rows Row Source Operation
------- ---------------------------------------------------

     23 SORT ORDER BY
  10022 INLIST ITERATOR
  10022 TABLE ACCESS BY INDEX ROWID BUSQUEDA_ARTICULOS   10024 INDEX RANGE SCAN (object id 14848)

---

---

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ------

---

Parse 1 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 12 0.00 0.00 0 1862 0 23
------- ------ -------- ---------- ---------- ---------- ---------- ------

---

total 15 0.00 0.00 0 1862 0 23

Misses in library cache during parse: 1

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ------

---

Parse 0 0.00 0.00 0 0 0 0
Execute 0 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ------

---

total 0 0.00 0.00 0 0 0 0

Misses in library cache during parse: 0

    2 user SQL statements in session.
    0 internal SQL statements in session.     2 SQL statements in session.

---

---

Trace file: ora00508.trc
Trace file compatibility: 7.03.02
Sort options: default

       1  session in tracefile.
       2  user  SQL statements in trace file.
       0  internal SQL statements in trace file.
       2  SQL statements in trace file.
       2  unique SQL statements in trace file.
      54  lines in trace file.
******************    END QUERY WITHOUT INTERMEDIA********************



******************    START QUERY WITH INTERMEDIA********************

alter session set sql_trace=true

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ------

---

Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ------

---

total 1 0.00 0.00 0 0 0 0

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 40

---

---

select u.name, o.name
from

 association$ a, user$ u, obj$ o                     where a.obj# = :1
                                    and a.property = :2
                 and a.statstype# = o.obj#

and
  u.user# = o.owner#

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ------

---

Parse 4 0.00 0.00 0 0 0 0
Execute 4 0.00 0.00 0 0 0 0
Fetch 4 0.00 0.00 0 22 0 3
------- ------ -------- ---------- ---------- ---------- ---------- ------

---

total 12 0.00 0.00 0 22 0 3

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)

---

---

  declare

     sel number;
     dummy varchar2(1);

     out_args exception;
     pragma exception_init(out_args, -6572);
    begin
      begin
        -- check ODCIStatsSelectivity pragma
        select "CTXSYS"."TEXTOPTSTATS".ODCIStatsSelectivity(
                        sys.ODCIPREDINFO('CTXSYS',
                               'CTX_CONTAINS',
                               'TEXTCONTAINS',
                               32),
                        sel,
                        sys.ODCIArgDescList(sys.ODCIArgDesc(3, NULL, NULL,

NULL), sys.ODCIArgDesc(5, NULL, NULL, NULL), sys.ODCIArgDesc(2, 'BUSQUEDA_ARTICULOS', 'ALERTA_TEMP', '"TITULO_ARTICULO"'), sys.ODCIArgDesc(3, NULL, NULL, NULL)),

                        0,
                        NULL
                        , NULL, 'study') into dummy
        from sys.dual;
        exception
          when out_args then
            NULL;
          when others then
            raise;
      end;
      :1 := "CTXSYS"."TEXTOPTSTATS".ODCIStatsSelectivity(
                     sys.ODCIPREDINFO('CTXSYS',
                            'CTX_CONTAINS',
                            'TEXTCONTAINS',
                            32),
                     sel,
                     sys.ODCIArgDescList(sys.ODCIArgDesc(3, NULL, NULL,

NULL), sys.ODCIArgDesc(5, NULL, NULL, NULL), sys.ODCIArgDesc(2, 'BUSQUEDA_ARTICULOS', 'ALERTA_TEMP', '"TITULO_ARTICULO"'), sys.ODCIArgDesc(3, NULL, NULL, NULL)),

                     0,
                     NULL
                     , NULL, 'study');
      if sel IS NULL then
        :2 := -1;
      else
        :2 := sel;
      end if;

    end;

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ------

---

Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 6 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ------

---

total 2 0.00 0.00 0 6 0 1

Misses in library cache during parse: 1
Misses in library cache during execute: 1 Optimizer goal: CHOOSE
Parsing user id: 40 (recursive depth: 1)

---

---

SELECT
"CTXSYS"."TEXTOPTSTATS".ODCISTATSSELECTIVITY(SYS.ODCIPREDINFO('CTXSYS', 'CTX_CONTAINS','TEXTCONTAINS',32),:b1,SYS.ODCIARGDESCLIST(SYS.ODCIARGDESC(3,

   NULL , NULL , NULL ),SYS.ODCIARGDESC(5, NULL , NULL , NULL ),   SYS.ODCIARGDESC(2,'BUSQUEDA_ARTICULOS','ALERTA_TEMP','"TITULO_ARTICULO"'),   SYS.ODCIARGDESC(3, NULL , NULL , NULL )),0, NULL , NULL ,'study') FROM
 SYS.DUAL call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ------

---

Parse 1 0.00 0.00 0 0 0 0
Execute 0 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ------

---

total 1 0.00 0.00 0 0 0 0

Misses in library cache during parse: 1
Parsing user id: 40 (recursive depth: 2)

---

---

select /*+ ORDERED */ i.idx_name, u2.name from
 sys.user$ u, sys.obj$ o, ctxsys.dr$index i, sys.user$ u2 where i.idx_owner#

• u2.user# and i.idx_text_name = 'TITULO_ARTICULO' and i.idx_table# = o.obj# and o.type# = 2 and o.name = 'BUSQUEDA_ARTICULOS' and o.owner# = u.user# and u.name = 'ALERTA_TEMP'

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ------

---

Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 18 8 2
------- ------ -------- ---------- ---------- ---------- ---------- ------

---

total 6 0.00 0.00 0 18 8 2

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 35 (recursive depth: 2)

---

---

select a.default_cpu_cost, a.default_io_cost from

 association$ a                                 where a.obj# = :1
                          and a.property = :2


call     count       cpu    elapsed       disk      query    current

rows
------- ------ -------- ---------- ---------- ---------- ---------- ------

---

Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 1 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ------

---

total 3 0.00 0.00 0 1 0 0

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)

---

---

  declare

     cost sys.ODCICost := sys.ODCICost(NULL, NULL, NULL);
     dummy varchar2(1);

     out_args exception;
     pragma exception_init(out_args, -6572);
    begin
      begin
        -- check ODCIStatsIndexCost pragma
        select "CTXSYS"."TEXTOPTSTATS".ODCIStatsIndexCost(
                        sys.ODCIINDEXINFO('ALERTA_TEMP',
                               'INDEX_TITULOS_BA',

sys.ODCICOLINFOLIST(sys.ODCICOLINFO('ALERTA_TEMP', 'BUSQUEDA_ARTICULOS', '"TITULO_ARTICULO"', 'VARCHAR2', NULL))),

                        2,
                        cost,
                        sys.ODCIQUERYINFO(0,
                               NULL),
                        sys.ODCIPREDINFO('CTXSYS',
                               'CONTAINS',
                               NULL,
                               0),
                        sys.ODCIArgDescList(sys.ODCIArgDesc(3, NULL, NULL,

NULL), sys.ODCIArgDesc(5, NULL, NULL, NULL), sys.ODCIArgDesc(3, NULL, NULL, NULL)),

                        0,
                        NULL
                        , 'study') into dummy
        from sys.dual;
        exception
          when out_args then
            NULL;
          when others then
            raise;
      end;
      :1 := "CTXSYS"."TEXTOPTSTATS".ODCIStatsIndexCost(
                     sys.ODCIINDEXINFO('ALERTA_TEMP',
                            'INDEX_TITULOS_BA',

sys.ODCICOLINFOLIST(sys.ODCICOLINFO('ALERTA_TEMP', 'BUSQUEDA_ARTICULOS', '"TITULO_ARTICULO"', 'VARCHAR2', NULL))),

                     2,
                     cost,
                     sys.ODCIQUERYINFO(0,
                            NULL),
                     sys.ODCIPREDINFO('CTXSYS',
                            'CONTAINS',
                            NULL,
                            0),
                     sys.ODCIArgDescList(sys.ODCIArgDesc(3, NULL, NULL,

NULL), sys.ODCIArgDesc(5, NULL, NULL, NULL), sys.ODCIArgDesc(3, NULL, NULL, NULL)),

                     0,
                     NULL
                     , 'study');
      if cost.CPUCost IS NULL then
        :2 := -1;
      else
        :2 := cost.CPUCost;
      end if;
      if cost.IOCost IS NULL then
        :3 := -1;
      else
        :3 := cost.IOCost;
      end if;
      if cost.NetworkCost IS NULL then
        :4 := -1;
      else
        :4 := cost.NetworkCost;
      end if;

    end;

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ------

---

Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 6 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ------

---

total 2 0.00 0.00 0 6 0 1

Misses in library cache during parse: 1
Misses in library cache during execute: 1 Optimizer goal: CHOOSE
Parsing user id: 40 (recursive depth: 1)

---

---

SELECT
"CTXSYS"."TEXTOPTSTATS".ODCISTATSINDEXCOST(SYS.ODCIINDEXINFO('ALERTA_T   EMP','INDEX_TITULOS_BA',SYS.ODCICOLINFOLIST(SYS.ODCICOLINFO('ALERTA_TEMP',   'BUSQUEDA_ARTICULOS','"TITULO_ARTICULO"','VARCHAR2', NULL ))),2,:b1,   SYS.ODCIQUERYINFO(0, NULL ),SYS.ODCIPREDINFO('CTXSYS','CONTAINS', NULL ,0),
  SYS.ODCIARGDESCLIST(SYS.ODCIARGDESC(3, NULL , NULL , NULL ),   SYS.ODCIARGDESC(5, NULL , NULL , NULL ),SYS.ODCIARGDESC(3, NULL , NULL ,   NULL )),0, NULL ,'study')
FROM
 SYS.DUAL call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ------

---

Parse 1 0.00 0.00 0 0 0 0
Execute 0 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ------

---

total 1 0.00 0.00 0 0 0 0

Misses in library cache during parse: 1
Parsing user id: 40 (recursive depth: 2)

---

---

  declare

     cost sys.ODCICost := sys.ODCICost(NULL, NULL, NULL);
     dummy varchar2(1);

     out_args exception;
     pragma exception_init(out_args, -6572);
    begin
      begin
        -- check ODCIStatsFunctionCost pragma
        select "CTXSYS"."TEXTOPTSTATS".ODCIStatsFunctionCost(
                        sys.ODCIFuncInfo('CTXSYS',
                               'CTX_CONTAINS',
                               'TEXTCONTAINS',
                               2),
                        cost,
                        sys.ODCIArgDescList(sys.ODCIArgDesc(2,

'BUSQUEDA_ARTICULOS', 'ALERTA_TEMP', '"TITULO_ARTICULO"'), sys.ODCIArgDesc(3, NULL, NULL, NULL))

                        , NULL, 'study') into dummy
        from sys.dual;
        exception
          when out_args then
            NULL;
          when others then
            raise;
      end;
      :1 := "CTXSYS"."TEXTOPTSTATS".ODCIStatsFunctionCost(
                     sys.ODCIFuncInfo('CTXSYS',
                            'CTX_CONTAINS',
                            'TEXTCONTAINS',
                            2),
                     cost,
                     sys.ODCIArgDescList(sys.ODCIArgDesc(2,

'BUSQUEDA_ARTICULOS', 'ALERTA_TEMP', '"TITULO_ARTICULO"'), sys.ODCIArgDesc(3, NULL, NULL, NULL))

                     , NULL, 'study');
      if cost.CPUCost IS NULL then
        :2 := -1;
      else
        :2 := cost.CPUCost;
      end if;
      if cost.IOCost IS NULL then
        :3 := -1;
      else
        :3 := cost.IOCost;
      end if;
      if cost.NetworkCost IS NULL then
        :4 := -1;
      else
        :4 := cost.NetworkCost;
      end if;

    end;

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ------

---

Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 3 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ------

---

total 2 0.00 0.00 0 3 0 1

Misses in library cache during parse: 1
Misses in library cache during execute: 1 Optimizer goal: CHOOSE
Parsing user id: 40 (recursive depth: 1)

---

---

SELECT
"CTXSYS"."TEXTOPTSTATS".ODCISTATSFUNCTIONCOST(SYS.ODCIFUNCINFO('CTXSYS   ','CTX_CONTAINS','TEXTCONTAINS',2),:b1,   SYS.ODCIARGDESCLIST(SYS.ODCIARGDESC(2,'BUSQUEDA_ARTICULOS','ALERTA_TEMP',   '"TITULO_ARTICULO"'),SYS.ODCIARGDESC(3, NULL , NULL , NULL )), NULL ,   'study')
FROM
 SYS.DUAL call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ------

---

Parse 1 0.00 0.00 0 0 0 0
Execute 0 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ------

---

total 1 0.00 0.00 0 0 0 0

Misses in library cache during parse: 1
Parsing user id: 40 (recursive depth: 2)

---

---

select * from busqueda_articulos
where contains(titulo_articulo,'study')>0 order by titulo_articulo

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ------

---

Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 12 0.00 0.00 13773 17626 0 23
------- ------ -------- ---------- ---------- ---------- ---------- ------

---

total 14 0.00 0.00 13773 17626 0 23

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 40

Rows Row Source Operation
------- ---------------------------------------------------

     23 SORT ORDER BY
  12039 TABLE ACCESS BY INDEX ROWID BUSQUEDA_ARTICULOS   12040 DOMAIN INDEX

---

---

BEGIN :p := CTX_QUERY.PREFERENCE; :t := CTX_QUERY.WS_THRESHOLD;END;

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ------

---

Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ------

---

total 2 0.00 0.00 0 0 0 1

Misses in library cache during parse: 1
Misses in library cache during execute: 1 Optimizer goal: CHOOSE
Parsing user id: 35 (recursive depth: 1)

---

---

SELECT IDX_DOCID_COUNT
FROM
 DR$INDEX WHERE IDX_ID = :idxid

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ------

---

Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 2 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ------

---

total 3 0.00 0.00 0 2 0 1

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 35 (recursive depth: 1)

---

---

SELECT TOKEN_FIRST,TOKEN_LAST,TOKEN_COUNT,TOKEN_INFO FROM
 "ALERTA_TEMP"."DR$INDEX_TITULOS_BA$I" WHERE TOKEN_TEXT = :word AND   TOKEN_TYPE = :wtype ORDER BY TOKEN_TEXT, TOKEN_TYPE, TOKEN_FIRST

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ------

---

Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 7 0.00 0.00 0 26 0 12
------- ------ -------- ---------- ---------- ---------- ---------- ------

---

total 9 0.00 0.00 0 26 0 12

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 35 (recursive depth: 1)

---

---

select data
from
 "ALERTA_TEMP"."DR$INDEX_TITULOS_BA$R" where row_no = :row_no

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ------

---

Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 1 4 1
------- ------ -------- ---------- ---------- ---------- ---------- ------

---

total 3 0.00 0.00 0 1 4 1

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 40 (recursive depth: 1)

---

---

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ------

---

Parse 1 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 12 0.00 0.00 13773 17626 0 23
------- ------ -------- ---------- ---------- ---------- ---------- ------

---

total 15 0.00 0.00 13773 17626 0 23

Misses in library cache during parse: 1

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ------

---

Parse 17 0.00 0.00 0 0 0 0
Execute 14 0.00 0.00 0 15 0 4
Fetch 16 0.00 0.00 0 70 12 19
------- ------ -------- ---------- ---------- ---------- ---------- ------

---

total 47 0.00 0.00 0 85 12 23

Misses in library cache during parse: 7
Misses in library cache during execute: 4

   14 user SQL statements in session.
    5 internal SQL statements in session.    19 SQL statements in session.

---

---

Trace file: ora00495.trc
Trace file compatibility: 7.03.02
Sort options: default

       1  session in tracefile.
      14  user  SQL statements in trace file.
       5  internal SQL statements in trace file.
      19  SQL statements in trace file.
      15  unique SQL statements in trace file.
     379  lines in trace file.

******************    END QUERY WITH INTERMEDIA********************

kopek <matezuka_at_yahoo.com> escribió en el mensaje de noticias f7cb1d69.0207040003.5d19c572_at_posting.google.com...

> Do you have the tkprof output from the the SQL statements that you
> ran? What plan did you get for these statements? Should be interesting
> to see. BTW, how is your temp tablespace configured? What are the
> extent sizes?

Received on Thu Jul 04 2002 - 11:45:44 CDT