Skip to content
pvmehta.com

pvmehta.com

  • Home
  • About Me
  • Toggle search form
  • My Minimum Tuning Programs Oracle
  • move_arch_files.ksh Linux/Unix
  • Privilege to describe the table. Oracle
  • Korn Shell Arithmatic Linux/Unix
  • set_env_dba Linux/Unix
  • Caching sequence in Memory Oracle
  • Zip and unzip with tar Linux/Unix
  • Changing Instance Name ( No DB_NAME) Oracle
  • For Search and replace unix command. Linux/Unix
  • VIvek Encryption Package and Its Usage Oracle
  • get_ratio.sql get the ratio of users from v$session and this uses CASE-WHEN-THEN clause Oracle
  • Find Time Consuming SQL Statements in Oracle 10g Oracle
  • Is It Recommended To Apply Patch Bundles When PSU Is Available? -ID 743554.1 Oracle
  • Unix split command to split files Linux/Unix
  • Useful Solaris Commands on 28-SEP-2005 Linux/Unix

Category: SQL scripts

How to find the real execution plan and binds used in that explain plan in Oracle 10g??

Posted on 09-Jun-2006 By Admin No Comments on How to find the real execution plan and binds used in that explain plan in Oracle 10g??

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??” »

Oracle, SQL scripts

How to find pinned objects from shared pool. (pinned via dbms_shared_pool.keep)

Posted on 08-Jun-2006 By Admin No Comments on How to find pinned objects from shared pool. (pinned via dbms_shared_pool.keep)

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

Oracle, SQL scripts

how to find OS block size

Posted on 01-Jun-2006 By Admin No Comments on how to find OS block size

13:01:01 SYS@AWRTEST:-> select lebsz from x$kccle; LEBSZ ——— 512 512 512 3 rows selected. or df -g on unix prompt.

Oracle, SQL scripts

Oracle Identifiers

Posted on 31-May-2006 By Admin No Comments on Oracle Identifiers

Oracle Identifiers: =================== In order not to make using Oracle confusing, there are some identifiers whose meaning must be totally clear if one want to work on Oracle: SID (System Identifier): ======================== A SID (almost) uniquely identifies an instance. Actually, $ORACLE_HOME, $ORACLE_SID and $HOSTNAME identify an instance uniquely. The SID is 64 characters, or less;…

Read More “Oracle Identifiers” »

Oracle, SQL scripts

DETERMINING WHICH INSTANCE OWNS WHICH SHARED MEMORY & SEMAPHORE SEGMENTS

Posted on 24-May-2006 By Admin No Comments on DETERMINING WHICH INSTANCE OWNS WHICH SHARED MEMORY & SEMAPHORE SEGMENTS

To obtain the shared memory id and semaphore id for 8.1.X you can do either of the following: $ORACLE_HOME/bin/sysresv IPC Resources for ORACLE_SID “V817” : Shared Memory: ID KEY 14851 0x8a85a74c Semaphores: ID KEY 11206656 0x4bd4814c Oracle Instance alive for sid “V817” OR %sqlplus internal SQL> oradebug ipc Information written to trace file. Trace file…

Read More “DETERMINING WHICH INSTANCE OWNS WHICH SHARED MEMORY & SEMAPHORE SEGMENTS” »

Oracle, SQL scripts

executing Function from SQLPLUS prompt

Posted on 21-May-2006 By Admin No Comments on executing Function from SQLPLUS prompt

SQL> variable komal number SQL> begin 2 :komal := add_me(45,12); 3 end; 4 / PL/SQL procedure successfully completed. SQL> print komal KOMAL ———- 57 SQL> variable tejal number; SQL> execute :tejal := add_me(10,20) PL/SQL procedure successfully completed. SQL> print tejal TEJAL ———- 30 SQL>

Oracle, SQL scripts

Parallel DML

Posted on 04-May-2006 By Admin No Comments on Parallel DML

How to execute any DML statement in Parallel mode?? (1) ALTER SESSION ENABLE PARALLEL DML; (2) Apply Parallel(table_name, parallel_degree) in hint. You can see three new clumns (TQ, IN-OUT, PQ Distribut) in execution plan of “select * from table(dbms_xplan.display())”. These new columns will specify the query is running in parallel mode.

