SQL to view OS process and Oracle session info

Original source:
http://www.tiplib.com/113/sql-view-os-process-oracle-session-info

Use this commands and sql query to show information about OS process and Oracle user session.
Useful if for example you notice a single process is chewing up CPU on the server.

At the Unix prompt (replace 22910 with the actual process id):

$ ps -ef | grep 22910

oracle 22910     1 14 09:16:59 ?       32:09 oracleDBNAME
(DESCRIPTION=(LOCAL=no)(ADDRESS=(PROTOCOL=BEQ)))

In sqlplus or TOAD run this query:

SELECT	s.saddr, s.sid, s.serial#, s.username,
	s.osuser, s.machine, s.program, s.logon_time, s.status, 
	p.program, p.spid
FROM v$session s, v$process p
WHERE s.paddr = p.addr
AND p.spid IN (22910);

If otherwise you need to identify Unix process for a specific Oracle database session run SQL
statement below (assuming SIDs are 39 and 24, can also put username or osuser etc in whe where
clause):

SELECT	p.program, p.spid, s.saddr, s.sid, s.serial#, s.username,
	s.osuser, s.machine, s.program, s.logon_time, s.status 
FROM v$session s, v$process p
WHERE s.paddr = p.addr
AND s.sid IN (39, 24)

PROGRAM                        SPID
------------------------------ ---------   ...
oracle@hostname (TNS V1-V3)    590         ...
oracle@hostname (TNS V1-V3)    6190        ...

At the OS prompt get the process information

ps -ef | egrep '590|6190'

  oracle 18232 14573  0 10:16:17 pts/4    0:00 egrep 590|6190
  oracle  6190     1  0   Jul 28 ?       14:40 oracleDBNAME
(DESCRIPTION=(LOCAL=no)(ADDRESS=(PROTOCOL=BEQ)))
  oracle   590     1  0   Jul 30 ?       12:10 oracleDBNAME
(DESCRIPTION=(LOCAL=no)(ADDRESS=(PROTOCOL=BEQ)))

how can i extract a range of lines from a text file on unix

http://stackoverflow.com/questions/83329/how-can-i-extract-a-range-of-lines-from-a-text-file-on-unix

I have a ~23000 line sql dump containing several databases worth of data. I need to extract a certain section of this file (i.e. the data for a single database) and place it in a new file. I know both the start and end line numbers of the data that I want.

Does anyone know a unix command (or series of commands) to extract all lines from a file between say line 16224 and 16482 and then redirect them into a new file?

sed -n 16224,16482p filename > newfile

Generate oAuth key in ..

Java

public String computeHmac(String baseString, String key)     throws NoSuchAlgorithmException, InvalidKeyException, IllegalStateException, UnsupportedEncodingException {     Mac mac = Mac.getInstance("HmacSHA1");     SecretKeySpec secret = new SecretKeySpec(key.getBytes(), mac.getAlgorithm());     mac.init(secret);     byte[] digest = mac.doFinal(baseString.getBytes());     return Base64.encode(digest); }
or

MessageDigest digest = MessageDigest.getInstance("SHA-1");
			digest.reset();
			byte[] hash = digest.digest(body.getBytes("UTF-8"));
			String encodedHash = Base64.encode(hash);

MySQL 99.9% CPU , 1.4%Memory , help ….

Before I jumped into my findings , here I share my problem of my SMS system

  • > 200 request per minutes
  • 20 persistent connection <- I used OJB framework, now it is in the ATTIC
  • 99.9% MySQL CPU
  • 150Kb/s traffic to MySQL
  • I panic
  • MySQL 5.1.35 running on Redhat Linux Release 9 (Shrike)
  • No my.cnf , running on default installation
Since i have no my.cnf, i have to set the SERVER VARIABLES manually each time the mysql instance was restarted. I’ve checked so many websites , in order to improve performance , you are advised to enable query caching, and yes it works.
This is how to do it, since i don’t have my.cnf anywhere in the server
1. create empty my.cnf at /etc/my.cnf . This will overwrite any my.cnf exists in the system (if you run default mysql)
2. Below are the content of my.cnf
will be continued