I had a customer who needed to know how to retain performance after migrating to the cloud for SQL execution and I recommended SQL Baselines, but the information was surprisingly missing on how to collect an AWR baseline, THEN export out baselines and post migration, start a import baselines and review performance. Goal To stabilize performance of Oracle database during migrations to new Azure infrastructure from on-premises environments. Oracle provides in 12c and above, the ability to create full management baselines of SQL executions, which are a combination of SQL profiles, hints and defined statistical data to ensure performance remains…
-
-
There’s so much I need to get written down these days, but there’s only so many hours in a day and days in a week and I’ve totally failed in this area. Well, I have a little time right now, so going to try to get something down. It only took me four times to get this published! 🙂 I’m often asked how I migrate an Oracle database to Azure and although each database is unique, there are some things that are pretty consistent that need to be identified and some that should be resolved before migrating to the cloud.…
-
As often as I use the Automatic Workload Repository(AWR) data in my day-to-day job at Microsoft, I think most can imagine how often I’m explaining its value, along with tracing to my Microsoft peeps. Its time to write a post dedicated to the Microsoft crowd who want to understand a bit more about AWR, so hopefully its helpful! What is the Automatic Workload Repository? The Automatic Workload Repository, (AWR) had been around since Oracle 10g and requires the diagnostic and tuning management pack licensing to use all of its features in Oracle’s Enterprise Edition database. Versions before 10.2.0.4 had limited…
-
There are two ways to compare one database to another in the AWR Warehouse. I covered the ADDM Comparison Report here and now we’ll go through the second one, which is much more involved and has us empowering the AWR Warehouse taking two AWR Warehouse reports and comparing two databases to each other. The AWR Warehouse, once setup and databases that are targets already monitored by your EM12c or EM13c environment, can then be added and upload all AWR snapshots to this central repository. Comparison Period Report The AWR Warehouse second comparison reporting option is accessible from the drop down…
-
A lot of my ideas for blog posts come from questions emailed to me or asked via Twitter. Today’s blog is no different, as I was asked by someone in the community what the best method of comparing databases using features within AWR when migrating from one host and OS to another. There is a lot of planning that must go into a project to migrate a database to another host or consolidate to another server, but when we introduce added changes, such as a different OS, new applications, workload or other demands, these need to be taken into consideration.…
-
It’s that time of year again and the massive undertaking of the Collaborate conference is upon us. This yearly conference, a collaboration between Quest, Oracle Applications User Group, (OAUG) and Independent Oracle User Group, (IOUG) is one of the largest conferences in the world for those that specialize in all areas of the Oracle database. The conferene is held in different cities, but recently its been sticking to the great destination of Las Vegas, NV. We’ll be at the Mandalay, which like many casinos, is like it’s own little self-contained city within a city. The week will be crazy and…
-
The other day I had a customer who was to send me an ASH report after there was a challenge in providing me EM Diagnostics from their repository database. After the report hadn’t been submitted after a number of days, the customer admitted that they would execute an ASH report request and would subsequently kill the session after eight hours or so of non-completion. Although I hadn’t experienced this myself, there are a number of causes behind an ASH report not running and troubleshooting it is a straight forward approach. Analysis The first thing to do, is to gather some…
-
This question was posted in Twitter from @matvarsh30, who asked, “How can I display CPU usage over different periods of time for databases in Enterprise Manager?” Everyone loves their trusty Top Activity, but the product’s functionality is limited when it comes to custom views and this is what our user had run into. There are numerous ways to display this data, but I’m going to focus on one of my favorite features in the product that was created to replace Top Activity, ASH Analytics. Retaining AWR Data for ASH Analtyics Note: This process to display CPU graphs will work for…
-
There was a question posted on Oracle-l forum today that should have a blog post for easy lookup for folks. Regarding your Enterprise Manager repository database, (aka OMR.) This database has a restricted use license, which means you can use it for the Enterprise Manager repository, but you can’t add partitioning to it or RAC or dataguard features without licensing those features. You also can’t use the diagnostic and tuning pack features available in Enterprise Manager on the repository database without licensing it outside of the EMDiagnostics tool. You can view information about the license that is part of…
-
When you need to have information about one SQL_ID and don’t need everything and the kitchen sink, there are a few different ways to collect this via Oracle. I’m surprised how rarely this is covered in performance tuning/optimization, (whatever the current “acceptable” term is for fixing a database when there are performance issues arise… J) classes, manuals and documentation. I’d like to cover two of my favorite here and know, there are other options that I won’t be digging into, (SQLd360, SQLTXPLAIN, tracing, etc…) The Workload Repository SQL Report I also refer to this as the “SQL ID Specific AWR…
-
When sizing the AWR Warehouse, one of my requirements is to have certain reports for a sampling of databases that will source the AWR Warehouse. This report provides me the right information to create the correct sizing requirements vs. any assumptions done with other choices. The report is the General AWR Information report and can be found in the $ORACLE_HOME/rdbms/admin directory. It provides the following information: Time of Report AWR Retention and interval settings, including if non-default setting. Schema percentage of space used in the SYSAUX tablespace. Objects and space consumption in the SYSAUX tablespace, including breakdown of the AWR space…
-
Mauro decided to give me another challenge- run SQLd360 against the AWR Warehouse and let him know how it does straight “out of the box”. It’s a simpler installation process than SQLTXPLAIN, you simply unzip and run, (no installation of any packages…) I chose a SQL_ID from one of the source databases loaded and ran it for a 31 day history: SQL> @sqld360.sql 8ymbm9h6ndphq T 31 I saw a few errors scroll by on the screen as the process to collect the data and zip it up ran. There isn’t the same log file to see on successful runs to…
-
I finally have a moment to look into my “hacked” SQLT XPRECT/XTRACT runs and see if the changes I made were enough to run properly with the AWR Warehouse. The answer is yes and no… and maybe “it depends”… 🙂 The Results The data from the AWR Warehouse to pull the corresponding data for the appropriate SQL_ID from the correct source database in the AWR repository worked, but the environment information still populates incorrectly, as I didn’t update anything outside of the get_database_id function in the SQLT$A package and the executables that call it, as documented in my earlier blog…
-
Yeah, so I did it- I installed SQLTXPLAIN, (SQLT) on the AWR Warehouse! From previous experience with this fantastic tool, I was pretty sure I’d have some great new adventures with the AWR Warehouse data and I wanted to try and test out the AWR pieces of this tool with the Warehouse. This is a work in progress, so I’m going to do this blog in multiple parts to ensure we don’t miss anything. Installation I didn’t expect SQLT AWR features to work out of the box. I still needed to install it, run a few reports and see what would…
-
Active Session History, (ASH) reports are one of my favorite when investigating a performance issue. The biggest challenge I think that faces many administrators is to know when to turn to an ASH report and how to use the report to diagnose an issue. Today’s example is brought to us thanks to a Workload replay that should be a simple, but also, a clear step by step of when and why to use an ASH report. Top Activity In our EM12c environment, we note some high activity in a database and use Top Activity to view what’s going on: As…
-
I’ve had a recent request to write an update on working with AWR reports, so as promised, here it is! The Automatic Workload Repository The Automatic Workload Repository, (AWR) was one of the best enhancements to Oracle back in release 10g. There was quite a goal put in front of the development group when they were asked to develop a product that: 1. Provided significant performance recommendation and wait event data enhancements over its predecessor statspack. 2. Was always on, meaning that the data would continually collect without manual intervention from the database administrator. 3. Wouldn’t impact the current processing,…
-
I’ve had a few folks ask me a similar question about the AWR Warehouse, occuring numerous times this week. “How can I limit what the user of the AWR Warehouse can view in the AWR Warehouse?” “How can I add source databases to the AWR Warehouse without DBA privileges?” This topic bridges into the area of confusion of use of the EM12c environment, (which I consistently promote for use by DBAs, Developers and all of IT) and then the AWR Warehouse, which shares it’s user interface as part of cloud control, that currently is a DBA’s deep analysis and research…
-
When concurrency is the crippling factor in a database performance issue, often I’m told that viewing blocked sessions in Enterprise Manager is difficult. The query behind, along with flash image generation in any Enterprise Manager can take considerable time to render and no matter how valuable the view is, the wait is something DBAs just can’t hold out for when needing the answer now. Blocking Sessions View in OEM If you’re wondering which feature I’m speaking of, once you log into any database, click on Performance, Blocking Sessions. If there aren’t any or any significant load on the database, it…