Oracle, SQL scripts

Find Multiple levels of object dependencies : depen.sql

Posted on 21-Apr-2006 By Admin No Comments on Find Multiple levels of object dependencies : depen.sql

set echo off set head off set lines 130 set verify off accept v_owner prompt ‘Enter Owner Name : ‘ accept v_name prompt ‘Enter Object Name : ‘ accept v_type prompt ‘Enter Object Type : ‘ prompt Dependency tree for OWNER : &v_owner, OBJECT_NAME : &v_name OBJECT_TYPE : &v_type prompt ========================================================================================== SELECT rpad (rpad(‘*’, LEVEL*2,…

Read More “Find Multiple levels of object dependencies : depen.sql” »

Oracle, SQL scripts

SCRIPT TO LIST RECURSIVE DEPENDENCY BETWEEN OBJECTS UTLDTREE.sql

Posted on 21-Apr-2006 By Admin No Comments on SCRIPT TO LIST RECURSIVE DEPENDENCY BETWEEN OBJECTS UTLDTREE.sql

Subject: Script To List Recursive Dependency Between Objects Doc ID: Note:139594.1 Type: BULLETIN Last Revision Date: 16-JUN-2004 Status: PUBLISHED PURPOSE ——- This script provides a listing of how objects depend on other given objects such as packages, package bodies or procedures recursively. SCOPE & APPLICATION ——————- You often get invalidated objects and would like to…

Read More “SCRIPT TO LIST RECURSIVE DEPENDENCY BETWEEN OBJECTS UTLDTREE.sql” »

Oracle, SQL scripts

DBMS_PROFILER for tuning PLSQL programs.

Posted on 21-Apr-2006 By Admin No Comments on DBMS_PROFILER for tuning PLSQL programs.

Sometimes when all the SQL statements are well tuned but the routine still takes noticeable time to execute. DBMS_PROFILER Oracle 8i provides a new tool called PL/SQL Profiler. This is a powerful tool to analyze a Program unit execution and determine the runtime behavior. It gives execution time for individual statemenets in procedure, functtion or…

Read More “DBMS_PROFILER for tuning PLSQL programs.” »

Oracle, SQL scripts

Posts pagination

Previous 1 … 20 21 22 … 35 Next

Categories

  • Ansible (0)
  • AWS (2)
  • Azure (1)
  • Linux/Unix (149)
  • MYSQL (5)
  • Oracle (393)
  • PHP/MYSQL/Wordpress (10)
  • POSTGRESQL (0)
  • Power-BI (0)
  • Python/PySpark (7)
  • RAC (17)
  • rman-dataguard (26)
  • shell (149)
  • SQL scripts (342)
  • SQL Server (6)
  • Uncategorized (0)
  • Videos (0)

Recent Posts

  • Trace a SQL session from another session using ORADEBUG30-Sep-2025
  • SQL Server Vs Oracle Architecture difference25-Jul-2025
  • SQL Server: How to see historical transactions25-Jul-2025
  • SQL Server: How to see current transactions or requests25-Jul-2025
  • T-SQL Vs PL/SQL Syntax25-Jul-2025
  • Check SQL Server edition25-Jul-2025
  • Checking SQL Server Version25-Jul-2025
  • Oracle vs MYSQL Architecture differences (For DBAs)24-Jul-2025
  • V$INSTANCE of Oracle in MYSQL24-Jul-2025
  • Day to day MYSQL DBA operations (Compared with Oracle DBA)24-Jul-2025

Archives

  • 2025
  • 2024
  • 2023
  • 2010
  • 2009
  • 2008
  • 2007
  • 2006
  • 2005
  • Oracle Metalink useful notes Oracle
  • Linux CPU info. Linux/Unix
  • Monitor Long Running Job Oracle
  • create PLAN_TABLE command. Oracle
  • This is from Temi Oracle
  • Finding locked objects Oracle
  • fkwoind.sql fkwoindex.sql Oracle
  • CPU Core related projections AWS

Copyright © 2025 pvmehta.com.

Powered by PressBook News WordPress theme