Home   |   Register   |   Rules   |   Calendar   |   Get Daily   |     |  
 


If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > DB2 > Performance Issue

Reply
Page 1 of 3 1 23
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old 03-18-09, 10:29
rameshj6 rameshj6 is offline
Registered User
  
Join Date: Oct 2008
Posts: 51
Cool Performance Issue
Os: AIX
Version : 9.1

Select query taking nearly 7 - 8 hrs. to complete however same query was taking less than hour in same environment and with same amount of data.

Estimated Cost = 141769.031250


can any one please suggest on this.
Reply With Quote
  #2 (permalink)  
Old 03-18-09, 10:38
ARWinner ARWinner is offline
Registered User
  
Join Date: Jan 2003
Posts: 3,575
Obviously something changed. Maybe if you supply more info like the query, access plan, etc, you might get a better response.

Andy
Reply With Quote
  #3 (permalink)  
Old 03-18-09, 10:46
Pat Phelan Pat Phelan is offline
Resident Curmudgeon
  
Join Date: Feb 2004
Location: In front of the computer
Posts: 12,605
Quote:
Originally Posted by rameshj6
can any one please suggest on this.
I'd suggest that you try to make it run faster.

Adding to ARWinner's comment, if you provide more information about your problem then we can probably provide more information or suggestions for a solution.

-PatP
Reply With Quote
  #4 (permalink)  
Old 03-18-09, 13:14
rameshj6 rameshj6 is offline
Registered User
  
Join Date: Oct 2008
Posts: 51
query and access plan
Please find the Explain Plan..............
Last edited by rameshj6; 03-18-09 at 13:20.
Reply With Quote
  #5 (permalink)  
Old 03-18-09, 13:26
rameshj6 rameshj6 is offline
Registered User
  
Join Date: Oct 2008
Posts: 51
Cool
Code:
==================== STATEMENT ==========================================

	Isolation Level          = Cursor Stability
	Blocking                 = Block Unambiguous Cursors
	Query Optimization Class = 7

	Partition Parallel       = No
	Intra-Partition Parallel = No

	SQL Path                 = "SYSIBM", "SYSFUN", "SYSPROC", "SYSIBMADM", 
                                   "CRIDWE"


Statement Isolation Level = Uncommitted Read

Section Code Page = 1208

Estimated Cost = 141769.031250
Estimated Cardinality = 202583.484375

Access Table Name = ANLYSIS.DIM_GEO_RME  ID = 14,31
|  #Columns = 3
|  Relation Scan
|  |  Prefetch: Eligible
Isolation Level: Uncommitted Read
|  Lock Intents
|  |  Table: Intent None
|  |  Row  : None
|  Sargable Predicate(s)
|  |  #Predicates = 1
|  |  Process Build Table for Hash Join
Hash Join
|  Estimated Build Size: 8000   
|  Estimated Probe Size: 1776000
|  Access Table Name = ANLYSIS.DIM_CST_LCL  ID = 14,19
|  |  Index Scan:  Name = ANLYSIS.SRC_SYS_ID_DIM_CST_LCL_IDX  ID = 3
|  |  |  Regular Index (Not Clustered)
|  |  |  Index Columns:
|  |  |  |  1: SRC_SYS_ID (Ascending)
|  |  #Columns = 0
|  |  #Key Columns = 1
|  |  |  Start Key: Inclusive Value
|  |  |  |  |  1: '1'
|  |  |  Stop Key: Inclusive Value
|  |  |  |  |  1: '1'
|  |  Index-Only Access
|  |  Index Prefetch: Eligible 15
Isolation Level: Uncommitted Read
|  |  Lock Intents
|  |  |  Table: Intent None
|  |  |  Row  : None
|  |  Sargable Index Predicate(s)
|  |  |  Insert Into Sorted Temp Table  ID = t1
|  |  |  |  #Columns = 1
|  |  |  |  #Sort Key Columns = 1
|  |  |  |  |  Key 1: (Ascending)
|  |  |  |  Sortheap Allocation Parameters:
|  |  |  |  |  #Rows     = 27058.000000
|  |  |  |  |  Row Width = 16
|  |  |  |  Piped
|  |  |  |  Duplicate Elimination
|  Sorted Temp Table Completion  ID = t1
|  List Prefetch Preparation
|  |  Access Table Name = ANLYSIS.DIM_CST_LCL  ID = 14,19
|  |  |  #Columns = 6
|  |  |  Fetch Using Prefetched List
|  |  |  |  Prefetch: 1414 Pages
Isolation Level: Uncommitted Read
|  |  |  Lock Intents
|  |  |  |  Table: Intent None
|  |  |  |  Row  : None
|  |  |  Sargable Predicate(s)
|  |  |  |  #Predicates = 1
|  |  |  |  Process Probe Table for Hash Join
|  Residual Predicate(s)
|  |  #Predicates = 2
Hash Join
|  Estimated Build Size: 2016000
|  Estimated Probe Size: 72752000
|  Access Table Name = ANLYSIS.DIM_PD_LCL  ID = 14,14
|  |  Index Scan:  Name = ANLYSIS.SRC_SYS_ID_DIM_PD_LCL_IDX  ID = 6
|  |  |  Regular Index (Not Clustered)
|  |  |  Index Columns:
|  |  |  |  1: SRC_SYS_ID (Ascending)
|  |  #Columns = 10
|  |  #Key Columns = 1
|  |  |  Start Key: Inclusive Value
|  |  |  |  |  1: '1'
|  |  |  Stop Key: Inclusive Value
|  |  |  |  |  1: '1'
|  |  Data Prefetch: Eligible 3534
|  |  Index Prefetch: Eligible 3534
Isolation Level: Uncommitted Read
|  |  Lock Intents
|  |  |  Table: Intent None
|  |  |  Row  : None
|  |  Sargable Predicate(s)
|  |  |  #Predicates = 1
|  |  |  Process Build Table for Hash Join
|  Hash Join
|  |  Early Out: Single Match Per Outer Row
|  |  Estimated Build Size: 640000 
|  |  Estimated Probe Size: 835008000
|  |  Access Table Name = ANLYSIS.ANL_SALES_WTD  ID = 5,42
|  |  |  #Columns = 10
|  |  |  Relation Scan
|  |  |  |  Prefetch: Eligible
Isolation Level: Uncommitted Read
|  |  |  Lock Intents
|  |  |  |  Table: Intent None
|  |  |  |  Row  : None
|  |  |  Sargable Predicate(s)
|  |  |  |  Process Probe Table for Hash Join
Insert Into Sorted Temp Table  ID = t2
|  #Columns = 17
|  #Sort Key Columns = 12
|  |  Key 1: CNTRY_CD (Ascending)
|  |  Key 2: (Ascending)
|  |  Key 3: EASI_PD_GRP (Ascending)
|  |  Key 4: EASI_PD_CAT (Ascending)
|  |  Key 5: EASI_SUB_CAT (Ascending)
|  |  Key 6: PD_FRNT_TRTMNT_CD (Ascending)
|  |  Key 7: EASI_MKT (Ascending)
|  |  Key 8: PD_FRNT_CD (Ascending)
|  |  Key 9: (Ascending)
|  |  Key 10: EXCT_IND (Ascending)
|  |  Key 11: FISCAL_YEAR (Ascending)
|  |  Key 12: FISCAL_WEEK_NBR (Ascending)
|  Sortheap Allocation Parameters:
|  |  #Rows     = 202584.000000
|  |  Row Width = 304
|  Piped
Access Temp Table  ID = t2
|  #Columns = 17
|  Relation Scan
|  |  Prefetch: Eligible
Return Data to Application
|  #Columns = 22

End of section


