Friday, February 22, 2013

Oracle Enterprise Manager (Example 1)

I decided it was time I started looking at Oracle Enterprise Manager. Here is the performance tab for an Oracle 11.2.0.1.0 test database. It does not look very busy as most people have gone home. As always, click on the images as necessary to enlarge them and bring them into focus:


In the red session below, I created a user in the database, connected as that user, created a table and left the session with an uncommitted transaction: 

SQL> conn / as sysdba
Connected.
SQL> create user andrew
  2  identified by reid
  3  default tablespace users
  4  quota unlimited on users
  5  /
 
User created.
 
SQL> grant create session, create table to andrew
  2  /
 
Grant succeeded.
 
SQL> conn andrew/reid
Connected.
SQL> create table andrews_table
  2  (col1 varchar2(10))
  3  /
 
Table created.
 
SQL> insert into andrews_table values ('ANDREW')
  2  /
 
1 row created.
 
SQL> commit
  2  /
 
Commit complete.
 
SQL> update andrews_table set col1 = 'BRIAN'
  2  /
 
1 row updated.
 
SQL>
 
Then in a different session in blue, I created another user, checked the time to compare it against the time line in the OEM window and tried to update the same row:
 
SQL> conn / as sysdba
Connected.
SQL> create user john
  2  identified by smith;
 
User created.
 
SQL> grant create session to john
  2  /
 
Grant succeeded.
 
SQL> grant update on andrew.andrews_table to john
  2  /
 
Grant succeeded.
 
SQL> select to_char(sysdate,'hh24:mi:ss')
  2  time_now from dual
  3  /
 
TIME_NOW
--------
18:05:15
 
SQL> conn john/smith
Connected.
SQL> update andrew.andrews_table
  2  set col1 = 'COLIN'
  3  /
 
The blue session had to wait because the red session had not committed its transaction. I left both sessions for a while. Then I looked at OEM again:


This showed that some kind of application wait had started around the time noted above (i.e. just after 1805 hours). I hovered over the word Application on the right of the screen:


Then I clicked on it and OEM took me to another screen. This showed me that user JOHN was waiting for a row lock (enq: TX - row lock contention). When I hovered over SQL ID, OEM even showed me the SQL he was trying to run:


 I went back to the red session, committed the transaction and noted the time:
 
SQL> update andrews_table set col1 = 'BRIAN'
  2  /
 
1 row updated.
 
SQL> commit
  2  /
 
Commit complete.
 
SQL>  select to_char(sysdate,'hh24:mi:ss')
  2  time_now from dual
  3  /
 
TIME_NOW
--------
18:41:16
 
SQL>
 
The update in the blue session completed:
 
SQL> update andrew.andrews_table
  2  set col1 = 'COLIN'
  3  /
 
1 row updated.
 
SQL>
 
I waited a bit longer then went back to the test database’s performance tab in OEM. The application wait had finished at the time noted above (i.e. around 1841 hours):

Wednesday, February 20, 2013

CONNECT_TIME

I had to limit a SQL*Plus session’s connection time today while I was setting up a new environment so I decided to document how I did it. The example below was run on an Oracle 11.2.0.2.7 database. First I connected as SYS and limited CONNECT_TIME to 1 minute in the DEFAULT profile: 

SQL> conn / as sysdba
Connected.
SQL> alter profile default
  2  limit connect_time 1
  3  /
 
Profile altered.
 
SQL>
 
Then I set RESOURCE_LIMIT to TRUE so that limits would be enforced:
 
SQL> alter system set resource_limit = true
  2  /
 
System altered.
 
SQL>
 
I created a user and gave it the DEFAULT profile. Then I connected as that user, looked at CONNECT_TIME in USER_RESOURCE_LIMITS then checked the time at 10 second intervals:
 
SQL> create user andrew
  2  identified by reid
  3  profile default
  4  /
 
User created.
 
SQL> grant create session to andrew
  2  /
 
Grant succeeded.
 
SQL> conn andrew/reid
Connected.
SQL> select limit from user_resource_limits
  2  where resource_name = 'CONNECT_TIME'
  3  /
 
