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

C. Boutet

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

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

Jonathan Lewis

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