Set the Buffer_Pool_Keep Size
Accordingly in the init.ora File
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.
Have a Oracle Question
Do
you have an Oracle Question?
Oracle Books
Oracle
Certification, Database Administration, SQL, Application, Programming Reference
Books
Oracle Application
Oracle
Application Hints and Tips
Oracle Home
Oracle
Database, SQL, Application, Programming Tips
All the site contents are Copyright © www.erpgreat.com
and the content authors. All rights reserved.
All product names are trademarks of their respective
companies.
The site www.erpgreat.com is not affiliated with or endorsed
by any company listed at this site.
Every effort is made to ensure the content integrity.
Information used on this site is at your own risk.
The content on this site may not be reproduced
or redistributed without the express written permission of
www.erpgreat.com or the content authors.
|