oracle awr warehouse.pdf

7/17/2019 Oracle AWR Warehouse.pdf

http://slidepdf.com/reader/full/oracle-awr-warehousepdf 1/46



Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |

Enterprise Manager AWR

WarehouseEnterprise Manager 12c, Release 4

Kellyn Pot’Vin,Consulting Member of Enterprise Manager Technical Team

Strategic Customer ProgramGP Gongloor, Director of Product ManagementOracle CorporationJuly 17th, 2014

Oracle Confidential




Safe Harbor StatementThe following is intended to outline our general product direction. It is inteinformation purposes only, and may not be incorporated into any contract.commitment to deliver any material, code, or functionality, and should not in making purchasing decisions. The development, release, and timing of afunctionality described for Oracle’s products remains at the sole discretion

Oracle Confidential




Program Agenda

1

2

3

4

5

What is the AWR Warehouse

Installation and ETL

AWR Warehouse Interface and Features

Use Cases

Summary

Oracle Confidential




Program Agenda with Highlight

1

2

3

4

5




Use Cases

Summary

Oracle Confidential




AWR Warehouse - Business Drivers

• Automatic Wor

(AWR) is the deperformance reOracle databas

• Default retentidays prevents dterm performa(“Compare perthis quarter’s blast quarter’s”)

• Increasing AWRperiod increasecost in critical penvironments

CRMFinance Supply Chain

0%

30%

60%

90%

120%

0%

30%

60%

90%

120%

0%

30%

60%

90%

120%




AWR Warehouse - Architecture

• Central warehouse co

long term AWR data r• Historical and ongoing

snapshots collected frdatabases enabled fowarehouse

• ETL jobs moves snapssource databases intowarehouse

• Retention period confweeks, months, years(default)




AWR Warehouse Features• Warehouse dashboa

ETL jobs

• All AWR features avaterm AWR data

• Performance pag

• AWR report

• ASH analytics

• Compare Period

• Compare Period

• Integrated seamlessly

• Zero runtime overheaProduction databases


http://slidepdf.com/reader/full/oracle-awr-warehousepdf 9/46Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |


1

2

3

4

5




Use Cases

Summary

Oracle Confidential


http://slidepdf.com/reader/full/oracle-awr-warehousepdf 10/46Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |

Why Have an AWR Warehouse?

Oracle Confidential




Requirements

• AWR Warehouse Repository -11.2.0.4 or higher.

• Preferred Credentials set up for all targets involved.

• Discover the database you will use for your repository in the

•

Pre-discover any source database before able to add.

• For a RAC target or AWR Warehouse, ensure you’ve set up a slocation for the ETL load files.

Oracle Confidential




AWR ETL Jobs

Oracle Confidential – In

DBMS Job on Source Databaseto directory

Job in EM Job Service thatpulls file from SourceTarget and then pushes toAWR Warehouse TargetDirectory Final DBMS Job Inserts d

into AWR Warehouse

All data identified by OLD/NEWwith the EM_ID, (CAW_DBID_M

AWR WAREHOUSE)

Source Target

Enterprise Manager

AWR Warehouse




“Under the Hood” Source Target ETL Job

DBMS Scheduler Job Name: MGMT_CAW_EXTRACT

Exec Call: begin dbsnmp.mgmt_caw_extract.run_extract; end;

How Often: 3 Hour Intervals if “playing catch up”, otherwise, 24

Oracle Confidential




EM Job Service Dump File Transfer

• Runs intervtime is addWare

• AgentDirec

Oracle Confidential




“Under the Hood” AWR Warehouse ETL Job

Oracle Confidential

DBMS Scheduler Job Name: MGMT_CAW_LOAD

Exec Call: begin dbsnmp.mgmt_caw_load.run_master;

How Often: 5 Minute Intervals

Biggest Resource Demand from the “run_master”:begin dbms_swrf_internal.move_to_awr(schname => :1); end;




ETL Load Duration to AWR Warehouse?

• Daily uploads take just a matter of second

• “Catch up” loads took anywhere from 2-12over 2 GB file load.

Oracle Confidential




