Oracle调优(入门及提高篇)
在过去的十年中， Oracle 已经成为世界上最专业的数据库之一。对于 IT 专家来说，就是要确保利用Oracle 的强大特性来提高他们公司的生产力。最有效的方法之一是通过 Oracle 调优。它有大量的调整参数和技术来改进你的 Oracle 数据库的性能。 Oracle 调优是一
在过去的十年中， Oracle 已经成为世界上最专业的数据库之一。对于 IT 专家来说，就是要确保利用Oracle 的强大特性来提高他们公司的生产力。最有效的方法之一是通过 Oracle 调优。它有大量的调整参数和技术来改进你的 Oracle 数据库的性能。
Oracle 调优是一个复杂的主题。关于调优可以写整整一本书，不过，为了改善 Oracle 数据库的性能，有一些基本的概念是每个 Oracle DBA 都应该遵从的。
在这篇简介中，我们将简要地介绍以下的 Oracle 主题：
-- 外部调整：我们应该记住 Oracle 并不是单独运行的。因此我们将查看一下通过调整 Oracle 服务器以得到高的性能。
--Row re-sequencing 以减少磁盘 I/O ：我们应该懂得 Oracle 调优最重要的目标是减少 I/O 。
--Oracle SQL 调整。 Oracle SQL 调整是 Oracle 调整中最重要的领域之一，只要通过一些简单的 SQL 调优规则就可以大幅度地提升 SQL 语句的性能，这是一点都不奇怪的。
-- 调整 Oracle 排序：排序对于 Oracle 性能也是有很大影响的。
-- 调整 Oracle 的竞争：表和索引的参数设置对于 UPDATE 和 INSERT 的性能有很大的影响。
我们首先从调整 Oracle 外部的环境开始。如果内存和 CPU 的资源不足的话，任何的 Oracle 调整都是没有帮助的。
外部的性能问题
Oracle 并不是单独运行的。 Oracle 数据库的性能和外部的环境有很大的关系。这些外部的条件包括有：
. CPU--CPU 资源的不足令查询变慢。当查询超过了 Oracle 服务器的 CPU 性能时，你的数据库性能就受到 CPU 的限制。
.内存 -- 可用于 Oralce 的内存数量也会影响 SQL 的性能，特别是在数据缓冲和内存排序方面。
.网络 -- 大量的 Net8 通信令 SQL 的性能变慢。
许多新手都错误的认为应该首先调整 Oracle 数据库，而不是先确认外部资源是否足够。实际上，如果外部环境出现瓶颈，再多的 Oracle 调整都是没有帮助的。
在检查 Oracle 的外部环境时，有两个方面是需要注意的：
1 、当运行队列的数目超过服务器的 CPU 数量时，服务器的性能就会受到 CPU 的限制。补救的方法是为服务器增加额外的 CPU 或者关闭需要很多处理资源的组件，例如 Oracle Parallel Query 。
2 、内存分页。当内存分页时，内存容量已经不足，而内存页是与磁盘上的交换区进行交互的。补救的方法是增加更多的内存，减少 Oracle SGA 的大小，或者关闭 Oracle 的多线程服务器。
可以使用各种标准的服务器工具来得到服务器的统计数据，，例如 vmstat,glance,top 和 sar 。 DBA 的目标是确保数据库服务器拥有足够的 CPU 和内存资源来处理 Oracle 的请求。
以下让我们来看一下 Oracle 的 row-resequencing 是如何能够极大地减少磁盘 I/O 的。
Row-resequencing (行的重新排序)
就象我们上面提到的，有经验的 Oracle DBA 都知道 I/O 是响应时间的最大组成部分。其中磁盘 I/O 特别厉害，因为当 Oracle 由磁盘上的一个数据文件得到一个数据块时，读的进程就必须等待物理 I/O 操作完成。磁盘操作要比数据缓冲慢 10,000 倍。因此，如果可以令 I/O 最小化，或者减少由于磁盘上的文件竞争而带来的瓶颈，就可以大大地改善 Oracle 数据库的性能。
如果系统响应很慢，通过减少磁盘 I/O 就可以有一个很快的改善。如果在一个事务中通过按一定的范围搜索 primary-key 索引来访问表，那么重新以 CTAS 的方法组织表将是你减少 I/O 的首要策略。通过在物理上将行排序为和 primary-key 索引一样的顺序，就可以加快获得数据的速度。
就象磁盘的负载平衡一样，行的重新排序也是很简单的，而且也很快。通过与其它的 DBA 管理技巧一起使用，就可以在高 I/O 的系统中大大地减少响应的时间。
在高容量的在线事务处理环境中( online transaction processing ， OLTP )，数据是由一个 primary 索引得到的，重新排序表格的行就可以令连续块的顺序和它们的 primary 索引一样，这样就可以在索引驱动的表格查询中，减少物理 I/O 并且改善响应时间。这个技巧仅在应用选择多行的时候有用，或者在使用索引范围搜索和应用发出多个查询来得到连续的 key 时有效。对于随机的唯一 primary-key (主键)的访问将不会由行重新排序中得到好处。
让我们看一下它是如何工作的。考虑以下的一个 SQL 的查询，它使用一个索引来得到 100 行：
selectsalaryfromemployeewherelast_name like 'B%';
这个查询将会使用 last_name_index ，搜索其中的每一行来得到目标行。这个查询将会至少使用 100 次物理磁盘的读取，因为 employee 的行存放在不同的数据块中。
不过，如果表中的行已经重新排序为和 last_name_index 的一样，同样的查询又会怎样处理呢?我们可以看到这个查询只需要三次的磁盘 I/O 就读完全部 100 个员工的资料(一次用作索引的读取，两次用作数据块的读取)，减少了 97 次的块读取。
重新排序带来的性能改善的程度在于在你开始的时候行的乱序性如何，以及你需要由序列中访问多少行。至于一个表中的行与索引的排序键的匹配程度，可以查看数据字典中的 dba_indexes 和 dba_tables 视图得到。
在 dba_indexes 的视图中，查看 clustering_factor 列。如果 clustering_factor 的值和表中的块数目大致一样，那么你的表和索引的顺序是一样的。不过，如果 clustering_factor 的值接近表中的行数目，那就表明表格中的行和索引的顺序是不一样的。
行重新排序的作用是不可以小看的。在需要进行大范围的索引搜索的大表中，行重新排序可以令查询的性能提高三倍。
一旦你已经决定重新排序表中的行，你可以使用以下的工具之一来重新组织表格。
. 使用 Oracle 的 Create Table As Select (CTAS) 语法来拷贝表格
. Oracle9i 自带的表格重新组织工具
以下，我们来看以下 SQL 语句的调优。
SQL 调优
Oracle 的 SQL 调优是一个复杂的主题，甚至是需要整本书来介绍 Oracle SQL 调优的细微差别。不过有一些基本的规则是每个 Oracle DBA 都需要跟从的，这些规则可以改善他们系统的性能。 SQL 调优的目标是简单的：
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
AI Hentai Generator
Generate AI Hentai for free.
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
Generate PPT with one click! Kimi: Let the 'PPT migrant workers' become popular first
Aug 01, 2024 pm 03:28 PM
Kimi: In just one sentence, in just ten seconds, a PPT will be ready. PPT is so annoying! To hold a meeting, you need to have a PPT; to write a weekly report, you need to have a PPT; to make an investment, you need to show a PPT; even when you accuse someone of cheating, you have to send a PPT. College is more like studying a PPT major. You watch PPT in class and do PPT after class. Perhaps, when Dennis Austin invented PPT 37 years ago, he did not expect that one day PPT would become so widespread. Talking about our hard experience of making PPT brings tears to our eyes. "It took three months to make a PPT of more than 20 pages, and I revised it dozens of times. I felt like vomiting when I saw the PPT." "At my peak, I did five PPTs a day, and even my breathing was PPT." If you have an impromptu meeting, you should do it
How long will Oracle database logs be kept?
May 10, 2024 am 03:27 AM
The retention period of Oracle database logs depends on the log type and configuration, including: Redo logs: determined by the maximum size configured with the "LOG_ARCHIVE_DEST" parameter. Archived redo logs: Determined by the maximum size configured by the "DB_RECOVERY_FILE_DEST_SIZE" parameter. Online redo logs: not archived, lost when the database is restarted, and the retention period is consistent with the instance running time. Audit log: Configured by the "AUDIT_TRAIL" parameter, retained for 30 days by default.
The order of the oracle database startup steps is
May 10, 2024 am 01:48 AM
The Oracle database startup sequence is: 1. Check the preconditions; 2. Start the listener; 3. Start the database instance; 4. Wait for the database to open; 5. Connect to the database; 6. Verify the database status; 7. Enable the service (if necessary ); 8. Test the connection.
All CVPR 2024 awards announced! Nearly 10,000 people attended the conference offline, and a Chinese researcher from Google won the best paper award
Jun 20, 2024 pm 05:43 PM
In the early morning of June 20th, Beijing time, CVPR2024, the top international computer vision conference held in Seattle, officially announced the best paper and other awards. This year, a total of 10 papers won awards, including 2 best papers and 2 best student papers. In addition, there were 2 best paper nominations and 4 best student paper nominations. The top conference in the field of computer vision (CV) is CVPR, which attracts a large number of research institutions and universities every year. According to statistics, a total of 11,532 papers were submitted this year, and 2,719 were accepted, with an acceptance rate of 23.6%. According to Georgia Institute of Technology’s statistical analysis of CVPR2024 data, from the perspective of research topics, the largest number of papers is image and video synthesis and generation (Imageandvideosyn
From bare metal to a large model with 70 billion parameters, here is a tutorial and ready-to-use scripts
Jul 24, 2024 pm 08:13 PM
We know that LLM is trained on large-scale computer clusters using massive data. This site has introduced many methods and technologies used to assist and improve the LLM training process. Today, what we want to share is an article that goes deep into the underlying technology and introduces how to turn a bunch of "bare metals" without even an operating system into a computer cluster for training LLM. This article comes from Imbue, an AI startup that strives to achieve general intelligence by understanding how machines think. Of course, turning a bunch of "bare metal" without an operating system into a computer cluster for training LLM is not an easy process, full of exploration and trial and error, but Imbue finally successfully trained an LLM with 70 billion parameters. and in the process accumulate
Counting down the 12 pain points of RAG, NVIDIA senior architect teaches solutions
Jul 11, 2024 pm 01:53 PM
Retrieval-augmented generation (RAG) is a technique that uses retrieval to boost language models. Specifically, before a language model generates an answer, it retrieves relevant information from an extensive document database and then uses this information to guide the generation process. This technology can greatly improve the accuracy and relevance of content, effectively alleviate the problem of hallucinations, increase the speed of knowledge update, and enhance the traceability of content generation. RAG is undoubtedly one of the most exciting areas of artificial intelligence research. For more details about RAG, please refer to the column article on this site "What are the new developments in RAG, which specializes in making up for the shortcomings of large models?" This review explains it clearly." But RAG is not perfect, and users often encounter some "pain points" when using it. Recently, NVIDIA’s advanced generative AI solution
AI in use | AI created a life vlog of a girl living alone, which received tens of thousands of likes in 3 days
Aug 07, 2024 pm 10:53 PM
Editor of the Machine Power Report: Yang Wen The wave of artificial intelligence represented by large models and AIGC has been quietly changing the way we live and work, but most people still don’t know how to use it. Therefore, we have launched the "AI in Use" column to introduce in detail how to use AI through intuitive, interesting and concise artificial intelligence use cases and stimulate everyone's thinking. We also welcome readers to submit innovative, hands-on use cases. Video link: https://mp.weixin.qq.com/s/2hX_i7li3RqdE4u016yGhQ Recently, the life vlog of a girl living alone became popular on Xiaohongshu. An illustration-style animation, coupled with a few healing words, can be easily picked up in just a few days.
How much memory does oracle require?
May 10, 2024 am 04:12 AM
The amount of memory required by Oracle depends on database size, activity level, and required performance level: for storing data buffers, index buffers, executing SQL statements, and managing the data dictionary cache. The exact amount is affected by database size, activity level, and required performance level. Best practices include setting the appropriate SGA size, sizing SGA components, using AMM, and monitoring memory usage.
