Showing posts with label Linux. Show all posts
Showing posts with label Linux. Show all posts

Friday, July 15, 2016


MEMORY USED BY SQL STATEMENT

select machine,to_char(SQL_EXEC_START,'DD-MON-YY HH24:MI:SS')            runtime,
       pga_allocated 
  from dba_hist_active_sess_history 
 where sql_id = '&sql_id';


EXAMPLE OUTPUT:

MACHINE                   RUNTIME                 PGA_ALLOCATED

------------------------- -------------------- ----------------
MYPCNAME                  13-JUL-16 16:18:40        807,153,664
MYPCNAME                  13-JUL-16 16:18:40        329,003,008
MYPCNAME                  13-JUL-16 16:18:40        274,280,448
MYPCNAME                  13-JUL-16 16:18:40        215,887,872
MYPCNAME                  13-JUL-16 16:18:40        204,615,680

Monday, April 15, 2013

Got a Large SGA?, USE HugePages



3 Key Reasons:

  1. Oracle "Strongly recommends always deploying HugePages with Oracle Databases"

  2. Reduces memory footprint by a factor of 500

  3. 4K vs. 2MB memory pages.  More pages can be mapped in memory with less overhead

Great video from OpenWorld DemoGrounds


HOW TO

Do steps 1-4 on EACH NODE


1. Set Oracle User memlock Limits
---------------------------------
NOTE: Value should be 90% of total memory
      <value> is in kb (ex. 200 gb = 209715200 kb)

As Root 
vi /etc/security/limits.conf

oracle soft memlock <value>
oracle hard memlock <value>


2. Set kernel.shmmax
--------------------
NOTE: Value should be comfortably larger than the largest SGA

As Root
get existing value:
 cat /proc/sys/kernel/shmmax
set new value:
 vi /etc/sysctl.conf
 AND
 echo <value in bytes> > /proc/sys/kernel/shmmax
 AND
 sysctl –p

verify new value
 cat /proc/sys/kernel/shmmax


3. Set kernel.shmall
--------------------
NOTE: Value should be sum of SGA's / pagesize 

Get pagesize
 getconf PAGESIZE
Determine sum of SGAs
 ipcs -m |grep oracle, sum of 5th column values + future SGAs

example: 
pagesize= 4,096
SGA's   = 92gb + 58gb growth = 161061273600 bytes
SGA's / pagesize = 39,321,600
kernel.shmall should be 39321600

get existing value:
 cat /proc/sys/kernel/shmall
set new value:
 vi /etc/sysctl.conf
 AND
 echo <value> > /proc/sys/kernel/shmall 
 AND
 sysctl -p
verify new value
 cat /proc/sys/kernel/shmall


4. Set vm.nr_hugepages
----------------------
NOTE: Use recommended value returned from hugepages_settings.sh
get the script here

As Root, add the following to /etc/sysctl.conf

 # Hugepages
 # Allow oracle user access to hugepages
 vm.hugetlb_shm_group = 310
 vm.nr_hugepages = <value>

AND run

sysctl -p

verify setting:
 cat /proc/sys/vm/nr_hugepages


5. Set use_large_pages=ONLY in spfile (11g only)
------------------------------------------------
 For Each DATABASE
  alter system set use_large_pages=ONLY scope=spfile;


6. Restart Database(s)
----------------------
 Verify huge pages in use (alert log)
 Run ipcs -m (should only have a couple segments per database)

Tuesday, January 29, 2013

How to disown a Background Linux Process



Have you ever started a script in the background (using &) and then wanted to close your session but didn't want to kill the background process?  Disown it.  It's kinda like using nohup, which is probably what you wanted to do in the first place.

Get the Job Number
jobs

Disown the Job
disown %<job#>

close the window and go home!

How to TRIM a LOG



Trim a log file FIFO (First In - First Out).
sed -i -e :a -e '$q;N;<# lines to leave>,$D;ba' <mylogfile.log​>

In this example we'll leave the last 25,000 lines in the logfile and trim the older entries.
sed -i -e :a -e '$q;N;25000,$D;ba' mylogfile.log​

Setup / Use xWindows with Putty


In Putty, Connection, SSH, X11, check box for “Enable X11 forwarding”

host> echo $DISPLAY
localhost:12.0

/home/oracle > xauth list
Copy key for 12
  
host> xauth add <key>
host> xeyes

Schedule a Script in Linux


Schedule a Job (without using cron)
  at -f sar_network_errors.sh -v 21:55

Job queue
  srpora73.srp.gov /home/oracle/scripts/ken > atq
  3       2012-10-05 21:55 a oracle​

Remove a Job
  atrm <job #>