Home Database Mysql Tutorial ORA-04031 错误解决

ORA-04031 错误解决

Jun 07, 2016 pm 04:48 PM
o database

当我们在共享池中试图分配大片的连续内存失败的时候,Oracle首先清除池中当前没使用的所有对象,使空闲内存块合并。如果仍然没有

诊断并解决ORA-04031 错误
 
当我们在共享池中试图分配大片的连续内存失败的时候,Oracle首先清除池中当前没使用的所有对象,使空闲内存块合并。如果仍然没有足够大单个的大块内存满足请求,就会产生ORA-04031 错误。
 
当这个错误出现的时候你得到的错误解释信息类似如下:
 
04031, 00000, "unable to allocate %s bytes of shared memory (\"%s\",\"%s\",\"%s\",\"%s\")"
// *Cause: More shared memory is needed than was allocated in the shared
// pool.
// *Action: If the shared pool is out of memory, either use the
// dbms_shared_pool package to pin large packages,
// reduce your use of shared memory, or increase the amount of
// available shared memory by increasing the value of the
// INIT.ORA parameters "shared_pool_reserved_size" and
// "shared_pool_size".
// If the large pool is out of memory, increase the INIT.ORA
// parameter "large_pool_size".

 
1.共享池相关的实例参数
 
在继续之前,有必要理解下面的实例参数:
 
•SHARED_POOL_SIZE
 这个参数指定了共享池的大小,单位是字节。可以接受数字值或者数 字后面跟上后缀"K" 或 "M" 。"K"代表千字节, "M"代表兆字节。
•SHARED_POOL_RESERVED_SIZE
 指定了为共享池内存保留的用于大的连续请求的共享池空间。当共享池碎片强制使 Oracle 查找并释放大块未使用的池来满足当前的请求的时候,这个参 数和SHARED_POOL_RESERVED_MIN_ALLOC 参数一起可以用来避免性能下降。
•这个参数理想的值应该大到足以满足任何对保留列表中内存的请求扫描而无需从共享池中刷新对 象。既然操作系统内存可以限制共享池的大小,一般来说,你应该设定这个参数为 SHARED_POOL_SIZE 参数的 10% 大小。
•SHARED_POOL_RESERVED_MIN_ALLOC 这个参数的值控制保留内存的分配。如果一个足 够尺寸的大块内存在共享池空闲列表中没能找到,内存就从保留列表中分配一块比这个值大的空 间。默认的值对于大多数系统来说都足够了。如果你加大这个值,那么Oracle 服务器将允许从这 个保留列表中更少的分配并且将从共享池列表中请求更多的内存。这个参数在Oracle 8i 和更高的版本中是隐藏的。提交如下的语句查找这个参数值: SELECT  nam.ksppinm NAME, val.ksppstvl VALUE
    FROM x$ksppi nam, x$ksppsv val
  WHERE nam.indx = val.indx AND nam.ksppinm LIKE '%shared%'
ORDER BY 1;
 10g 注释:Oracle 10g 的一个新特性叫做 "自动内存管理" 允许DBA保留一个共享内存池来分shared pool,buffer cache, java pool 和large pool。一般来说,当数据库需要分配一个大的对象到共享池中并且不能找到连续的可用空间,将自动使用其他SGA结构的空闲空间来增加共享池的大小 。既然空间分配是Oracle自动管理的,ora-4031出错的可能性将大大降低。自动内存管理在初始化参数SGA_TARGET大于0的时候被激活。 当前设定可以通过查询v$sga_dynamic_components 视图获得。请参考10g管理手册以得到更多内容 。
 
2.诊断ORA-04031 错误
 
注:大多数的常见的 ORA-4031 的产生都和 SHARED POOL SIZE 有关,这篇文章中的诊断步骤大多都是关于共享池的。 对于其它方面如Large_pool或是Java_pool,内存分配算法都是相似的,一般来说都是因为结构不够大造成。
 
ORA-04031 可能是因为 SHARED POOL 不够大,,或是因为碎片问题导致数据库不能找到足够大的内存块 。
 ORA-04031 错误通常是因为库高速缓冲中或共享池保留空间中的碎片。 在加大共享池大小的时 候考虑调整应用,使用共享的SQL 并且调整如下的参数:
SHARED_POOL_SIZE,
SHARED_POOL_RESERVED_SIZE,
SHARED_POOL_RESERVED_MIN_ALLOC.
 首先判定是否ORA-04031 错误是由共享池保留空间中的库高速缓冲的碎片产生的。提交下的查询:
SELECT free_space, avg_free_size,used_space, avg_used_size, request_failures,
      last_failure_size
  FROM v$shared_pool_reserved;
 如果:
REQUEST_FAILURES > 0 并且 LAST_FAILURE_SIZE > SHARED_POOL_RESERVED_MIN_ALLOC
 
