Home » RDBMS Server » Server Administration » DB parameter change (oracle 11gr2 on RHEL )
DB parameter change [message #667872] Tue, 23 January 2018 02:35 Go to next message
dba4oracle
Messages: 77
Registered: June 2010
Member
Hi,

want to know if db parameter is changed(dyanamic parameter) like open_cursors which put max limit to sessions can open cursors
will sessions existing before this parameter change will get this new value

ie: if open_cursors was 50 and 100 sessions running,if open_cursors changed to 200 will that 100 sessions (old before change) also have
changed value of 200 for open cursors limit or will they fail after crossing 50

please suggest

Thanks
Re: DB parameter change [message #667875 is a reply to message #667872] Tue, 23 January 2018 05:03 Go to previous messageGo to next message
Roachcoach
Messages: 1571
Registered: May 2010
Location: UK
Senior Member
Usually this sort of thing is a new session affecting change.
Re: DB parameter change [message #667879 is a reply to message #667875] Tue, 23 January 2018 07:13 Go to previous messageGo to next message
dba4oracle
Messages: 77
Registered: June 2010
Member
thanks
but open_cursors parameter is configured at db level (spfile/pfile),you mean session when new session connect is it reading this parameter files
or how it works
Re: DB parameter change [message #667885 is a reply to message #667872] Tue, 23 January 2018 09:57 Go to previous messageGo to next message
Michel Cadot
Messages: 65844
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

SQL> select ISSES_MODIFIABLE, ISSYS_MODIFIABLE, ISINSTANCE_MODIFIABLE
  2  from v$parameter
  3  where name='open_cursors';
ISSES ISSYS_MOD ISINS
----- --------- -----
FALSE IMMEDIATE TRUE

From V$PARAMETER documentation page:

Quote:
ISSYS_MODIFIABLE Indicates whether the parameter can be changed with ALTER SYSTEM and when the change takes effect:

IMMEDIATE - Parameter can be changed with ALTER SYSTEM regardless of the type of parameter file used to start the instance. The change takes effect immediately.

DEFERRED - Parameter can be changed with ALTER SYSTEM regardless of the type of parameter file used to start the instance. The change takes effect in subsequent sessions.

FALSE - Parameter cannot be changed with ALTER SYSTEM unless a server parameter file was used to start the instance. The change takes effect in subsequent instances.

Conclusion?

Re: DB parameter change [message #667886 is a reply to message #667885] Tue, 23 January 2018 10:07 Go to previous messageGo to next message
dba4oracle
Messages: 77
Registered: June 2010
Member
yes with IMMEDIATE as SSYS_MODIFIABLE it change dyanamically but sessions connected before change what value they have ,new/changed or old

how sessions get value of this para while connecting
Re: DB parameter change [message #667887 is a reply to message #667886] Tue, 23 January 2018 10:18 Go to previous messageGo to next message
Michel Cadot
Messages: 65844
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

What DEFERRED means?

Re: DB parameter change [message #667888 is a reply to message #667887] Tue, 23 January 2018 10:22 Go to previous messageGo to next message
dba4oracle
Messages: 77
Registered: June 2010
Member
The change takes effect in subsequent sessions
but here is immediate for open cursors
Re: DB parameter change [message #667889 is a reply to message #667888] Tue, 23 January 2018 10:28 Go to previous messageGo to next message
cookiemonster
Messages: 13288
Registered: September 2008
Location: Rainy Manchester
Senior Member
So does that answer your question?
Re: DB parameter change [message #667890 is a reply to message #667888] Tue, 23 January 2018 10:29 Go to previous messageGo to next message
BlackSwan
Messages: 26194
Registered: January 2009
Location: SoCal
Senior Member
dba4oracle wrote on Tue, 23 January 2018 08:22
The change takes effect in subsequent sessions
but here is immediate for open cursors
Session only sees data as it exists at the start of transaction
Session won't see data that changes after session starts

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/cncpt/data-concurrency-and-consistency.html#GUID-7AD41DFA-04E5-4738-B 744-C4407170411C
Re: DB parameter change [message #667891 is a reply to message #667890] Tue, 23 January 2018 10:35 Go to previous messageGo to next message
dba4oracle
Messages: 77
Registered: June 2010
Member
hi blackswan

immediate for open cursors implies session will have new value of open cursor

and your statement imply session will have old value of open cursor

what is true
Re: DB parameter change [message #667892 is a reply to message #667891] Tue, 23 January 2018 10:36 Go to previous messageGo to next message
dba4oracle
Messages: 77
Registered: June 2010
Member




hi blackswan

SSYS_MODIFIABLE immediate for open cursors implies session will have new value of open cursor

and your statement imply session will have old value of open cursor

what is true
Re: DB parameter change [message #667893 is a reply to message #667890] Tue, 23 January 2018 10:46 Go to previous messageGo to next message
cookiemonster
Messages: 13288
Registered: September 2008
Location: Rainy Manchester
Senior Member
There's zero reason to assume that applies to DB parameters unless there's something in the docs that explicitly states that.
Re: DB parameter change [message #667894 is a reply to message #667892] Tue, 23 January 2018 10:57 Go to previous messageGo to next message
BlackSwan
Messages: 26194
Registered: January 2009
Location: SoCal
Senior Member
dba4oracle wrote on Tue, 23 January 2018 08:36




hi blackswan

SSYS_MODIFIABLE immediate for open cursors implies session will have new value of open cursor

and your statement imply session will have old value of open cursor

what is true
What do you observe when you test this scenario on your database?
Re: DB parameter change [message #667895 is a reply to message #667894] Tue, 23 January 2018 11:03 Go to previous messageGo to next message
dba4oracle
Messages: 77
Registered: June 2010
Member
i got error from old session even after increasing open cursors para
Re: DB parameter change [message #667896 is a reply to message #667895] Tue, 23 January 2018 11:07 Go to previous messageGo to next message
BlackSwan
Messages: 26194
Registered: January 2009
Location: SoCal
Senior Member
dba4oracle wrote on Tue, 23 January 2018 09:03
i got error from old session even after increasing open cursors para
So now you have your answer.
Re: DB parameter change [message #667897 is a reply to message #667896] Tue, 23 January 2018 11:27 Go to previous messageGo to next message
dba4oracle
Messages: 77
Registered: June 2010
Member
but want to understand how to avoid if know particular session open cursor value will exceed,increasing para value will not help as per you
Re: DB parameter change [message #667898 is a reply to message #667897] Tue, 23 January 2018 11:30 Go to previous messageGo to next message
dba4oracle
Messages: 77
Registered: June 2010
Member
many docs given solution to avoid error ora 001000 increase open cursor para
Re: DB parameter change [message #667899 is a reply to message #667897] Tue, 23 January 2018 11:33 Go to previous messageGo to next message
BlackSwan
Messages: 26194
Registered: January 2009
Location: SoCal
Senior Member
dba4oracle wrote on Tue, 23 January 2018 09:27
but want to understand how to avoid if know particular session open cursor value will exceed,increasing para value will not help as per you
If/when a database parameter is changed, only new sessions that start after the change will utilize the new value.
Old sessions will continue to use the old value.
What don't you understand about the 2 sentences above?

IMO, database parameters only get changed when new application software gets deployed & no client session are active.
Re: DB parameter change [message #667909 is a reply to message #667899] Tue, 23 January 2018 22:33 Go to previous messageGo to next message
dba4oracle
Messages: 77
Registered: June 2010
Member
Hi Blackswan,


I got your point,what i want to know is if we see some sessions about to exceed ,meaning sessions will trhough error fail

What is fix in this situation (killing that session not solution as it might be doing important task),as you mentioned increasing open cursors parameter value will not help

Thanks
Re: DB parameter change [message #667910 is a reply to message #667909] Tue, 23 January 2018 23:23 Go to previous messageGo to next message
BlackSwan
Messages: 26194
Registered: January 2009
Location: SoCal
Senior Member
dba4oracle wrote on Tue, 23 January 2018 20:33
Hi Blackswan,


I got your point,what i want to know is if we see some sessions about to exceed ,meaning sessions will trhough error fail

What is fix in this situation (killing that session not solution as it might be doing important task),as you mentioned increasing open cursors parameter value will not help

Thanks
Why did you implement a "solution" that requires ever more open cursors?

The fix is to change code so that it does NOT require many & more open cursors.

How many open cursors are enough for any & every situation?
Re: DB parameter change [message #667915 is a reply to message #667910] Wed, 24 January 2018 03:27 Go to previous messageGo to next message
dba4oracle
Messages: 77
Registered: June 2010
Member
Hi Blackswan,

Yes i know fixing code is permanent fix,but it can take some time,for identifying and fixing it (as per process)
if we see this issue as DBA like to do some workaround instead of just waiting for app team to fix the code and have outage

there has to be some way to fix this on the fly

also if you explain from sessions perspective ,how session get this value ,from where (from sga,memory,any file) will be helpful in understanding it better

thanks
Re: DB parameter change [message #667916 is a reply to message #667909] Wed, 24 January 2018 06:34 Go to previous messageGo to next message
EdStevens
Messages: 981
Registered: September 2013
Senior Member
dba4oracle wrote on Tue, 23 January 2018 22:33
Hi Blackswan,


I got your point,what i want to know is if we see some sessions about to exceed ,meaning sessions will trhough error fail

What is fix in this situation (killing that session not solution as it might be doing important task),as you mentioned increasing open cursors parameter value will not help

Thanks
So how is it that you "see" that a given session is "about to exceed", but has not yet exceeded?
Re: DB parameter change [message #667917 is a reply to message #667915] Wed, 24 January 2018 06:39 Go to previous messageGo to next message
dba4oracle
Messages: 77
Registered: June 2010
Member
its confusing what blackswan is saying and what oracle document says i am pasting reference from oracle document as below,which says alter system without deffered change parameter values globally

he ALTER SYSTEM statement without the DEFERRED keyword modifies the global value of the parameter for all sessions in the instance, for the duration of the instance (until the database is shut down). The value of the following initialization parameters can be changed with ALTER SYSTEM:


AQ_TM_PROCESSES
ARCHIVE_LAG_TARGET
ASM_DISKGROUPS
ASM_DISKSTRING
ASM_POWER_LIMIT
BACKGROUND_DUMP_DEST
CIRCUITS
COMMIT_WRITE
CONTROL_FILE_RECORD_KEEP_TIME
CORE_DUMP_DEST
CPU_COUNT
CREATE_STORED_OUTLINES
CURSOR_SHARING
DB_nK_CACHE_SIZE
DB_BLOCK_CHECKING
DB_BLOCK_CHECKSUM
DB_CACHE_ADVICE
DB_CACHE_SIZE
DB_CREATE_FILE_DEST
DB_CREATE_ONLINE_LOG_DEST_n
DB_FILE_MULTIBLOCK_READ_COUNT
DB_FLASHBACK_RETENTION_TARGET
DB_KEEP_CACHE_SIZE
DB_RECOVERY_FILE_DEST
DB_RECOVERY_FILE_DEST_SIZE
DB_RECYCLE_CACHE_SIZE
DG_BROKER_CONFIG_FILEn
DG_BROKER_START
DISPATCHERS
FAL_CLIENT
FAL_SERVER
FAST_START_MTTR_TARGET
FAST_START_PARALLEL_ROLLBACK
FILE_MAPPING
FILESYSTEMIO_OPTIONS
FIXED_DATE
GLOBAL_NAMES
HS_AUTOREGISTER
JAVA_POOL_SIZE
JOB_QUEUE_PROCESSES
LARGE_POOL_SIZE
LDAP_DIRECTORY_ACCESS
LICENSE_MAX_SESSIONS
LICENSE_MAX_USERS
LICENSE_SESSIONS_WARNING
LOCAL_LISTENER
LOG_ARCHIVE_CONFIG
LOG_ARCHIVE_DEST
LOG_ARCHIVE_DEST_n
LOG_ARCHIVE_DEST_STATE_n
LOG_ARCHIVE_DUPLEX_DEST
LOG_ARCHIVE_LOCAL_FIRST
LOG_ARCHIVE_MAX_PROCESSES
LOG_ARCHIVE_MIN_SUCCEED_DEST
LOG_ARCHIVE_TRACE
LOG_CHECKPOINT_INTERVAL
LOG_CHECKPOINT_TIMEOUT
LOG_CHECKPOINTS_TO_ALERT
MAX_DISPATCHERS
MAX_DUMP_FILE_SIZE
MAX_SHARED_SERVERS
NLS_LENGTH_SEMANTICS
NLS_NCHAR_CONV_EXCP
OPEN_CURSORS
OPTIMIZER_DYNAMIC_SAMPLING
OPTIMIZER_FEATURES_ENABLE
OPTIMIZER_INDEX_CACHING
OPTIMIZER_INDEX_COST_ADJ
OPTIMIZER_MODE
PARALLEL_ADAPTIVE_MULTI_USER
PARALLEL_INSTANCE_GROUP
PARALLEL_MAX_SERVERS
PARALLEL_MIN_SERVERS
PARALLEL_THREADS_PER_CPU
PGA_AGGREGATE_TARGET
PLSQL_CODE_TYPE
PLSQL_DEBUG
PLSQL_NATIVE_LIBRARY_DIR
PLSQL_NATIVE_LIBRARY_SUBDIR_COUNT
PLSQL_OPTIMIZE_LEVEL
PLSQL_V2_COMPATIBILITY
PLSQL_WARNINGS
PRE_11G_ENABLE_CAPTURE
QUERY_REWRITE_ENABLED
QUERY_REWRITE_INTEGRITY
REMOTE_DEPENDENCIES_MODE
REMOTE_LISTENER
RESOURCE_LIMIT
RESOURCE_MANAGER_PLAN
RESUMABLE_TIMEOUT
SERVICE_NAMES
SGA_TARGET
SHARED_POOL_SIZE
SHARED_SERVER_SESSIONS
SHARED_SERVERS
SKIP_UNUSABLE_INDEXES
SQLTUNE_CATEGORY
STANDBY_ARCHIVE_DEST
STANDBY_FILE_MANAGEMENT
STAR_TRANSFORMATION_ENABLED
STATISTICS_LEVEL
STREAMS_POOL_SIZE
TIMED_OS_STATISTICS
TIMED_STATISTICS
TRACE_ENABLED
UNDO_RETENTION
UNDO_TABLESPACE
USER_DUMP_DEST
WORKAREA_SIZE_POLICY
The ALTER SYSTEM ... DEFERRED statement does not modify the global value of the parameter for existing sessions, but the value will be modified for future sessions that connect to the database. The value of the following initialization parameters can be changed with ALTER SYSTEM ... DEFERRED:

Re: DB parameter change [message #667918 is a reply to message #667916] Wed, 24 January 2018 06:41 Go to previous messageGo to next message
dba4oracle
Messages: 77
Registered: June 2010
Member
querying views like v$open_cursors
sesstat,sessions
Re: DB parameter change [message #667919 is a reply to message #667917] Wed, 24 January 2018 06:43 Go to previous messageGo to next message
dba4oracle
Messages: 77
Registered: June 2010
Member
https://docs.oracle.com/cd/B19306_01/server.102/b14237/initparams004.htm#i1124758
Re: DB parameter change [message #667920 is a reply to message #667919] Wed, 24 January 2018 07:05 Go to previous messageGo to next message
dba4oracle
Messages: 77
Registered: June 2010
Member
deferred = this is the interesting element which i was aware of the existence till i read the book of Mr. Kyte. Indicates that the parameter change takes place for subsequent sessions only (not currently established sessions, including the one making the change). This element must be used for the parameters whose ISSYS_MODIFIABLE column in v$parameter view is deferred.
Re: DB parameter change [message #667924 is a reply to message #667920] Wed, 24 January 2018 08:23 Go to previous messageGo to next message
dba4oracle
Messages: 77
Registered: June 2010
Member

open_cursors Tips
Oracle Database Tips by Donald BurlesonMarch 23, 2015
For example, if you set open_cursors=100, Oracle will be allowed to allocate up to 100 cursor slots in the library cache. Because the slots are only allocated as they are requested, there is no added overhead to setting this value higher than actually needed.

The starting value is set by Oracle at instance creation time.

Just like the sessions and processes parameters, your application usage determines the value for open_cursors.

If you set open_cursors value too high, you risk having a task abort with the ORA-01000 error:

ORA-01000 maximum open cursors exceeded

Whenever you get an ORA-01000 error you need to determine if the session has a bug or whether the cursor requests are legitimate. You can change the open_cursors parameter dynamically while the database is running using an alter system statement:

alter system set open_cursors = 400 scope=both;
Re: DB parameter change [message #667926 is a reply to message #667915] Wed, 24 January 2018 08:56 Go to previous messageGo to next message
BlackSwan
Messages: 26194
Registered: January 2009
Location: SoCal
Senior Member
dba4oracle wrote on Wed, 24 January 2018 01:27
Hi Blackswan,

Yes i know fixing code is permanent fix,but it can take some time,for identifying and fixing it (as per process)
if we see this issue as DBA like to do some workaround instead of just waiting for app team to fix the code and have outage

there has to be some way to fix this on the fly

also if you explain from sessions perspective ,how session get this value ,from where (from sga,memory,any file) will be helpful in understanding it better

thanks
Why must "some way to fix this on the fly" be true?
post SQL & results that show how to increase value of OPEN_CURSORS parameter value for an existing session.
if you can NOT increase value of OPEN_CURSORS parameter value for an existing session, then no fix exists;
and ONLY solution is to modify the code to close Open Cursors when they are no longer needed.
Re: DB parameter change [message #667928 is a reply to message #667926] Wed, 24 January 2018 09:09 Go to previous messageGo to next message
dba4oracle
Messages: 77
Registered: June 2010
Member
hi blackswan
what you say about below sql

alter system set open_cursors = 400 scope=both;

(assume using spile)wii it change open_cursors to 400 in current session or no

if no please share link of oracle doc for ref.

thanks
Re: DB parameter change [message #667929 is a reply to message #667928] Wed, 24 January 2018 09:11 Go to previous messageGo to next message
dba4oracle
Messages: 77
Registered: June 2010
Member
https://gerardnico.com/wiki/db/oracle/parameter#memory

- Changeable online and immediately reflected in all sessions
The change will connect all currently connected sessions. For example, USER_DUMP_DEST is changeable online and is immediately reflected in all sessions:
Re: DB parameter change [message #667930 is a reply to message #667928] Wed, 24 January 2018 09:16 Go to previous messageGo to next message
BlackSwan
Messages: 26194
Registered: January 2009
Location: SoCal
Senior Member
dba4oracle wrote on Wed, 24 January 2018 07:09
hi blackswan
what you say about below sql

alter system set open_cursors = 400 scope=both;

(assume using spile)wii it change open_cursors to 400 in current session or no

if no please share link of oracle doc for ref.

thanks
Above has ZERO impact on any existing session.
Post SQL & results that show me wrong.

post SQL & results that show how to increase value of OPEN_CURSORS parameter value for an EXISTING session
Re: DB parameter change [message #667931 is a reply to message #667929] Wed, 24 January 2018 09:34 Go to previous messageGo to next message
dba4oracle
Messages: 77
Registered: June 2010
Member
select name,value,ISSYS_MODIFIABLE from v$parameter where name='open_cursors';
NAME, VALUE , ISSYS_MOD
---------------------------------------------------
open_cursors 300 IMMEDIATE


If it is a dynamic parameter #
sql> alter system set open_cursors=500 scope=both;
scope=both
# Value is updated to spfile
# Value is updated in memory

>>Value is updated in memory ---what it means current sessions will get changed value or no
Re: DB parameter change [message #667932 is a reply to message #667931] Wed, 24 January 2018 09:35 Go to previous messageGo to next message
BlackSwan
Messages: 26194
Registered: January 2009
Location: SoCal
Senior Member
dba4oracle wrote on Wed, 24 January 2018 07:34
select name,value,ISSYS_MODIFIABLE from v$parameter where name='open_cursors';
NAME, VALUE , ISSYS_MOD
---------------------------------------------------
open_cursors 300 IMMEDIATE


If it is a dynamic parameter #
sql> alter system set open_cursors=500 scope=both;
scope=both
# Value is updated to spfile
# Value is updated in memory

>>Value is updated in memory ---what it means current sessions will get changed value or no
Above has ZERO impact on any existing session.
Post SQL & results that show me wrong.

post SQL & results that show how to increase value of OPEN_CURSORS parameter value for an EXISTING session
Re: DB parameter change [message #667934 is a reply to message #667932] Wed, 24 January 2018 11:13 Go to previous messageGo to next message
cookiemonster
Messages: 13288
Registered: September 2008
Location: Rainy Manchester
Senior Member
Blackswan - have you actually read the quote from the documentation Michel posted in the 4th post in this thread?
It explicitly states that modifying open_cursors affects existing sessions.
Re: DB parameter change [message #667935 is a reply to message #667934] Wed, 24 January 2018 11:31 Go to previous messageGo to next message
BlackSwan
Messages: 26194
Registered: January 2009
Location: SoCal
Senior Member
cookiemonster wrote on Wed, 24 January 2018 09:13
Blackswan - have you actually read the quote from the documentation Michel posted in the 4th post in this thread?
It explicitly states that modifying open_cursors affects existing sessions.
Then it should be trivial to post SQL & results that show this reality.
Re: DB parameter change [message #667944 is a reply to message #667918] Thu, 25 January 2018 06:31 Go to previous messageGo to next message
EdStevens
Messages: 981
Registered: September 2013
Senior Member
dba4oracle wrote on Wed, 24 January 2018 06:41
querying views like v$open_cursors
sesstat,sessions
And you actually sit there and repeatedly issue the query all day long so that you can that you "see" that a given session is "about to exceed" the limits?
Re: DB parameter change [message #667960 is a reply to message #667944] Fri, 26 January 2018 09:32 Go to previous messageGo to next message
dba4oracle
Messages: 77
Registered: June 2010
Member
Hi Edstevens
cmon you cannot be so naive,i just given query, even though its not relevant to ask query for this issue ,still i shared as was asked repeatedly,script,tool/oem can do the work of checking threshold and alerting(it is not important what technique we use to find threshold when open cursor go above threshold ,there are tons of tools which can throw alert)
important is action to take once know open cursor max limit for particular session is above to breach

hope i could confirm if increasing cursor value will help or no,else i have to setup env for testing (it is time consuming )
Re: DB parameter change [message #667962 is a reply to message #667960] Sat, 27 January 2018 09:55 Go to previous messageGo to next message
EdStevens
Messages: 981
Registered: September 2013
Senior Member
dba4oracle wrote on Fri, 26 January 2018 09:32
Hi Edstevens
cmon you cannot be so naive,
You call it naive,I call it experience. Yes', I've seen people who do EXACTLY that.

Quote:
else i have to setup env for testing (it is time consuming )
Creating a test system is Time will invested. I'm surprised you don't already have one. But on the other hand, I think the entire premise of this thread is a waste of time. I know that in my own job I've got better things to worry about than if some session "might" be "about to" "possibly" exceed a predetermined threshold. I can very easily imagine a situation where all of my users get to (but never exceed) 98% of the limit. I certainly don't want to be flooded with alerts about every car on the road that is doing 69.8 mph in a 70 mph speed zone.

[Updated on: Sat, 27 January 2018 09:57]

Report message to a moderator

Re: DB parameter change [message #667971 is a reply to message #667962] Mon, 29 January 2018 03:44 Go to previous messageGo to next message
dba4oracle
Messages: 77
Registered: June 2010
Member
Thanks Edstevens

test will definitely clear this,we do have test env but dont want to use it as it may impact work of others using same test instance,so i have to create new one ,but by forum this time and effort could had saved

in production some critical batch job is running that particular session opening many cursors (say its application bad code or bug etc that needs to be fix)
if max cursor error thrown after it crossed open_cursors set,that batch job will fail,it will be production impact,if have workaround this can be prevented

Re: DB parameter change [message #667972 is a reply to message #667971] Mon, 29 January 2018 03:49 Go to previous message
Roachcoach
Messages: 1571
Registered: May 2010
Location: UK
Senior Member
>if max cursor error thrown after it crossed open_cursors set,that batch job will fail,it will be production impact,if have workaround this can be prevented


Not if it is leaking you can't. You may delay it, that's about it.
Previous Topic: UnUsed column in the database
Next Topic: ORA-01012 ORA-02063 ORA-02396 Database Link Disconnect
Goto Forum:
  


Current Time: Fri Oct 19 03:46:50 CDT 2018