1 2 Previous Next 22 Replies Latest reply: Feb 6, 2013 9:44 AM by JustinCave RSS

    Query Performance

    969952
      Hi All,

      I have written the below query. But it's taking very long time. the table is having around 50000 records. but still it's running long time.

      Edited by: 966949 on Feb 6, 2013 6:38 AM
        • 1. Re: Query Performance
          JustinCave
          There is an entry in the FAQ that explains SQL and PL/SQL FAQ. There is a ton of information we'd need before we could begin to help you none of which we have.

          Justin
          • 2. Re: Query Performance
            969952
            Hi,

            Referred all the required documents and ran the query again. But still it's taking long run.

            Help me out if need to add any thing else in the query to improve the performance.

            Thanks.
            • 3. Re: Query Performance
              JustinCave
              Sorry, I don't understand...

              If you read through that, you would have seen information that explains what information we need to be able to assist you. You'll need to post that information here before anyone will be able to suggest anything.

              Justin
              • 4. Re: Query Performance
                969952
                Please have a look into the query which I have posted in this thread and suggest me if any changes required to improve the performance.

                Thanks.
                • 5. Re: Query Performance
                  SomeoneElse
                  OK, so you've gone through all the documents in an hour but you haven't posted any of the results.

                  And are you sure you posted a real, working query? I don't think so. This one should have returned at least two syntax errors:
                  SELECT   E.location_id,E. EMP_id, E.DEPT_id,
                           TRUNC (E.DATED, 'HH24') date_time,
                           E.consumption_flag, AVGSF,
                           DECODE (e.consumption_flag,
                                   'Y', SUM (E.quantity),
                                   AVG (E.measured_quantity)
                                  ) avg_or_consumption
                      FROM e_view E
                  GROUP BY  E.location_id,E. EMP_id, E.DEPT_id,
                           TRUNC (E.DATED, 'HH24),
                  
                  ------------------------------^
                  
                           mt.consumption_flag,
                           AVGSF,
                  
                  --------------^
                  ;
                  • 6. Re: Query Performance
                    969952
                    can you please find the query now.

                    Thanks.
                    • 7. Re: Query Performance
                      6363
                      966949 wrote:
                      Please have a look into the query which I have posted in this thread and suggest me if any changes required to improve the performance.
                      You have a few options -

                      - Truncate all tables used by the query
                      - Replace all tables used in the query with the dual table
                      - Read (this means read all the words in the links) in the link previously posted {message:id=9360003} and follow the instructions regarding what information you need to provide to get any assistance from this forum
                      • 8. Re: Query Performance
                        SomeoneElse
                        can you please find the query now.
                        Nope, same errors.

                        The point here is not the syntax errors themselves. But you're not cutting/pasting an actual working query. How can a query be slow if it can't execute?
                        • 9. Re: Query Performance
                          969952
                          After executing the " select count(*) from <mview name> have received around 10cr records.

                          so need to execute the whole SQL statement as mentioned in the materialized view. SO please help me out to tune the query..
                          • 10. Re: Query Performance
                            JustinCave
                            Huh?

                            Where is a materialized view coming from? That's different than the query you posted.

                            As we've said now multiple times, we need additional information from you in order to help. We have no idea what your tables look like. We have no idea what the query plan looks like. We have no idea what indexes are available. We have no idea what sort of performance you are getting. We have no idea what sort of performance you expect. We have no idea what sort of wait events you are seeing. Without that information, we can't help you. That's why the FAQ exists-- to tell you what information you would need to post in order for us to have the information we need to help you.

                            Posting over and over again asking people to tune your query without posting any of the information that you have now been asked for repeatedly is, unfortunately, not going to cause us to suddenly develop the ability to see your system and guess at how to improve the performance of your query.

                            Justin
                            • 11. Re: Query Performance
                              969952
                              Hi,

                              I ran the below query. Please find the timings of the below MV's and let me know how to decrease the time for the below MV
                              Single Record View
                              As of: 2/5/2013 12:13:53 PM
                              
                              MVIEW_NAME:      HOURLY_SUMMARY_MV
                              START_TIME:      2/4/2013 8:12:50 AM
                              END_TIME:        2/4/2013 9:15:26 PM
                              FULLREFRESHTIM:  46956
                              INCREFRESHTIM:   0
                              Query I ran is :
                              SELECT 
                                 mview_name,
                                 last_refresh_date "START_TIME",
                                 CASE
                                    WHEN fullrefreshtim <> 0 THEN
                                       LAST_REFRESH_DATE + fullrefreshtim/60/60/24
                                    WHEN increfreshtim <> 0 THEN
                                       LAST_REFRESH_DATE + increfreshtim/60/60/24
                                    ELSE
                                       LAST_REFRESH_DATE
                                 END "END_TIME",
                                 fullrefreshtim,
                                 increfreshtim
                              FROM all_mview_analysis
                              WHERE owner='yuo';
                              remaining MV's FULLREFRESHTIM: is max upto 50

                              but for this MV it's too high. Please let me knwo if any information required from my end.

                              Thanks.
                              • 12. Re: Query Performance
                                JustinCave
                                OK. So your problem is that it takes too long to refresh some materialized view.

                                What is the materialized view definition? Is that definition somehow related to the query you posted? Are you doing an incremental refresh? Or a full refresh? Can you do an incremental refresh?

                                Assuming that the materialized view definition is related to the query that you posted, we're still going to need the information that we've repeatedly asked you for (query plans, etc.) that are documented in the FAQ's that we've repeatedly linked to.

                                Justin
                                • 13. Re: Query Performance
                                  969952
                                  Hi,

                                  Here am posting my MV. Please have a look

                                  Edited by: 966949 on Feb 6, 2013 6:39 AM
                                  • 14. Re: Query Performance
                                    JustinCave
                                    Thank you for that.

                                    I assume that you are still working on providing the rest of the information that we've requested.

                                    Justin
                                    1 2 Previous Next