User Tools

Site Tools


Oracle Database 512 pages
Trace File 10053 to understand the Optimizer (CBO)(Access|Scan) (Paths|Method)Oracle Automatic Storage Management Cluster File System (ACFS)ACTIVE_INSTANCE_COUNT parameter(Actual|Final) Execution PlanActual (Runtime) Plan StatisticsAdaptive OptimizationAdaptive PlanADDM (Automated Database Diagnostic Monitor) - Automatic Performance Diagnostics ToolAnalyticsSQL - Analytic FunctionsMode Archive Log / ArchivingArchived Redo Log FilesARCHIVELOGHow to change the Database Archiving ModeHow to know the archivelog mode ?The effect of ArraySize on Logical I/OASM (Automatic Storage Management)Prepare Storage for Oracle Automatic Storage Management on LinuxAuthenticationAUTHIDAutomatic reoptimization / (statistics|cardinality) feedbackAutonomous TransactionsAutoTask (Automated maintenance tasks)AutotraceAWR (Advanced|Automatic) - Workload Repository - Performance Statistics Gathering ToolBackground ProcessOracle Databse - SQL - betweenBitmap IndexesBlevel Index Statistics(Data|Db|Logical|Oracle) Block or Page(B|Balanced) Tree - BTree indexesBuffer IO (Logical IO)Buffer cacheBuffer PoolHow can we load a large number of rows into an indexed existing table ?Bytes or Characters for VARCHAR2 and CHAROracle Database - bytes sent via SQL*Net to client(Memory) Cache (Tables, ...)Rows (of Card as Cardinality for 9i)Cartesian Joins OperationCascade(Consolidated|Root) Database (Cdb)ORACLE - Streams / Change Data CaptureHow to change the password for the account SYS/SYSTEM ?Character SetConfigure Database Options with the chopt toolClient ApplicationCluster Scan/AccessThe (index) Clustering Factor StatisticsCman.ora configuration fileColumns StatisticsCOMMIT (of a transaction)CompileCompressionConcurrency Wait ClassHow to connect SYS as SYSDBA without passwordConnect Descriptor (TNS Connect String)Connect IdentifierConnect String (Client Connection Initiation)Oracle Connection ManagerHow to retrieve the connect descriptor parameters? (host, port, service name and SID)Connection PoolRead ConsistencyConsistent (Read get|Buffer Mode) (CR)(Integrity) Constraints(Application) ContextControl FilesCostCPU_COUNT parameterCreate table as select (CTAS)CURRENT_SCHEMACURRENT_USER, SESSION_USERData DictionaryData FilesData Load(Data operations|Execution Plan Steps)Data Pump - (Export|Import) UtilitiesData Redistribution (Parallel)DatabaseOracle Enterprise Manager Database Control (Database Control)Database LinkDatabase ServiceOracle - DataTypeCharacter DataTypeOracle - Data type ConversionDatatype DatetimeNumeric DatatypeData WarehousingOracle - Date Datatype(Db Block Get|Current Get) Buffer Mode (CU)DB_BLOCK_SIZE initialization parameterDB_DOMAINDB_FILE_MULTIBLOCK_READ_COUNT Parameter'db file scattered read' wait event db file sequential read Wait EventDB_NAMEDB_UNIQUE_NAME parameterDatabase Administrator (Dba)DBCADBMS_APPLICATION_INFO to manage long-running operationDBMS_FLASHBACKDBMS_LOB PackageDBMS_LOCKDBMS_MONITORDbms_mview PackageDBMS_SPACE PackagePL/SQL Package DBMS_XDBDBMS_XPLANDatabase Writer Process (DBWn)DDL Locks (data dictionary lock)DeadlockDebug.f for debugging PL/SQL applicationDedicated Server ArchitectureDedicated Server ProcessDimensionsDirect (path insert|load) (/*+ APPEND */)"direct path read temp" and "direct path read" wait eventDirectory ObjectDirectory naming method (LDAP-compliant)DISK_ASYNCH_IO parameterASM - Disk groupDispatcher and Shared Server ProcessExplain Plan from Cursor Cache (DBMS_XPLAN.DISPLAY_CURSOR)Oracle SQL - DISTINCT (or UNIQUE)Distributed TransactionsDML Lock(Degree|Degree of Parallelism (DOP))Implications of the Driving TableDUALDynamic (Statistics|Sampling)PL/SQL - Dynamic SQLEasy Connect Method with host, port and service name (EZCONNECT)Entreprise ManagerEnvironment Variables / Registry ValuesOracle Database 11g: Administration I - 1Z0-052 PL/SQL - Execute ImmediateExecution(Execution|Query) PlanHow to read a query plan ?Export Utility (exp)Data Pump Export (expdp)Explain PlanEXPLAIN PLAN and Autotrace Comparison(Logical) Extent (Unit of Storage)External naming methodExternal TablesHow to load a CLOB field with an external tableOracle database - File (Common Specification for data file, control file,...)File SystemEffect of a filter on a outer join tableFIRST_ROWSSQL - First Value Analytic function_FIX_CONTROL Optimizer ParameterFlash CacheFlashbackFlashback QueryFloating-Point Number Datatype (BINARY_FLOAT, BINARY_DOUBLE)FOREIGN KEY Constraints(Fragmented) (Unused|Free) space (Reclaim|Shrink|resize)Full Table ScansOracle - Function-based IndexGateway (Heterogeneous services)Gateway ArchitectureGateway Data DictionaryGateway - Data Dictionary (from the remote data source) - The Translation ViewsGateway LogGateway Agent RegistrationGlobal Database Name (GLOBAL_NAME)GLOBAL_NAMES parameterGlossaryGolden Gate (OGG)Granule (Parallel Data Access)Grid ControlOperating System GroupHard Parse (Library cache miss)Oracle Database and HardwareHash Access / ScanHASH AREA memoryHASH_AREA_SIZE parameterHash joins(Level|Height) Index statisticsHigh Water Mark (HWM) - boundary between used and unused spaceData Loading HintHintsHistogram (Column statistics)Histogram AnalyticHow to create a time dimension table in Sql ?PLSQL - IF StatementImport (imp)Data Pump Import (impdp)IndexesIndex ScansIndex StatisticsINITRANS parameterInsert StatementInstallation of a database RAC 10gInstallation of 11gR1/R2 on WindowsInstallation 11g Release 2 (11.2) on Linux OEL 5 (X86)Oracle Database 11gR2 - Installation on Linux OEL 5 Update 5 (x86_64)12c Installation on Windows 64 bitInstallationInstallation Oracle Database 10gR2 on WindowsInstance (Database Start-up)INSTANCE_NAME parameterINTEGER DatatypeInterconnect Traffic (RAC)Internal LocksOracle SQL - IntervalInput/Output (I/O)Oracle - JavaOracle - Java/JSERVER - Debug Load / CompilationOracle - JSERVER - How to get the Java Version ?Procedure Stockee JavaJava Database Connectivity (JDBC) DriverJoin (Operation|Method|Mechanism|Algorithm)Join OrderSQL - Analytic Function LAG/LEADNLS_LANGUAGE parameterLatches (System Lock)Leaf (block|Node) of a Btree indexLog Writer (lgwr)Session (Library|Cursor) CacheLike SQL ConditionConfiguration of the Listener (listener.ora)ListenerOracle - LOB DatatypeLocal Naming MethodLocksLocks and Foreign Keys - Concurrency control of parent keysLogical StructureLogMinerLong and Long Raw DatatypeLRUMaterialized view (Snapshot)MemoryMERGE JOIN operationOracle OLAP - Methodology for Defining the Analytic WorkspaceMethodology for Designing and Building Materialized ViewsOracle Database SQL - MinusMemory manager (MMAN)SQL - Model ClauseOracle - MonitoringMultiblockMutexesNaming conventionNaming method (naming resolution method)Nested Loop JoinOperations Involved in NESTED LOOPSOracle Net (SQL*Net)Oracle Net ManagerNet Service AliasNet Service Name (tnsname)Oracle Net Configuration AssistantNLS_DATE_FORMATOracle - NLS_DATE_LANGUAGENLS_LANG (LOCALE)NLS_LENGTH_SEMANTICS parameterNLS_NUMERIC_CHARACTERS (decimal separator and thousands separator)Globalization (Localization, NLS parameter)NLS_SORT (Sorting Character parameter)NOARCHIVELOGNOLOGGINGNon-schema ObjectNull value (Missing Value)NUMBER Data TypeObjectsODBCOracle ODBC driverOptimal Flexible Architecture (OFA)Query optimizerOPTIMIZER_FEATURES_ENABLE ParameterOptimizer Goal (CBO/RBO)ORA-01720 - grant option does not exist for 'AnObject'Error (Ora-XXXXX)ORA_HASH FunctionORA_NLSOracle Validated Configuration RPM for a Linux InstallationBackupOracle base directory (ORACLE_BASE)Character Set Functions : CONVERT, UNISTRA Common Business Problem : Messages (AQ)AuditingOracle Database Is Function (IsDate, ...)Green/Grid ComputingOracle Home Directory (ORACLE_HOME|OH)ORACLE_HOSTNAME environment variableHow to read data through an ODBC connection with Gateway (Heterogeneous services)Oracle OlapORACLE_SID environment variableORACLE_UNQNAME environment variableDatatype difference between VARCHAR2 and CHARHow to reconfigure Enterprise Manager Database Control (with Emca)How to find the version informations of the Database ?How to detect read from the temporary tablespace ?Table Logging Mode (LOGGING, NOLOGGING)Oraenv and coraenv script to set Linux environment variableOracle Inventory directory (oraInventory)OUI-10044: The selected Oracle Home ... Oracle Universal Installer (OUI)Parallel OperationsPARALLEL_DEGREE_LIMITOracle Database 11g - PARALLEL_DEGREE_POLICY parameterParallel DMLHow to (enable|disable) parallel query and get (degree of parallelism|DOP) ?Parallel_execution_message_size parameterPARALLEL_MAX_SERVERS parameterPARALLEL_MIN_PERCENT parameterPARALLEL_MIN_SERVERS parameterParallel Execution MonitoringParallel execution with Oracle Partitioning (parallel partition-wise join)Parallel execution in an RAC environment(Parallel|PX) ServerPARALLEL_SERVER_TARGETPARALLEL_SERVERS_TARGET parameterPARALLEL_THREADS_PER_CPU Parameter(Initialization|System) ParametersParameter FilesSQL ParsingPartitionsPartition Exchange for Data Movement / Data LoadPartition Pruning (Elimination)Partition-Wise Join (PWJ)Partitioning and Materialized ViewsPATH environment VariablePattern matchingPCTFREE (Percentage Free)Pluggable Database (PDB)PerformancePerformance statistics (V$ views)Process global area (PGA) - Work AreaPGA_AGGREGATE_TARGET ParameterPhysical I/OPhysical ReadPhysical Database Structures (Files)(Pivot|Unpivot) StatementThe Query Plan EstimatorThe Query Plan GeneratorPMON processPooled Server ArchitecturePort AssignementPL/SQL - PragmaMaterialized Views (ROLAP) and Cubes (MOLAP) comparison (Pre Compute Operations)Execution Plan PredicatePrimary KeyPrivilegesProducer / Consumer ModelUser ProfileParallel SlavesThe Query Coordinator (QC)Oracle - Controlling the Behavior of the Query Optimizer with initialization parameters(Iterative|Query) PlanDatabase Oracle - Query RewriteOracle - The Query TransformerExample of query Tuning (to decrease the logical I/O)Oracle Real Application Clusters (RAC)SQL - RANK FunctionRDFReadRecoveryOracle - Recursive CallsOracle - Recursive call caused by Hard ParseOracle - Recursive call caused by PL/SQL functionOracle - Recursive calls caused by data modification (triggers, function based index, ...)Recursive call caused by space requestsRecycle Bin (Flashback Drop)(online table) RedefinitionWhy you have still a lot of redo and archive ? The Index side effect.Redo Log (Log Files)How to bypass the redo log generation ?Redo Size statisticsSQL - Regular expressionResourcesResource Manager (DBRM)Ressource ManagerResult SetReverse Key IndexesRoleROLLBACK (undo of a transaction)Row Locks (TX)SQL - ROW_NUMBER functionRow OperationRow SetRow source generatorRowidRowid Scans operationRownumRunstatsSample SQL Clause for Table Scans/AccessSample SchemasSAVEPOINT (of a transaction)SchedulerSchemaSchema ObjectSystem Change Numbers (SCNs)Session Data Unit (SDU) parameterUser, Security(Logical) Segment (Database Object Storage)Oracle SQL - SELECTSELECT_CATALOG_ROLESELECT ... FOR UPDATE statementSelectivitySequenceServer ProcessServer Result CacheService HandlerService nameSERVICE_NAMES parameterService RegistrationSessionSession Statistics (V$SESSTAT)SQL Plus - Set Null(Set|Set-based) OperationSystem Global Area - SGA (Shared Memory)Shared PoolShared server architectureShared sql areaSystem Identifier (SID)Skip_unusable_indexes parameterSoft parse (Library cache hit)(Software|Installation) Owner (oracle, grid)Sort Aggregate OperationSort Area memorySORT_AREA_SIZE parameterSORT JOIN (or SORT MERGE JOIN)Sorts (Disk)Sorts (memory) statisticsSpace (Size)Special Characters(initialization|server) parameter file (init.ora | spfile.ora)SQLSQL Access Advisor (Summary Advisor)SQL*Net roundtrips to from clientOracle Database - SQL*Net StatisticsSQL Parallel ExecutionSQL Plan ManagementSQL Processing - How Oracle Database processes SQL statements?Oracle - SQL_TRACESQL Tuning AdvisorSQL Loader - Lob FileSQLNET.ORAStandBy DatabaseStar TransformationAutomating Shutdown and Startup on LinuxOptimizer StatisticsSetting Up StatspackStattabStorage OptionsStored ProcedureJava Stored ProcedurePL/SQL - Result Sets from Stored ProceduresSynonym (Object Alias)SYSASM privilegesSysdateSYSDBA privilegeV$SYSMETRICSYSOPER privilegeSystem LockSYSTEM TablespaceTableTable Function (Pipelined)Table Lock (TM)Table Size(Optimizer) Table StatisticsTableSpaceTemporary Datafiles (tempfiles)Oracle Database 12c - Temporal ValidityTemporary TablespaceTerritory (NLS_TERRITORY)TIMED_STATISTICS parameterTIMESTAMP data typeOracle - TkprofTNS_ADMIN environement variableTnsnames.oraTNS Ping UtilityTO_NUMBER functionTrace ToolTrace File (.trc)TransactionsTransaction table - Interested transaction list (ITL)TriggerTwo-Task Common (TTC) protocolLOCAL or TWO_TASK Environment VariableUNDOUnionUNIQUE ConstraintsUpdateV$object_usageUser (Account/Client)Userenv (Current Session Informations)Utl_mail packagePL/SQL - Using utl_match functions to compare string similarityV$SQLV$SQL_PLANV$SQL_PLAN_STATISTICSV$SQL_PLAN_STATISTICS_ALLV$SQL_WORKAREAV$SQLAREAV$ tableVersionViewVirtual circuitVpd (Virtual Private Database)Wait EventWhat are the benefits to define a dimension in Oracle ?SQL - With clause(Query) Work AreaWORK_AREA_SIZE_POLICYXmlOracle XML DB


