asked on

ORACLE TUNING

WE HAVE AN APP USING ORACLE 9 WHICH HAS BEEN RUNNING FOR 3/4 YEARS FINE
NOW USERS ARE EXPERIENCING SLOW RESPONSES INTERMITTENTLY
WE THINK EITHER
ORACLE DATABASE NEEDS TO RUN SOME TUNING PROGRAM "COMPUTE STATISTICS??" AS NOW DATA HAS EXPANDED TO A POINT TO MAKE ORACLE INEFFICIENT.CAN SOMEONE TELL US MORE INFO ON THIS

OR
USER VIA A USER REPORTING TOOL IS HITTING TABLE WITH INEFFICIENT QUERY.
WE HAVE FOUND THE V$SQL TABLE WHICH LISTS QUERIES AND TIME TAKEN. HOWEVER CAN WE ALSO FIND THE USER WHO HAS RUN THIS QUERY.

ALSO NOTE WE USE mlogs TO PROVIDE AN AUDIT OF MOST OF OUR TABLES
THANKS IN ANTISCIPATION

ASKER CERTIFIED SOLUTION

MohanKNair

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Acton Wang

>> HOWEVER CAN WE ALSO FIND THE USER WHO HAS RUN THIS QUERY.

use this to see which sql user is currently using:

select s.username,l.sql_text
from v$session s, v$sql l
where s.sql_address = l.address and s.sql_hash_value = l.hash_value
/

For all sqls user has executed, you have to either (1) audit (2) enable user trace.

Acton

SOLUTION

Acton Wang

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

johnsone

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial