Home » RDBMS Server » Performance Tuning » Query doing FTS on a big table (Oracle 10g 10.2.0.4.0 on AIX 5.3)
Query doing FTS on a big table [message #513199] Fri, 24 June 2011 06:04 Go to next message
raj9999
Messages: 49
Registered: June 2011
Member
Hi Experts,

Below query is taking a long time...

select gam.SOL_ID,COUNT(gam.FORACID) from gam,smt where
gam.ACID=smt.ACID and gam.ACID NOT IN(select ACID from imt) and
gam.SCHM_TYPE in('SBA','CCA','CAA','ODA') and GAM.ACCT_CLS_FLG='N' and
gam.SOL_ID IN(select SOL_ID from IMT) group by gam.SOL_ID
/

attached is the explain plan.

in which index on IMT table is not used. And the query is doing a FTS on IMT table. What needs to be done to avoid FTS on IMT table.

Report message to a moderator

Re: Query doing FTS on a big table [message #513203 is a reply to message #513199] Fri, 24 June 2011 06:11 Go to previous messageGo to next message
Michel Cadot
Messages: 68501
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Many of us can't or don't want to download files, so post them inline and post them formatted.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Please read http://www.orafaq.com/forum/mv/msg/84315/433888/102589/#msg_433888 and post the required information.

Regards
Michel

Report message to a moderator

Re: Query doing FTS on a big table [message #513209 is a reply to message #513203] Fri, 24 June 2011 06:54 Go to previous messageGo to next message
raj9999
Messages: 49
Registered: June 2011
Member
below is the explain plan

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1895877274

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                          |    69 |  3588 |    91G  (1)|999:59:59 |
|   1 |  SORT GROUP BY                   |                          |    69 |  3588 |    91G  (1)|999:59:59 |
|*  2 |   FILTER                         |                          |       |       |            |          |
|   3 |    NESTED LOOPS                  |                          |   476K|    23M|  7249   (1)| 00:01:27 |
|   4 |     NESTED LOOPS                 |                          |   474K|    19M|  7245   (1)| 00:01:27 |
|   5 |      SORT UNIQUE                 |                          |   296K|  1446K|   161   (1)| 00:00:02 |
|   6 |       INDEX FULL SCAN            | IDX_IMT_ACCT_ID          |   296K|  1446K|   161   (1)| 00:00:02 |
|   7 |      INLIST ITERATOR             |                          |       |       |            |          |
|*  8 |       TABLE ACCESS BY INDEX ROWID| GENERAL_ACCT_MAST_TABLE  |  6882 |   248K|    89   (0)| 00:00:02 |
|*  9 |        INDEX RANGE SCAN          | IDX_GAM_SCHM_TYPE_SOL_ID |  5076 |       |     1   (0)| 00:00:01 |
|* 10 |     INDEX UNIQUE SCAN            | IDX_SBCA_MAST_TABLE      |     1 |    10 |     1   (0)| 00:00:01 |
|* 11 |    TABLE ACCESS FULL             | IMAGE_TABLE              |     1 |    10 |   195K  (1)| 00:39:03 |
-------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM "TBAADM"."IMAGE_TABLE" "IMT" WHERE
              LNNVL("ACID"<>:B1)))
   8 - filter("GAM"."ACCT_CLS_FLG"='N')
   9 - access(("GAM"."SCHM_TYPE"='CAA' OR "GAM"."SCHM_TYPE"='CCA' OR "GAM"."SCHM_TYPE"='ODA' OR
              "GAM"."SCHM_TYPE"='SBA') AND "GAM"."SOL_ID"="SOL_ID")
  10 - access("GAM"."ACID"="SMT"."ACID")
  11 - filter(LNNVL("ACID"<>:B1))

29 rows selected.

Report message to a moderator

Re: Query doing FTS on a big table [message #513224 is a reply to message #513209] Fri, 24 June 2011 09:01 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Forgive me but posted SQL did not result in posted PLAN since the TABLE_NAME do not match
SELECT gam.sol_id,
       COUNT(gam.foracid)
FROM   gam,
       smt
WHERE  gam.acid = smt.acid
       AND gam.acid NOT IN(SELECT acid
                           FROM   imt)
       AND gam.schm_type IN( 'SBA', 'CCA', 'CAA', 'ODA' )
       AND gam.acct_cls_flg = 'N'
       AND gam.sol_id IN(SELECT sol_id
                         FROM   imt)
GROUP  BY gam.sol_id

/

FTS occurs on IMT since no WHERE clause exists

Report message to a moderator

Re: Query doing FTS on a big table [message #513253 is a reply to message #513224] Sat, 25 June 2011 01:16 Go to previous messageGo to next message
raj9999
Messages: 49
Registered: June 2011
Member
IMT table is IMAGE_TABLE. on which FTS is being done.
How to avoid that? we can make change in the query but output should be same.
Kindly suggest.

Report message to a moderator

Re: Query doing FTS on a big table [message #513261 is a reply to message #513253] Sat, 25 June 2011 03:44 Go to previous messageGo to next message
cookiemonster
Messages: 13904
Registered: September 2008
Location: Rainy Manchester
Senior Member
An index on sol_id may help.

Report message to a moderator

Re: Query doing FTS on a big table [message #513378 is a reply to message #513261] Mon, 27 June 2011 02:59 Go to previous messageGo to next message
raj9999
Messages: 49
Registered: June 2011
Member
Index on sol_id is already there.

Report message to a moderator

Re: Query doing FTS on a big table [message #513380 is a reply to message #513378] Mon, 27 June 2011 03:07 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
On it's own or part of a composite? B-Tree or bitmap? Are all stats up to date? Is the table big enough to merit index lookup?

Report message to a moderator

Re: Query doing FTS on a big table [message #513383 is a reply to message #513380] Mon, 27 June 2011 03:40 Go to previous messageGo to next message
cookiemonster
Messages: 13904
Registered: September 2008
Location: Rainy Manchester
Senior Member
To save further guessing games we need more information.
Please post the DDL for each table (plus the synonyms you are apparently using).
Post a complete list of indexes for each table.
Tell us how many rows are in each table and how many of those rows match the where clause of your query.

Report message to a moderator

Re: Query doing FTS on a big table [message #514162 is a reply to message #513383] Sat, 02 July 2011 00:30 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
Also provide rowcounts of each table and if possible the filtered rowcounts from each table. If these are large tables then the presence of NESTED LOOP everywhere suggests that your statistics are not collected or are collected incorrectly.

Kevin

Report message to a moderator

Re: Query doing FTS on a big table [message #514520 is a reply to message #514162] Tue, 05 July 2011 04:46 Go to previous message
michael_bialik
Messages: 621
Registered: July 2006
Senior Member
Do you have an index on ACID columns defined for IMAGE_TABLE?

Report message to a moderator

Previous Topic: problem with outlines
Next Topic: Typical Question of Query Performance
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Mon Sep 25 01:26:26 CDT 2023
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.