jeudi 28 juin 2012

Health Check


8 times out of Ten, the problem doesn’t come from the DB !!!!!!!!!!!!!!!!!!!!!!!!!!!

Just a word to say that any time there is performance issue, Oracle is the “Penalty-Taker”, but the problem is hardly ever on the Database side ( as long as the DBA works ** ).
A simple look at the session waits gives us the hint that something wrong is going on on another component ( client-side , network , bad programming … ) : most of the time is consumed by the client to server communication :

EVENT
TOTAL_WAITS
SQL*Net message to client
39426
SQL*Net message from client
39425
db file sequential read
9172
latch: shared pool
157
latch: library cache
98
db file scattered read
43


0] Real Time wait event

0.1 V$SESSION_WAIT

select s.program, s.sid, s.serial#, e.event, e.WAIT_TIME, e.SECONDS_IN_WAIT, e.state , e.p1 , e.p2 , e.p3 , e.wait_class , p.pga_alloc_mem from v$session s, v$session_wait e , v$process p
where s.sid=e.sid and s.paddr=p.addr
and e.wait_class != 'Idle' -- idle events : Waits that signify the session is inactive, waiting for work (for example, 'SQL*Net message from client')
order by e.SECONDS_IN_WAIT desc ;

idle wait list
http://docs.oracle.com/cd/B16240_01/doc/doc.102/e16282/oracle_database_help/oracle_database_idle_events.html

col event for a35
col wait_class for a30

select event, wait_class , sum(1) from v$session_wait
group by event,wait_class order by 3 desc ;

0.2 DB Time

select * from V$SYS_TIME_MODEL order by 3 desc

0.3 Top consumers

From V$SQL

select sql_id , plan_hash_value , parsing_schema_name , executions , buffer_gets , disk_reads , first_load_time , rows_processed , elapsed_time , last_load_time , sql_text from v$sql
where buffer_gets > 1000
order by buffer_gets/executions desc


1] Global Health

1.0 Find Instance Availability using SYSMAN schema in Grid Console DB

Get Database Downtime from SYSMAN Repository.

select distinct target_name , start_timestamp , end_timestamp , AVAILABILITY_STATUS from MGMT$AVAILABILITY_HISTORY a
where target_type = 'oracle_database' and AVAILABILITY_STATUS = 'Target Down' and target_name in ('DB1', 'DB2')
and start_timestamp between to_date('26/05/2015 01:00:00', 'DD/MM/YYYY HH24:MI:SS') and to_date('01/06/2015 12:00:00', 'DD/MM/YYYY HH24:MI:SS')
order by 1,2 desc

TARGET_NAME
START_TIMESTAMP
END_TIMESTAMP
AVAILABILITY_STATUS
DB1
01/06/2015 05:31
01/06/2015 05:42
Target Down
DB2
01/06/2015 05:47
01/06/2015 06:02
Target Down

1.1 Identification & Parameters

alter session set nls_sort=binary ;

-- 1/ Configuration

-- 1.1/ ident
select 'A_IDENTIFICATION',null,null from dual union
select 'serveur' , 'host' , host_name from v$instance union
select 'version' , 'oracle kernel version' , version from v$instance union
select name,description,value from v$parameter where name like 'compatible' union
select 'character set' ,' Jeu de caractères' , VALUE$ from sys.PROPS$ where name='NLS_CHARACTERSET' union
select 'log_mode','1',log_mode  from v$database union
select 'volume datafiles' , 'total_GB' , to_char(ceil(sum(bytes)/1024/1024/1024)) from dba_data_files union
select 'volume segments' , 'total_GB' , to_char(ceil(sum(bytes)/1024/1024/1024)) from dba_segments union
select 'volume tempfiles' , 'total_GB' , to_char(ceil(sum(bytes)/1024/1024/1024)) from dba_temp_files
order by 1 ;

-- 1.2/ optimizer
select 'B_OPTIMIZER',null,null from dual union
select name,description,value from v$parameter where name like '%optimizer%' and name not like '!_%' escape '!' order by 1 ;

-- 1.3/ sga
select 'C_MEMORY',null,null from dual union
select name,description,value from v$parameter where
name like '%sga_%' or name like '%pga%' or ( name like '%area%' and name != 'workarea_size_policy' ) or name like 'shared_servers%' union
SELECT 'shared_servers_HWM' , 'at_startup=HIGHWATER' , to_char(SERVERS_HIGHWATER) "HIGHWATER" FROM V$SHARED_SERVER_MONITOR
order by 1

