Using Database Diagnostic and
Tuning Packs through
Using DB Diagnostic and Tuning Packs through OEM 12c
9/28/14 2
Responsibilities
• ERCOT covers 75% of Texas land
• ERCOT handles 85% of Texas load (23 million consumers) via • More than 40,500 miles of transmission lines and
• 550+ generation units (more than 74,000 MWs of capacity)
• Peak Demand in ERCOT was set on August 3, 2011 at 68,305 MWs
• Physical assets are owned by transmission providers and generators, including Municipal Utilities and Cooperatives
ERCOT Quick Facts
The Texas Legislature restructured the Texas electric market in 1999 by unbundling the investor-owned utilities and creating retail customer choice in those areas, and assigned ERCOT four primary responsibilities:
• System reliability – planning and operations • Open access to transmission
• Retail switching process for customer choice
Senior Oracle DBA
• 28+ years of IT experience
• 20+ years of Oracle experience (DBA and Developer)
• 10 years of instructing Oracle University courses
• Experience in:
– Oracle Database versions 6 on up
– Oracle RAC and Data Guard
– Oracle GoldenGate, Streams and Advanced Replication
– Oracle Enterprise Manager
Using DB Diagnostic and Tuning Packs through OEM 12c
9/28/14 4
• Set up User Access
• Determine Workload
• Gather SQL Statements
• Analyze SQL
• Bring It Together
• Questions
Session Topics
• Set up User Access
• Determine Workload
• Gather SQL Statements
• Analyze SQL
• Bring It Together
• Questions
Session Topics
Using DB Diagnostic and Tuning Packs through OEM 12c
9/28/14 6
Create users through OEM UI
Each user takes about 15 mouse clicks
Time consuming for large number of users
Use LDAP-authenticated accounts
Auto-generated accounts ease administration
LDAP groups matching EM roles ease authorization
Create users through command line interface
Generate your user create script
Allows for batch processing of user creation
Example of EMCLI:
emcli create_user -name="SPARKE" -password="My14OEM#1“ -type="EM_USER“
-roles="ERCOT_DEV_USER“ -email=sparke@ercot.com
-profile="MGMT_ADMIN_USER_PROFILE" -desc="Spark-E“ -expired="true" -department="354" -location="Taylor"
Diagnostic and Tuning Packs are a database feature
EM provides a user-friendly interface
Privileges are required by the database user accessing the feature
Create a role for each pack
Grant the Diagnostic Pack role to the Tuning Pack role
Grant the appropriate role to the database user
Example:
CREATE ROLE diagnostic_pack_access_role;
GRANT select any dictionary TO diagnostic_pack_access_role; GRANT oem_advisor TO diagnostic_pack_access_role;
GRANT EXECUTE ON dbms_workload_repository TO diagnostic_pack_access_role;
CREATE ROLE tuning_pack_access_role;
GRANT diagnostic_pack_access_role TO tuning_pack_access_role;
Using DB Diagnostic and Tuning Packs through OEM 12c
9/28/14 8
• Set up User Access
• Determine Workload
• Gather SQL Statements
• Analyze SQL
• Bring It Together
• Questions
Session Topics
• Look at top SQL in latest ADDM report
• Identify candidate statements in Real-Time SQL Monitoring • Use the performance tab of the database target
• Run user-defined ADDM report for time period of interest
Using DB Diagnostic and Tuning Packs through OEM 12c
9/28/14 10
Analyzes the AWR data for you!
ADDM Report
Impact on the workload.
SQL ID
See what’s going on – as it happens!
Real-Time SQL Monitoring
Make note of the SQL ID.
You’ll want that later.
Using DB Diagnostic and Tuning Packs through OEM 12c
9/28/14 12
Living, breathing explain plan!
Real-Time SQL Monitoring
Live SQL execution statistics!
Real-Time SQL Monitoring
Using DB Diagnostic and Tuning Packs through OEM 12c
9/28/14 14
We’re about to gather some SQL.
Identify Your Time Period of Interest
• Set up User Access
• Determine Workload
• Gather SQL Statements
• Analyze SQL
• Bring It Together
• Questions
Session Topics
Using DB Diagnostic and Tuning Packs through OEM 12c
9/28/14 16
Organize Statements in SQL Tuning Sets
DB Object for SQL Analysis
• SQL Statement(s) • Execution Context • Execution Statistics
• Managed with DBMS_SQLTUNE from SQL command line or OEM.
Portable
• Pack SQL Tuning Set into a staging table.
• Export staging table and import into another database. • Unpack staging table in receiving database.
Using DB Diagnostic and Tuning Packs through OEM 12c
9/28/14 18
Using DB Diagnostic and Tuning Packs through OEM 12c
9/28/14 20
Using DB Diagnostic and Tuning Packs through OEM 12c
9/28/14 22
• Set up User Access
• Determine Workload
• Gather SQL Statements
• Analyze SQL
• Bring It Together
• Questions
Session Topics
Tools to Analyze SQL
SQL Tuning Advisor
• Each SQL statement analyzed individually • Displays information about execution plans • Reports recommendations and rationale
SQL Access Advisor
• Analyzes statements with respect to workload
• Can recommend indexes, materialized views and partitioning • Displays recommendations, rationale and potential impact
Using DB Diagnostic and Tuning Packs through OEM 12c
9/28/14 24
SQL Tuning Advisor
• Analysis shows performance improvement may be possible.
• Recommendations for changes to statement access path is not this tool’s forte.
SQL Access Advisor – Summary
Using DB Diagnostic and Tuning Packs through OEM 12c
9/28/14 26
SQL Access Advisor – Recommendations
• Recommendations are ordered by their impact to the workload
analyzed. • Focus on the
recommendation with the most impact.
• Drill down into the details to identify the statements affected by this recommendation.
SQL Access Advisor – Recommended Actions
Allows you to customize and harvest the implementation script.
Using DB Diagnostic and Tuning Packs through OEM 12c
9/28/14 28
SQL Access Advisor – Recommended Actions
• Lists actions to take
• Indicates whether recommended action has already been implemented.
• Field to enter schema and/or tablespace name(s) for use when generating the implementation DDL.
Scrutinize Recommended Actions
Apply your system expertise to the recommended actions.
Does it make sense to partition
Use meaningful object names
• Does the partitioning method make sense?
Using DB Diagnostic and Tuning Packs through OEM 12c
9/28/14 30
• Set up User Access
• Determine Workload
• Gather SQL Statements
• Analyze SQL
• Bring It Together
• Questions
Session Topics
Put the Pieces Together
Collaboration is needed for an effective solution.
• Technical experts confer. – Data Architect
– Database Administrator – Application Developer • Review technical analysis. • Determine best hypothesis. • Deploy and test solution.
– Check to see if the execution plan is as expected. – Test work load to see if performance improves.
Using DB Diagnostic and Tuning Packs through OEM 12c
9/28/14 32
Summary
• Utilize tools to assist you in analyzing SQL performance issues. – Automatic Database Diagnostic Monitor (ADDM)
– Database SQL Tuning Advisor – SQL Access Advisor
• Use SQL Tuning Advisor to help identify statements that need attention.
• Use SQL Access Advisor to help recommended changes to improve SQL access to the data
• Collaborate with colleagues to develop best solution. • Deploy and test hypothesis.
– Did the change have the intended result? – Has the performance improved?
Resources
• Oracle Enterprise Manager product page on the Oracle Technology Network: – Product overview http://otn.oracle.com/oem
– Demonstrations (Screenwatches):
http://www.oracle.com/technetwork/oem/em12c-screenwatches-512013.html – Product documentation: http://docs.oracle.com
– Community discussion forums and blogs
– Links to other learning opportunities like live webcast events:
http://www.oracle.com/technetwork/oem/grid-control/webcasts-index-090211.html
• Oracle by Example http://otn.oracle.com/obe
– Using SQL Tuning Advisor through SQL Developer 3.0:
https://apex.oracle.com/pls/apex/f?p=44785:24:0::NO:24:P24_CONTENT_ID ,P24_PREV_PAGE:5591,29https://apex.oracle.com/pls/apex/f?p=44785:24:0
Using DB Diagnostic and Tuning Packs through OEM 12c
9/28/14 34