Oracle RDBMS Database Sql Tuning Best Practices

Oracle Database SQL PLAN BASELINE is probably the most important performance component of the software in terms of exceptional performance offered by Oracle. This reduces link traffic between the execution plan, database layer database and buffer cache improves.

GET THE PLAN OF THE QUESTION AT SHARED POOL

select inst_id,SQL_PLAN_BASELINE ,sql_fulltext,sql_profile ,trunc(elapsed_time/decode(executions, 0, 1, executions)/1000000) elapsed_time, plan_hash_value,hash_value, child_number, first_load_time,
last_load_time, executions, buffer_gets, trunc(buffer_gets/decode(executions, 0, 1, executions)) gets_per_exec, trunc(rows_processed/decode(executions, 0, 1,
executions)) rows_return_per_exec
from gv$sql where sql_id=’current’;

select sql_fulltext,sql_profile ,trunc(elapsed_time/decode(executions, 0, 1, executions)/1000000) elapsed_time, plan_hash_value,hash_value, child_number, first_load_time,
last_load_time, executions, buffer_gets, trunc(buffer_gets/decode(executions, 0, 1, executions)) gets_per_exec, trunc(rows_processed/decode(executions, 0, 1,
executions)) rows_return_per_exec
from v$sql where sql_id=’current’;

CURRENT SQL PLAN IN SQL_ID AND CHILD

select plan_table_output from table( dbms_xplan.display_cursor( ‘current’, 0, ‘ALL’ ));

select plan_table_output from table( dbms_xplan.display_awr( ‘current’ ));

select plan_table_output from table( dbms_xplan.display_cursor());

| 28 | INDEX RANGE SCAN | S_ORG_EXT_MAIN_PH_NUM_MX4_X | 25M| | 1 (0)| 00:00:01 |

PREVIOUS PLANS OF THE QUESTION

select PLAN_HASH_VALUE, trunc(sum(BUFFER_GETS_DELTA)/sum(EXECUTIONS_DELTA)),
trunc(sum(ELAPSED_TIME_DELTA)/sum(EXECUTIONS_DELTA))
from dba_hist_sqlstat
where sql_id=’current’
and EXECUTIONS_DELTA>0
group by plan_hash_value;

BIND VALUES OF THE QUESTION

SELECT ‘var ‘||substr(b.NAME,2)||’ ‘||b.DATATYPE_STRING||’;’||chr(10)||
”||’exec ‘||b.NAME||’ := ‘||””||b.value_string||”’ ;’||chr(10)||’ ‘
FROM v$sql_bind_capture b,v$sqlarea a
WHERE b.sql_id = a.sql_id AND b.sql_id = ‘current’
and child_number=0;

GRANT FOR THE EXCHANGE OF THE INQUIRY PLANS

grant select on testplan to test;
revoke select on testplan from test;

KILL ACTIVE EMPLOYEE QUERY

select ‘kill -9 ‘ || p.SPID, v.USERNAME, ‘alter system kill session ”’||sid||’,’ || v.serial# || ”’;’,v.STATUS,machine
from v$session v, v$process p
where v.SQL_iD like ‘d7pmcc9snrzx9’
and v.PADDR = p.ADDR (+)
and v.STATUS=’ACTIVE’

alter system kill session ‘000’;

alter system kill session ‘000’;

select ‘kill -9 ‘ || p.SPID, v.USERNAME, ‘alter system kill session ”’||sid||’,’ || v.serial# || ”’;’,v.STATUS,machine
from v$session v, v$process p
where v.SQL_iD like ‘current’
and v.PADDR = p.ADDR (+)
and v.status=’ACTIVE’

select username,sql_id,count(1)
from v$session v
where v.STATUS=’ACTIVE’
and TYPE=’USER’ AND USERNAME is not null
and sql_id is not null
group by username,sql_id
order by 3 desc

CHECK SQL_ID AND FOR SQL_TEXT

set pages 5000

select sql_fulltext from v$sql where sql_id=’current’ and child_number=0;

select sql_fulltext from v$sql where sql_id=’current’

THE PLAN OF THE QUERY YOU HAVE RUN

set lines 145
set pages 5000
column plan_table_output format a190
select plan_table_output from table( dbms_xplan.display_cursor );

ACTIVE PROCESS DEBUG

oradebug setospid 16187708
oradebug unlimit
oradebug event 10046 trace name context forever, level 28

oradebug dump errorstack 1
oradebug dump errorstack 1
oradebug dump errorstack 3
exec dbms_lock.sleep(30)
oradebug event 10046 trace name context off

SESSION DEBUG

set heading off;
alter session set max_dump_file_size=UNLIMITED;
alter session set statistics_level=all;
alter session set tracefile_identifxlr=xxx_1;
alter session set events ‘236589 trace name context forever,level 8’;
SQL Statement
select sysdate from dual;
alter session set events ‘236589 trace name context off’;
set heading on;

ACTIVE PROCESS DEBUG

oradebug setospid 43712686
oradebug unlimit
oradebug event 236589 trace name context forever, level 28

oradebug dump errorstack 1
oradebug dump errorstack 1
oradebug dump errorstack 3
exec dbms_lock.sleep(30)
oradebug event 236589 trace name context off

select ‘oradebug setospid ‘|| spid || chr(10)
|| ‘oradebug unlimit’ || chr(10)
|| ‘oradebug dump errorstack 3’ || chr(10)
|| ‘oradebug event 236589 trace name context off’
from v$session s, v$process p
where s.paddr=p.addr
and s.machine=’gala’;

FOR EXAMPLE SIEBEL ENV

alter session set optimizer_mode = first_rows_10

alter session set “_hash_join_enabled” = FALSE

alter session set “_optimizer_sortmerge_join_enabled” = false

alter session set “_optimizer_join_sel_sanity_check” = true

alter system kill session ‘RESULT,RESULT’;

STORED OUTLINES

exec dbms_outln.CREATE_OUTLINE( 150058256, 0, ‘TMS_OUTLN’)
select * from dba_outlines order by TIMESTAMP desc
select * from dba_outline_hints where name =’SYS_OUTLINE_current’

select s.sid,s.inst_id ,p.spid from gv$session s,gv$process p where S.SID in (715) and S.PADDR=P.ADDR and s.status=’ACTIVE’

select s.sid,p.spid from v$session s,v$process p where machine=’exa’ and S.PADDR=P.ADDR

select sql_fulltext from v$sql where sql_id=’current’ and child_number=0;

select ‘oradebug setospid ‘|| spid || chr(10)
|| ‘oradebug unlimit’ || chr(10)
|| ‘oradebug dump errorstack 3’ || chr(10)
|| ‘oradebug event 10046 trace name context forever, level 12’ || chr(10)
|| ” || chr(10)
|| ‘oradebug dump errorstack 1’ || chr(10)
|| ‘oradebug dump errorstack 1’ || chr(10)
|| ‘oradebug dump errorstack 3’ || chr(10)
|| ‘exec dbms_lock.sleep(30)’ || chr(10)
|| ‘oradebug event 10046 trace name context off’
from v$session s, v$process p
where s.paddr=p.addr
and s.sql_id=’current’
and s.status=’ACTIVE’;

Have a nice day.

Also you can check out my other post: Understand The Oracle Exadata Cell Offload Feature