Tag Archives: database

Oracle: Basic Performance Tuning

This is a one page list of simple guidelines to tune SQL without much DBA knowledge.
This is all you need to know to tune some reports from 2 hours (or more) to 20 seconds (or less). I have compiled this list during a report tuning assignment
where I achieved that kind of runtime improvements repeatedly.
There are many more tips but they tend to be more difficult to understand and there are many good books already written about this. 

  • Understand the data. Look around table structures and data. Get a feel for the data model and how to navigate it.
  • If a view joins 3 extra tables to retrieve data that you do not need, don’t use the view!
  • When joining 2 views that themselves select from other views, check that the 2 views that you are using do not join the same tables!
  • Avoid multiple layers of view. For example, look for queries based on views that are themselves views. It may be desirable to encapsulate from a development point of view. But from a performance point of view, you loose control and understanding of exactly how much task loading your query will generate for the system.
  • Look for tables/views that add no value to the query. Try to remove table joins by getting the data from another table in the join.
  • WHERE EXISTS sub-queries can be better than join if can you reduce drastically the number of records in driver query. Otherwise, join is better.
  • WHERE EXISTS can be better than join when driving from parent records and want to make sure that at least on child exists. Optimizer knows to bail out as soon as finds one record. Join would get all records and then distinct them!
  • In reports, most of the time fewer queries will work faster. Each query results in a cursor that Reports has to open and fetch. See Reports Ref Manual for exceptions.
  • Avoid NOT in or NOT = on indexed columns. They prevent the optimizer from using indexes. Use where amount > 0 instead of where amount != 0.
  • Avoid writing where project_category is not null. nulls can prevent the optimizer from using an index.
  • Consider using IN or UNION in place of OR on indexed columns. ORs on indexed columns causes the optimizer to perform a full table scan.
  • Avoid calculations on indexed columns. Write WHERE approved_amt > 26000/3 instead of WHERE approved_amt/3 > 26000.
  • Avoid this: SUBSTR(haou.attribute1,1,LENGTH(‘:p_otc’)) = :p_otc). Consider this: WHERE  haou.attribute1 like :p_otc||’%’
  • Talk to your DBA. If you think that a column used in a WHERE clause should have an index, don’t assume that an index was defined. Check and talk to your DBA if you don’t find any.
  • Consider replacing outer joins on indexed columns with UNIONs. A nested loop outer takes more time than a nested loop unioned with another table access by index.
  • Consider adding small frequently accessed columns (not frequently updated) to an existing index. This will enable some queries to work only with the index, not the table.
  • Consider NOT EXISTS instead of NOT IN.
  • If a query is going to read most of the records in a table (more than 60%), use a full table scan.
  • Try to group multiple sub queries into one.

Beyond the Simple stuff …

  • If you want to actually understand what you are doing, here are a few things that you need to start playing with:
  • Get into EXPLAIN_PLAN. There are multiple way of doing this. The less user friendly is to simply issue this in SQL*Plus: explain plan set statement_id = ‘HDD1’ for ;
  • Look at the trace from Oracle Reports. It tells you how much time it spends on each query. With r25: C:ORANTBINR25RUN32.EXE module=p:oldbcmtrka1_hdd.rdf userid=opps/opps@new tracefile=p:trace3.txt trace_opts=(trace_all)
  • Use the SQL Trace by issuing an alter session set sql_trace=true; then look at it with TKPROF .trc .lis sort=(EXECPU).

If you remember nothing else …

  • Don’t apply these guidelines blindly, EXPERIMENT: compare one method to another. Do NOT expect that one trick will work all the time.
  • Educate yourself: read, read, read. It SAVES time!

Reference: http://www.iherve.com/oracle/tune100.htm


how to quickly install the Oracle Database Client product on 32-bit Windows

This guide describes how to quickly install the Oracle Database Client product on 32-bit Windows systems. It includes information about the following:

  1. Review Information About this Guide
  2. Log In to the System with Administrator Privileges
  3. Check the Hardware Requirements
  4. Check the Software Requirements
  5. Install the Oracle Database Software
  6. Install Products from the Oracle Database Companion CD
  7. What to Do Next
  8. Additional Information
  9. Documentation Accessibility

1 Review Information About this Guide

This guide describes how to install Oracle Database using the default installation options into a new Oracle home.

Tasks Described in this Guide

The procedures in this guide describe how to:

  • Configure your system to support Oracle Database.
  • Install the Oracle Database software on a local file system.
  • Configure a general-purpose Oracle database that uses the local file system for database file storage.
  • Install software from the Oracle Database Companion CD, which improves the performance of Oracle Database on your system.

