Oracle Session-related optimizations

Reading: Students are not all used to meet this situation, a business system development period of business concurrency is only estimated a value, and the system on-line after this concurrency may overflow or insufficient   situation. In this case, how can our DBAs give reasonable performance tuning recommendations? This paper is to use the data to give a reasonable value of the session concurrency.   Purpose: Use data to give a reasonable value for Oracle Performance tuning session.   Environment: os:6.1 aixoracle:11.2.0.4  rac on 2 nodes 1. User Max concurrent session after instance startup? Select Sessions_current,sessions_highwater from V$license;  2. Does the database have a large number of inactive sessions? Select (select COUNT (*) from v$session where status= ' INACTIVE ') as "session INACTIVE", (select COUNT (*) from v$session) as The ' session total ' from dual; --can initially determine whether the middleware connection number is set reasonably. If the inactive/total ratio is greater than 0.6 consider whether it is possible to optimize (personal suggestion)  3. Database A lot of inactive what is the business of the session? Select Username,count (*) from v$session where status= ' INACTIVE ' GROUP by username; --can initially determine what business has excessive INACTIVE, Whether the contact can be optimized (for " 4"). Now that we have found a large number of inactive exist in the database, how to give the optimization advice? My method: Write a script that counts the total session with the active session, executes the script regularly and writes the results to a table. After a lot of statistics (peak business and Low peak) can   get a reasonable session value, you can put this reasonable concurrent session value to the business, and then optimize the use of system resources, rather than generate a large number of inactive session   4-1. Create a table that collects statistics dou_tunningcreate table dou_tunning (usernAme Varchar2 (+), total number,active number,osdate date);  4-2. Writing a session-gathering script VI check_session.sh# For Oracle user environment variables, be sure to edit in script #sqlplus-s ' System/system ' <insert into  dou_tunning (select T.username,t.total, D.active,sysdate from (select Username,count (*) as total from gv\  $session where username are not null and username no T in (' SYSTEM ', ' Sysman ', ' Dbsnmp ') group by username) T, (Select username,count (*) as active from gv\ $session where u Sername is isn't null and status= ' ACTIVE ' GROUP by username) d where t.username=d.username); commit;exit; Eof   4-3. Timed execution of the collection session script doudou1:/home/oracle$crontab-l5,15,25,35,45,55  * * * * sh/home/ Oracle/check_session.sh --aix in the crontab does not support the "/", very helpless can only be written like this. Of course, you can also collect session information every minute to give customers more detailed optimization data.  4-4. According to the collected data, according to the peak of the business session value, and then give the optimal concurrency session value  5.case Analysis select * from dou_tunning where username= ' tab_ DOUDOU ' Tab_doudou 363 1 2014/8/12 13:25:01Tab_doudou 361 12014/8/12 13:28:00Tab_doudou 358 12014/8/12 13:30:01Tab_doudou 358 32014/8/12 13:31:00Tab_doudou 358 22014/8/12 13:32:00Tab_doudou 358 32014/8/12 13:33:00Tab_doudou 358 22014/8/12 13:34:01Tab_doudou 362 12014/8/12 13:36:00Tab_doudou 363 42014/8/12 13:37:00Tab_doudou 363 22014/8/12 13:38:01Tab_doudou 366 32014/8/12 13:39:00Tab_doudou 368 22014/8/12 13:40:00Tab_doudou 364 22014/8/12 13:41:00Tab_doudou 364 12014/8/12 13:42:00Tab_doudou 358 32014/8/12 13:43:00Tab_doudou 362 12014/8/12 13:44:00Tab_doudou 362 22014/8/12 13:47:00Tab_doudou 360 12014/8/12 13:48:00Tab_doudou 358 12014/8/12 13:50:00Tab_doudou 357 32014/8/12 13:51:01Tab_doudou 357 12014/8/12 13:52:00Tab_doudou 358 12014/8/12 13:53:00Tab_doudou 356 12014/8/12 13:55:00Tab_doudou 362 42014/8/12 14:05:00Tab_doudou 362 22014/8/12 14:15:00Tab_doudou 363 12014/8/12 14:45:00Tab_doudou 363 22014/8/12 14:55:01Tab_doudou 361 32014/8/12 15:05:00Tab_doudou 362 12014/8/12 15:15:01Tab_doudou 366 12014/8/12 15:25:00Tab_doudou 360 12014/8/12 15:35:00Tab_doudou 359 22014/8/12 15:45:00Tab_doudou 361 12014/8/12 15:55:00Tab_doudou 361 12014/8/12 16:05:00--The above is a core business statistics session value, you can see close to 400 of the session, most are inactive, this one resource use serious unreasonable case, of course, I will continue to the system for analysis and comparison,  Finally, a constructive session value is presented to the client. Summary: The rational use of resources is also an optimized way. Optimization is actually a kind of thought.

Oracle Session-related optimizations