LIMIT
----------------------------------------
1
 
SQL> select to_char(sysdate,'hh24:mi:ss')
  2  time_now from dual
  3  /
 
TIME_NOW
--------
17:31:40
 
SQL> host sleep 10
 
SQL> select to_char(sysdate,'hh24:mi:ss')
  2  time_now from dual
  3  /
 
TIME_NOW
--------
17:31:50
 
SQL> host sleep 10
 
SQL> select to_char(sysdate,'hh24:mi:ss')
  2  time_now from dual
  3  /
 
TIME_NOW
--------
17:32:00
 
SQL> host sleep 10
 
SQL> select to_char(sysdate,'hh24:mi:ss')
  2  time_now from dual
  3  /
 
TIME_NOW
--------
17:32:10
 
SQL> host sleep 10
 
SQL> select to_char(sysdate,'hh24:mi:ss')
  2  time_now from dual
  3  /
 
TIME_NOW
--------
17:32:20
 
SQL> host sleep 10
 
SQL> select to_char(sysdate,'hh24:mi:ss')
  2  time_now from dual
  3  /
 
TIME_NOW
--------
17:32:30
 
SQL> host sleep 10
 
Once the session had been connected for 1 minute, the next SQL statement failed and the session was terminated with an ORA-02399:
 
SQL> select to_char(sysdate,'hh24:mi:ss')
  2  time_now from dual
  3  /
select to_char(sysdate,'hh24:mi:ss')
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-02399: exceeded maximum connect time, you are
being logged off
ORA-02399: exceeded maximum connect time, you are
being logged off
 
SQL>
 
… and a subsequent attempt to run the SQL showed that the session was no longer connected:
 
SQL> select to_char(sysdate,'hh24:mi:ss')
  2  time_now from dual
  3  /
select to_char(sysdate,'hh24:mi:ss')
*
ERROR at line 1:
ORA-01012: not logged on
Process ID: 884
Session ID: 61 Serial number: 2851
 

Monday, February 18, 2013

Challenge Your Colleagues (No 1)

To set up this challenge, you need an Oracle test database. I used one running on Oracle 11.2.0.2.7 but this works on other versions too. First you need to create a user as follows:
 
SQL> create user scott identified by tiger
  2  /
 
User created.
 
SQL> grant create session,
  2  select any table,
  3  execute any procedure to scott
  4  /
 
Grant succeeded.
 
SQL>
 
Then you need to ensure that the database has the following initialization parameter set to TRUE:
 
SQL> l
  1  select value from v$parameter
  2* where name = 'O7_DICTIONARY_ACCESSIBILITY'
SQL> /
 
VALUE
------------------------------
TRUE
 
SQL>
 
Check that your colleagues will be able to connect to the database remotely using SQL*Plus:
 
SQL> conn scott/tiger@orcl
Connected.
SQL>
 
Then E-mail the challenge to them using the following text appropriately modified:
 
Dear Colleagues,
 
I would like to challenge you to reset the SYSTEM password in my test database, explain how you did it and tell me the new password. To solve this challenge, you must connect to the database from SQL*Plus as follows conn scott/tiger@orcl. Answers which involve connecting as any other user will not be allowed.
 
You have until 28th February (or some other date) to do this. All entries must be submitted by E-mail. A prize of a bar of chocolate / bottle of champagne (according to your budget) will be awarded to the first correct answer drawn at random.
 
Good luck
 
Andrew
 
Once the deadline has passed, send out the answer below, inserting the name of the actual winner at the end and award the prize:
 
Dear Colleagues,
 
User Scott had the SELECT ANY TABLE and EXECUTE ANY PROCEDURE system privileges:
 
SQL> conn scott/tiger@orcl
Connected.
SQL> select privilege from user_sys_privs
  2  /
 
PRIVILEGE
----------------------------------------
CREATE SESSION
SELECT ANY TABLE
EXECUTE ANY PROCEDURE
 
SQL>
 
The database had O7_DICTIONARY_ACCESSIBILITY set to TRUE:
 