Results of a Successful Installation

After you successfully install Oracle Database:

  • The database that you created and the default Oracle Net listener process are running on the system.
  • Oracle Enterprise Manager Database Control and iSQL*Plus are running and can be accessed using a Web browser.
  • A single-node version of the Oracle Cluster Synchronization Services (CSS) daemon is running and is configured to start automatically when your system boots.

Tasks Not Described in this Guide

This guide does not describe how to complete the following tasks:

  • Installing the software on a system that has an existing Oracle software installation.
  • Installing Oracle Clusterware and Oracle Real Application Clusters (RAC).
  • Installing Automatic Storage Management (ASM)
  • Cloning an Oracle home
  • Enabling Enterprise Manager e-mail notifications or automated backups.
  • Using alternative storage options such as Automatic Storage Management (ASM) or raw devices for database storage.

Where to Get Additional Installation Information

For more detailed information about installing Oracle Database components, including information about the tasks not described in this guide, see one of the following guides:

  • If you are installing the software on a single system or if you want to install Automatic Storage Management (ASM), see Oracle Database Installation Guide for Microsoft Windows (32-Bit).
  • If you are installing Oracle Real Application Clusters (RAC), see Oracle Real Application Clusters Installation and Configuration Guide for your platform.
    This guide also describes how to install Oracle Clusterware, which is a prerequisite for RAC installations.You can use Oracle Clusterware with single-instance Oracle Database installations as well.

Both of these guides are available on the Oracle Database installation media. To access them, use a Web browser to open the welcome.htm file in the database directory of the DVD, then select the Documentation tab.
For more information about installing Oracle Database Companion CD components, see one of the following guides:

  • Oracle Database Companion CD Installation Guide for Microsoft Windows (32-Bit)
  • Oracle Database Companion CD Quick Installation Guide for Microsoft Windows (32-Bit)

These guides are available in the companion directory on the Oracle Database installation media.

2 Log In to the System with Administrator Privileges

Log on as a member of the Administrators group to the computer on which to install Oracle components. If you are installing on a Primary Domain Controller (PDC) or a Backup Domain Controller (BDC), log on as a member of the Domain Administrators group.

3 Check the Hardware Requirements

The following hardware components are required for Oracle Database:

Table 1 Hardware Requirements
Requirement Minimum Value
Physical memory (RAM) 256 MB minimum, 512 MB recommended
Virtual memory Double the amount of RAM
Disk space Basic Installation Type total: 2.04 GB
Advanced Installation Types total: 1.94 GB
See Table 2 for details.
Video adapter 256 colors
Processor 550 MHz minimum

3.1 Hard Disk Space Requirements

Table 2 lists the space requirements for NTFS. The starter database requires 1.03 GB of disk space. The figures in this table include the starter database. FAT32 space requirements are slightly larger.

Table 2 Disk Space Requirements for NTFS
Installation Type TEMP Space C:Program FilesOracle Oracle Home Datafiles * Total
Basic Installation 125 MB 3.1 MB 905 MB 1.03 GB 2.04 GB
Advanced Installation: Enterprise Edition 125 MB 3.1 MB 905 MB ** 950 MB ** 1.94 GB **
Advanced Installation: Standard Edition 125 MB 3.1 MB 905 MB ** 950 MB ** 1.94 GB **
Advanced Installation: Personal Edition 125 MB 3.1 MB 905 MB ** 950 MB ** 1.94 GB **

* Refers to the contents of the admin, flash_recovery_area, and oradata directories in the ORACLE_BASE directory.
** This size can be higher depending on the installation options selected, such as languages or additional components. If you choose to install Oracle Database with automated backups enabled, include at least 2 GB extra for datafile disk space.

See Also:

“About NTFS File System and Windows Registry Permissions” in Oracle Database Platform Guide for Microsoft Windows (32-Bit)

To ensure that the system meets these requirements, follow these steps:

  1. Determine the physical RAM size. For a computer using Windows 2003, for example, open System in the Windows Control Panel and select the General tab. If the size of the physical RAM installed in the system is less than the required size, then you must install more memory before continuing.
  2. Determine the size of the configured virtual memory (also known as paging file size). For a computer using Windows 2003, for example, open System in the Control Panel, select the Advanced tab, and click Settings in the Performance section. Then select the Advanced tab. The virtual memory is listed in the Virtual Memory section.
    If necessary, see your operating system documentation for information about how to configure additional virtual memory.
  3. Determine the amount of free disk space on the system. For a computer using Windows 2003, for example, open My Computer, right-click the drive where the Oracle software is to be installed, and choose Properties.
  4. Determine the amount of disk space available in the temp directory. This is equivalent to the total amount of free disk space, minus what will be needed for the Oracle software to be installed.
    If there is less than 125 MB of disk space available in the temp directory, then first delete all unnecessary files. If the temp disk space is still less than 125 MB, then set the TEMP or TMP environment variable to point to a different hard drive. For a computer using Windows 2003, for example, open System in the Control Panel, select the Advanced tab, and click Environment Variables.

4 Check the Software Requirements

Table 3 lists the software requirements for Oracle Database.

Table 3 Software Requirements
Requirement Value
System Architecture Processor: Intel (x86), AMD64, and Intel EM64T
Note: Oracle provides 32-bit (x86), 64-bit (Itanium), and 64-bit (x64) versions of Oracle Database for Windows. The 32-bit database version, which this installation guide describes, runs on the 32-bit version of Windows on either x86 or x64 hardware. Oracle provides limited certification for 32-bit Oracle Database on 64-bit Windows (x64). For additional information, visit OracleMetaLink at:

http://metalink.oracle.com
Operating System Oracle Database for Windows is supported on the following operating systems:

  • Windows 2000 with service pack 1 or later. All editions, including Terminal Services and Microsoft Windows 2000 MultiLanguage Edition (MLE), are supported.
  • Windows Server 2003 – all editions
  • Windows XP Professional

Windows NT is not supported.
Windows Multilingual User Interface Pack is supported on Windows Server 2003 and Windows XP Professional.

Compiler Pro*Cobol has been tested and certified with the following two compilers:

  • ACUCOBOL-GT version 6.2
  • Micro Focus Net Express 4.0

Object Oriented COBOL (OOCOBOL) specifications are not supported.
The following components are supported with the Microsoft Visual C++ .NET 2002 7.0 and Microsoft Visual C++ .NET 2003 7.1 compilers:

  • Oracle C++ Call Interface
  • Oracle Call Interface
  • GNU Compiler Collection (GCC)
  • External callouts
  • PL/SQL native compilation
  • XDK

Note: If you plan to use GNU Compiler Collection as your primary compiler, see “Configuring GNU Compiler Collection as the Primary Compiler” in Oracle Database Installation Guide for Microsoft Windows (32-Bit) for configuration instructions.

Network Protocol The Oracle Net foundation layer uses Oracle protocol support to communicate with the following industry-standard network protocols:

  • TCP/IP
  • TCP/IP with SSL
  • Named Pipes

5 Install the Oracle Database Software

