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?
--------------------------------------------------------------
| Id | Operation | Name |
--------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | NESTED LOOPS | |
| 2 | NESTED LOOPS | |
| 3 | TABLE ACCESS BY INDEX ROWID | EMPLOYEES |
|* 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://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](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){kind=link}
