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
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 #325006 is a reply to message #325003] Wed, 04 June 2008 15:07 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
Congratulations on posting the correct forum on your 1st attempt.


http://www.orafaq.com/forum/t/84315/74940/
which of the suggestions in URL above have you tried & what were the results?


Are statistics current on all table & indexes involved?


With regards to 'precache indexes', how does any dumb RDBMS know before an index is used that now is the correct time for the RDBMS to 'precache index' just before you access it?
Re: Low oracle performance [message #325017 is a reply to message #325003] Wed, 04 June 2008 15:33 Go to previous messageGo to next message
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 messageGo to next message
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 #325062 is a reply to message #325003] Wed, 04 June 2008 23:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
how to set up oracle server for best warehousng applications?

OracleŽ Database Data Warehousing Guide

Regards
Michel
Re: Low oracle performance [message #325145 is a reply to message #325003] Thu, 05 June 2008 02:35 Go to previous messageGo to next message
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 messageGo to next message
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 messageGo to next message
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 messageGo to next message
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 #331770 is a reply to message #325003] Sat, 05 July 2008 08:15 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
Please read & follow Posting Guidelines below
http://www.orafaq.com/forum/t/88153/0/

http://www.orafaq.com/forum/t/84315/74940/
which have you tried & what were the results
Re: Low oracle performance [message #331775 is a reply to message #325003] Sat, 05 July 2008 08:33 Go to previous messageGo to next message
Kriptas
Messages: 118
Registered: June 2008
Senior Member
I tried all usefull information written there, ans result are the same!
Re: Low oracle performance [message #331781 is a reply to message #331775] Sat, 05 July 2008 08:43 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
Kriptas wrote on Sat, 05 July 2008 06:33
I tried all usefull information written there, ans result are the same!



Then you have to live with what you have.

[Updated on: Sat, 05 July 2008 08:53] by Moderator

Report message to a moderator

Re: Low oracle performance [message #331809 is a reply to message #325003] Sat, 05 July 2008 12:20 Go to previous messageGo to next message
Kriptas
Messages: 118
Registered: June 2008
Senior Member
then "I LOVE ORACLE" again!
Re: Low oracle performance [message #331860 is a reply to message #331809] Sun, 06 July 2008 01:52 Go to previous messageGo to next message
rleishman
Messages: 3724
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 messageGo to next message
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 messageGo to next message
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 messageGo to next message
rleishman
Messages: 3724
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 messageGo to next message
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 messageGo to next message
rleishman
Messages: 3724
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 messageGo to next message
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 messageGo to next message
rleishman
Messages: 3724
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 messageGo to next message
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 messageGo to next message
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 messageGo to next message
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 messageGo to next message
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 messageGo to next message
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 #332387 is a reply to message #325003] Tue, 08 July 2008 07:32 Go to previous messageGo to next message
Kriptas
Messages: 118
Registered: June 2008
Senior Member
On my PC all datafiles is in one disc.

I run test on 3 different servers (machines) and my pc.
on 4 different instances (9.2 RULE machine1, 9.2 CBO machine2, (9.2 RULE, 10gr2 CBO instances on the same machine3), 10gr2 CBO my pc)

[Updated on: Tue, 08 July 2008 09:42]

Report message to a moderator

Re: Low oracle performance [message #348379 is a reply to message #325003] Tue, 16 September 2008 10:09 Go to previous messageGo to next message
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 #348385 is a reply to message #348379] Tue, 16 September 2008 10:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So why are you there?

(Yes, Frank, I know, "don't feed the troll". Wink )

Regards
Michel

[Updated on: Tue, 16 September 2008 10:35]

Report message to a moderator

Re: Low oracle performance [message #348391 is a reply to message #325003] Tue, 16 September 2008 10:51 Go to previous message
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.
Previous Topic: Database buffer cache monitoring
Next Topic: Index rebuild
Goto Forum:
  


Current Time: Sat Dec 03 19:58:46 CST 2016

Total time taken to generate the page: 0.06851 seconds