utlrp.sql用来编译数据库内无效的对象，但有时可能会遇到utlrp.sql执行时间很长的情况。utlrp.sql执行时间长的可能原因之一是数据库并行度过高。
以下是在2节点的RAC环境下的测试情况。
环境：
主机:：2台 M5000 8CPU 4核每CPU 2线程每核 （在solaris上用psrinfo可以看到64个逻辑CPU）
操作系统：solaris10 u8
数据库：Oracle 10.2.0.4 RAC，2节点，安装了最新的PSU
节点1上：
SQL> show parameter parallel
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
fast_start_parallel_rollback string LOW
parallel_adaptive_multi_user boolean TRUE
parallel_automatic_tuning boolean FALSE
parallel_execution_message_size integer 2152
parallel_instance_group string
parallel_max_servers integer 1024
parallel_min_percent integer 0
parallel_min_servers integer 0
parallel_server boolean TRUE
parallel_server_instances integer 2
parallel_threads_per_cpu integer 2
recovery_parallelism integer 0
最大并行server进程为1024，节点2上的设置与节点1相同。
节点1：
SQL> select count(1) from dba_objects where status <> 'VALID';
COUNT(1)
----------
0
数据库中没有无效对象。
节点1：执行utlrp.sql
SQL> @?/rdbms/admin/utlrp.sql
等待一段时间后，在另一个节点上检查gv$session_longops
节点2：
SQL> select * from gv$session_longops;
INST_ID SID SERIAL#
---------- ---------- ----------
OPNAME
------------------------------------------------------------------------------------------------------------------------
TARGET
------------------------------------------------------------------------------------------------------------------------
TARGET_DESC SOFAR TOTALWORK
---------------------------------------------------------------- ---------- ----------
UNITS START_TIME LAST_UPDATE_ TIMESTAMP TIME_REMAINING
---------------------------------------------------------------- ------------ ------------ ------------ --------------
ELAPSED_SECONDS CONTEXT
--------------- ----------
MESSAGE
------------------------------------------------------------------------------------------------------------------------
USERNAME SQL_ADDRESS SQL_HASH_VALUE SQL_ID
------------------------------------------------------------ ---------------- -------------- --------------------------
QCSID
----------
1 1286 6495
Gather Table's Index Statistics
Table UTL_RECOMP_COMPILED 0 0
Indexes 02-SEP-10 02-SEP-10
0 0
Gather Table's Index Statistics: Table UTL_RECOMP_COMPILED : 0 out of 0 Indexes done
SYS 00000003DF9296C0 3169722189 3cmwr02yfw7ud
0
1 1286 6495
Gather Table's Index Statistics
Table UTL_RECOMP_SORTED 0 0
Indexes 02-SEP-10 02-SEP-10
0 0
Gather Table's Index Statistics: Table UTL_RECOMP_SORTED : 0 out of 0 Indexes done
SYS 00000003DF9296C0 3169722189 3cmwr02yfw7ud
0
1 1286 6495
Gather Table's Index Statistics
Table UTL_RECOMP_COMPILED 0 0
Indexes 02-SEP-10 02-SEP-10
0 0
Gather Table's Index Statistics: Table UTL_RECOMP_COMPILED : 0 out of 0 Indexes done
SYS 00000003DF9296C0 3169722189 3cmwr02yfw7ud
0
1 1286 6495
Gather Table's Index Statistics
Table UTL_RECOMP_SORTED 0 0
Indexes 02-SEP-10 02-SEP-10
0 0
Gather Table's Index Statistics: Table UTL_RECOMP_SORTED : 0 out of 0 Indexes done
SYS 00000003DF9296C0 3169722189 3cmwr02yfw7ud
0
1 1286 6495
Gather Table's Index Statistics
Table UTL_RECOMP_COMPILED 0 0
Indexes 02-SEP-10 02-SEP-10
0 0
Gather Table's Index Statistics: Table UTL_RECOMP_COMPILED : 0 out of 0 Indexes done
SYS 00000003DF9296C0 3169722189 3cmwr02yfw7ud
0
1 1286 6495
Gather Table's Index Statistics
Table UTL_RECOMP_SORTED 0 0
Indexes 02-SEP-10 02-SEP-10
0 0
Gather Table's Index Statistics: Table UTL_RECOMP_SORTED : 0 out of 0 Indexes done
SYS 00000003DF9296C0 3169722189 3cmwr02yfw7ud
0
1 1286 6495
Gather Table's Index Statistics
Table UTL_RECOMP_COMPILED 0 0
Indexes 02-SEP-10 02-SEP-10
0 0
Gather Table's Index Statistics: Table UTL_RECOMP_COMPILED : 0 out of 0 Indexes done
SYS 00000003DF9296C0 3169722189 3cmwr02yfw7ud
0
1 1286 6495
Gather Table's Index Statistics
Table UTL_RECOMP_SORTED 0 0
Indexes 02-SEP-10 02-SEP-10
0 0
Gather Table's Index Statistics: Table UTL_RECOMP_SORTED : 0 out of 0 Indexes done
SYS 00000003DF9296C0 3169722189 3cmwr02yfw7ud
0
8 rows selected.
检查session 1286在执行什么sql语句：
节点2：
SQL> select sql_text from gv$session a,gv$sqltext_with_newlines b
2 where DECODE(a.sql_hash_value, 0, prev_hash_value, sql_hash_value)=b.hash_value and a.INST_ID=b.INST_ID and a.sid=&1 order by piece;
Enter value for 1: 1286
old 2: where DECODE(a.sql_hash_value, 0, prev_hash_value, sql_hash_value)=b.hash_value and a.INST_ID=b.INST_ID and a.sid=&1 order by piece
new 2: where DECODE(a.sql_hash_value, 0, prev_hash_value, sql_hash_value)=b.hash_value and a.INST_ID=b.INST_ID and a.sid=1286 order by piece
SQL_TEXT
------------------------------------------------------------------------------------------------------------------------
CREATE INDEX utl_recomp_comp_idx1
ON utl_recomp_compiled(obj#) PARALLEL
该会话在并行创建索引，而检查表utl_recomp_compiled时发现这个表中记录数为0。因此判断utlrp.sql执行时间长可能是由于并行问题引起的。
检查两个节点上的并行进行数：
节点2：
oracle@ofs00mlcprc02:~ $> ps -ef | grep ora_p | grep -v grep | wc -l
260
节点1：
oracle@ofs00mlcprc01:~ $> ps -ef | grep ora_p | grep -v grep | wc -l
260
每个节点上大约有250多个并行进程。
修改并行度后再进行测试：
节点1：
12:44:04 SQL> alter system set parallel_max_servers=4 scope=both sid='*';
System altered.
Elapsed: 00:00:00.10
节点1：
12:44:25 SQL> @?/rdbms/admin/utlrp.sql
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2010-09-02 12:45:24
Elapsed: 00:00:00.01
12:45:24 DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid
12:45:24 DOC> objects in the database. Recompilation time is proportional to the
12:45:24 DOC> number of invalid objects in the database, so this command may take
12:45:24 DOC> a long time to execute on a database with a large number of invalid
12:45:24 DOC> objects.
12:45:24 DOC>
12:45:24 DOC> Use the following queries to track recompilation progress:
12:45:24 DOC>
12:45:24 DOC> 1. Query returning the number of invalid objects remaining. This
12:45:24 DOC> number should decrease with time.
12:45:24 DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
12:45:24 DOC>
12:45:24 DOC> 2. Query returning the number of objects compiled so far. This number
12:45:24 DOC> should increase with time.
12:45:24 DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
12:45:24 DOC>
12:45:24 DOC> This script. automatically chooses serial or parallel recompilation
12:45:24 DOC> based on the number of CPUs available (parameter cpu_count) multiplied
12:45:24 DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu).
12:45:24 DOC> On RAC, this number is added across all RAC nodes.
12:45:24 DOC>
12:45:24 DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
12:45:24 DOC> recompilation. Jobs are created without instance affinity so that they
12:45:24 DOC> can migrate across RAC nodes. Use the following queries to verify
12:45:24 DOC> whether UTL_RECOMP jobs are being created and run correctly:
12:45:24 DOC>
12:45:24 DOC> 1. Query showing jobs created by UTL_RECOMP
12:45:24 DOC> SELECT job_name FROM dba_scheduler_jobs
12:45:24 DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
12:45:24 DOC>
12:45:24 DOC> 2. Query showing UTL_RECOMP jobs that are running
12:45:24 DOC> SELECT job_name FROM dba_scheduler_running_jobs
12:45:24 DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
12:45:24 DOC>#
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.67
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2010-09-02 12:45:25
Elapsed: 00:00:00.00
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
12:45:25 DOC> The following query reports the number of objects that have compiled
12:45:25 DOC> with errors (objects that compile with errors have status set to 3 in
12:45:25 DOC> obj$). If the number is higher than expected, please examine the error
12:45:25 DOC> messages reported with each object (using SHOW ERRORS) to see if they
12:45:25 DOC> point to system misconfiguration or resource constraints that must be
12:45:25 DOC> fixed before attempting to recompile these objects.
12:45:25 DOC>#
OBJECTS WITH ERRORS
-------------------
0
Elapsed: 00:00:00.00
12:45:25 DOC> The following query reports the number of errors caught during
12:45:25 DOC> recompilation. If this number is non-zero, please query the error
12:45:25 DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
12:45:25 DOC> are due to misconfiguration or resource constraints that must be
12:45:25 DOC> fixed before objects can compile successfully.
12:45:25 DOC>#
ERRORS DURING RECOMPILATION
---------------------------
0
Elapsed: 00:00:00.01
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.26
12:45:25 SQL>
不到1分钟就执行完了，比原来执行时间缩短了很多。
utlrp.sql运行时间长的问题
-
cqubityj
2010-09-03 09:42:28 -
Linux操作系统
- 原创