Category Archives: SQL

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'; 

Unshared Queries

 
-- ################################################
-- # Creator: Vincent Fenoll
-- # Created: 2011/02/24
-- # Name: unshared_queries.sql
-- ################################################
-- #
-- # Compatible: Oracle 9i 10g 11g
-- #
-- ################################################
-- #
-- # Display unshared queries
-- # To find the reason look at V$SQL_SHARED_CURSOR :
-- # http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/dynviews_2124.htm
-- #
-- ################################################
 
set lines120 pages2000
spool non_shared.log
SELECT sysdate, a.hash_value, a.version_count , a.users_opening , a.users_executing, a.sharable_mem, a.sql_text, c.*
FROM v$sqlarea a, v$sql_shared_cursor c
WHERE a.address=c.address
and a.version_count > 5
order by a.version_count desc;
spool off