Asked By
shimi
0 points
N/A
Posted on - 10/18/2011
Hi, I want to design a database for my company business in Oracle. But I don’t know how I shall get better performance from my database. How can we organize the table spaces in Oracle database to have maximum performance?
How can I increase the performance of my company database?
Hi there,
It’s obvious that you will need to add tablespaces to improve the performance of your database and for the optimum database performance you can need to do following things:
1) Store data in tablespaces which will avoid the disk contention.
2) Try to implement raid-0 (striping) for better performance.
3) Use system tablespace- recursive callsuserdata.
4) Use objectsIndex tablespace- for the indexes.
5) Also use roll back segments-Undo tablespace or manual roll back tablespace.
6) place application specific data in respective tablespaces
All you need to do is put all the tablespaces mentioned above in separate disks
How can I increase the performance of my company database?
Hello Shimi,
The performance of database actually depends on its design. As a computer science student I know few things about database design. There are so many books are available which can describe about it. Just for giving an idea about database design I want to mention some terms like primary key, foreign key, database scheme, table relations and mostly data flow. These things are very important. You wanted to know about “tablespace” efficiency here. You can use it using some PL/SQL. In PHP you can use this code to reuse tablespace. This also works for performance increasing.
set linesize 250
col tablespace format a10
col username format a8
col osuser format a6
col sid_pid format a10
col machine format a10
col sid format 9990
col serial# format 99990
col spid format a5
compute sum label 'TOTAL' of blocks_mb on report;
break on report
select t1.tablespace
, sum( t1.blocks * to_number( t3.value ) ) / 1024 / 1024 blocks_mb
, sum( t1.extents ) tot_extents
, t2.username
, t2.osuser
, t2.status
, t2.SID ||','|| t2.serial# SID_PID
, t4.spid
, t2.sql_address
, t5.hash_value
from v$sort_usage t1
, v$session t2
, v$parameter t3
, v$process t4
, v$sqltext t5
where t1.SESSION_ADDR = t2.SADDR
and t3.name = 'db_block_size'
and (t2.PROCESS=t4.SPID or t2.paddr = t4.addr)
and t2.sql_address = t5.address
and t5.piece = 0
group by
t1.tablespace
, t2.username
, t2.osuser
, t2.machine
, t2.status
, t2.schemaname
, t2.program
, t2.SID ||','|| t2.serial#
, t4.spid
, t2.sql_address
, t5.hash_value
order by 2
/
Thank you,
Riley weaver
