? Use SQL Optimization guidance for:– Identify the SQL statements that use the most resources – optimize the SQL statements that use the most resources? Optimize workloads with SQL access guidance
SQL optimization process? Identify SQL statements that are not well optimized. Optimize each statement. Optimize the entire application. In general, the most obvious optimization work is SQL optimization. SQL that is not well optimized will unnecessarilyuse too many resources. This inefficiency reduces scalability, uses more OS and database resources, and increases response time. to optimize SQL statements that are not well optimized, you must first identify these statements before you optimize them. Can beto optimize each SQL statement individually, but optimizing the resolution of one statement often affects multiple other statementsthe performance. As the name implies, the SQL statements that use the most resources are statements that need to be optimized. These statements are used for the longest time,the most CPU resources, or the most physical or logical read times. The ways to refine each statement are:Check Optimizer statistics，Check the interpretation plan for the most efficient access pathMeasuringtry to replace SQL constructs, and test for possible new indexes, manifested views, and partitions. Test the performance of the entire application using the optimized SQL statement. Is overall performance improved? The method is reliable, but somewhat dilatory. Optimizing a single statement is not difficult. Test each statement to optimize the overall shadow of the applicationIt may be very difficult to sound. In Oracle Database One, you can use a set of SQL guidance to identify and refine statements (individually optimized or collectivelyoptimization).
Oracle Database One G provides a set of SQL guidance: SQL access guidance, SQL tuning guidance, SQL performancethe debugger and SQL repair instructions. AWR determines the most recent high-load SQL statement and records the relevant statistics. SQL Optimization guidance parses one or more SQL statements (one at a time). It checks statistics, SQLprofiles, indexes, manifested views, and adjusted SQL. SQL optimization guidance can be shipped manually at any time, but is primarily run against the most recent high-load SQL statement during each maintenance window. Click the AutomaticSQL Tuning Results (Automatic SQL optimization results) to view and implement recommendations. This automated job can beconfigured to automatically implement recommended SQL profiles for high-load statements. The SQL Access Guide examines changes that are applied to a set of SQL statements to determine whether there is a net improvement in performance. This set of statements can becan be a set of hypothetical SQL statements, past statements, or manually created statements. SQL Performance Analyzer can be used to predict and prevent any changes in the database environment that affect the SQL execution plan structurepotential performance issues. The SQL repair guidance is run from the Support Workbench when an SQL statement fails due to a fatal error. Serious errors Alsocan produce unexpected events. The repair guide tries to find and recommend a SQL patch. If no patches are foundprogram, you can then package an unexpected event in the Support workbench and use the unexpected event package as a service request (SR)submitted to Oracle technical support.
- Automatic SQL Optimization Results
By default, the automatic SQL tuning task runs every night. The automatic SQL optimization Results link will show the results overviewto the page. If you click View Report, you can view each SQL statement that is checked. Clicking the Configure button displays a page where you can change the automatic optimization task'sdefault settings and enable the SQL Profile Auto-implementation feature.
- Implement automated optimization recommendations
If you click the View on the Automatic Tuning Results Summary (Auto-tuning Results Summary) pagereport (View reports) button, the Automatic SQL Tuning Result Details is displayed (Automatic SQL excellentResults Details) page. You can implement all recommendations, or refine them to view or implement individual recommendations. in theon the Recommendations page, you can click the glasses icon on the right to view the implementation of the SQL profileimpact on the interpretation plan.
- SQL Optimization Guide: overview
Comprehensive SQL optimization detects outdated or missing statisticsInformationOptimizing SQL Plans(SQL profile)Add missing index tuning SQL structure SQL tuning guidance is the primary driver used in the optimization process. It can perform several types of analysis:? Statistical information Analysis: Check that each query object is missing statistics or whether statistics are obsolete, and then makeIt is recommended to collect relevant statistical information. ? SQL Profile Analysis:The optimizer validates its own estimates and collects ancillary information to eliminate estimation errors. Itbuild the SQL profile using the auxiliary information and suggest the creation of the SQL profile. Create a SQL profilefile, the query optimizer can generate a reasonably optimized plan from this file.? Access Path Analysis:Investigate whether the new index will significantly improve access performance for each table in the query. If appropriate,It is recommended that you create such an object. ? SQL Structure Analysis: Determine the SQL statement that used the error plan and make structural adjustments to these statementsof the relevant recommendations. Suggested changes may involve syntactic changes or semantic changes. The SQL Optimization Guide examines each SQL statement included in the guidance task separately. Creating a new index Maymay increase the response time of the DML. Therefore, the recommended index should be checked using SQL Access guidanceor other objects affect the amount of work (a set of SQL statements) to determine whether there is a net improvement in performance.
- Using SQL Optimization Guidance
? Use SQL optimization guidance to parse SQL statements and get performance recommendations. Source of SQL Optimization Guidance Analysis: – Top-level activity: Analyze the top-level SQL statement currently active –SQL optimization set: Analyze a user-supplied set of SQL statements – Previous SQL (AWR): Analyze the SQL statements collected by the AWR snapshotthe statements inSQL Tuning guidance runs automatically every night as an automatic SQL optimization task. Sometimes, you may need to have aSQL statements are immediately optimized. You can use SQL optimization to guide Analysis of SQL statements at any time and gain performancerecommendations. Typically, running this guidance is similar to using ADDM to find performance problems. In addition, you can run SQL tuning guidance to analyze which SQL statements consume the most CPU time, I/O, and memory. Even if multiple statements are submitted for analysis in a single task, the analysis of each statement is still performed separately. To obtainconsider an optimization recommendation for the overall performance of a set of SQL statements, using SQL Access guidance.
- SQL Tuning Guidance Options
On the Schedule SQL Tuning Advisor (Scheduled SQL Tuning Guide) page, you can select the SQL that you want to includestatement, and change the automatic default settings for the optimization task. You can set the source of the SQL statement;Advisor system permissions, you can submit tasks. Oracle Enterprise Manager then guides the SQL optimizationCreate an optimization task. The SQL statement option allows you to select one or more SQL statements from the following sources: recent top-level activities,previous SQL statements stored in the AWR or SQL optimization set created. It is important to select the appropriate scope for the optimization task. If you select the Limited option, the SQL tuning guidegenerate recommendations based on statistics check results, access path analysis results, and SQL structural analysis results. "Limited(with restrictions) option does not produce SQL profile recommendations. If you select the "Comprehensive (Integrated)" optionThe SQL Optimization Guide will not only generate all the recommendations generated by the "Limited (limited)" option, in the SQL overviewProfiling mode also invokes the optimizer to build the SQL profile. Use the comprehensive (integrated) selectionitem, you can also specify a time limit for the tuning task, and the default value for that time limit is 30 minutes. Select the RunSQL Tuning Advisor (running SQL Tuning Guide), use the SQL Tuning Options (SQLoptimization option) page to configure the optimization task.
- SQL Tuning Guidance Recommendations
The SQL optimization results for a task are displayed immediately after the task is completed, or later through the Advisor central (referring toGuide Center) page to access these optimization results. A summary of the suggestions is also displayed. Individual recommendations can be reviewed and implemented. Select the statement and click View.
- Using SQL Optimization Guidance: Example
SQL Tuning guidance can be invoked by performing the following steps: 1. In the Database home page, click Related links in the area of"Advisor Central (Guidance Center)". 2. Click SQL Advisors (SQL guidance). The SQL Tuning Advisor Links dialog box appears, SQLOptimization Guidance link) page. This guidance can be run on any of the following sources:-Active SQL (Active SQL): parsing the currently active top-level SQL statement-SQL Tuning sets (SQL tuning Set): Parsing a user-supplied set of SQL statements-historical SQL (AWR) (formerly SQL (AWR)): Analyze the SQL captured by the AWR snapshotStatement3. Select Active SQL (active SQL). Select the five-minute interval you want to analyze by dragging the gray box to thethe target time period. Select one or more statements to parse within the selected period. 4. Click Run SQL Tuning Advisor (running SQL Tuning Guide). The SQL Tuning will appearOptions (SQL optimization option) page, which displays the SQL statements during this time interval. Provide the name of the task andDescription, select "Comprehensive (Integrated)" As the scope, select "Immediately (Immediate)" asis the start time. Click OK. 5. Re-navigate to the Advisor Central (Guide) page. The status of the guided task is listed in the Results (knotbelow this heading in the area. Please wait until the task status becomes completed. By clicking BrowseRefresh Check status in the filter. Select the task and click View Result.the SQL Tuning result (SQL Optimization results) page is displayed. 6. Select the SQL statement and click View Recommendations (view suggestions).
A repeating SQL statement is a statement that is just a different literal value or format. Each of the different statements in Kugauthere will be a separate cursor in the cache. If you replace the text with a bound variable and the uniform format, the duplicatestatements can use the same cursor. By clicking Performance in the Additional monitoring links (other monitoring links) area (sexcan) tab page, Duplicate SQL (Duplicate SQL), to determine the duplicate SQL statement. have beenSQL that is determined to be duplicated (regardless of formatting or text differences) is listed together. This will help to determine the applicationwhich SQL can be merged, thus reducing the need for library caches and increasing the execution speed of statements.
- SQL Access Guide: overview
Evaluate the full workload of SQL and recommend indexes, partitioning, and manifested views that will improve the overall performance of SQL workloads. SQL Access guidance can suggest a suitable set of manifested views, manifested view logs, partitions, andindex. Understanding and using these structures is critical when optimizing SQL, because these structures can be veryThe Earth improves performance. SQL Access guidance recommends usingbitmap indexes, function-based indexes, and B-Tree indexes。 Bitmap indexing can reduce many classesresponse time for ad hoc queries, and reduces storage requirements relative to other indexing techniques. B-Tree Index inmost commonly used in data warehouses to index unique or near-unique keywords. Another component of the SQL Access Guide also provides recommendations for optimizing the manifested view so that these views can be quickly refreshedand rewrite it with regular queries.
- Typical SQL access-guided sessions
When you start the SQL Access guidance session, you can select the Use default options (using defaults) to setthe recommended pre-defined guidance options begin. Alternatively, you can select the Inherit options from a Task or Template(Inherit from task or template option) "To start a task and have the task inherit a set of options defined by a template or taskvalues. These templates include multiple generic templates for general-purpose environment, OLTP, and Data Warehouse database design. You canSave the custom template from the previous task and reuse it if needed. Click Continue (Continue) to start the SQL Access Guidance Wizard.
Use the Workload source page of the SQL Access Guidance Wizard to provide a defined amount of work toAccess guidance can generate recommendations. The supported workload sources include:? Current and recent SQL activity (currently and most recent SQL Activities): using cache whenpre-SQL as workload? Use of an existing SQL Tuning set (using an existing SQL optimization set): You can add the previously created SQLthe optimization set is specified as the workload source? Create a hypothetical Workload from the following Schemas and Tables (from the following PartiesScenarios and tables create hypothetical workloads): Provide a scenario for access guidance you can search the dimension table and generate workloadYou can further reduce your workload by applying the filters available in the Filter Options sectionthe range. Using these options, you can narrow down the range of SQL statements in your current workload. Guide the filter should beafter the workload is used, you can optimize the focus section. The available filter options are:? The SQL statement that consumes the most resources? User, module identifier, or action? Table
Use the Recommendations Options page to choose whether to restrict guidance based on a single accessThe law suggests. Select from the section "Access structures to recommend (the accessible structure to be recommended)"Indexes (index), materialized views (manifested view), partitioning (partitions)or any combination of the above. You can select Evaluation only (evaluation only) to evaluate only the existingaccess the structure. In this mode, the guidance does not generate new proposals, but rather provides comments on the use of existing structures. This item isIt is useful to track the current index, the validity of the manifested view, and the change in MV log usage over time. You can use the "Advisor mode" section to run instructions in one of two modes. These patterns willaffect the quality of the recommendations and the time required for processing. In comprehensive (integrated) mode, the guide will search forlarge pool of candidates to get the highest quality recommendations. In the Limited (restricted) mode, guide the executionvery fast, but will limit the candidate recommendations. You can select advanced options to show or hide the options that you can use to setspace limits, optimization options, and default storage locations. Use "Workload categorization (Workload category)"section You can set the Workload volatility (workload instability) and Workload Scope (workload"option. When generating recommendations, you can choose to focus on read-only operations, or to investigate the instability of referenced objects. You'reYou can also select partial Workload (partial workload), which does not include the deletion of unused access structuresSelect "Complete Workload (total effort)", which includes removing unused access structures's recommendations. Use the Space Restrictions section to specify hard space limits and to force instructions to use onlyThe total space requirement over the specified limit generates recommendations. Use the Tuning Options section to specify options to customize the recommendations that guide the build. Use "Prioritize Tuning of SQL statements by (to determine the priority of optimizing SQL statements)" drop-downlist, you can press Optimizer cost (optimizer overhead), Buffer gets (number of buffers) ,CPU Time (CPU), disk Reads (disk reads), Elapsed time (spents)and "Execution count (execution count)" is prioritized. Use the default Storage Locations (storage location) section to override the location of the schema and tablespacethe default value for justification. By default, the index is placed in the schema and table space of the referenced table. The manifested view is placed in thein the corresponding user's scenario and table space, one of the queries that the user executes provides information for the manifested view suggestion. After defining these parameters, you can schedule and review the optimization tasks.
Use the Advisor Central page to list all completed SQL Access guidance tasks. Chooseto view the proposed task, then click the View Result button. Use the Results for TaskSummary (Summary) page of the task results, which provides an overview of the guidance for finding results. This page gives a list ofcharts and statistics, providing suggestions for improvements that might result in overall workload performance and query execution time. Usethe page can display the statement count and the recommended action count. To view other aspects of the results of a guided task, click one of the other three tabs on the page: "Recommendations(recommended), "SQL statements (SQL statement)" or "details". A chart and a table are provided in the recommendations (recommended) page, where the overall effort is recommendedThe total cost improvement percentage displays top-level recommendations. Top recommendations improve overall performance in the largest range. Click the Show SQL button to view the SQL scripts generated for the selected recommendation. In the tableClick the appropriate suggestion identifier to see a list of actions that need to be taken to implement the recommendation. In the actionspage, you can view all the appropriate SQL statements that the implementation requires to execute. For recommendations that do not wish to be implemented, makeThe corresponding check box is unchecked. Then, click Schedule Implementation (Schedule implementation) to pressthe button to implements the reserved operation. This step is performed in the form of a scheduler job.
- SQL Performance Analysis Program: overview
? Target User: DBA, QA, application developer? Help predict the impact of system changes on SQL workload response time? Build different versions of SQL workload performance data (that is, SQL executionplanning and execution statistics)? Execute SQL serially (regardless of concurrency)? Analyze performance differences? Provide fine-grained performance analysis for a single SQL? Integrated with SQL optimization guidance to optimize regression Oracle Database One g includes the SQL Performance Analyzer, which accurately evaluates changes to the amount ofthe effect of the SQL statement. SQL Performance Analyzer helps predict the performance of potential changes to the SQL query effortTo the sound. This feature provides DBAs with detailed information about the performance of SQL statements, such as pre-and post-execution statistics, andA statement that has improved or decreased performance. This allows you to make changes (for example) in the test environment to determine the datawhether the upgrade of the library will improve workload performance.
- SQL Performance Analyzer: Usage scenarios
SQL Performance Analyzer can be used to predict and prevent any changes in the database environment that affect the SQL execution plan structurepotential performance issues. These changes may include, but are not limited to, any of the following changes:? Database upgrade? Implement optimization recommendations? Program changes? Collect statistical information? Change database parameters? Changing the operating system and hardware you can use SQL Performance Analyzer to analyze the SQL performance changes caused by the most complex environment changes. As the applicationas the program advances in the development lifecycle, database application developers can test for scenarios, database objects, andrewrite the application's changes to mitigate any potential performance impact. SQL Performance Analyzer can also compare SQL performance statistics. SQL Performance Analyzer can be accessed through Oracle Enterprise Manager or using the Dbms_sqlpa package.
- Using SQL Performance Analyzer
1. Capture the SQL workload for the production environment. 2. Transfer the SQL workload to the test system. 3. Build "Pre-change" performance data. 4. Make the changes. 5. Build "post-change" performance data. 6. Compare the results of step 3 and step 5. 7. Optimize the SQL for regression. 1. Collect sql: In this phase, you collect the set of SQL statements that represent the SQL effort in the production system. 2. Transfer: The resulting workload must be transferred to the test system. Export the STS from the production system, and thenthen import the test system. 3. Calculate "Previous Version" performance: Execute the SQL statement before making any changes to collect and evaluate futureThe baseline information required to change the possible impact on workload performance. 4. Make changes: Once you have obtained the previous version data, you can implement the planned changes and then start looking at the performanceimpact. 5. Calculate "later version" performance: After you make changes in the database environment, perform this step. SQL workloadrun in virtual execution mode (collect statistics only), and collect the data that you captured in step 3The same information. 6. Compare and analyze SQL performance: After you have obtained two versions of SQL workload performance data, you can comparewith previous versions of the data for performance analysis. 7. SQL optimized for regression: In this phase, you have accurately identified which SQL statements are making database changesmay cause performance problems. You can use any of the database tools to optimize your system. In the implementation of any excellentYou should repeat the process to create new, later version data, and then analyze performance differences to ensure newcan be acceptable.
? Use SQL Optimization guidance for:– Identify the SQL statements that use the most resources – optimize the SQL statements that use the most resources? Optimize workloads with SQL access guidance
Source: http://blog.csdn.net/rlhua/article/details/13021985
