Home » RDBMS Server » Performance Tuning » Session parametres
Session parametres [message #65260] Wed, 07 July 2004 03:01 Go to next message
Nauj
Messages: 33
Registered: February 2004
Member
Hello,

I'm working in a Oracle 9i session and I want to know the value of some session parameters like:

optimizer_goal, sort_area_size, ...

is there any system view where I can see these values??

Thank in advance.
Re: Session parametres [message #65262 is a reply to message #65260] Wed, 07 July 2004 05:37 Go to previous messageGo to next message
croK
Messages: 170
Registered: April 2002
Senior Member
Those parameters are SYSTEM parameters, not SESSION parameters.
You can see the values associated to system parameters by querying v$parameter.
Best regards.
Re: Session parametres [message #65264 is a reply to message #65260] Thu, 08 July 2004 01:44 Go to previous messageGo to next message
Oskar
Messages: 26
Registered: May 2002
Junior Member
You can use DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION to trace a particular session and use tkprof to analyze it.

Go to http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96533/sqltrace.htm#8760 for further info.
Re: Session parametres [message #65266 is a reply to message #65260] Sat, 10 July 2004 23:24 Go to previous message
Frank
Messages: 7901
Registered: March 2000
Senior Member
In sqlplus:
show parameter <Part of paramname>:

SCOTT>show parameter optimizer

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling integer 2
optimizer_features_enable string 10.1.0
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100
optimizer_mode string ALL_ROWS

SCOTT>show parameter sort_area

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sort_area_retained_size integer 0
sort_area_size integer 65536

You must however have select privs on v_$parameter

hth
Previous Topic: Health Check - on Enqueues
Next Topic: URGENT: Backup of big table
Goto Forum:
  


Current Time: Wed Feb 21 19:47:06 CST 2024