Home » RDBMS Server » Performance Tuning » Automatic SQL Tuning (oracle 10.2.0.4 - Windows server 2003)
Automatic SQL Tuning [message #601998] Thu, 28 November 2013 07:02 Go to next message
muktha_22
Messages: 527
Registered: December 2009
Senior Member
Hi All,


I am doing Automatic SQL Tuning though Creating Tuning Task for a particular SNAP SHOTS.
DECLARE
  l_cursor  DBMS_SQLTUNE.sqlset_cursor;
BEGIN
  OPEN l_cursor FOR
    SELECT VALUE(p)
    FROM   TABLE (DBMS_SQLTUNE.select_workload_repository (
                    2620,  -- begin_snap
                    2627,  -- end_snap
                    NULL, -- basic_filter
                    NULL, -- object_filter
                    NULL, -- ranking_measure1
                    NULL, -- ranking_measure2
                    NULL, -- ranking_measure3
                    NULL, -- result_percentage
                    10)   -- result_limit
                  ) p;

  DBMS_SQLTUNE.load_sqlset (
    sqlset_name     => 'test_sql_tuning_set',
    populate_cursor => l_cursor);
END;


In the above case, all the SQL statements for that snapshot period will be examined.
But, My requirement is, I want to do this for a Particular USER's. But also SNAP SHOT should be included.

Kindly help

Regards.
Muktha.
Re: Automatic SQL Tuning [message #602006 is a reply to message #601998] Thu, 28 November 2013 10:21 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
After you capture the monitoring statements, query V$SQL_MONITOR and join it with V$SESSION using SQL_ID and use the USERNAME from V$SESSION.
Re: Automatic SQL Tuning [message #602033 is a reply to message #602006] Fri, 29 November 2013 00:39 Go to previous messageGo to next message
muktha_22
Messages: 527
Registered: December 2009
Senior Member
Hi Lalit,


Thank you so much for the reply.

But, because I am using 10g version, the V$SQL_MONITOR query is not available.
Is there any method to display reports directly/indirectly from AWR for a specific user?

Regards
Muktha
Re: Automatic SQL Tuning [message #602043 is a reply to message #602033] Fri, 29 November 2013 01:20 Go to previous message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Sorry, I am not able to understand your exact requirement.

When you say AWR report, it is pulled from the following views :
SELECT * FROM V$ACTIVE_SESSION_HISTORY; -- Displays the active session history (ASH) sampled every second.
SELECT * FROM V$METRIC; -- Displays metric information.
SELECT * FROM V$METRICNAME; -- Displays the metrics associated with each metric group.
SELECT * FROM V$METRIC_HISTORY; -- Displays historical metrics.
SELECT * FROM V$METRICGROUP; -- Displays all metrics groups.
SELECT * FROM DBA_HIST_ACTIVE_SESS_HISTORY; -- Displays the history contents of the active session history.
SELECT * FROM DBA_HIST_BASELINE; -- Displays baseline information.
SELECT * FROM DBA_HIST_DATABASE_INSTANCE; -- Displays database environment information.
SELECT * FROM DBA_HIST_SNAPSHOT; -- Displays snapshot information.
SELECT * FROM DBA_HIST_SQL_PLAN; -- Displays SQL execution plans.
SELECT * FROM DBA_HIST_WR_CONTROL; -- Displays AWR settings.


For example, in V$ACTIVE_SESSION_HISTORY view, you will have details for each session. You could know the session is under which USERNAME by quering V$SESSION view by joining SESSION_ID and SID.

Read this
Previous Topic: Need help in tuning query
Next Topic: Need help in tuning query
Goto Forum:
  


Current Time: Thu Mar 28 04:54:32 CDT 2024