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  |
 |
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 #559348 is a reply to message #559331] |
Mon, 02 July 2012 13:01  |
joy_division
Messages: 4963
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 (2 Merged) [message #559863 is a reply to message #559515] |
Sat, 07 July 2012 01:34 |
|
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 #559885 is a reply to message #559869] |
Sat, 07 July 2012 02:46 |
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 #559942 is a reply to message #559890] |
Sun, 08 July 2012 01:01 |
|
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 |
 |
Michel Cadot
Messages: 68446
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 |
John Watson
Messages: 8836
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 #560049 is a reply to message #560047] |
Tue, 10 July 2012 02:41 |
John Watson
Messages: 8836
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 |
|
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 |
John Watson
Messages: 8836
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 |
|
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.
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Sat May 27 22:02:00 CDT 2023
|
] 
Search
Help
Members
Register
Login
Home
