Tuning - Suggestions/Comments

[joyce_ank]

Here's a brief summary of a query on my database. I'd like to start tuning the database. Any pointers,suggestions or comments.

Thanks

Oracle Version Information
-------------------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.7.1.1 - Production
PL/SQL Release 8.1.7.1.0 - Production
CORE 8.1.7.0.0 Production
TNS for 32-bit Windows: Version 8.1.7.1.0 - Production
NLSRTL Version 3.4.1.0.0 - Production


NLS_Database_Parameters Values
------------------------------ ---------------
NLS_CALENDAR GREGORIAN
NLS_CHARACTERSET WE8ISO8859P1
NLS_LANGUAGE AMERICAN
NLS_NCHAR_CHARACTERSET WE8ISO8859P1
NLS_RDBMS_VERSION 8.1.7.1.1
NLS_SORT BINARY
NLS_TERRITORY AMERICA
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI
.SSXFF AM
Database Compatible Parameter Value
------------------------------ ---------------
compatible 8.1.0.0.0


*******************************************
SYSTEM GLOBAL AREA, DB BLOCK SIZE, DATABASE
BUFFERS, SHARED POOL SIZE, SORT AREA SIZE
*******************************************

System Global Area (SGA) Size
Group Name (kB)
------------------------ -----------
Fixed Size 74.03
Variable Size 20260.00
Database Buffers 4400.00
Redo Buffers 76.00
-----------
sum 24810.03


Database block size: (Bytes)
------------------------------ ----------
db_block_size 8192

Database Buffers: (pcs)
------------------------------ ----------
db_block_buffers 550


Shared Pool Size: (Bytes) (kB)
------------------------------ ---------- --------
shared_pool_size 18000000 17578

Sort Area Size: (Bytes) (kB)
------------------------------ ---------- --------
sort_area_size 65536 64


Redo Log Buffer: (Bytes) (kB)
------------------------------ ---------- --------
log_buffer 8192 8


*****************************
REDO LOG FILES, CONTROL FILES
*****************************

Size
Redo Log Files Gr Status (kB)
------------------------------------------ -- -------- ------
U:\ORACLE\ORADATA\EVOLVLIV\REDO01.LOG 1 INACTIVE 5120
U:\ORACLE\ORADATA\EVOLVLIV\REDOO1B.LOG 1 INACTIVE 5120
U:\ORACLE\ORADATA\EVOLVLIV\REDO02.LOG 2 INACTIVE 5120
U:\ORACLE\ORADATA\EVOLVLIV\RED002B.LOG 2 INACTIVE 5120
U:\ORACLE\ORADATA\EVOLVLIV\REDO03.LOG 3 ACTIVE 5120
U:\ORACLE\ORADATA\EVOLVLIV\REDO03B.LOG 3 ACTIVE 5120
U:\ORACLE\ORADATA\EVOLVLIV\REDO04.LOG 4 CURRENT 5120
U:\ORACLE\ORADATA\EVOLVLIV\RED004B.LOG 4 CURRENT 5120


***************************************
TABLESPACES, DATABASE FILES, FREE SPACE
***************************************

Initial Next Min Max Inc
Tablespace Status Contents (kB) (kB) Ext Ext (%)
--------------- ------- --------- ------- ------ --- ---- ----
CL_DAT01 ONLINE PERMANENT 40 40 1 505 50
CL_IND01 ONLINE PERMANENT 40 40 1 505 50
CL_RBS01 ONLINE PERMANENT 504 504 10 505 0
CL_TMP01 ONLINE TEMPORARY 104 104 1 0
SYSTEM ONLINE PERMANENT 16 16 1 505 50

Size
Id# Database filename (MB) Tablespace
---- --------------------------------------- -------- ---------------
1 U:\ORACLE\ORADATA\EVOLVLIV\SYSTEM01.DBF 250.00 SYSTEM
2 U:\ORACLE\ORADATA\EVOLVLIV\CL_RBS01.DBF 100.00 CL_RBS01
3 U:\ORACLE\ORADATA\EVOLVLIV\CL_TMP01.DBF 100.00 CL_TMP01
4 U:\ORACLE\ORADATA\EVOLVLIV\CL_DAT01.DBF 500.00 CL_DAT01
5 U:\ORACLE\ORADATA\EVOLVLIV\CL_IND01.DBF 300.00 CL_IND01

Size Free Free
Id# Tablespace (MB) (MB) (%)
---- --------------- --------- --------- --------
4 CL_DAT01 500.00 138.95 27.79
5 CL_IND01 300.00 125.72 41.91
2 CL_RBS01 100.00 12.14 12.14
3 CL_TMP01 100.00 21.71 21.71
1 SYSTEM 250.00 194.96 77.98
--------- ---------
sum 1250.00 493.48


Max. number of database files (init.ora) pcs
------------------------------------------------ ----------
db_files 99


*****************
ROLLBACK-SEGMENTS
*****************

Init Next Min Max Inc
Owner Segment name Tablespace (kB) (kB) Ext Ext (%) Status
------- ------------ ------------ ------ ------ --- ---- --- -------
SYS R01 CL_RBS01 504 504 10 505 0 ONLINE
SYS R02 CL_RBS01 504 504 10 505 0 ONLINE
SYS R03 CL_RBS01 504 504 10 505 0 ONLINE
SYS R04 CL_RBS01 504 504 10 505 0 ONLINE
SYS R05 CL_RBS01 504 504 10 505 0 ONLINE
SYS R06 CL_RBS01 504 504 10 505 0 ONLINE
SYS R07 CL_RBS01 504 504 10 505 0 ONLINE
SYS R08 CL_RBS01 504 504 10 505 0 ONLINE
SYS R09 CL_RBS01 504 504 10 505 0 ONLINE
SYS R10 CL_RBS01 504 504 10 505 0 ONLINE
SYS SYSTEM SYSTEM 56 56 2 505 0 ONLINE

[marist89]

80% of performance problems stem from poorly written SQL. I would concentrate your efforts on SQL.

[joyce_ank]

Thanks

[Sureshy]

Hi Joyce..

Jeff has given you the best advice on where to start.

You can also look into some scripts which will tell you how well (or badly) you SGA is sized (utlbstat/estat is an all round performance indicator script provided by oracle).

Also, whether your latches need tuning, I/O tuning, using Oracle blocks efficiently etc etc.

I can not tell you all you need to know about this in one reply. You should really buy a book on the subject or attend an Oracle ILT course.

Pando has written quite a good 'sizing sga' thread in the 'How To' forum and there are many good quality performance monitoring scripts available on this site and many others on the internet.

Good Luck!

Suresh

[pando]

uh I never wrote that

you probably mean someone else

[Sureshy]

Sorry Pando...

Padman has written quite a good 'How to Tune Sga' in How To Forum.