To install the Oracle Database software:

  1. If you are installing Oracle Database on a multihomed computer or a computer that uses multiple aliases, use the System in the Control Panel to create the ORACLE_HOSTNAME system environment variable. Set this variable to point to the host name of the computer on which you are installing Oracle Database.
  2. Insert Oracle Database installation media and navigate to the database directory. Alternatively, navigate to the directory where you downloaded or copied the installation files.
    Use the same installation media to install Oracle Database on all supported Windows platforms.
  3. Double-click setup.exe to start Oracle Universal Installer.
  4. In the Welcome window, select either Basic Installation or Advanced Installation, and then answer the prompts as needed.

    See Also:

    Chapter 1 in Oracle Database Installation Guide for Microsoft Windows (32-Bit) for more information on the Basic and Advanced installation methods

    The subsequent windows that appear, which are listed in Table 4, depend on the installation method you have chosen. The order in which the windows appear depends on the options you select.

  5. Follow these guidelines to complete the installation:
    • Do not install Oracle Database 10g release 2 (10.2) software into an existing Oracle home that contains Oracle9i or earlier software.
    • If you install Oracle Database 10g release 2 (10.2) in an Oracle home directory that already contains Oracle Database 10g release 2 (10.2) client software, the listener is not created. To create the listener, install and run Oracle Net Configuration Assistant after the installation. If the Administrator client is installed before Oracle Database, Oracle Net Configuration Assistant is already installed.
    • Follow the instructions displayed in the Oracle Universal Installer windows. If you need additional information, click Help.
    • When prompted for a password, follow these guidelines:
      Make the password be between 4 and 30 characters long.
      Use the database character set for the password’s characters, which can include the underscore (_), dollar ($), and pound sign (#) characters.
      Do not start passwords with a numeral.
      Do not use a user name for a password.
      Do no use Oracle reserved words for the password.
      Do not use change_on_install for the SYS account password.
      Do not use manager for the SYSTEM account password.
      Do not use sysman for the SYSMAN account password.
      Do not use dbsnmp for the DBSNMP account password.
      If you choose to use the same password for all the accounts, do not use change_on_install, manager, sysman, or dbsnmp as a password.
      Have the password include at least 1 alphabetic, 1 numeric, and 1 punctuation mark character
      Do not use simple or obvious words, such as welcome, account, database, and user for the password.

      Note:

      You must remember the passwords that you specify.

    • Do not modify the Java Runtime Environment (JRE) except by using a patch provided by Oracle Support Services. Oracle Universal Installer automatically installs the Oracle-supplied version of the JRE. This version is required to run Oracle Universal Installer and several Oracle assistants.
    • If you encounter errors while installing or linking the software, see Appendix G in Oracle Database Installation Guide for Microsoft Windows (32-Bit) for information about troubleshooting.
    • If you chose an installation type that runs Database Configuration Assistant and Oracle Net Configuration Assistant in interactive mode, you must provide detailed information about configuring your database and network.
      If you need assistance when using the Database Configuration Assistant or Oracle Net Configuration Assistant in interactive mode, click Help on any window.

      Note:

      If you chose a default installation, Database Configuration Assistant and Oracle Net Configuration Assistant run non-interactively.

  6. When the configuration tools finish, click Exit, then click Yes to exit from Oracle Universal Installer.
  7. When Oracle Enterprise Manager Database Control opens a Web browser, enter the username and password you created during the installation.
    You can log in as SYS, SYSTEM, or SYSMAN. If you log in as SYS, then you must connect AS SYSDBA. Enter the password you specified for the account during installation.
  8. Optionally, delete the OraInstalldate_time directory if you want to remove the temporary files that were created during the installation process. The OraInstalldate_time directory holds about 45 MB of files. This directory is created in the location set by the TEMP environment variable setting.
    Restarting your computer also removes the OraInstalldate_time directory.
  9. See Chapter 4 in Oracle Database Installation Guide for Microsoft Windows (32-Bit) for information about postinstallation tasks that you must complete after you have installed Oracle Database.

Table 4 Oracle Universal Installer Screens
Screen Recommended Action
Select Installation Method Select one of the following, then click Next:

  • Basic Installation: Lets you quickly install Oracle Database using minimal input. It installs the software and optionally creates a general-purpose database using the information that you specify on this window.
  • Advanced Installation: Lets you perform more complex installations, such as creating individual passwords for different accounts, creating specific types of starter databases (for example, for transaction processing or data warehouse systems), using different language groups, specifying e-mail notifications, and so on.
Select Installation Type Select Enterprise Edition, Standard Edition, Personal Edition, or Custom. Click Next.
Specify Home Details In the Destination section, accept the default values or enter the Oracle home name and directory path in which to install Oracle components. The directory path should not contain spaces.
Click Next.
Available Product Components If you selected Custom for the Installation Type, this window appears. Select from the list and click Next. To learn more about each component, position the mouse over the component’s name.
Product-specific Prerequisite Checks This window checks that your system meets the minimum requirements for the installation. Click Next.
Upgrade an Existing Database If you have a previous updatable version of Oracle Database or Automatic Storage Management installed, this window appears. For in-place database installations where Automatic Storage Management is running, ASM is upgraded automatically.
Click Yes if you want to upgrade or No if not. If you click Yes, the Summary window appears next.
For more information on upgrades, refer to Oracle Database Upgrade Guide.
Select Configuration Option Select one of the following:

  • Create a database: Select this option if you are creating a database using the following database types: General purpose, Transaction processing, Data warehousing. The Advanced option starts Database Configuration Assistant for advanced database creation configuration options.
  • Configure Automatic Storage Management (ASM): Select this option to create an Automatic Storage Management instance only. To create an ASM instance, you must provide an ASM SYS Password. After you provide this password, Oracle Universal Installer lets you create an ASM disk group. After you complete this Oracle Universal Installer session, you can run it again to install and configure one or more Oracle databases that will use ASM.
  • Install database Software only: Select this option to install the database software only but not create a database or configure Automatic Storage Management.
Select ASM Management Option If you selected Configure Automatic Storage Management (ASM) from the Select Configuration Option window, and if you have Oracle Enterprise Manager 10g Grid Control installed, this window appears. Select Yes or No, depending on if you want to use Grid Control to manage Automatic Storage Management. If you select Yes, then select from the list of Enterprise Management agents to use.
Configure Automatic Storage Management Appears if you selected Configure Automatic Storage Management (ASM) from the Select Configuration Option window. Enter the disk group name. The disk group list shows both candidate and member disks; you can click Show Candidates or Show All to filter their display. Then select the disk group’s redundancy level and its member disks.
For Redundancy Level, choose one of the following. The default is normal redundancy.

  • High: The contents of the disk group are three-way mirrored by default. To create a disk group with high redundancy, specify at least three failure groups (a minimum of three devices).
  • Normal: By default the datafiles of the disk group are two-way mirrored and the control files are three-way mirrored. You can create certain files that are three-way mirrored or not mirrored. To create a disk group with normal redundancy, specify at least two failure groups (a minimum of two devices) for two-way mirroring.
  • External: ASM does not mirror the contents of the disk group. Choose this option when 1) the disk group contains devices, such as RAID devices, that provide their own data protection; or 2) the database does not require uninterrupted access to data, for example, if you have a suitable backup strategy already in place.
