ORA-04031 - unable to allocate %s bytes of shared memory (\”%s\”,\”%s\”,\”%s\”,\”%s\”).

Cause of Error :-

More shared memory is needed than actually allocated to Shared_Pool.

Reason :- 

Issue may be due to the complex SELECT queries that had run with huge list of input parameters to IN operator
and with OR concatenation.

ORA-04031: unable to allocate 664 bytes of shared memory ("shared pool","SELECT  "MANDT" , "OBJNR" , ...","sql area","ctxdef : kkslod")

First Perception Strikes -  Shared memory is running shortage in space and we need to increase amount of shared memory in init.ora file.

   ************************

Workaround :-

1. To check if ASMM is configured in the database : So, SGA is dynamically managing memory allocations to memory parameters.

Since, SGA_TARGET is configured : shared_pool_size is Ignored in parameter file.

2. Need to check if SGA has sufficient memory allocated to it if needed by memory parameters. the DBA should look at the current SGA dynamic components and SGA resize operations first before trying to give more memory to SGA/shared pool.

------------   show parameter SGA;  ------   show parameter SGA_TARGET;  ---------------

3. Oracle Support is also providing Automatic Diagnostic tools . We just need to attach and provide the associated trace file generated by ORA-04031.the DBA should look at the current SGA dynamic components and SGA resize operations first before trying to give more memory to SGA/shared pool.

Check how fast the resize ops is taking place -

SQL> SELECT component,
oper_type,
oper_mode,
parameter,
initial_size,
target_size,
final_size,
status,
TO_CHAR (start_time, ‘dd-mon-yyyy:hh24:mi:ss’) started,
TO_CHAR (end_time, ‘dd-mon-yyyy:hh24:mi:ss’) ended
FROM v$sga_resize_ops
ORDER BY started;

Check Shared Pool Advice how much extra memory is needed -

SQL> SELECT shared_pool_size_for_estimate, estd_lc_size, estd_lc_time_saved
FROM v$shared_pool_advice;

Check Shared Pool Reserved Size -

SQL> SELECT free_space,
avg_free_size,
free_count,
max_free_size,
request_misses,
request_failures
FROM v$shared_pool_reserved;

4. However, Fragmentation of shared pool memory is also another possible cause of Error .

It is caused by not using binding variable in queries. So even it appears you have free space in shared pool, you can't allocation enough space for a query. 

If the problem is more likely to be shared pool fragment problem. No matter how much memory you give to shared pool, eventually the problem will come back again if you don't deal with the source of the problem.

Business Intelligence Software

Business Intelligence Software

A new software product in the market is gaining success by it's productiveness & abilities.The product is none other than Business Intelligence Software.The name gives the functioning idea & concept of this software—The tools & technologies helps to make sense your data.The utilization of Business Intelligence Software tools actually provides you up-to-the-minute information on where you've been, where you are, and where you're headed in a easier & better way.The working principle is based on that the tools turn a mass of jumbled data into key summaries making the work quite easier for anyone and detailed Business Intelligence Solutions help to guide you through critical business decisions. The work of professionals becomes easy & faster by this product only while comparing with others products in market .Its somewhat a difficult task to generate documents in different layouts and forms .The flexibility of Business Intelligence Software lies in the fact it works with Microsoft Word,Excel & PowerPoint also.The professionals working in software companies will get a lot of benefits from Business Intelligence Software. The tools work with Ms Word to design documents with additional functionalities such that it works more conviently as programming languages such as Java,.Net also.You can make more strategic business decisions when you have more sensed business data So, the fact Business Intelligence Software is making the work easier & expressive with sense & meanings.The user is free to input the data either in the form of spreadsheets in Ms Excel can manage the data as per the choices & get the output effectively in any of the extensions as PDF, Docx, HTML,XLSX, PPTX & many more that none can imagine. Business Intelligence Software has intelligency in that it perform the work of many softwares in just one. No doubt we can say it a One Man Army in other words.We know that XML documents are too difficult to deal with as it contains parse trees which have their own fixed grammer but Business Intelligence Software is compatible to work easily with all kind of tasks whether the input file is XML or it can be a source code file of any multiple extensions.

The tools & Technologies involved in Business Intelligence Software also saves a lot of money & time of clients and make the work of customers effective in a smart convenient manner.The Business Intelligence Software currently used by a number of MNCs or we can say in better way that a number of companies are benefitted by Business Intelligence on a large scale.A business professional can understand the utilization of this software.The Business Intelligence Software has the abilities to accomplish the tasks of making sensed data.Thus, this software is crossing the boundaries of other reporting softwares in the market because the only software that is flexible to work in so many environments is only Business Intelligence Software.
The work of the clients becomes quiet Time Saving , more Cost Effective & easy by this software product that is responsible for layout in any manner you want & templete formation reach the target easy which for other reporting software is a troublesome task to achive. The thing more matters than anything else is the satisfaction of customer & Business Intelligence Solutions fulfil all the requirements.

The most important thing to be noted is that implementing in programming is somewhat very easy & can be completed in just a few lines of code in either Java or .NET also.This means you are getting so many benefits of Business Intelligence Software which is also too easy to implement i.e From all benefits is the only basis from the basic principle of Business Intelligence Software.The coding is also efforts saving, the time saving, cost effectiveness & productiveness that make the Business Intelligence Software a unique software in the market & it differentiates it from all the other softwares presently working in market. The checkboxes comprises many option choices as Design Reports in MS Word,Design Reports in MS Excel,Design Reports in PowerPoint,Unlimited Data Access with Drag-and-Drop functionality, Report Scheduling, Dashboards, Report Collaboration, Dynamic Charting, Free-Form Design, Excel as a Datasource, Report Servers and Engines, SharePoint Integration, PODs, Macros are all ticked correct which is the most impressive facility of Business Intelligence Software only.

There are softwares which takes a lot of time in formatting & exporting data which make then quiet time consuming.The Free-Form Designing ability Business Intelligence Software make it effective in Dynamic field also. The tools ability makes the files formatted in any format whether XML or PowerPoint or Word.Also validation features provides the user validations that documents are set properly.The avalability in Java, .NET and Sharepoint with Docgen facility make the software quite fast & time effcetive also.The consistant messaging make the communication between the customers very effective in all manners.I am sure everyone who use this software must get benefits & benefits only.The basic 3 important features that everyone look for in any product is Power, Easy to use,Fast to Access & one can get all these benefits from just one product that is Business Intelligence Software only.One has to try this product atleast once & enjoy the easy , time saving way to generate complex documents.Also , It does make your money saves a lot in all respects.This is really a new approach to deal with in different layouts , templetes & formats. One can save the files in any formats they use to be whether PDF, HTML, DOCX, XML including exporting of data in files also.Just use it one time & get benefitted for life time. Thats the only aim of Business Intelligence Software & all must get success achieve in their hands fast & early.