那么ORA-04031 错误就是因为共享池保留空间缺少连续空间所致。要解决这个问题,可以考虑加大SHARED_POOL_RESERVED_MIN_ALLOC 来降低缓冲进共 享池保留空间的对象数目,并增大 SHARED_POOL_RESERVED_SIZE 和 SHARED_POOL_SIZE 来加大共享池保留空间的可用 内存。
 如果:
REQUEST_FAILURES > 0 并且 LAST_FAILURE_SIZE  或者
REQUEST_FAILURES 等于0 并且 LAST_FAILURE_SIZE  
那么是因为在库高速缓冲缺少连续空间导致ORA-04031 错误。
 
第一步应该考虑降低SHARED_POOL_RESERVED_MIN_ALLOC 以放入更多的对象到共享池 保留空间中并且加大SHARED_POOL_SIZE。

更多详情见请继续阅读下一页的精彩内容:

linux

Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
2 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Repo: How To Revive Teammates
4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island Adventure: How To Get Giant Seeds
3 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

Detailed tutorial on establishing a database connection using MySQLi in PHP Detailed tutorial on establishing a database connection using MySQLi in PHP Jun 04, 2024 pm 01:42 PM

How to use MySQLi to establish a database connection in PHP: Include MySQLi extension (require_once) Create connection function (functionconnect_to_db) Call connection function ($conn=connect_to_db()) Execute query ($result=$conn->query()) Close connection ( $conn->close())

iOS 18 adds a new 'Recovered' album function to retrieve lost or damaged photos iOS 18 adds a new 'Recovered' album function to retrieve lost or damaged photos Jul 18, 2024 am 05:48 AM

Apple's latest releases of iOS18, iPadOS18 and macOS Sequoia systems have added an important feature to the Photos application, designed to help users easily recover photos and videos lost or damaged due to various reasons. The new feature introduces an album called "Recovered" in the Tools section of the Photos app that will automatically appear when a user has pictures or videos on their device that are not part of their photo library. The emergence of the "Recovered" album provides a solution for photos and videos lost due to database corruption, the camera application not saving to the photo library correctly, or a third-party application managing the photo library. Users only need a few simple steps

How to handle database connection errors in PHP How to handle database connection errors in PHP Jun 05, 2024 pm 02:16 PM

To handle database connection errors in PHP, you can use the following steps: Use mysqli_connect_errno() to obtain the error code. Use mysqli_connect_error() to get the error message. By capturing and logging these error messages, database connection issues can be easily identified and resolved, ensuring the smooth running of your application.

How does Go WebSocket integrate with databases? How does Go WebSocket integrate with databases? Jun 05, 2024 pm 03:18 PM

How to integrate GoWebSocket with a database: Set up a database connection: Use the database/sql package to connect to the database. Store WebSocket messages to the database: Use the INSERT statement to insert the message into the database. Retrieve WebSocket messages from the database: Use the SELECT statement to retrieve messages from the database.

How to use database callback functions in Golang? How to use database callback functions in Golang? Jun 03, 2024 pm 02:20 PM

Using the database callback function in Golang can achieve: executing custom code after the specified database operation is completed. Add custom behavior through separate functions without writing additional code. Callback functions are available for insert, update, delete, and query operations. You must use the sql.Exec, sql.QueryRow, or sql.Query function to use the callback function.

How to handle database connections and operations using C++? How to handle database connections and operations using C++? Jun 01, 2024 pm 07:24 PM

Use the DataAccessObjects (DAO) library in C++ to connect and operate the database, including establishing database connections, executing SQL queries, inserting new records and updating existing records. The specific steps are: 1. Include necessary library statements; 2. Open the database file; 3. Create a Recordset object to execute SQL queries or manipulate data; 4. Traverse the results or update records according to specific needs.

Top 10 global digital currency trading apps recommended (2025 currency trading software ranking) Top 10 global digital currency trading apps recommended (2025 currency trading software ranking) Mar 12, 2025 pm 05:48 PM

This article recommends the top ten digital currency trading apps in the world, including Binance, OKX, Huobi Global, Coinbase, Kraken, Gate.io, KuCoin, Bitfinex, Gemini and Bitstamp. These platforms have their own characteristics in terms of transaction pair quantity, transaction speed, security, compliance, user experience, etc. For example, Binance is known for its high transaction speed and extensive services, while Coinbase is more suitable for novices. Choosing a platform that suits you requires comprehensive consideration of your own needs and risk tolerance. Learn about the world's mainstream digital currency trading platforms to help you conduct digital asset trading safely and efficiently.

How to install and register the btc trading app? How to install and register the btc trading app? Feb 21, 2025 pm 07:09 PM

This article will provide a detailed introduction to how to install and register a Bitcoin trading application. The Bitcoin trading app allows users to manage and trade cryptocurrencies such as Bitcoin. The article guides users through the installation and registration process step by step, including downloading applications, creating accounts, performing identity verification, and first deposit. The goal of the article is to provide beginners with clear and easy-to-understand guidelines to help them easily enter the world of Bitcoin trading.

See all articles