Oracle Performance Tuing: BITMAP CONVERSION TO ROWID

最新推荐文章于 2025-07-28 07:30:00 发布

转载最新推荐文章于 2025-07-28 07:30:00 发布 · 168 阅读

本文展示了TRSPRD系统的性能分析报告，包括系统配置、内存大小、处理器状态等详细信息，并通过SQL查询展示了特定时间段内数据库的运行情况及部分业务数据统计。

TRSPRD performance analysis

$ uname -a

SunOS ctmtrs 5.10 Generic_144488-09 sun4u sparc SUNW,Sun-Fire-V445

$ prtconf | grep Memory

Memory size: 8192 Megabytes

$ cd /usr/sbin

$ psrinfo -v

Status of virtual processor 0 as of: 02/09/2012 12:23:57

on-line since 11/03/2011 21:52:55.

The sparcv9 processor operates at 1592 MHz,

and has a sparcv9 floating point processor.

Status of virtual processor 1 as of: 02/09/2012 12:23:57

on-line since 11/03/2011 21:51:29.

The sparcv9 processor operates at 1592 MHz,

and has a sparcv9 floating point processor.

$ prtdiag

System Configuration: Sun Microsystems sun4u Sun Fire V445

System clock frequency: 199 MHZ

Memory size: 8GB

==================================== CPUs ====================================

E$ CPU CPU

CPU Freq Size Implementation Mask Status Location

--- -------- ---------- --------------------- ----- ------ --------

0 1592 MHz 1MB SUNW,UltraSPARC-IIIi 3.4 on-line MB/C0/P0

1 1592 MHz 1MB SUNW,UltraSPARC-IIIi 3.4 on-line MB/C1/P0

$ vmstat 1 5

kthr memory page disk faults cpu

r b w swap free re mf pi po fr de sr m0 m1 m3 m4 in sy cs us sy id

5 6 0 13829344 2608576 3430 351 39308 40 40 0 0 2 0 191 2 3893 9821 6415 56 27 17

3 13 0 12762432 1184440 422 198 10799 0 0 0 0 0 0 271 0 6278 13360 10666 64 36 0

4 14 0 12762432 1182896 159 9 9082 0 0 0 0 0 0 327 0 4684 13576 7638 67 33 0

5 16 0 12762368 1186976 152 5 6392 0 0 0 0 0 0 333 0 5149 9465 7301 59 41 0

5 11 0 12762368 1188696 285 8 11325 0 0 0 0 0 0 357 0 6712 13877 11214 65 35 0

$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Feb 9 14:32:49 2012

SQL> conn /as sysdba

Connected.

SQL> show SGA

Total System Global Area 1577058304 bytes

Fixed Size 1978944 bytes

Variable Size 268438976 bytes

Database Buffers 1291845632 bytes

Redo Buffers 14794752 bytes

SQL> archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /u10/oradata/TRSPRD/archive

Oldest online log sequence 98862

Next log sequence to archive 98865

Current log sequence 98865

SQL>

SQL Tuning Analysis

