-
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.)
Jeff Hunter
-
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.
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
|