`
- 浏览:
1313221 次
-
Tuning the Shared pool(1)
Tuning the Shared pool
培训目标:
* Determine the size of an object and pin it in the shared pool.钉在
* Tune the shared pool reserved space
* Describe the user global area and session memory considerations.
* Measure the library cache hit ratio命中率
* List other tuning issues related to the shared pool.
* Measure the dictionary cache hit ratio.
* Set the large pool.
Shared Pool Contents
Database buffer cache
Redo log buffer
Shared pool
Library cache
Data dictionary cache
Large pool
Large pool
Shared Pool
Define by SHARED_POOL_SIZE
10g后有SGA_TARGET,11g后有MEMORY_TARGET,都减轻了管理员负担。
Library cache contains statement text,parsed code,and execution plan.
Data dictionary cache contains definitions for tables,columns,and privileges from the data dictionary tables.
UGA(用在共享模式,dedicate模式不用,就算是共享模式,也把UGA放在large pool中,这也是一种调优的途径,以避免占用shared pool的位置)。
The Library Cache
Used to store SQL statements and PL/SQL blocks that are to be shared by users.
Managed by a least recently used(LRU) algorithm
Used to prevent statements reparsing.
预防句子再解析。
Reports error ORA-04031 if the shared pool is out of free memory.
SQL Sharing Criteria
Oracle automatically determines whether a SQL statement or PL/SQL block being issued is identical to another statement currently in the shared pool.
Oracle performs the following steps for the comparison:
1.The text of the statement issued is compared to existing statements in the shared pool.
2.The text of the statement is hashed.If there is no matching hash value,then the SQL statement does not currently exist in the shared pool,and a hard parse is performed.
输入的语句会被hash处理,出来一个较短的特征值,这样就比较容易比对。
3.If there is a matching hash value for an existing SQL statement in the shared pool,then Oracle compares the text of the matched statement to the text of the statement hashed to see if they are identical.The text of the SQL statements or PL/SQL blocks must
be identical,character for character,including spaces,case,and comments.
如果hash值是一样的,则把文本逐个字母比对,甚至空间与大小写也要关注(真要关注空格与大小写吗?)
下面这两个是不能共享相同的SQL执行代码的。
SELECT * FROM employees;
SELECT * FROM Employees;
SELECT * FROM employees;
desc v$sysstat 关于系统的一些统计指标
select * from v$sysstat;看到一行是parse count (hard) 后面列出的是硬解析的次数。
此时用另一个会话以HR/HR连接数据库
drop table t
create table t(id number);
insert into t values(&i);
输入1,2,3。
然后现在看一下硬解析次数,再select * from t where id=1;再看一下,发现硬解析次数加了1。再试验一下空间数目,大小写不同,发现硬解析都会增加。看来大小写与空间数目都挺重要的。
show parameter cursor
alter system set cursor_sharing='exact';
alter system set cursor_sharing='similar';
据说改成similar或force后,两个sql语句只是字面值不一样的话,也可以用相同的shared sql area.
例如select * from t where id=21与select * from t where id=22;
4.在SQL语句中涉及的对象的统计数据,要与存在的语句的对象的统计数据相同,才能重用执行代码。例如,如果两个不同的用户登录了,都输入select * from employees,也不能认为是相同的,因为他们各自的schema下都有employees表。
5.绑定变量,在SQL语句中,一定要match in name,datatype,and length;
下面两条语句因为绑定变量名字不一样,也认为是两条不同的SQL语句。
select * from employees where department_id=:department_id;
select * from employees where department_id=:dept_id;
Use Bind Variables.
分享到:
Global site tag (gtag.js) - Google Analytics
相关推荐
quadtree useful for UDF cost modeling: the abilities to (1) adapt to changing UDF execution patterns and (2) use limited memory. To this end, we have developed a novel technique we call the memory-...
oracle tuning oracle tuning
Oracle Enterprise Manager Database Tuning with the Oracle Tuning Pack Release 9.0.1
2015 Oracle 技术嘉年华(OTN)分会场6杜伟业-SQL Tuning The past, present and future
Learn about thread pool tuning, EJB tuning, and JMS tuning, which are crucial parts of enterprise applications. The persistence layer and the JBoss Clustering service are two of the most crucial ...
Due to the increasing complexity in application workloads and query engines, database administrators are turning to automated tuning tools that systematically explore the space of physical design ...
Expert Oracle RAC Performance Diagnostics and Tuning provides comprehensive coverage of the features, technology and principles for testing and tuning RAC databases. The book takes a deep look at ...
Oracle Database 12c Performance Tuning Recipes is a ready reference for database administrators in need of immediate help with performance issues relating to Oracle Database. The book takes an example...
An introduction to the WebSphere Application Server Performance Tuning Toolkit (PTT)
Tuning the embedded system software method
Reading this book and using SQL helps you learn to tune even the most complex SQL, and you'll learn to do it quickly, without the huge learning curve usually associated with tuning as a whole. ...
This wholly revised third edition extends the presentation of PI and PID controller tuning rules, for single variable processes with time delays, to include additional rules compiled since the second...
developer, integrator, or consultant, Performance Tuning for Linux Serverswill help you maximize the performance and value of every Linux system and application you run. © Copyright Pearson ...
Grant Fritchey's book SQL Server 2012 Query Performance Tuning is the answer to your SQL Server query performance problems. The book is revised to cover the very latest in performance optimization ...
SQL Tuning使用指南 QCO的SQL Tuning模块是一种功能强大的SQL语句分析、测试、优化工具,它可以贯穿Oracle数据库应用的整个生命周期,在开发阶段、试运行阶段和生产阶段帮助获得最佳SQL语句,以提高应用系统的...
Understand the basic aspects of the Java virtual machine and application server performance tuning Design guidelines for better enterprise application performance Discover different Java optimization ...
You will learn Query Store, adaptive execution plans, and automated tuning on the Microsoft Azure SQL Database platform. Anyone responsible for writing or creating T-SQL queries will find valuable ...
Linux Performance and Tuning Guidelines 4285 PDF Linux® is an open source operating system developed by people from all over the world. The source code is freely available and can be used under the ...
sql tuning oracle sql tuning
SQL Tuning author Dan Tow outlines a timesaving method he's developed for finding the optimum execution plan--rapidly and systematically--regardless of the complexity of the SQL or the database ...