Ask The Real Tom

During my work as Oracle DBA with the focus on performance tuning I have to check often stuff on the system level.

Here I like to share my command list for checking importent stuff on solaris 10.

cpu count

psrinfo -v

cpu info

 mpstat  #report per-processor statistics

Memory

/usr/sbin/prtconf | grep Mem
Memory size: 16384 Megabytes

64 or 32 bit

isainfo -v # available
uname -a   # running
isainfo -kv #active

show system error codes

man -s2 intro

Edit / Show process priority

priocntl

Installed Software

 pkginfo | grep VERITAS

ACL (http://www.softpanorama.org/Solaris/ACL/index.shtml)

getfacl foo
setfacl -m user:jeff:rw- foo

Trace process

truss -p <pid>

Summary about system calls

truss -c -p <pid>

Follow forked childs

truss -f <cmd>

Prozess wait statistics

truss -c -p <pid>

DTrace dynamic tracing compiler and tracing utility

    dtrace

Examples dtrace

# New processes with arguments,
dtrace -n 'proc:::exec-success { trace(curpsinfo->pr_psargs); }'

# Files opened by process,
dtrace -n 'syscall::open*:entry { printf("%s %s",execname,copyinstr(arg0)); }'

# Syscall count by program,
dtrace -n 'syscall:::entry { @num[execname] = count(); }'

# Syscall count by syscall,
dtrace -n 'syscall:::entry { @num[probefunc] = count(); }'

# Syscall count by process,
dtrace -n 'syscall:::entry { @num[pid,execname] = count(); }'

# Disk size by process,
dtrace -n 'io:::start { printf("%d %s %d",pid,execname,args[0]->b_bcount); }'   

# Pages paged in by process,
dtrace -n 'vminfo:::pgpgin { @pg[execname] = sum(arg0); }'

Dtrace References

• http://www.sun.com/emrkt/expertexchange/DTracevLowRes.pdf
• http://docs.sun.com/app/docs/doc/817-6223

report user-level lock statistics

plockstat

report kernel lock and profiling statistics

lockstat

Print the /proc tracing flags, the pending and held signals, and other /proc status information for each lwp in each process.

pflags

process tree

ptree

sysdef - output system definition

sysdef -i

ex. max processe kernel parameter

user@abc:/tmp $ sysdef -i |grep proc
fs/procfs
fs/sparcv9/procfs
  30000        maximum number of processes (v.v_proc)
  29995        maximum processes per user id (v.v_maxup)
  100  max undo entries per process (SEMUME)
  250  max attached shm segments per process (SHMSEG)

% prtconf | grep Mem
Memory size: 32760 Megabytes

% id -p
uid=59008(oracle) gid=10001(dba) projid=3(default)

% prctl -n project.max-shm-memory -i project 3
project: 3: default
NAME    PRIVILEGE       VALUE    FLAG   ACTION                       RECIPIENT
project.max-shm-memory
        privileged      7.84GB      -   deny                                 -
        system          16.0EB    max   deny                                 -

 prctl -n project.max-shm-memory -i project oracle

solaris 10

kernel parameter check

Take a userprocess id (ps -ef |grep ..)

prctl  <pid>

all parameter

prctl -i project oracle

Management Commands

Commands for managing project attributes include the following:

   * projects: Displays project memberships for users, lists projects from the project database, prings information on given projects.
   * newtask: Executes the shell or command in a new task in the current project.
   * projadd: Adds a new entry to the /etc/project entry.
   * projmod: Modifies information for a project in /etc/project.
   * projdel: Deletes a project from /etc/project.
   * rctladm: Displays/modifies global state of active resource controls, sets logging or actions.
   * prctl: Displays/modifies local resource controls.
   * ipcs: Identifies which IPC objects are being used in a project.
   * rcapadm: Manages rcapd memory-capping daemon.
   * prstat -J: Displays resource consumption on a per-project basis
   * priocntl -i project-name: Sets/displays scheduling parameters of the project.
   * poolbind -i project-name: Assigns a project to a resource pool.

process info

Memory by process (rss in kb)

ps -e -o pid,vsz,rss,comm |grep <cmdname>

Sumarize memory of a set of processes

ps -e -o pid,vsz,rss,comm |grep java|awk '{sum = sum + $2} END {print sum}'

ps -ly

GUI - top

sdtprocess

The bad situation:

• You deleted a datafile containing a partition
• You are running 10.2.0.4 where you can not drop unused columns on compressed tables due a bug
• You can not drop the tablespace since it is containing the partition.
• Exchange partition does not work cause of the unused column

The good solution:


# get the creeate table sql (skip the partitions part)
set long 3000
SELECT dbms_metadata.get_ddl('TABLE','T_XXXXXX','SCHAMAX') from dual;

#get the object id of the table
select OBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID,SUBOBJECT_NAME from dba_objects where object_name='T_XXXXXX'
and object_type='TABLE';

#get the unused column name and type
select COLUMN_NAME,DATA_TYPE,COLUMN_ID,hidden_column from dba_tab_cols where table_name='T_XXXXX' and hidden_column='YES';
-- it name ex. SYS_C00017_06101305 -> co its column 17

# add the column to the create table statement, name the table test (create it not in the sys schema)
# create the table
# drop the column
alter table schamex.test set unused column "SYS_C00017_06101305:25:02$";

# exchange the partition
ALTER TABLE schmemaX.T_XXXXXX EXCHANGE PARTITION P_XXXX_2009KW08 WITH TABLE schmemaX.test;

# drop the table
drop table schmemaX.test;

# drop your offline tablespace (with the lost datafiles)
drop tablespace T_XXXX_2009KW08 including contents and datafiles;


Conclusion:
Oracle is great -> but you have to be great as well!