| SGA too big? [message #52607] |
Wed, 31 July 2002 10:56  |
Toyn
Messages: 36
Registered: April 2001
|
Member |
|
|
I'm doing some joins and they are taking an exorbitant amount of time (almost a week). I am the only user on this system, and I cannot understand why my joins would take so long. Even if the joins are doing full table scans (although my db_file_multiblock_read_count=8, and all tables have indexes reflecting those columns in the join query)...there are only about 400k rows involved in the 2 tables Are any of the following parameters miscalculated? Thanks.
SVRMGR> show sga
Total System Global Area 641900048 bytes
Fixed Size 48656 bytes
Variable Size 105062400 bytes
Database Buffers 534675456 bytes
Redo Buffers 2113536 bytes
beast>top
last pid: 10207; load averages: 0.01, 0.02, 0.03
58 processes: 57 sleeping, 1 on cpu
CPU states: 97.9% idle, 0.3% user, 1.1% kernel, 1.5% iowait,0.0% swap
Memory: 1536M real, 718M free, 675M swap in use, 2627M swap free
db_block_buffers = 32634
log_buffer = 2097152
db_block_size = 16384
VALUE NAME
----- -----
5K shared_pool_reserved_min_alloc
4750000 shared_pool_reserved_size
95000000 shared_pool_size
|
|
|
|
| Re: SGA too big? [message #52610 is a reply to message #52607] |
Wed, 31 July 2002 13:12  |
Toyn
Messages: 36
Registered: April 2001
|
Member |
|
|
Here are the stats when I run the join query:
last pid: 10337; load averages: 1.47, 1.08, 0.59 16:54:03
70 processes: 68 sleeping, 1 running, 1 on cpu
CPU states: 65.8% idle, 24.4% user, 2.3% kernel, 7.5% iowait, 0.0% swap
Memory: 1536M real, 585M free, 674M swap in use, 2630M swap free
|
|
|
|
| Re: SGA too big? [message #52616 is a reply to message #52607] |
Thu, 01 August 2002 04:54 |
Sanjay
Messages: 236
Registered: July 2000
|
Senior Member |
|
|
Before you actually run the query, try doing a EXPLAIN PLAN and optimize the query. Try hitting the indexes anywhere you can...if not create an index and see if it speeds up the query or not.
Good luck.
|
|
|
|
| Re: SGA too big? [message #52618 is a reply to message #52607] |
Thu, 01 August 2002 07:10 |
Toyn
Messages: 36
Registered: April 2001
|
Member |
|
|
I've run the explain plan before:
Q_PLAN
--------------------------------------------------------------------------------
UPDATE STATEMENT
UPDATE ORDERS
TABLE ACCESS FULL ORDERS
TABLE ACCESS FULL TEMP_ED_ORDERS
And I've created indexes on both tables listed within the query, and included those columns in the indexes. There's only about 400k rows in each table:
update orders a set (ord_stat)=
(select code_desc from temp_ed_orders b
where a.order_num=b.order_num);
I really think this has something to do with my system/SGA setttings. Is there anything wrong with how they're setup? Thanks!
|
|
|
|
| Re: SGA too big? [message #52631 is a reply to message #52607] |
Thu, 01 August 2002 21:59 |
santosh
Messages: 85
Registered: October 2000
|
Member |
|
|
Hi,
The problem which i could see in your query is that
u got duplicate or null records in order_num columns of one of the table to avoid this you can enforce primary key like constarint on both the columns which will put indexing also. Or else you can try creating cluster and rebuild both tables which will enhance the operation of updation.
Santosh
|
|
|
|
| Re: SGA too big? [message #52636 is a reply to message #52607] |
Fri, 02 August 2002 12:46 |
Toyn
Messages: 36
Registered: April 2001
|
Member |
|
|
There are neither dups, or null values in the ord_num columns. I ran an initial update statement:
insert into orders (order_num)
select order_num from temp_ed_orders;
Then I execute the update statement which is giving me the issue:
update orders a set (ord_stat)=
(select code_desc from temp_ed_orders b
where a.order_num=b.order_num);
That's why I'm wondering if my sga is set incorrectly...any suggestions would be great! Thanks!
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