Select Database Configuration Select the database configuration that best meets your needs: General Purpose, Transaction Processing, Data Warehouse; Advanced.
See the online help provided by either Oracle Universal Installer or Database Configuration Assistant for a description of these preconfigured database types.
Click Next.

6 Install Products from the Oracle Database Companion CD

The Oracle Database Companion CD contains products that improve the performance of or complement Oracle Database. For most installations, Oracle recommends that you install Oracle Database 10g Products from the Companion CD.

See Also:
  • Oracle Database Companion CD Installation Guide for Microsoft Windows (32-Bit)
  • Oracle Database Companion CD Quick Installation Guide for Microsoft Windows (32-Bit)

7 What to Do Next

To become familiar with this release of Oracle Database, Oracle suggests that you complete the following tasks:

  • Log in to Oracle Enterprise Manager Database Control using a Web browser.
    Oracle Enterprise Manager Database Control is a Web-based application that you can use to manage a single Oracle database. The default URL for Database Control is:
    http://host.domain:port/em/

    Log in with the user name SYSMAN. Use the password that you specified for this user during the Oracle Database installation.

  • See Chapter 4 of Oracle Database Installation Guide for Microsoft Windows (32-Bit) for information about required and optional postinstallation tasks, depending on the components that you want to use.
  • Review Chapter 5 of Oracle Database Installation Guide for Microsoft Windows (32-Bit) for information about how to get started using Oracle Database.
  • Read Oracle Database 2 Day DBAOracle Database 2 Day DBA guide, to learn more about using Oracle Enterprise Manager Database Control to administer a database.
    This guide, designed for new Oracle DBAs, describes how to use Oracle Enterprise Manager Database Control to manage all aspects of an Oracle database installation. It also provides information about how to enable e-mail notifications and automated backups, which you might not have configured during the installation.

8 Additional Information

This section contains information about the following:

Product Licenses

You are welcome to install and evaluate the products included in this media pack for 30 days under the terms of the Trial License Agreement. However, you must purchase a program license if you want to continue using any product after the 30 day evaluation period. See the following section for information about purchasing program licenses.

See Also:

Oracle Database Licensing Information

Purchasing Licenses, Version Updates, and Documentation

You can purchase program licenses, updated versions of Oracle products, and printed versions of Oracle documentation from the Oracle Store Web site:

http://oraclestore.oracle.com/

Contacting Oracle Support Services

If you have purchased Oracle Product Support, you can call Oracle Support Services for assistance 24 hours a day, seven days a week. For information about purchasing Oracle Product Support or contacting Oracle Support Services, go to the Oracle Support Services Web site:

http://www.oracle.com/support/

Locating Product Documentation

Documentation for Oracle products is available in both HTML and Adobe portable document format (PDF) formats from several locations:

  • On disks in the media pack:
    • Platform-specific documentation is available on the installation media. To access the documentation, see the welcome.htm file located in the top-level directory of the DVD.
    • Generic product documentation is available on the Oracle Documentation Library on the DVD.
  • From the Oracle Technology Network Web site:
    http://www.oracle.com/technology/documentation/

To view PDF documents, download the free Adobe Acrobat Reader from the Adobe Web site, if necessary:

http://www.adobe.com/

9 Documentation Accessibility

Our goal is to make Oracle products, services, and supporting documentation accessible, with good usability, to the disabled community. To that end, our documentation includes features that make information available to users of assistive technology. This documentation is available in HTML format, and contains markup to facilitate access by the disabled community. Accessibility standards will continue to evolve over time, and Oracle is actively engaged with other market-leading technology vendors to address technical obstacles so that our documentation can be accessible to all of our customers. For more information, visit the Oracle Accessibility Program Web site at
http://www.oracle.com/accessibility/

Accessibility of Code Examples in Documentation

Screen readers may not always correctly read the code examples in this document. The conventions for writing code require that closing braces should appear on an otherwise empty line; however, some screen readers may not always read a line of text that consists solely of a bracket or brace.