• No results found

Using Database Diagnostic and Tuning Packs through Oracle Enterprise Manager 12c. Eric Siglin OCM, OCP, CTT+ Senior Oracle DBA

N/A
N/A
Protected

Academic year: 2021

Share "Using Database Diagnostic and Tuning Packs through Oracle Enterprise Manager 12c. Eric Siglin OCM, OCP, CTT+ Senior Oracle DBA"

Copied!
34
0
0

Loading.... (view fulltext now)

Full text

(1)

Using Database Diagnostic and

Tuning Packs through

(2)

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

(3)

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

(4)

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

(5)

• Set up User Access

• Determine Workload

• Gather SQL Statements

• Analyze SQL

• Bring It Together

• Questions

Session Topics

(6)

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"

(7)

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;

(8)

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

(9)

• 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

(10)

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

(11)

See what’s going on – as it happens!

Real-Time SQL Monitoring

Make note of the SQL ID.

You’ll want that later.

(12)

Using DB Diagnostic and Tuning Packs through OEM 12c

9/28/14 12

Living, breathing explain plan!

Real-Time SQL Monitoring

(13)

Live SQL execution statistics!

Real-Time SQL Monitoring

(14)

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

(15)

• Set up User Access

• Determine Workload

• Gather SQL Statements

• Analyze SQL

• Bring It Together

• Questions

Session Topics

(16)

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.

(17)
(18)

Using DB Diagnostic and Tuning Packs through OEM 12c

9/28/14 18

(19)
(20)

Using DB Diagnostic and Tuning Packs through OEM 12c

9/28/14 20

(21)
(22)

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

(23)

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

(24)

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.

(25)

SQL Access Advisor – Summary

(26)

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.

(27)

SQL Access Advisor – Recommended Actions

Allows you to customize and harvest the implementation script.

(28)

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.

(29)

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?

(30)

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

(31)

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.

(32)

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?

(33)

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

(34)

Using DB Diagnostic and Tuning Packs through OEM 12c

9/28/14 34

References

Related documents

Afirma Mançano que cada territorio es una totalidad soberana diferenciada de otras por las relaciones sociales y las escalas geográficas, lo que no implica una suerte de

The strength of each element in the chart can be determined by using table 8 above (e.g. In spring: wood prosperous, fire strong, water weak, metal trap, and earth dead).

A nonstress test is usually performed to assess fetal well- being in a pregnant patient with a prolonged pregnancy (42 weeks or more), diabetes, a history of poor

• “Labor supply responses to income shocks: Evidence from Bukidnon, 1984/85 and 2003,” presented at the Poverty and Economic Policy Research Network, Department of

The objective of this lab to provide exercises designed to showcase the new database performance management capabilities in Oracle Enterprise Manager Cloud

Hundreds of performance and configuration metrics available for incident thresholds, reports, analysis and comparison.. Copyright © 2014 Oracle and/or

In the Oracle Database 12c: Performance Management and Tuning course, learn about the performance analysis and tuning tasks expected of a DBA: proactive management through

This session will demonstrate how to use Oracle Enterprise Manager 12c for continuous demonstrate how to use Oracle Enterprise Manager 12c for continuous security compliance