select
      'BUFFER_POOL_KEEP = ('||sum(s.blocks)||',2)'
      from
      Dba_segments s
      Where
      s.buffer_pool = 'KEEP'
      /

1) For Querying the Tables which are Fully Scanned Recently Pleases Use the Following Query

      Create view RecentFullScans(Owner, tableName) as
      SELECT distinct u.name owner, o.name
      FROM obj$ o,x$bh x, user$ u
      WHERE x.obj=o.obj#
      AND o.type#=2
      AND standard.bitand(x.flag,524288)>0
      AND o.owner#=u.user#
      AND u.name <>'SYS'
      order by 1,2;

2) To Get information about the Various TABLE Scans (Short and long Tables) please use the Following Query.

      selectname,value from v$sysstat
      where name like '%table scans%';
      if the large table scans(In number of Percentage)are Higher than we need to Create indexes but if the Small
      table Scans are Higher we need to Cache the Tables Using the Following Commands forImproving
      the Performace.

      alter table SchemaName.TableName Cache;

Chained Rows Analysis and Removal

1.occurs when an update causes a row to increase in length so that it no longer fits in the database block.

   - Oracle is forced to find another data bblock, which is chained to the original.
   - Increases the number of I/Os needed to accomplish a task.
   - Performance degrades quickly
   - chaining rarely happens early on so thaat the distance between the two data blocks requires a substantial
     amount of thrashing.

2.Analysis:

3.run the utlchain.sql script ( ~oracle/dbms/adm/utlchain.sql ) this creates the
   CHAINED_ROWS table
   run the analyze command with the list chained rows option

4.AnalyzeSchemaName.Tablenamelist chained rows

5.Once analyzed all the Chained rows will be listed in the Sys.Chained_rows table.
   Problem Resolution:
   If the ratio of chained rows: row count is low: Move the chained rows to a temp table Delete the original records
   Move the records from the temp table back to the original table as an insert
   Drop the temp table
   Delete the corresponding records in the CHAINED_ROWS table
   re-run the analyze command
   If the ratio of chained rows : row count is high:
   Two Causes: (bad PCTFREE factor or Row Length is larger than Block Size)
   If the Row Length is larger than Block Size thenHead_rowidisthe pseudo column
   that is used forQueryingthe Chained_rows
   For select therow use the query such as
            Select * from lms.xyz where rowid in (SELECT head_rowid
            FROM chained_rowsWHEREtable_name = upper('&1'));
            This will give all the rows that are chained in that particular table.

Regarding theinit.oraparameterssetting
1) DB_BLOCK_BUFFERS :30000(Specified in the number ofthe db_blocks)
    In our casethedb_block size is8kb (8X1024 bytes). Generally this can use
   Around.25/.50 times the actual physical memory available.

2) SHARED_POOL_SIZE (Data Dictionary Cache, Shared SQL Area) - specifies memory allocated to system
    global area (SGA) for data dictionary caching and shared SQL statements
      - Each processed SQL statement: data dicttionary is searched several times.
      - Ideal Scenario: Entire data dictionary in cache
      - Entire SQL statements are stored in thiis cache as well
      - When executing a statement already in ccache oracle avoids parsing.
      - Statements must be identical i.e. (&quoot;from" in cache is different from "FROM" at the prompt)
      - Stored procedures facilitate the use off shared SQL area
      - General Rule: 50% to 75% of db_block_buffers
      - Queries exist that can be performed agaainst Oracle v$ tables to determine cache hit ratio. Specified in
      the bytes(divide by 1024X1024) to get the memory in MB.

3) SORT_AREA_SIZE - memory oracle will use per user process for sorting data.
      - memory is allocated outside SGA < onnly used when necessary >
      - Monitor temporary tablespace to determiine if an increase is necessary (Temporary Tablespace: used to create temp
      tables for large joins & order by clauses)
      - Specified in bytesThe Segment Size For temporary tablespace should be calculated as Follows.
      N*Sort_area_size+ Db_block_size.
      The SORT_AREA_SIZEParameteris modifiable in the deferred mode using the Alter system Parameter.
      Alter System Set SORT_AREA_SIZE = 4194304 DEFERRED;
      Sort area Size must be in multiples of the Db_Block size.

      Similarly the SORT_AREA_RETAINED_SIZE is system modifiable (Deferred) parameter and can be
      modified Using the
      Alter system Set Parameter.
      Generally the Sort_area_size parameter Should be equal to the
      Sort_area_size Parameter.

