Printable View
I need a direction from you guys..
My company has some data intensive tables (30-50 million records) to start with. OLTP database. Data insertion through Java application.. (No sql loader)..
Writes, I am not bothered now.. Aswell, no DB level tuning now.. But later..
But Performance is a problem from Programmer's level..
What should I do first?? I need some steps..
Any good docs or any scripts available..
Thx..
1. make sure they are using bound variables. You want to make sure oracle is not parsing each and every insert statement.
2. Use array inserts. Insert a bunch of stuff at one time instead of multiple writes.
3. Use sql*loader. You can bypass oracle's buffer cache and insert directly into tables.
(I don't understand why people always try to re-invent the wheel. Spend your programmers time on application logic & GUI's.)
Thanx Marist89.. All those points for writes..
How about reads and writes while writes are stillgoing on..
A typical OLTP environment though..
RAID 1+0 is a good choice for a large volume of data.
Distribute the data on many disks. Partition may help.
Use B-Tree in OLTP environment.
Cache the Ref.Tables data in Buffer Pool Keep.
Configure good amount of SORT AREA SIZE.
Configure a large amount for DB_BLOCK_BUFFER.
Check the application Logic.
Avoid Group by, and SORT in SQL statement.
Verify the EXECUTION PLAN for Nested Loops.
How do you use B-Tree?
What is caching Ref Tbls in Buffer Pool keep
How do you configure sort_area_size
what is the alternate for sorting and group by..
------------------------------------------------------------
comment on the below::
SQL> show parameters buffer
NAME TYPE VALUE
------------------------------------ ------- ------------------------------
buffer_pool_keep string
buffer_pool_recycle string
db_block_buffers integer 7500
log_buffer integer 163840
use_indirect_data_buffers boolean FALSE
SQL> show parameters sort
NAME TYPE VALUE
------------------------------------ ------- ------------------------------
nls_sort string
sort_area_retained_size integer 131072
sort_area_size integer 131072
sort_multiblock_read_count integer 2
Suggestions, welcome .. Thx.