Select distinct
P. pid unix_process,
S.t erminal,
To_char (s.l ogon_time, 'YYYY/MON/DD HH24: MI) Logon_Time,
S.u sername
The from v $process p, v $session s
Where p.a DDR=supachai panitchpakdi addr order by 2
Commonly used to monitor the SQL
1. Waiting for the cases of monitoring:
Select the event, the sum (decode (wait_time, 0, 1)) prev, sum (decode (wait_time, 0, 0)) curr, count (*)
The from v $session_wait
Group by the event order by 4;
2. The rollback segments contention:
Select the name that waits, gets, waits/gets thewire from v $rollstat a, v $rollname b where a.u sn=b.u sn;
3. Monitor table space proportion of I/O:
The select df. Tablespace_name name, df file_name "file", f.p hyrds pyr, f.p hyblkrd PBR, f.p hywrts pyw,
F.p hyblkwrt PBW
The from v $filestat f, dba_data_files df
Where f.f ile#=df. File_id
4. To monitor file system I/O ratio:
Select substr (a.f ile#, 1, 2) "#", substr (a.n ame, 1, 30) "name", a.s tatus, a. ytes,
P. hyrds, p. hywrts
The from v $datafile a, v $filestat b
Where a.f ile#=b. ile#
5. Under a user to find all the indexes:
The select user_indexes table_name, user_indexes index_name, uniqueness, column_name
The from user_ind_columns, user_indexes
Where user_ind_columns. Index_name=user_indexes. Index_name
And user_ind_columns. Table_name=user_indexes. Table_name
The order by user_indexes table_type, user_indexes table_name,
User_indexes index_name, column_position;
6. Monitor the SGA shooting
The select Dr. Alue + b.v alue logical_reads, c. alue "phys_reads,"
Round (100 * ((Dr. Alue + b.v alue) - c. alue)/(Dr. Alue + b.v alue)) "BUFFER HIT thewire"
The from v $sysstat a, v $sysstat b and v $sysstat c
Where a.s tatistic#=38 and b.s tatistic#=39
And c.s. tatistic#=40;
7. Monitor in the SGA dictionary buffer hit ratio
Select the parameter that gets Getmisses, Getmisses/(gets + Getmisses) * 100 "miss thewire",
(1 - (sum (getmisses)/(sum (gets) + sum (getmisses)))) * 100 "Hit thewire"
The from v $rowcache
Where the gets + getmisses & lt;> 0
Group by the parameter, gets getmisses;
8. Monitoring of Shared cache the SGA, shooting should be less than 1%
Select sum (pins) Total "pins", the sum (reloads) "Total reloads",
The sum (reloads)/sum (pins) * 100 libcache
The from v $librarycache;
Select sum (pinhits - reloads)/sum (pins) "hit radio", the sum (reloads)/sum (pins) "reload percent"
The from v $librarycache;
9. According to the category and size of all database objects
Select count (name) num_instances, type, the sum (source_size) source_size,
The sum (parsed_size parsed_size), sum (code_size) code_size, sum (error_size) error_size,
The sum (source_size) + sum (parsed_size) + sum (code_size) + sum (error_size) size_required
The from dba_object_size
Group by type order by 2;
10. Monitor the hit ratio of redo log buffer area, in the SGA should be less than 1%
SELECT the name, gets, misses, immediate_gets, immediate_misses,
Decode (gets, 0, 0, misses/gets * 100) ratio1,
Decode (immediate_gets + immediate_misses, 0, 0,
Immediate_misses/(immediate_gets + immediate_misses) * 100) ratio2
The FROM v $latch the WHERE the name (IN 'redo allocation', 'redo copy');
CodePudding user response:
B: well, record the blog,CodePudding user response:
B: well, record the blog,CodePudding user response:
To write good,,,,,,,,