Re: Indexes on temp tables

Do you know how to lock the statistics?

You can also create a duplicate table, insert rows until it will use the index, then export the statistics from the duplicate table to the initial table.

On Jan 15, 2008 1:11 PM, Ram Raman <veeeraman_at_gmail.com> wrote:

> The stats are collected on it every morning. I did not see the number of
> rows change in the past 3 days.
>
>
> TO_CHAR(LAST_ANALYZED,'DD-MON- TABLE_NAME NUM_ROWS
> ---------------------------------------- -------------------- ----------
> 15-JAN-08 04:00:27am PS_CM_ITEM_MTH_T4 2414
>
> Thanks.
>
>
>
> On 1/15/08, Andrew Kerber <andrew.kerber_at_gmail.com> wrote:
> >
> > You might try gathering statistics on the table when it is at its
> > largest size and see if it will use the index without a hint then. If it
> > does, lock the statistics so they do not get changed.
> >
> > If the query uses bind variables, you might be able to create a sql
> > profile with the appropriate plan.
> >
> > On Jan 15, 2008 12:07 PM, Ram Raman <veeeraman_at_gmail.com> wrote:
> >
> > > Hi,
> > >
> > > Oracle: 10.2.
> > >
> > > We have a query that has been running slow. After researching on the
> > > query I found out that that it was not using an index on one of the
> > > temporary tables involved. I used a hint to force the query to use the
> > > index, and the query's perfomance improved very well. But now I learn from
> > > the developers that the query may use any of a range of temp tables when it
> > > runs - there seem to be about 24 of them. I have the option of adding hints
> > > with the names of all indexes for all the temp tables. I tested the query in
> > > this way and it works fine. This solution works, but is there a better way.
> > >
> > >
> > > PS. Even though they are called temporary tables, they are in
> > > permanent tablespace.
> > >
> > >
> > > Qry:
> > >
> > > SELECT /*+ INDEX(D PS_CM_ITEM_MTH_T7) INDEX(D PS_CM_ITEM_MTH_T4)
> > > INDEX(D PS_CM_ITEM_MTH_T7) */
> > > X.PROCESS_INSTANCE
> > > , A.BUSINESS_UNIT
> > > , A.INV_ITEM_ID, A.CM_BOOK, A.DT_TIMESTAMP, A.SEQ_NBR,
> > > A.CM_DT_TIMESTAMP , A.CM_SEQ_NBR, B.CM_SEQ_COST
> > > , B.COST_ELEMENT, 0, 0 , 0 , B.CM_UNIT_COST_VO , 0 , 0
> > > FROM PS_CM_DEPLETE A, PS_CM_RMATCH_S_VW B, PS_TRANSACTION_INV C,
> > > PS_CM_ITEM_MTH_T4 D, PS_CM_CSTACCTG X
> > > WHERE X.PROCESS_INSTANCE = 2981824
> > > AND X.REQUEST_ID = 'AB301'
> > > AND X.BUSINESS_UNIT = A.BUSINESS_UNIT
> > > AND X.CM_BOOK = A.CM_BOOK
> > > AND B.BUSINESS_UNIT_RECV = C.BUSINESS_UNIT_PO
> > > AND B.RTV_ID = C.RTV_ID
> > > AND B.RTV_LN_NBR = C.RTV_LN_NBR
> > > AND B.RTV_DISTRIB_NUM = C.RTV_DISTRIB_NUM
> > > AND C.BUSINESS_UNIT = A.BUSINESS_UNIT
> > > AND C.INV_ITEM_ID = A.INV_ITEM_ID
> > > AND C.DT_TIMESTAMP = A.DT_TIMESTAMP
> > > AND C.SEQ_NBR = A.SEQ_NBR
> > > AND A.POSTED_FLAG = 'N'
> > > AND A.CM_DUMMY_FLG ='N'
> > > AND D.PROCESS_INSTANCE = X.PROCESS_INSTANCE
> > > AND D.BUSINESS_UNIT = A.BUSINESS_UNIT
> > > AND D.INV_ITEM_ID = A.INV_ITEM_ID
> > > AND D.CM_BOOK = A.CM_BOOK
> > > AND (D.CM_METHOD NOT IN ('6','9') OR D.CM_COST_ELEMNT_OPT <> '1')
> > >
> > > AND EXISTS (
> > > SELECT 'X'
> > > FROM PS_SET_CNTRL_REC
> > > , PS_CM_ELEMENT
> > > WHERE A.BUSINESS_UNIT = PS_SET_CNTRL_REC.SETCNTRLVALUE
> > > AND PS_SET_CNTRL_REC.RECNAME = 'CM_ELEMENT'
> > > AND PS_CM_ELEMENT.SETID = PS_SET_CNTRL_REC.SETID
> > > AND PS_CM_ELEMENT.COST_ELEMENT = B.COST_ELEMENT
> > > AND PS_CM_ELEMENT.COST_CATEGORY = 'MAT' )
> > > Plan:
> > >
> > >
> > > 17:14:47 SQL> select * from table(dbms_xplan.display);
> > > more..
> > >
> > > PLAN_TABLE_OUTPUT
> > >
> > > ------------------------------------------------------------------------------------------------------------------------
> > >
> > >
> > > --------------------------------------------------------------------------------------------------------
> > > | Id | Operation | Name | Rows
> > > | Bytes | Cost (%CPU)| Time |
> > > --------------------------------------------------------------------------------------------------------
> > >
> > > | 0 | SELECT STATEMENT | |
> > > | | 21 (100)| |
> > > | 1 | HASH GROUP BY | | 1
> > > | 259 | 21 (20)| 00:00:01 |
> > > | 2 | NESTED LOOPS | | 1
> > > | 259 | 18 (17)| 00:00:01 |
> > > | 3 | NESTED LOOPS | | 1
> > > | 206 | 17 (18)| 00:00:01 |
> > > | 4 | MERGE JOIN CARTESIAN | | 1
> > > | 139 | 14 (22)| 00:00:01 |
> > > | 5 | MERGE JOIN CARTESIAN | | 1
> > > | 78 | 11 (28)| 00:00:01 |
> > > |* 6 | TABLE ACCESS FULL | PS_CM_ITEM_MTH_T4 | 1
> > > | 38 | 10 (30)| 00:00:01 |
> > > | 7 | BUFFER SORT | | 1
> > > | 40 | 1 (0)| 00:00:01 |
> > > |* 8 | INDEX RANGE SCAN | PSBCM_CSTACCTG | 1
> > > | 40 | 1 (0)| 00:00:01 |
> > > | 9 | BUFFER SORT | | 93
> > > | 5673 | 13 (24)| 00:00:01 |
> > > |* 10 | TABLE ACCESS FULL | PS_CM_RMATCH_COST | 93
> > > | 5673 | 3 (0)| 00:00:01 |
> > > |* 11 | TABLE ACCESS BY INDEX ROWID | PS_CM_DEPLETE | 1
> > > | 67 | 3 (0)| 00:00:01 |
> > > |* 12 | INDEX RANGE SCAN | PSACM_DEPLETE | 18
> > > | | 2 (0)| 00:00:01 |
> > > | 13 | NESTED LOOPS | | 1
> > > | 40 | 2 (0)| 00:00:01 |
> > > |* 14 | TABLE ACCESS BY INDEX ROWID| PS_CM_ELEMENT | 1
> > > | 13 | 1 (0)| 00:00:01 |
> > > |* 15 | INDEX SKIP SCAN | PS_CM_ELEMENT | 1
> > > | | 1 (0)| 00:00:01 |
> > > |* 16 | INDEX UNIQUE SCAN | PSASET_CNTRL_REC | 1
> > > | 27 | 1 (0)| 00:00:01 |
> > > |* 17 | TABLE ACCESS BY INDEX ROWID | PS_TRANSACTION_INV | 1
> > > | 53 | 1 (0)| 00:00:01 |
> > > |* 18 | INDEX UNIQUE SCAN | PS_TRANSACTION_INV | 1
> > > | | 1 (0)| 00:00:01 |
> > >
> > > --------------------------------------------------------------------------------------------------------
> > > Thanks.
> > >
> > >
> > >
> >
> >
> >
> > --
> > Andrew W. Kerber
> >
> > 'If at first you dont succeed, dont take up skydiving.'
>
>
>

-- 
Andrew W. Kerber

'If at first you dont succeed, dont take up skydiving.'

--
http://www.freelists.org/webpage/oracle-l