Home » RDBMS Server » Performance Tuning » Need A Small help on Tuning this query. (10.2.0.2)
| Need A Small help on Tuning this query. [message #423706] |
Sun, 27 September 2009 01:08  |
balakrishnay
Messages: 54
Registered: September 2009
Location: Pune
|
Member |
|
|
Hi,
SQL> select * from xla_trial_balances
where source_application_id=200
2 3 and gl_date between to_date('01-APR-1950','DD-MON-YYYY') and to_date('30-SEP-2009','DD-MON-YYYY')
4 and party_id not in (select vendor_id from po_vendors where vendor_type_lookup_code = 'INDIA TDS AUTHORITY')
5 /
Execution Plan
----------------------------------------------------------
Plan hash value: 2840953585
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10M| 1785M| 216M (19)|215:31:18 | | |
|* 1 | FILTER | | | | | | | |
| 2 | PARTITION LIST ALL| | 10M| 1785M| 36878 (15)| 00:02:13 | 1 | 18 |
|* 3 | TABLE ACCESS FULL| XLA_TRIAL_BALANCES | 10M| 1785M| 36878 (15)| 00:02:13 | 1 | 18 |
| 4 | NESTED LOOPS | | 1 | 22 | 168 (19)| 00:00:01 | | |
|* 5 | TABLE ACCESS FULL| AP_SUPPLIERS | 1 | 17 | 167 (19)| 00:00:01 | | |
|* 6 | INDEX UNIQUE SCAN| HZ_PARTIES_U1 | 1 | 5 | 1 (0)| 00:00:01 | | |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM "AR"."HZ_PARTIES" "HP","AP"."AP_SUPPLIERS" "PAV"
WHERE "PAV"."VENDOR_TYPE_LOOKUP_CODE"='INDIA TDS AUTHORITY' AND LNNVL("PAV"."VENDOR_ID"<>:B1) AND
"PAV"."PARTY_ID"="HP"."PARTY_ID"))
3 - filter("SOURCE_APPLICATION_ID"=200 AND "GL_DATE">=TO_DATE('1950-04-01 00:00:00',
'yyyy-mm-dd hh24:mi:ss') AND "GL_DATE"<=TO_DATE('2009-09-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
5 - filter("PAV"."VENDOR_TYPE_LOOKUP_CODE"='INDIA TDS AUTHORITY' AND
LNNVL("PAV"."VENDOR_ID"<>:B1))
6 - access("PAV"."PARTY_ID"="HP"."PARTY_ID")
Statistics for the table
Table Number Empty Average Chain Average
Name of Rows Blocks Blocks Space Count Row Len
-------------------------------- ----------- -------- -------- ------- ------ -------
XLA_TRIAL_BALANCES 1,0577,530 282,915 0 0 0 177
Column Column Distinct
Name Details Values Density
------------------------------ ------------------------ --------- -------
EVENT_CLASS_CODE VARCHAR2(30) NOT NULL 8 0
GL_DATE DATE NOT NULL 4131 0
TRX_CURRENCY_CODE VARCHAR2(15) NOT NULL 10 0
ENTERED_ROUNDED_DR NUMBER(22) 186279 0
ENTERED_ROUNDED_CR NUMBER(22) 190885 0
ENTERED_UNROUNDED_DR NUMBER(22) 186282 0
ENTERED_UNROUNDED_CR NUMBER(22) 190889 0
ACCTD_ROUNDED_DR NUMBER(22) 187262 0
ACCTD_ROUNDED_CR NUMBER(22) 191906 0
ACCTD_UNROUNDED_DR NUMBER(22) 187261 0
ACCTD_UNROUNDED_CR NUMBER(22) 191906 0
CODE_COMBINATION_ID NUMBER(22) NOT NULL 490 0
BALANCING_SEGMENT_VALUE VARCHAR2(25) 23 0
NATURAL_ACCOUNT_SEGMENT_VALUE VARCHAR2(25) 39 0
COST_CENTER_SEGMENT_VALUE VARCHAR2(25) 101 0
INTERCOMPANY_SEGMENT_VALUE VARCHAR2(25) 0 0
MANAGEMENT_SEGMENT_VALUE VARCHAR2(25) 0 0
PARTY_ID NUMBER(15,0) 12420 0
PARTY_SITE_ID NUMBER(15,0) 18610 0
PARTY_TYPE_CODE VARCHAR2(30) 1 1
AE_HEADER_ID NUMBER(15,0) 1185939 0
GENERATED_BY_CODE VARCHAR2(30) 1 1
SOURCE_VALUATION_METHOD VARCHAR2(30) 0 0
APPLIED_VALUATION_METHOD VARCHAR2(30) 0 0
CREATION_DATE DATE NOT NULL 8416 0
CREATED_BY NUMBER(15,0) NOT NULL 7 0
LAST_UPDATE_DATE DATE NOT NULL 8416 0
LAST_UPDATED_BY NUMBER(15,0) NOT NULL 8 0
LAST_UPDATE_LOGIN NUMBER(15,0) 1935 0
REQUEST_ID NUMBER(15,0) 1935 0
PROGRAM_APPLICATION_ID NUMBER(15,0) 2 1
PROGRAM_ID NUMBER(15,0) 2 1
PROGRAM_UPDATE_DATE DATE 8416 0
DEFINITION_CODE VARCHAR2(30) NOT NULL 15 0
LEDGER_ID NUMBER(15,0) NOT NULL 13 0
RECORD_TYPE_CODE VARCHAR2(30) NOT NULL 2 1
SOURCE_ENTITY_ID NUMBER(15,0) 1155376 0
SOURCE_APPLICATION_ID NUMBER(15,0) 1 1
APPLIED_TO_ENTITY_ID NUMBER(15,0) 3391849 0
APPLIED_TO_APPLICATION_ID NUMBER(15,0) 2 1
B Average Average
Index Tree Leaf Distinct Leaf Blocks Data Blocks Cluster
Name Unique Level Blks Keys Per Key Per Key Factor
------------------------------ --------- ----- ------ -------- ----------- ----------- ----------
XLA_TRIAL_BALANCES_N1 NONUNIQUE 2 60440 6156 9 551 3392,120
XLA_TRIAL_BALANCES_N2 NONUNIQUE 2 54110 23361 2 179 4181,850
Index Column Col Column
Name Name Pos Details
------------------------------ ------------------------------ ---- ------------------------
XLA_TRIAL_BALANCES_N1 DEFINITION_CODE 1 VARCHAR2(30) NOT NULL
GL_DATE 2 DATE NOT NULL
XLA_TRIAL_BALANCES_N2 CODE_COMBINATION_ID 1 NUMBER(22) NOT NULL
DEFINITION_CODE 2 VARCHAR2(30) NOT NULL
GL_DATE 3 DATE NOT NULL
Its not making use of index on gl_date and more ever this is big table which has partitions in this and tried creating functional based index on gl_Date but no use.
Regards
Bala
|
|
|
|
|
|
| Re: Need A Small help on Tuning this query. [message #423709 is a reply to message #423706] |
Sun, 27 September 2009 01:35  |
balakrishnay
Messages: 54
Registered: September 2009
Location: Pune
|
Member |
|
|
Hi,
select * from DBA_PART_KEY_COLUMNS where name='XLA_TRIAL_BALANCES'
SQL> /
Column Col
OWNER NAME OBJECT_TYPE Name Pos
--------------- ------------------------- --------------- ------------------------------ ----
XLA XLA_TRIAL_BALANCES TABLE DEFINITION_CODE 1
po_vendors is a view accessing AP_SUPPLIERS table which has 22k rows.
Regards
Bala
|
|
|
|
|
|
| Re: Need A Small help on Tuning this query. [message #423712 is a reply to message #423711] |
Sun, 27 September 2009 03:21 |
balakrishnay
Messages: 54
Registered: September 2009
Location: Pune
|
Member |
|
|
Hi Michel,
Thanks for your response even i was thinking in the same way .. since they are fetching entire data from 1950 to sep 2009 .I said its not possible to developer but still she is asking to do something on this .
I am frustrated about this.
Actually the complete query looks like this .
create table itc.koel_acctd_xlatb_data_sagar as
(
SELECT xtb.definition_code,
nvl(xtb.applied_to_entity_id,xtb.source_entity_id) entity_id,
xtb.code_combination_id ,
xtb.source_application_id,
SUM (Nvl(xtb.entered_unrounded_cr,0)) - SUM (Nvl(xtb.entered_unrounded_dr,0)) entered_unrounded_rem_amount,
SUM (Nvl(xtb.entered_rounded_cr,0)) - SUM (Nvl(xtb.entered_rounded_dr,0)) entered_rounded_rem_amount,
SUM (Nvl(xtb.acctd_unrounded_cr,0)) - SUM (Nvl(xtb.acctd_unrounded_dr,0)) acctd_unrounded_rem_amount,
SUM (Nvl(xtb.acctd_rounded_cr,0)) - SUM (Nvl(xtb.acctd_rounded_dr,0)) acctd_rounded_rem_amount,
xtb.ledger_id,
xtb.party_id,
xtb.balancing_segment_value,
xtb.natural_account_segment_value,
xtb.cost_center_segment_value,
xtb.intercompany_segment_value,
xtb.management_segment_value,
xtb.gl_date
FROM xla_trial_balances xtb
where 1=1
and xtb.party_id not in (select vendor_id from po_vendors where vendor_type_lookup_code = 'INDIA TDS AUTHORITY')
--and xtb.definition_code = 'AP_200_1'
and xtb.source_application_id=200
--and xtb.ledger_id = 1
and xtb.gl_date between '01-APR-1950' and '30-SEP-2009'
GROUP BY xtb.definition_code,
nvl(xtb.applied_to_entity_id,xtb.source_entity_id) ,
xtb.code_combination_id ,
xtb.source_application_id,
xtb.ledger_id,
xtb.party_id,
xtb.balancing_segment_value,
xtb.natural_account_segment_value,
xtb.cost_center_segment_value,
xtb.intercompany_segment_value,
xtb.management_segment_value,
xtb.gl_date
HAVING SUM (Nvl(xtb.acctd_rounded_cr,0)) <> SUM (Nvl(xtb.acctd_rounded_dr,0))
)
/
I have changed the having clause with this .
case when (sum(Nvl(xtb.acctd_rounded_cr,0)) - SUM (Nvl(xtb.acctd_rounded_dr,0)))<>SUM (Nvl(xtb.acctd_rounded_dr,0))
then null end,
But developer they don't have patience to check the validity of the data again.
Can someting can be done on the above query. Higly appreciated your inputs please.
And also i would like to know why you are focusing on this .
What is the partition key of xla_trial_balances table? Can you tell me?
Thank you
Bala
[Updated on: Sun, 27 September 2009 03:23] Report message to a moderator |
|
|
|
| Re: Need A Small help on Tuning this query. [message #423725 is a reply to message #423712] |
Sun, 27 September 2009 08:22 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
and xtb.party_id not in
(select vendor_id
from po_vendors
where vendor_type_lookup_code = 'INDIA TDS AUTHORITY'
AND VENDOR_ID IS NOT NULL)
AND XTB.PARTY_ID IS NOT NULL
Ensure there are no nulls in the subquery comparison and you will allow Oracle to perform an anti-join.
Ross Leishman
|
|
|
|
Goto Forum:
Current Time: Sat May 18 07:44:21 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