-- 1.4/ blocks & io
select 'D_BLOCKS_IO',null,null from dual union
select name,description,value from v$parameter where
name like '%block%' and name != 'db_block_buffers' or
name like 'filesystem%' or name like '%disk_asyn%'  -- io
order by 1 ;

-- 1.5/ cursors
select 'E_CURSORS',null,null from dual union
select name,description,value from v$parameter where
name like '%cursor_%' and name not like '%space_for%' order by 1 ;

-- 1.x/ divers
select 'F_VARIOUS',null,null from dual union
select name,description,value from v$parameter where
name like '%utl_f%' or              -- utl
--name like '%undo_m%' or           -- undo
name like '%recycleb%' or    -- recycle bin
name in ('log_checkpoint_interval','log_checkpoint_timeout','fast_start_mttr_target') or               -- checkpoint
name like '%parallel_max%' or
name like '%nls%leng%' or name like '%nls%date%format%'
order by 1 ;

-- 1.xx/ hidden parameters
select 'G_HIDDEN',null,null from dual union
select name,description,value from v$parameter where
name like '!_%' escape '!' and isdefault ='FALSE' order by 1 ;


A_IDENTIFICATION
character set
 Jeu de caractères
WE8MSWIN1252
compatible
Database will be completely compatible with this software version
10.2.0.5.0
log_mode
1
ARCHIVELOG
serveur
host
sr-pprod-1
version
oracle kernel version
10.2.0.5.0
volume datafiles
total_GB
504
volume segments
total_GB
395
volume tempfiles
total_GB
15
B_OPTIMIZER
optimizer_dynamic_sampling
optimizer dynamic sampling
2
optimizer_features_enable
optimizer plan compatibility parameter
10.2.0.5
optimizer_index_caching
optimizer percent index caching
0
optimizer_index_cost_adj
optimizer index cost adjustment
100
optimizer_mode
optimizer mode
ALL_ROWS
optimizer_secure_view_merging
optimizer secure view merging and predicate pushdown/movearound
FALSE
C_MEMORY
bitmap_merge_area_size
maximum memory allow for BITMAP MERGE
1048576
create_bitmap_area_size
size of create bitmap buffer for bitmap index
8388608
hash_area_size
size of in-memory hash work area
33554432
pga_aggregate_target
Target size for the aggregate PGA memory consumed by the instance
268435456
sga_max_size
max total SGA size
3221225472
sga_target
Target size of SGA
3221225472
shared_servers
number of shared servers to start up
20
shared_servers_HWM
at_startup=HIGHWATER
20
sort_area_retained_size
size of in-memory sort work area retained between fetch calls
0
sort_area_size
size of in-memory sort work area
16777216
D_BLOCKS_IO
db_block_checking
header checking and data and index block checking
FALSE
db_block_checksum
store checksum in db blocks and check during reads
TRUE
db_block_size
Size of database block in bytes
8192
db_file_multiblock_read_count
db block to be read each IO
119
disk_asynch_io
Use asynch I/O for random access devices
TRUE
filesystemio_options
IO operations on filesystem files
SetAll
E_CURSORS
cursor_sharing
cursor sharing mode
EXACT
open_cursors
max # cursors per session
1100
session_cached_cursors
Number of cursors to cache in a session.
200
F_VARIOUS
fast_start_mttr_target
MTTR target in seconds
300
log_checkpoint_interval
# redo blocks checkpoint threshold
0
log_checkpoint_timeout
Maximum time interval between checkpoints in seconds
0
nls_date_format
NLS Oracle date format

nls_length_semantics
create columns using byte or char semantics by default
BYTE
parallel_max_servers
maximum parallel query servers per instance
160
recyclebin
recyclebin processing
on
utl_file_dir
utl_file accessible directories list
*
G_HIDDEN
_b_tree_bitmap_plans
enable the use of bitmap plans for tables w. only B-tree indexes
FALSE
_optimizer_transitivity_retain
retain equi-join pred upon transitive equality pred generation
FALSE
_projection_pushdown
projection pushdown
FALSE

1.2 Performance

[10G-11G]


