This will be a blog about Oracle Database Manageability and how you can become more successful in this area.
Who am I:
My Oracle career started over 25years ago. I was fresh from university and I got my first job as a programmer in a small IT department at a brewery in Sweden. Same year we started our new strategy where Oracle become the strategic database of choice. That’s when I came in contact with Oracle version 6.
In 1995 I joined Oracle as a consultant and rather soon my assignments became focused on DBA activity. I had the opportunity to work with some high profile projects which gave me lot’s of experience and contacts within Development. In 2010 I finally transferred to work as product manager for Real Application Testing mainly focusing on customers outside USA. Our group is part of Oracle Enterprise Manager Organization and we are focusing on Database manageability which includes Diagnostic Pack, Tuning Pack and Real Application Testing
My thought with this blog is to provide best practices, “how to do” and other information that can be of interest with Diagnostics, Tuning and Real Application Testing.
Please provide Feedback and ask questions.
If you have any topics that you want me to address just let me know.
I got a question the other day: Is it possible to use SQL Performance Analyzer (SPA) on an application using Temporary Tables?
The answer is not straight forward YES or NO.
Before answering the question, we need to iron out, what is a temporary table and how does SPA perform it’s trial.
Temporary Tables
We have two types of temporary tables in Oracle.
Global Temporary Tables: The definition of a temporary table is visible to all sessions, but the data in a temporary table is visible only to the session that inserts the data into the table.
Private Temporary Tables: A private temporary table differs from a temporary table in that its definition and data are visible only within the session that created it.
SQL Performance Analyzer
SPA is a tool to identify Plan and performance changes on same statement executed in original environment and in a changed environment. This can be before and after an upgrade or before and after changing something that can impact the optimizer.
SPA will use a specific SQL tuning set collected from production with a set of bind variables also collected in the SQL tuning Set.
SPA Trials
A SPA trial can either execute SQL statements or it can convert the execution statistics from the SQL tuning set into SPA statistics. When executing SQL statements there are no concept of a transaction binding several statements together. SPA will execute statements according to the order in the SQL tuning set. DML without a where clause will be omitted since and DML with where clause will be “rewritten” to a SQL statement so we can get the execution plan and validate before and after.
SPA and Temporary Tables.
Now that we know that there are no transactions occurring in SPA then there will be different outcomes when running a SPA Trial with SQL Statements including a temporary table.
If the trial using the convert method, then there is no problem it will use execution statistics from the SQL Tuning Set.
If the trial using the execute method, there are two scenarios.
For a Global Temporary Table there will be a table defined but the table will be empty and even if we could populate the table with data for the SPA session there are just a minimal chance that we could populate it with the data that corresponds to bind variable in the SQL Tuning Set.
For a Private Temporary Table there is a big risk that the table definition doesn’t exist for the user who executes the SPA trial. In that case there will be an error “ORA-00942 Table or view does not exist” the impact will be that the statement will be sent to the bucket of errors and the SPA trial will continue. If the user has the table definition, then it will have same issue as the Global Temporary Tables.
Solution
How do we test queries with temporary tables then? We cannot execute these queries with SPA because they will either error out or they will return zero rows and execution statistics will be hard to compare.
But there are a few ways we can do it:
STS compare using Database replay.
STS compare using two STS. One with complete workload and one with complete workload where queries using temporary tables has been filtered out.
STS compare using Database Replay and two STS
SQL Performance Analyzer has a feature called STS Compare. It will use the convert method on two different SQL Tuning Sets. We need to capture an STS before changes and one after changes and compare, but they should be based on same workload, or the comparison may be wrong.
This can be achieved by using Database Replay. At the same time as we start Database Replay Capture then we start a SQL Tuning Set capture as well. If the database is a none-RAC database, it can be done with the DBMS_WORKLOAD_CAPTURE.START_CAPTURE API or with the capture UI in Enterprise Manager.
If the database is a RAC database, it needs to be a separate SQL Tuning Set capture done from either Enterprise Manager UI or from API, from each instance of the database. In this case we capture STS from cursor cache and the cursor cache is private for each node. Since there is a filter option then it is possible to only capture statements relevant to Temporary Tables. It might be filtering on a certain user or a certain module or action.
When the capture has finished there will be separate STS for each instance. Depending on how connection mapping is done during replay it may be difficult to guarantee that same users are replayed against same instance. Normally in replay we use a round robin connection. If this approach is used, then we need to merge captured STS.
There is now a reference STS from capture environment. It has all statements and an aggregated statistics from all executions on them. If a workload filter is used, then it will be narrowed down to statements of interest.
During Database Replay create a new STS using same filters as used in capture. If RAC merge STS.
There are now two STS one from capture and one from replay with statistics from all executions during capture and replay. Since every statement using Temporary Tables are executed in the correct context, statistics should be correct from both capture and replay and it is possible to compare statistics between capture and replay.
This is done with a SPA feature called STS compare currently only available as API but will be implemented in a later release update of Enterprise Manager 13.5.
STS compare using two STS
This method will use STS compare as well but with another approach. It will not be as accurate as the previous use case, but it will give us a fair estimate on the impact.
In this method we are not concentrating on changed execution plans and the impact from it. Instead, we are focusing on the total impact from queries using temporary tables.
STS compare can not only detect query plan changes and changes in execution statistics. It can also detect missing and new SQL statements and the impact of these queries. In this case our intention is to measure the impact of missing queries.
First, capture an STS. It should include all application related activity so filters should be on users like SYSTEM, DBSNMP, SYSMAN and other no application related users.This SQL Tuning Set should be captured from Cursor Cache for a period that includes the workload of interest.
Next, clone this STS and filter out statements including temporary tables.
There are now two STS one with including all statements and a second including all statements except statements using temporary tables.
STS compare Example
In the example below there will be an STS with 30 statements and there is one statement using temporary tables called STAGE with a numeric extension.
The process will be following:
Identify statements including STAGE in the SQL Text.
Create a new STS where these statements are excluded
Run SPA STS compare
Identify the impact of missing statements
The process will include both Enterprise Manager workflows for step 1 and 2 and API for step 3.
Identify statements including STAGE in the SQL Text
From the database home page go to Performance > SQL > SQL Tuning Sets
Select the collected STS of interest and click “Details”
Click “Search for SQL within tuning set”
Now, find SQL statements where SQL text includes stage.
In this example I know that SQL statements is written in lowercase. In the pre added filter condition “SQL Text” change operator to LIKE and enter the name to look for with initial and trailing %.
Click “Search”
A list of Statements will now appear in the Results section. SQL ID for this statement is 200mw76ta6n1r. If there are any suspicion that the statement shouldn’t be excluded, click on the SQL Text, and inspect the statement.
Create a new STS where these statements are excluded
Now we should reverse the search and exclude the SQL ID 200mw76ta6n1r.
In the filter condition for SQL ID change operator to “NOT IN” and enter 200mw76ta6n1r in value. If there are more statements to filter out then separate SQL ID with “,”.
Click “Search”.
We have now a list of all statement except 200mw76ta6n1r.
Select “Save to a new SQL Tuning Set” with your name of choice. Click “Save”
We have now a new SQL tuning set where in my case we have excluded one statement.
Run SPA STS compare and identify the impact of missing statements
We will now perform the STS comparison using API. So, it is time to connect to SQL*Plus or any other tool of choice to run SQL statements. The last step is to spool the report to a text file, so your SQL tool need to have this ability.
We will now perform the STS comparison using API. So, it is time to connect to SQL*Plus or any other tool of choice to run SQL statements. The last step is to spool the report to a text file, so your SQL tool need to have this ability.
In SQL*Plus execute following statements:
Create the SPA task. This will be the container for all trial in this context
Create the second trial. This is a conversion of the STS without statement 200mw76ta6n1r. If we want to test differences between or reference and other STS with different filters, then we can add additional SPA trial.
Perform a workload comparison between trial one and trial two. The workload impact threshold is set to 0 so any changes will be seen as either improvement or regression. Additional comparisons can be done between reference STS and any other STS
Set pagesize 0 VAR rep CLOB; EXEC :rep := DBMS_SQLPA.REPORT_ANALYSIS_TASK('TEMP_IMPACT','HTML', 'all', 'all'); SET LONG 100000 LONGCHUNKSIZE 100000 LINESIZE 130
spool temp_impact.html PRINT :rep spool off
We have now done a comparison and can open the report in a browser.
Identify the impact of missing statements
The comparison report will provide information on missing statements and the impact on the workload.
In this case we had one statement missing and the impact on the workload is 17.52%
We can now drill down on the SQL statement and look at details. As we can see here, the statement has been executed 10269 times during the period, so it is a statement where even a minor change can impact the total workload. This makes it important for us to do further investigations.
The execution plan is provided together with the execution statistics which provides us with the ability to analyze further. It may be possible for us to execute this query in our test environment with populated temp tables to generate after change execution plans to compare with.
Conclusion
SPA has a rich set of features to perform comprehensive performance testing of changes in the database. Application using temp tables has some restriction described in this blogpost, but it is still a good tool to validate performance changes. You will have the ability to identify the overall impact of statements using temp tables and you can therefor estimate the impact of these queries.
If their impact is large, then it is important to investigate further which can be done with manual testing.
With this method we have reduced the amount of manual testing and have the ability to do a risk analyze of the impact before progressing.
A SQL Tuning Set (STS) is a collection of SQL statement extracted from the Cursor Cache or from the AWR. It is stored in the data dictionary object and it includes following information:
A set of SQL statements
Associated execution context, such as user schema, application module name and action, list of bind values, and the environment for SQL compilation of the cursor
Associated basic execution statistics, such as elapsed time, CPU time, buffer gets, disk reads, rows processed, cursor fetches, the number of executions, the number of complete executions, optimizer cost, and the command type
Associated execution plans and row source statistics for each SQL statement (optional)
The STS can be used as input for Tuning Advisor or it can be used as input for SQL Performance Analyzer
Recently I worked with with a customer who wanted to clone the STS and remove a couple of statements. This can be done from API or from Enterprise Manager. If you have access to Enterprise Manager then it should be the obvious choice but it is actually rather hidden.
Deleting from an existing STS is rather simple just select the statements and delete them. But if have a large STS and you want to delete hundreds or even thousands of statements then it can be rather tedious task.
So how can we do this from Enterprise Manager?
To access SQL Tuning Sets in Enterprise Manager select the database or PDB of choice and select Performance > SQL > SQL Tuning Sets
We select the SQL Tuning set that we want to clone and click on “Details”
The first column “Select” can be used when deleting one or many SQL statements from this STS but that will be irreversible so it is better to keep the original STS and create a clone.
Instead of having a clone button we need to use the “Search for SQL within tuning set”
Here, a complete new UI section appears and even if we accessed it from a certain STS it is actually a a complete general interface. Instead of selecting SQL statements from the STS it is possible to do it from Cursor Cache or from AWR Baselines/snapshots.
Lets continue with the selected STS. In this example our goal is to remove every SQL statement with Elapsed time less than one second. So we apply a filter condition where Elapsed Time (sec) > 1 and click “Search”. The original STS had 24 statements and the new list only includes 19 statements.
If this fulfills our requirements then we can go ahead and save it. Even this section provides us with options. Either save it in an existing STS or as in this example create a new STS . Provide a name for the new STS and click “Save”.
We have now created a new STS and in same time removed unwanted statements from it.
Below is a video on the same topic but instead of filtering on Elapsed time the filter is on SQL ID.
I often get questions, if it is possible to change the amount of instances from a capture to a replay. The answer is:
Yes, you can replay a workload using different number of nodes. This is more about remapping replay clients .
Let’s assume that captured workload is on a 2-node cluster and we have approximately 400 users.
When replaying the workload, we need to start 8 replay clients. 8 replay clients need 2 CPU (cores)
Best practice is that replay clients should be running from a different server(s) here it will be OK with one smaller machine. (A customer workload required 480 replay clients, 96 CPU cores).
The replay can be done in 3 ways:
Non CDB database
CDB database
PDB (19c and later)
User connections will connect to the database via a remapped connection. The remapped connection can be:
Do nothing (use captured connection string)
Use TNS (either TNS alias or a full connect descriptor)
Override and use the connect descriptor given when connecting the replay client
Override cannot be used when replaying against CDB. Because user connections should go to individual PDB’s
Let’s assume we remap connections:
For a RAC database my advice is to use a TNS alias and now it is more about what we want to be distributed and how it should be distributed.
A batch may be sticky to instance 1 but all online traffic should be evenly distributed so if we can distinguish this from the captured connect string then we can set up different services and use different TNS entries.
When distributes the workload it is important to make sure to use both server-side load balancing and client-side load balancing.
The reason for this is server-side load balancing will connect to the least used node. Initially Replay clients will connect users to first node in the list and it will preconnect all sessions that was connected when we started the capture and since there are no activity then all session will go to the first node. This is why client-side load balancing is needed as well.
In this series of Chargeback posts we have talked about installing Chargeback, creating changeplans and adding costcenters. In this post we talk about adding Chargeback entities.
By adding an entity and assign a changeplan and a costcenter to it we will start to collect usage or configuration data which will be the input to charging for the entity. Database (CDB) targets are discovered at database level so if we want to charge on PDB then we need to change the granularity to “Metered by PDB” which allow us to have different costcenters for each PDB in the container database. Be aware that the CDB consumes resources as well so even the CDB needs to be assigned to a costcenter.
An important part of Chargeback is whom to charge. In Chargeback we can configure our own cost centers as well as we can import cost centers from a central LDAP repository.
Cost Centers are built in a hierarchical structure and when we start there is always a default cost center available. But the reason for using Chargeback is that we want to divide costs between different owners.
The hierarchical structure is a great feature to get a complete picture of your IT costs. Whats allocated on the lowest level will be rolled up to next level all the way to the top, so it is easy to generate reports and find out top consumers, trends and other important metrics to follow up on.
In this video we are showing how to configure your cost centers manually.
An Important piece in Chargback is how to charge a certain entity. Since this series of Chargeback blogs are from a database point of view then my example is for databases.
This example will work for any cloud solution where ,you have several databases/PDB’s and need to divide the cost. But there are some caveats that we need to straighten out.
Allocation vs Utilization
In Oracle Cloud as well as other cloud environments you pay for what you have allocated and not for your usage second by second. There is the ability to burst CPU’s so you allocate more capacity for a shorter time period, but it is still the allocation of resources that creates the bill.
In most cases you allocate more resources than you need because you do not want to be in the situation where CPU, storage and memory is at 100%. This will not allow you to grow and it will cause waits and swapping that is unhealthy for performance. So there should always be a headroom.
So the first question to answer is, if I’m only using 75% of my resources who should pay for allocated but not utilized resources. We could always add a factor to the equation and say calculate all utilization with factor 1.25. This will be rather correct as long as your utilization is rather constant. But we all know that there are peak periods an none peak. So there may be periods where utilization is 25%. If this is the case there will be a discrepancy between the Chargeback bill and the cloud providers bill.
Chargeback Configuration vs Utilization
So can we solve this by using Chargeback configuration instead of utilization. with configuration we can look at CPU count for a database and charge on CPU count. This may work in some consolidated environments but will not work in some environments.
Why do we consolidate? Because we have many small databases and they are not using a vast amount of resources. The overhead of putting them in their own VM is higher than resources they use. It can be everything from CPU, storage, memory and administrative costs. So the best solution is to reduce the overhead as much as possible which can be done with multitenant databases.
In this environment then we can have 20 databases with allowed resources of 1 CPU but available CPU’s is only 10. So we have over allocated the amount of databases since the usage is normal less than 1.
now we can use a factor of 0.5 instead and it will summarize to 10. But if the amount of databases/PDB’s changes then it will not be 10 anymore and over/under charging occurs.
So currently there are no bulletproof model for getting a Chargeback bill equal to a cloud bill.
But is this really what you want. Where should the overhead of your internal IT staff be allocated. are there other costs that needs to be considered as well?
Universal plan vs Extended plan
In Chargeback we have different plans, We have the Universal plan it is a simple plan that only have 3 charge parameters as input. CPU, Memory and Storage.
The Universal plan is the base for Extended plans and Extended plans will inherit base charges from the Universal plan.
The benefit with the Extended plan is that we can add different charges or options and versions etc
Below is a matrix in possible items to measure both for configuration metrics and usage metrics. Full description can be found in the Enterprise manager Cloud Management Pack documentation
Chargeback in not part of the default Enterprise Manager installation but, if you have chargeback available in the Enterprise menu the its already installed. To be able to access Chargeback you need to enabled it by installing the Oracle Consolidation Planning and Chargeback plugin.
Be aware that the installation of the plugin requires a short period of downtime.
Here is a video that describes how to install the plugin
Chargeback is an Enterprise Manager feature that have been in Enterprise Manager since EMCC 12.1 but we have seen an increasing interest due to cloud adoption.
So, why is there an increasing interest in Cloud Adoption? Cloud providers are already providing you with a bill.
Yes, that is true but there are some caveats with that bill.
1. Is it detailed enough?
2. Can I track usage?
3. Can I track charged databases (Multitenant)?
4. Can I add internal costs?
At least for question 1 – 3 the answer is no it is not possible. You get a bill and it is for the VM. Another problem is that you get your bill for what you have allocated. So if you allocated 10 CPU’s then you pay for the allocation of 10 CPU’s.
So whats happen if you adding more than one database or if you adopting Multitenant? You will still get one bill from the cloud provider and have no clue if one database/PDB uses more resources than another.
One reason for adopting a consolidated environment is that you have the ability to consolidate in a way that some applications uses more I/O and another uses more CPU or that one database is active during certain hours and another during other hours. So you plan your consolidation to utilize resources as efficient as possible and if you have some resource conflicts then we have different solutions to constrain resources such as I/O resource manager instance caging with CPU Count…. Planning your consolidation can be done by Migration Planner in Enterprise Manager.
So now we are in the situation where we have several databases/PDB’s and we want to charge on the utilization. Chargeback included in Cloud Management pack can extract usage and configuration data and use it to calculate resource utilization for individual databases and PDB’s. So we have the ability to divide cloud bill charges but there are some limitations since Chargeback was built for charging On-premise entities.
I will in a series of blog posts with including videos describe how Chargeback works and how it can be used for re-charging a cloud bill
Adobe Flash is coming to its end-of-life on December 31, 2020. Adobe has said that it will block the Flash player from running after that date.
So how will this impact Enterprise Manager?
Earlier versions of Enterprise Manager was written mostly with Flash as UI technology. It was actually first in Enterprise Manager 13.3.2.0 (EM 13.3 PG), released in 2019 we replaced Flash with Oracle JET Technology.
So how will this impact Enterprise Manager? I assume many of you have been using Database Performance pages in Enterprise manager and key database performance pages were written in Flash. When Adobe blocks Flash player to run it will also block these pages and tests that we have done indicate that this is true, so all of these Flash will be blocked.
So the solution is to upgrade and our recommendation is 13.4
From Database version 12.2 we have enhanced the capture feature. This enhancement have provided us with the ability to capture the first level of recursive calls from PL/SQL procedures.
When Database Replay was released for 11.1 it was designed to have minimal impact on the production database so we restricted the capture process to only capture the top level call to the procedure (exec {procedure_name}).
For the majority of workloads this have been sufficient and the replay have had minor to no issues. But for some workloads and especially workloads with long running PL/SQL procedures it has caused workloads to hang or provide high divergence.
An example of this problem can be found when doing test captures on Swingbench load generator. The default mode for Swingbench is server side load generation where the load generator sends a call to a PL/SQL procedure and the procedure drives the workload. As a load generator it is good since there are minimal network traffic and you can therefore add high load with from a small computer or even use the database server as a load generator.
The problem that occurs for database replay is that the calls that we capture is the PL/SQL call and the PL/SQL is then doing a work based on values some random generated values. Such as random think time implemented with the random function. amount of orders generated before exiting and so on.
When replaying this workload we execute the PL/SQL but the procedure will have different think time and different amount of orders generated due to that the random function produce a different value during replay.
The result is that the replay do not behave in same way a procedure that was executing for 5 minutes might take 7 minutes during replay which will cause the replay to take longer time.
The solution for this was to use client side swingbench where the client executes each sql with think time passed from the client. So when capturing we capture the SQL/DML with it’s timing.
In 12.2 we introduced extended capture. With this feature instead of capturing the PL/SQL call we are capturing every single SQL/DML executed inside the procedure. If there is a loop executing one statement 1000 times with different binds for each execution then we are capturing 1000 calls to the database with it’s bind variables and during replay we executes these 1000 calls based on the timing during capture
We are now able to replay server side Swingbench and other applications with lot’s of long running PL/SQL.
There some caveats with this such as:
The user who executes the PL/SQL might just have execute privileges on the PL/SQL. And might not have select, insert, update and delete privileges on the table. During replay we are executing the SQL/DML as the connecting user so this user needs to have access to all those objects.
PL/SQL calls inside the procedure. If there are several layers of PL/SQL then second layer will be executed as a PL/SQL call. we are not able to capture on second or third level of recursive calls. In most case this is sufficient since second and third layer often are calls to short running PL/SQL used for validation och to collect some data to be used in the top level PL/SQL
The default mode for Capture is still the Top level capture so to be able to enable the feature you have to explicitly define it
A workload captured in Extended mode will by default be replayed in PL/SQL Top level mode. To be able to leverage the extended mode in replay it needs to be explicitly defined here as well.