-
Performane problem PROD versus PREPROD
ORACLE 9.2
Solaris 2.8
BLOCKS : 8k
We have 2 databases, the production and the pre-pod.
The pre-pod database is refreshed periodically with the data
from prod using inport/export dumps.
Lately, I have come across a performance problem on the production database.
A Full scan of one particular table takes 32 minutes versus 9 minutes
on the pre-prod , when the machine on the pre-prod is normally slower than
the prod one.
The prod table has more rows than the one on the pre-prod (144718411 versus 121815630).
For me that should not account for the difference.
I thought that maybie , I had chained rows on the production database.
So, to prove my assumption, I ran those queries that I am listing with results both on the pre-prod
and the prod :
QUERY 1 :
SQL> select a.name, b.value
from v$statname a, v$mystat b
where a.statistic#=b.statistic#
and name like 'table%';
PRE-PROD
NAME VALUE
------------------------------------------------- ----------
table scans (short tables) 5
table scans (long tables) 0
table scans (rowid ranges) 0
table scans (cache partitions) 0
table scans (direct read) 0
table scan rows gotten 3
table scan blocks gotten 3
table fetch by rowid 85
table fetch continued row 0
table lookup prefetch client count 0
10 rows selected.
Elapsed: 00:00:00.00
PROD
NAME VALUE
---------------------------------------------------- ----------
table scans (short tables) 5
table scans (long tables) 0
table scans (rowid ranges) 0
table scans (cache partitions) 0
table scans (direct read) 0
table scan rows gotten 3
table scan blocks gotten 3
table fetch by rowid 26
table fetch continued row 0
table lookup prefetch client count 0
10 rows selected.
Elapsed: 00:00:00.04
QUERY 2
SQL> select count(*)
from contrat1.fa_usage_detail
where id_type_identifiant is not null; (One of the first columns in the definition of the table)
PRE-PROD
COUNT(*)
----------
121815630
Elapsed: 00:09:15.54
PROD
COUNT(*)
----------
144718411
Elapsed: 00:31:36.26
QUERY 3
To verify that "table fetch continued row" is still 0
SQL>select a.name, b.value
from v$statname a, v$mystat b
where a.statistic#=b.statistic#
and name like 'table%';
PRE-PROD
NAME VALUE
---------------------------------------------------- ----------
table scans (short tables) 12
table scans (long tables) 21
table scans (rowid ranges) 0
table scans (cache partitions) 0
table scans (direct read) 0
table scan rows gotten 121815633
table scan blocks gotten 4990985
table fetch by rowid 1268
table fetch continued row 0
table lookup prefetch client count 0
10 rows selected.
Elapsed: 00:00:00.03
PROD
NAME VALUE
--------------------------------------------------- ----------
table scans (short tables) 10
table scans (long tables) 25
table scans (rowid ranges) 0
table scans (cache partitions) 0
table scans (direct read) 0
table scan rows gotten 144718414
table scan blocks gotten 6167092
table fetch by rowid 1269
table fetch continued row 0
table lookup prefetch client count 0
10 rows selected.
Elapsed: 00:00:00.07
QUERY 4
SQL> select count(*)
from contrat1.fa_usage_detail
where id_catalogue is not null;
This is the last column of the table which was added recently, and was updated.
I wanted to see the value of "table fetch continued row" increase to show me
the chained rows :
PRE-PROD
COUNT(*)
----------
17355117
Elapsed: 00:09:29.09
PROD
COUNT(*)
----------
41099556
Elapsed: 00:33:33.13
QUERY 5
To verify that "table fetch continued row" increased on the prod and not on the pre-prod
SQL>select a.name, b.value
from v$statname a, v$mystat b
where a.statistic#=b.statistic#
and name like 'table%';
PRE-PROD
NAME VALUE
---------------------------------------------------- ----------
table scans (short tables) 19
table scans (long tables) 42
table scans (rowid ranges) 0
table scans (cache partitions) 0
table scans (direct read) 0
table scan rows gotten 243631263
table scan blocks gotten 9981967
table fetch by rowid 2669
table fetch continued row 0
table lookup prefetch client count 0
10 rows selected.
Elapsed: 00:00:00.03
PROD
NAME VALUE
------------------------------------------------------ ----------
table scans (short tables) 15
table scans (long tables) 50
table scans (rowid ranges) 0
table scans (cache partitions) 0
table scans (direct read) 0
table scan rows gotten 289436825
table scan blocks gotten 12334181
table fetch by rowid 2516
table fetch continued row 1
table lookup prefetch client count 0
10 rows selected.
Elapsed: 00:00:00.12
We seem to have only one chained row which could not
account for the difference in response time.
I would like to add that the problem is only on this table.
All other queries on the other tables are faster in prod than in pre-prod.
I would also add that I ran the queries several times on the prod and on the pre-prod(when they were not busy)
with similar results.I am showing here the results of just one run.
If anybody have any ideas about what the problem might be, what I am doing wrong in my tests, or
what other queries I should run , You're welcome to share your ideas.
QUERY 6
SQL> select blocks,chain_cnt,avg_row_len,num_rows
from dba_tables
where owner ='CONTRAT1'
and table_name ='FA_USAGE_DETAIL';
PRE-PROD
BLOCKS CHAIN_CNT AVG_ROW_LEN NUM_ROWS
---------- ---------- ----------- ----------
4994116 0 286 122240740
Elapsed: 00:00:00.02
[B]
PROD[/B]
BLOCKS CHAIN_CNT AVG_ROW_LEN NUM_ROWS
---------- ---------- ----------- ----------
6167089 0 293 144698310
Elapsed: 00:00:00.02
Thanks in advance.
-
I guess the HW setup and configurations make much difference in response time.
You did not tell us the server details of prod and pre-prod.
Are your sure both init.ora parameters are same.
Tamil
-
 Originally Posted by tamilselvan
I guess the HW setup and configurations make much difference in response time.
You did not tell us the server details of prod and pre-prod.
Are your sure both init.ora parameters are same.
Tamil
both init.ora are the same.
The prod has 4 pocessors and the pre-prod 2.
-
what are the explain plans
-
 Originally Posted by davey23uk
what are the explain plans
Both of them do a full scan of the table.
IF I go through an index, it takes a couple of seconds in both cases.
-
Whats the HWM on the table in both environments?
Assistance is Futile...
-
 Originally Posted by waitecj
Whats the HWM on the table in both environments?
What is the command to get the HWM?
-
Assuming that both tables are analyzed:
Code:
--Find the total number of blocks allocated to the segment (Thus the HWM)
SELECT BLOCKS
FROM DBA_SEGMENTS
WHERE SEGMENT_NAME = your_table;
-- To find the number of blocks that are empty (how many blocks your are reading during a FTS for no reason)
SELECT EMPTY_BLOCKS
FROM DBA_TABLES
WHERE ]TABLE_NAME = your_table;
Someone else is bound to come up with simpler apporaches for this (possibly using DBMS_SPACE or something), but this will work just fine.
Assistance is Futile...
-
Turn on tracing: and run tkprof against the trace output after running the two querries).
"alter session set sql_trace=true "
What is the o/s reporting for I/O stats during each querry?
-
check if preprod is doing FTS parallely.. on the other hand in prod serially..
Abhay
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|