-- 10g - 11g
SELECT lower(job_name) Domain , enabled Threshold , to_char(failure_count) Value , to_char(last_start_date) Status
FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME ='GATHER_STATS_JOB'
union
-- system stats, if 1 minute : are NOWORKLOAD Stats not relevant
select lower(sname) , pname , to_char(pval1) , pval2 from sys.aux_stats$ where pval2 is not null
union
-- 2.2/ ratios since instance startup
-- 10g v$pgastat
SELECT 'Dictionary Cache Hit Ratio' "Metric", 'threshold > 90%' "Threshold" , to_char(round((1-(Sum(getmisses)/(Sum(gets)+Sum(getmisses))))*100,2)) "Value" ,
case when round((1-(Sum(getmisses)/(Sum(gets)+Sum(getmisses))))*100,2) > 90 then 'OK' else 'KO' end "Status" FROM v$rowcache union
SELECT 'Library Cache Hit Ratio' , 'threshold > 99%' , to_char(round((1-(Sum(reloads)/(Sum(pins)+Sum(reloads))))*100,2)) "Hit Ratio" ,
case when round((1-(Sum(reloads)/(Sum(pins)+Sum(reloads))))*100,2) > 99 then 'OK' else 'KO' end "Status" FROM v$librarycache union
SELECT 'Buffer Cache Hit Ratio' , 'threshold > 90%' , to_char(round((1-(phys.value/(db.value+cons.value)))*100,2)) "Hit Ratio" ,
case when round((1-(phys.value/(db.value+cons.value)))*100,2) > 90 then 'OK' else 'KO' end "Status"
FROM v$sysstat phys,v$sysstat db,v$sysstat cons WHERE phys.name = 'physical reads' AND db.name = 'db block gets' AND cons.name = 'consistent gets' union
SELECT 'Latch Hit Ratio' , 'threshold > 98%' , to_char(round((1-(Sum(misses)/Sum(gets)))*100,2)) "Hit Ratio" ,
case when round((1-(Sum(misses)/Sum(gets)))*100,2) > 98 then 'OK' else 'KO' end "Status" FROM v$latch union
SELECT 'Disk Sort Ratio' , 'threshold < 05%' , to_char(round((disk.value/mem.value) * 100,2)) "Hit Ratio" ,
case when round((disk.value/mem.value) * 100,2) < 5 then 'OK' else 'KO' end "Status" FROM v$sysstat disk,v$sysstat mem WHERE disk.name = 'sorts (disk)' AND mem.name = 'sorts (memory)' union
SELECT 'Dispatcher Workload' , 'threshold < 50%' , to_char(round(NVL((Sum(busy)/(Sum(busy)+Sum(idle)))*100,0),2)) "% time busy" ,
case when round(NVL((Sum(busy)/(Sum(busy)+Sum(idle)))*100,0),2) < 50 then 'OK' else 'KO' end "Status" FROM v$dispatcher union
SELECT 'Shared Servers Workload' , 'threshold < 0,01 1/100sec' , DECODE(TOTALQ,0,'No Requests',round(WAIT/TOTALQ),5) "AVERAGE WAIT TIME (1/100 Sec)" , 
case when DECODE(TOTALQ,0,'No Requests',round(WAIT/TOTALQ),5) != 'No Requests' then
case when DECODE(TOTALQ,0,'No Requests',round(WAIT/TOTALQ),5) < 0.01 then 'OK' else 'KO' end
end "Status" FROM V$QUEUE WHERE TYPE = 'COMMON' union
SELECT 'PGA Hit Ratio' , 'threshold > 95%' , to_char(value) "Hit Ratio" ,
case when value > 95 then 'OK' else 'KO' end "Status" FROM v$pgastat where name = 'cache hit percentage'
order by 1 ;


Domain
Threshold / Value
Status
Buffer Cache Hit Ratio
threshold > 90%
99,17
OK
Dictionary Cache Hit Ratio
threshold > 90%
98,86
OK
Disk Sort Ratio
threshold < 05%
0
OK
Dispatcher Workload
threshold < 50%
0,07
OK
Latch Hit Ratio
threshold > 98%
99,99
OK
Library Cache Hit Ratio
threshold > 99%
99,97
OK
PGA Hit Ratio
threshold > 99%
85,58
OK
Shared Servers Workload
threshold < 0,01 1/100sec
0,00404
OK
gather_stats_job
TRUE
0
05/07/13 06:00:03,282408 +02:00
sysstats_info
DSTART

03-22-2010 15:18
sysstats_info
DSTOP

03-22-2010 15:18
sysstats_info
STATUS

COMPLETED

Note : when having a low hit-ratio,