4) Regarding the Chained Row Analysis Using theUsing TOAD
     - Please make sure that the Delete Triggers are Disabled so that
     It Does not Update the Log table of the Registration Table .

6.During the Repairing of the Chained Rows First the Rows are deleted

7.From the Original table and Put up in a Temporary table. Which in turn
   Are inserted Backinto the OriginalDatabase Table .
   - To solve this Problemisto Recreate the Table with Increased Amount of the
      Pct Free so the the Row Chaining and Migration is Stopped.

5) Regarding the Partitioning of the Tables
  -Toallow the movements of the rows from one partition to the Other
    Partition pleaseused the Following .
    Make sure that the table is enable with the row movement Clause
    IfnotPlease give the following command for allowing the row movement
    Across the partitions.
   Alter table tablename enable row movement;
   This Clause will allow the row movement along Various partitions of the
   Tables.
   For abling the row movmentFrom one partition to Other Partition
   please give the following command
   Alter table tablenamedisablerow movement;
   Creatingthe Database Links
   If GLOBAL_NAME = TRUEin Init.Ora Filethen we have to use the Same nameas Service While Creating Links
   IF IT IS SET TO FALSETHEN WE CAN USETHE NAME OTHER THANTHE SERVICE//(DATABASE
   INSTANCE) WHILECREATING THE LINK.

   select * from all_db_links can be used to Query about the Various links which are there in a Particular
   Instance of the Database.
   create database LINKNAME
   Connect to scott identified by tiger using 'LINKNAME'

   Regarding theListener
   HereistheSampleListener.OraFilewhich is generated when one Does the settingsVia the Network AdministrationTool.

      # LISTENER.ORA Network Configuration File: D:\Oracle\Ora81\network\admin\listener.ora
      # Generated by Oracle configuration tools.
      LISTENER =
      (DESCRIPTION_LIST =
      (DESCRIPTION =
      (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
      )
      (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = satora)(PORT = 1521))
      )
      )
      (DESCRIPTION =
      (PROTOCOL_STACK =
      (PRESENTATION = GIOP)
      (SESSION = RAW)
      )
      (ADDRESS = (PROTOCOL = TCP)(HOST = satora)(PORT = 2481))
      )
      )
      SID_LIST_LISTENER =
      (SID_LIST =
      (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = D:\Oracle\Ora81)
      (PROGRAM = extproc)
      )
      (SID_DESC =
      (GLOBAL_DBNAME = lms)
      (ORACLE_HOME = D:\Oracle\Ora81)
      (SID_NAME = lms)
      )
      )
      In this Please make sure that the ORACLE_SID is running .The name assigned to the Machine (Such as
      SATORA/KENNY)isSolvedinto the I.PAddress.

      If the Oracle_SID is changed (Say previously the service was named ABC and Now it Changed to LMS) than Make
      sure that the Enviornmental Variable ORACLE_SID is Solved properly.
      On Windows NT Goto the Environmental Variables and Set the environment.

      Command For Listener Control Utility is LSNRCTL (Listener Control).

      If the Service Name is changed From ABC to LMS Please make sure that
      This Change is reflected in theLISTENER.ORA File.

      If the Listener Service starts to Fail at the Boot up timePlease Use the Command
      line toStart the service . E:\Oracle\Ora81\bin\TNSLSNR this will start the ListenerService.

Return to : Oracle Database, SQL, Application, Programming Tips