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 Go to next message
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 #423708 is a reply to message #423706] Sun, 27 September 2009 01:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Are you sure the plan belongs to the query?
Query mentions po_vendors whereas execution plan mentions AP_SUPPLIERS.

What is the partition key of xla_trial_balances table?

Regards
Michel
Re: Need A Small help on Tuning this query. [message #423709 is a reply to message #423706] Sun, 27 September 2009 01:35 Go to previous messageGo to next message
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 #423711 is a reply to message #423709] Sun, 27 September 2009 03:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Unless you have the most part of the table outside ('01-APR-1950','30-SEP-2009') range (which I doubt), I think there is nothing you can do given that you have only 1 value for source_application_id and the suquery returns a unique value.
The only thing you can do is to activate parallelism on the table as far as your io subsystem and cpu allow it.

Note that the statement will return 10M rows; are you sure someone will read them? Smile

Regards
Michel
Re: Need A Small help on Tuning this query. [message #423712 is a reply to message #423711] Sun, 27 September 2009 03:21 Go to previous messageGo to next message
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 Go to previous message
rleishman
Messages: 3724
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
Previous Topic: Bulk insert is faster then insert into select * ......
Next Topic: performance issue wtith single instance database
Goto Forum:
  


Current Time: Fri Dec 02 12:17:35 CST 2016

Total time taken to generate the page: 0.22849 seconds