| ORACLE TUNING PROBLEM ON LAPTOP [message #153384] |
Mon, 02 January 2006 00:08  |
gaupa
Messages: 7
Registered: November 2005
Location: Ahmedabad
|
Junior Member |
|
|
Following query showing the index name in execution plan
Problem:
Following query run when Os is start it will take 50 sec.
after the first start it will take 7 sec.
The user is using LAPTOP and personal database on laptop. He face performance problem on this query.
Please help in query tuning on Database startup time.
Thanks in advance
Query:
Select col1,col2,col3,col5....,col89
FROM tab1 A
WHERE (a.col1 = '001') AND (a.col2 = 0)
and a.col3 Is not NULL
AND ( (a.col4='XXXX' OR a.col5 ='X') )
ORDER by A.coldate DESC
index:
INDEX1 ( col1,col2,col3,col4)
Statistic information
==================
tab1 : Number of records: 73000
(separate tablespace for this table)
INIT PARAMETERS:
================
db_name = oracle
db_files = 20
control_files = (c:\orant\database\ctl1ORCL.ora, c:\orant\database\ctl2ORCL.ora)
db_file_multiblock_read_count = 32 # LARGE
db_block_buffers = 40000
shared_pool_size = 12000000 # LARGE
log_checkpoint_interval = 10000
processes = 500 # INITIAL
dml_locks = 300 # MEDIUM
log_buffer = 32768 # MEDIUM
sequence_cache_entries = 100 # LARGE
sequence_cache_hash_buckets = 89 # LARGE
db_block_size = 8192
snapshot_refresh_processes = 1
remote_login_passwordfile = shared
text_enable = true
UTL_FILE_DIR = *
sort_area_size=1000000
NLS_DATE_FORMAT = "MM-DD-YYYY"
open_cursorS = 500
rollback_segments = (RB1,RB2,RB3)
OPTIMIZER_MODE= rule
|
|
|
|
| Re: ORACLE TUNING PROBLEM ON LAPTOP [message #153388 is a reply to message #153384] |
Mon, 02 January 2006 00:12  |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
An Oracle database is not meant to be stopped and started too often. If the database just started, the database is so-called 'cold', meaning no blocks in memory, no queries in the shared pool etc.
Everything you do takes a (long) time to complete, since every query must be hard-parsed, every single block must be retrieved from disk etc.
hth
|
|
|
|
| Re: ORACLE TUNING PROBLEM ON LAPTOP [message #153389 is a reply to message #153388] |
Mon, 02 January 2006 00:17 |
gaupa
Messages: 7
Registered: November 2005
Location: Ahmedabad
|
Junior Member |
|
|
Thank You very much. But any otherway at which i decrease the query time. because it will take to much time on laptop.
It means any tuning option which help me in I/O reading.
|
|
|
|
| Re: ORACLE TUNING PROBLEM ON LAPTOP [message #153393 is a reply to message #153389] |
Mon, 02 January 2006 00:28 |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
How many rows are returned?
In what context is the query executed (sqlplus, showing all rows returned? forms, showing only the first row? etc)
7 seconds is too much time too, IF you are only displaying the first (n) row(s).
hth
|
|
|
|
| Re: ORACLE TUNING PROBLEM ON LAPTOP [message #153397 is a reply to message #153393] |
Mon, 02 January 2006 01:26 |
gaupa
Messages: 7
Registered: November 2005
Location: Ahmedabad
|
Junior Member |
|
|
The number of record is 73000 but we need only initial 250 records which is order by date.
The return records fill the Visual basic grid.In the visual basic we cut the first 250 records.
|
|
|
|
| Re: ORACLE TUNING PROBLEM ON LAPTOP [message #154791 is a reply to message #153384] |
Tue, 10 January 2006 10:56 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
Many of your params look like typical 8i ones. If this is just a laptop database, I'd suggest using a current and supported version. Also I'm not sure I'd consider 32MB of redo buffer to be "small". And I agree with everything Frank said, you can't keep shutting down and starting up, what is the point in that?
And if you are still in the old mindsets, read about and gather proper statistics on your objects if you don't already have them and plan to use the CBO (if you upgrade, consider it even if you don't). And a laptop database with 500 processes seems beyond excessive.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
