ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle Performance Tuing: BITMAP CONVERSION TO ROWID

Oracle Performance Tuing: BITMAP CONVERSION TO ROWID

原创 Linux操作系统 作者:licheng79 时间:2012-02-09 17:43:21 0 删除 编辑

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

 

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

 

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')
   )
  )
)

SQL>alter session set "_b_tree_bitmap_plans"=false

 http://www.bga.org/~lessem/psyc5112/usail/man/solaris/vmstat.1.html

http://blog.csdn.net/jackpk/article/details/3788445

http://www.dba-oracle.com/t_bitmap_conversion_to_rowid.htm

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26136400/viewspace-715976/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-09-07

  • 博文量
    54
  • 访问量
    67281