Category: SQL scripts

Find hash value & time when this plan was taken for particular sql id. Filename : fphv.sql REM *** Find Plan Hash value column plan_hash_value format 999999999999 accept v_sqlid prompt ‘Enter SQL_ID : ‘ select distinct sql_id, plan_hash_value, to_char(timestamp, ‘DD-MON-RRRR:HH24:MI:SS’) from dba_hist_sql_plan where sql_id=’&v_sqlid’; Then, Apply sql id and plan_hash_value for a unique plan. REM…

Read More “To find explain plan for a statement that occurred in past.” »

set lines 120 pages 200 column event format a30 column object_type format a14 column object_name format a30 column owner format a10 column sql_text format a120 spool currwaitobj accept event_name prompt ‘Enter fraction of event to trace : ‘ accept start_time1 prompt ‘Start Time in (DD-MON-RRRR:HH24:MI format) : ‘ accept stop_time1 prompt ‘Stop Time in (DD-MON-RRRR:HH24:MI…

Read More “currwaitobj.sql SQl_ID and SQL statement you can get from currwaitobj.sql” »

REM fplan.sql REM Find execution plan from dba_hist_sql_plan for a specific SQL_ID and PLAN_HASH_VALUE. column plan_hash_value format 999999999999 accept v_sqlid prompt ‘Enter SQL_ID : ‘ accept v_plan_hash_value prompt ‘Enter PLAN Hash Value : ‘ SELECT LPAD(‘ ‘,2*(LEVEL-1))||operation||’ ‘||options ||’ ‘||object_name ||’ ‘|| DECODE(id, 0, ‘Cost = ‘||position) “Query Plan” FROM dba_hist_sql_plan START WITH id =…

Read More “Find execution plan from dba_hist_sql_plan for a specific SQL_ID and PLAN_HASH_VALUE fplan.sql” »

Filename : fphv.sql REM *** Find Plan Hash value column plan_hash_value format 999999999999 accept v_sqlid prompt ‘Enter SQL_ID : ‘ select distinct sql_id, plan_hash_value, to_char(timestamp, ‘DD-MON-RRRR:HH24:MI:SS’) from dba_hist_sql_plan where sql_id=’&v_sqlid’;

set lines 120 pages 200 select status, count(1) from v$shared_server group by status; select * from v$shared_server_monitor; select dispatcher, count(1) from v$circuit group by dispatcher; select * from v$sgastat where name like ‘%free memory%’;

column osuser format a10 column username format a30 column machine format a30 column program format a40 column sid format 99999999 column serial# format 99999999 column TOT format 999999 heading “Total Sessions” break on report compute sum of TOT on report set lines 141 set pages 500 select machine, osuser, username, count(1) “TOT” from v$session group…

Read More “sess1.sql” »

set lines 120 pages 200 column event format a30 column object_type format a14 column object_name format a30 column owner format a10 column sql_text format a120 accept event_name prompt ‘Enter fraction of event to trace : ‘ select distinct a.event, s.sql_id, s.sql_text, s.executions from v$session a, v$sqlarea s where s.sql_id = a.sql_id and lower(a.event) like lower(‘%&event_name%’)…

Read More “currwait.sql” »

######### # _kks_use_mutex_pin # # Enables use of more efficient mutex mechanism for # implementing library cache pins. # ######### _kks_use_mutex_pin=TRUE Apparently, Oracle is migrating some of its serialization protection from latches to mutexes. For example, the structures previously protected by the Library Cache Pin latch are now protected by a mutex and evidenced by…

Read More “mutex in Oracle 10.2.0.2 or Oracle 10g” »

Problem: ——– I cannot create a db_link on my database that does not have the fuly qualified domain.name. eg. if I create a db_link called fred as follows: create database link fred connect to ITU604SP4B identified by using ‘sup9206_hippo’ a link called fred.intec.co.za is created, I just want fred. I have set global_names= FALSE db_domain=””…

Read More “Renaming Global Name GLOBAL_NAME” »

column name format a10 column position format 99 column datatype_string format a20 column value_string format a50 set lines 120 pages 200 select sql_id, name, position, datatype_string, value_string from v$sql_bind_capture where sql_id = ‘&sql_id’ /