Hi,
Our Production database is going slow, and we don't know why.
We have trace this simple query over Production and Test environments. It takes more time in Production that in Test although the number of rows of the table COGS_TCORPORATIVOS_1 are similar in both environments and Production BD has a lot more resources (CPU, memory,..) than Test DB.
Can be this possible? I mean, although Production BD has a lot more resources (CPU, memory,..) than Test DB, it can be slower because it has more work load. How can I know if this is the cause, or it is any other?
PRODUCTION
==========
TKPROF: Release 12.1.0.2.0
********************************************************************************
SQL ID: 0fgshk6tqjuty Plan Hash: 468000025
SELECT *
FROM
COTA.COGS_TCORPORATIVOS_1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.02 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 188386 7.40 7.96 1504 188396 0 188385
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 188388 7.43 7.99 1504 188396 0 188385
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS (recursive depth: 1)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
188385 188385 188385 TABLE ACCESS FULL COGS_TCORPORATIVOS_1 (cr=188396 pr=1504 pw=0 time=3963966 us cost=45 size=9230865 card=188385)
********************************************************************************
TEST
====
TKPROF: Release 12.1.0.2.0
********************************************************************************
SQL ID: 0fgshk6tqjuty Plan Hash: 228364047
SELECT *
FROM
COTA.COGS_TCORPORATIVOS_1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 188360 1.14 1.16 0 188371 0 188359
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 188362 1.14 1.16 0 188371 0 188359
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS (recursive depth: 1)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
188359 188359 188359 TABLE ACCESS FULL COGS_TCORPORATIVOS_1 (cr=188371 pr=0 pw=0 time=602601 us cost=3 size=159 card=3)
********************************************************************************
Thanks in advance.
Regards, Jose Luis.