Home » RDBMS Server » Performance Tuning » Query slow (Oracle 9i)
Query slow [message #293950] Tue, 15 January 2008 14:12 Go to next message
gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen
Senior Member
user reports a perfomance issue. below running query runs slower in production but fast in development.


SELECT DISTINCT template_adj_id,
                pcms_template_adj.wp_adj_id template_wp_adj_id,
                pcms_adj.wp_adj_id curr_wp_adj_id, pcms_template_adj.err_cd,
                pcms_template_adj.mntnc_action_cd, pcms_adj.slsrp_num,
                pcms_usage.curr_wc_adj_rule
           FROM pcms_adj, pcms_template_adj, pcms_usage
          WHERE pcms_template_adj.wp_source_id = wp_cust_num(+)
            AND pcms_template_adj.wp_mtl_num = pcms_adj.wp_mtl_num(+)
            AND pcms_template_adj.source_type IN (1, 2)
            AND pcms_adj.wp_adj_id = pcms_usage.curr_wp_adj_id(+)
            AND 'M' = pcms_usage.curr_wc_adj_rule(+)
            AND (   (    pcms_adj.slsrp_num IS NOT NULL
                     AND pcms_adj.wp_adj_id IS NOT NULL
                    )
                 OR (pcms_adj.slsrp_num IS NULL AND pcms_adj.wp_adj_id IS NULL
                    )
                )
            AND template_id = 1063



DEVELOPMENT

Operation	Object Name	Rows	Bytes	Cost	Object Node	In/Out	PStart	PStop

SELECT STATEMENT Optimizer Mode=CHOOSE		41  	 	615  	 	      	             	 
  SORT UNIQUE		41  	4 K	615  	 	      	             	 
    NESTED LOOPS OUTER		1 K	133 K	578  	 	      	             	 
      FILTER		  	 	 	 	      	             	 
        NESTED LOOPS OUTER		  	 	 	 	      	             	 
          TABLE ACCESS BY INDEX ROWID	PRCG.PCMS_TEMPLATE_ADJ	1  	86  	1  	 	      	             	 
            INDEX RANGE SCAN	PRCG.PCMS_TEMPLATE_IDX1	42  	 	1  	 	      	             	 
          TABLE ACCESS BY INDEX ROWID	PRCG.PCMS_ADJ	1 K	26 K	1  	 	      	             	 
            INDEX RANGE SCAN	PRCG.PCMS_ADJ_IDX5	41 K	 	2  	 	      	             	 
      TABLE ACCESS BY INDEX ROWID	PRCG.PCMS_USAGE	1  	8  	1  	 	      	             	 
        INDEX RANGE SCAN	PRCG.PCMS_USAGE_IDX1	2  	 	2  	 	      	             	 



PRODUCTION

Operation	Object Name	Rows	Bytes	Cost	Object Node	In/Out	PStart	PStop

SELECT STATEMENT Optimizer Mode=CHOOSE		4 K	 	36569  	 	      	             	 
  SORT UNIQUE		4 K	324 K	36569  	 	      	             	 
    NESTED LOOPS OUTER		4 K	324 K	36451  	 	      	             	 
      FILTER		  	 	 	 	      	             	 
        MERGE JOIN OUTER		  	 	 	 	      	             	 
          SORT JOIN		4 K	174 K	289  	 	      	             	 
            TABLE ACCESS BY INDEX ROWID	PRCG.PCMS_TEMPLATE_ADJ	4 K	174 K	213  	 	      	             	 
              INDEX RANGE SCAN	PRCG.PCMS_TEMPLATE_IDX1	8 K	 	43  	 	      	             	 
          SORT JOIN		958 K	27 M	15893  	 	      	             	 
            TABLE ACCESS FULL	PRCG.PCMS_ADJ	958 K	27 M	5256  	 	      	             	 
      TABLE ACCESS BY INDEX ROWID	PRCG.PCMS_USAGE	1  	8  	5  	 	      	             	 
        INDEX RANGE SCAN	PRCG.PCMS_USAGE_IDX1	2  	 	2  	



As you see, in production, there is a change in execution plan and there is a FULL TABLE SCAn in producion.
i checked the statistics.. is upated everyday.Could someone please tell me what could be he problem here?( QUERY is same in both database)

[Updated on: Tue, 15 January 2008 14:15]

Report message to a moderator

Re: Query slow [message #293952 is a reply to message #293950] Tue, 15 January 2008 14:16 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/84315/74940/
Which of the suggestions have you tried & what were the results?
Re: Query slow [message #293956 is a reply to message #293950] Tue, 15 January 2008 14:23 Go to previous messageGo to next message
gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen
Senior Member
i dono have any prior exp in turing query.. if you could give a hint, may be i can catchup from that..
Re: Query slow [message #293957 is a reply to message #293950] Tue, 15 January 2008 14:26 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
If you can't or won't read & follow the contents of the posted URL, I won't waste my time posting it again here.
Re: Query slow [message #293960 is a reply to message #293950] Tue, 15 January 2008 14:39 Go to previous messageGo to next message
gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen
Senior Member
>>If you can't or won't read & follow the contents of the posted URL

did i say ?
Re: Query slow [message #293972 is a reply to message #293950] Tue, 15 January 2008 16:47 Go to previous message
gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen
Senior Member
i was not actualy considering one table and its index statistics was outdated. analyzed again and that resolved issue.
Previous Topic: Newbie: Query uses index and runs slow
Next Topic: Help
Goto Forum:
  


Current Time: Fri Dec 02 19:01:57 CST 2016

Total time taken to generate the page: 0.21279 seconds