Oracle SQL for a Table

I do not believe that you can determine a SQL instruction against a particular tablespace. However, we look at whether the SQL code will contribute to long wait times but,  you should never be involved in looking at the code.  Both the AWR report and a Grab file would be required to determine whether the code is the cause of the problem.

Check the Direct Path Read and Direct Path Read Temp found in the Top 5 Tierd Foreground Events

Top 5 Timed Foreground Events

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Avg

wait   % DB

Event Waits     Time(s)   (ms)   time Wait Class

------------------------------ ------------ ----------- ------ ------ ----------

direct path read 9,540,466     140,731 15   72.2 User I/O

DB CPU 25,097          12.9

db file sequential read 2,383,416      18,776 8    9.6 User I/O

read by other session 1,916,289 2,689      1    1.4 User I/O

log file sync 45,221 535     12     .3 Commit

•TT  This is a classic case of having to tune Oracle

•     When a session is reading buffers from disk directly into the PGA (opposed to the buffer cache in SGA), it waits on this event.  If the I/O subsystem does not support asynchronous I/Os, then each wait corresponds to a physical read request.

Time Model Statistics DB/Inst: RREP/RREP  Snaps: 36966-36968

-> Total time in database user-calls (DB Time): 194881.3s

-> Statistics including the word "background" measure background process

   time, and so do not contribute to the DB time statistic

-> Ordered by % or DB time desc, Statistic name

Statistic Name Time (s) % of DB Time

-----------------------------------------------------------------------------------------

sql execute elapsed time 194,019.0         99.6

SQL ordered by Elapsed Time            DB/Inst: RREP/RREP  Snaps: 36966-36968

-> Resources reported for PL/SQL code includes the resources used by all SQL

statements called by the code.

-> % Total DB Time is the Elapsed Time of the SQL statement divided

into the Total Database Time multiplied by 100

-> Total DB Time (s):         194,881

-> Captured SQL account for   93.8% of Total

-> Total DB Time (s):         194,881

-> Captured PL/SQL account for   89.8% of Total

Elapsed CPU Elap per  % Total

Time (s)   Time (s)  Executions   Exec (s)  DB Time    SQL Id

---------- ---------- ------------ ---------- ------- -------------

148,426 10,263 4,509       32.9    76.2 2hpnjnra1n5wf

Causes

This happens in the following situations:

•The sorts are too large to fit in memory and some of the sort data is written out directly to disk. This data is later read back in, using direct reads.

•Parallel slaves are used for scanning data.

•The server process is processing buffers faster than the I/O system can return the buffers. This can indicate an overloaded I/O system

Recommendations

RUN GATHER STATISTICS FOR THE APPLICATION'S SCHEMA'S AT LEAST ONCE PER WEEK AT 30% OR MORE (EASY AND FREE)

SQL TUNING (MODERATE AND FREE)

THE DATABASE IS WEIGHTED FOR "READ" I/O

- ADD MORE DISKS FOR BETTER READ I/O ....OR (SOME COST/GOOD)

- USE EFDS (MORE COSTLY /BEST)

- USE ASM (FREE / MODERATE DBA COMPLEXITY)

NICK

Thanks Nick for your response!

I tried to find the top busy SQL by different orders, and clicked in SQL ID, then I can see the full text of the SQL with table details, not sure if it is the right way?

hi ,

in AWR report, you can always find the exact SQL statement which causes the perfformance issue ,with by TOP 5 or 10 SQL statement or by wait event. by reading the SQL, you will know which table is has accessed, in fact, from there, you can work with apps team to fine-tune them.

alan

Simon,

I agree with Nick. It is really hard to locate the table by decode SQL only. The SQL text should be used to troubleshoot whether the code is the cause of the problem. Like my experience on AWR, we ever dig out one event "SQL*Net message from dblink" at top of wait event and then go to SQL text to locate where dblink runs. I believe it is more efficient way to do searching, especially for complex syntax like below. Hope it helpful.

Lu