How can I increase the performance of my company database?

Asked By 0 points N/A Posted on -
qa-featured

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?

SHARE
Best Answer by Riley weaver
Answered By 0 points N/A #93755

How can I increase the performance of my company database?

qa-featured

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

Best Answer
Best Answer
Answered By 10 points N/A #93756

How can I increase the performance of my company database?

qa-featured

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

Related Questions