一次现场oracle 9i 性能调优

第一步
   对数据库整体做了一个了解
$ORACLE_HOME/bin/sqlplus "sys/sys061201 as sysdba" << EOF
spool peiyz_healthcheck_info.txt
set linesize 200
set pagesize 1000
set time on
set timing on
col owner for a10
col segment_name for a30
col index_name for a30
col table_name for a30
col colum_name for a30
col name for a60
col member for a50
col file_name for a50
col tablespace_name for a30
col ftime for a20
col username for a20
col default_tablespace for a40
col temporary_tablespace for a40
alter session set nls_date_format='yyyymmdd hh24:mi:ss';
show parameter instance_name
host hostname
select * from v$version;
select sum(bytes)/1024/1024 datafile_size_M from dba_data_files;
select sum(bytes)/1024/1024 tempfile_size_M from dba_temp_files;
show sga
show parameter db_block_size
select count(*) tablespace_count from dba_tablespaces;
select count(1) datafile_count from dba_data_files;
select count(1) tempfile_count from dba_temp_files;  
select count(1) from v$controlfile;
select * from gv$log;
select * from gv$logfile;
archive log list
select count(*) from v$session;
select * from v$controlfile;
select sequence#,to_char(first_time,'yyyymmdd_hh24:mi:ss') ftime,round((first_time-lag(first_time) over(order by first_time))*24*60,2) minutes
  from v$log_history
 where first_time > sysdate - 1 order by first_time;
select * from dba_role_privs where granted_role='DBA';
select username,default_tablespace,temporary_tablespace from dba_users where default_tablespace='SYSTEM';
select username,default_tablespace,temporary_tablespace from dba_users order by temporary_tablespace;
COL TABLESPACE_NAME FOR A30
COL EXTENT_MANAGEMENT FOR A10
COL SEGMENT_SPACE_MANAGEMENT FOR A6
COL ALLOCATION_TYPE FOR A9
SELECT TABLESPACE_NAME,EXTENT_MANAGEMENT,ALLOCATION_TYPE,SEGMENT_SPACE_MANAGEMENT from DBA_TABLESPACES;
show parameter undo
select max(UNDOBLKS),avg(UNDOBLKS),max(maxquerylen),max(SSOLDERRCNT),max(NOSPAC