Re: stupid question on FTS
- From: Vasu <vasudevanr@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Fri, 24 Aug 2012 15:16:00 -0500
Not sure of the formatting.. (also attaching this as text file). Query
plan output , that I tried today.
select /* +USE_NL_WITH_INDEX(b PK_GBL_BESP_BESID) parallel(b 12)*/
min(gmtExpireTime),max(gmtExpireTime) ,count(*)
from pt_bes_migrated a, SESS_DETAIL b, SESS_MAST c
where c.eventid = b.f_event_id
and a.SESS_DETAIL_ID = b.SESS_DETAIL_ID
and not exists (select 1 from pt_mdsess_tracker d where
d.sessionid=a.SESS_DETAIL_ID )
-----------------------
SESS_MAST - 57 mil. rows MASTER
SESS_DETAIL - 133 mil. rows DETAIL
PT_BES_MIGRATED - 13 mil. rows WORK-TABLE - having result sessions
that occurred at a certain period .
PT_MDSESS_TRACKER - 1 mil. rows work-table
This is a post-validation query (just a one-time query to verify data
migration ).
PT_BES_MIGRATED is a result of data extraction , that has 10% of SESSION
records from SESS_DETAIL, from there its JOINED to SESS_MAST (parent) to
see the min,max period covered.
SESS_MAST is RANGE-RANGE (sub)partioned (12 sub-partitions)
SESS_DETAIL is REFERENCE partitioned
NOT TO WORRY.. the JOINS make use of PK/UK Global INdexes, so NO scope for
partition-pruning (so it works much like a regular table for our query).
I expected the FTS in line 8, in the query plan would go away.., that never
happened.
I started with some hints, fired these queries and captured the cached
plan. stopped the query after 10 minutes, re-tried again .
Finally, the last query finished in 11 minutes , I didn't see if parallel
jobs really kicked in. Will try again.
----------------- INITIAL TABLE STATS
-------------------------------------------------------------------------------
select table_name,num_rows,last_analyzed
from user_tables
where table_name in ('PT_BES_MIGRATED','PT_MDSESS_TRACKER','SESS_MAST
','SESS_DETAIL ')
-------===============================================----------------------
1 SESS_MAST 33391053 8/22/2012 5:36:38 PM
2 PT_BES_MIGRATED
3 PT_MDSESS_TRACKER
4 SESS_DETAIL 37064760 8/22/2012 5:28:32 PM
SQL_ID 290v8f05tjd5m, child number 0
-------------------------------------
select /* + index(b,IX_GBL_BESP_BEID) ordered use_NL(b) */
min(gmtExpireTime),max(gmtExpireTime) ,count(*) from pt_bes_migrated
a, SESS_DETAIL b, SESS_MAST c where c.eventid =
b.f_event_id and a.SESS_DETAIL_ID =
b.SESS_DETAIL_ID and not exists (select 1 from
pt_mdsess_tracker d where d.sessionid=a.SESS_DETAIL_ID )
Plan hash value: 360214183
-------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows |
Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| | | 715K(100)| | | |
| 1 | SORT AGGREGATE | | 1 |
55 | | | | | |
|* 2 | HASH JOIN | | 17M|
897M| 848M| 715K (2)| 02:23:08 | | |
|* 3 | HASH JOIN | | 17M|
652M| 620M| 314K (2)| 01:03:00 | | |
|* 4 | HASH JOIN RIGHT ANTI | | 17M|
424M| 26M| 35434 (2)| 00:07:06 | | |
| 5 | INDEX FAST FULL SCAN | IX_PTMDSESSTRK_SESSIONID | 1117K|
13M| | 830 (1)| 00:00:10 | | |
| 6 | INDEX FAST FULL SCAN | IX_BESMIGR_SESSID | 18M|
226M| | 11270 (1)| 00:02:16 | | |
| 7 | PARTITION REFERENCE ALL| | 37M|
494M| | 201K (2)| 00:40:24 | 1 | 351 |
| 8 | TABLE ACCESS FULL | SESS_DETAIL | 37M|
494M| | 201K (2)| 00:40:24 | 1 | 351 |
| 9 | VIEW | index$_join$_003 | 33M|
477M| | 314K (2)| 01:02:59 | | |
|* 10 | HASH JOIN | |
| | | | | | |
| 11 | PARTITION RANGE ALL | | 33M|
477M| | 118K (1)| 00:23:37 | 1 | 27 |
| 12 | PARTITION RANGE ALL | | 33M|
477M| | 118K (1)| 00:23:37 | 1 | 13 |
| 13 | INDEX FAST FULL SCAN| IX_BE_GMTEXPTM | 33M|
477M| | 118K (1)| 00:23:37 | 1 | 351 |
| 14 | PARTITION HASH ALL | | 33M|
477M| | 157K (1)| 00:31:28 | 1 | 32 |
| 15 | INDEX FAST FULL SCAN | PK_GLB_BE_EVENTID | 33M|
477M| | 157K (1)| 00:31:28 | 1 | 32 |
-------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C"."EVENTID"="B"."F_EVENT_ID")
3 - access("A"."SESS_DETAIL_ID
"="B"."SESS_DETAIL_ID ")
4 - access("D"."SESSIONID"="A"."SESS_DETAIL_ID ")
10 - access(ROWID=ROWID)
Note
-----
- dynamic sampling used for this statement (level=2)
------------------------------------------------------------------------------------------------------------------
------------------- optimizer_dynamic_sampling set to 9
----------------------------------------------------------
Plan hash value: 360214183
-------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows |
Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| | | 691K(100)| | | |
| 1 | SORT AGGREGATE | | 1 |
55 | | | | | |
|* 2 | HASH JOIN | | 12M|
664M| 628M| 691K (2)| 02:18:15 | | |
|* 3 | HASH JOIN | | 12M|
483M| 459M| 301K (2)| 01:00:19 | | |
|* 4 | HASH JOIN RIGHT ANTI | | 12M|
314M| 27M| 30121 (2)| 00:06:02 | | |
| 5 | INDEX FAST FULL SCAN | IX_PTMDSESSTRK_SESSIONID | 1140K|
14M| | 830 (1)| 00:00:10 | | |
| 6 | INDEX FAST FULL SCAN | IX_BESMIGR_SESSID | 13M|
171M| | 11270 (1)| 00:02:16 | | |
| 7 | PARTITION REFERENCE ALL| | 37M|
494M| | 201K (2)| 00:40:24 | 1 | 351 |
| 8 | TABLE ACCESS FULL | SESS_DETAIL | 37M|
494M| | 201K (2)| 00:40:24 | 1 | 351 |
| 9 | VIEW | index$_join$_003 | 33M|
477M| | 314K (2)| 01:02:59 | | |
|* 10 | HASH JOIN | |
| | | | | | |
| 11 | PARTITION RANGE ALL | | 33M|
477M| | 118K (1)| 00:23:37 | 1 | 27 |
| 12 | PARTITION RANGE ALL | | 33M|
477M| | 118K (1)| 00:23:37 | 1 | 13 |
| 13 | INDEX FAST FULL SCAN| IX_BE_GMTEXPTM | 33M|
477M| | 118K (1)| 00:23:37 | 1 | 351 |
| 14 | PARTITION HASH ALL | | 33M|
477M| | 157K (1)| 00:31:28 | 1 | 32 |
| 15 | INDEX FAST FULL SCAN | PK_GLB_BE_EVENTID | 33M|
477M| | 157K (1)| 00:31:28 | 1 | 32 |
-------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C"."EVENTID"="B"."F_EVENT_ID")
3 - access("A"."SESS_DETAIL_ID
"="B"."SESS_DETAIL_ID ")
4 - access("D"."SESSIONID"="A"."SESS_DETAIL_ID ")
10 - access(ROWID=ROWID)
Note
-----
- dynamic sampling used for this statement (level=9)
------------------------------ trying RULE hint
-------------------------------------------------------------------------------
select /* +RULE ordered USE_NL_WITH_INDEX(b PK_GBL_BESP_BESID) */
min(gmtExpireTime),max(gmtExpireTime) ,count(*) from pt_bes_migrated
a, SESS_DETAIL b, SESS_MAST c where c.eventid =
b.f_event_id and a.SESS_DETAIL_ID =
b.SESS_DETAIL_ID and not exists (select 1 from
pt_mdsess_tracker d where d.sessionid=a.SESS_DETAIL_ID )
Plan hash value: 360214183
-------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows |
Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| | | 1640K(100)| | | |
| 1 | SORT AGGREGATE | | 1 |
55 | | | | | |
|* 2 | HASH JOIN | | 12M|
664M| 628M| 1640K (2)| 05:28:10 | | |
|* 3 | HASH JOIN | | 12M|
483M| 459M| 948K (2)| 03:09:41 | | |
|* 4 | HASH JOIN RIGHT ANTI | | 12M|
314M| 27M| 30121 (2)| 00:06:02 | | |
| 5 | INDEX FAST FULL SCAN | IX_PTMDSESSTRK_SESSIONID | 1140K|
14M| | 830 (1)| 00:00:10 | | |
| 6 | INDEX FAST FULL SCAN | IX_BESMIGR_SESSID | 13M|
171M| | 11270 (1)| 00:02:16 | | |
| 7 | PARTITION REFERENCE ALL| | 133M|
1776M| | 728K (2)| 02:25:42 | 1 | 351 |
| 8 | TABLE ACCESS FULL | SESS_DETAIL | 133M|
1776M| | 728K (2)| 02:25:42 | 1 | 351 |
| 9 | VIEW | index$_join$_003 | 57M|
821M| | 586K (1)| 01:57:17 | | |
|* 10 | HASH JOIN | |
| | | | | | |
| 11 | PARTITION RANGE ALL | | 57M|
821M| | 242K (1)| 00:48:32 | 1 | 27 |
| 12 | PARTITION RANGE ALL | | 57M|
821M| | 242K (1)| 00:48:32 | 1 | 13 |
| 13 | INDEX FAST FULL SCAN| IX_BE_GMTEXPTM | 57M|
821M| | 242K (1)| 00:48:32 | 1 | 351 |
| 14 | PARTITION HASH ALL | | 57M|
821M| | 286K (1)| 00:57:24 | 1 | 32 |
| 15 | INDEX FAST FULL SCAN | PK_GLB_BE_EVENTID | 57M|
821M| | 286K (1)| 00:57:24 | 1 | 32 |
-------------------------------------------------------------------------------------------------------------------------------
2 - access("C"."EVENTID"="B"."F_EVENT_ID")
3 - access("A"."SESS_DETAIL_ID
"="B"."SESS_DETAIL_ID ")
4 - access("D"."SESSIONID"="A"."SESS_DETAIL_ID ")
10 - access(ROWID=ROWID)
------------------------------- POST - STATISTICS GATHERING
--------------------------------------
select table_name,num_rows,last_analyzed
from user_tables
where table_name in ('PT_BES_MIGRATED','PT_MDSESS_TRACKER','SESS_MAST
','SESS_DETAIL ')
1 SESS_MAST 57438138 8/24/2012 6:59:13 PM
2 PT_BES_MIGRATED 13546877 8/24/2012 7:26:32 PM
3 PT_MDSESS_TRACKER 1135851 8/24/2012 7:27:01 PM
4 SESS_DETAIL 133056772 8/24/2012 6:42:55 PM
select /* +first_rows USE_NL_WITH_INDEX(b PK_GBL_BESP_BESID) parallel(b
12)*/ min(gmtExpireTime),max(gmtExpireTime) ,count(*) from
pt_bes_migrated a, SESS_DETAIL b, SESS_MAST c where
c.eventid = b.f_event_id and a.SESS_DETAIL_ID =
b.SESS_DETAIL_ID and not exists (select 1 from
pt_mdsess_tracker d where d.sessionid=a.SESS_DETAIL_ID )
Plan hash value: 360214183
-------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows |
Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| | | 1621K(100)| | | |
| 1 | SORT AGGREGATE | | 1 |
43 | | | | | |
|* 2 | HASH JOIN | | 12M|
509M| 473M| 1621K (2)| 05:24:18 | | |
|* 3 | HASH JOIN | | 12M|
331M| 307M| 936K (2)| 03:07:21 | | |
|* 4 | HASH JOIN RIGHT ANTI | | 12M|
165M| 20M| 25991 (3)| 00:05:12 | | |
| 5 | INDEX FAST FULL SCAN | IX_PTMDSESSTRK_SESSIONID | 1135K|
7764K| | 823 (3)| 00:00:10 | | |
| 6 | INDEX FAST FULL SCAN | IX_BESMIGR_SESSID | 13M|
90M| | 11632 (3)| 00:02:20 | | |
| 7 | PARTITION REFERENCE ALL| | 133M|
1776M| | 728K (2)| 02:25:42 | 1 | 351 |
| 8 | TABLE ACCESS FULL | SESS_DETAIL | 133M|
1776M| | 728K (2)| 02:25:42 | 1 | 351 |
| 9 | VIEW | index$_join$_003 | 57M|
821M| | 586K (1)| 01:57:17 | | |
|* 10 | HASH JOIN | |
| | | | | | |
| 11 | PARTITION RANGE ALL | | 57M|
821M| | 242K (1)| 00:48:32 | 1 | 27 |
| 12 | PARTITION RANGE ALL | | 57M|
821M| | 242K (1)| 00:48:32 | 1 | 13 |
| 13 | INDEX FAST FULL SCAN| IX_BE_GMTEXPTM | 57M|
821M| | 242K (1)| 00:48:32 | 1 | 351 |
| 14 | PARTITION HASH ALL | | 57M|
821M| | 286K (1)| 00:57:24 | 1 | 32 |
| 15 | INDEX FAST FULL SCAN | PK_GLB_BE_EVENTID | 57M|
821M| | 286K (1)| 00:57:24 | 1 | 32 |
-------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C"."EVENTID"="B"."F_EVENT_ID")
3 - access("A"."SESS_DETAIL_ID
"="B"."SESS_DETAIL_ID ")
4 - access("D"."SESSIONID"="A"."SESS_DETAIL_ID ")
10 - access(ROWID=ROWID)
-------------------------------------------------------------------------------------------------------------------
On Fri, Aug 24, 2012 at 8:30 AM, Vasu <vasudevanr@xxxxxxxxx> wrote:
> I think I am in a similar situation , as the stats became stale after
> adding 50% more rows to the table .
>
> I got the stats done by referring to the article by Doug.. and the list of
> useful pointers are here.
> http://jonathanlewis.wordpress.com/2010/03/17/partition-stats/
>
> I am able to get the desired access path through hints, but not so lucky
> at times. Though I don't expect a FTS on that table, its a close call..and
> optimizer may still be correct, as the specific SQL JOINing that table is
> inspecting less than 10% rows. I have all the necessary Indexes and the
> Rule-Based optimizer would have chosen the expected path (Favoring the
> index..than FTS ).
>
> Will do additional analysis based on the inputs and share the results.
>
> Thanks
>
> On Fri, Aug 24, 2012 at 8:09 AM, Uzzell, Stephan <SUzzell@xxxxxxxxxx>wrote:
>
>> Are you seeing an FTS on the whole table? Or on a partition?
>>
>> We've seen something similar - with a very large table with monthly
>> partitions. It *seems* to us that the 10g stats job that collects stale
>> stats looks at the table as a whole, not the partition. Because the new
>> monthly partitions are so small relative to the table, they don't trigger
>> the collect stale stats job. Therefore Oracle has no stats on the new
>> partitions, thinks they are tiny, thinks the FTS will be cheap, and chooses
>> that over a more appropriate index scan.
>>
>> We're still working on how to best manage statistics for the new
>> partitions...
>>
>> Stephan Uzzell
>>
>> -----Original Message-----
>> From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
>> On Behalf Of Vasu
>> Sent: Thursday, 23 August, 2012 20:35
>> To: oracle-l@xxxxxxxxxxxxx
>> Subject: stupid question on FTS
>>
>> I have a huge table in my OLTP DB, that has 100 million+ rows (pls don't
>> ask why), and is partitioned.
>> I know it doesn't make sense for my App to ever do a FTS on it.. But
>> Oracle at times picks up FTS as the best access path (and our STATS is not
>> at it best yet).
>>
>> It just wished for a setting..that "I never want to have a FTS on my
>> table..Unless otherwise explicitly told thru a Hint" .
>>
>> yes, a Hint/setting can't compensate for lack of STATS.. but just that my
>> desparate situation/laziness forced me to think that way.
>>
>> Any thoughts?
>>
>> Thanks,
>> Vasu
>>
>>
>> --
>> //www.freelists.org/webpage/oracle-l
>>
>>
>>
>
>
> --
> -Vasu
>
>
--
-Vasu
--
//www.freelists.org/webpage/oracle-l
Other related posts:
