Solved

Database performance is very slow re-arranging the partitions

Posted on 2004-04-02
8
1,611 Views
Last Modified: 2007-12-19
Hi everybody,

I am using Oracle9i Enterprise Edition Release 9.2.0.1.0 on linux 8.

I have added some partition and splited some big partions into smaller ones.

To re-size the datafile:-

1). Created a new tablespace
2). Moved the partions and tables (non partitioned) to the new tablespace
3). Rebuild all the invalid indexes
4). Analyzed all the objects using dbms_stats package
5). Dropped the old tablespace

After this my system is running very slow.

If running the following query:-

SELECT SQL_TEXT, ROWS_PROCESSED, FIRST_LOAD_TIME
FROM   V$SQLAREA
WHERE  OPEN_VERSIONS > 0
ORDER BY 3 DESC, 1

I am getting the following error:-

ORA-04031: unable to allocate 1760 bytes of shared memory ("large pool","unknown object","cursor work he","KGL Iterator information")

alert_orcl.log is showing:-

ARC0: Completed archiving  log 1 thread 1 sequence 10053
Sat Apr  3 12:06:54 2004
Errors in file /home/oracle/OraHome1/admin/orcl/bdump/orcl_s001_2910.trc:
ORA-07445: exception encountered: core dump [kghfrh()+24] [SIGSEGV] [Address not
 mapped to object] [0x1D] [] []
ORA-04030: out of process memory when trying to allocate 200 bytes (callheap,PLS
 cca hp desc)
Sat Apr  3 12:08:00 2004
found dead shared server 'S001', pid = (14, 3)
Sat Apr  3 12:17:07 2004
Errors in file /home/oracle/OraHome1/admin/orcl/bdump/orcl_s002_2912.trc:
ORA-07445: exception encountered: core dump [kghfrh()+24] [SIGSEGV] [Address not
 mapped to object] [0x1D] [] []
ORA-04030: out of process memory when trying to allocate 200 bytes (callheap,PLS
 cca hp desc)

My Init.ora setting are the following:-

*.aq_tm_processes=1
*.background_dump_dest='/home/oracle/OraHome1/admin/orcl/bdump'
*.compatible='9.2.0.0.0'
*.control_files='/u1/oradata/orcl/control01.ctl','/u2/oradata/orcl/control02.ctl','/u3/oradata/orcl/control03.ctl'
*.core_dump_dest='/home/oracle/OraHome1/admin/orcl/cdump'
*.db_block_size=8192
*.db_cache_size= 1024M #1073741824
*.db_domain='mis.com'#Changed on 01/09/2003 (R.S.Bisht)
*.db_files=100
*.db_file_multiblock_read_count=16
*.db_name='orcl'
*.dispatchers='(PROTOCOL=TCP)'
*.dml_locks=1500
*.fast_start_mttr_target=300
*.global_names=TRUE #Changed on 28/08/2003 (R.S.Bisht)
*.hash_join_enabled=TRUE
*.instance_name='orcl'
*.java_pool_size=60M
*.job_queue_processes=10
*.large_pool_size=30M
*.log_archive_dest_1='LOCATION=/u6/oradata/orcl/archive/ MANDATORY REOPEN=300'
*.log_archive_dest_2='LOCATION=/u7/oradata/orcl/archive/ OPTIONAL REOPEN'
*.log_archive_format='%t_%s.arc'
*.log_archive_start=TRUE
*.log_buffer=5242880
*.open_cursors=2000 #Changed on 28/08/2003 (R.S.Bisht)
*.pga_aggregate_target=30M
*.processes=500 #Changed on 01/09/2003 (R.S.Bisht)
*.query_rewrite_enabled='FALSE'
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=500
*.sga_max_size=1400M
# *.shared_pool_size=104857600
*.shared_pool_size=100M # Changed on 02/04/2004 (R.S.Bisht)
*.sort_area_size=5242880
*.star_transformation_enabled='FALSE'
*.timed_statistics=TRUE
*.undo_management='AUTO'
*.undo_retention=28800
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/home/oracle/OraHome1/admin/orcl/udump'
*.utl_file_dir='/home/rsbisht/work'

I have dual processor server with 2 GB of RAM and 2GB swap space. I am getting the following information from  /proc/meminfo

        total:    used:    free:  shared: buffers:  cached:
Mem:  2113671168 2102468608 11202560        0   737280 136564736
Swap: 2146787328 1609981952 536805376
MemTotal:      2064132 kB
MemFree:         10940 kB
MemShared:           0 kB
Buffers:           720 kB
Cached:         109032 kB
SwapCached:      24332 kB
Active:        1922864 kB
Inact_dirty:     15864 kB
Inact_clean:     45956 kB
Inact_target:   396936 kB
HighTotal:     1179072 kB
HighFree:         1024 kB
LowTotal:       885060 kB
LowFree:          9916 kB
SwapTotal:     2096472 kB
SwapFree:       524224 kB
Committed_AS:  1704432 kB

A am not been able to find out the problem.

What are the reasons? Whether the init.ora settings are correct?

With Regards

[ R. S. Bisht ]
0
Comment
Question by:rsbisht
8 Comments
 
LVL 8

Expert Comment

by:baonguyen1
Comment Utility
Hi,

What is your SHMMAX ( the maximum size(in bytes) of a single shared memory segment) values of the kernel ,  ?

Look like you need to increase the value to the bigger value.

Hope this helps
0
 
LVL 8

Expert Comment

by:baonguyen1
Comment Utility
And this is other diagnosing error ORA-04031:

DIAGNOSING AND RESOLVING ORA-04031 ERROR

For most applications, shared pool size is critical to Oracle performance. The shared pool holds both the data dictionary cache and the fully parsed or compiled representations of PL/SQL blocks and SQL statements.

When any attempt to allocate a large piece of contiguous memory in the shared pool fails Oracle first flushes all objects that are not currently in use from the pool and the resulting free memory chunks are merged. If there is still not a single chunk large enough to satisfy the request ORA-04031 is returned.

The message that you will get when this error appears is the following:
 

Error: ORA 4031
Text: unable to allocate %s bytes of shared memory (%s,%s,%s)
---------------------------------------------------------------------------- ---
Cause: More shared memory is needed than was allocated in the shared pool.
Action: 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 parameter "shared_pool_size".

1. Instance parameters related with the Shared Pool
 

Before continuing, understanding the following instance parameters will be essential:
 
SHARED_POOL_SIZE - This parameter specifies the size of the shared pool in bytes and can accept
a numerical values or a number followed by the suffix "K" or "M" where "K" means "multiply by 1000"
and "M" means "multiply by 1000000"
 
SHARED_POOL_RESERVED_SIZE - It specifies the shared pool space which is reserved for large contiguous requests for shared pool memory. This parameter along with the SHARED_POOL_RESERVED_MIN_ALLOC parameter, can be used to avoid performance degradation
in the shared pool from situations where shared pool fragmentation forces Oracle to search for and free
chunks of unused pool to satisfy the current request.
Ideally, this parameter should be large enough to satisfy any request scanning for memory on the reserved list without flushing objects from the shared pool. Since the operating system memory may constraint the size of the shared pool, in general, you should set this parameter to 10% of the SHARED_POOL_SIZE parameter.
 

SHARED_POOL_RESERVED_MIN_ALLOC - The value of this parameter controls allocation of reserved memory. Memory allocation larger than this value can allocate space from the reserved list if a chunk of memory of sufficient size is not found on the shared pool free lists. The default value is adequate for most systems. If you increase the value, then the Oracle server will allow fewer allocations from the reserved list and will request more memory from the shared pool list. This parameter is hidden in Oracle 8i and can be found running the following SQL statement:

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;

2. Diagnosing error ORA-04031:
 
The ORA-04031 error is usually due to fragmentation in the library cache or shared pool reserved space.  Before of increasing the shared pool size consider to tune the application to use shared sql and tune SHARED_POOL_SIZE, SHARED_POOL_RESERVED_SIZE, and SHARED_POOL_RESERVED_MIN_ALLOC.
First determine if the ORA-04031 was a result of fragmentation in the library
cache or in the shared pool reserved space by issuing the following query:

SELECT free_space, avg_free_size, used_space,
avg_used_size, request_failures, last_failure_size
FROM    v$shared_pool_reserved;
The ORA-04031 is a result of lack of contiguous space in the shared pool
reserved space if:
REQUEST_FAILURES is > 0 and LAST_FAILURE_SIZE is >
SHARED_POOL_RESERVED_MIN_ALLOC.

To resolve this  consider increasing SHARED_POOL_RESERVED_MIN_ALLOC to lower
the number of objects being cached into the shared pool reserved space and
increase SHARED_POOL_RESERVED_SIZE and SHARED_POOL_SIZE to increase the
available memory in the shared pool reserved space.

