13 Replies Latest reply: Jan 30, 2013 1:17 PM by Matthew Morris RSS

    Oracle Database 11g SQL Tuning courses

    Roger25
      Hi,

      I want to know what's the difference between http://education.oracle.com/pls/web_prod-plq-dad/db_pages.getpage?page_id=609&p_org_id=1001&lang=US&get_params=dc:D52163GC10,p_preview:N and http://education.oracle.com/pls/web_prod-plq-dad/db_pages.getpage?page_id=609&p_org_id=1001&lang=US&get_params=dc:D52163GC20,p_preview:N ? The latter is related to Oracle Database 11g Release 2, and the first one?

      I'm preparing for 1Z0-117 (Oracle Database 11g Release 2: SQL Tuning) exam (which was validated against Release 2), and i can see a suggested course from OU, which is D52163GC10 (so Oracle Database 11g: SQL Tuning Workshop). But i think this is not updated, so it's not related to Oracle DB 11g Release 2. Is it valid for 1Z0-117 preparation?
      So i want to know the differences (topics, etc) between D52163GC20 and D52163GC10.

      Thanks!
        • 1. Re: Oracle Database 11g SQL Tuning courses
          PhHein
          Moved from Java-Certification.
          • 2. Re: Oracle Database 11g SQL Tuning courses
            Matthew Morris
            So i want to know the differences (topics, etc) between D52163GC20 and D52163GC10.
            I pulled up the two URLs from your post, then clicked on the topics for one, cut & pasted into Notepad. I then opened the second URL and did the same. They contain most of the same topics, but the order is quite different. It looks like the Release II is simply an updated version of the firs. It may be updated for 11G Release 2, or it may simply be Release II of the course. I certainly can't see taking both of them given the degree of commonality. The 1Z0-117 test is going to have the very latest information since it's not even production yet, so there doesn't seem to be any reason to even consider taking the earlier SQL Tuning workshop if the purpose is preparing for the exam.
            • 3. Re: Oracle Database 11g SQL Tuning courses
              Roger25
              I have the first course, that's why I ask. Now, I hope i should not buy the release II course in order to prepare for exam. I think the first one is sufficient (regardin SQL Tuning). If there are the same topics (but the order changed), the first course isn't sufficient? I think it is. Now let's be serious, what major differences in SQL Tuning are between Oracle 11gR1 and 11gR2? :) I think they are unsignificant (however, where can i find the new "features" about tuning in release II?)

              Regards
              • 4. Re: Oracle Database 11g SQL Tuning courses
                Brandye Barrington-Oracle
                Actually, I'm seeing that on the exam page http://education.oracle.com/pls/web_prod-plq-dad/db_pages.getpage?page_id=5001&get_params=p_exam_id:1Z0-117, the recommended course is the Release 2 course. It is recommended that you use the Release 2 or GC20 version of the course for prep.

                From what country are you accessing the exam page?

                Regards,
                Brandye Barrington
                Certification Forum Moderator
                • 5. Re: Oracle Database 11g SQL Tuning courses
                  Roger25
                  Romania, but now I see, clicking on that course gives me on the GC20 course page
                  I think it's all about money here (the newer course)... looking at differenced between 11gR1 and 11gR1, I did not find any major changes in SQL tuning between the two
                  • 6. Re: Oracle Database 11g SQL Tuning courses
                    bigdelboy
                    Roger25 wrote:
                    Romania, but now I see, clicking on that course gives me on the GC20 course page
                    I think it's all about money here (the newer course)... looking at differenced between 11gR1 and 11gR1, I did not find any major changes in SQL tuning between the two
                    Its like the difference between Roger22 and Roger25. ;-)
                    • 7. Re: Oracle Database 11g SQL Tuning courses
                      Matthew Morris
                      ... looking at differenced between 11gR1 and 11gR1, I did not find any major changes in SQL tuning between the two
                      Actually, I did. I lined the topics lists of the two up and deleted all topics that were identical or obviously the same topic renamed. That left a considerable number in the Release 2 workshop (below). A number of topics from Release 1 were dropped. By far the largest single common factor in the eliminated topics was HINTs. Oracle is moving away from these, so that makes sense. I might have missed a few more commonalities, but the below list is a fair indicator of what is 'new' in the Release II workshop. They are in alphabetical rather than topical order because that's how I matched up the two workshops to remove the duplicates.

                      Antijoins
                      Automatic SQL Tuning Considerations
                      Automatic SQL Tuning Result Details: Drilldown
                      Automatic SQL Tuning: Result Details
                      Automatic SQL Tuning: Result Summary
                      Automatic Tuning Process
                      Bind Variable Peeking
                      Composite Indexes and Invisible Index
                      Configuring Automatic SQL Tuning
                      Connecting to the Database Instance
                      Count Stop Key Operator
                      Data Modeling, Table Design, Index Design, Using Views, SQL Execution Efficiency, Overview of SQL*Plus & SQL Developer
                      Enterprise Manager and SQL Plan Baselines
                      Equijoins and Nonequijoins
                      Filter operations and Concatenation Operations
                      Forcing Cursor Sharing
                      Hash Join and Cartesian Join
                      Histograms, Frequency Histograms and Histogram Considerations
                      Important Baseline SQL Plan Attributes
                      Index Clustering Factor
                      Index-Organized Tables
                      Inlist Iterator and View Operator
                      Interpreting an Execution Plan
                      Joining the Intermediate Result Set with Dimensions
                      Loading a SQL Plan Baseline Automatically and Purging SQL Management Base Policy
                      Maintaining SQL Performance and SQL Plan Management: Overview
                      Manual Statistics Gathering
                      Min/Max and First Row Operators and Other N-Array Operations
                      Nested Loops Join
                      Nested Loops Join: 11g Implementation
                      Optimizer Join Methods
                      Optimizer: Table and Index Operations
                      Outer Joins
                      Physical Structure
                      Plan Generator
                      Plan Table & AUTOTRACE
                      Possible Recommendations
                      Possible SQL Plan Manageability Scenarios
                      Result Cache Operator
                      Segments, Extents, and Blocks & SYSTEM and SYSAUX Tablespaces
                      Semijoins
                      Session Level Tracing: Example
                      Simplicity in Application Design
                      Sort Merge join
                      Sorting Operators and Buffer Sort Operator
                      SQL Access Advisor Session: Initial Options
                      SQL Access Advisor: Overview
                      SQL Access Advisor: Recommendation Options
                      SQL Access Advisor: Results
                      SQL Access Advisor: Results and Implementation
                      SQL Access Advisor: Schedule and Review
                      SQL Access Advisor: Workload Source
                      SQL Performance Analyzer and SQL Plan Baseline Scenario
                      SQL Performance Management
                      SQL Plan Baseline: Architecture
                      SQL Plan Selection
                      SQL Tuning Loop
                      SQL Tuning Loop, Access Path Analysis and SQL Structure Analysis
                      Star Transformation Hints
                      Star Transformation Plan Examples
                      Table, Index and Column Statistics
                      The CURSOR_SHARING Parameter
                      Transformer & Estimator
                      Tuning SQL Statements Automatically
                      Types of Optimizer Statistics
                      UNION [ALL], INTERSECT, MINUS
                      Use Enterprise Manager to Trace Services
                      Using Automatic SQL Tuning
                      Using SQL Access Advisor
                      Using SQL Tuning Advisor
                      Using the V$SQL_PLAN View
                      • 8. Re: Oracle Database 11g SQL Tuning courses
                        Roger25
                        lol :)
                        Ok, now I found only http://juliandontcheff.wordpress.com/2011/04/24/what-is-new-with-automatic-sql-tuning-in-11-2-0-2/ as a difference (SQL tuning).
                        Others may post other differences too, it will be useful for me (i did not want to buy the release II course, just for some minor changes...)
                        • 9. Re: Oracle Database 11g SQL Tuning courses
                          Roger25
                          "Actually, I did. I lined the topics lists of the two up and deleted all topics that were identical or obviously the same topic renamed. That left a considerable number in the Release 2 workshop (below)."

                          You haven't deleted.. for example "Plan Generator", or "Result Cache Operator", and many other titles are found also in the first course too. I don't think these topic lists are just in release II and not in the first course. So i don't know what do you mean by this list :)

                          also i don't know why HINTS were removed from release II..
                          • 10. Re: Oracle Database 11g SQL Tuning courses
                            Matthew Morris
                            You haven't deleted.. for example "Plan Generator", or "Result Cache Operator", and many other titles are found also in the first course too.
                            I don't see either of these in the course topics listed for Oracle Database 11g: SQL Tuning Workshop:

                            http://education.oracle.com/pls/web_prod-plq-dad/db_pages.getpage?page_id=609&p_org_id=1001&lang=US&get_params=dc:D52163GC10,p_preview:N#tabs-3
                            • 11. Re: Oracle Database 11g SQL Tuning courses
                              Roger25
                              Well, having the D52163GC10, i can see "Plan generator" (look at http://imageshack.us/photo/my-images/201/68423472.jpg/ ).
                              In the contents list of the 3rd chapter (Introduction to the optimizer). Maybe what we see here http://education.oracle.com/pls/web_prod-plq-dad/db_pages.getpage?page_id=609&p_org_id=1001&lang=US&get_params=dc:D52163GC10,p_preview:N#tabs-3 , isn't so detailed (so many of them are present in the real course, but not in the "Course topics" section, from the link above).
                              • 12. Re: Oracle Database 11g SQL Tuning courses
                                Matthew Morris
                                ...Maybe what we see here ... isn't so detailed
                                There's not much I can do about that. Since you have the book, you could take my list as a starting point and remove ones like this to provide a more complete comparison in this thread... for others that might be interested. This certification is likely to become very popular shortly...
                                • 13. Re: Oracle Database 11g SQL Tuning courses
                                  Matthew Morris
                                  ...also i don't know why HINTS were removed from release II..
                                  Just saw this. Oracle seems to be moving away from hints in favor of SQL Profiles. There are a number of issues with using HINTs to tune SQL performance. They are labor-intensive, can't always be used (as with packaged applications), and don't evolve if the data changes such that they are no longer appropriate (among other issues).