Community announcement: SQL Tuning Boot Camp by Maria Colgan (SQL Maria) organized by NoCOUG on February 13 in Pleasanton

Calling Oracle DBAs and application developers in the San Francisco bay area. Insider tip: The event is free if you become a NoCOUG member first. [http://nocoug.wildapricot.org/EmailTracker/EmailTracker.ashx?emailCode=KeDSAolxuPjy09og%2bWQHN8QwrnQ%2bp5KgZG21DPC2CkLjb0L0TKC3cZRWJ2ukR41qi6vMDneVB7nVO0I%2fOEcQYpM3pjcI%2fjxqeJadO6Ggjmw%3d]

[http://NoCOUG.wildapricot.org/resources/Pictures/Conference%20202002/Boot%20Camp.jpg] U.S. Marine Corps Sgt. Andrew Lopez, a drill instructor, provides encouragement to participants during the push-up section of the Boot Camp Challenge at Marine Corps Recruit Depot San Diego, Calif., Oct. 7, 2006.

The following questions will help you decide whether you or somebody you know should attend the SQL Tuning Boot Camp by Maria Colgan (SQL Maria) organized by NoCOUG on February 13 at the Rosewood Commons Conference Center in Pleasanton. The boot camp will start with the basics and finish with the most advanced topics. Seating is very limited so please register ASAP.

1. What is the unit of “cost” in a query execution plan?
2. Why are EXPLAIN PLAN and DBMS_XPLAN.DISPLAY not useful for SQL tuning?
3. What is wrong with the following statement in the SQL Tuning Guide<http://nocoug.wildapricot.org/EmailTracker/LinkTracker.ashx?linkAndRecipientCode=PQQTb5hJMcWLg8ERPOwI84PKkNgz%2fQ69T2jWKCxmv17AS82VybNQ0Gj%2bmviqoJE1qdRk%2b3jWnc9U%2fHWRAOqpj8pug0t8SFBf4Jmynw1QbEE%3d>: “The execution order in EXPLAIN PLAN output begins with the line that is the furthest indented to the right.”
4. In what order does Oracle execute the steps of the following query execution plan?

   ---

|*  4 |     INDEX RANGE SCAN             | EMP_DEPARTMENT_IX |


|   5 |    TABLE ACCESS BY INDEX ROWID   | JOBS              |


|*  6 |     INDEX UNIQUE SCAN            | JOB_ID_PK         |


|   7 |   VIEW                           |                   |

|   8 |    NESTED LOOPS                  |                   |

|   9 |     NESTED LOOPS                 |                   |

|  10 |      NESTED LOOPS                |                   |


|  11 |       TABLE ACCESS BY INDEX ROWID| DEPARTMENTS       |


|* 12 |        INDEX UNIQUE SCAN         | DEPT_ID_PK        |


|  13 |       TABLE ACCESS BY INDEX ROWID| LOCATIONS         |


|* 14 |        INDEX UNIQUE SCAN         | LOC_ID_PK         |
|* 15 |      INDEX UNIQUE SCAN           | COUNTRY_C_ID_PK   |


|  16 |     TABLE ACCESS BY INDEX ROWID  | REGIONS           |


|* 17 |      INDEX UNIQUE SCAN           | REG_ID_PK         |
--------------------------------------------------------------


5. In what order does Oracle execute the steps of the following query execution plan?

--------------------------------------------------------------


| Id  | Operation                        | Name              |


--------------------------------------------------------------


|   0 | SELECT STATEMENT                 |                   |


|*  1 |  HASH JOIN                       |                   |


|   2 |   TABLE ACCESS FULL              | REGIONS           |


|*  3 |   HASH JOIN                      |                   |


|   4 |    INDEX FAST FULL SCAN          | COUNTRY_C_ID_PK   |


|*  5 |    HASH JOIN                     |                   |


|   6 |     TABLE ACCESS FULL            | LOCATIONS         |


|*  7 |     HASH JOIN                    |                   |


|   8 |      TABLE ACCESS BY INDEX ROWID | DEPARTMENTS       |


|*  9 |       INDEX UNIQUE SCAN          | DEPT_ID_PK        |
|* 10 |      HASH JOIN                   |                   |


|  11 |       TABLE ACCESS FULL          | JOBS              |


|  12 |       TABLE ACCESS BY INDEX ROWID| EMPLOYEES         |


|* 13 |        INDEX RANGE SCAN          | EMP_DEPARTMENT_IX |
--------------------------------------------------------------

[http://NoCOUG.wildapricot.org/resources/Pictures/Conference%20202002/MariaColgan.jpg]

SQL Tuning Boot Camp

Since the Cost-Based Optimizer’s introduction in Oracle 7, we have been fascinated and intimidated by it and the statistics it relies on. It has long been felt that the internals of the Optimizer are shrouded in mystery and a degree in wizardry is required to make it do the right thing. This one-day workshop will explain the fundamentals of the cost-based optimizer and the statistics that feed it. Using real-world examples, we will demonstrate that it’s not nearly as complicated as some folks would lead you to believe. We will also provide a methodology for diagnosing and resolving the most common SQL execution performance problems, allowing you to become an Optimizer whisperer.

Agenda

• Brief History of the Optimizer and how it operates
• Understanding Optimizer statistics
• Explain the Explain Plan
• Prevent sub-optimal execution plans

Presenter Bio

Maria Colgan is a master product manager at Oracle Corporation and has been with the company since version 7.3 was released in 1996. Maria’s core responsibility is creating material and lectures on the Oracle Database and the best practices for incorporating it into your environments. She is also responsible for getting feedback from Oracle customers and partners incorporated into future releases of the product. Prior to this role; she was the product manager for Oracle Database In-Memory and the Oracle Database query optimizer. Maria is the primary author of the SQLMaria blog https://sqlmaria.com and a contributing author to the Oracle Optimizer blog http://blogs.oracle.com/optimizer.

Post-conference networking reception and happy hour hosted by Quest at Sunshine Saloon<https://www.sunshinesaloon.com/>, 1807 Santa Rita Rd.

More information and online registration: NoCOUG 2020 Winter Conference: SQL Tuning Boot Camp<http://nocoug.wildapricot.org/EmailTracker/LinkTracker.ashx?linkAndRecipientCode=rbV9t6pIuo%2b2EyG7me2Lw3SGAzpvwwMRaY1R%2fvmfXGVFZPsXoD%2fK6qFR%2fEk0bOKszZRlmk%2fqdYENXnGyF6DmHhqbO9TlrMYgfzH1bo4qzwQ%3d>

[http://NoCOUG.wildapricot.org/resources/Pictures/Conference%20201802/RosewoodCommons%20Theater2.jpg][http://NoCOUG.wildapricot.org/resources/Pictures/Conference%20201802/RosewoodCommons%20Lobby7.jpg][http://NoCOUG.wildapricot.org/resources/Pictures/Conference%20201802/RosewoodCommons%20Lobby3.jpg][http://NoCOUG.wildapricot.org/resources/Pictures/Conference%20201802/RosewoodCommons%20Lobby2.jpg]

Gold Sponsors

[http://NoCOUG.wildapricot.org/resources/Pictures/Vendor%20Logos/AWS-Logo.png]<https://aws.amazon.com/aurora/?sc_channel=ba&sc_campaign=nocoug_aurora_319&sc_detail=640x480&sc_country=mult&sc_geo=mult&sc_category=aurora&sc_outcome=aware>

[http://NoCOUG.wildapricot.org/resources/Pictures/Vendor%20Logos/flashgrid-logo.nocoug.png]<https://www.flashgrid.io/>

[http://NoCOUG.wildapricot.org/resources/Pictures/Vendor%20Logos/o_memsql_CMYK_on-light-bg_horiztonal.png]<https://www.memsql.com/>

[http://NoCOUG.wildapricot.org/resources/Pictures/Vendor%20Logos/Quest%20Logo.png]<https://www.quest.com/>

[http://NoCOUG.wildapricot.org/resources/Pictures/Vendor%20Logos/Redwood%20Compliance.png]<https://www.redwoodcompliance.com/>

The Northern California Oracle Users Group is a volunteer-run 501(c)(3) organization that has been serving the Oracle Database community of Northern California for more than thirty years by organizing four conferences a year and publishing a quarterly journal. Download the complete digital archive of the NoCOUG Journal using: “wget www.nocoug.org/Journal/NoCOUG_Journal_{2001..2019}{02..12..3}.pdf”.

--
http://www.freelists.org/webpage/oracle-l