-
query runs slow in the prod and fast in the test
Hi guys,
I have a wird problem.
A query runs fast in the test (6 sec.) environment and slow in the production environment (27 sec.).
Both of the envs are similar regarding data. I copied the prod into the test before i examined the query.
Both hosts: HP-UX B.11.00 U 9000/800
Oracle version: 8.1.7.3
Following the tkprof output results in the production env:
==========================================================
SELECT DEPT_ID,DEPT_NAME,ORDER_ID,SITE_NAME,START_DATE,
START_TIME,END_DATE, END_TIME,WORK_DESC,CONTINUOUS,PRIORITY_NAME,
STATUS_ID,STATUS_NAME,WORK_RISK,
MATE,PARALLEL
FROM
ORDERS_LIST WHERE DEPT_ID>=0 AND ORDER_ID>0
order by ORDERS_LIST.ORDER_ID
DESC
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.35 0.40 0 0 0 0
Execute 1 0.01 0.00 0 0 0 0
Fetch 1103 1.26 27.10 1642 775 228 16516
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1105 1.62 27.50 1642 775 228 16516
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 225 (WEB_HAZ)
Rows Row Source Operation
------- ---------------------------------------------------
16516 SORT ORDER BY
16516 VIEW ORDERS_LIST
16516 UNION-ALL
6268 HASH JOIN
39 TABLE ACCESS FULL TDEPTS
6268 HASH JOIN
23 TABLE ACCESS FULL TSTATUS
6268 HASH JOIN
2 TABLE ACCESS FULL TPRIORITYS
6268 HASH JOIN OUTER
6268 HASH JOIN OUTER
6268 HASH JOIN
1854 TABLE ACCESS FULL TWORKS
16516 TABLE ACCESS FULL ORDERS
321 INDEX FULL SCAN (object id 74900)
282 TABLE ACCESS FULL TSITES
10248 HASH JOIN
39 TABLE ACCESS FULL TDEPTS
10248 HASH JOIN
23 TABLE ACCESS FULL TSTATUS
10248 HASH JOIN
2 TABLE ACCESS FULL TPRIORITYS
10248 HASH JOIN OUTER
10248 HASH JOIN OUTER
10248 TABLE ACCESS FULL ORDERS
282 TABLE ACCESS FULL TSITES
321 INDEX FULL SCAN (object id 74900)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
16516 SORT (ORDER BY)
16516 VIEW OF 'ORDERS_LIST'
16516 UNION-ALL
6268 HASH JOIN
39 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'TDEPTS'
6268 HASH JOIN
23 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'TSTATUS'
6268 HASH JOIN
2 TABLE ACCESS GOAL: ANALYZED (FULL) OF
'TPRIORITYS'
6268 HASH JOIN (OUTER)
6268 HASH JOIN (OUTER)
6268 HASH JOIN
1854 TABLE ACCESS GOAL: ANALYZED (FULL) OF
'TWORKS'
16516 TABLE ACCESS GOAL: ANALYZED (FULL) OF
'ORDERS'
321 INDEX GOAL: ANALYZED (FULL SCAN) OF
'ORDERS_CONNECT_PK' (UNIQUE)
282 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'TSITES'
10248 HASH JOIN
39 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'TDEPTS'
10248 HASH JOIN
23 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'TSTATUS'
10248 HASH JOIN
2 TABLE ACCESS GOAL: ANALYZED (FULL) OF
'TPRIORITYS'
10248 HASH JOIN (OUTER)
10248 HASH JOIN (OUTER)
10248 TABLE ACCESS GOAL: ANALYZED (FULL) OF
'ORDERS'
282 TABLE ACCESS GOAL: ANALYZED (FULL) OF
'TSITES'
321 INDEX GOAL: ANALYZED (FULL SCAN) OF
'ORDERS_CONNECT_PK' (UNIQUE)
prod init*.ora parameters
==========================
db_name = "prod3"
open_cursors = 300
max_enabled_roles = 30
db_block_buffers = 2048
shared_pool_size = 52428800
large_pool_size = 614400
java_pool_size = 20971520
log_checkpoint_interval = 10000
log_checkpoint_timeout = 1800
processes = 150
log_buffer = 163840
db_block_size = 8192
job_queue_processes = 20
job_queue_interval = 60
distributed_transactions = 10
open_links = 4
compatible = "8.1.7"
sort_area_size = 65536
sort_area_retained_size = 65536
Following the results of the tkprof output in test env:
=======================================================
SELECT DEPT_ID,DEPT_NAME,ORDER_ID,SITE_NAME,START_DATE,
START_TIME,END_DATE, END_TIME,WORK_DESC,CONTINUOUS,PRIORITY_NAME,
STATUS_ID,STATUS_NAME,WORK_RISK,
MATE,PARALLEL
FROM
ORDERS_LIST WHERE DEPT_ID>=0 AND ORDER_ID>0
order by ORDERS_LIST.ORDER_ID
DESC
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.56 0.62 0 2 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1101 1.70 5.56 2378 741 156 16498
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1103 2.26 6.18 2378 743 156 16498
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 512
Rows Row Source Operation
------- ---------------------------------------------------
16498 SORT ORDER BY
16498 VIEW ORDERS_LIST
16498 UNION-ALL
6260 HASH JOIN
39 TABLE ACCESS FULL TDEPTS
6260 HASH JOIN
23 TABLE ACCESS FULL TSTATUS
6260 HASH JOIN
2 TABLE ACCESS FULL TPRIORITYS
6260 HASH JOIN OUTER
6260 HASH JOIN OUTER
6260 HASH JOIN
1851 TABLE ACCESS FULL TWORKS
16498 TABLE ACCESS FULL ORDERS
323 INDEX FULL SCAN (object id 262169)
282 TABLE ACCESS FULL TSITES
10238 HASH JOIN
39 TABLE ACCESS FULL TDEPTS
10238 HASH JOIN
23 TABLE ACCESS FULL TSTATUS
10238 HASH JOIN
2 TABLE ACCESS FULL TPRIORITYS
10238 HASH JOIN OUTER
10238 HASH JOIN OUTER
10238 TABLE ACCESS FULL ORDERS
282 TABLE ACCESS FULL TSITES
323 INDEX FULL SCAN (object id 262169)
test init*.ora parameters:
======================
db_name = "test1"
open_cursors = 1000
max_enabled_roles = 140
db_block_buffers = 2048
shared_pool_size = 52428800
large_pool_size = 614400
java_pool_size = 20971520
log_checkpoint_interval = 10000
log_checkpoint_timeout = 1800
processes = 150
log_buffer = 163840
db_block_size = 8192
job_queue_processes = 4
job_queue_interval = 60
distributed_transactions = 10
open_links = 4
compatible = "8.1.7"
sort_area_size = 65536
sort_area_retained_size = 65536
query_rewrite_enabled = true
query_rewrite_integrity = trusted
Any recommendations?
Thanks in advance,
Nir
-
Do you have the same number of extents in both databases ?
Maybe the segments in the "test" database were reorganized during import/export for example ...
Hope this help !
-
How busy is your production?
Do you have a lot of read waits on prod compare to test?
Tomaž
"A common mistake that people make when trying to design something completely
foolproof is to underestimate the ingenuity of complete fools" - Douglas Adams
-
Hi TomazZ,
Following the situation of the wait events in the databases:
production:
=============
prod3>
SELECT time, count, class
2 FROM V$WAITSTAT
3 ORDER BY time,count
4 ;
TIME COUNT CLASS
---------- ---------- ------------------
0 0 sort block
0 0 save undo block
0 0 save undo header
0 0 free list
0 0 bitmap block
0 0 unused
0 0 system undo block
0 0 system undo header
0 0 bitmap index block
0 0 extent map
10 9 segment header
207 3142 undo header
1351 7896 undo block
447550 88476 data block
14 rows selected.
htamga,
There are no extents problem. The bigest segment in the prod db has 4 extents.
test
=====
test1>
SELECT time, count, class
2 FROM V$WAITSTAT
3 ORDER BY time,count
4 ;
TIME COUNT CLASS
---------- ---------- ------------------
0 0 sort block
0 0 save undo block
0 0 save undo header
0 0 free list
0 0 bitmap block
0 0 unused
0 0 system undo block
0 0 undo block
0 0 system undo header
0 0 bitmap index block
0 0 extent map
0 1 segment header
48 5 undo header
355 50 data block
14 rows selected.
-
Those the tables in both environnement has about the same number of rows?
Analyse the tables in the environment that is slow.
-
Hi Steve,
All the tables in both of the environment have the same rows.
In addition,all the tables are analyzed everynight at 24:00.
Nir
-
Sorry, I was thinking more of session waits or statistics.
Well, actually I was thinking of you production being very busy (especially on physical reads), hence the slower query execution.
Tomaž
"A common mistake that people make when trying to design something completely
foolproof is to underestimate the ingenuity of complete fools" - Douglas Adams
-
Contension? check the undo header- if this value to the total request is more than 1% then try to increase the number of rbs
Badrinath
There is always a better way to do the things.
-
Hi guys,
Eventually, i've solved the problem by increasing the parameter SORT_AREA_SIZE from 65536 (default) to 1048576.
After the increasing,the query ran fast.
Thanks anyway,
Nir
-
How can it happen? Your environments are the same and you had to increase the sort area
Then probably there were other users using the system and also there were a lot of disk i/o happening to your temp tablespace
There is always a better way to do the things.
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
|