-- zoom out in AWR to see where are the low BHR periods :
select to_char(end_interval_time,'yyyy-mm-dd HH24') mydate, new.name buffer_pool_name,
(((new.consistent_gets-old.consistent_gets)+(new.db_block_gets-old.db_block_gets))-(new.physical_reads-old.physical_reads))/((new.consistent_gets-old.consistent_gets)+(new.db_block_gets-old.db_block_gets))    bhr
from dba_hist_buffer_pool_stat old,dba_hist_buffer_pool_stat new,dba_hist_snapshot sn
where (((new.consistent_gets-old.consistent_gets)+(new.db_block_gets-old.db_block_gets))-(new.physical_reads-old.physical_reads))/((new.consistent_gets-old.consistent_gets)+(new.db_block_gets-old.db_block_gets)) < .90
and new.name = old.name and new.snap_id = sn.snap_id and old.snap_id = sn.snap_id-1
order by 1 desc

About Memory, look out for the DB_CACHE_ADVICE :
set pages 5000 lines 150
select name,block_size,size_for_estimate,estd_physical_reads,estd_physical_read_factor from v$db_cache_advice ;
NAME                 BLOCK_SIZE SIZE_FOR_ESTIMATE ESTD_PHYSICAL_READS ESTD_PHYSICAL_READ_FACTOR
-------------------- ---------- ----------------- ------------------- -------------------------
...
DEFAULT                    8192              4480           760563602                    1.7061
DEFAULT                    8192              5376           665365253                    1.4926
DEFAULT                    8192              6272           599198372                    1.3441
DEFAULT                    8192              7168           551192422                    1.2365
DEFAULT                    8192              8064           509920177                    1.1439
DEFAULT                    8192              8960           472893791                    1.0608
DEFAULT                    8192              9728           445785579                         1
DEFAULT                    8192              9856           440867668                      .989
DEFAULT                    8192             10752           411798823                     .9238
DEFAULT                    8192             11648           383724763                     .8608
DEFAULT                    8192             12544           357395247                     .8017
DEFAULT                    8192             13440           333332491                     .7477
...

It is based according to the real DB Cache Allocation, in our example 9600 MB.
To have the big picture, one can query SGA Advisor :

SQL> select * from v$sga_target_advice order by sga_size;

  SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR ESTD_PHYSICAL_READS ESTD_BUFFER_CACHE_SIZE ESTD_SHARED_POOL_SIZE     CON_ID
---------- --------------- ------------ ------------------- ------------------- ---------------------- --------------------- ----------
     17408              .5     59115454             41.4822          3527857756                    896                 12800          0
     21760            .625     31222933             21.9096          3527857756                    896                 17152          0
     26112             .75      4069031              2.8553          3527857756                    896                 21888          0
     30464            .875      1619888              1.1367           672788067                   5376                 22016          0
     34816               1      1425080                   1           445791192                   9856                 22144          0
     39168           1.125      1314209               .9222           316779221                  14336                 22272          0
     43520            1.25      1301098                .913           282854511                  17024                 23680          0
     47872           1.375      1296965               .9101           282854511                  17024                 28032          0
     52224             1.5      1293403               .9076           282854511                  17024                 32384          0
...

2] Shared Server Monitoring

Shared Servers ( formerly MTS ) is a very smart way of saving DB Server’s Physical Memory for : Multiple & Inactive sessions.

2.1 Dispatchers

Goal
Monitor Dispatchers Performance
Version
10gR2
Views
V$DISPATCHER V$QUEUE
Log as
SYSTEM


-- Dispatchers % utilization and Number of clients

SELECT NAME "NAME", SUBSTR(NETWORK,1,23) "PROTOCOL", OWNED,
STATUS "STATUS", To_Char(busy*100/(busy+idle),'0D99') "%TIME BUSY"
FROM V$DISPATCHER;

-- Average Response Time in the " Response Queues " ( 1/100 Sec )

SELECT To_Char( decode (sum(totalq),0,'No responses',sum(wait)/sum(totalq)), '0D9999') "Average Wait Time (1/100 Sec)"
FROM V$QUEUE q, V$DISPATCHER d WHERE q.type = 'DISPATCHER' AND q.paddr = d.paddr ;


DISPATCHERS
NAME
PROTOCOL
OWNED
STATUS
%TIME BUSY
D000
(ADDRESS=(PROTOCOL=tcp)
10
WAIT
0,02
D001
(ADDRESS=(PROTOCOL=tcp)
13
WAIT
0

Dispatchers: Average Response Time in the " Response Queues " ( 1/100 Sec )
0,0045


2.2 Shared Servers

Goal
Monitor Shared Servers Performance
Version
10gR2
Views
V$SHARED_SERVER V$QUEUE V$SHARED_SERVER_MONITOR
Log as
SYSTEM


-- Average Waiting time in the " Request Queue " ( 1/100 Sec )

SELECT To_Char( DECODE(TOTALQ, 0, 'No Requests',WAIT/TOTALQ), '0D9999') "AVERAGE WAIT TIME (1/100 Sec)"
  FROM V$QUEUE
 WHERE TYPE = 'COMMON';

-- Current Number of Shared Servers

SELECT COUNT(*) "Shared Server Processes" FROM V$SHARED_SERVER WHERE STATUS != 'QUIT';


Shared Servers: Average Waiting time in the " Request Queue " ( 1/100 Sec )
Current Number of Shared Servers
1,5692
6

To avoid the overhead penalty of Create/Drop Shared Servers Management, the number of Servers at Startup should be set to be =  1 + HIGHWATER :


-- Shared Servers behaviour

SELECT MAXIMUM_CONNECTIONS "MAX CONN", MAXIMUM_SESSIONS "MAX SESS",
SERVERS_STARTED "STARTED", SERVERS_TERMINATED "TERMINATED",SERVERS_HIGHWATER "HIGHWATER"
FROM V$SHARED_SERVER_MONITOR ;


MAX CONN
MAX SESS
STARTED
TERMINATED
HIGHWATER
27
27
574
570
8

This query gives the number of Requests per Shared Server ; if this is unbalance, it may appear that one program monopilizes one Shared Server and should deserve a Dedicated connexion.


-- % utilisation des Shared Servers et nombre de requêtes traitées

SELECT NAME,requests,To_Char(busy*100/(busy+idle),'09D99') "Busy %",status 
FROM v$shared_server WHERE status != 'QUIT'
ORDER BY 3 desc;


NAME
REQUESTS
Busy %
STATUS
S001
304
97,46
EXEC
S004
374
68,24
WAIT(RECEIVE)
S000
167480
33,68
WAIT(RECEIVE)
S006
1
30,09
TERMINATED
S003
3304
3,06
WAIT(RECEIVE)
S005
137
0,38
WAIT(COMMON)
S002
838
0,38
EXEC


2.3 Healthy System

The above results show a badly tuned System. Let’s adjust the number of Shared :


SQL> sho parameter shared_s

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
max_shared_servers                   integer     8
shared_server_sessions               integer
shared_servers                       integer     4

SQL> alter system set max_shared_servers = 16 ;
System altered.

SQL> alter system set shared_servers = 12 ;
System altered.


NAME
PROTOCOL
OWNED
STATUS
%TIME BUSY
D000
(ADDRESS=(PROTOCOL=tcp)
6
WAIT
0,01
D001
(ADDRESS=(PROTOCOL=tcp)
9
WAIT
0

Dispatchers: Average Response Time in the " Response Queues " ( 1/100 Sec )
0,0051

Shared Servers: Average Waiting time in the " Request Queue " ( 1/100 Sec )
Current Number of Shared Servers
0,0051
12

MAX CONN
MAX SESS
STARTED
TERMINATED
HIGHWATER
28
30
0
0
12

NAME
REQUESTS
Busy %
STATUS
S002
852521
48,09
WAIT(RECEIVE)
S000
846232
33,91
EXEC
S001
311613
24,19
WAIT(RECEIVE)
S003
143388
5,64
EXEC
S005
42334
2,31
WAIT(COMMON)
S006
2968
0,05
WAIT(COMMON)
S004
9511
0,02
WAIT(COMMON)
S011
29
0
WAIT(COMMON)
S007
422
0
WAIT(COMMON)
S008
192
0
WAIT(COMMON)
S009
179
0
WAIT(COMMON)
S010
0
0
WAIT(COMMON)


2.4 Sessions


-- number of Dedicated connexions or Shared S ( None or Shared )
select server,count(1) "Connexions"  from v$session group by server ;



-- number of Dedicated connexions per application
select schemaname,count(1) "Connexions Dedicated" from v$session where server = 'DEDICATED' group by schemaname order by 2 desc ;



-- number of Shared connexions per application
select schemaname,count(1) "Connexions Shared Servers" from v$session where server != 'DEDICATED' group by schemaname order by 2 desc ;



-- total number of Shared Servers connexions
select count(1) from v$session where server != 'DEDICATED' ;



3] Index

3.1 Redundant Index : Searchin’, Seek and Destroy

Because “INDEX02 on Table T ( COL1,COL2 )” is included in “INDEX01 on Table T ( COL1,COL2,COL3 )”, we may remove that redundant Index INDEX02.

   and tab_owner.name  ='SCH1'

-- index redundant & type

select a.owner , a.table_name , a.included_index_name_to_drop , a.including_index_name , b.index_type
from
(
SELECT
   /* RULE */
   tab_owner.name owner, t.name table_name,
   o1.name || '(' || DECODE(bitand(i1.property, 1), 0, 'N', 1, 'U', '*') || ')' included_index_name_to_drop ,
   o2.name || '(' || DECODE(bitand(i2.property, 1), 0, 'N', 1, 'U', '*') || ')' including_index_name
FROM  sys.USER$ tab_owner, sys.OBJ$ t, sys.IND$ i1, sys.OBJ$ o1, sys.IND$ i2, sys.OBJ$ o2
WHERE i1.bo# = i2.bo# AND i1.obj# <> i2.obj# AND i2.cols >= i1.cols AND i1.cols > 0 AND
   i1.cols = ( SELECT /*+ ORDERED */ COUNT(1) FROM sys.ICOL$ cc1, sys.icol$ cc2
               WHERE cc2.obj# = i2.obj# AND cc1.obj# = i1.obj# AND
                     cc2.pos# = cc1.pos# AND cc2.COL# = cc1.COL#) AND
   i1.obj# = o1.obj# AND i2.obj# = o2.obj# AND t.obj# = i1.bo# AND
   t.owner# = tab_owner.USER# AND tab_owner.name LIKE '%'
   and tab_owner.name  ='SCH1'
) a , dba_indexes b
where a.owner = b.owner and replace(replace(a.included_index_name_to_drop,'(N)',''),'(U)','') = b.index_name
ORDER BY 1, 2 ;

OWNER
TABLE_NAME
INCLUDED_INDEX_NAME
INCLUDING_INDEX_NAME
INDEX_TYPE
05/02/2013

TO DROP


SCH1
CO
GLCO(N)
LIGNE(N)
NORMAL
SCH1
CRD
I_CRD_IND1(N)
I_CRD_CRDSERIAL(N)
NORMAL
SCH1
CRDIZONEFLD
CRDIZONECRDIZONEFLD(N)
I_CRDIZONEFLD(N)
NORMAL
SCH1
CRDIZONEFLD
CRDIZONECRDIZONEFLD(N)
PK_CRDIZONEFLD(U)
NORMAL
SCH1
DELTAHORAIRE
REFCO(N)
CLE15(N)
NORMAL
SCH1
DROITPERIODE
REFDROIT(N)
CLE1(N)
NORMAL
SCH1
GLSOCIETE
REFGL(N)
CLE2(N)
NORMAL
SCH1
GR
MEGR(N)
CLE3(N)
NORMAL
SCH1
GRLI
REFRAD(N)
CLE4(N)
NORMAL
SCH1
IRST
RSTIRST(N)
HEUREDEB(N)
NORMAL
SCH1
LISTEMESSAGE
REFTICKET(N)
TICKETPOSITION(N)
NORMAL
SCH1
MGDROIT
REFMG(N)
CLE6(N)
NORMAL
SCH1
MX
CPMX(N)
CLE8(N)
NORMAL

How many Index are included in one or more other index.

-- index redundant per schema
select owner , count(1) from (
SELECT
   /*+ RULE */
   tab_owner.name owner, t.name table_name,
   o1.name || '(' || DECODE(bitand(i1.property, 1), 0, 'N', 1, 'U', '*') || ')' included_index_name ,
   o2.name || '(' || DECODE(bitand(i2.property, 1), 0, 'N', 1, 'U', '*') || ')' including_index_name
FROM  sys.USER$ tab_owner, sys.OBJ$ t, sys.IND$ i1, sys.OBJ$ o1, sys.IND$ i2, sys.OBJ$ o2
WHERE i1.bo# = i2.bo# AND i1.obj# <> i2.obj# AND i2.cols >= i1.cols AND i1.cols > 0 AND
   i1.cols = ( SELECT /*+ ORDERED */ COUNT(1) FROM sys.ICOL$ cc1, sys.icol$ cc2
               WHERE cc2.obj# = i2.obj# AND cc1.obj# = i1.obj# AND
                     cc2.pos# = cc1.pos# AND cc2.COL# = cc1.COL#) AND
   i1.obj# = o1.obj# AND i2.obj# = o2.obj# AND t.obj# = i1.bo# AND
   t.owner# = tab_owner.USER# AND tab_owner.name LIKE '%'
ORDER BY 1, 2
) group by owner order by 2 desc

OWNER
COUNT(1)
04/02/2013
DUP IDX
SCH1
336
SCH2
91
SCH3
89
SCH4
70
SCH5
63
SCH6
63
SCH7
61
SCH8
57


-- drop only Normal index, other types: I'm affraid to touch & Not Unique ( they are mostly PKs )
select a.owner , a.table_name , a.included_index_name_to_drop , a.including_index_name
, b.index_type , 'drop index "'||a.owner||'"."'||replace(a.included_index_name_to_drop,'(N)','')||'" ; '
from
(
SELECT
   /* RULE */
   tab_owner.name owner, t.name table_name,
   o1.name || '(' || DECODE(bitand(i1.property, 1), 0, 'N', 1, 'U', '*') || ')' included_index_name_to_drop ,
   o2.name || '(' || DECODE(bitand(i2.property, 1), 0, 'N', 1, 'U', '*') || ')' including_index_name
FROM  sys.USER$ tab_owner, sys.OBJ$ t, sys.IND$ i1, sys.OBJ$ o1, sys.IND$ i2, sys.OBJ$ o2
WHERE i1.bo# = i2.bo# AND i1.obj# <> i2.obj# AND i2.cols >= i1.cols AND i1.cols > 0 AND
   i1.cols = ( SELECT /*+ ORDERED */ COUNT(1) FROM sys.ICOL$ cc1, sys.icol$ cc2
               WHERE cc2.obj# = i2.obj# AND cc1.obj# = i1.obj# AND
                     cc2.pos# = cc1.pos# AND cc2.COL# = cc1.COL#) AND
   i1.obj# = o1.obj# AND i2.obj# = o2.obj# AND t.obj# = i1.bo# AND
   t.owner# = tab_owner.USER# AND tab_owner.name LIKE '%'
   -- and tab_owner.name  ='SCH1'
) a , dba_indexes b
where a.owner = b.owner and replace(replace(a.included_index_name_to_drop,'(N)',''),'(U)','') = b.index_name
and index_type = 'NORMAL' and instr(included_index_name_to_drop,'(U)') = 0
ORDER BY 1, 2 ;


drop index "APEX_040000"."WWV_FLOW_ADV_CHK_MSG_CHK_IDX1" ;
drop index "APEX_040000"."WWV_FLOW_BUGS_IDX1" ;
…

Some index may appear more than once, because a “Distinct”clause adds significant elapse time, so I live with it.


4] Performance Throughput

3.1 Oracle Redo Log Write Throughput

3.1.1 In a current 5 minutes Period

-- v$mystat This view records statistical data about the session that accesses it. Join statistic# with v$statname.
-- v$sesstat is also similar to v$sysstat, except that v$sysstat accumulates the statistics as soon as a session terminates.
-- v$sysstat is similar to v$sesstat. While v$sesstat displays statitics for the current session, v$sysstat displays the cumulated statitics since startup of the database.

set serveroutput on

declare

STA_TI date ;
STO_TI date ;
STA_SUM integer ;
STO_SUM integer ;
TOT_TI number ;
TOT_SUM integer ;

begin

select ceil(VALUE*0.5/1024) RDO_MB , sysdate into STA_SUM , STA_TI from v$sysstat where statistic# = ( SELECT statistic#  FROM v$statname  where name = 'redo blocks written' ) ;
sys.dbms_lock.sleep(300);
select ceil(VALUE*0.5/1024) RDO_MB , sysdate into STO_SUM , STO_TI from v$sysstat where statistic# = ( SELECT statistic#  FROM v$statname  where name = 'redo blocks written' ) ;

select ROUND(1440*60 * (STO_TI - STA_TI),2)  into TOT_TI from dual ;
select STO_SUM-STA_SUM into TOT_SUM from dual ;

dbms_output.put_line( 'Total Time : ' ||(TOT_TI) ) ;
dbms_output.put_line( 'Number of RDO MB Written : ' ||(TOT_SUM) ) ;
dbms_output.put_line( 'RDO MB Written Per SEC : ' ||ceil((TOT_SUM/TOT_TI)) ) ;

end;
/

Total Time : 300
Number of RDO MB Written : 89943
RDO MB Written Per SEC : 300

3.1.2 From AWR Period

-- AWR Metric Redo Block Written Per Snap
-- -> Rdo Volume Per Sec. Average of 30 Minutes Period
select BEGIN_INTERVAL_TIME , END_INTERVAL_TIME , ceil((RDO_KB_SEC_CUR-RDO_KB_SEC_preceding)/1024) RDO_MB_SEC from (
select BEGIN_INTERVAL_TIME , END_INTERVAL_TIME , RDO_KB_SEC_CUR ,
ceil(lag(RDO_KB_SEC_CUR, 1, -1) over(partition by INST order by begin_interval_time)) RDO_KB_SEC_preceding
from (
select 'INSTANCE' INST , BEGIN_INTERVAL_TIME , END_INTERVAL_TIME , ceil(VALUE*0.5/(30*60)) RDO_KB_SEC_CUR
from DBA_HIST_SYSSTAT a , DBA_HIST_SNAPSHOT b
where STAT_NAME = 'redo blocks written' and a.SNAP_ID=b.SNAP_ID
AND begin_interval_time >= TO_DATE('01/05/2016 00:00:00','DD/MM/YYYY HH24:MI:SS')
AND begin_interval_time <  TO_DATE('22/05/2016 10:00:00','DD/MM/YYYY HH24:MI:SS')
order by 2
))
order by 3 desc

3.2 Get Hammer-DB TPC-C Transaction Per Second Result

alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';

select sysdate , count(1) NUM_TPCC_USR from V$SESSION where username = 'TPCC' ;

select sysdate , SUM(TX_PER_SEC) TX_PER_SEC_AVG from (
WITH SN1 AS ( SELECT statistic#  FROM v$statname  WHERE NAME = 'user commits' ),
SN2 AS ( SELECT statistic#  FROM v$statname  WHERE NAME = 'user rollbacks' ),
SL1 AS ( select SID , ROUND(( SYSDATE - logon_time) * 24*60*60, 1) SEC_LOG from V$SESSION where username = 'TPCC' )
SELECT a.SID , a.VALUE COMMI , b.VALUE RBACK , SL1.SEC_LOG , ceil((a.VALUE+b.VALUE)/SL1.SEC_LOG) TX_PER_SEC FROM v$sesstat a , v$sesstat b , SN1 , SN2 , SL1
WHERE a.statistic# = SN1.statistic# and b.statistic# = SN2.statistic#
and a.sid = SL1.SID and b.sid = SL1.SID
ORDER BY A.SID DESC ) ;

SYSDATE              TX_PER_SEC_AVG
-------------------- --------------
23-MAY-2016 12:56:44           1565

3.3 Get Hammer-DB TPC-H Query Per Minute Result

set serveroutput on

declare

STA_TI date ;
STO_TI date ;
STA_SUM integer ;
STO_SUM integer ;
TOT_TI number ;
TOT_SUM integer ;

begin

select sum(executions) , sysdate into STA_SUM , STA_TI from v$sql a where command_type = 3 and parsing_user_id in ( select USER_ID from DBA_USERS where username = 'TPCH' );
sys.dbms_lock.sleep(300);
select sum(executions) , sysdate into STO_SUM , STO_TI from v$sql a where command_type = 3 and parsing_user_id in ( select USER_ID from DBA_USERS where username = 'TPCH' );

select ROUND(1440*60 * (STO_TI - STA_TI),2)  into TOT_TI from dual ;
select STO_SUM-STA_SUM into TOT_SUM from dual ;

dbms_output.put_line( 'Total Time : ' ||(TOT_TI) ) ;
dbms_output.put_line( 'Number of Executions : ' ||(TOT_SUM) ) ;
dbms_output.put_line( 'QUERIES PER MIN : ' ||ceil((TOT_SUM/TOT_TI*60)) ) ;


end;
/

Total Time : 240
Number of Executions : 435
QUERIES PER MIN : 109

3.4 Oracle find out maximum number of transaction per second from AWR

-- TXNCOUNT Identifies the total number of transactions executed within the period
-- per 10 minutes period from AWR
-- Find MAX TPS from AWR
select BEGIN_TIME , END_TIME ,
ceil(TXNCOUNT/10/60) TPS
from dba_hist_undostat
where to_char(BEGIN_TIME,'DD/MM/YYYY HH24:MI:SS') between '22/05/2016 00:00:00' and '22/05/2016 01:00:00'
order by 3 desc ;

BEGIN_TIME          END_TIME                  TPS
22/05/2016 00:32:27 22/05/2016 00:42:27        4094
22/05/2016 00:42:27 22/05/2016 00:52:27        888


Publié par orACsd à 08:58
Libellés :