Home » RDBMS Server » Performance Tuning » Oracle 10g showing problem with index (2 Merged) (10g)
Oracle 10g showing problem with index (2 Merged) [message #559327] Mon, 02 July 2012 11:14 Go to next message
avaquestions
Messages: 10
Registered: July 2012
Location: Abu Dhabi
Junior Member
hi Friends,

I'm having an Oracle 10g Db and having Two Users
1. Old User was working live till this last month 15th
2. is Newly created user (production) and migrated master data and transaction data from 1st jan 2012 to 15 Jun 2012


here the problem we are facing is..... all queries which execute for retrieving data for a month (something like monthly report) is showing Full cost in New User... if we run the same in old User it will be showing a small cost as well as retrieve data with in seconds..
There are 281729 records in the table against the Selecting period in both the users...
Known reason for this problem is INDEX ... Index is not working in Newly created user if we retrieve data for more then 20days.. the same index will work with a shorter period...
any one know what will be the reason...

we try all possible operations like Rebuilding indexes , drop and create index again.. etc .. but which results the same ...

Present Production Users Execution Plan

LEVEL OPERATION
1.00 SELECT STATEMENT Cost = 47456
2.00 TABLE ACCESS DX_MST_ORG_BRANCH_LANG BY INDEX ROWID Cost = 1
3.00 INDEX PK_DX_MST_ORG_BRANCH_LANG UNIQUE SCAN Cost = 1
2.00 HASH GROUP BY Cost = 2
3.00 TABLE ACCESS DX_TRNS_REMITTANCE FULL Cost = 1

Old Users Execution Plan

LEVEL OPERATION
1.00 SELECT STATEMENT Cost = 9776
2.00 TABLE ACCESS DX_MST_ORG_BRANCH_LANG BY INDEX ROWID Cost = 1
3.00 INDEX PK_DX_MST_ORG_BRANCH_LANG UNIQUE SCAN Cost = 1
2.00 HASH GROUP BY Cost = 2
3.00 TABLE ACCESS DX_TRNS_REMITTANCE BY INDEX ROWID Cost = 1
4.00 INDEX NDX_REMIT_TRANDATE SKIP SCAN Cost = 1


Both the Execution Plan is against the same Query in Different Users for same period ....

why this is happening .. Any idea ..?
Re: Oracle 10g showing problem with index [message #559329 is a reply to message #559327] Mon, 02 July 2012 11:21 Go to previous messageGo to next message
BlackSwan
Messages: 23048
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/


http://www.orafaq.com/forum/m/433888/136107/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof
Oracle 10g showing problem with index [message #559331 is a reply to message #559327] Mon, 02 July 2012 11:24 Go to previous messageGo to next message
avaquestions
Messages: 10
Registered: July 2012
Location: Abu Dhabi
Junior Member
hi Friends,

I'm having an Oracle 10g Db and having Two Users
1. Old User was working live till this last month 15th
2. is Newly created user (production) and migrated master data and transaction data from 1st jan 2012 to 15 Jun 2012


here the problem we are facing is..... all queries which execute for retrieving data for a month (something like monthly report) is showing Full cost in New User... if we run the same in old User it will be showing a small cost as well as retrieve data with in seconds..
There are 281729 records in the table against the Selecting period in both the users...
Known reason for this problem is INDEX ... Index is not working in Newly created user if we retrieve data for more then 20days.. the same index will work with a shorter period...
any one know what will be the reason...

we try all possible operations like Rebuilding indexes , drop and create index again.. etc .. but which results the same ...
Re: Oracle 10g showing problem with index [message #559340 is a reply to message #559331] Mon, 02 July 2012 12:07 Go to previous messageGo to next message
BlackSwan
Messages: 23048
Registered: January 2009
Senior Member
WHY MY INDEX IS NOT BEING USED
http://communities.bmc.com/communities/docs/DOC-10031

http://searchoracle.techtarget.com/tip/Why-isn-t-my-index-getting-used

http://www.orafaq.com/tuningguide/not%20using%20index.html
Re: Oracle 10g showing problem with index [message #559348 is a reply to message #559331] Mon, 02 July 2012 13:01 Go to previous messageGo to next message
joy_division
Messages: 4545
Registered: February 2005
Location: East Coast USA
Senior Member
avaquestions wrote on Mon, 02 July 2012 12:24

There are 281729 records in the table against the Selecting period in both the users...


You say "table" but I see two tables in the execution plan.
you need to list indexes on all tables in both environments.
Are statistics up to date?
Anything else is a guess without giving all that is asked from you.
Re: Oracle 10g showing problem with index [message #559394 is a reply to message #559348] Tue, 03 July 2012 02:08 Go to previous messageGo to next message
avaquestions
Messages: 10
Registered: July 2012
Location: Abu Dhabi
Junior Member
the thing is both users having same tables and indexes ... so if query works in old User it should work in new user also isn't it ?

we dropped and re-generated indexes on 02/July/2012 so it updated all statistics

we went through statistics of table and index Look like statistics up to date for index and table

NDX_REMIT_TRANDATE
--------------------------
Last Analyzed 2012-07-02 22:52:01
Blevel 2
Distinct Keys 1463208
Clustering Factor 1422364
Leaf Blocks 6256
Average Leaf Blocks Per Key 1
Average Data Blocks Per Key 1
Number of Rows 1636409
Sample Size 296877

DX_TRNS_REMITTANCE
-----------------------
Statistics
Last Analyzed Jul 2, 2012 10:49:21 PM
Empty Blocks 6290
Average Space 1294
Number of Rows 1599379
Sample Size 1599379
Average Row Length 929
Continued Row Count 0
Average Space Freelist Blocks 0
Number of Freelist Blocks 0

any other possible mistakes ...........

Re: Oracle 10g showing problem with index [message #559497 is a reply to message #559394] Tue, 03 July 2012 10:54 Go to previous messageGo to next message
avaquestions
Messages: 10
Registered: July 2012
Location: Abu Dhabi
Junior Member
Hi joy....

i have two doubts which is listed below

1. is there any known issue with user which Exported from TableSpace A and Importing to Tablespabce B ?
we altered all the tables and indexes to tablespace B even any problem

if the above scenario will make problem then my second Question

2. is there any known issue with two users which work in same tablespace and both having same structure...
Re: Oracle 10g showing problem with index [message #559498 is a reply to message #559497] Tue, 03 July 2012 10:57 Go to previous messageGo to next message
BlackSwan
Messages: 23048
Registered: January 2009
Senior Member
1) no known issue
2) no known issue
Re: Oracle 10g showing problem with index (2 Merged) [message #559515 is a reply to message #559327] Tue, 03 July 2012 12:55 Go to previous messageGo to next message
joy_division
Messages: 4545
Registered: February 2005
Location: East Coast USA
Senior Member
You still really haven't supplied enough information. Show the query. Also, I am unfamiliar with this kind of execution plan. It seems to be lacking better details. Is it from TOAD or some old version or explain plan?
Re: Oracle 10g showing problem with index (2 Merged) [message #559863 is a reply to message #559515] Sat, 07 July 2012 01:34 Go to previous messageGo to next message
avaquestions
Messages: 10
Registered: July 2012
Location: Abu Dhabi
Junior Member
sir ....

below mentioned which make problem

SELECT BRANCHCODE, '01/Jun/2012' as TRANDATE, (SELECT DESCRIPTION FROM DX_MST_ORG_BRANCH_LANG WHERE LANGCODE = 'EN' AND BRANCHCODE = DX_TRNS_REMITTANCE.BRANCHCODE AND ORGCODE = '00001')AS BRANCH
,COUNT(CASE WHEN SERVCODE IN ('00001','00059','00060','00061','00062','00063') THEN 1 END) AS DD
,nvl(SUM(CASE WHEN SERVCODE IN ('00001','00059','00060','00061','00062','00063') THEN EQUVAMT END),0) AS DDLCY
,COUNT(CASE WHEN SERVCODE IN ('00002','00032','00033','00046','00047','00048','00049','00055','00056','00057','00058','00064','00065','00074','00078') THEN 1 END) AS TT
,nvl(SUM(CASE WHEN SERVCODE IN ('00002','00032','00033','00046','00047','00048','00049','00055','00056','00057','00058','00064','00065','00074','00078') THEN EQUVAMT END),0) AS TTLCY
,COUNT(CASE WHEN SERVCODE NOT IN ('00002','00032','00033','00046','00047','00048','00049','00055','00056','00057','00058','00064','00065','00074','00078','00001','000 59','00060','00061','00062','00063') THEN 1 END) AS PT
,nvl(SUM(CASE WHEN SERVCODE NOT IN ('00002','00032','00033','00046','00047','00048','00049','00055','00056','00057','00058','00064','00065','00074','00078','00001','000 59','00060','00061','00062','00063') THEN EQUVAMT END),0) AS PTLCY
FROM DX_TRNS_REMITTANCE WHERE PAIDFLG = 'Y' AND CANCELIND = '0' AND TRANTYPE = '0' AND TRANDATE between to_date('01/Jun/2012 00:00:00','dd/Mon/yyyy HH24:mi:ss') AND to_date('30/Jun/2012 23:59:59','dd/Mon/yyyy HH24:mi:ss')
GROUP BY BRANCHCODE
Re: Oracle 10g showing problem with index (2 Merged) [message #559869 is a reply to message #559863] Sat, 07 July 2012 01:59 Go to previous messageGo to next message
John Watson
Messages: 4802
Registered: January 2010
Location: Global Village
Senior Member
Hello. It really difficult to read your code and everything, because it isn't formatted properly or enclosed within [code] tags. Please read How to use [code] tags and make your code easier to read and use the tags for all code and exection plans. There is a code formatter on http://www.dpriver.com/pp/sqlformat.htm

It looks as though your change is that the first execution plan uses an index skip scan to retrieve rows from DX_TRNS_REMITTANCE, and the second plan uses a full table scan? Have you tried hinting the code to get a skip scan? If that works, then you need to find out why the optimizer isn't doing this.
Re: Oracle 10g showing problem with index (2 Merged) [message #559885 is a reply to message #559869] Sat, 07 July 2012 02:46 Go to previous messageGo to next message
muralikri
Messages: 638
Registered: August 2011
Location: chennai
Senior Member

Hey post like..

SELECT BRANCHCODE, '01/Jun/2012' as TRANDATE, (SELECT DESCRIPTION FROM DX_MST_ORG_BRANCH_LANG WHERE LANGCODE = 'EN' AND BRANCHCODE = DX_TRNS_REMITTANCE.BRANCHCODE AND ORGCODE = '00001')AS BRANCH
,COUNT(CASE WHEN SERVCODE IN ('00001','00059','00060','00061','00062','00063') THEN 1 END) AS DD
,nvl(SUM(CASE WHEN SERVCODE IN ('00001','00059','00060','00061','00062','00063') THEN EQUVAMT END),0) AS DDLCY
,COUNT(CASE WHEN SERVCODE IN ('00002','00032','00033','00046','00047','00048','00049','00055','00056','00057','00058','00064','00065','00074','00078') THEN 1 END) AS TT
,nvl(SUM(CASE WHEN SERVCODE IN ('00002','00032','00033','00046','00047','00048','00049','00055','00056','00057','00058','00064','00065','00074','00078') THEN EQUVAMT END),0) AS TTLCY
,COUNT(CASE WHEN SERVCODE NOT IN ('00002','00032','00033','00046','00047','00048','00049','00055','00056','00057','00058','00064','00065','00074','00078','00001','000 59','00060','00061','00062','00063') THEN 1 END) AS PT
,nvl(SUM(CASE WHEN SERVCODE NOT IN ('00002','00032','00033','00046','00047','00048','00049','00055','00056','00057','00058','00064','00065','00074','00078','00001','000 59','00060','00061','00062','00063') THEN EQUVAMT END),0) AS PTLCY
FROM DX_TRNS_REMITTANCE WHERE PAIDFLG = 'Y' AND CANCELIND = '0' AND TRANTYPE = '0' AND TRANDATE between to_date('01/Jun/2012 00:00:00','dd/Mon/yyyy HH24:mi:ss') AND to_date('30/Jun/2012 23:59:59','dd/Mon/yyyy HH24:mi:ss')
GROUP BY BRANCHCODE
Re: Oracle 10g showing problem with index (2 Merged) [message #559890 is a reply to message #559885] Sat, 07 July 2012 03:11 Go to previous messageGo to next message
Michel Cadot
Messages: 59775
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
NO NO NO! Do NOT post like that.

INDENT the code.
LIMIT the lines to 80 character width.
This is UNREADABLE.
Use the SQL formatter John pointed you to if you don't how to format a code.

Regards
Michel

[Updated on: Sat, 07 July 2012 03:12]

Report message to a moderator

Re: Oracle 10g showing problem with index (2 Merged) [message #559942 is a reply to message #559890] Sun, 08 July 2012 01:01 Go to previous messageGo to next message
avaquestions
Messages: 10
Registered: July 2012
Location: Abu Dhabi
Junior Member
sir sorry for the mistake .... will try to follow the standards of form

SELECT 
        BRANCHCODE ,
       '01/Jun/2012' as  TRANDATE ,
       (SELECT DESCRIPTION FROM DX_MST_ORG_BRANCH_LANG WHERE LANGCODE = 'EN' 
  AND BRANCHCODE = DX_TRNS_REMITTANCE.BRANCHCODE AND ORGCODE = '00001')AS  BRANCH ,
       COUNT(CASE WHEN SERVCODE IN ('00001','00059') THEN 1 END) AS  DD ,
       nvl(SUM(CASE WHEN SERVCODE IN ('00001','00059') THEN EQUVAMT END),0) AS  DDLCY ,
       COUNT(CASE WHEN SERVCODE IN    ('00002','00032')THEN 1 END) AS  TT ,
       nvl(SUM(CASE WHEN SERVCODE IN ('00002','00032')THEN EQUVAMT END),0) AS  TTLCY ,
       COUNT(CASE WHEN SERVCODE NOT IN ('00047','00048') THEN 1 END) AS  PT ,
       nvl(SUM(CASE WHEN SERVCODE NOT IN ('00047','00048') THEN EQUVAMT END),0) AS  PTLCY 
FROM DX_TRNS_REMITTANCE 
WHERE PAIDFLG = 'Y' 
  AND CANCELIND = '0' 
  AND TRANTYPE = '0' 
  AND TRANDATE between 
  to_date('01/Jun/2012 00:00:00','dd/Mon/yyyy HH24:mi:ss') 
  AND to_date('30/Jun/2012 23:59:59','dd/Mon/yyyy HH24:mi:ss')

GROUP BY BRANCHCODE


hope this is the way have to use the code tag in forms ..
Re: Oracle 10g showing problem with index (2 Merged) [message #559948 is a reply to message #559942] Sun, 08 July 2012 01:49 Go to previous messageGo to next message
Michel Cadot
Messages: 59775
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Here's what give the formatter John gave:
SELECT branchcode, 
       '01/Jun/2012'             AS TRANDATE, 
       (SELECT description 
        FROM   dx_mst_org_branch_lang 
        WHERE  langcode = 'EN' 
           AND branchcode = dx_trns_remittance.branchcode 
           AND orgcode = '00001')AS BRANCH, 
       Count(CASE 
               WHEN servcode IN ( '00001', '00059', '00060', '00061', 
                                  '00062', '00063' ) THEN 1 
             END)                AS DD, 
       Nvl(SUM(CASE 
                 WHEN servcode IN ( '00001', '00059', '00060', '00061', 
                                    '00062', '00063' ) THEN equvamt 
               END), 0)          AS DDLCY, 
       Count(CASE 
               WHEN servcode IN ( '00002', '00032', '00033', '00046', 
                                  '00047', '00048', '00049', '00055', 
                                  '00056', '00057', '00058', '00064', 
                                  '00065', '00074', '00078' ) THEN 1 
             END)                AS TT, 
       Nvl(SUM(CASE 
                 WHEN servcode IN ( '00002', '00032', '00033', '00046', 
                                    '00047', '00048', '00049', '00055', 
                                    '00056', '00057', '00058', '00064', 
                                    '00065', '00074', '00078' ) THEN equvamt 
               END), 0)          AS TTLCY, 
       Count(CASE 
               WHEN servcode NOT IN ( '00002', '00032', '00033', '00046', 
                                      '00047', '00048', '00049', '00055', 
                                      '00056', '00057', '00058', '00064', 
                                      '00065', '00074', '00078', '00001', 
                                      '000 59', '00060', '00061', '00062', 
                                      '00063' ) 
             THEN 1 
             END)                AS PT, 
       Nvl(SUM(CASE 
                 WHEN servcode NOT IN ( '00002', '00032', '00033', '00046', 
                                        '00047', '00048', '00049', '00055', 
                                        '00056', '00057', '00058', '00064', 
                                        '00065', '00074', '00078', '00001', 
                                        '000 59', '00060', '00061', '00062', 
                                        '00063' ) 
               THEN 
                 equvamt 
               END), 0)          AS PTLCY 
FROM   dx_trns_remittance 
WHERE  paidflg = 'Y' 
   AND cancelind = '0' 
   AND trantype = '0' 
   AND trandate BETWEEN To_date('01/Jun/2012 00:00:00', 
                        'dd/Mon/yyyy HH24:mi:ss') AND 
                        To_date( 
                            '30/Jun/2012 23:59:59', 'dd/Mon/yyyy HH24:mi:ss' 
                        ) 
GROUP  BY branchcode  

It is quite good even if I prefer a little more compact:
SELECT branchcode, 
       '01/Jun/2012'             AS TRANDATE, 
       (SELECT description 
        FROM   dx_mst_org_branch_lang 
        WHERE  langcode = 'EN' 
           AND branchcode = dx_trns_remittance.branchcode 
           AND orgcode = '00001')AS BRANCH, 
       Count(CASE 
               WHEN servcode IN ( '00001', '00059', '00060', '00061', '00062', '00063' ) 
                 THEN 1 
             END)                AS DD, 
       Nvl(SUM(CASE 
                 WHEN servcode IN ( '00001', '00059', '00060', '00061', '00062', '00063' ) 
                   THEN equvamt 
               END), 0)          AS DDLCY, 
       Count(CASE 
               WHEN servcode IN ( '00002', '00032', '00033', '00046', '00047', '00048', '00049', '00055', 
                                  '00056', '00057', '00058', '00064', '00065', '00074', '00078' ) 
                 THEN 1 
             END)                AS TT, 
       Nvl(SUM(CASE 
                 WHEN servcode IN ( '00002', '00032', '00033', '00046', '00047', '00048', '00049', '00055', 
                                    '00056', '00057', '00058', '00064', '00065', '00074', '00078' ) 
                   THEN equvamt 
               END), 0)          AS TTLCY, 
       Count(CASE 
               WHEN servcode NOT IN ( '00002', '00032', '00033', '00046', '00047', '00048', '00049', '00055', 
                                      '00056', '00057', '00058', '00064', '00065', '00074', '00078', '00001', 
                                      '000 59', '00060', '00061', '00062', '00063' ) 
                 THEN 1 
             END)                AS PT, 
       Nvl(SUM(CASE 
                 WHEN servcode NOT IN ( '00002', '00032', '00033', '00046', '00047', '00048', '00049', '00055', 
                                        '00056', '00057', '00058', '00064', '00065', '00074', '00078', '00001', 
                                        '000 59', '00060', '00061', '00062', '00063' ) 
                   THEN equvamt 
               END), 0)          AS PTLCY 
FROM   dx_trns_remittance 
WHERE  paidflg = 'Y' 
   AND cancelind = '0' 
   AND trantype = '0' 
   AND trandate BETWEEN To_date('01/Jun/2012 00:00:00', 'dd/Mon/yyyy HH24:mi:ss') 
                    AND To_date('30/Jun/2012 23:59:59', 'dd/Mon/yyyy HH24:mi:ss') 
GROUP  BY branchcode  

Regards
Michel

[Updated on: Sun, 08 July 2012 01:53]

Report message to a moderator

Re: Oracle 10g showing problem with index (2 Merged) [message #559951 is a reply to message #559948] Sun, 08 July 2012 01:58 Go to previous messageGo to next message
John Watson
Messages: 4802
Registered: January 2010
Location: Global Village
Senior Member
And to get back to the question, Arin, let me quote myself:
Quote:
It looks as though your change is that the first execution plan uses an index skip scan to retrieve rows from DX_TRNS_REMITTANCE, and the second plan uses a full table scan? Have you tried hinting the code to get a skip scan?
and the Black Swan:
Quote:
1) DDL for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof
Re: Oracle 10g showing problem with index (2 Merged) [message #560047 is a reply to message #559951] Tue, 10 July 2012 02:33 Go to previous messageGo to next message
avaquestions
Messages: 10
Registered: July 2012
Location: Abu Dhabi
Junior Member
yes we tried .... hinting index also but it won't work in new user

sir one thing we noticed is in second user we inserted these records with single insert query from old user and single commit but first user it was daily transaction entry(when it was on production)

is there any known issue with the bulk insertion ? ... we updated statistics of tables but that didn't make any significant change
Re: Oracle 10g showing problem with index (2 Merged) [message #560049 is a reply to message #560047] Tue, 10 July 2012 02:41 Go to previous messageGo to next message
John Watson
Messages: 4802
Registered: January 2010
Location: Global Village
Senior Member
Quote:
yes we tried
What did you try?
Quote:
but it won't work in new user
Not exactly a detailed description.

You still have not provided the information requested: this topic can go nowhere until you do.
(and, by the way, you could have said "thank you" to the people who are trying to assist.)
Re: Oracle 10g showing problem with index (2 Merged) [message #560189 is a reply to message #560049] Wed, 11 July 2012 02:32 Go to previous messageGo to next message
avaquestions
Messages: 10
Registered: July 2012
Location: Abu Dhabi
Junior Member
sir Watson

your question was "Have you tried hinting the code to get a skip scan?"
and the response was "yes" and mentioned that "index hind didn't work" ie considering index but no change in the cost or no change in the result.

i'll try to describe situation..

both the users having same indexes and same tables.
Old user with more data is working fine because since 2009 on words it is working and went through many turning processes. what we know about turning is applying indexes , updating statistics so we tried it in the new user but there is no significant change.

Listing the Plan for quick reference

Present Production Users Execution Plan

LEVEL OPERATION
1.00 SELECT STATEMENT Cost = 47456
2.00 TABLE ACCESS DX_MST_ORG_BRANCH_LANG BY INDEX ROWID Cost = 1
3.00 INDEX PK_DX_MST_ORG_BRANCH_LANG UNIQUE SCAN Cost = 1
2.00 HASH GROUP BY Cost = 2
3.00 TABLE ACCESS DX_TRNS_REMITTANCE FULL Cost = 1

Old Users Execution Plan

LEVEL OPERATION
1.00 SELECT STATEMENT Cost = 9776
2.00 TABLE ACCESS DX_MST_ORG_BRANCH_LANG BY INDEX ROWID Cost = 1
3.00 INDEX PK_DX_MST_ORG_BRANCH_LANG UNIQUE SCAN Cost = 1
2.00 HASH GROUP BY Cost = 2
3.00 TABLE ACCESS DX_TRNS_REMITTANCE BY INDEX ROWID Cost = 1
4.00 INDEX NDX_REMIT_TRANDATE SKIP SCAN Cost = 1

the above plan is based on the normal execution is with out hinting index ......

Below going to list only the details of New User which having problem and the details with hint index,

we tried hinting indexes in two methods

1 . /*+ index(DX_TRNS_REMITTANCE,NDX_REMIT_TRANDATE )*/ : This is the index which is there in the execution plan index and plan is listed below for quick verification.

Index looks like this
CREATE INDEX ndx_remit_trandate 
  ON dx_trns_remittance (orgcode, trandate, paidflg) 



2 . /*+ index(DX_TRNS_REMITTANCE, (orgcode, trandate, paidflg) )*/

for both the case Plan is
LEVEL OPERATION
1.00 SELECT STATEMENT Cost = 68220
2.00 TABLE ACCESS DX_MST_ORG_BRANCH_LANG BY INDEX ROWID Cost = 1
3.00 INDEX PK_DX_MST_ORG_BRANCH_LANG UNIQUE SCAN Cost = 1
2.00 HASH GROUP BY Cost = 2
3.00 TABLE ACCESS DX_TRNS_REMITTANCE BY INDEX ROWID Cost = 1
4.00 INDEX NDX_REMIT_TRANDATE SKIP SCAN Cost = 1
------------------------------------------------------------------------------------------


in our last post we mentioned about how this data come to the new users transaction tables ie in the above case DX_TRNS_REMITTANCE table which is inserted from Old user out of 8098616 we inserted 1438737 records to the new user but Master Tables like DX_MST_ORG_BRANCH_LANG is imported from old user..

method which used for inserting data to new user Transaction tables..
CREATE TABLE newuser.dx_trns_remittance AS 
  SELECT * 
  FROM   olduser.dx_trns_remittance 
  WHERE  trandate BETWEEN To_date('01/Jan/2012 00:00:00', 
                              'dd/Mon/yyyy hh24:mi:ss') AND 
                              To_date( 
                                  '14/Jun/2012 23:59:59', 
                              'dd/Mon/yyyy hh24:mi:ss' 
                              ); 

is this cause any problem.... ?

Hope this is a complete description of the present situation

i'm thanking to all who spent precious time for helping me in this case and when we look forward

Thank you Watson for pointing me out the manners in Form
Re: Oracle 10g showing problem with index (2 Merged) [message #560192 is a reply to message #560189] Wed, 11 July 2012 02:59 Go to previous messageGo to next message
John Watson
Messages: 4802
Registered: January 2010
Location: Global Village
Senior Member
It looks as though you are now getting the execution plan that you want. I'm surprised at this, since the hint is wrong. The skip scan hint is index_ss.

It really would help if you followed the forum guidelines: you have already been asked to use EXPLAIN PLAN to show the execution plan, not that odd format you are using. In particular, we do not know what that "Cost" means. Generally speaking, the cost information in a real execution plan is not comparable across plans. Just because it is higher in one plan than the other does not mean that the plan is slower. Cost is only significant within a plan, it shows you the most expensive step.
There are many possible reasons for an index being ignored by the CBO, the most obvious one being that using the index slows down the statement. Your insert of rows could have that effect, by changing the clustering factor.
Do you actually have a problem? What is the timing for the statements, on the original system, and the new system with and without the hint?
Re: Oracle 10g showing problem with index (2 Merged) [message #560255 is a reply to message #560192] Wed, 11 July 2012 09:52 Go to previous messageGo to next message
avaquestions
Messages: 10
Registered: July 2012
Location: Abu Dhabi
Junior Member
Sir, hope this is the format of Execution plan you are looking for ...

old user 
----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                           |    25 |   775 | 10102   (1)| 00:02:02 |
|   1 |  TABLE ACCESS BY INDEX ROWID | DX_MST_ORG_BRANCH_LANG    |     1 |    60 |     3   (0)| 00:00:
|*  2 |   INDEX UNIQUE SCAN          | PK_DX_MST_ORG_BRANCH_LANG |     1 |       |     2   (0)| 00:00:01 |
|   3 |  HASH GROUP BY               |                           |    25 |   775 | 10102   (1)| 00:02:02 |
|*  4 |   TABLE ACCESS BY INDEX ROWID| DX_TRNS_REMITTANCE        |   193K|  5872K| 10088   (1)| 00:02:02 |
|*  5 |    INDEX SKIP SCAN           | NDX_REMIT_TRANDATE        |  5855 |       |  5468   (1)| 00:01:06 |
----------------------------------------------------------------------------------------------------

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

   2 - access("BRANCHCODE"=:B1 AND "ORGCODE"='00001' AND "LANGCODE"='EN')
   4 - filter("CANCELIND"='0' AND "TRANTYPE"='0')
   5 - access("TRANDATE">=TO_DATE('2012-05-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND
              "PAIDFLG"='Y' AND "TRANDATE"<=TO_DATE('2012-05-31 23:59:59', 'yyyy-mm-dd hh24:mi:ss'))
       filter("TRANDATE">=TO_DATE('2012-05-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND
              "TRANDATE"<=TO_DATE('2012-05-31 23:59:59', 'yyyy-mm-dd hh24:mi:ss') AND "PAIDFLG"='Y')

22 rows selected.



New users Exicution plan 

----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                           |    25 |   775 |   197K  (1)| 00:39:33 |
|   1 |  TABLE ACCESS BY INDEX ROWID | DX_MST_ORG_BRANCH_LANG    |     1 |    60 |     3   (0)| 00:00:
|*  2 |   INDEX UNIQUE SCAN          | PK_DX_MST_ORG_BRANCH_LANG |     1 |       |     2   (0)| 00:00:01 |
|   3 |  HASH GROUP BY               |                           |    25 |   775 |   197K  (1)| 00:39:33 |
|*  4 |   TABLE ACCESS BY INDEX ROWID| DX_TRNS_REMITTANCE        |   223K|  6768K|   197K  (1)| 00:39:32 |
|*  5 |    INDEX FULL SCAN           | NDX_REMIT_TRANDATE        |   226K|       |  6401   (2)| 00:01:17 |
----------------------------------------------------------------------------------------------------

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

   2 - access("BRANCHCODE"=:B1 AND "ORGCODE"='00001' AND "LANGCODE"='EN')
   4 - filter("CANCELIND"='0' AND "TRANTYPE"='0')
   5 - access("TRANDATE">=TO_DATE('2012-05-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND
              "PAIDFLG"='Y' AND "TRANDATE"<=TO_DATE('2012-05-31 23:59:59', 'yyyy-mm-dd hh24:mi:ss'))
       filter("TRANDATE">=TO_DATE('2012-05-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND
              "TRANDATE"<=TO_DATE('2012-05-31 23:59:59', 'yyyy-mm-dd hh24:mi:ss') AND "PAIDFLG"='Y')

22 rows selected.


both the Plan is with hint ... if this will not satisfy the requirement i'll post with out hint also. hope this will help you to understand the situation.what you mean by timing , is it the response time of the query execution ?

I saw this type of plan many times but i didn't try to read it before .. now i was trying to understand it but not understood completely.. any way thanking you for spending your time and utilizing deep knowledge that you have.
Re: Oracle 10g showing problem with index (2 Merged) [message #560257 is a reply to message #560255] Wed, 11 July 2012 10:02 Go to previous messageGo to next message
John Watson
Messages: 4802
Registered: January 2010
Location: Global Village
Senior Member
Quote:
The skip scan hint is index_ss.
Re: Oracle 10g showing problem with index (2 Merged) [message #560260 is a reply to message #560257] Wed, 11 July 2012 10:11 Go to previous message
avaquestions
Messages: 10
Registered: July 2012
Location: Abu Dhabi
Junior Member
i didn't get you sir ..... what is it means ....
Previous Topic: Elapsed time
Next Topic: DBA performance tools
Goto Forum:
  


Current Time: Tue Nov 25 19:42:53 CST 2014

Total time taken to generate the page: 0.14105 seconds