The ORA-04031 is a result of lack of contiguous space in the library cache if:

REQUEST_FAILURES is > 0 and LAST_FAILURE_SIZE is <
SHARED_POOL_RESERVED_MIN_ALLOC

or

REQUEST_FAILURES is 0 and LAST_FAILURE_SIZE is < SHARED_POOL_RESERVED_MIN_ALLOC

The first step would be to consider lowering SHARED_POOL_RESERVED_MIN_ALLOC to
put more objects into the shared pool reserved space and increase
SHARED_POOL_SIZE.
 

3. Resolving error ORA-04031:
Oracle BUGs
 
 

The first diagnostic step to resolve this error is to apply the latest patchset available in your platform. Most of the ORA-4031 errors related to BUGs can be avoided by applying these patchsets. The following table summarize the most common BUGs related with this error, possible workaround and the patchset that fixes the problem.
 
  BUG Description Workaround Fixed
<Bug:1397603> ORA-4031 / SGA memory leak of PERMANENT memory occurs for buffer handles. _db_handles_cached = 0 8172, 901
<Bug:1640583> ORA-4031 due to leak / cache buffer chain contention from AND-EQUAL access  Not available 8171, 901
 Bug:1318267  
   Not Public INSERT AS SELECT statements may not be shared when they should be if  TIMED_STATISTICS. It can lead to ORA-4031 _SQLEXEC_PROGRESSION_COST=0  8171,  8200
  Bug:1193003
     Not Public Cursors may not be shared in 8.1 when they should be Not available 8162, 8170, 901
<Bug:2104071> ORA-4031/excessive "miscellaneous"
shared pool usage possible.
(many PINS) None-> This is known to affect the XML parser.  8174, 9013, 9201


There are some BUGs reported in the shared pool structure that can generate the ORA-04031 error, but it usually addresses databases with huge amount of sharable version of SQL/PLSQL statements. Once the latest patchset is applied we strongly recommend to tune both the database and the application before of considering this error  a product defect.

 
For a complete reference of known BUGs, please refer to:
<Note:62143.1>: Main issues affecting the Shared Pool on Oracle 7 , Oracle8 and Oracle8i.
ORA-4031 when compiling Java code:
If you run out of memory while compiling a java code (within loadjava or deployejb), you should see an error:
A SQL exception occurred while compiling: : ORA-04031: unable to allocate bytes of shared memory ("shared pool","unknown object","joxlod: init h", "JOX: ioc_allocate_pal")

The solution is to shut down the database and set JAVA_POOL_SIZE to a larger value. The mention of "shared pool" in the error message is a misleading reference to running out of memory in the "Shared Global Area". It does not mean you should increase your SHARED_POOL_SIZE. Instead, you must increase your JAVA_POOL_SIZE, restart your server, and try again.


Shared Pool Fragmentation:
Every time a SQL or PL/SQL statement needs to be executed the parse representation is loaded in the shared pool requiring a specific amount of free contiguous space. The first resource where the database scans is the free memory available in the shared pool. Once the free memory is exhausted, the database looks for reusing an already allocated piece not in use. If a chunk with the exact size is not available, the scan continues looking for space based on the following criteria:
- The chuck size is larger than the required size
- The space is contiguous
- The chuck is available (not in use)
Then that chunk is split and the remaining free space is added to the appropriate free space list. When the database is operating in this way for a certain period of time the shared pool structure will be fragmented.
When the shared pool is suffering fragmentation, the allocation of a piece of free space takes much more time generating slow performance in the database (the "chunk allocation" is protected by a single latch called "shared pool latch" which is held during the whole operation) or ORA-04031 errors (when the database cannot find a contiguous piece of free memory).

See <Note:61623.1>: for a detailed discussion on shared pool fragmentation.

If the SHARED_POOL_SIZE is large enough, most ORA-04031 errors are a result of  dynamic sql fragmenting the shared pool.  This can be caused by:

        o Not sharing SQL
        o Making unnecessary parse calls (soft)
        o Not using bind variables

To reduce fragmentation you will need to address one or more of the causes described before. You can take advantage of different options to prevent this problem including, but not limited: application tuning, database tuning or instance parameters adjustment.

Please refer to <Note:62143.1>, which describes all this options in detail. This note contains as well further detail  on how the shared pool works.