(
SELECT
to_date(to_char(( ISC.START_TIME ),'dd/MM/YYYY')),
to_char(( ISC.START_TIME ),'HH24:MI:SS'),
to_date(to_char(( ISC.END_TIME ),'dd/MM/YYYY')),
to_char(( ISC.END_TIME ),'HH24:MI:SS'),
ISC.CHG_DURATION,
ISC.A_SUBSCRIBER,
ISC.ORG_CLI,
ISC.B_SUBSCRIBER,
ISC.ORG_DNR,
ISC.CDR_TYPE,
ISC.RECORD_TYPE,
ISC.INCOMING_ROUTE,
ISC.OUTGOING_ROUTE,
ISC.FILE_NAME,
'' Rec_Type,
'' SELECTED_CODEC,
'' CALLING_SUBR_IMSI,
'' CALLING_SUBR_IMEI,
'' CALLED_SUBR_IMSI,
'' CALLED_SUBR_IMEI,
'' FIRST_CALLING_LOC,
'' LAST_CALLING_LOC,
'' FIRST_CALLED_LOC,
'' LAST_CALLED_LOC,
'' REDIRECT_NUM,
'' REDIRECT_IMSI,
'' MOBILE_STATION_ROAM_NUM,
'' CDR_TYPE,
ISC.ORIGIN_CHG,
ISC.TARIFF_CLASS
FROM
ISC
WHERE
(
   ISC.START_TIME BETWEEN '01-02-2012 00:00:00' AND '08-02-2012 23:59:59'
   AND
   (
    ISC.A_SUBSCRIBER In ('85366752312','66752312')
    OR
    ISC.B_SUBSCRIBER In ('02466752312','0400085366752312','040192090085366752312','04066752312','04166752312','04366752312','04466752312','06166752312','06566752312','06666752312','07066752312','07166752312','07266752312','10004166752312','10006166752312','10066752312','1307266752312','1310004366752312','18666752312','192090085366752312','266752312','66752312','852066752312','85285366752312','8530888866752312','85360066752312','853660066752312','85366752312','85389766752312','85392066752312','92566752312','92666752312','92766752312','92866752312')
   )
)
UNION
SELECT
to_date(to_char(( VW_MSC.START_TIME ),'dd/MM/YYYY')),
to_char(( VW_MSC.START_TIME ),'HH24:MI:SS'),
to_date(to_char(( VW_MSC.END_TIME ),'dd/MM/YYYY')),
to_char(( VW_MSC.END_TIME ),'HH24:MI:SS'),
VW_MSC.CHG_DURATION,
VW_MSC.A_SUBSCRIBER,
VW_MSC.ORG_CLI,
VW_MSC.B_SUBSCRIBER,
VW_MSC.ORG_DNR,
VW_MSC.CDR_TYPE,
VW_MSC.RECORD_TYPE,
VW_MSC.INCOMING_ROUTE,
VW_MSC.OUTGOING_ROUTE,
VW_MSC.FILE_NAME,
decode(VW_MSC.RECORD_TYPE,'0','TRANSIT','1','MO','2','RCF','3','CF','4','MT','5','SMSMO','6','SMSIWMS','7','SMSMT','8','SMSGMS'),
VW_MSC.SELECTED_CODEC,
VW_MSC.CALLING_SUBR_IMSI,
VW_MSC.CALLING_SUBR_IMEI,
VW_MSC.CALLED_SUBR_IMSI,
VW_MSC.CALLED_SUBR_IMEI,
VW_MSC.FIRST_CALLING_LOC,
VW_MSC.LAST_CALLING_LOC,
VW_MSC.FIRST_CALLED_LOC,
VW_MSC.LAST_CALLED_LOC,
VW_MSC.REDIRECT_NUM,
VW_MSC.REDIRECT_IMSI,
VW_MSC.MOBILE_STATION_ROAM_NUM,
decode(VW_MSC.CDR_TYPE,'2','2G','5','3G'),
VW_MSC.ORIGIN_CHG,
VW_MSC.TARIFF_CLASS
FROM
VW_MSC
WHERE
(
   VW_MSC.START_TIME BETWEEN '01-02-2012 00:00:00' AND '08-02-2012 23:59:59'
   AND
   (
    VW_MSC.A_SUBSCRIBER In ('85366752312','66752312')
    OR
    VW_MSC.B_SUBSCRIBER In ('02466752312','0400085366752312','040192090085366752312','04066752312','04166752312','04366752312','04466752312','06166752312','06566752312','06666752312','07066752312','07166752312','07266752312','10004166752312','10006166752312','10066752312','1307266752312','1310004366752312','18666752312','192090085366752312','266752312','66752312','852066752312','85285366752312','8530888866752312','85360066752312','853660066752312','85366752312','85389766752312','85392066752312','92566752312','92666752312','92766752312','92866752312')
    OR
    VW_MSC.REDIRECT_NUM In ('1466752312','1E66752312','1185366752312','1385366752312')
   )
)
)