| Akses Table very Slow [message #58191] |
Wed, 06 August 2003 05:20  |
Supar
Messages: 9
Registered: January 2003
|
Junior Member |
|
|
Hai ... any one have suggest for my problem
I have transaction table that have 3,628,657 records
and 638.28 MB in size. When my user try to query data from that table , it take long time to get the data . Does any one have suggest or tip to Tunning my database server for better performance for problem above (because i can't touch sql statement ). I already make index for Primary Key and other columns.
This is my Table Structure :
COLUMN01 NOT NULL VARCHAR2(4) PK1
COLUMN02 NOT NULL VARCHAR2(4) PK2
COLUMN03 NOT NULL VARCHAR2(8) PK3
COLUMN04 NOT NULL VARCHAR2(1) PK4
COLUMN05 NOT NULL VARCHAR2(1)
COLUMN06 NOT NULL VARCHAR2(1)
COLUMN07 NOT NULL VARCHAR2(3)
COLUMN08 NOT NULL VARCHAR2(24)
COLUMN09 NOT NULL VARCHAR2(6)
COLUMN10 NOT NULL VARCHAR2(8)
COLUMN11 NOT NULL VARCHAR2(8)
COLUMN12 NOT NULL NUMBER(15,2)
COLUMN13 NOT NULL NUMBER(15,2)
COLUMN14 NOT NULL NUMBER(15,2)
COLUMN15 NOT NULL VARCHAR2(8)
COLUMN16 NOT NULL VARCHAR2(6)
COLUMN17 NOT NULL VARCHAR2(10)
COLUMN18 NOT NULL VARCHAR2(4)
COLUMN19 NOT NULL VARCHAR2(17)
COLUMN20 NOT NULL VARCHAR2(24)
COLUMN21 NOT NULL VARCHAR2(4)
COLUMN22 VARCHAR2(20)
Please help Me ....
Thank's A Lot
|
|
|
|
| Re: Akses Table very Slow [message #58192 is a reply to message #58191] |
Wed, 06 August 2003 06:03  |
shad
Messages: 50
Registered: October 2000
|
Member |
|
|
(1) What do you mean u can't touch sql statement? That is where you need to start.
(2)Use explain plan to find the out the execution plan for the query. You may have indexes but they not be used for different reasons.
(3) Consider using bind variables in the application and cusrsor sharing if the it s a lot of users executing similar code.
But in summary, start with the explain plan. Check executions, parses and fetches.
|
|
|
|
|
|
| Re: Akses Table very Slow [message #58207 is a reply to message #58192] |
Wed, 06 August 2003 19:27 |
Supar
Messages: 9
Registered: January 2003
|
Junior Member |
|
|
|
I can't touch my sql because user running report from application and the appliation will generate SQL on the fly to get data from database, its like Package Application, we can not modify the sql directly ..
|
|
|
|
| Re: Akses Table very Slow [message #58211 is a reply to message #58207] |
Thu, 07 August 2003 04:51 |
shad
Messages: 50
Registered: October 2000
|
Member |
|
|
Sorry, Parto.
-- We are no saying you should modify the sql.
-- Turn on user tracing on the PC (trace_level_client =16 and trace_directory_client = oracle_home/network/admin) the user is running the report and ask the user to run the report.
-- The cli.trc (or sqlnet.trc)will show the sql.
-- Get explain plan for the sql statement
Keep us posted.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