The following views will help you to identify non-sharable versions of SQL/PLSQL text in the shared pool:

V$SQLAREA View
This view keeps information of every SQL statement and PL/SQL block executed in the database. The following SQL can show you statements with literal values or candidates to include bind variables:
SELECT substr(sql_text,1,40) "SQL",
count(*) ,
sum(executions) "TotExecs"
FROM v$sqlarea
WHERE executions < 5
GROUP BY substr(sql_text,1,40)
HAVING count(*) > 30
ORDER BY 2;

Note: The number "30" in the having section of the statement can be adjusted as needed to get more detailed information.
 

X$KSMLRU View
There is a fixed table called x$ksmlru that tracks allocations in the shared pool that cause other objects in the shared pool to be aged out. This fixed table can be used to identify what is causing the large allocation.
If many objects are being periodically flushed from the shared pool then this will cause response time problems and will likely cause library cache latch contention problems when the objects are reloaded into the shared pool.

One unusual thing about the x$ksmlru fixed table is that the contents of the fixed table are erased whenever someone selects from the fixed table. This is done since the fixed table stores only the largest allocations that have occurred. The values are reset after being selected so that subsequent large allocations can be noted even if they were not quite as large as others that occurred previously. Because of this resetting, the output of selecting from this table should be carefully kept since it cannot be retrieved back after the query is issued.

To monitor this fixed table just run the following:

SELECT * FROM X$KSMLRU WHERE ksmlrsiz > 0;

In Oracle8i this table cannot be selected from by anyone other than  the SYS user.

 
Small shared pool size
Ultimately, a small shared pool can be the cause of the ORA-04031 error, but be careful when increasing the shared pool size when fragmentation is the real problem. It certainly will delay the time when the error is noticed but it will increase the performance impact when finding a piece of free memory in a larger fragmented shared pool.
The following information will help you to adjust the size of the shared pool:
 

Library Cache Hit Ratio
The hit ratio helps to measure the usage of the shared pool based on how many times a SQL/PLSQL statement needed to be parsed instead of being reused. The following SQL statement help you to calculate the library cache hit ratio:
SELECT SUM(PINS) "EXECUTIONS",
            SUM(RELOADS) "CACHE MISSES WHILE EXECUTING"
            FROM V$LIBRARYCACHE;

If the ratio of misses to executions is more than 1%, then try to reduce the library cache misses by increasing the shared pool size.
 

Shared Pool Size Calculation
To calculate the size of the shared pool that best fits to your current workload, please refer to:
<Note:1012046.6>: HOW TO CALCULATE YOUR SHARED POOL SIZE.


4. ORA-04031 error and Large Pool
 

Large pool is an optional memory area that can be configured to provide large memory allocations for on of the following uses :
session memory for the multi-threaded server and the Oracle XA interface.
The memory ( Buffers ) for Oracle backup and restore operations and for I/O server processes.
Parallel Execution messaging buffers.

Large pool does not have an LRU list. It is different from reserved space in the shared pool, which uses the same LRU list as other memory
allocated from the shared pool. So chunks of memory are never aged out of the large pool,memory has to be explicitly allocated and freed by
each session. If there is no free memory left when a request is made then an ORA-4031 will be signalled similar to this :

ORA-04031: unable to allocate XXXX bytes of shared memory
("large pool","unknown object","session heap","frame")

Few thinge we can check when we get this error

1- Check V$SGASTAT and see how much memory is used and free
SQL> select pool,name,bytes from v$sgastat
         where pool = 'large pool';

POOL          NAME                        BYTES
-----------   -------------------------- ----------
large pool    free memory             6079520
large pool    session heap             64480

2- You can also take a heapdump level 32 to dump the large pool heap and check free chunks sizes.
 

for more details
 

Memory is allocated from the large pool in chunks of LARGE_POOL_MIN_ALLOC bytes to help avoid fragmentation. Any request to allocate
a chunk size less LARGE_POOL_MIN_ALLOC will be allocated with size of LARGE_POOL_MIN_ALLOC. So you may see more memory usage
when using Large Pool compared to Shared Pool.

Usually when you get ora-4031 on large pool this means that you have to increase the value of the LARGE_POOL_SIZE parameter.

If there is no large pool configured then shared pool will be used instead.


4. Advanced analysis to ORA-04031 error
 

