troubleshooting Question

SQL Tuning - High CPU value during fetch

Avatar of medi911
medi911 asked on
Oracle Database
3 Comments1 Solution1488 ViewsLast Modified:
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
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                     110        0.00          0.00
  SQL*Net message from client                   110        0.00          0.06
ASKER CERTIFIED SOLUTION
Computer101

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros