|
-
Originally posted by Arbe
All,
as of next week I need to start tuning the db that comes with our application at site of several customers.
I've theoretical knowledge on tuning (hit rates,...), but where do I really start? Does anyone have tips (and maybe some scripts) where my quest for cpu should start?
Thx,
Rik
You may try to get some basic info with this script, save it as dba.sql first:
Code:
/*==================================================*/
/* */
/* ORACLE v 8.x database SQL-script */
/* Basic information about database */
/* Connect with DBA-priviledges */
/* */
/* To run script in SQL*Plus type: */
/* */
/* @dba.sql */
/* */
/*==================================================*/
clear columns
column name noprint new_value xdbname
select name from v$database
/
set feedback off
set linesize 70
set pagesize 999
spool dba_&&xdbname..txt
prompt*******************************************************************
clear columns
col report head 'REPORT OF DATABASE:' for a46
col day head 'DATE TIME' for a20
select 'Basic Information' report,
TO_CHAR(sysdate,'DD.MM.YYYY HH24:MI:SS') day
from sys.dual
/
prompt*******************************************************************
prompt
prompt
clear columns
col host_name head 'Hostname of Computer:' for a20
select host_name from v$instance
/
clear columns
col instance_name head 'Instance Name' for a14
col started head 'StartUp Time' for a19
col status for a8
col opentime head 'Open Time' for a19
select vi.instance_name,
to_char(vi.startup_time, 'DD.MM.YYYY HH24:MI:SS') started,
vt.status,
to_char(vt.open_time, 'DD.MM.YYYY HH24:MI:SS') opentime
from v$instance vi, v$thread vt
/
clear columns
col name head 'Database Name' for a22
col log_mode head 'Archivelog Mode' for a20
col createdate head 'Date of Creation' for a19
select name, log_mode,
to_char(created, 'DD.MM.YYYY HH24:MI:SS') createdate
from v$database
/
prompt
clear columns
col banner head 'Oracle Version Information' for a61
select banner from v$version
/
prompt
clear columns
col parameter head 'NLS_Database_Parameters'
col value head 'Values' for a15
select parameter, value from nls_database_parameters
order by parameter
/
col name head 'Database Compatible Parameter' for a30
col value head 'Value' for a15
select name, value from v$parameter
where name='compatible'
/
prompt
prompt
prompt *******************************************
prompt SYSTEM GLOBAL AREA, DB BLOCK SIZE, DATABASE
prompt BUFFERS, SHARED POOL SIZE, SORT AREA SIZE
prompt *******************************************
clear columns
clear computes
col name head 'System Global Area (SGA)|Group Name' for a24
col value head 'Size|(kB)' for 9999999.99
compute sum of value on report
break on report
select name, value/1024 value from v$sga
/
prompt
clear columns
col headbsize head 'Database block size:' for a30
col bsize head '(Bytes)' for a10
select name headbsize, value bsize from v$parameter where name = 'db_block_size'
/
clear columns
col headbfs head 'Database Buffers:' for a30
col bfs_pcs head '(pcs)' for a10
select name headbfs, value bfs_pcs from v$parameter where name = 'db_block_buffers'
/
prompt
clear columns
col headsps head 'Shared Pool Size:' for a30
col sps_size head '(Bytes)' for a10
col sps_ksize head '(kB)' for 9999999
select name headsps, value sps_size, value/1024 sps_ksize from v$parameter where name = 'shared_pool_size'
/
clear columns
col headsas head 'Sort Area Size:' for a30
col sas_size head '(Bytes)' for a10
col sas_ksize head '(kB)' for 9999999
select name headsas, value sas_size, value/1024 sas_ksize from v$parameter where name = 'sort_area_size'
/
prompt
clear columns
col headlogbfs head 'Redo Log Buffer:' for a30
col logbfs_size head '(Bytes)' for a10
col logbfs_ksize head '(kB)' for 9999999
select name headlogbfs, value logbfs_size, value/1024 logbfs_ksize from v$parameter where name = 'log_buffer'
/
prompt
prompt
prompt *****************************
prompt REDO LOG FILES, CONTROL FILES
prompt *****************************
clear columns
col member head 'Redo Log Files' for a42
col group# head 'Gr' for 9
col status head 'Status' for a8
col kbytes head 'Size|(kB)' for 99999
select lf.member,
lf.group#,
lg.status,
lg.bytes/1024 kbytes
from v$logfile lf, v$log lg
where lf.group#=lg.group#
order by group#
/
clear columns
col name head 'Control Files' for a45
col status head 'Status' for a15
select name, status from v$controlfile
/
prompt
prompt
prompt ***************************************
prompt TABLESPACES, DATABASE FILES, FREE SPACE
prompt ***************************************
clear columns
col tablespace_name head 'Tablespace' for a15
col status head 'Status' for a7
col contents head 'Contents' for a9
col initext head 'Initial|(kB)' for 99999
col nextext head 'Next|(kB)' for 99999
col min_extents head 'Min|Ext' for 99
col max_extents head 'Max|Ext' for 999
col pct_increase head 'Inc|(%)' for 999
select ts.tablespace_name,
ts.status,
ts.contents,
ts.initial_extent/1024 initext,
ts.next_extent/1024 nextext,
ts.min_extents,
ts.max_extents,
ts.pct_increase
from sys.dba_tablespaces ts
order by tablespace_name
/
clear columns
col file_id head 'Id#' for 999
col file_name head 'Database filename' for a39
col Mbytes head 'Size|(MB)' for 9999.99
col tablespace_name head 'Tablespace' for a15
select df.file_id file_id,
df.file_name file_name,
df.bytes/(1024*1024) MBytes,
ts.tablespace_name tablespace_name
from sys.dba_tablespaces ts, sys.dba_data_files df
where ts.tablespace_name = df.tablespace_name
order by df.file_id, df.tablespace_name
/
clear columns
clear computes
col fid head 'Id#' for 999
col tsname head 'Tablespace' for a15
col total head 'Size|(MB)' for 99999.99
col free head 'Free|(MB)' for 99999.99
col freepros head 'Free|(%)' for 9999.99
break on report
compute sum of total on report
compute sum of free on report
select df.file_id fid,
df.tablespace_name tsname,
df.bytes/(1024*1024) total,
NVL(sum(fs.bytes)/(1024*1024),0) free,
NVL((100*((sum(fs.bytes))/df.bytes)),0) freepros
from sys.dba_data_files df, sys.dba_free_space fs
where df.file_id = fs.file_id(+)
group by df.tablespace_name, df.FILE_ID, df.bytes
order by df.tablespace_name
/
prompt
clear columns
col name head 'Max. number of database files (init.ora)' for a48
col value head 'pcs' for a10
select name, value from v$parameter
where name='db_files'
/
prompt
prompt
prompt *****************
prompt ROLLBACK-SEGMENTS
prompt *****************
clear columns
col owner head 'Owner' for a7
col segment_name head 'Segment name' for a12
col status head 'Status' for a7
col tablespace_name head 'Tablespace' for a12
col initial_extent/1024 head 'Init|(kB)' for 99999
col next_extent/1024 head 'Next|(kB)' for 99999
col min_extents head 'Min|Ext' for 99
col max_extents head 'Max|Ext' for 999
col pct_increase head 'Inc|(%)' for 99
select owner, segment_name, tablespace_name,
initial_extent/1024, next_extent/1024,
min_extents, max_extents,pct_increase, status
from sys.dba_rollback_segs
order by segment_name
/
prompt
prompt
prompt ****************
prompt USER INFORMATION
prompt ****************
clear columns
col user_id head 'Id#' for 999
col username head 'Username' for a12
col created head 'Created' for a12
col defTBS head 'Default|Tablespace' for a15
col tempTBS head 'Temporary|Tablespace' for a15
select user_id, username, TO_CHAR(created,'DD.MM.YYYY') created,
default_tablespace defTBS, temporary_tablespace tempTBS
from sys.dba_users
order by username
/
spool off
Oracle Certified Master
Oracle Certified Professional 6i,8i,9i,10g,11g,12c
email: [email protected]
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|
Reply With Quote