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)
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)
Labels:
11g,
hugepages,
Linux,
Oracle,
Performance,
transparent,
use_large_pages,
x64,
x86-64
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
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
Install Xming http://www.straightrunning.com/XmingNotes/
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 #>
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 #>
Subscribe to:
Posts (Atom)