Re: stupid question on FTS
From: Vasu <vasudevanr_at_gmail.com>
Date: Fri, 24 Aug 2012 15:16:00 -0500
Message-ID: <CA+O6cL+chyKEbu3kcz2XLArw_12vARUsATr8Fva3OQjhzZEbBg_at_mail.gmail.com>
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 )
PT_MDSESS_TRACKER - 1 mil. rows work-table
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
| Id | Operation | Name | Rows |
Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
| 0 | SELECT STATEMENT | |
| | | 715K(100)| | | |
| 1 | SORT AGGREGATE | | 1 |
| Id | Operation | Name | Rows |
Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
| 0 | SELECT STATEMENT | |
| | | 691K(100)| | | |
| 1 | SORT AGGREGATE | | 1 |
| Id | Operation | Name | Rows |
Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
| 0 | SELECT STATEMENT | |
| | | 1640K(100)| | | |
| 1 | SORT AGGREGATE | | 1 |
| Id | Operation | Name | Rows |
Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
| 0 | SELECT STATEMENT | |
| | | 1621K(100)| | | |
| 1 | SORT AGGREGATE | | 1 |
Date: Fri, 24 Aug 2012 15:16:00 -0500
Message-ID: <CA+O6cL+chyKEbu3kcz2XLArw_12vARUsATr8Fva3OQjhzZEbBg_at_mail.gmail.com>
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 sessionsthat 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 PM2 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
- 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_at_gmail.com> 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_at_micros.com>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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] >> On Behalf Of Vasu >> Sent: Thursday, 23 August, 2012 20:35 >> To: oracle-l_at_freelists.org >> 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 >> >> >> -- >> http://www.freelists.org/webpage/oracle-l >> >> >> > > > -- > -Vasu > >
-- -Vasu -- http://www.freelists.org/webpage/oracle-lReceived on Fri Aug 24 2012 - 15:16:00 CDT