Category: SQL scripts

Driver table: Take the driver table which returns the less number of rows for predicate with literal valae. For example, Consider the statement below: SELECT SUM(BIP.VALUE) value FROM BSKT_ITEM_CALC_PRICING BIP,BSKT_PRICING_ELEMENT BP WHERE BIP.BASKET_ITEM_ID IN (:”SYS_B_0″) AND BIP.PRICING_ELEMENT_ID = BP.PRICING_ELEMENT_ID AND BP.PRICING_TYPE_CODE = :1; Here SQL> select BASKET_ITEM_ID, count(1) from BSKT_ITEM_CALC_PRICING group by BASKET_ITEM_ID; BASKET_ITEM_ID COUNT(1)…

Read More “How to choose Driver table in SQL statement” »

set lines 132 set pages 1400 SELECT LPAD(‘ ‘,2*(LEVEL-1))||operation||’ ‘||options ||’ ‘||object_name ||’ ‘|| DECODE(id, 0, ‘Cost = ‘||position) “Query Plan” FROM v$sql_plan START WITH id = 0 and sql_id=’4ftvbpzhwkfd8′ and child_number=0 CONNECT BY PRIOR id = parent_id and sql_id=’4ftvbpzhwkfd8′ and child_number=0;

Good Doc About UNDO Management: http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:6894817116500 Cache Buffer Chain Latch: http://www.orafaq.com/maillist/oracle-l/2003/02/27/2671.raw

/etc/init.d/init.cssd stop to stop cssd process.

column osuser format a10 column username format a30 column machine format a30 column program format a50 set lines 132 set pages 500 select osuser, username, machine, program from v$session; select machine, username, count(1) from v$session group by machine, username;

REM ***** gsp.sql ***** REM ***** This is used to get SPID from SID. col username format a30 col machine format a20 col program format a40 accept _sid prompt ‘Enter Oracle Session ID ->’ select a.sid, b.pid, b.spid, a.username,a.program,a.machine from v$session a,V$process b where a.paddr = b.addr and a.sid = &_sid / REM ***** gsq.sql…

Read More “My Minimum Tuning Programs” »

Using following query, we can estimate # of rows resides in each USN (Rollback Segment) and who fired that transactions. New modified GCU.sql on 16-JUN-2006 /* Get Curent USN */ column start_dt format a20 set lines 132 select /*+ ALL_ROWS */ a.usn, a1.name, a.extents, a.hwmsize, a.status usn_status, to_char(b.start_date, ‘DD-MON-RRRR:HH24:MI:SS’) start_dt, b.status tx_status, c.sid, c.sql_id, b.used_ublk,…

Read More “How to find who is using which Rollback segment and how many rows or blocks in that rollback segments,” »

We can directly resize datafiles TEST.SQL>SELECT FILE_NAME, BYTES FROM DBA_DATA_FILES WHERE TABLESPACE_NAME=’SYSTEM’; FILE_NAME BYTES —————- ———- /…/dbsGNX.dbf 419430400 TEST.SQL>ALTER DATABASE DATAFILE ‘/…/dbsGNX.dbf’ RESIZE 390M; Database altered. TEST.SQL>SELECT FILE_NAME, BYTES FROM DBA_DATA_FILES WHERE TABLESPACE_NAME=’SYSTEM’; FILE_NAME BYTES ————— ———- /…/dbsGNX.dbf 408944640 But the minimum file size is the size of the extend the furthest in the datafile:…

Read More “How to Decide upto what level you can decrement your datafile size. ( Shrink Datafile)” »

Step-1 : Find the Query and its SQL_ID to trace from AWR reports. Step-2 : Execute following query to find real execution plan: SELECT LPAD(‘ ‘,2*(LEVEL-1))||operation||’ ‘||options ||’ ‘||object_name ||’ ‘|| DECODE(id, 0, ‘Cost = ‘||position) “Query Plan” FROM v$sql_plan START WITH id = 0 and sql_id=’8ska10yfjx4b5′ CONNECT BY PRIOR id = parent_id and sql_id=’8ska10yfjx4b5′;…

Read More “How to find the real execution plan and binds used in that explain plan in Oracle 10g??” »

SELECT owner, name, type FROM v$db_object_cache WHERE kept = ‘YES’ AND TYPE = ‘CURSOR’;