Since 2010, OraERP is a Oracle Forums, Community of Oracle Professionals including Fusion/Cloud Application Consultants, Enterprise Architects, ERP Cloud, HCM Cloud, CX Cloud and OCI Experts, ERP Experts, Oracle Apps Functional Consultants, Apps DBAs, DBAs, Cloud DBAs, Digital Architect, PaaS Experts, IaaS, OCI Architects, Technical Consultants, Fusion Middleware Experts, SQL, PL/SQL Developers and Project Managers. Welcome to OraERP.com Social Community, a friendly and active community of Oracle Technology Professionals who believe that technology can ‘make the world a better place’. By joining Oracle ERP Community you will have the ability to Post Topics, Receive our Newsletter, subscribe to threads and access many other special features. Registration is Quick and Simple. Get unlimited access to Oracle Tutorials, Articles, eBooks, Tools and Tips .
Oracle Forum - The Knowledge Center for Oracle Professionals - Looking Beyond the Possibilities
›
Platform as a Service (PaaS)
›
Oracle Technology
›
Oracle Database
SQL Performance Analyzer in Oracle Database 11g
SQL Performance Analyzer in Oracle Database 11g
Poll: Which three statements about performance analysis by SQL Performance Analyzer are true? You do not have permission to vote in this poll. |
|||
It detects changes in SQL execution plans. | 0% |
0 | 0% |
It produces results that can be used to create the SQL plan baseline. | 0% |
0 | 0% |
The importance of SQL statements is based on the size of the objects accessed. | 0% |
0 | 0% |
It generates recommendations to run SQL Tuning Advisor to tune regressed SQLs. | 0% |
0 | 0% |
It shows only the overall impact on workload and not the net SQL impact on workload. | 0% |
0 | 0% |
Total | 0 vote(s) | 0% |
* You voted for this item. | [Show Results] |
SQL Performance Analyzer in Oracle Database 11g
|
03-28-2011, 06:31 PM,
|
|||
|
|||
SQL Performance Analyzer in Oracle Database 11g
The concept of SQL tuning sets, along with the DBMS_SQLTUNE package to manipulate them, was introduced in Oracle 10g as part of the Automatic SQL Tuning functionality. Oracle 11g makes further use of SQL tuning sets with the SQL Performance Analyzer, which compares the performance of the statements in a tuning set before and after a database change. The database change can be as major or minor as you like, such as:
Unlike Database Replay , the SQL Performance Analyzer does not try and replicate the workload on the system. It just plugs through each statement gathering performance statistics. The SQL Performance Analyzer can be run manually using the DBMS_SQLPA package or using Enterprise Manager. This article gives an overview of both methods.
Setting Up the Test The SQL performance analyzer requires SQL tuning sets, and SQL tuning sets are pointless unless they contain SQL, so the first task should be to issue some SQL statements. We are only trying to demonstrate the technology, so the example can be really simple. The following code creates a test user called SPA_TEST_USER. Quote:CONN / AS SYSDBA Next, connect to the test user and create a test table called MY_OBJECTS using a query from the ALL_OBJECTS view. Quote:CONN spa_test_user/spa_test_userThis schema represents our "before" state. Still logged in as the test user, issue the following statements. Quote:SELECT COUNT(*) FROM my_objects WHERE object_id <= 100; Notice, all statements make reference to the currently unindexed OBJECT_ID column. Later we will be indexing this column to create our changed "after" state. The select statements are now in the shared pool, so we can start creating an SQL tuning set. Creating SQL Tuning Sets using the DBMS_SQLTUNE Package The DBMS_SQLTUNE package contains procedures and functions that allow us to create, manipulate and drop SQL tuning sets. The first step is to create an SQL tuning set called spa_test_sqlset using the CREATE_SQLSET procedure. Quote:CONN / AS SYSDBA Next, the SELECT_CURSOR_CACHE table function is used to retrieve a cursor containing all SQL statements that were parsed by the SPA_TEST_USER schema and contain the word "my_objects". The resulting cursor is loaded into the tuning set using the LOAD_SQLSET procedure. Quote:DECLARE The DBA_SQLSET_STATEMENTS view allows us to see which statements have been associated with the tuning set. Quote:SELECT sql_text Now we have an SQL tuning set, we can start using the SQL performance analyzer. Running the SQL Performance Analyzer using the DBMS_SQLPA Package The DBMS_SQLPA package is the PL/SQL API used to manage the SQL performance ananlyzer. The first step is to create an analysis task using the CREATE_ANALYSIS_TASK function, passing in the SQL tuning set name and making a note of the resulting task name. Quote:CONN / AS SYSDBA Next, use the EXECUTE_ANALYSIS_TASK procedure to execute the contents of the SQL tuning set against the current state of the database to gather information about the performance before any modifications are made. This analysis run is named before_change. Quote:BEGIN Now we have the "before" performance information, we need to make a change so we can test the "after" performance. For this example we will simply add an index to the test table on the OBJECT_ID column. In a new SQL*Plus session create the index using the following statements. Quote:CONN spa_test_user/spa_test_user Now, we can return to our original session and test the performance after the database change. Once again use the EXECUTE_ANALYSIS_TASK procedure, naming the analysis task "after_change". Quote:BEGIN Once the before and after analysis tasks are complete, we must run a comparison analysis task. The following code explicitly names the analysis tasks to compare using name-value pairs in the EXECUTION_PARAMS parameter. If this is ommited, the latest two analysis runs are compared. Quote:BEGIN With this final analysis run complete, we can check out the comparison report using the REPORT_ANALYSIS_TASK function. The function returns a CLOB containing the report in 'TEXT', 'XML' or 'HTML' format. Its usage is shown below. Note. Oracle 11gR2 also includes an 'ACTIVE' format that looks more like the Enterprise Manager output. Quote:SET PAGESIZE 0 An example of this file for each available type is shown below.
Creating SQL Tuning Sets using Enterprise Manager Click on the "SQL Tuning Sets" link towards the bottom of the "Performance" tab. On the "SQL Tuning Sets" screen, click the "Create" button. Enter a name for the SQL tuning set and click the "Next" button. Select the "Load SQL statements one time only" option, select the "Cursor Cache" as the data source, then click the "Next" button. Set the appropriate values for the "Parsing Schema Name" and "SQL Text" filter attributes, remove any extra attributes by clicking their remove icons, then click the "Next" button. Accept the immediate schedule by clicking the "Next" button. Assuming the review information looks correct, click the "Submit" button. The "SQL Tuning Sets" screen shows the confirmation of the tuning set creation and the scheduled job to populate it. Once the population job completes, clicking on the SQL tuning set displays its contents. Now we have an SQL tuning set, we can start using the SQL performance analyzer. Running the SQL Performance Analyzer using Enterprise Manager Click the "SQL Performance Analayzer" link on the "Software and Support" tab. Click the "Guided Workflow" link on the "SQL Performance Analayzer" screen. Click the execute icon on the first step to create the SQL Performance Analyzer task. Enter a name for the SPA task, select the SQL tuning set to associate with it, then click the "Create" button. When the status of the previous step becomes a green tick, click the execute icon on the second step to capture the SQL tuning set performance information of the "before" state. Enter a "Replay Trial Name" of "before_change", check the "Trial environment established" checkbox, then click the "Submit" button. When the status of the previous step becomes a green tick, click the execute icon on the third step to capture the SQL tuning set performance information of the "after" state. Alter the state of the database by creating an index on the OBJECT_ID column of the test table. Quote:CONN spa_test_user/spa_test_user@prod Enter a "Replay Trial Name" of "after_change", check the "Trial environment established" checkbox, then click the "Submit" button. When the status of the previous step becomes a green tick, click the execute icon on the forth step to run a comparison analysis task. Accept the default "Trial 1 Name" and "Trial 2 Name" settings by clicking the "Submit" button. When the status of the previous step becomes a green tick, click the execute icon on the fifth step to view the comparison report. The resulting page contains the comparison report for the SQL Performance Analyzer task. Clicking on a specific SQL ID displays the statement specific results, along with the before and after execution plans. Optimizer Upgrade Simulation The SQL Performance Analyzer allows you to test the affects of optimizer version changes on SQL tuning sets. Click the "Optimizer Upgrade Simulation" link on the "SQL Performance Analyzer" page. Enter a task name, select the two optimizer versions to compare, then click the "Submit" button. The task is listed in the "SQL Performance Analyzer Tasks" section. Refresh the page intermittently until the task status becomes a green tick, then click on the task name. The resulting screen shows details of the selected task. Click on the "Comparison Report" classes icon allows you to view the comparison report. Parameter Change The SQL Performance Analyzer provides a shortcut for setting up tests of initialization parameter changes on SQL tuning sets. Click the "Parameter" link on the "SQL Performance Analyzer" page. Enter a task name and the parameter you wish to test. Enter the base and changed value, then click the "Submit" button. The task is listed in the "SQL Performance Analyzer Tasks" section. Refresh the page intermittently until the task status becomes a green tick, then click on the task name. The resulting screen shows details of the selected task. Click on the "Comparison Report" classes icon allows you to view the comparison report. Transferring SQL Tuning Sets In the examples listed above, the tests have been performed on the same system. In reality you are more likely to want to create a tuning set on your production system, then run the SQL Performance Analyzer against it on a test system. Fortunately, the DBMS_SQLTUNE package allows you to transport SQL tuning sets by storing them in a staging table. First, create the staging table using the CREATE_STGTAB_SQLSET procedure. Quote:CONN sys/password@prod AS SYSDBA Next, use the PACK_STGTAB_SQLSET procedure to export SQL tuning set into the staging table. Quote:BEGIN Once the SQL tuning set is packed into the staging table, the table can be transferred to the test system using Datapump, Export/Import or via a database link. Once on the test system, the SQL tuning set can be imported using the UNPACK_STGTAB_SQLSET procedure. Quote:BEGIN The SQL tuning set can now be used with the SQL Performance Analyzer on the test system. More details you can find from attached Doc. |
|||
12-23-2019, 07:17 PM,
|
|||
|
|||
RE: SQL Performance Analyzer in Oracle Database 11g
инфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфо инфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфо инфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинйоинфоинфоинфоинфоинфо инфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфо инфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфо инфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфо инфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфо инфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфо инфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфо |
|||
|
« Next Oldest | Next Newest »
|
Possibly Related Threads… | |||||
Thread | Author | Replies | Views | Last Post | |
Oracle Export and Import Utility | Pryia Rai | 1 | 4,454 |
01-07-2020, 01:52 PM Last Post: viggora |
|
Oracle Architecture in less than 10 minutes | admin | 2 | 8,939 |
12-31-2019, 11:12 PM Last Post: viggora |
|
Manual Oracle Uninstall | admin | 2 | 5,327 |
12-26-2019, 11:33 PM Last Post: viggora |
|
oracle database 12c installation error | Ahmad Mujeeb | 2 | 3,864 |
12-24-2019, 06:57 AM Last Post: viggora |
|
location of alert log in 11g | Erica Kristen | 3 | 4,322 |
12-24-2019, 06:29 AM Last Post: viggora |
|
ORA-00600: internal error code, arguments: [17182] - adding standby database to broke | Pryia Rai | 3 | 6,543 |
12-24-2019, 04:23 AM Last Post: viggora |
|
Oracle error messages | Pryia Rai | 2 | 4,155 |
12-24-2019, 04:10 AM Last Post: viggora |
|
Oracle 11gR2 How to find user session | Sara Khan | 2 | 3,474 |
12-24-2019, 03:15 AM Last Post: viggora |
|
ORACLE HOME issue with DBCA | Richard | 3 | 5,537 |
12-24-2019, 01:29 AM Last Post: viggora |
|
WFMLRSVCApp.ear file missing 11g Installation | admin | 3 | 31,139 |
12-24-2019, 12:17 AM Last Post: viggora |
Users browsing this thread: 1 Guest(s)
Current time: 10-04-2024, 12:14 AM
Powered By MyBB, © 2002-2024 MyBB Group.