实时获得最耗CPU资源的SQL语句
在Oracle性能诊断和日常监控中，最耗CPU的语句通常也是我们最需要关心的语句。所以在Oracle10g的awr中，将cpu time和elapsed time最高的语句加入到了报表，并且放到了SQL语句部分的前两位。那么在平时的监控中，也可以通过shell脚本实时捕获系统中CPU耗用最多的进程中正在执行的SQL，以更加有效和及时的诊断和发现问题。
首先写一个根据spid来或者其SQL的脚本get_by_spid.sql
#!/bin/ksh
# creator:NinGoo
# function: get sql statement by spid
# parameter: spid
# useage: get_by_spid.sh spid
sqlplus -S /nolog <
col SERIAL# format 999999
col sid format 99999
col username format a10
col machine format a12
col program format a32
col sql_text format a81
set lines 1000
set pages 1000
set verify off
col sql_hash_value new_value hash_value head hash_value
select sid,serial#,username,program,sql_hash_value,
to_char(logon_time,'yyyy/mm/dd hh24:mi:ss') as login_time
from v\$session
where paddr in ( select addr from v\$process where spid=$1);
select sql_text
from v\$sqltext_with_newlines
where hash_value = &hash_value
order by piece;
exit;
EOF
然后再在另外一个shell脚本topsql.sh中获得系统中CPU耗用最多的oracle server process的spid，循环调用第一个脚本获得SQL
#!/bin/ksh
# creator:NinGoo
# function: get top cpu sql
# parameter: N
# useage: topsql.sh N
if [ $# -eq 0 ]; then
echo “Usage: `basename $0` N”
exit 1
fi
topcpu=`ps auxw|grep LOCAL|sort -rn +2 |head -$1|awk ‘{print $2}’`
i=0
for spid in $topcpu
do
i=`expr $i + 1`
echo “\033[32;1m===============top $i cpu sql=============\033[0m”
. /home/oracle/worksh/get_by_spid.sh $spid
done
那么调用就很简单了，假如我们要看系统top 3的sql语句，只需要执行topsql.sh 3即可。当然，如果我们自己通过top/topas等工具已经获得spid了，那么只要执行get_by_spid.sh spid就能获得该进程正在执行的sql语句了。
夏令时与Oracle PatchSet 10.2.0.4
Oracle10.2.0.4发布有一段时间了，在Metailink上搜索Patch Number：6810189，即可获得下载，目前已经发布了Linux X86/Linux X86_64/windows(32)三个版本，据说Solaris版本也已经发布，但是到今天Metalink上还是没有看到。
10.2.0.4中包含了DSTv4(Daylight Saving Time，也就是所谓的夏令时)的更新，所以如果你原有的系统中有使用Time Zone相关数据类型的（主要是TIMESTAMP WITH LOCAL TIME ZONE (TSLTZ)和TIMESTAMP WITH TIME ZONE (TSTZ)两种），升级之前需要检查系统的DST版本，然后做出相应的处理。
执行以下SQL，看数据库中是否使用了受影响的数据类型：
select c.owner || ‘.’ || c.table_name || ‘(‘ || c.column_name || ‘) -’ || c.data_type || ‘ ‘ col
from dba_tab_cols c, dba_objects o
where c.data_type like ‘%TIME ZONE’
and c.owner=o.owner
and c.table_name = o.object_name
and o.object_type = ‘TABLE’
order by col
/
如果没有，恭喜你，不用管啥DST了。如果有记录，那么根据现有的DST和Oracle版本的不同，需要做不同的处理。具体请参考Metalink(Note:553812.1)
如果是10g，可以通过如下SQL查询目前的DST版本：
select * from v$timezone_file;
如果是9i，是没有视图可以直接查的，Metalink上提供了一个utltzver.sql的脚本可以查询。
关于夏令时和Oracle，还可以参考Fenng的文章。
使用_px_trace跟踪分析并行执行的情况
并行在系统资源充足的情况下，可以极大的加快操作的速度，在数据仓库环境中应用较多。而在OLTP环境中，由于并发较大，开启并行可能瞬间导致资源耗尽，所以一般只有在业务低估期间执行一些诸如创建索引等维护操作时才会考虑开启并行，并且在执行完成后去掉对象的并行度，否则可能后果很严重。
由于并行涉及到多个进程间分配协调任务，往往比较容易出现各种各样的问题，而且从数据字典中比较难以定位到具体的原因。Oracle提供了一些event来trace并行过程，如10384,10390,10399等等，但是这些event往往无法trace整个的并行过程，有时候需要设置多个event才能trace到我们需要的内容。而_px_trace则提供了一个统一的trace入口，并且有些信息还是event无法trace到的。
语法如下：
alter session set “_px_trace”=[[Verbosity,]area],[[Verbosity,]area],..,[time];
- Verbosity表示trace信息的详细程度，取值为high,medium,low
- area表示trace的区域，取值scheduling,execution,granul,messaging,buffer,compilation,all,none
- time表示是否在trace中包含时间信息
SQL> alter session set “_px_trace”=”compilation”,”execution”,”messaging”,”time”;
会话已更改。
SQL> select count(*) from test;
COUNT(*)
———-
11846
SQL> alter session set “_px_trace”=”none”;
会话已更改。
生成的trace文件比较多，qc和slave进程都会生成相应的trace文件。具体的trace信息分析我这里就不写了，有兴趣的可以参考Metalink(Note:444164.1)
Oracle一个典型行列转换的几种实现方法
假如有如下表，其中各个i值对应的行数是不定的
SQL> select * from t;
I A D
———- ———- ——————-
1 b 2008-03-27 10:55:42
1 a 2008-03-27 10:55:46
1 d 2008-03-27 10:55:30
2 z 2008-03-27 10:55:55
2 t 2008-03-27 10:55:59
要获得如下结果，注意字符串需要按照D列的时间排序：
1 d,b,a
2 z,t
这是一个比较典型的行列转换，有好几种实现方法
