10 Replies Latest reply on Dec 8, 2012 2:40 AM by rp0428

    gtt taking very long - same thing in table runs fast why?

    907388
      Hi All,

      I have a report which is based off GTT. The reason we went for GTT is that process for each region can happen in parallel and users can run multiple reports. But it seems like when there is GTT report takes time (just this, other reports with GTT are running fine).

      Once we CONVERT gtt to actual tables, it is running in less than five minutes.

      Is there a liimitation with GTT not being able to process very large data or reading through large record set over 15 million?

      Since processes can happen in parrallel, if GTT will not work can we use dynamic tables that are dropped and recreated each time? If the report uses dynamic sql - can we create one table for EACH session/

      I know reporting tbale is the long term option but for now we would like to go with dynamic tables or gtt.

      Regards
        • 1. Re: gtt taking very long - same thing in table runs fast why?
          sb92075
          904385 wrote:
          Hi All,

          I have a report which is based off GTT. The reason we went for GTT is that process for each region can happen in parallel and users can run multiple reports. But it seems like when there is GTT report takes time (just this, other reports with GTT are running fine).

          Once we CONVERT gtt to actual tables, it is running in less than five minutes.

          Is there a liimitation with GTT not being able to process very large data or reading through large record set over 15 million?

          Since processes can happen in parrallel, if GTT will not work can we use dynamic tables that are dropped and recreated each time? If the report uses dynamic sql - can we create one table for EACH session/

          I know reporting tbale is the long term option but for now we would like to go with dynamic tables or gtt.

          Regards
          Does GTT have any index?
          CBO make better choices when table & index statistics are correct.
          post EXPLAIN PLAN for both fast & slow SQL
          • 2. Re: gtt taking very long - same thing in table runs fast why?
            907388
            Yes stats are all updated..DBA checked that.

            Do you think GTT has limitations when dealing with large data sets?
            • 3. Re: gtt taking very long - same thing in table runs fast why?
              907388
              Stats are updated and GTT had indexes on them - full schema nalyze was done.

              When SAME GTT is converted into TAble it runs fine. Why do you think? Are there limitations of using GTT?
              • 4. Re: gtt taking very long - same thing in table runs fast why?
                907388
                Hi,

                For exactly the SAME parameters, the GTT takes longer than actual table.

                Stats are updated and GTT had indexes on them - full schema analyze was done.

                I searched and saw something about excessive redo and undo table space issues with GTT etc, Are there any bugs with GTT that we should be aware of?

                Regards
                • 5. Re: gtt taking very long - same thing in table runs fast why?
                  sb92075
                  post EXPLAIN PLAN for both fast & slow SQL
                  • 6. Re: gtt taking very long - same thing in table runs fast why?
                    rp0428
                    >
                    Are there any bugs with GTT that we should be aware of?
                    >
                    You mean for the version of Oracle that you are using that you never told anyone about?

                    No.
                    >
                    Are there limitations of using GTT?
                    >
                    No.

                    And we can't analyze ddl, code or execution plans that we can't see so unless you post some specific information you won't be able to get any specific answers.
                    • 7. Re: gtt taking very long - same thing in table runs fast why?
                      Nikolay Savvinov
                      Hi
                      904385 wrote:
                      Hi All,

                      I have a report which is based off GTT. The reason we went for GTT is that process for each region can happen in parallel and users can run multiple reports. But it seems like when there is GTT report takes time (just this, other reports with GTT are running fine).

                      Once we CONVERT gtt to actual tables, it is running in less than five minutes.

                      Is there a liimitation with GTT not being able to process very large data or reading through large record set over 15 million?
                      There is no such limitation. Observed behavior can be due to TEMP tablespace (GTTs reside in TEMP segments) residing on a slow storage, or some other reasons -- without proper diagnostic information, we cannot tell exactly.

                      Trace your process and see what's taking time.

                      Best regards,
                      Nikolay
                      • 8. Re: gtt taking very long - same thing in table runs fast why?
                        Sven W.
                        904385 wrote:
                        Yes stats are all updated..DBA checked that.
                        How did he/she check that?

                        A GTT holds data only temporarily. So the stats must be created after the data is loaded into the temp table.
                        If the DBA looks from another session he will see no data in the GTT and might create statistics that are wrong, but that will look ok for the DBA session.
                        And if you put new data in it in second users session you might even want to create new stats in this session. But the stats will be used in each session, as soon as they are available. So they might be good for one session, but are bad for another session using the same GTT with different data.

                        A solution can be to use dynamic sampling for the selects that run on the GTT.

                        See also this OTN article from Tom: http://www.google.de/search?q=oracle+11g+gtt+statistics
                        The second use for dynamic sampling is with global temporary tables. Often global temporary tables do not have statistics, and dynamic sampling can provide the optimizer with information about these tables. Your application would load the global temporary table, and the first hard parse of any query that utilized the temporary table would dynamically sample it to ascertain the correct size of the temporary table.
                        Example how to use dynamic sampling:
                        select /*+ dynamic_sampling (myGTT 4) */ 
                                col1, col2, col3 
                        from myGTT where col1=42;
                        4 in this case is the dynamic sampling level .

                        The different levels are also explained in the article that I linked above.
                        • 9. Re: gtt taking very long - same thing in table runs fast why?
                          907388
                          Hi,

                          Three questions:

                          1.
                          I have put in stats analyze for EACH GTT within the sp so users get fresh stats. However, would having stats for EACH Gtt within sp slow execution of the report?

                          I have put in dbms_stats within sp..

                          Not sure if dynoamic samplying will help but will try that later if stats dont work..


                          2).

                          AFTER INSERT of EACH GTT
                          and BEFORE FINAL data joins ALL GTT together. I have ALSO added ON COMMIT PRESERVE ROWS but it is DUPLICATING the data -what could be the reason?


                          3).

                          How do I Trace the process to figure out at RUNTIME to figure out how much table space is being used?

                          Please advise asap!

                          Thanks

                          Edited by: 904385 on Dec 7, 2012 11:37 AM
                          • 10. Re: gtt taking very long - same thing in table runs fast why?
                            rp0428
                            I replied to all of those questions in my first response above.
                            >
                            Are there any bugs with GTT that we should be aware of?
                            >
                            You mean for the version of Oracle that you are using that you never told anyone about?

                            No.
                            >
                            Are there limitations of using GTT?
                            >
                            No.

                            And we can't analyze ddl, code or execution plans that we can't see so unless you post some specific information you won't be able to get any specific answers.

                            Those same answers still apply. You haven't provide any specific information on which to base a specific answer.