2100 recursive calls
12 db block gets
3689668 consistent gets
3602433 physical reads
0 redo size
10679182 bytes sent via SQL*Net to client
191062 bytes received via SQL*Net from client
27258 SQL*Net roundtrips to/from client
4 sorts (memory)
2 sorts (disk)
408842 rows processed
Message was edited by:
Charles,It might be that the cardinaltiy estimates are incorrect - maybe it has been a while since you collected statistics on the tables and indexes. It might be that Oracle needs to resolve/join the view first, rather than last. It might be that predicates are not being pushed into the view. It might be that hints are needed in the view. It might be that you need to eliminate the view. It might be that you need histograms on some of the columns. It might be... and the list goes on.
Thanks for giving great explanation. Then what is the
next step to resolve this issue.Please let me know.
STAT #2 id=12 cnt=1781 pid=3 pos=2 obj=35205 op='TABLE ACCESS BY INDEX ROWID EDI830_LINES (cr=143 pr=10 pw=0 time=47302 us)'That would tell me that I possibly need to address the cause of that delay, if it is significant (47302/1000000 seconds).
exec dbms_stats.gather_table_stats('<TABLE OWNER>,'TABLE_NAME',CASCADE=>TRUE);for tables it's succeeded. But for VIew CASE i am getting error. What is the solution for this. After that what i have to follow.Please give me idea for this also. Before that i will do what u have mentioned in the above message.
Data WILL NOT be truncated to match the output schema and the output data file may be improperly formatted.but output schema length and input schema lenght are same. but I am getting program failure.Pls give me any idea for this.
How to use HINTS for this query . I have used asI am not sure where you received the suggestion for using the All_ROWS hint. In one of my replies to you, I did suggest to you to try using the leading hint to force the CASE_SIZE view to be resolved first, the results of which will drive into the MASTER table. That may or may not help. One of the problems with the CASE_SIZE view is that you have views that depend on views, and those views depend on other views - the predicates from the tables in the query may not be pushing deep enough into the multiple levels of views. You may need to rewrite the CASE_SIZE view to improve performance, and better yet include the logic of the optimized version of the view as an inline view in your SQL statement. You can start determining the SQL that makes up the view by executing the following when connected to the database as the view owner:
follows.SELECT /*+All_ROWS*/ ,Stmt>what is the next step for checking the increment in
SELECTYou will likely see other tables and views referenced in the SQL statement, continue collecting the SQL statements for the other views referenced.
SELECTTo convert the CASE_SIZE view to an inline view, you would change your original SQL statement to:
SELECTThe one hint that I suggested is:
MAS.PACK_IND = 'N'
AND MAS.ITEM_LEVEL = MAS.TRNS_LEVEL
AND MAS.STATUS = 'A'
AND MAS.FOREIND = 'Y'
AND MAS.ITEM = IM.ITEM
AND IM.LOCATION_TYPE = 'S'
AND MAS.ITEM = CS.ITEM
AND CS.SUPPLY_INDICATOR = 'Y'
AND CS.COUNTRY_INDICATOR = 'Y'
AND IM.LOCATION =WH.WAREHOSE_NO
AND NVL(WH.CLOSE_DATE,'04-APR-9999')>=TO_DATE(&VERSDATE}, 'YYYYMMDD')