Author: Admin

When you are creating a procedure and you are using objects from other schemas, then the procedure owner needs related privilages exclusively on those objects of other schemas. This cannot be resolved with ROLE.

To collect CPU stats from Server following code needs to be setup. ( Step By Step ) Step-1 ====== Create a user named PERFSTAT/PERFSTAT. This is not for statspack, we only need 3 tables and package for this settings. Give appropriate default TS for same. Step-2 ====== create tables with following definitons: CREATE TABLE STATS$VMSTAT2…

Read More “Settting up get_vmstat.sh for colletinf CPU Usage.” »

To give grant on v$database or any such synonym, need to follow following steps. 11:02:21 SYS@UOCQA:-> select * from dba_synonyms where synonym_name = ‘V$DATABASE’; OWNER SYNONYM_NAME TABLE_OWNER —————————— —————————— —————————— TABLE_NAME —————————— DB_LINK —————————————————————————————————- PUBLIC V$DATABASE SYS V_$DATABASE Then, 11:02:22 SYS@UOCQA:-> GRANT SELECT ON V_$DATABASE TO PUBLIC;

http://www.sun.com/blueprints/0303/817-1781.pdf

Create virtual indexes 11:42:42 CS@UOCQA:-> create index index_1 on BSKT_SHOPPING_BASKET_ITEM (BASKET_RECIPIENT_ID, BASKET_ITEM_ID) nosegment; need to set a hidden parameter as following. 11:42:42 CS@UOCQA:-> alter session set “_use_nosegment_indexes”=true; Indexes created without any segment. cannot views with User_indexes. Can be viewed in USER_OBJECTS. Useful in producton environment where we cannot create any indexes. Existance of this index…

Read More “Virtual Indexes in Oracle” »

(1) Operation : SORT UNIQUE Cause : DISTINCT Clause, and IN sub-query where subquery is independent meand no co-relation with parenst query. If for IN subquery, there is an unique index on selected columns then SORT UNIQUE can be avoided. (2) Operation : SORT AGGREGATE Cause : Whenever u use group functions. It does not…

Read More “Optimizer SORT Operations” »

http://www.EvDBT.com/library.htm

select a.ksppinm “Parameter”, b.ksppstvl “Session Value”, c.ksppstvl “Instance Value” from x$ksppi a, x$ksppcv b, x$ksppsv c where a.indx = b.indx and a.indx = c.indx and substr(ksppinm,1,1)=’_’ order by a.ksppinm;

spool badsql5 set lines 132 set pages 300 col USR format a14 col PAR heading “Parsing ID” format 9999999 col ELA heading “ELP time(Sec)” format 9999999.0 col HASH heading “Hash Value” col USR heading “Parsing User” col T heading “SQL Text having Elapsed time > 1 sec” break on HASH on USR on ELA skip…

Read More “Find Time Consuming SQL Statements in Oracle 10g” »