Debug.f for debugging PL/SQL application


It's a package designed to allow you to add logging or trace statements to a PL/SQL application.


CREATE PROCEDURE p ( p_owner IN VARCHAR2, p_object_name IN VARCHAR2)
    l_status NUMBER := 0 ;
    Debug.f ('Entering procedure, inputs "%s", "%s" )
    .... some code ....
    Debug.f ('Normal EXIT, status = %d', l_status );

If the debug package where told to generate a trace file via a call to debug.init, you will have this :

12062002 213953(P.PROCEDURE  5) Enter procedure inputs "A", "B"
12062002 213955(P.PROCEDURE 56) Normal exit, status = 0

Where :

  • 12062002 is the date MMDDYYYY format
  • 213953 is the time HH24MISS format

How to install it

      CREATE USER utility identified BY utility;
      ALTER USER utility DEFAULT tablespace users quota unlimited ON users;
  • run install_debug in that schema
  • create a public synonym for debug


  • Bookmark "Debug.f for debugging PL/SQL application" at
  • Bookmark "Debug.f for debugging PL/SQL application" at Digg
  • Bookmark "Debug.f for debugging PL/SQL application" at Ask
  • Bookmark "Debug.f for debugging PL/SQL application" at Google
  • Bookmark "Debug.f for debugging PL/SQL application" at StumbleUpon
  • Bookmark "Debug.f for debugging PL/SQL application" at Technorati
  • Bookmark "Debug.f for debugging PL/SQL application" at Live Bookmarks
  • Bookmark "Debug.f for debugging PL/SQL application" at Yahoo! Myweb
  • Bookmark "Debug.f for debugging PL/SQL application" at Facebook
  • Bookmark "Debug.f for debugging PL/SQL application" at Yahoo! Bookmarks
  • Bookmark "Debug.f for debugging PL/SQL application" at Twitter
  • Bookmark "Debug.f for debugging PL/SQL application" at myAOL
database/oracle/debugf.txt · Last modified: 2009/03/16 13:29 by gerardnico