Archive

Archive for July, 2011

Using Explain Plan for Plan Execution in Oracle

July 29, 2011 Leave a comment

EXPLAIN PLAN FOR
SELECT DISTINCT s.srvr_id
FROM servers s, serv_inst i
WHERE s.srvr_id = i.srvr_id;

set pagesize 25
set linesize 121

SELECT * FROM TABLE(dbms_xplan.display);

Display a specific plan by name:

EXPLAIN PLAN
SET STATEMENT_ID = ‘abc’ FOR
SELECT DISTINCT s.srvr_id
FROM servers s, serv_inst i
WHERE s.srvr_id = i.srvr_id;

set pagesize 25
set linesize 121

SELECT * FROM TABLE(dbms_xplan.display(‘PLAN_TABLE’,’abc’,’BASIC’));
SELECT * FROM TABLE(dbms_xplan.display(‘PLAN_TABLE’,’abc’,’TYPICAL’));
SELECT * FROM TABLE(dbms_xplan.display(‘PLAN_TABLE’,’abc’,’ALL’));

Advertisements
Categories: Oracle

Monitor disk usage on HP-UX

July 26, 2011 Leave a comment

If you don’t have a fancy monitoring system, put a script in a cronjob like the one below and it will email you if you are below your thresholds.

#!/bin/sh

# These limits are for AVAIL column in MBs from bdf output
# warn = 80MB, low = 50MB
warn=80000
low=50000

email=user@domain.com

# Excluding /home since it really isnt being used; always put / last in filesystems
filesystems=”/stand /var /usr /tmp /opt /”

for fs in $filesystems
do
size=`bdf $fs|grep $fs|awk ‘{ print $4; }’`
if [ $size -le $low ]
then
mailx -s “URGENT: Low disk space for $fs, ($size)MB left” $email < /dev/null
break
fi
if [ $size -le $warn ]
then
mailx -s "WARNING: Low disk space for $fs ($size)MB left" $email < /dev/null
fi
done

Categories: HP-UX, Linux

Oracle – tuning your buffer cache

July 22, 2011 Leave a comment

In Oracle 11g, you have the option of using Automatic Memory Management by setting only the memory_target, memory_max_target parameters. However, there are times when you want to set a certain minimum size for the buffer cache when the database is restarted. Use the query below to find the current buffer cache usage and what the optimal amount may be.

column c1 heading ‘Cache Size (m)’ format 999,999,999,999
column c2 heading ‘Buffers’ format 999,999,999
column c3 heading ‘Estd Phys|Read Factor’ format 999.90
column c4 heading ‘Estd Phys| Reads’ format 999,999,999

select
size_for_estimate c1,
buffers_for_estimate c2,
estd_physical_read_factor c3,
estd_physical_reads c4
from
v$db_cache_advice
where
name = ‘DEFAULT’
and
block_size = (SELECT value FROM V$PARAMETER
WHERE name = ‘db_block_size’)
and
advice_status = ‘ON’;

Estd Phys Estd Phys
Cache Size (m) Buffers Read Factor Reads
—————- ———— ———– ————
1,488 174,840 1.69 51,500,844
2,976 349,680 1.51 46,049,280
4,464 524,520 1.40 42,564,190
5,952 699,360 1.31 40,032,442
7,440 874,200 1.25 38,053,847
8,928 1,049,040 1.19 36,248,974
10,416 1,223,880 1.14 34,784,904
11,904 1,398,720 1.10 33,456,226
13,392 1,573,560 1.04 31,863,882
14,880 1,748,400 1.00 30,515,295
14,884 1,748,870 1.00 30,511,825
16,368 1,923,240 .96 29,230,516
17,856 2,098,080 .92 27,997,396
19,344 2,272,920 .88 26,923,627
20,832 2,447,760 .85 25,968,141
22,320 2,622,600 .82 25,038,758
23,808 2,797,440 .79 24,145,979
25,296 2,972,280 .76 23,232,426
26,784 3,147,120 .73 22,166,922
28,272 3,321,960 .70 21,294,246
29,760 3,496,800 .66 20,193,450

21 rows selected.

In reading the output, the 1.0 read factor is currently where your cache has been tuned to. If you want to increase it, the “Estd Phys Reads” will drop, but you’ll have to have plenty of physical memory to accommodate this. To do this, set the db_cache_size = 14G.

Categories: Oracle

How to check the architecture of an RPM package in Redhat

July 19, 2011 Leave a comment

[root@~]# rpm -q libaio.x86_64
libaio-0.3.105-2

[root@~]# rpm -q –queryformat ‘%{NAME}-%{VERSION}-%{RELEASE} %{ARCH}\n’ libaio
libaio-0.3.106-5 x86_64
libaio-0.3.106-5 i386

Categories: Linux, Redhat

Oracle changing statspack collection level

July 15, 2011 Leave a comment

To check your STATSPACK collection levels available:

SELECT * FROM stats$level_description ORDER BY snap_level;

Change the STATSPACK collection level to 5:

exec statspack.snap(i_snap_level => 5, i_modify_parameter => ‘true’);

Categories: Oracle

Oracle – when was last time my index was rebuilt?

July 15, 2011 Leave a comment

Taking a look at the LAST_DDL_TIME you can find the last time.

select OBJECT_NAME, timestamp, LAST_DDL_TIME from dba_objects where OBJECT_NAME=’IX_CARDBLOCKS’
SQL> /

OBJECT_NAME
——————————————————————————–
TIMESTAMP LAST_DDL_
——————- ———
IX_CARDBLOCKS
2011-06-29:21:22:53 29-JUN-11

SQL> alter index rebuild ix_cardblocks;

Run the first command again and you should see a change in time on LAST_DDL_TIME to current.

Categories: Oracle