Skip to content

Posts tagged ‘performance tuning’

Jul 25

How To Know If An Object (Table / Procedure / View /…) Is Currently Being Used 

APPLIES TO:

Oracle Database Exadata Express Cloud Service – Version N/A and later
Oracle Database Cloud Service – Version N/A and later
Oracle Database – Enterprise Edition – Version 10.2.0.5 and later
Oracle Database Cloud Schema Service – Version N/A and later
Oracle Database Exadata Cloud Machine – Version N/A and later
Information in this document applies to any platform.

GOAL

NOTE: In the images and/or the document content below, the user information and environment data used represents fictitious data from the Oracle sample or bulit-in schema(s), Public Documentation delivered with an Oracle database product or other training material.  Any similarity to actual environments, actual persons, living or dead, is purely coincidental and not intended in any manner.

The information is intended to help with situations where you want to know whether a certain object is being used, e.g.

1. If the objects is currently being used, e.g. by a DML statement, DDL on the objects will fail. How can you check it before executing the DDL command?

2. If a procedure “USER2.TEST_PROCEDURE” is being executed by user USER3 and owner USER2 wants to replace the procedure code using “create or replace procedure test_procedure ….”. How can you find the session that is running the procedure “USER2.TEST_PROCEDURE”?

SOLUTION

Situation 1

To check objects currently being used, e.g. by a DML statement, before executing a DDL command, you can query v$locked_object as in the example below.


Session A

SQL> connect user1/<PASSWORD>;
Connected.

SQL> drop table test;

Table dropped.

SQL> create table test (a number);

Table created.

SQL> insert into test values (1);

1 row created.



Session B

SQL> connect / as sysdba
Connected.

SQL> truncate table user1.test;
truncate table user1.test
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified
–> this is expected because user1.test is locked in Session A

SQL>
select do.OWNER, do.OBJECT_NAME, do.OBJECT_ID,
       lo.SESSION_ID, lo.ORACLE_USERNAME, lo.OS_USER_NAME,
       lo.PROCESS, lo.LOCKED_MODE
from   dba_objects do, v$locked_object lo
where  do.OBJECT_ID = lo.OBJECT_ID
and    do.OWNER = ‘USER1’
and    do.OBJECT_NAME = ‘TEST’;

OWNER    OBJECT_NAME  OBJECT_ID  SESSION_ID ORACLE_USERNAME
——– ———— ———- ———- ——————————
OS_USER_NAME         PROCESS        LOCKED_MODE
——————– ————– ———–
USER1    TEST            63754         1079 USER1
oracle               23013                    3



From above, you can see user1.test is locked by Oracle user ‘USER1’ with OS user name ‘oracle’.
Session id is 1079, OS process id is 23013.

In case you are running a RAC system, then the command should be changed to below to detect the resource holder and instance number of holder session:

SQL> select do.OWNER, do.OBJECT_NAME, do.OBJECT_ID,
lo.inst_id,lo.SESSION_ID, lo.ORACLE_USERNAME, lo.OS_USER_NAME,
lo.PROCESS, lo.LOCKED_MODE
from dba_objects do, gv$locked_object lo
where do.OBJECT_ID = lo.OBJECT_ID
and do.OWNER = ‘USER1’
and do.OBJECT_NAME = ‘TEST’;

OWNER OBJEC OBJECT_ID INST_ID SESSION_ID ORACLE_USERNAME OS_USER_NAME PROCESS LOCKED_MODE
—————————— —– ———- ———- ———- —————————— —————————— ———————— ———–
USER1 TEST 87450 1 52 USER1 oracle 7906 3


You can kill the session to release the lock.

SQL> select SID, SERIAL# from v$session where sid = 1079;

SID        SERIAL#
———- ———-
1079       663

SQL> alter system kill session ‘1079,663’;

System altered.



Then the DDL can be executed successfully:

SQL> truncate table user1.test;

Table truncated.

Situation 2

If a DDL statement on an object, e.g. create or replace procedure, is hanging because another user is executing the procedure, you can find the blocking session as in the example below.

Using 3 sessions:

  1. Session A: connect as sysdba
  2. Session B: connect as user user2/<PASSWORD>
  3. Session C: connect as user USER3/<PASSWORD>


Session A

SQL>
connect / as sysdba
create user user2 identified by user2;
grant connect, resource to user2;
create user USER3 identified by USER3;
grant connect, resource to USER3;
grant execute on dbms_lock to user2;
grant execute on dbms_lock to USER3;