If the shared pool has been tuned to resolve the ORA-04031 error using the
methods described above and the ORA-04031 error is still occurring, set the
following event in the initSID.ora file and restart the instance to dump a
trace file on the next occurrence of the error:
event = "4031 trace name errorstack level 3"

This trace file can be provided to Oracle Support for troubleshooting.

Hope this helps
0
 
LVL 8

Expert Comment

by:baonguyen1
Comment Utility
And this can also help

===================
This document is useful for DBA's and System Administrators tasked with  
resolving an ORA-4030 error.
 
BACKGROUND
==========
The ORA-4030 error is caused when an Oracle process(while doing work on behalf  
of a client program) requests additional memory from the operating system,  but
the operating system cannot accommodate the request.  This can occur because the
operating system does not have enough physical memory or swap available for the
 process, the OS is configured to limit the amount of memory available for UNIX
processes or an actual Oracle BUG has been encountered.
 
 
HOW TO RESOLVE THE ORA-4030
===========================
1. Reduce the PGA (Program Global Area) for the client process encountering the
error. This is valid if the database is not configured with MTS (Multi Threaded
Server) or Shared Servers.  
 
For Oracle 8i and below the major portion of ORA-4030 instances can be solved by
reducing the SORT_AREA_SIZE for the sessions. This will lessen the demand that
the process places on physical memory. SORT_AREA_SIZE can be modified by the
'alter session' command but persistent changes must be recorded in the  
init<sid>.ora. See the Oracle Reference guide available on your Online Generic  
Documentation CD-ROM for more information on the 'sort_area_size' parameter.
 
For Oracle 9i and up, the PGA can be sized using the PGA_AGGREGATE_TARGET  
parameter set in the init.ora or spfile.ora
 
2. Increase the amount of memory a UNIX process can request and use from the
operating system. This usually refers to stack and/or data size UNIX process  
resource limits.
 
This process varies slightly depending on the UNIX platform and the type of
UNIX shell you are using.  Generally speaking either the 'limit' or 'ulimit'
command will allow your System Administrator to increase memory and data size
limits. Oracle Support cannot recommend a specific value for these limits.
However, doubling existing values or setting them to 'unlimited' is usually
sufficient.
 
3. Increase the amount of swap available on your system.  You should have 2-3  
times the amount of physical memory available as swap space.

4. Finally, if you still experience the problem after addressing the above
issues, it's recommended that you move to the latest patchset release to
eliminate any possible Oracle product defects
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:rsbisht
Comment Utility
my shared memory segment is 2048000000
0
 
LVL 23

Accepted Solution

by:
seazodiac earned 300 total points
Comment Utility
@rsbisht:

you have no problem with your OS settings...


the problem arises from your SGA_MAX_SIZE and the total of the sub pools such as SHARED_POOL_SIZE, DB_CACHE_SIZE, LOG_BUFFER, LARGE_POOL_SIZE, JAVA_POOL_SIZE, what I mean by that is you might over-utilized your SGA.

put it another way, the total of sub pools might exceed the size of SGA...



before doing anything, can you verify if you indeed use the pfile to start the database OR SPFILE?

in the sqlplus window , run this query,


SQL>select count(*) from v$spparameter where isspecified ='TRUE';


can you tell me what's the result of the above query?


just want to make sure that we make changes in the right place...
0
 
LVL 3

Expert Comment

by:rajnadimpalli
Comment Utility
@ rsbisht :

Its seems after your re-org of partitions and tables ..most of the sqls in the shared_pool_area were getting invalid (SQL Invalidations) ,which causes each sql to go hard parse (which you may be noticing system slowness) and each hard parse will require a piece of shared pool memory ....which usually causes too much fragmented memory in your shared pool over a period of time.

Solutions :

1. To avoid ORA-04031    :   a) try to flash the shared pool    :     sql> alter system flash shared pool;
                                          b) if not fixed..only option is bounce the database to fix the problem

2. To address system slowness : This may be challenging exercise for you. Before re-org execise if you have the execution plans and system stats..then you can compare then with current system and work towards to bring the system to original status..other-wise....use normal methods of tunning

Btw: Your init.ora seems ok..I assume you have same setting before and after re-org ( Split large table,convert non-partiton to partitions..etc)..and your system are not having such problem before the re-org....

-Raj
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Oracle SQL Query Syntax 6 84
Oracle Finace 3 43
Calculating percentages per course - Oracle Query 3 27
sql for Oracle views 8 36
Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now