Server Administration :: Shared Pool Free Percentage Greater Than 100
Jul 5, 2010
value of "Shared Pool Free %" in v$sysmetric is larger than 100%
SQL> select value, group_id from v$sysmetric where metric_name = 'Shared Pool Free %';
VALUE GROUP_ID
186.45107 2
186.4685382 3
SQL> show parameter shared_pool
NAME TYPE VALUE
-------------- ------------ --
shared_pool_reserved_size big integer 832149913
shared_pool_size big integer 0
SQL> show sga
Total System Global Area 1.0155E+11 bytes
Fixed Size 2163880 bytes
Variable Size 8.1068E+10 bytes
Database Buffers 2.0401E+10 bytes
Redo Buffers 79310848 bytes
If I have specify the SGA_TARGET, do I still need to specify the amount of memory for SHARED_POOL_SIZE? I thought once the SGA_TARGET, auto memory management is in place and SHARED_POOL is not neccessary.
we are running a 2 node rac database of oracle 10.2.0.4 in aix 6.3. The shared pool utilisation goes up when we run our jobs and sql statements. But it does not come down. It been 2 gig for over 3 days without any processes running.
Is there a threshold time period after which oracle will release the space utilisation from the shared pool????
We are facing performance issues on our production instance 10g(10.2.0.4) 32-bit OELinux 5.3 2GB SGA. The performance is mainly related to one of the table which is sized about 32Gb. We have rebuild the indexes as well but problemstill persist. We are considering to pin SQL statement in shared pool which is hitting the same table frequently. But as far what we have find, is that we can only pin procedures or function in shared pool. True/false?If we can, then how to pin SQL statement in shared pool?If we can not, then is there any other way?
we have 5 tempfile ( each of 65 gb ) allocated to TEMP tablespace...and still we are running in short of space..when i checked the TEMP segment usage, i am able to see much FREE blocks. how to release those space ?
1 row selected. further when i checked the session details using TEMP segment, i got below output:
SELECT b.tablespace, b.segfile#, b.segblk#, b.blocks, a.sid, a.serial#,a.username, a.osuser, a.status FROM v$session a,v$sort_usage b WHERE a.saddr = b.session_addr ORDER BY b.tablespace, b.segfile#, b.segblk#, b.blocks;
TABLESPACE SEGFILE# SEGBLK# BLOCKS SID SERIAL# USERNAME OSUSER STATUS ------------------------------- ---------- ---------- ---------- ---------- ---------- ------------------------------ ------------------------------ -------- TEMP 15001 3549184 576 475 1237 EQUIPMENT infa ACTIVE TEMP 15001 4002368 64 796 4677 CRM infa ACTIVE TEMP 15002 580608 20352 868 615 EDW infa ACTIVE TEMP 15002 3962112 832 92 1065 EDWSTG infa ACTIVE TEMP 15002 4021120 576 1236 7257 EQUIPMENT infa ACTIVE TEMP 15003 23936 64 819 5586 EDW infa ACTIVE TEMP 15003 3798400 832 855 1801 EDWSTG infa ACTIVE TEMP 15004 205056 21632 795 8171 EDW infa ACTIVE TEMP 15004 4031488 832 403 1299 EDWSTG infa ACTIVE TEMP 15004 4131456 576 19 6802 EQUIPMENT infa ACTIVE TEMP 15005 3617856 832 1166 6204 EDWSTG infa ACTIVE TEMP 15005 3741760 576 862 953 EQUIPMENT infa ACTIVE TEMP 15005 4042752 18176 1226 5379 CDM infa ACTIVE
3 rows selected. if i killed the SID - 1226, then those temp blocks ( 18176 blocks ) will be released and can other session use that space further ?
there is one more column - SEGBLK# explain what is the exact meaning of this column ?
to reclaim the space, should i issue below command - sql>alter tablespace TEMP coalesce;
C:UsersAdministrator>sqlplus SQL*Plus: Release 10.2.0.4.0 - Production on Thu Jan 17 15:59:33 2013 Copyright (c) 1982, 2007, Oracle. All Rights Reserved. Enter user-name: sys@orcl as sysdba Enter password: Connected to an idle instance. SQL> create pfile from spfile;
[code].....
here its my init.ora file
############################################################################## # Copyright (c) 1991, 2001, 2002 by Oracle Corporation ############################################################################## ########################################### # Cache and I/O ###########################################
I have a package when it runs, it terminates with "unable to allocate 4120 bytes of shared memory"error at a particular INSERT statement. So everytime we had this error, we had to flush the shared pool area, after which it would run without issues(for 3-4 weeks).
It had a dynamic subpartition clause framed, which made it to repharse the query every time.So to reduce this, we removed the subpartition clause(made it to static query), which did benefit of not getting shared pool error.
I would like to find the current shared pool memory usage of my program.Is there a way I could find the live consumption of shared pool area of my program(pkg).
i am continuously inserting data to oracle database after some time like 2 hours oracle disconnects,it creates erros like
ERROR: ORA-01034: ORACLE not available ORA-27101: shared memory realm does not exist Process ID: 0 Session ID: 0 Serial number: 0
after restarting the database with shutdown immediate ans startup if i start inserting records it will show erros like
ORA-01653: unable to extend table SYSTEM.GLT_PROT_TRAFFIC_SUM_VOIP by 8192 in tablespace GLCOMM
but i have created the Tablespace with BigFile Auto Extend and max size unlimited, i am having 400GB disk space created Redo logs with 15gb,i have tried serveral times reinstalling the oracle but problem is not solved.
same problem is happenging with small datafiles also,
operating system: windows server 2008 R2 standard oracle server : 11g oracle client : 64 bit
1. How would I know whether I'm running Shared Server or Dedicated Server? 2. How to configure Shared Server? 3. How to configure Dedicated Server? 4. How Can I use both these connection types with one database?
I mean one session with dedicated and one with shared at the same time.
I need to copy .CSV File from a Windows Server shared path (\hostnameoutput) to another server which i believe is on unix.The other server name is abc.hcl.com. On this server i need to put it in the root directory. I will have to use SFTP and not FTP.
I have some confusion about Keep Pool in Buffer Cache.
1. What is the reasoning for placing a table in the KEEP buffer pool because if it is frequently accessed, it will be around when needed (ie if it is constantly being accessed it will not age out) . 2. Would the table be still in the Default Pool if the Keep Pool is not sized and the command is being issued alter TABLE SCOTT.EMP storage (buffer_pool keep) ? 3. If the database is restarted will the table be wiped out of the Keep Pool and again be pinned to the Keep Pool ?
We have a Data Source with min_pool_size (10) and max_pool_size (20). A Data Source is by default a connection pool. By starting a transaction we are retrieving a connection from the pool (i.e., opening it to retrieve data, perform queries, inserts and updates). Our application server is JBoss. An application workflow uses many transactions to build a product. The same connection is not used by the application for the entire workflow; but, it uses and returns them to the connection pool. We do not use Java syntax like "rs.close():"... this is performed by iBATIS.
On the Linux side when we execute a "ps" command (ps -elf|grep -i ora) we see all the Oracle processes. A further refinement of that command (ps -elf|grep -i local=no)shows a list of the "waiting" connections in the connection pool. The DB may be queried with the following syntax:
SELECT schemaname, sid, serial# FROM gv$session where schemaname = 'APP_USER' order by SID;
A list of connected sid and serial numbers is returned, identifying which connections are in use. From here we are able to force the connection to trace by executing the following:
exec dbms_monitor.session_trace_enable(249, 6595, TRUE, FALSE); ! 249 and 6595 being SID and Serial# from query above
There should be no need to execute the inverse, since the connection is returned to the pool when the transaction is committed or rolled back.
We are trying to trace in order to use the Quest Benchmark Factory. Their instructions request the following syntax be applied to each session:
alter session set events '10046 trace name context forever, level 4';
and again, the inverse should not be necessary.
alter session set events '10046 trace name context off'
When it became too cumbersome to alter each session as it appeared, we issued an "alter system" to monitor (trace) everything. The trace files filled the disk, and four hours of testing was stopped two hours in. Doing a system level trace is probably not a good idea.
My first inclination was to create a post-logon trigger to set trace in the session; however, these connections, coming from an JBoss connection pool, do not logon each time, and I presume that they are not all the "same session".
We opened a support ticket with Quest last Friday and do not have an answer yet. This was the third ticket with them, the first to get Benchmark Factory installed (the original installer did not work). The second ticket was to setting up a shared directory on Linux with a folder on Windows, a setup configuration required by their tool. The third ticket to address this issue.
They needed to contact "the developers" to answer the last two questions. Their latest suggestion is to fix ticket two so we "won't need to trace" anything. How do I set trace in these connections?
I am fairly new to Oracle SQL developer and I am having an issue that may very simple but I just cannot work it out logically for some reason.I am trying to calculate the percentage of sales each employee has made form the total of trades. I have been trying to use the COUNT function but it obviously splits the counts for each employee. I then tried a inline view but couldn't get it to work and also a sub-query and had an issue with "not a single group function" and then "not a GROUP BY expression".
how I can do this? I will also need to add the syntax to another inline view
Percentage will be calculated as rtons of code not having first digit 0 devided by itons having having code 0 result multiply 100 for the same date code wise e.g. For dated 17-jul-13 meal percentage will be calculated as round((104.304/193.155)*100,3)=54.000
