![]() ![]() Still logged in as the test user, issue the following statements. This schema represents our "before" state. Next, connect to the test user and create a test table called MY_OBJECTS using a query from the ALL_OBJECTS view.ĮXEC DBMS_STATS.gather_table_stats(USER, 'MY_OBJECTS', cascade => TRUE) GRANT CONNECT, CREATE TABLE TO spa_test_user The following code creates a test user called SPA_TEST_USER.ĬREATE USER spa_test_user IDENTIFIED BY spa_test_user We are only trying to demonstrate the technology, so the example can be really simple. 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. Running the SQL Performance Analyzer using Enterprise Manager.Creating SQL Tuning Sets using Enterprise Manager.Running the SQL Performance Analyzer using the DBMS_SQLPA Package.Creating SQL Tuning Sets using the DBMS_SQLTUNE Package.This article gives an overview of both methods. The SQL Performance Analyzer can be run manually using the DBMS_SQLPA package or using Enterprise Manager. It just plugs through each statement gathering performance statistics. Unlike Database Replay, the SQL Performance Analyzer does not try and replicate the workload on the system. Schema changes, such as adding indexes or materialized views.Database initialization parameter changes.Database, operating system, or hardware configuration changes.Database, operating system, or hardware upgrades.The database change can be as major or minor as you like, such as: 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 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. Home » Articles » 11g » Here SQL Performance Analyzer in Oracle Database 11g Release 1 ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |