Slitness in consultation and trace analysis

[vvp0]

Good afternoon everyone!

This is my first topic here in the forum and I need the help of you ...

I have a slowness problem in a gigantic consultation with 3 unions , I already checked that the second consultation that causes slow, I made a trace of the query and verified that there is a join that is bringing many records and with the Access Access Access Rowid access method of knowing if this access method is actually the Suitable for connecting tables containing many records such as é the case of mine (approx 100,000 in the two tables) and if there is another and output modifying this? -
Note: The intriguing is that this consultation só is slow in this client in the other 100 to extremely fast and in my environment also ... EXCOMPOSED that it can be the performance of their bank. -

Look at a part of my trace:

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 
 
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS 
 
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 someone is beast in bank performance and sqltuning there, because not I know more where to move ...

[rogenaro]

Rowid access is performed to retrieve data from a record, usually when an index is used to access the records.
This access is fast, and should not cause problems.

What you would need to analyze on Explain Plan are like joins are running (Nested Loops, Hash Join, Merge) and how the data is being accessed (Full Scan, Index via Full Scan, Index via Skip Scan, Index via Range Scan or Index via Unique Scan - Overall the slower for the fastest, depending on the clear situation).

Knowing how the tables involved are populated, it is possible to analyze if the plan is acceptable or not (a scan range on an index that will return half of the records of the table, for example, is not much Advantageous)

Check the order in which the joints happen.
For Nested Loops, for example, the first table displayed in the plane is the table that will be used to seek the records in the nested tables. In this way, it is usually preferable in these situations that the mother table is one with the highest number of records filtered by the WHERE clause conditions.

[vvp0]

Vlw guy, from one analyzed in my plan and in the consultation, where I can move ....