Session B

SQL>
connect user2/<PASSWORD>

create or replace procedure user2_test_p1 is
begin
  null;
end;
/

grant execute on user2_test_p1 to USER3;



Session C

SQL>
connect USER3/<PASSWORD>
begin
  user2.user2_test_p1;
  dbms_lock.sleep(120);
end;
/



Session A

SQL>
connect / as sysdba

SQL>
select distinct ses.ksusenum sid, ses.ksuseser serial#,
       ob.kglnaobj obj_name, ob.KGLNAOWN obj_owner,
       ses.KSUUDNAM cur_user
from   x$kglpn pn, x$kglob ob, x$ksuse ses
where  ob.KGLNAOBJ=’USER2_TEST_P1′
and    (ob.KGLHDPMD <> 0
        or
        (ob.KGLHDPMD = 0 and ob.KGLHDLMD not in (0,1))
       )
and    ob.kglhdadr = pn.kglpnhdl
and    pn.kglpnuse = ses.addr;

SID        SERIAL#    OBJ_NAME             OBJ_OWNER       CUR_USER
———- ———- ——————– ————— ——————
1094       5909       USER2_TEST_P1          USER2             USER3

SQL>
select sql_text
from   v$sql a, v$session b
where  a.sql_id=b.sql_id
and    b.serial#=5909;

SQL_TEXT
——————————————————————————–
begin user2.user2_test_p1; dbms_lock.sleep(120); end;



If the DDL is already hanging due to a lock, you can follow below test case to find the lock details.

Session C

SQL> connect USER3/<PASSWORD>

SQL>
begin
  user2.user2_test_p1;
  dbms_lock.sleep(120);
end;
/



Session B

SQL> connect user2/<PASSWORD>

SQL> alter procedure user2_test_p1 compile;
     –> This is hanging



Session A

col obj_name format a30
col pin_cnt format 999
col pin_mode format 999
col pin_req format 999
col state format a30
col event format a30
col wait_time format 999999999
col seconds_in_wait format 999999999

SQL>
select distinct ses.ksusenum sid, ses.ksuseser serial#,
       ob.kglnaobj obj_name,
       pn.kglpncnt pin_cnt, pn.kglpnmod pin_mode, pn.kglpnreq pin_req,
       w.state, w.event, w.wait_time, w.seconds_in_Wait
from   x$kglpn pn, x$kglob ob, x$ksuse ses, v$session_wait w
where  pn.kglpnhdl in (select kglpnhdl from x$kglpn where kglpnreq > 0)
and    ob.kglhdadr = pn.kglpnhdl
and    pn.kglpnuse = ses.addr
and    w.sid = ses.indx
order by seconds_in_wait desc;

SID        SERIAL#    OBJ_NAME                       PIN_CNT PIN_MODE PIN_REQ
———- ———- —————————— ——- ——– ——-
STATE                          EVENT                          WAIT_TIME
—————————— —————————— ———-
SECONDS_IN_WAIT
—————
      1082       6765 USER2_TEST_P1                          3        2       0
WAITING                        PL/SQL lock timer                       0
             39

      1074       6060 USER2_TEST_P1                          0        0       3
WAITING                        library cache pin                       0
             27

SQL>
select sql_text
from   v$sql a, v$session b
where  a.sql_id=b.sql_id
and    b.serial#=6765;

SQL_TEXT
——————————————————————————–
begin user2_test_p1; dbms_lock.sleep(120); end;

SQL>
select sql_text
from   v$sql a, v$session b
where  a.sql_id=b.sql_id
and  b.serial#=6060;

SQL_TEXT
——————————————————————————–
alter procedure user2_test_p1 compile

In case you are running a RAC system, you can get the holder via hanganalyze trace:

eg:

SQL> conn / as sysdba
SQL> oradebug setmypid
SQL> oradebug -g all hanganalyze 3                       ============> this syntax only works for Oracle 11.2 or higher
Hang Analysis in /u01/app/oracle/diag/rdbms/abc/abc2/trace/abc2_diag_5180.trc

The trace file will show something like:

