medi911
asked on
SQL Tuning - High CPU value during fetch
One of our applications takes an unusual long time to run (the application scans accounts receivables for every fiscal month in the past). After running a session trace it turns out that most time is consumed during the row fetch operation. The statement in question is executed 108 times and it take over a minute cumulative returning one row. Please see TKPROF excerpt below. What could cause that the fetch operation is so CPU bound and which steps do I have to take to improve performance?
Thanks in advance.
select invoice_id, currency_id, total_amount, invoice_date from receivable txn
where type = 'M' and txn.customer_id = :1
and entity_id = :2
and invoice_date >= :3
and invoice_date <= :4
and status != 'X'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 108 0.03 0.02 0 0 0 0
Fetch 109 63.32 63.31 0 2041094 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 218 63.35 63.33 0 2041094 0 1
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 115 (SYSADM)
Rows Execution Plan
------- --------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
0 FILTER
0 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF 'RECEIVABLE'
(TABLE)
0 INDEX MODE: ANALYZED (RANGE SCAN) OF 'X_RECEIVABLE_1'
(INDEX)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
--------------------------
SQL*Net message to client 110 0.00 0.00
SQL*Net message from client 110 0.00 0.06
Thanks in advance.
select invoice_id, currency_id, total_amount, invoice_date from receivable txn
where type = 'M' and txn.customer_id = :1
and entity_id = :2
and invoice_date >= :3
and invoice_date <= :4
and status != 'X'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 108 0.03 0.02 0 0 0 0
Fetch 109 63.32 63.31 0 2041094 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 218 63.35 63.33 0 2041094 0 1
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 115 (SYSADM)
Rows Execution Plan
------- --------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
0 FILTER
0 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF 'RECEIVABLE'
(TABLE)
0 INDEX MODE: ANALYZED (RANGE SCAN) OF 'X_RECEIVABLE_1'
(INDEX)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
--------------------------
SQL*Net message to client 110 0.00 0.00
SQL*Net message from client 110 0.00 0.06
ASKER
Thanks for the reply. I was finally able to identify the problem by scanning through the raw trace file. Tuns out that bind variables :1 and :2 in the query are passed as CHAR and not as VARCHAR2 which apparently confuses the query optimizer (the exact mechanics are beyond my knowledge). Unfortunately I don't have access to the application source code so I guess I'm only left with the option to contact the vendor and request to change it.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
First, ask your dba to set the parameter pga_aggregate_target=1g. Tell the Dba to demonstrate this via email that has been done.
Also and index based on the fields after the "where" clause must be created.
Ask your Dba to create an index on the table reaceivable based on the fileds type +customer+invoice_date+sta
Tell the dba to run "statisitics" on the table and indexes, running the dbms stat on the table with cascade=>true.
Now, run your query.
select invoice_id, currency_id, total_amount, invoice_date from receivable txn
where type = 'M' and txn.customer_id = :1
and entity_id = :2
and invoice_date >= :3
and invoice_date <= :4
and status != 'X'