Home » RDBMS Server » Performance Tuning » Is there a way to monitor historical locks in Standard Edition (11.2.0.3 SE, 11.2.0.3 EE,)
Is there a way to monitor historical locks in Standard Edition [message #624251] Fri, 19 September 2014 09:43 Go to next message
kytemanaic
Messages: 55
Registered: February 2009
Member
Hi,

here's my initialization

as sysdba
CREATE TABLESPACE test DATAFILE '/u02/app/oracle2/oradata/ORCLSE/test.db' SIZE 6M AUTOEXTEND ON;


as sysdba
CREATE user test IDENTIFIED BY test DEFAULT TABLESPACE test TEMPORARY TABLESPACE temp QUOTA UNLIMITED ON test;



as sysdba

GRANT
  Create SESSION,
  Alter  SESSION,
  Create TABLE
TO
  limited_role;

GRANT limited_role TO test;


as test

--insert test data
create table test(a int primary key);
insert into test (a) VALUES (1);
insert into test (a) VALUES (2);
insert into test (a) VALUES (3);
insert into test (a) VALUES (4);
insert into test (a) VALUES (5);

insert into test (a) VALUES (6);
insert into test (a) VALUES (7);
insert into test (a) VALUES (8);

COMMIT;


here's what happen in the standard edition session 1


TEST@ORCLSE>SELECT sys_context('userenv','sid') FROM dual;

SYS_CONTEXT('USERENV','SID')
----------------------------------------------------------

13

TEST@ORCLSE>update test set a=9 where a=1;

1 row updated.

TEST@ORCLSE>



session 2 in standard edition


TEST@ORCLSE>SELECT sys_context('userenv','sid') from dual;

SYS_CONTEXT('USERENV','SID')
-------------------------------------------------------------

71

TEST@ORCLSE>update test set a=10 where a=1;



standard edition as sysdba


SYS@ORCLSE>ed
Wrote file afiedt.buf

  1  SELECT  distinct a.sql_id ,a.inst_id,a.blocking_session,a.blocking_session_
serial#,a.user_id,s.sql_text,a.module
  2  FROM  GV$ACTIVE_SESSION_HISTORY a  ,gv$sql s
  3  where a.sql_id=s.sql_id
  4  and blocking_session is not null
  5  and a.user_id <> 0
  6* and a.sample_time > sysdate - 30/24/60
SYS@ORCLSE>/

no rows selected

SYS@ORCLSE>SELECT COUNT(*) FROM GV$ACTIVE_SESSION_HISTORY;

  COUNT(*)
----------
         0

SYS@ORCLSE>SELECT * FROM v$version;

BANNER
-----------------------------------------------------------

Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production




as can be observed no historical locks can be seem.

but however if I'm using Enterprise Edition, historical locks can be retrieved

enterprise edition session 1

TEST@ORCLEE>SELECT sys_context('userenv','sid') FROM dual;

SYS_CONTEXT('USERENV','SID')
----------------------------------------------------------

134

TEST@ORCLEE>update test set a=9 where a=1;

1 row updated.

TEST@ORCLEE>



enterprise edition session 2


TEST@ORCLEE>SELECT sys_context('userenv','sid') from dual;

SYS_CONTEXT('USERENV','SID')
----------------------------------------------------------

193

TEST@ORCLEE>update test set a=10 where a=1;



enterprise edition as sysdba


SYS@ORCLEE>SELECT * FROM v$version;

BANNER
--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SYS@ORCLEE>ed
Wrote file afiedt.buf

  1  SELECT  distinct a.sql_id ,a.inst_id,a.blocking_session,a.blocking_session_
serial#,a.user_id,s.sql_text,a.module
  2  FROM  GV$ACTIVE_SESSION_HISTORY a  ,gv$sql s
  3  where a.sql_id=s.sql_id
  4  and blocking_session is not null
  5  and a.user_id <> 0
  6* and a.sample_time > sysdate - 30/24/60
SYS@ORCLEE>/

SQL_ID           INST_ID BLOCKING_SESSION BLOCKING_SESSION_SERIAL#    USER_ID
------------- ---------- ---------------- ------------------------ ----------
SQL_TEXT
--------------------------------------------------------------------------------

MODULE
------------------------------------------------
bgaaky9fxur65          1              134                     1058         98
update test set a=10 where a=1
SQL*Plus


SYS@ORCLEE>



i.e. there's record in GV$ACTIVE_SESSION_HISTORY.

from http://docs.oracle.com/cd/E11882_01/license.112/e47877/editions.htm#DBLIC116 and http://docs.oracle.com/cd/E11882_01/server.112/e25513/dynviews_1007.htm#REFRN30299

no where does it says that Standard edition cannot track historical data.


so is there a way to check for historical lock in Standard Edition? If yes would love to know how to retrieve historical lock in Standard Edition.

thanks a lot!
Re: Is there a way to monitor historical locks in Standard Edition [message #624252 is a reply to message #624251] Fri, 19 September 2014 09:48 Go to previous message
John Watson
Messages: 7259
Registered: January 2010
Location: Global Village
Senior Member
Active session history requires Enterprise Edition plus the diagnostocs pack. If you have not licensed these, you must disable them:

orclz>
orclz> sho parameter pack

NAME                                 TYPE       VALUE
------------------------------------ ---------- --------------------
control_management_pack_access       string     DIAGNOSTIC+TUNING
orclz> select count(*) from v$active_session_history;

  COUNT(*)
----------
       470

orclz> alter system set control_management_pack_access=none;

System altered.

orclz> select count(*) from v$active_session_history;

  COUNT(*)
----------
         0

orclz>
Previous Topic: Query requires tuning
Next Topic: Db servers capacity
Goto Forum:
  


Current Time: Tue Feb 20 02:31:41 CST 2018

Total time taken to generate the page: 0.02715 seconds