Category Archives: Tuning

Library cache hit ratio

 
-- ############################################################
-- # Creator: Vincent Fenoll
-- # Created: 2011/03/10
-- # Name: Library cache hit ratio
-- ############################################################
-- #
-- # Compatible: Oracle 7i 8i 9i 10g 11g
-- #
-- ############################################################
-- #
-- # How to calculate Library cache hit ratio
-- # Other ratios see: "Quick tune"
-- # 
-- ############################################################

Calculate the cache hit ratio for the library cache with the following query:

    Select sum(pinhits) / sum(pins) "Hit Ratio",
        sum(reloads) / sum(pins) "Reload percent"
    From v$librarycache
    Where namespace in
    ('SQL AREA', 'TABLE/PROCEDURE', 'BODY', 'TRIGGER');

-- The hit ratio should be above 85 percent. 
-- The reload percent should be very low, 2% or less. 
-- If this is not the case, increase the initialisation parameter SHARED_POOL_SIZE. 
-- OPEN_CURSORS may also need to increased.

Display Binds variables

 
-- ############################################################
-- # Creator: Vincent Fenoll
-- # Created: 2011/03/10
-- # Name: Display Binds variables
-- ############################################################
-- #
-- # Compatible: Oracle 10g 11g
-- #
-- ############################################################
-- #
-- # How to display values of binds variables
-- # 
-- ############################################################

set line 150 pagesize 80
col name for a50
col value_string for a10
select name, position, value_string, to_char(last_captured,'mm/dd/yyyy hh24:mi:ss'
) from v$sql_bind_capture where sql_id = '4hkxkwjm5964k';

Display cursor

 
-- ############################################################
-- # Creator: Vincent Fenoll
-- # Created: 2011/03/&
-- # Name: Display cursor
-- ############################################################
-- #
-- # Compatible: Oracle 10g 11g
-- #
-- ############################################################
-- #
-- # How to display more informations than explain 
-- # plan about the cursor: binds variables, 
-- #  estimated vs real rows (tuning with cardinalities)...
-- # 
-- # pre-requisite:
-- # - statistics_level=ALL
-- # - CBO used and tables analyzed
-- # - cursor_sharing=force (for "SERIAL PEEKED_BINDS IOSTATS LAST")
-- # - Better to have enough SGA to find old statements (1G)
-- # 
-- ############################################################


--      INPUTS (v$sql_area): 
--	&1 = SQL_ID (in lowercase)
--	&2 = child_number


-- Informations about the last SQL Statement:
SELECT * FROM TABLE(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));


-- Informations about another statement identified with sql_id and child_number:
SELECT * FROM TABLE(dbms_xplan.display_cursor('&1', &2, 'ALLSTATS LAST'));


-- To display the values of bind variables
select * from table(dbms_xplan.display_cursor('&1', &2, 'SERIAL PEEKED_BINDS IOSTATS LAST'));


-- To read the result:
--   "E-Rows" is the number of estimated rows that Oracle expects that step in the plan to return
--   "A-Rows" is the actual number. 
--   "Starts" column is the number of times that that step in the plan is "executed"
--    Note: "A-Rows" column is the cumulative count over all executions, 
--          "E-Rows" is the estimate for each execution of the step. 


Output example:


-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE              |      |      1 |      1 |      1 |00:00:00.02 |     196 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1   |      1 |     40 |    225 |00:00:00.02 |     196 |
|   3 |    NESTED LOOPS              |      |      1 |    225 |    241 |00:00:00.03 |     196 |
|*  4 |     TABLE ACCESS FULL        | T2   |      1 |     40 |     40 |00:00:00.01 |      45 |
|*  5 |     INDEX RANGE SCAN         | T_I1 |     10 |     40 |    400 |00:00:00.01 |       8 |
-----------------------------------------------------------------------------------------------        

In this example, in line 5:  E-rows = 40 and A-rows = 400, because line 5 starts 10 times: 
so 400 actual rows = (10 starts) * (40 estimated rows per start).


Explain plan

 
-- ################################################
-- # Creator: Vincent Fenoll
-- # Created: 2011/03/10
-- # Name: Extended traces
-- ################################################
-- #
-- # Compatible: Oracle 9i 10g 11g
-- #
-- ################################################
-- #
-- # How to display the execution plan with explain plan
-- # for a sql statement
-- # 
-- # pre-requisite:
-- # PLAN_TABLE; created with @?/rdbms/admin/utlxplan.sql
-- #
-- ################################################



-- In thie first example the SQL Statement to audit is very
-- useful to show active SQL (often bad sql to tune) 


set lines 140 pages 2000

explain plan for
  select sql_fulltext 
  from v$session a, v$sqlarea b 
  where a.sql_address=b.address(+) and status = 'ACTIVE';

@?/rdbms/admin/utlxpls

explain plan for
  select sysdate from dual;

@?/rdbms/admin/utlxpls

Extended traces

 
-- ################################################
-- # Creator: Vincent Fenoll
-- # Created: 2011/03/10 
-- # Name: Extended traces
-- ################################################
-- #
-- # Compatible: Oracle 8i 9i 10g 11g 12c
-- #
-- ################################################
-- #
-- # How to put an extended trace for another session
-- # 
-- # level 	Information included
-- # -----------------------------------------------------
-- # 1        Standard SQL_TRACE functionality
-- # 4        As level 1 plus tracing of bind variables
-- # 8        As level 1 plus wait events
-- # 12       As level 1 plus bind variables and wait events.
-- # 
-- # Useful when you don't want to activate traces
-- # on the whole database.
-- # 
-- # New method exists from 10g: DBMS_SESSION DBMS_MONITOR
-- # but I prefer this one
-- # 
-- ################################################

-- First, find the information (sid, serial#) to trap the user session
set lines140
col username format a40
select username, sid, serial# from v$session;

USERNAME                              SID    SERIAL#
------------------------------ ---------- ----------
                                      601      30377
myuser                                651      32901


-- Set 2 parameters in the user session
exec sys.dbms_system.set_int_param_in_session (651, 32901, 'max_dump_file_size', 2147483647)
exec sys.dbms_system.set_bool_param_in_session (651, 32901, 'timed_statistics', true)

-- Activate the traces
exec sys.dbms_system.set_ev(651, 32901 , 10046, 12, '')
-- You can also activate event 10053 level 1 for to trace the optimizer
	/*  Traces ...application runnning.....[ ].......... */

-- Deactivate the traces
exec sys.dbms_system.set_ev(651, 32901, 10046, 0, '')

-- Go to the user_dump_dest folder and make a tkprof on the trc file

-- for example, sort by execution elapsed (sort=exeela)
tkprof qb_ora_7949.trc parse_qb_ora_7949.txt sys=no  aggregate=yes sort=exeela waits=yes

-- Look at the beginning of the report to find bad SQL and verify at the end of the report 
-- that the total elapsed time corresponds to the bad SQL. 
-- If not, perhaps you have a lot of small other SQL in the main body of the report

-- It's also important to look also at the total waits

-- If you did'nt find your problem, you can also sort another predicate (example by parse elapsed or whatever...)
tkprof qb_ora_7949.trc parse_qb_ora_7949.txt sys=no  aggregate=yes sort=prsela waits=yes


-- If you did'nt find your problem, you can also trace the optimizer (EVENT=10053)
exec sys.dbms_system.set_ev(11, 12365, 10053, 1, '')


-- To activate traces for the current session:
alter session set timed_statistics = true; 
alter session set statistics_level=all; 
alter session set max_dump_file_size = unlimited; 
alter session set events '10046 trace name context forever,level 12'; 
alter session set events '10053 trace name context forever, level 1';