——————————————————————————-
Chain 1:
——————————————————————————-
Oracle session identified by:
{
instance: 2 (abc.abc2)
os id: 8829
process id: 44, oracle@xxx (TNS V1-V3)
session id: 57
session serial #: 23
}
is waiting for ‘library cache pin’ with wait info:
{
p1: ‘handle address’=0x8899a6b0
p2: ‘pin address’=0x8b45baf0
p3: ‘100*mode+namespace’=0x1559600010003
time in wait: 3 min 19 sec
timeout after: 11 min 40 sec
wait id: 301
blocking: 0 sessions
current sql: alter procedure user2_test_p1 compile
short stack: ksedsts()+465<-ksdxfstk()+32<-ksdxcb()+1927<-sspuser()+112<-__sighandler()<-semtimedop()+10<-skgpwwait()+160<-ksliwat()+2022<-kslwaitctx()+163<-kjusuc()+3400<-ksipgetctxi()+1759<-kqlmPin()+2943<-kqlmClusterLock()+237<-kglpnal()+4044<-kglpin()+1373<-kkdllk0()+904<-kkdlGetCodeObject()+307<-kkpalt()+353<-opiexe()+18119<-opiosq0()+3932<-kpooprx()+274<-kpoal8()+842<-opiodr()+917<-ttcpip()+2183<-opitsk()+1710<-opiino()+969<-opiodr()+917<-opidrv()+570<-sou2o()+103<-opimai_real()+133<-ssthrdmain()+265<-main()+201<-__libc_
wait history:
* time between current wait and wait #1: 0.000137 sec
1. event: ‘library cache lock’
time waited: 0.000631 sec
wait id: 300 p1: ‘handle address’=0x8899a6b0
p2: ‘lock address’=0x898f2828
p3: ‘100*mode+namespace’=0x1559600010003
* time between wait #1 and #2: 0.001686 sec
2. event: ‘library cache pin’
time waited: 0.000642 sec
wait id: 299 p1: ‘handle address’=0x8987e208
p2: ‘pin address’=0x88a29c50
p3: ‘100*mode+namespace’=0x1084c00030002
* time between wait #2 and #3: 0.004552 sec
3. event: ‘SQL*Net message from client’
time waited: 4 min 24 sec
wait id: 298 p1: ‘driver id’=0x62657100
p2: ‘#bytes’=0x1
}
and is blocked by
=> Oracle session identified by:
{
instance: 1 (abc.abc1)
os id: 8388
process id: 43, oracle@xxx (TNS V1-V3)
session id: 43
session serial #: 115
}
which is waiting for ‘PL/SQL lock timer’ with wait info:
{
p1: ‘duration’=0x0
time in wait: 3 min 30 sec
timeout after: 196 min 29 sec
wait id: 77
blocking: 1 session
current sql: begin
user2.user2_test_p1;
dbms_lock.sleep(12000);
end;
short stack: ksedsts()+465<-ksdxfstk()+32<-ksdxcb()+1927<-sspuser()+112<-__sighandler()<-semtimedop()+10<-skgpwwait()+160<-ksliwat()+2022<-kslwaitctx()+163<-psdwat()+169<-pevm_icd_call_common()+897<-pfrinstr_ICAL()+169<-pfrrun_no_tool()+63<-pfrrun()+627<-plsql_run()+649<-peicnt()+302<-kkxexe()+525<-opiexe()+18001<-kpoal8()+2118<-opiodr()+917<-ttcpip()+2183<-opitsk()+1710<-opiino()+969<-opiodr()+917<-opidrv()+570<-sou2o()+103<-opimai_real()+133<-ssthrdmain()+265<-main()+201<-__libc_start_main()+253
wait history:
* time between current wait and wait #1: 0.001350 sec
1. event: ‘SQL*Net message from client’
time waited: 10.208592 sec
wait id: 76 p1: ‘driver id’=0x62657100
p2: ‘#bytes’=0x1
* time between wait #1 and #2: 0.002433 sec
2. event: ‘SQL*Net message to client’
time waited: 0.000001 sec
wait id: 75 p1: ‘driver id’=0x62657100
p2: ‘#bytes’=0x1
* time between wait #2 and #3: 0.000004 sec
3. event: ‘SQL*Net break/reset to client’
time waited: 0.000091 sec
wait id: 74 p1: ‘driver id’=0x62657100
p2: ‘break?’=0x0
}

Chain 1 Signature: ‘PL/SQL lock timer'<=’library cache pin’

Jan 15

Difference between SQL profiles and SQL plan baselines

This post explains the difference between SQL profiles and SQL plan baselines and how they interact. But first let’s briefly recap each feature.

The query optimizer normally uses information like object and system statistics, compilation environment, bind values and so on to determine the best plan for a SQL statement. In some cases, defects in either these inputs or the optimizer can lead to a sub-optimal plan.  A SQL profile contains auxiliary information that mitigates these problems.  When used together with its regular inputs, a SQL profile helps the optimizer minimize mistakes and thus more likely to select the best plan.

A SQL plan baseline for a SQL statement consists of a set of accepted plans. When the statement is parsed, the optimizer will only select the best plan from among this set. If a different plan is found using the normal cost-based selection process, the optimizer will add it to the plan history but this plan will not be used until it is verified to perform better than the existing accepted plan and is evolved.

So, SQL profiles provide additional information to the optimizer to help select the best plan; they don’t constrain the optimizer to any specific plan, which is why they can be shared. SQL plan baselines, on the other hand, constrain the optimizer to only select from a set of accepted plans. The cost-based approach is still used to choose a plan, but only within this set of plans. SQL plan baselines are a more conservative plan selection strategy than SQL profiles. So when should you use SQL profiles versus SQL plan baselines?

You should use a SQL profiles if you just want to help the optimizer a little in its costing process without constraining it to any specific plan. This approach can be extremely useful when you want the system to adapt immediately to changes like new object statistics. You should use SQL plan baselines if you are more conservative and want to control which plans are used. If you are using SQL plan baselines and find that the optimizer sometimes does not select the best plan from the accepted list or does not find a best-cost plan to add to the plan history, then you can always use a SQL profile as well. What happens if a SQL statement has both a SQL Profile and a SQL plan Baseline?

If you recall, SQL Plan Management (SPM) has three component, plan capture, plan selection, and plan evolution. The presence of a SQL profile affects all three components of SPM and we will describe each of those interactions below.

SPM plan capture and SQL profiles

When the statement is executed it will be hard parsed and a cost based plan will be generated. That plan will be influenced by the SQL Profile. Once the cost based plan is determined it will be compared to the plans that exist in the SQL plan baseline. If the plan matches one of the accepted plans in the SQL plan baseline, we will go ahead and use it. However, if the cost based plan doesn’t match any of the accepted plans in the SQL plan baseline it will be added to the plan baseline as an unaccepted plan.

SPM plan selection and SQL profiles

When a SQL statement with a SQL plan baseline is parsed, the accepted plan with the best cost will be chosen.  This process uses the regular optimizer.  The presence of a SQL profile will affect the estimated cost of each of these plans and thus potentially the plan that is finally selected.

SPM plan evolution and SQL profiles

The third sub-component of SPM is verification or evolution of non-accepted plans. The evolution process test-executes the non-accepted plan against the best of the accepted plans.  The best accepted plan is selected based on cost.  Again, if a SQL profile exists, it will influence the estimated cost and thus the accepted plan chosen for comparison against the non-accepted plan.

Hopefully this information gives you a clear picture of how SQL profile and SQL plan baselines differ and how they interact with one another!

Sep 6

Sql Tune to improve buffer gets

Before buffer get tune. SQL plan was doing index fast  full scan. here is you can see that

buffer_get_1

buffer_get_2

After running SQL tuning adviser that suggest to implement new profile that took index rang scan and buffer get improved dramatically also CPU cost.

buffer_get_3

buffer_get_4

as you can see above buffer get reduced from 22K to 615 and CPU cost reduced from 772 to 1. huge improvement.

 

 

 

 

 

Jul 15

Run SQL Tuning Adviser Manually

Find sqlid, begin and end snapshot and put in here 

declare
l_sql_tune_task_id varchar2(100);
begin
l_sql_tune_task_id := dbms_sqltune.create_tuning_task (
begin_snap => 5175,
end_snap => 5176,
sql_id => ‘7v5ac8zqn9y65’,
scope => dbms_sqltune.scope_comprehensive,
time_limit => 300,
task_name => ‘sql_tune’,
description => ‘task description’);
dbms_output.put_line(‘l_sql_tune_task_id: ‘ || l_sql_tune_task_id);
end;
/

Run sql tune 

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => ‘sql_tune’);

Find commendation

SELECT
DBMS_SQLTUNE.report_tuning_task(‘sql_tune’) AS recommendations
FROM dual;

Run recommendation if any

execute dbms_sqltune.accept_sql_profile(task_name => ‘sql_tune’,task_owner => ‘SYS’, replace => TRUE);

 

 

 

%d bloggers like this: