Forum Stats

  • 3,825,769 Users
  • 2,260,558 Discussions
  • 7,896,671 Comments

Discussions

Query Tuning question : How to avoid FTS on this table ?

2»

Answers

  • C. Boutet
    C. Boutet Member Posts: 121
    edited Nov 7, 2016 4:43AM

    As you're on 9.2 you can enable row source execution statistics so you can actually determine where the time is going - currently you are guessing it's the full table scan

    Hi Andrew,

    Any idea how to generate Row source stats in 9i ? Although GATHER_PLAN_STATISTICS hint seems to be valid , DISPLAY_CURSOR is not present in 9i's DBMS_XPLAN

    SQL> select * from v$version where rownum < 2;BANNER----------------------------------------------------------------Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit ProductionSQL>SQL>SQL>SQL>SQL> select /*+ GATHER_PLAN_STATISTICS */ count(*) from PDTCOST_CHARGE_MAP where rownum < 60 ;  COUNT(*)----------        59SQL> set linesize 1000 pagesize 300SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'allstats last'));SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'allstats last'))                                                    *ERROR at line 1:ORA-00907: missing right parenthesisSQL> desc DBMS_XPLANFUNCTION DISPLAY RETURNS DBMS_XPLAN_TYPE_TABLE Argument Name                  Type                    In/Out Default? ------------------------------ ----------------------- ------ -------- TABLE_NAME                     VARCHAR2                IN     DEFAULT STATEMENT_ID                   VARCHAR2                IN     DEFAULT FORMAT                         VARCHAR2                IN     DEFAULT

    SQL>

  • berx
    berx Member Posts: 219 Silver Badge
    edited Nov 7, 2016 9:35AM

    If you want to emulate DBMS_XPLAN.DISPLAY_CURSOR in 9i, please check this post
    Formatted SQL Plan Display in Oracle 9i | ora-solutions.net - Martin Decker

    (there are similar descriptions out there, this was just my first hit).

    I've seen people complaining about high SGA activity (latches) of such scripts, so please be careful.

    hth

    Martin

    C. BoutetC. BoutetMartin Preiss
  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,974 Blue Diamond
    edited Nov 7, 2016 6:04PM

    Commented on the hash join / 3rd child and general 'or subquery' issue here:  http://jonathanlewis.wordpress.com/2016/11/07/filter-subquery/

    Regards

    Jonathan Lewis

    834dea03-c1a1-4fb6-b221-783c0b356084
This discussion has been closed.