SQL> conn scott/tiger@orcl
Connected.
SQL> l
  1  select value from v$parameter
  2* where name = 'O7_DICTIONARY_ACCESSIBILITY'
SQL> /
 
VALUE
------------------------------
TRUE
 
SQL>
 
This allowed him to reset the SYSTEM password as follows, which explains why we do not allow you to have the EXECUTE ANY PROCEDURE privilege:
 
SQL> CONN SCOTT/tiger@ORCL
Connected.
SQL> DECLARE
  2  USER_NO NUMBER;
  3  CHANGE_PASSWORD VARCHAR2(50) :=
  4  'ALTER USER SYSTEM IDENTIFIED BY NEW_PASSWORD';
  5  CURSOR_NO INTEGER;
  6  BEGIN
  7  SELECT USER_ID INTO USER_NO FROM ALL_USERS
  8  WHERE USERNAME = 'SYSTEM';
  9  CURSOR_NO:=SYS.DBMS_SYS_SQL.OPEN_CURSOR();
10  SYS.DBMS_SYS_SQL.PARSE_AS_USER
11  (CURSOR_NO,CHANGE_PASSWORD,DBMS_SQL.NATIVE,USER_NO);
12  SYS.DBMS_SYS_SQL.CLOSE_CURSOR(CURSOR_NO);
13  END;
14  /
 
PL/SQL procedure successfully completed.
 
SQL> CONN SYSTEM/NEW_PASSWORD@ORCL
Connected.
SQL> SHOW USER
USER is "SYSTEM"
SQL>
 
The winner of the challenge was John Smith.
 
Kind regards,
 
Andrew

Thursday, February 14, 2013

A Simple Test of V$SEGSTAT_NAME and V$SEGMENT_STATISTICS

This was tested on Oracle 11.2.0.2.7. V$SEGSTAT_NAME shows the names of various statistics which Oracle records. The SAMPLED column shows whether the figures are obtained by sampling or not: 

SQL> l
  1* select * from v$segstat_name
SQL> /
 
STATISTIC# NAME                           SAMPLED
---------- ------------------------------ -------
         0 logical reads                  YES
         1 buffer busy waits              NO
         2 gc buffer busy                 NO
         3 db block changes               YES
         4 physical reads                 NO
         5 physical writes                NO
         6 physical read requests         NO
         7 physical write requests        NO
         8 physical reads direct          NO
         9 physical writes direct         NO
        11 optimized physical reads       NO
        12 gc cr blocks received          NO
        13 gc current blocks received     NO
        14 ITL waits                      NO
        15 row lock waits                 NO
        17 space used                     NO
        18 space allocated                NO
        20 segment scans                  NO
 
18 rows selected.
 
SQL>
 
The statistics themselves are recorded in V$SEGMENT_STATISTICS:
 
SQL> desc v$segment_statistics
Name                       Null?    Type
-------------------------- -------- ------------------
OWNER                               VARCHAR2(30)
OBJECT_NAME                         VARCHAR2(30)
SUBOBJECT_NAME                      VARCHAR2(30)
TABLESPACE_NAME                     VARCHAR2(30)
TS#                                 NUMBER
OBJ#                                NUMBER
DATAOBJ#                            NUMBER
OBJECT_TYPE                         VARCHAR2(18)
STATISTIC_NAME                      VARCHAR2(64)
STATISTIC#                          NUMBER
VALUE                               NUMBER
 
SQL>
 
I thought the row lock waits statistic looked interesting so I decided to test it. First, in the red session below, I created a table, inserted a row into it, committed it then updated it:
 
SQL> conn andrew/reid
Connected.
SQL> create table lock_test
  2  (col1 varchar2(10))
  3  /
 
Table created.
 
SQL> insert into lock_test values ('ANDREW')
  2  /
 
1 row created.
 
SQL> commit
  2  /
 
Commit complete.
 
SQL> update lock_test
  2  set col1 = 'BRIAN'
  3  /
 
1 row updated.
 
SQL>
 
