Slowness in consultation and analysis of trace

Tuning de Banco, Tuning de SQL, Ferramentas de tuning

Poston Mon, 29 Oct 2012 4:19 pm

Good afternoon everyone!

This is my first topic here on the forum and I need your help ...

I'm having a problem of slowness in a gigantic query with 3 unions, already checked that the second query that causes slowness, I ran a trace of the query and checked that there is a join that bringing many records and with the method of access TABLE ACCESS BY INDEX ROWID.I wonder if this method of access is really suitable for connection of tables that contains many records as is the case of my (approx. 100,000 in both tables) and if there is other and modify this form outa? -NOTE: the puzzling is that this query slow é só on this client in the other 100 to extremely quickly and in my environment too ... descofio that may be the performance of their bank. -A portion of my trace:
Code: Select all
Rows     Row Source Operation
-------  ---------------------------------------------------
33           TABLE ACCESS BY INDEX ROWID ATENDIME (cr=3293923 pr=0 pw=0 time=55781817 us)
17988299            INDEX RANGE SCAN ATENDIME_V_INTERNADOS (cr=87843 pr=0 pw=0 time=35978810 us)(object id 72840)

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.10       0.10          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1     56.68      56.10          0    3310107          0          61
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3     56.79      56.21          0    3310107          0          61


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       91      0.28       0.28          0          0          0           0
Execute     89      0.00       0.00          0          0          0          83
Fetch        5     59.91      59.30          0    3494205          0          74
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      185     60.20      59.60          0    3494205          0         157
if anyone is beast on Bank performance and sqlTuning ai of a hand, because I do not know where to move ...

Poston Mon, 29 Oct 2012 5:01 pm

Access by rowid is performed to retrieve the data of a record, usually when an index is used to access the records.
This access is fast, and it should not cause problems.

What you would need to parse the explain plan are how joins are running (nested loops, hash join, merge) and how the data is being accessed (full scan, full scan, via index index via skip scan, via index range scan or index via unique scan-generally the slowest to the fastest, depending on the situation).

Knowing how the tables involved are populated, it is possible to analyze whether the plan is acceptable or not (a range scan on an index that will return half of the records in the table, for example, is not very advantageous) also check the order in which the jois happen.
For nested loops, for example, the first table displayed in the plan is the table that will be used to fetch the records in nested tables. In this way, it is often preferable in these situations that the mother is the one with the greatest number of records filtered by conditions in the where clause.
Location: Londrina - PR

Rafael O. Genaro

Poston Mon, 29 Oct 2012 7:08 pm

Vlw man, vo of a parsed in my plan and in the query, see where can I move ....

Return to DBA Tuning

Who is online

Users browsing this forum: No registered users and 2 guests