Let us have your views ...
| Description |
This process copies the content of v$sqlarea to a holding table, and produces an explain plan
for each code segment.
This is useful for situations where shutting down and restarting the database to enable/disable tracing is problematic (e.g. 24/7 production systems), or where there is an immediate need to identify resource intensive code. |
| Sample Result |
------------------------------------------------------------------------------------------------------------------------ SELECT LINE_NO,ADDR_LINE_CODE, TEXT_LINE FROM ADDRESS_TEXT_MSG WHERE ADDRESS_ID = :b1 AND LANG_CODE = 'EN' ORDER BY LINE_NUMBER . SELECT STATEMENT 14 . SORT ORDER BY 1 . PARTITION HASH SINGLE 1 . TABLE ACCESS FULL ADDRESS_TEXT_MSG 1 Disk reads : 203,072 Loads : 2 Executions : 3,737 Buffer gets : 718,203 Rows Processed : 15,031 Optimiser mode : CHOOSE Invalidations : 0 Parse calls : 4 Reads per row : 13.510 ------------------------------------------------------------------------------------------------------------------------ SELECT A10.COMN_ID, B10.COMN_STS_START_DT FROM PARTIES_IN_COMMUNICATIONS A10, C_STATUS_HISTORIES B10 WHERE TRUNC(B10.C_STS_START_DT) BETWEEN TO_DATE(:b1,:b2) AND TO_DATE(:b3,:b2) AND A10.CID = :b5 AND A10.ROLE_TYPE_CODE = 'CUS' AND B10.C_ID = A10.C_ID AND B10.CURRENT_IND = 'Y' AND B10.C_STS_CD != 'D' ORDER BY B10.C_STS_START_DT DESC . SELECT STATEMENT 155 . SORT ORDER BY 1 . HASH JOIN 1 . PARTITION HASH SINGLE 1 . TABLE ACCESS FULL PARTIES_IN_COMMUNICATIONS 1 . TABLE ACCESS FULL C_STATUS_HISTORIES 2 Disk reads : 106,516 Loads : 1 Executions : 47 Buffer gets : 118,104 Rows Processed : 673 Optimiser mode : CHOOSE Invalidations : 0 Parse calls : 4 Reads per row : 158.270 ------------------------------------------------------------------------------------------------------------------------ SELECT A_TEXT_LINE, A_LINE_NUMBER, A_LINE_NO, A_LINE_TYPE FROM ADDRESS_TEXT_MSG WHERE ADDRESS_ID = :b1 . SELECT STATEMENT 12 . PARTITION HASH SINGLE 1 . TABLE ACCESS FULL ADDRESS_TEXT_MSG 1 Disk reads : 21,573 Loads : 2 Executions : 130 Buffer gets : 25,936 Rows Processed : 650 Optimiser mode : CHOOSE Invalidations : 0 Parse calls : 2 Reads per row : 33.189 ------------------------------------------------------------------------------------------------------------------------ |
| Steps |
There are three sections :
- create holding tables - populate holding tables and run explain plan - output results Once the holding tables are created they do not need to be created again; the second process truncates the tables for each run. The code for each section should be held as a file for easy execution; file names used for identification here are : section 1 - ep_sa1.sql; section 2 - ep_sa2.sql; section 3 - ep_sa3.sql. (The origin for the file name structure is lost in the mists of time, but probably comes from ExplainPlan_Sga_Area, or EP_SA.) Errors might be reported depending on which schema section 2 (ep_sa2.sql) is run. If not run under the SYS schema, the explain plan will not be able to evaluate SYS owned objects, though this is not a concern, as tuning applies to application code rather than SYS executed code. Creating the tables (section 1, ep_sa1.sql) under a non-SYS/SYSTEM schema, and then executing section 2 (ep_sa2.sql) as SYS is an option, providing public or SYS synonyms have been created to identify the location of the holding tables. Once the code in section 2 (ep_sa2.sql) has been executed, and the data committed, section 3 code (ep_sa3.sql) can be executed a number of times (spooling to a text file is recommended). This allows for changing the order of display (currently set to 'order by disk_reads'). |
| Create holding tables - ep_sa1.sql |
create table EP_SAREA_PLAN_TABLE
( STATEMENT_ID VARCHAR2(30),
TIMESTAMP DATE,
REMARKS VARCHAR2(80),
OPERATION VARCHAR2(30),
OPTIONS VARCHAR2(30),
OBJECT_NODE VARCHAR2(128),
OBJECT_OWNER VARCHAR2(30),
OBJECT_NAME VARCHAR2(30),
OBJECT_INSTANCE NUMBER(38),
OBJECT_TYPE VARCHAR2(30),
OPTIMIZER VARCHAR2(255),
SEARCH_COLUMNS NUMBER(38),
ID NUMBER(38),
PARENT_ID NUMBER(38),
POSITION NUMBER(38),
COST NUMBER(38),
CARDINALITY NUMBER(38),
BYTES NUMBER(38),
OTHER_TAG VARCHAR2(255),
OTHER LONG
)
tablespace &tablespace_name
/
|
| Populate holding tables and run explain plan - ep_sa2.sql |
set echo off
set verify off
set lines 1000
set pages 999
set head on
set feed on
set serveroutput on size 1000000 format wrap
set trimspool on
clear col
clear break
clear computes
set arrays 1
set long 10000
declare
cursor csr_sqla_all is
select *
from EP_SAREA_SQLTEXT
order by disk_reads desc
for update of statement_id;
ls_exp_code varchar2( 10000 );
li_counter integer := 0;
li_sqlarea_recs integer;
cursor csr_count_sqlarea is
select count(*)
from EP_SAREA_SQLTEXT;
li_ditx_value integer;
procedure ditx ( es_text varchar2, es_desc varchar2, xi_return_val out integer, es_show_res integer default 0 )
is
li_handle integer;
li_feedback integer;
ls_err varchar2( 10000 );
begin
li_handle := dbms_sql.open_cursor;
dbms_sql.parse( li_handle, es_text, dbms_sql.native );
li_feedback := dbms_sql.execute( li_handle );
dbms_sql.close_cursor( li_handle );
xi_return_val := 1;
if es_show_res <> 0 then
dbms_output.put_line( es_desc );
end if;
exception
when others then
ls_err := sqlerrm;
dbms_output.put_line( 'Err : ' || substr( es_text, 1, 40 ) || ':' || sqlerrm );
dbms_sql.close_cursor( li_handle );
xi_return_val := -1;
end;
begin
dbms_output.enable( 1000000 );
ditx( 'truncate table EP_SAREA_PLAN_TABLE', 'Truncated table ep_sarea_plan_table.', li_ditx_value, 1 );
if li_ditx_value <> 1 then
ditx( 'delete EP_SAREA_PLAN_TABLE', 'Deleted table ep_sarea_plan_table.', li_ditx_value, 1 );
if li_ditx_value <> 1 then
dbms_output.put_line( 'Error encountered : please resolve before re-running.' );
return;
end if;
end if;
ditx( 'truncate table EP_SAREA_SQLTEXT', 'Truncated table ep_sarea.sqltext', li_ditx_value, 1 );
if li_ditx_value <> 1 then
ditx( 'delete EP_SAREA_SQLTEXT', 'Deleted table ep_sarea.sqltext', li_ditx_value, 1 );
if li_ditx_value <> 1 then
dbms_output.put_line( 'Error encountered : please resolve before re-running.' );
return;
end if;
end if;
ditx( 'insert into EP_SAREA_SQLTEXT ( select one.*, null from v$sqlarea one )', 'Updated ep_sarea_sqltext.', li_ditx_value, 1 );
open csr_count_sqlarea;
fetch csr_count_sqlarea into li_sqlarea_recs;
close csr_count_sqlarea;
for i_csr_sqla in csr_sqla_all loop
li_counter := li_counter + 1;
if upper( substr( ltrim( i_csr_sqla.sql_text ), 1, 6 ) ) in ( 'SELECT', 'UPDATE', 'DELETE', 'INSERT' ) then
ls_exp_code := 'explain plan set statement_id = ' || '''' || li_counter || '''' ||
' into EP_SAREA_PLAN_TABLE ' ||
' for ' || i_csr_sqla.sql_text;
ditx( ls_exp_code, 'Epd ' || li_counter || ' of ' || li_sqlarea_recs, li_ditx_value );
if li_ditx_value = 1 then
update EP_SAREA_SQLTEXT set statement_id = to_char( li_counter ) where current of csr_sqla_all;
else
update EP_SAREA_SQLTEXT set statement_id = to_char( li_counter * -1 ) where current of csr_sqla_all;
end if;
else
update EP_SAREA_SQLTEXT set statement_id = 0 where current of csr_sqla_all;
end if;
end loop;
dbms_output.put_line( rpad( '=', 120, '=' ) || chr(10) || chr(10) );
end;
/
commit;
|
| Output results - ep_sa3.sql |
set echo off
set verify off
set lines 1000
set pages 999
set head on
set feed on
set serveroutput on size 1000000 format wrap
set trimspool on
clear col
clear break
clear computes
set arrays 1
set long 10000
declare
cursor csr_sqla_done is
select *
from EP_SAREA_SQLTEXT
where statement_id > 0
order by disk_reads desc;
cursor csr_plan( xt_statement_id in EP_SAREA_PLAN_TABLE.STATEMENT_ID%type ) is
select '. ' || lpad( ' ', 2 * ( level - 1 ) ) || operation "Operation", options, object_name, position
from EP_SAREA_PLAN_TABLE
start with id = 0
and statement_id = xt_statement_id
connect by prior id = parent_id
and statement_id = xt_statement_id;
type tt_full_table is table of user_tables.table_name%type index by binary_integer;
begin
dbms_output.enable( 1000000 );
for i_csr_sqla in csr_sqla_done loop
dbms_output.put_line( rpad( '-', 120, '-' ) );
-- * Note : requires a later version of bigt which accepts a line length parameter
-- if the later version is not available, remove the parameter ', 120'
bigt( i_csr_sqla.sql_text, 120 );
dbms_output.put_line( chr(10) );
for i_csr_plan in csr_plan( i_csr_sqla.statement_id ) loop
dbms_output.put_line( rpad( i_csr_plan."Operation", 30 ) || ' ' ||
rpad( nvl( i_csr_plan.options, ' ' ), 15 ) || ' ' ||
rpad( nvl( i_csr_plan.object_name, ' ' ), 25 ) || ' ' ||
i_csr_plan.position );
end loop;
dbms_output.put_line( chr(10) );
dbms_output.put_line( 'Disk reads : ' || to_char( i_csr_sqla.disk_reads, '999,999,999' ) );
dbms_output.put_line( 'Loads : ' || to_char( i_csr_sqla.loads, '999,999,999' ) );
dbms_output.put_line( 'Executions : ' || to_char( i_csr_sqla.executions, '999,999,999' ) );
dbms_output.put_line( 'Buffer gets : ' || to_char( i_csr_sqla.buffer_gets, '999,999,999' ) );
dbms_output.put_line( 'Rows Processed : ' || to_char( i_csr_sqla.rows_processed, '999,999,999' ) );
dbms_output.put_line( 'Optimiser mode : ' || i_csr_sqla.optimizer_mode );
dbms_output.put_line( 'Invalidations : ' || to_char( i_csr_sqla.invalidations, '999,999,999' ) );
dbms_output.put_line( 'Parse calls : ' || to_char( i_csr_sqla.parse_calls, '999,999,999' ) );
if i_csr_sqla.rows_processed > 0 then
dbms_output.put_line( 'Reads per row : ' || to_char( ( i_csr_sqla.disk_reads / i_csr_sqla.rows_processed ), '999,999.999' ) );
else
dbms_output.put_line( 'Reads per row : ZERO or fewer rows processed.' );
end if;
dbms_output.put_line( chr(10) );
end loop;
dbms_output.put_line( rpad( '-', 120, '-' ) || chr(10) );
end;
/
|