Behind the Scene(DBMS_SCHEDULER, AWR Wareh

DELETE DBSNMP.CAW_LOAD_WORKERS

WHERE NAME NOT IN (

SELECT JOB_NAME

FROM DBA_SCHEDULER_RUNNING_JOBS

WHERE OWNER = 'SYS')

Oracle Confidential




Installed on Source Database DBSNMP Schema

CAW_EXTRACT_PROPERTIES : Information on ETL job, dump locaintervals.

CAW_EXTRACT_METADATA : All data about extracts- times, failu

Oracle Confidential




AWR Warehouse DBSNMP Objects

CAW_SPACE_USAGE: Populates resource consumption dashboa

CAW_PROPERTIES : Information about location of dump files, intretention time, etc. for AWR Warehouse.

CAW_PRIV_GRANTS : View privileges within the EM Console

CAW_LOAD_WORKERS : Only used during an actual ETL load proAWR Warehouse.

Oracle Confidential




AWR Warehouse DBSNMP Objects

CAW_SRC_DBS : Main info about source db’s, version, ETL status

CAW_SRC_DB_INSTANCES: Instance information about source da

CAW_LOAD_METADATA : AWR dump file local, last load, etc.

CAW_LOAD_ERRORS : Populates the errors view in the console.

CAW_DBID_MAPPING : Used to map all data between EnterprisAWR Warehouse and Database Identifiers.

Oracle Confidential




24 Databases X 10G of Data Over 3 Weeks….

Oracle Confidential





1

3

2

4

5




Use Cases

Summary

Oracle Confidential




Accessing the AWR Warehouse

Targets Databases

Performance AWR Warehouse

Oracle Confidential




AWR Warehouse Console Interface

Oracle Confidential




AWR Warehouse Configurations

Along with AWR Warehouse Basics-

• Configuration Status

• Version of Repository Database

• Host Information

• Connect Info, (SID/Service Name, P

• Space Usage, Upload Interval and

Oracle Confidential




Console Breakdown- High Level Warehouse Info

• Space Usage, (also available in the awrinfo.sql report.)

• Number of Databases, Incident report access and any sourceWithout Recent Uploads.

Oracle Confidential




Console Interface, Snapshots at a Glance

• Displays snapshot upload space allocated by date.

• Considerably less space when just adding daily, (48MB approx

Oracle Confidential




Working with AWR Source Databases in Repositor

• Highlight database from list, (clicking on target name will take

performance home page for target.)

• Run basic reports, view ASH Analytics or perform ADDM Com

• Also Add databases from the options.

Oracle Confidential




AWR Warehouse Source Databases

• Clicking on any

Name will takeperformance hfor that target.

• Target Type, DBversion info, owsnapshot, (ETL enabled.

• AWRW objectdbsnmp.CAW_

Oracle Confidential




High Level Snapshot Data

•Shows numsnapshots vcounts.

• Shows oldenewest sna

• AWRW objedbsnmp.CA

Oracle Confidential




Actions Drop Down

•Most options are also in menuof database list.

• Enable or Disable ETL Jobs foDatabases.

•Update snapshots via immediETL job on any source target inWarehouse.

Oracle Confidential




Would like to View Data Differently, Change Order

•

Uncheck any Coview less inform

• Can add Snapshinformation, (addata to filter thr

Oracle Confidential




Viewing Errors with the AWR ETL Load

**This can also be viewed in dbsnmp.CAW_LOAD_ERRORS

Oracle Confidential




Security- Administrators of AWR Data in the Ware

Allows the ability to remmanage AWR snapshotsAWR Warehouse, not juwarehouse data.

Oracle Confidential





1

4

2

3

5




Use CasesSummary

Oracle Confidential




What Can I do with the AWR Warehouse?

Oracle Confidential – In

0

50000

100000

150000

200000

250000

300000

350000

400000

June 10, 2013

l_reads_s read_iops_max read_iops_direct read_iops_direct_max

0

100000

200000

300000

400000

500000

600000

June 10, 2014

l_reads_s read_iops_max read_iops_direct read_iops_direct_max




CPU Usage Issues

“Nothing’s chamonths…”

Oracle Confidential




Using the AWR Warehouse for Capacity Planning

“Do we really needpartitioning and prOur disk usage hasincreased this last

Oracle Confidential




Querying the AWR Warehouse Tips

Update Scripts with DBID identified to filter.

Join:

• CAW_DBID_MAPPING on OLD_DBID/NEW_DBID=DBID

•

CAW_DBID_MAPPING on TARGET_NAME=TARGET_NAME

Oracle Confidential




How Current AWR Queries Change

from dba_hist_sys_time_model stm, dba_hist_snapshot s, gv$parameter p, dbsnmp.caw_dbid

where stm.stat_name in ('DB CPU','background cpu time')

and LOWER(m.target_name)= '&dbname'

and s.dbid= m.new_dbid and s.snap_id = stm.snap_id

and s.dbid = stm.dbid and s.instance_number = stm.instance_number

and p.name = 'cpu_count' and p.inst_id = s.instance_number)

Oracle Confidential





1

5

2

3

4




Use CasesSummary

Oracle Confidential




AWR Warehouse Offers

•

Incredible new repository to store AWR data from source targ

• Easy to use and familiar interface to run common reports anddata from

• Simple schema additions to existing source database to mana

inspect information or isolate issues.

• Only small changes to existing AWR queries to enhance to AWWarehouse queries.

Oracle Confidential




Want to Learn More?

Oracle Screenwatch: https://www.youtube.com/watch?v=StydM

DBAKevlar Blog Posts:

http://dbakevlar.com/2014/06/awr-warehouse-in-em12c-rel-4/

http://dbakevlar.com/2014/06/awr-warehouse-in-em12c-rel-4-pScripts: http://dbakevlar.com/scripts/

Oracle Confidential

https://www.youtube.com/watch?v=StydMitHtuI




http://dbakevlar.com/2014/06/awr-warehouse-in-em12c-rel-4-part-ii/

http://dbakevlar.com/scripts/

http://dbakevlar.com/scripts/

http://dbakevlar.com/2014/06/awr-warehouse-in-em12c-rel-4-part-ii/



oracle awr warehouse.pdf

Documents