Home » RDBMS Server » Performance Tuning » Low oracle performance (oracle 10g r2, HPUX)
Low oracle performance [message #325003] |
Wed, 04 June 2008 14:55 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Kriptas
Messages: 118 Registered: June 2008
|
Senior Member |
|
|
Hello,
I have big problem with oracle...
I worked few years with MS SQL Server2000/2005, and now i working with oracle 10g r2.
I'm sorry, but I have no other words but "Oracle sux".
Orcle performance is about 3-20 times lower then MS SQL server performance. Our DBA can't do nothing about that.
At the moment I can't deal ferfom tuning of one small query,
which like that
select column1, column2
from table1 t1
inner join table2 t2 on t1.column3=t2.column3
table1 have 2000 records,
table2 have 300 000 000 records.
table2 have b-tree index on column3
(I tried to range partitioning of table2 by column4 and bitpam indexes on column3 or global b-tree index on column3)
the problem is, that the same query, on ms sql server withut any indexes runs 5 minutes, with the same indexes on t2.column3 runs 4 minutes, with clustered index only on column4 runs 3 minutes.
the qury on oracle 10g r2 with indexes runs 3 hours, with hints and full scan on table2, runs 20 minutes.
oracle server 3-4 times powerful then ms sql server...
I do not know that to do ;/
one of the problem with oracle, it do not precache indexes...
index before runing the query, can't be in cach, becouse query runs ones in a week.
how to set up oracle server for best warehousng applications?
thanx
|
|
|
|
Re: Low oracle performance [message #325017 is a reply to message #325003] |
Wed, 04 June 2008 15:33 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Kriptas
Messages: 118 Registered: June 2008
|
Senior Member |
|
|
all statistics and histograms on all table1 and table2 columns are gathered.
when query runs with nested loops, it uses index.
one loop returns about 1000 records..
if I choose 1 record from driving table with where clause,
it returns 1000 records in ~5 seconds(so the whole result will load in ~1500 * 5 seconds), if I run that query again, ir returns result in 0.1 second.
so for the first time it takes ~5 seconds to every record in driving table...
o I need to use that query only 1 in a week, so when I run it again, index is not in the cache. and it loads index to chache very slow again.
I read the topic, and write response on that.
[Updated on: Wed, 04 June 2008 15:35] Report message to a moderator
|
|
|
Re: Low oracle performance [message #325030 is a reply to message #325003] |
Wed, 04 June 2008 17:31 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
coleing
Messages: 213 Registered: February 2008
|
Senior Member |
|
|
Ok.
If the hardware is really 3-4 times more powerful, then you or your DBA are doing something drastically wrong.
Make sure the Oracle installation is set up to use all that hardware. Ie, the initialisation parameters give you plenty of PGA and SGA, and use the disks properly.
You must first ensure stats are gathered on your target tables and indexes.
then, follow the documentation on what makes Oracle use an index. Start with joining columns with the same datatype as its the most commonly overlooked problem.
You need to use an explain plan and show it here for us to help you tune the query.
|
|
|
|
Re: Low oracle performance [message #325145 is a reply to message #325003] |
Thu, 05 June 2008 02:35 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
I can say, without fear of contradiction, that your DBA must have got your setup spectacularly wrong.
Can you post the query that you are executing, along with an explain plan, and details of the indexes?
|
|
|
Re: Low oracle performance [message #329832 is a reply to message #325003] |
Thu, 26 June 2008 12:27 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Kriptas
Messages: 118 Registered: June 2008
|
Senior Member |
|
|
1.SELECT STATEMENT, GOAL = ALL_ROWS Cost=72723 Cardinality=88889 Bytes=2133336 Optimizer=ALL_ROWS Depth=0 CPU cost=751773090
2. TABLE ACCESS BY INDEX ROWID Object owner=SDWH Object name=SDWH_CREDIT_EVENTS Cost=735 Cardinality=898 Bytes=17062 Optimizer=ANALYZED Depth=1 CPU cost=7593124 Object alias=T@SEL$1
3. NESTED LOOPS Cost=72723 Cardinality=88889 Bytes=2133336 Depth=2 CPU cost=751773090
4. TABLE ACCESS FULL Object owner=SDWH Object name=GT_ZZZ122 Cost=3 Cardinality=99 Bytes=495 Optimizer=ANALYZED Depth=3 CPU cost=53822 Object alias=GT_ZZZ122@SEL$1
5. INDEX RANGE SCAN Object owner=SDWH Object name=IDX_EVT_CRT_CODE Cost=2 Cardinality=900 Optimizer=ANALYZED Depth=3 Access predicates="CREDIT_EVT_CREDIT_CODE"="CREDIT_CODE" CPU cost=200336 Object alias=T@SEL$1
our DBA do not know why it runs so long, they tace the query with 8 lvl, but do not sow anything.
if I select only one column that is in index, query run fast (becouse it do not need to access the table, it reads it from index) but when it access table by rowid, it took to long.
I tried to look to another big tables, the same result accessing it by index.
the tables are created new with APPEND hint, index are recreated... so whats wrong?
|
|
|
Re: Low oracle performance [message #330274 is a reply to message #325003] |
Sat, 28 June 2008 12:47 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Kriptas
Messages: 118 Registered: June 2008
|
Senior Member |
|
|
I tried to generate table at home PC.
inserted about 200 000 000 records with cardinality of interested column - 200 000
(in original table was 300 000 000 records, and cardinality ~300 000)
each row lenght is 21 byte.
so size of table is ~4GB
(original table size 25 GB)
created b-tree index on tahat column, and perform nested loop select of 1500 records (in result I got 1 500 000 records)
query time was about 1 minute.. it is good and right time for that query i think...
so why at work that query using that original (25gb table) runs 3 hours? and in MS SQL only 4 minutes?
At work i tried that query on 3 different servers, on oracle 10g r2 CBO, oracle 9.2 RULE, oracle 9.2 RULE, on diferent tablespaces and etc. in all cases it takes 3-5 hours.
oracle 10G r2 server is 8xItanium CPU, 16GB ram, HP EVA 6000 array... but it takes very long time to execute query (in fact it takes long time to access rows by row_id)
so i Have 2 things in mind :
1) on all servers OS is HP UX and on oracle 9.2 CBO LINUX REDHAT.
Can it be, that UNIX have problems with accessing adresses?
My PC is on Windows XP OS..
2) Our DBA doing something wrong, and doing that on all servers.
|
|
|
Re: Low oracle performance [message #331765 is a reply to message #325003] |
Sat, 05 July 2008 06:59 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Kriptas
Messages: 118 Registered: June 2008
|
Senior Member |
|
|
Hello again.
The original table still is very slow. I loaded all data to my PC, to the oracle RDBMS, and run the same query.
Result was worse then on the server... query run time was 5 hours.
I reloaded the same table into other table with create table as statement and used group by indexed column statement in loading query.
After that I indexed the column.
Query from new table run time was only 30s!!!!!!!
Why oracle is so slow with indexes on columns with randomly distributed data? and it is DRAMATICALY slow?
We have about 10 larger tables, where is columns with same random distribution, and selecting from that tables with nested loops on that colums is very slow too.
rebuild data and order it we can't, because tables are used 24*7, and rebulding will take about 2-5 hours for every table.
after rebuilding other column data will be distributes randomly.
do anyone know what to do ?
thanx
|
|
|
|
|
|
|
Re: Low oracle performance [message #331860 is a reply to message #331809] |
Sun, 06 July 2008 01:52 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
I appreciate that it is frustrating when you cannot get something new to work properly. If Oracle was REALLY orders of magnitude slower than SQL Server, do you think ANYONE would use it. Give us a bit of credit for SOME intelligence.
The fact is that Oracle works perfectly well in thousands of organisations around the world. Just not yours. Is that Oracle's fault?
I don't mean to sound rude but stop whining and give us the information we need to help you. Complaining about Oracle has not made your query any faster.
First of all, you have not even posted the query so we can see it. You posted your interpretation of it, changing vital details.
Second, you did not format your explain plan, making it hard to read.
Third, one of the suggestions in the sticky that you said was useless is to use SQL Trace and TKPROF to get detailed statistics of the SQL. Do that, and post the TKPROF output here (assuming you want help, and not just sympathy). And use CODE tags to format your post.
Ross Leishman
|
|
|
Re: Low oracle performance [message #331864 is a reply to message #325003] |
Sun, 06 July 2008 03:10 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Kriptas
Messages: 118 Registered: June 2008
|
Senior Member |
|
|
there is original select
select log_id,
client_id,
activity_id
from part_of_clients_small
inner join LOG on client_id=clnt_id
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 80645 | 2441K| 60176 (1)|
| 1 | TABLE ACCESS BY INDEX ROWID| LOG | 815 | 21190 | 608 (1)|
| 2 | NESTED LOOPS | | 80645 | 2441K| 60176 (1)|
| 3 | TABLE ACCESS FULL | PART_OF_CLIENTS_SMALL | 99 | 495 | 3 (0)|
| 4 | INDEX RANGE SCAN | GT_IDX_1 | 975 | | 3 (0)|
------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
76007 consistent gets
52720 physical reads
0 redo size
3570023 bytes sent via SQL*Net to client
89228 bytes received via SQL*Net from client
8079 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
121162 rows processed
table part_of_clients_small has 100 rows
it takes to execute 15 minutes
this is other query
select * from LOG where client_id = 431341
Execution Plan
----------------------------------------------------------
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 815 | 69275 | 610 (1)|
| 1 | TABLE ACCESS BY INDEX ROWID| LOG | 815 | 69275 | 610 (1)|
| 2 | INDEX RANGE SCAN | GT_IDX_1 | 973 | | 6 (0)|
-----------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1780 consistent gets
396 physical reads
0 redo size
255624 bytes sent via SQL*Net to client
2603 bytes received via SQL*Net from client
204 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3042 rows processed
execution time 15s
|
|
|
Re: Low oracle performance [message #331868 is a reply to message #325003] |
Sun, 06 July 2008 03:18 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Kriptas
Messages: 118 Registered: June 2008
|
Senior Member |
|
|
rleishman,
it is hard to disciss with you, if you worked with oracle all your time, and you cant to compre oracle work with other rdbms.
In our company oracle "worked well" before I come.
Reports runed for 12-20 hours and it was normal.. because it is oracle and oracle is the best.. it can be slow. They worked all the time with oracle and have no idea that other rdms can be faster in some cases, maybe in most cases...
After tuning queries reports start running for only 5-10 minutes...
but when I put the same query in MS SQL server, it takes to execuet only 2-3 minutes.. without any tuning with hints or rdbms tuning or other extra work.
but it is other story..
|
|
|
Re: Low oracle performance [message #331879 is a reply to message #331868] |
Sun, 06 July 2008 07:10 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
It's possible you have network problems. Try this query that will do all of the IO work, but will not send any results back via the network:
SELECT * FROM (
select log_id,
client_id,
activity_id
from part_of_clients_small
inner join LOG on client_id=clnt_id
)
WHERE ROWNUM > 1
That's one possibility. But here's something more interesting:
76007 consistent gets
52720 physical reads
....
121162 rows processed
That's 76007 blocks found in the buffer cache plus 52720 that needed to be procured from disk. Total: 128727 blocks to return 121162 rows. MORE THAN A BLOCK PER ROW!!!!
Let's think about the possible reasons for this:
- Filtering? Perhaps you're reading lots of rows and discarding them because they do not match a WHERE clause. Nope, no where clause, and no unindexed join predicates. What's next?
- HWM? You could be reading lots of empty table blocks. Not in LOG, that's accessed by an index. You could possibly have a HWM problem on PART_OF_CLIENTS_SMALL though. Have lots of rows been deleted from this table leaving heaps of blank space? Gather statistics with DBMS_STATS.GATHER_TABLE_STATS and then check SELECT avg_row_length, numrows, blocks
FROM user_tables
WHERE table_name = 'PART_OF_CLIENTS_SMALL';
SELECT value
FROM sys.v_$parameter
WHERE name = 'db_block_size'
Calculate blocks*value/numrows. If it is MUCH larger than avg_row_length (eg. 50% larger or more), rebuild the table.
- Chaining? Big chance here for the LOG table. Do rows get updated such that many bytes are added? eg. A row might be inserted with minimal information and then updated to set lots of columns of a few large VARCHAR2s? Check USER_TABLE.CHAIN_CNT using a similar SQL to above. If it is large, rebuild the table and either modify the programs that populate the table, or tune the PCT_FREE attribute on the table.
- Fragmented index? Maybe on GT_IDX_1. There's a great article here that will help you check. This is most likely to occur if a large number of rows has been deleted or if the CLNT_ID has been updated on a large number of rows.
To my eyes, it looks like either chaining or fragmented index. Your second SQL that queries only LOG suffers the same problem, so it is unlikely to be PART_OF_CLIENTS_SMALL.
If you post the statistics for:
SELECT client_id
from LOG
where client_id = 431341 Then that will give us another clue since it will read the index only - not the table. If we are still getting a high ratio of consistent gets+physical reads to rows returned, then it is most likely fragmentation; otherwise it could be chaining.
Ross Leishman
|
|
|
Re: Low oracle performance [message #331882 is a reply to message #325003] |
Sun, 06 July 2008 07:27 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Kriptas
Messages: 118 Registered: June 2008
|
Senior Member |
|
|
both tables are "recreated"
Block size on my PC is 8kb, at work - 16kb.
PART_OF_CLIENT_SMALL
row_length - 10
num_rows -99
Blocks - 4
I exported tables to txt file at work, and inported them wih sqllrd to my PC at home.
index is created new.
I run the same query again :
Execution Plan
----------------------------------------------------------
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 815 | 69275 | 610 (1)|
| 1 | TABLE ACCESS BY INDEX ROWID| LOG | 815 | 69275 | 610 (1)|
| 2 | INDEX RANGE SCAN | GT_IDX_1 | 973 | | 6 (0)|
-----------------------------------------------------------------------------
Statistics
----------------------------------------------------------
239 recursive calls
0 db block gets
1981 consistent gets
396 physical reads
0 redo size
255624 bytes sent via SQL*Net to client
2603 bytes received via SQL*Net from client
204 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
3042 rows processed
into LOG tabele is rows are only inserted.
I runned query with different client_id
select * from log where client_id=774392
and result :
Execution Plan
----------------------------------------------------------
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 815 | 69275 | 610 (1)|
| 1 | TABLE ACCESS BY INDEX ROWID| LOG | 815 | 69275 | 610 (1)|
| 2 | INDEX RANGE SCAN | GT_IDX_1 | 973 | | 6 (0)|
-----------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1140 consistent gets
818 physical reads
0 redo size
172898 bytes sent via SQL*Net to client
1756 bytes received via SQL*Net from client
127 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1876 rows processed
[Updated on: Sun, 06 July 2008 07:34] Report message to a moderator
|
|
|
Re: Low oracle performance [message #331884 is a reply to message #331879] |
Sun, 06 July 2008 07:42 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
rleishman wrote on Sun, 06 July 2008 22:10 |
If you post the statistics for:
SELECT client_id
from LOG
where client_id = 431341 Then that will give us another clue since it will read the index only - not the table. If we are still getting a high ratio of consistent gets+physical reads to rows returned, then it is most likely fragmentation; otherwise it could be chaining.
|
It could also just be that the table is badly clustered. ie. Every row you want happens to be in a different block.
Also, TKPROF results would be much better than AUTOTRACE. See here for instructions.
If you trace with:
alter session set events '10046 trace name context forever, level 12'; we will get much more information, including recursive SQL, wait events, and CPU time.
Ross Leishman
|
|
|
Re: Low oracle performance [message #331891 is a reply to message #325003] |
Sun, 06 July 2008 08:25 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Kriptas
Messages: 118 Registered: June 2008
|
Senior Member |
|
|
I traced query
with tkprof
create table gt_zzz22 as
select log_id, client_id from log where client_id=2622121
results :
********************************************************************************
create table gt_zzz22 as
select log_id, client_id from log where client_id=2622121
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.14 0 0 0 0
Execute 1 0.04 17.80 503 507 35 1003
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.06 17.94 503 507 35 1003
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 61
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
control file sequential read 5 0.04 0.08
db file sequential read 503 0.16 16.35
direct path write 3 0.00 0.00
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 14.94 14.94
********************************************************************************
|
|
|
Re: Low oracle performance [message #331996 is a reply to message #331891] |
Mon, 07 July 2008 03:32 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
Nice example, thanks.
What were the recursive SQL totals at the bottom of the TKPROF output?
They shouldn't matter, because the DB FILE SEQUENTIAL READ is telling us that almost all of the elapsed time was spent reading table rows from an index ROWID.
Just for comparison, here is a SQL run on the largest table I could find that did a similar amount of work (mine uses a bitmap index, but that shouldn't matter for this demo). Mine read 590 blocks from disk (similar to yours) but in just 3.46 seconds rather than your 16.35 seconds. Nearly 5 times faster.
select SRC_SYS, LDGR_ID, DOC_NUM, LN_NUM, EFF_CMTH_SK, PRJMAP_SK, MTRL_QTY
from ef_actl_expns
where recv_ccntr_sk = 29769
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1358 consistent gets
590 physical reads
304 redo size
119345 bytes sent via SQL*Net to client
2433 bytes received via SQL*Net from client
177 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2632 rows processed
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.83 0.81 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 177 3.18 4.79 590 1358 0 2632
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 179 4.01 5.61 590 1358 0 2632
Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: 52
Rows Row Source Operation
------- ---------------------------------------------------
2632 PARTITION LIST ALL PARTITION: 1 20 (cr=1358 pr=590 pw=0 time=1191706 us)
2632 TABLE ACCESS BY LOCAL INDEX ROWID EF_ACTL_EXPNS PARTITION: 1 20 (cr=1358 pr=590 pw=0 time=1737953 us)
2632 BITMAP CONVERSION TO ROWIDS (cr=26 pr=2 pw=0 time=97800 us)
3 BITMAP INDEX SINGLE VALUE EF_AEXP_RECVCCNTR_FK PARTITION: 1 20 (cr=26 pr=2 pw=0 time=76510 us)(object id 171718)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 177 0.00 0.00
db file sequential read 590 0.13 3.46
SQL*Net message from client 177 0.01 0.42
Now you might imagine that my server is some fancy-schmance super computer. In fact, it is a 2-CPU Linux server running VMWare. ie. It's almost as powerful as a PC.
Unless you are suffering massive disk contention with other users, there is something fundamentally wrong with either your Oracle instance setup or your hardware setup. Is it possible that the index and table are on the same unstriped, un-raided disk?
This is where you get beyond my skills as an application tuner and into DBA or SA land. You need to take these results to your DBA as proof of an under-performing disk. The DBA should get you to reproduce the case (no worries there) whilst he monitors contention on disks, CPU, memory. Ideally, it should be done on an otherwise idle server.
If the DBA cannot find the fault from there, he should involve the System Administrator to do some benchmarks on the disks involved. If none of that works, you should get a skilled DBA to consult for a day.
Ross Leishman
|
|
|
Re: Low oracle performance [message #332003 is a reply to message #325003] |
Mon, 07 July 2008 03:53 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Kriptas
Messages: 118 Registered: June 2008
|
Senior Member |
|
|
Hmmm, the exaple was made on my PC, so there is no raded, no striped disk. just defragmented.
at work we using EVA 6000 disk array, so i cnt say anything, because database is on virtual disk, and how it works in side it is mistery for me...
But the speed on my PC is almost the same as at work and on that EVA 6000 and server.. maybe 10-20% slower.
it is hard to find skilled dba ;/
All dba says - system works - thats good, what do you want from us... call us then system hangs up.
|
|
|
Re: Low oracle performance [message #332011 is a reply to message #325003] |
Mon, 07 July 2008 04:25 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Kriptas
Messages: 118 Registered: June 2008
|
Senior Member |
|
|
and anyway.. i do not know your table size, and etc...
but selecting one client_id (1000 records) from LOG table must take arount 0.2 seconds... that will be normal ;/ 5 sec. is slow too.
|
|
|
Re: Low oracle performance [message #332079 is a reply to message #332003] |
Mon, 07 July 2008 07:57 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Just an observation - you say dumped the data out and then loaded it with SqlLdr back home - this will remove any chained rows, and rebuild any fragmented indexes.
That may explain why your home pc gets to run as fast as your server at work.
Can you generate that TkProf data from the db that's having the problem?
|
|
|
Re: Low oracle performance [message #332125 is a reply to message #325003] |
Mon, 07 July 2008 10:10 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Kriptas
Messages: 118 Registered: June 2008
|
Senior Member |
|
|
at work i created few tables with
create table log2 as
select * from LOG
on different tablespaces, on different servers,
created new indexes. the result the same +-10% of speed.
|
|
|
Re: Low oracle performance [message #332358 is a reply to message #332125] |
Tue, 08 July 2008 05:30 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Do the other tablespaces use data files that are on the same physical device as the original tests (DBA_DATA_FILES or v$datafile are the views to provide that information)
When you say 'different server' do you mean you've conducted the tests on an entirely different database, or that the datafiles were on a different server to the original test?
|
|
|
|
Re: Low oracle performance [message #348379 is a reply to message #325003] |
Tue, 16 September 2008 10:09 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Kriptas
Messages: 118 Registered: June 2008
|
Senior Member |
|
|
so, in final I can say that ORACLE do not work with indexes very well. Performance is so slow like in worst nightmares.
MS SQL SERVER outperform oracle in many ways.
|
|
|
|
Re: Low oracle performance [message #348391 is a reply to message #325003] |
Tue, 16 September 2008 10:51 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
Kriptas
Messages: 118 Registered: June 2008
|
Senior Member |
|
|
to see more things where ORACLE can not perform well.
i spend too mutch time trying to tune oracle for close performance to ms sql server, but without any good result.
there is problem that all oracle programers do not see other side.
|
|
|
Goto Forum:
Current Time: Wed Sep 11 21:29:20 CDT 2024
|