Home » RDBMS Server » Performance Tuning » ORACLE TUNING PROBLEM ON LAPTOP
ORACLE TUNING PROBLEM ON LAPTOP [message #153384] Mon, 02 January 2006 00:08 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous message
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.
Previous Topic: Plan-table
Next Topic: long scans and short scans
Goto Forum:
  


Current Time: Thu Mar 28 16:57:51 CDT 2024