In 10 Gb, Oracle launched its own auxiliary SQL optimization tool: SQL optimizer (SQL Tuning Advisor: STA), which is a new DBMS_SQLTUNE package. To use the STA, make sure that the optimizer is in CBO mode. However, I think this tool is only suitable for those who do not full
dbms_workload_repository. Create_snapshot;We can also use the Dbms_workload_repository package to complete the baseline, modification of the default settings and so on.ADDM (Automatic Database Diagnostic Monitor AWR)is a consultant system within Oracle that can automate some of the optimization recommendations for the most databases, give recommendations for SQL optimization, index creation, and statistics
Chapter 2 procedural performance tuning of PL/SQL applications
I. Reasons for PL/SQL Performance problems
When the execution efficiency of PL/SQL-based applications is low, it is usually caused by poor SQL statements and programming methods, poor understanding of PL/
SQL Optimization
Oracle SQL optimization is a complex topic, and even requires the entire book to introduce the nuances of Oracle SQL optimization. However, there are some basic rules that every Oracle DBA must follow. These rules
performance, and to control their sum within the value specified by the Sga_target. Once you assign a value to Sga_target (the default is 0, that is, ASMM is not started), the ASMM feature is started automatically.iii. Oracle Memory tuning approachWhen a performance problem arises in the production environment of a project, how do we determine which parameters need to be adjusted? 3.1 Check the library cac
actually specified as the sqlid of the statement) --Generates an AWR report of an SQL statement, runs the script Awrsqrpt.sql
Awrsqrpi.sql: Generates a statistical report of the specified SQL statement for the specified DB instance, specifying the snapshot interval;
Awrddrpt.sql: Specify two different time periods, generate a statistical comparison report of the two periods;
Awrddrpi.sql: Speci
Read Catalogue
(1) Select the most efficient table name order (valid only in the rule-based optimizer)
(2) connection order in the WHERE clause
(3) Avoid using ' * ' in the SELECT clause
(4) Reduce the number of accesses to the database
(5) Reset the ArraySize parameter in Sql*plus, sql*forms and pro*c to increase the amount of data retrieved per database access, with a recommended value of 200
Label: Read Catalogue
(1) Select the most efficient table name order (valid only in the rule-based optimizer)
(2) connection order in the WHERE clause
(3) Avoid using ' * ' in the SELECT clause
(4) Reduce the number of accesses to the database
(5) Reset the ArraySize parameter in Sql*plus, sql*forms and pro*c to increase the amount of data retrieved per database access, with a recommended
An outline that is roughly written in the optimization content of the physical design and logical design of the DB, mainly physical design, logical design introduction of the content is not much, the outline of the physical structure design and example optimization of organic combination of the logical structure design and application adjustment together ...
Oracle Performance Tuning Guidance outline
Desi
Client protocols
Aliases
The Protocol is the same as (2). You can specify more configurations.
3. sp_configure/reconfigure
Displays or changes the global configuration settings of the current server. Many configurations need to be set through sp_configure.
Syntax:
sp_configure [ [ @configname = ] 'option_name' [ , [ @configvalue = ] 'value' ] ] reconfigure
For example:
To configure advanced options with sp_configure, you must first run sp_
optimization phase. If the difference between the two is very large, the actual performance statistics are stored as statistical information feedback, and the statement is also marked as re-optimized. When the statement is executed the next time, statistical feedback is used to select a more appropriate degree of parallelism (DOP).--Note:1) from the Oracle 11GR2 backwards, the parallel hint in the statement will cause the system to automatically sele
SQL Auto-tuning is an Oracle-brought tuning tool that provides solutions.This time I mainly introduce the following automatic SQL tuning tools:
Automatic SQL
at the statement level, the hint syntax is:/*+ bind_aware*/;Ø about outlines: In the scene of outlines existence, the ACS will fail whether the system level is enabled or the statement level is enabled through hint;6. Conclusion:New features of Oracle Database 11g The ACS feature provides an easy way to efficiently share SQL statement execution plans with bound variables. Because ACS produces a new executi
Oracle 11g has made some improvements in optimization. Oracle automates the following optimization:
Redo Logfile Sizing Advisor-this feature recommends Redo the optimal size of log files to avoid excessive disk I/O caused by frequent checkpoints.
Automatic Checkpoint Tuning-Oracle database can now optimize checkp
leave a message or private messages, and look forward to an in-depth study of your child's shoes with SQL Server. Learn together and progress together.At the end of the article gives the previous several connections, the following content basically covers our daily write query operations decomposition and tuning content items, are original, it seems necessary to sort out a directory ....
Preface
In the past decade, Oracle has become one of the world's most professional databases. For IT experts, it is to ensure that their company's productivity is improved by leveraging the powerful features of oracle. One of the most effective methods is to use ORACLE optimization. It has a lot of parameters and technologies to improve the performance of your
In the past decade,OracleIt has become one of the most professional databases in the world. For IT experts, IT is to ensure that their company's productivity is improved by leveraging the powerful features of Oracle. One of the most effective methods isOracle Optimization. It has a lot of parameters and technologies to improve the performance of your Oracle database. The following describes how to optimize
Summary: This article begins with a detailed introduction to the concept of shared pool in Oracle and the memory structure included. It then provides an in-depth overview of Oracle's management mechanisms for shared pool. Finally, the paper introduces the practical method of buffer cache monitoring and tuning.
1. The concept of shared pool
Oracle databases, as
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.