BASIC PERFORMANCE TUNING. Explain Plan , Gather stats, Import/export stats.

GET Old HASH VALUE from SPreport.

select sql_id from v$sql where old_hash_value=4095008456;

sql_id = 7wm4xn0pc7756

steps:

spool D:\performance_check\7wm4xn0pc7756.txt
set long 32767
set lines 155
set pages 1000
select * from table(dbms_xplan.display_cursor('bf3fq0uq6smpt',null,'typical +peeked_binds'));

select * from table(dbms_xplan.display_cursor('0dwgw62h73m6d',null,'typical +peeked_binds'));

------------------------------------------------------------------------------------------------------------------------------------------------------------
Another method:

set lines 132
set autot trace exp
spool D:\explain.txt
>Query
spool off
>set autot trace off

-------------------------------------------------------------------------------------------------------------------------------------------------------------

Another method: (for insert,update,delete queries)

SQL> @?/rdbms/admin/utlxplan.sql

Table created.

SQL> EXPLAIN PLAN FOR INSERT INTO PRODUCTAVAILABILITYDATE VALUES  (350497116 ,to_date('2017-03-01 00:00:00','yyyy-mm-dd hh24:mi:ss'),  to_date('2017-03-01 23:59:00','yyyy-mm-dd hh24:mi:ss') );

Explained.

SQL> set linesize 132
SQL> SELECT * FROM TABLE(dbms_xplan.display());

---------------------------------------------------------------------------------------------------------------------------------------------------------

If table is going for full table scan, check whether index is available on where clause columns

To check indexes on particular table:

select index_name,column_name from dba_ind_columns where table_name='istf1415.cropcycle';

select table_name,index_name,column_name from dba_ind_columns where table_name='CROPCYCLE';


To check indexes on particular column:

select table_name,index_name,column_name from dba_ind_columns where column_name='';

select table_name,index_name,column_name from dba_ind_columns where column_name='CONTRACT';

To create index:

create index indexname on tablename(columnname);

-----------------------------------------------------------------------------------------------------------------------------------------------------------


Gathering stats for particular table:

EXEC DBMS_STATS.gather_table_stats('username', 'tablename',estimate_percent => 35);

---------------------------------------------------------------------------------------------

Gathering stats for particular user/schema:

EXEC DBMS_STATS.GATHER_SCHEMA_STATS('username',ESTIMATE_PERCENT=>100);

----------------------------------------------------------------------------------------------
Export and Import schema stats:

Export (Example: user is test):

execute dbms_stats.create_stat_table(ownname=>'TEST', stattab=> 'teststats');

If it is 12c,

ALTER SESSION SET NLS_LENGTH_SEMANTICS = BYTE

exec dbms_stats.export_SCHEMA_stats('TEST','teststats');

ho exp test/test file='E:\teststats.dmp' log='E:\teststats.log' tables=test.teststats

----------------------------------------------------------------------------------------------

Import (Example: user is R2):

imp r2/r2 file='E:\teststats.dmp' log='E:\teststatsimp.log' tables=teststats fromuser=test touser=r2

update teststats set c5 = 'R2' where c5='TEST';

EXEC DBMS_STATS.import_schema_stats('R2','teststats')

after this we can lock the table::

----> EXEC DBMS_STATS.LOCK_SCHEMA_STATS('schema name');

locking the table statistics

-----> EXEC DBMS_STATS.LOCK_TABLE_STATS('schema name','table name');

for unlocking --->lock replace with unlock

select dbms_sqltune_util0.sqltext_to_sqlid(‘SELECT v.verticalbalanceid, TO_CHAR (verticalbalanceid, '000') AS vbid1, v.particulars, v.balfunc_param,  v.schno, v.cyinner, v.pyinner,
v.monthcumul, v.prevyrmnth, v.prevyrcumul, NVL (getacgroupbalance (v.mid,  :fdate, DECODE (v.balfunc_param, 'O', :fdate, :tdate),
DECODE (v.balfunc_param, NULL, 'N',  v.balfunc_param ), :sbname,:PYearEnd , :p_provch_type, :PClStk ), 0 ) AS netbalance,
NVL (getacgroupbalance (v.mid, :pfdate,  DECODE (v.balfunc_param, 'O', :pfdate, :ptdate ),
DECODE (v.balfunc_param, NULL, 'N', v.balfunc_param ),  :sbname,:PYearEnd ,:p_provch_type, :PClStk  ), 0 ) AS prevbalance,
NVL (getacgroupbalance (v.mid, :fdate, DECODE  (v.balfunc_param, 'O', :fdate, :tdate),
DECODE (v.balfunc_param, NULL, 'C', v.balfunc_param ),  :sbname,:PYearEnd ,:p_provch_type, :PClStk ), 0 ) AS cumul_currbal,
NVL (getacgroupbalance (v.mid, ADD_MONTHS (:fdate, -12),  ADD_MONTHS (DECODE (v.balfunc_param, 'O', :fdate, :tdate ), -12 ),
DECODE (v.balfunc_param, NULL, 'N',  v.balfunc_param ), :sbname,:PYearEnd ,:p_provch_type, :PClStk ), 0 ) AS preyr_netbalance,
NVL (getacgroupbalance (v.mid,  ADD_MONTHS (:fdate, -12), ADD_MONTHS (DECODE (v.balfunc_param, 'O', :fdate, :tdate ), -12 ),
DECODE  (v.balfunc_param, NULL, 'C', v.balfunc_param ), :sbname, :PYearEnd ,:p_provch_type, :PClStk ), 0 ) AS preyr_cumulative, fontcolumn,  :sbname AS sname,
:PYearEnd as year, :fdate AS stdate, :tdate AS eddate, v.mid, CASE WHEN NVL (v.mid, 0)  <> 0 THEN 'View Details' ELSE NULL END AS vwdtls, 'GL' AS reptype,
:pfdate AS rpfdate, :ptdate AS rptdate ,  :PYearEnd as RPYearEnd , :p_provch_type as p_appropriation , :PClStk as RPClStk
FROM rtverticalpandl v,  rtverticalpandlhdr a
where a.docid  =  'PLSt000001'
and a.rtverticalpandlhdrid = v.rtverticalpandlhdrid
 ORDER BY 1)  sql_id from dual