Optimizer Plan:

                                              RETURN 
                                              (   1) 
                                                |    
                                              TBSCAN 
                                              (   2) 
                                                |    
                                               SORT  
                                              (   3) 
                                                |    
                                              HSJOIN 
                                              (   4) 
                           /-----------------/      \-----------------\
                     HSJOIN                                            HSJOIN 
                     (   5)                                            (   9) 
           /--------/      \--------\                               /-/      \
     TBSCAN                          FETCH                    FETCH            TBSCAN 
     (   6)                          (   7)                   (----)           (  14) 
       |                        /---/      \                 /      \            |    
 Table:                   IXSCAN           Table:      RIDSCN  Table:       Table:      
 ANLYSIS                  (   7)           ANLYSIS     (  11)  ANLYSIS      ANLYSIS     
 ANL_SALES_WTD              |              DIM_PD_LCL    |     DIM_CST_LCL  DIM_GEO_RME 
                Index:                                  SORT  
                ANLYSIS                                (  12) 
                SRC_SYS_ID_DIM_PD_LCL_IDX                |    
                                                       IXSCAN 
                                                       (  13) 
                                                      /      \---\
                                      Index:                      Table:      
                                      ANLYSIS                     ANLYSIS     
                                      SRC_SYS_ID_DIM_CST_LCL_IDX  DIM_CST_LCL
Last edited by Pat Phelan; 03-18-09 at 14:04. Reason: Added code blocks to improve formatting
Reply With Quote
  #6 (permalink)  
Old 03-18-09, 14:04
n_i n_i is offline
:-)
  
Join Date: Jun 2003
Location: Toronto, Canada
Posts: 4,449
Use the db2advis tool - it may suggest some indexes.
Reply With Quote
  #7 (permalink)  
Old 03-18-09, 14:22
tonkuma tonkuma is offline
Registered User
  
Join Date: Feb 2008
Location: Japan
Posts: 2,193
I'm sorry, I was too late.
You already used [code] tag.
Reply With Quote
  #8 (permalink)  
Old 03-18-09, 14:28
rameshj6 rameshj6 is offline
Registered User
  
Join Date: Oct 2008
Posts: 51
Quote:
Originally Posted by n_i
Use the db2advis tool - it may suggest some indexes.
I done evrything...........Is there any chance to change the parameters for increasing the performance of a query
Reply With Quote
  #9 (permalink)  
Old 03-18-09, 14:36
ARWinner ARWinner is offline
Registered User
  
Join Date: Jan 2003
Posts: 3,575
Quote:
Originally Posted by rameshj6
I done evrything...........Is there any chance to change the parameters for increasing the performance of a query
Not really, it is all about the access plan. You access plan is showing 2 table-scans. With the little information you gave, I would look how you are accessing those tables.

Andy
Reply With Quote
  #10 (permalink)  
Old 03-18-09, 16:08
rameshj6 rameshj6 is offline
Registered User
  
Join Date: Oct 2008
Posts: 51
Cool
select "DIM_GEO_RME_VW_Cust_Anl_Sales"."CNTRY_CD" "Country_Code" , "DIM_CST_LCL_VW_Anl_Sales"."CST_CODE" || '-' || "DIM_CST_LCL_VW_Anl_Sales"."CST_NM" "Customer" , "DIM_PD_LCL_VW_Anl_Sales"."EASI_PD_GRP" "Product_Group_Code" , "DIM_PD_LCL_VW_Anl_Sales"."EASI_PD_CAT" "Product_Category_1_Code" , "DIM_PD_LCL_VW_Anl_Sales"."EASI_SUB_CAT" "Product_Category_2_Code" , "DIM_PD_LCL_VW_Anl_Sales"."PD_FRNT_TRTMNT_CD" "Treatment_Code" , "DIM_PD_LCL_VW_Anl_Sales"."EASI_MKT" "Marketing_Code" , "DIM_PD_LCL_VW_Anl_Sales"."PD_FRNT_CD" "Front_Code" , "DIM_PD_LCL_VW_Anl_Sales"."PD_CD" || '-' || "DIM_PD_LCL_VW_Anl_Sales"."PD_NM" "Product" , "ANL_SALES_WTD"."EXCT_IND" "Exact_Indicator" , "ANL_SALES_WTD"."FISCAL_YEAR" "Year11" , "ANL_SALES_WTD"."FISCAL_WEEK_NBR" "Week_Number" , (coalesce("ANL_SALES_WTD"."SAL_VOL_SQM", 0)) "Sales_Volume_SQM" , (coalesce("ANL_SALES_WTD"."SALE_VAL_EUR", 0)) "Sales_Value_Euro" , (coalesce("ANL_SALES_WTD"."SALE_VAL_LV_CNTRY", 0)) "Sales_Value_LV_Country" , (coalesce("ANL_SALES_WTD"."MAT_COST_EUR", 0)) "Material_Cost_Euro" , (coalesce("ANL_SALES_WTD"."MAT_COST_LV_CNTRY", 0)) "Material_Cost_LV_Country" , sum((coalesce("ANL_SALES_WTD"."SAL_VOL_SQM", 0))) over (partition by "DIM_GEO_RME_VW_Cust_Anl_Sales"."CNTRY_CD", "DIM_CST_LCL_VW_Anl_Sales"."CST_CODE" || '-' || "DIM_CST_LCL_VW_Anl_Sales"."CST_NM", "DIM_PD_LCL_VW_Anl_Sales"."EASI_PD_GRP", "DIM_PD_LCL_VW_Anl_Sales"."EASI_PD_CAT", "DIM_PD_LCL_VW_Anl_Sales"."EASI_SUB_CAT", "DIM_PD_LCL_VW_Anl_Sales"."PD_FRNT_TRTMNT_CD", "DIM_PD_LCL_VW_Anl_Sales"."EASI_MKT", "DIM_PD_LCL_VW_Anl_Sales"."PD_FRNT_CD", "DIM_PD_LCL_VW_Anl_Sales"."PD_CD" || '-' || "DIM_PD_LCL_VW_Anl_Sales"."PD_NM", "ANL_SALES_WTD"."EXCT_IND", "ANL_SALES_WTD"."FISCAL_YEAR"
order by "DIM_GEO_RME_VW_Cust_Anl_Sales"."CNTRY_CD" asc , "DIM_CST_LCL_VW_Anl_Sales"."CST_CODE" || '-' || "DIM_CST_LCL_VW_Anl_Sales"."CST_NM" asc , "DIM_PD_LCL_VW_Anl_Sales"."EASI_PD_GRP" asc , "DIM_PD_LCL_VW_Anl_Sales"."EASI_PD_CAT" asc , "DIM_PD_LCL_VW_Anl_Sales"."EASI_SUB_CAT" asc , "DIM_PD_LCL_VW_Anl_Sales"."PD_FRNT_TRTMNT_CD" asc , "DIM_PD_LCL_VW_Anl_Sales"."EASI_MKT" asc , "DIM_PD_LCL_VW_Anl_Sales"."PD_FRNT_CD" asc , "DIM_PD_LCL_VW_Anl_Sales"."PD_CD" || '-' || "DIM_PD_LCL_VW_Anl_Sales"."PD_NM" asc , "ANL_SALES_WTD"."EXCT_IND" asc , "ANL_SALES_WTD"."FISCAL_YEAR" asc , "ANL_SALES_WTD"."FISCAL_WEEK_NBR" asc rows unbounded preceding) "Sales_Volume_SQM_YTD" , sum((coalesce("ANL_SALES_WTD"."SALE_VAL_EUR", 0))) over (partition by "DIM_GEO_RME_VW_Cust_Anl_Sales"."CNTRY_CD", "DIM_CST_LCL_VW_Anl_Sales"."CST_CODE" || '-' || "DIM_CST_LCL_VW_Anl_Sales"."CST_NM", "DIM_PD_LCL_VW_Anl_Sales"."EASI_PD_GRP", "DIM_PD_LCL_VW_Anl_Sales"."EASI_PD_CAT", "DIM_PD_LCL_VW_Anl_Sales"."EASI_SUB_CAT", "DIM_PD_LCL_VW_Anl_Sales"."PD_FRNT_TRTMNT_CD", "DIM_PD_LCL_VW_Anl_Sales"."EASI_MKT", "DIM_PD_LCL_VW_Anl_Sales"."PD_FRNT_CD", "DIM_PD_LCL_VW_Anl_Sales"."PD_CD" || '-' || "DIM_PD_LCL_VW_Anl_Sales"."PD_NM", "ANL_SALES_WTD"."EXCT_IND", "ANL_SALES_WTD"."FISCAL_YEAR"
order by "DIM_GEO_RME_VW_Cust_Anl_Sales"."CNTRY_CD" asc , "DIM_CST_LCL_VW_Anl_Sales"."CST_CODE" || '-' || "DIM_CST_LCL_VW_Anl_Sales"."CST_NM" asc , "DIM_PD_LCL_VW_Anl_Sales"."EASI_PD_GRP" asc , "DIM_PD_LCL_VW_Anl_Sales"."EASI_PD_CAT" asc , "DIM_PD_LCL_VW_Anl_Sales"."EASI_SUB_CAT" asc , "DIM_PD_LCL_VW_Anl_Sales"."PD_FRNT_TRTMNT_CD" asc , "DIM_PD_LCL_VW_Anl_Sales"."EASI_MKT" asc , "DIM_PD_LCL_VW_Anl_Sales"."PD_FRNT_CD" asc , "DIM_PD_LCL_VW_Anl_Sales"."PD_CD" || '-' || "DIM_PD_LCL_VW_Anl_Sales"."PD_NM" asc , "ANL_SALES_WTD"."EXCT_IND" asc , "ANL_SALES_WTD"."FISCAL_YEAR" asc , "ANL_SALES_WTD"."FISCAL_WEEK_NBR" asc rows unbounded preceding) "Sales_Value_Euro_YTD" , sum((coalesce("ANL_SALES_WTD"."SALE_VAL_LV_CNTRY", 0))) over (partition by "DIM_GEO_RME_VW_Cust_Anl_Sales"."CNTRY_CD", "DIM_CST_LCL_VW_Anl_Sales"."CST_CODE" || '-' || "DIM_CST_LCL_VW_Anl_Sales"."CST_NM", "DIM_PD_LCL_VW_Anl_Sales"."EASI_PD_GRP", "DIM_PD_LCL_VW_Anl_Sales"."EASI_PD_CAT", "DIM_PD_LCL_VW_Anl_Sales"."EASI_SUB_CAT", "DIM_PD_LCL_VW_Anl_Sales"."PD_FRNT_TRTMNT_CD", "DIM_PD_LCL_VW_Anl_Sales"."EASI_MKT", "DIM_PD_LCL_VW_Anl_Sales"."PD_FRNT_CD", "DIM_PD_LCL_VW_Anl_Sales"."PD_CD" || '-' || "DIM_PD_LCL_VW_Anl_Sales"."PD_NM", "ANL_SALES_WTD"."EXCT_IND", "ANL_SALES_WTD"."FISCAL_YEAR"
order by "DIM_GEO_RME_VW_Cust_Anl_Sales"."CNTRY_CD" asc , "DIM_CST_LCL_VW_Anl_Sales"."CST_CODE" || '-' || "DIM_CST_LCL_VW_Anl_Sales"."CST_NM" asc , "DIM_PD_LCL_VW_Anl_Sales"."EASI_PD_GRP" asc , "DIM_PD_LCL_VW_Anl_Sales"."EASI_PD_CAT" asc , "DIM_PD_LCL_VW_Anl_Sales"."EASI_SUB_CAT" asc , "DIM_PD_LCL_VW_Anl_Sales"."PD_FRNT_TRTMNT_CD" asc , "DIM_PD_LCL_VW_Anl_Sales"."EASI_MKT" asc , "DIM_PD_LCL_VW_Anl_Sales"."PD_FRNT_CD" asc , "DIM_PD_LCL_VW_Anl_Sales"."PD_CD" || '-' || "DIM_PD_LCL_VW_Anl_Sales"."PD_NM" asc , "ANL_SALES_WTD"."EXCT_IND" asc , "ANL_SALES_WTD"."FISCAL_YEAR" asc , "ANL_SALES_WTD"."FISCAL_WEEK_NBR" asc rows unbounded preceding) "Sales_Value_LV_Country_YTD" , sum((coalesce("ANL_SALES_WTD"."MAT_COST_EUR", 0))) over (partition by "DIM_GEO_RME_VW_Cust_Anl_Sales"."CNTRY_CD", "DIM_CST_LCL_VW_Anl_Sales"."CST_CODE" || '-' || "DIM_CST_LCL_VW_Anl_Sales"."CST_NM", "DIM_PD_LCL_VW_Anl_Sales"."EASI_PD_GRP", "DIM_PD_LCL_VW_Anl_Sales"."EASI_PD_CAT", "DIM_PD_LCL_VW_Anl_Sales"."EASI_SUB_CAT", "DIM_PD_LCL_VW_Anl_Sales"."PD_FRNT_TRTMNT_CD", "DIM_PD_LCL_VW_Anl_Sales"."EASI_MKT", "DIM_PD_LCL_VW_Anl_Sales"."PD_FRNT_CD", "DIM_PD_LCL_VW_Anl_Sales"."PD_CD" || '-' || "DIM_PD_LCL_VW_Anl_Sales"."PD_NM", "ANL_SALES_WTD"."EXCT_IND", "ANL_SALES_WTD"."FISCAL_YEAR"
order by "DIM_GEO_RME_VW_Cust_Anl_Sales"."CNTRY_CD" asc , "DIM_CST_LCL_VW_Anl_Sales"."CST_CODE" || '-' || "DIM_CST_LCL_VW_Anl_Sales"."CST_NM" asc , "DIM_PD_LCL_VW_Anl_Sales"."EASI_PD_GRP" asc , "DIM_PD_LCL_VW_Anl_Sales"."EASI_PD_CAT" asc , "DIM_PD_LCL_VW_Anl_Sales"."EASI_SUB_CAT" asc , "DIM_PD_LCL_VW_Anl_Sales"."PD_FRNT_TRTMNT_CD" asc , "DIM_PD_LCL_VW_Anl_Sales"."EASI_MKT" asc , "DIM_PD_LCL_VW_Anl_Sales"."PD_FRNT_CD" asc , "DIM_PD_LCL_VW_Anl_Sales"."PD_CD" || '-' || "DIM_PD_LCL_VW_Anl_Sales"."PD_NM" asc , "ANL_SALES_WTD"."EXCT_IND" asc , "ANL_SALES_WTD"."FISCAL_YEAR" asc , "ANL_SALES_WTD"."FISCAL_WEEK_NBR" asc rows unbounded preceding) "Material_Cost_Euro_YTD" , sum((coalesce("ANL_SALES_WTD"."MAT_COST_LV_CNTRY", 0))) over (partition by "DIM_GEO_RME_VW_Cust_Anl_Sales"."CNTRY_CD", "DIM_CST_LCL_VW_Anl_Sales"."CST_CODE" || '-' || "DIM_CST_LCL_VW_Anl_Sales"."CST_NM", "DIM_PD_LCL_VW_Anl_Sales"."EASI_PD_GRP", "DIM_PD_LCL_VW_Anl_Sales"."EASI_PD_CAT", "DIM_PD_LCL_VW_Anl_Sales"."EASI_SUB_CAT", "DIM_PD_LCL_VW_Anl_Sales"."PD_FRNT_TRTMNT_CD", "DIM_PD_LCL_VW_Anl_Sales"."EASI_MKT", "DIM_PD_LCL_VW_Anl_Sales"."PD_FRNT_CD", "DIM_PD_LCL_VW_Anl_Sales"."PD_CD" || '-' || "DIM_PD_LCL_VW_Anl_Sales"."PD_NM", "ANL_SALES_WTD"."EXCT_IND", "ANL_SALES_WTD"."FISCAL_YEAR"
order by "DIM_GEO_RME_VW_Cust_Anl_Sales"."CNTRY_CD" asc , "DIM_CST_LCL_VW_Anl_Sales"."CST_CODE" || '-' || "DIM_CST_LCL_VW_Anl_Sales"."CST_NM" asc , "DIM_PD_LCL_VW_Anl_Sales"."EASI_PD_GRP" asc , "DIM_PD_LCL_VW_Anl_Sales"."EASI_PD_CAT" asc , "DIM_PD_LCL_VW_Anl_Sales"."EASI_SUB_CAT" asc , "DIM_PD_LCL_VW_Anl_Sales"."PD_FRNT_TRTMNT_CD" asc , "DIM_PD_LCL_VW_Anl_Sales"."EASI_MKT" asc , "DIM_PD_LCL_VW_Anl_Sales"."PD_FRNT_CD" asc , "DIM_PD_LCL_VW_Anl_Sales"."PD_CD" || '-' || "DIM_PD_LCL_VW_Anl_Sales"."PD_NM" asc , "ANL_SALES_WTD"."EXCT_IND" asc , "ANL_SALES_WTD"."FISCAL_YEAR" asc , "ANL_SALES_WTD"."FISCAL_WEEK_NBR" asc rows unbounded preceding) "Material_Cost_LV_Country_YTD"
from "ANLYSIS"."DIM_GEO_RME_VW" "DIM_GEO_RME_VW_Cust_Anl_Sales", "ANLYSIS"."DIM_CST_LCL_VW" "DIM_CST_LCL_VW_Anl_Sales", "ANLYSIS"."DIM_PD_LCL_VW" "DIM_PD_LCL_VW_Anl_Sales", "ANLYSIS"."ANL_SALES_WTD" "ANL_SALES_WTD"
where "DIM_PD_LCL_VW_Anl_Sales"."SRC_SYS_ID" = '1' and "DIM_CST_LCL_VW_Anl_Sales"."SRC_SYS_ID" = '1' and "DIM_GEO_RME_VW_Cust_Anl_Sales"."SRC_SYS_ID" = '1' and NOT ("DIM_CST_LCL_VW_Anl_Sales"."CST_CMPNY_TP" in ('I', 'S') and "DIM_GEO_RME_VW_Cust_Anl_Sales"."FINCL_SLS_AREA_CD " = 'A') and "DIM_PD_LCL_VW_Anl_Sales"."EASI_PD_GRP" <> 'P' and "DIM_CST_LCL_VW_Anl_Sales"."EASI_CNTRY_CD" = "DIM_GEO_RME_VW_Cust_Anl_Sales"."CNTRY_CD" and "DIM_CST_LCL_VW_Anl_Sales"."CST_ID" = "ANL_SALES_WTD"."CST_ID" and "DIM_PD_LCL_VW_Anl_Sales"."PD_ID" = "ANL_SALES_WTD"."PD_ID"
order by "Country_Code" asc , "Customer" asc , "Product_Group_Code" asc , "Product_Category_1_Code" asc , "Product_Category_2_Code" asc , "Treatment_Code" asc , "Marketing_Code" asc , "Front_Code" asc , "Product" asc , "Exact_Indicator" asc , "Year11" asc , "Week_Number" asc with ur @
Reply With Quote
  #11 (permalink)  
Old 03-18-09, 16:45
ARWinner ARWinner is offline
Registered User
  
Join Date: Jan 2003
Posts: 3,575
How about making it easier on those of us who are trying to help you. Format the query so that it is easy to read.

Andy
Reply With Quote
  #12 (permalink)  
Old 03-19-09, 08:48
rameshj6 rameshj6 is offline
Registered User
  
Join Date: Oct 2008
Posts: 51
Cool
Please find the attachment for query
Attached Files
File Type: txt query_17_Mar.txt (8.3 KB, 40 views)
Reply With Quote
  #13 (permalink)  
Old 03-19-09, 09:06
ARWinner ARWinner is offline
Registered User
  
Join Date: Jan 2003
Posts: 3,575
Quote:
Originally Posted by rameshj6
Please find the attachment for query
It is still not very readable. Hint: keep expressions together and try to keep each line to under 80 characters and use indentation and use explicit joins.

Andy
Reply With Quote
  #14 (permalink)  
Old 03-19-09, 12:22
rameshj6 rameshj6 is offline
Registered User
  
Join Date: Oct 2008
Posts: 51
Cool
Can you please check the attachment now,I think it is good now
Attached Files
File Type: txt query.txt (10.5 KB, 57 views)
Reply With Quote
  #15 (permalink)  