Then, in a separate session, shown in blue, I checked if there had been any row lock waits on the table:
 
SQL> conn / as sysdba
Connected.
SQL> select value from v$segment_statistics
  2  where owner = 'ANDREW'
  3  and object_name = 'LOCK_TEST'
  4  and statistic_name = 'row lock waits'
  5  /
 
     VALUE
----------
         0
 
SQL>
 
I started a third session, in green, viewed the contents of the LOCK_TEST table then tried to update it. As you might expect, this session had to wait for the red session above:
 
SQL> conn andrew/reid
Connected.
SQL> select col1 from lock_test
  2  /
 
COL1
----------
ANDREW
 
SQL> update lock_test
  2  set col1 = 'COLIN'
  3  /
 
I returned to the blue session and verified that the row lock waits figure for the table had increased by 1:
 
SQL> l
  1  select value from v$segment_statistics
  2  where owner = 'ANDREW'
  3  and object_name = 'LOCK_TEST'
  4* and statistic_name = 'row lock waits'
SQL> /
 
     VALUE
----------
         1
 
SQL>

Problem with utlrp?

I looked at how you could use utlrp to compile invalid objects in an earlier post: 

SQL> @utlrp
 
TIMESTAMP
----------------------------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN  2013-02-14 16:09:12
 
DOC>   The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC>   objects in the database. Recompilation time is proportional to the
DOC>   number of invalid objects in the database, so this command may take
DOC>   a long time to execute on a database with a large number of invalid
DOC>   objects.
DOC>
DOC>   Use the following queries to track recompilation progress:
DOC>
DOC>   1. Query returning the number of invalid objects remaining. This
DOC>      number should decrease with time.
DOC>         SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC>   2. Query returning the number of objects compiled so far. This number
DOC>      should increase with time.
DOC>         SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC>   This script automatically chooses serial or parallel recompilation
DOC>   based on the number of CPUs available (parameter cpu_count) multiplied
DOC>   by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC>   On RAC, this number is added across all RAC nodes.
DOC>
DOC>   UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC>   recompilation. Jobs are created without instance affinity so that they
DOC>   can migrate across RAC nodes. Use the following queries to verify
DOC>   whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC>   1. Query showing jobs created by UTL_RECOMP
DOC>         SELECT job_name FROM dba_scheduler_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC>   2. Query showing UTL_RECOMP jobs that are running
DOC>         SELECT job_name FROM dba_scheduler_running_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#
 
PL/SQL procedure successfully completed.
 
 
TIMESTAMP
----------------------------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END  2013-02-14 16:09:18
 
 
PL/SQL procedure successfully completed.
 
DOC> The following query reports the number of objects that have compiled
DOC> with errors (objects that compile with errors have status set to 3 in
DOC> obj$). If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#
 
OBJECTS WITH ERRORS
-------------------
                  0
 
DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#
 
ERRORS DURING RECOMPILATION
---------------------------
                          2
 
 
PL/SQL procedure successfully completed.
 
Invoking Ultra Search Install/Upgrade validation procedure VALIDATE_WK
Ultra Search VALIDATE_WK done with no error
 
PL/SQL procedure successfully completed.

SQL>

However, I recently had a problem with utlrp where it failed to recompile a package body which had been invalidated:
 
SQL> select object_type, status
  2  from dba_objects
  3  where owner = 'JLS'
  4  and object_name = 'PK_JULES'
  5  /
 
OBJECT_TYPE         STATUS
------------------- -------
PACKAGE             VALID
PACKAGE BODY        INVALID

SQL>

When I recompiled it manually, it had no errors. I will update this post once I understand the cause of the problem. In the meantime, if you have any idea what caused this to happen, please let me know:
 
SQL> alter package jls.pk_jules compile
  2  /
 
Package altered.
 
SQL> select object_type, status
  2  from dba_objects
  3  where owner = 'JLS'
  4  and object_name = 'PK_JULES'
  5  /
 
OBJECT_TYPE         STATUS
------------------- -------
PACKAGE             VALID
PACKAGE BODY        VALID
 
SQL>