Old 03-19-09, 12:25
rameshj6 rameshj6 is offline
Registered User
  
Join Date: Oct 2008
Posts: 51
Cool
In addition i ran db2advise tool, Please find it below


Using user id as default schema name. Use -n option to specify schema
execution started at timestamp 2009-03-19-08.02.21.841283
found [1] SQL statements from the input file
Recommending indexes...
total disk space needed for initial set [ 4.459] MB
total disk space constrained to [17346.194] MB
Trying variations of the solution set.
Optimization finished.
2 indexes in current solution
[141769.0000] timerons (without recommendations)
[140863.0000] timerons (with current solution)
[0.64%] improvement


--
--
-- LIST OF RECOMMENDED INDEXES
-- ===========================
-- index[1], 3.251MB
CREATE INDEX "CRIDWE "."IDX903191503580000" ON "ANLYSIS "."DIM_PD_LCL"
("SRC_SYS_ID" ASC, "EASI_PD_GRP" ASC, "EASI_PD_CAT"
ASC, "EASI_SUB_CAT" ASC, "PD_FRNT_TRTMNT_CD" ASC,
"EASI_MKT" ASC, "PD_FRNT_CD" ASC, "PD_NM" ASC, "PD_CD"
ASC, "PD_ID" ASC) ALLOW REVERSE SCANS ;
COMMIT WORK ;
RUNSTATS ON TABLE "ANLYSIS "."DIM_PD_LCL" FOR INDEX "CRIDWE "."IDX903191503580000" ;
COMMIT WORK ;
-- index[2], 1.208MB
CREATE INDEX "CRIDWE "."IDX903191502490000" ON "ANLYSIS "."DIM_CST_LCL"
("SRC_SYS_ID" ASC, "EASI_CNTRY_CD" ASC, "CST_CMPNY_TP"
ASC, "CST_CODE" ASC, "CST_NM" ASC, "CST_ID" ASC) ALLOW
REVERSE SCANS ;
COMMIT WORK ;
RUNSTATS ON TABLE "ANLYSIS "."DIM_CST_LCL" FOR INDEX "CRIDWE "."IDX903191502490000" ;
COMMIT WORK ;
Reply With Quote
Reply
Page 1 of 3 1 23

« Previous Thread | Next Thread »
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


 
 
 
Home  |   Register  |   Get New  |   FAQ's  |   Sitemap

Powered by vBulletin
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.