Wichtige Views
Alle V$Views auslisten:
SELECT view_name from v$fixed_view_definition
WHERE view_name LIKE 'V$%'
ORDER BY view_name;
|
|
|
|
V$FILESTAT
|
Statistics for tuning I/O?
Which view would you query to monitor I/O contention for datafiles?
|
|
V$SYSTEM_EVENT
|
Total waits for "buffer busy waits" since instance startup?
|
|
V$LOCK,
V$LOCKED_OBJECT
|
Which two views could you query to determine lock
contention on the EMP table?
|
|
V$DB_OBJECT_CACHE
|
Which view can be queried to check the amount of sharable
memory used by a cached object?
|
|
V$SYSSTAT
|
Statistics for Redo Log Buffer
|
|
V$SYSTEM_EVENT,
V$SESSION_WAIT
V$SESSION_EVENT
|
Which three views should you use to determine if a process
has waited for a resource?
|
|
$DATAFILE_HEADER
|
Which data dictionary view would you query to determine if a datafile is in
backup mode?
|
|
V$SESSION_LONGOPS
|
Which data dictionary view can you query to check the progress
of a backup?
|
|
V$BACKUP
|
To see which Files are in BEGIN BACKUP MODE
|
Oracle-8 Database Administration
analyze table dept validate structure cascade;
alter system enable restricted session;
alter tablespace TAB nologging add datafile '....' ....;
When the instance is shut down
DBA_EXTENTS
OWNER VARCHAR2(30)
SEGMENT_NAME VARCHAR2(81)
PARTITION_NAME VARCHAR2(30)
SEGMENT_TYPE VARCHAR2(17)
TABLESPACE_NAME VARCHAR2(30)
EXTENT_ID NOT NULL NUMBER
FILE_ID NOT NULL NUMBER
BLOCK_ID NOT NULL NUMBER
BYTES NUMBER
BLOCKS NOT NULL NUMBER
RELATIVE_FNO NOT NULL NUMBER
alter user scott account unlock;
alter user scott account lock;
Only with TO_CHAR and TO_DATE
V$CONTROLFILE and V$SYSTEM_PARAMETER
VARRAY
The Server places locks on the data
Value greather than or equal to INITIAL * MINEXTENTS
Unlimited quota
alter system set resource_limit=true;
raising PCTUSED
CREATE SCHEMA
- Each segment in a database is created with at least one extent.
- Frequent de-allocation of extents can lead to a fragmented tablespace.
Either before or after a COMMIT is issued
Minimal information is required to record the changes in the redo logs, due LRU is
deferred.
Writes to the datafiles are deferred to reduce disk contention and I/O. Oracle's Fast
COMMIT allows LGWR to record only the changes and SCN in the redo log files. The size
of the transaction does not affect the time required to write to the redo log files.
These writes are sequential and faster than writing entire blocks to the datafiles.
Writes to the datafiles occur independently of the COMMIT.
SHOW SGA
SHOW PARAMETER
ALTER SYSTEM SET SORT_AREA_SIZE=163840 DEFERRED;
What affect would this command have on the SORT_AREA_SIZE initialization parameter?
It will be set to 163840 for all future sessions.
TRANSACTIONAL
To use operating system authentication
To allow users to connect to the database using O/S authentication, set up the users
in the operating system and set the REMOTE_LOGIN_PASSWORDFILE initialization
parameter to NONE. To connect to the database using the SYSDBA role, log in to the
operating system and connect to the database using this connect string: CONNECT / AS
SYSDBA
Database configurations can be stored in the OEM registry and not as external files.
NORMAL
To detect block corruption errors
V$PARAMETER, V$CONTROLFILE, SHOW PARAMETER
V$SESSION, V$INSTANCE, V$PARAMETER
When an instance is started in NOMOUNT state, you can only access the views that read
data from the SGA. V$PARAMETER, V$SESSION, and V$INSTANCE read from memory;
V$DATABASE, V$DATAFILE and V$LOGFILE all read data from the control file. The
database must be mounted for the control file to be opened. The data dictionary is
only accessible when the database is open.
SHUTDOWN TRANSACTIONAL;
An instance would start, but the database would not be mounted.
When a database is created, the database name is specified in the CREATE DATABASE
command and it must match the DB_NAME parameter. The database will not mount if the
database name recorded in the control file does not match the database name in the
STARTUP command.
CREATE DATABASE prod
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXLOGHISTORY 100
LOGFILE
GROUP 1 '/disk3/log1a.rdo, /disk4/log1b.rdo',
GROUP 2 '/disk3/log2a.rdo, /disk4/log2b.rdo' REUSE
DATAFILE '/disk2/system01.dbf' SIZE 50M;
Which line will return an error under any condition?
==> GROUP 1 '/disk3/log1a.rdo, /disk4/log1b.rdo',
For any datafiles or redo log files that already exist, use the REUSE option in the
CREATE DATABASE command. If the file does not exist, you must use the SIZE option to
specify the size of the file to be created either in kilobytes (K) or megabytes (M).
Directly below the Oracle directory
Separate objects by lifespans to minimize fragmentation.
Shut down the instance, delete any files created by the CREATE DATABASE command, and
change the SIZE options to REUSE in the LOGFILE parameter of the CREATE DATABASE
command.
To reallocate the space needed for the parameter
CATPROC.SQL
The data dictionary views categories:
|
ALL_
|
Accessible to all users and each view has an OWNER column, providing information
on objects accessible by the user
|
|
DBA_
|
Accessible to the DBA or anyone with the SELECT ANY TABLE privilege, providing
information on all the objects in the database and has an OWNER column
|
|
USER_
|
Accessible to all users, providing information on objects owned by the user
|
The DICTIONARY and DICT_COLUMNS data dictionary
views provide an overview for all the data dictionary views.
dbms*.sql defines the database package specifications
cat*.sql provides catalog and data dictionary information
prvt*.plb provides wrapped package code
utl*sql creates views and tables for database utilities.
If errors occur during compilation of objects, the STATUS column in
DBA_OBJECTS is set to INVALID. Compiled objects that are available for use are
marked VALID. In this example, the view would be marked as INVALID.
SQL> select type,records_total from V$CONTROLFILE_RECORD_SECTION;
TYPE
RECORDS_TOTAL
----------------- -------------
DATABASE
1
CKPT
PROGRESS
4
REDO
THREAD
4
REDO
LOG
62
DATAFILE
256
FILENAME
567
TABLESPACE
256
RESERVED1
256
RESERVED2
1
LOG
HISTORY
1696
OFFLINE
RANGE
290
ARCHIVED
LOG
1603
BACKUP
SET
305
BACKUP
PIECE
514
BACKUP
DATAFILE
526
BACKUP
REDOLOG
160
DATAFILE
COPY
512
BACKUP CORRUPTION
277
COPY
CORRUPTION
305
DELETED
OBJECT
1628
PROXY
COPY
640
8
Reinitialize the log files in Group 2 with the ALTER DATABASE CLEAR
LOGFILE command.
You can clear a group if all its members become corrupt by issuing the ALTER
DATABASE CLEAR LOGFILE command. This command can be used even if there are only
two redo log groups. If you need to clear a redo log group that has not yet
been archived, you must include the UNARCHIVED keyword in the command.
After LGWR writes 25 operating system blocks
-
You are tuning the PROD database and need to determine the sequence number of
the current redo log group. Which view would you query to display the
number of online redo log groups?
select SEQUENCE# from v$thread;
It is in use
V$LOGFILE displays each group, each member, and the status of each member. The values
of the STATUS column can be: INVALID - inaccessible, STALE - incomplete, DELETED - no
longer being used, NULL - in use
Complete recovery is possible even after the redo logs have been overwritten
using
the archived redolog files
select LOG_MODE from v$database;
The group is no longer needed for instance recovery.
After the group is archived, issue the ALTER SYSTEM SWITCH LOGFILE
command then the ALTER DATABASE DROP LOGFILE MEMBER command.
To drop a redo log member, use the ALTER DATABASE DROP LOGFILE MEMBER command. If
the database is in ARCHIVELOG mode, you cannot drop an online member until it
has been archived. If the group is active, you must force a log switch
before dropping one of its members. You cannot drop the last valid member of a group.
Dropping a member does not remove its O/S file.
The syntax to add a redo log group:
ALTER DATABASE prod
ADD LOGFILE GROUP 2 ('/disk2/log2a.rdo',
'/disk3/log3b.rdo') SIZE 1M;
If you specify the GROUP option, you must use an integer value. If you do not
use the GROUP option, the Oracle Server will automatically generate a value.
Use the SIZE option if you are creating a new file. Use the REUSE option if the file
already exists.
Reset internal file pointers in the control file.
50 (This is very bad, set it to 0)
Use the ALTER TABLESPACE command to change the MINIMUM EXTENTS value or the DEFAULT
STORAGE parameters for a tablespace.
Use the RESIZE option with the ALTER DATABASE DATAFILE command.
Issue the ALTER TABLESPACE ONLINE command then the ALTER TABLESPACE READ ONLY
command.
When you make a tablespace read-only, all the datafiles must be
online and the tablespace can have no pending transactions or active
rollback segments. To make a read-write tablespace read-only, use this command: ALTER
TABLESPACE tablespace_name READ ONLY; To make a read-only tablespace read-write, use
this command: ALTER TABLESPACE tablespace_name READ WRITE;
CREATE TABLESPACE user_data
DATAFILE '/disk2/us_data01.dbf' SIZE 10M
MINIMUM EXTENT 500K
DEFAULT STORAGE
(INITIAL 100K NEXT 100K MAXEXTENTS 500 PCTINCREASE 0);
Why will this statement fail?
INITIAL and NEXT should be a multiple of MINIMUM EXTENT.
When MINIMUM EXTENT is specified, every extent allocated in the tablespace should be
a multiple of MINIMUM EXTENT. In this case, NEXT and INITIAL should be multiples of
500K.
The default value of the NEXT parameter is 5 data blocks. In this
case, the size of the second extent allocated can be determined by: 5 * DB_BLOCK_SIZE
= 10K
When the tablespace is the SYSTEM tablespace
A syntax error would be returned.
The minimum INITIAL value allowed is: 2 * DB_BLOCK_SIZE
Datafile and OS-Blocks
The physical structure of a database includes O/S blocks, datafiles,
control files, and online redo log files.
ALTER TABLESPACE MINIMUM EXTENT
Use the ALTER TABLESPACE command to change the MINIMUM EXTENT value or the DEFAULT
STORAGE parameters for a tablespace.
ALTER TABLESPACE data01 COALESCE;
Which background process performs the same task?
Coalescing merges neighboring free extents. This is automatically done by the
SMON background process if the PCTINCREASE value for the tablespace
is greater than zero. You can manually coalesce a tablespace by using this command:
ALTER TABLESPACE tablespace_name COALESCE;
Query DBA_FREE_SPACE_COALESCED to determine if a tablespace needs to be coalesced.
A block is removed from the free list when it falls below the
PCTFREE threshold. It is returned to the free list when the used space falls below
PCTUSED.
An index can be partitioned to minimize disk contention while
decreasing the amount of time required for queries.
ALTER ROLLBACK SEGMENT rbs01 SHRINK TO 4M;
What would be the result if the current size of RBS01 is 3M?
The command is ignored.
The ALTER ROLLBACK SEGMENT command with the SHRINK option will shrink a rollback
segment. If no value is specified, the Oracle Server will attempt to shrink to the
OPTIMAL value. To shrink a rollback segment to a specific size, use this command:
ALTER ROLLBACK SEGMENT SHRINK TO integer K/M;
If the SHRINK TO size or OPTIMAL is greater than the current size of the rollback
segment, the command is ignored.
ALTER ROLLBACK SEGMENT rbs01 OFFLINE;
What will happen if RBS01 has pending transactions when the command is issued?
The segment is taken offline when all transactions are complete.
A rollback segment cannot be taken offline if it has pending transactions. If you use
the ALTER ROLLBACK SEGMENT command to take it offline before the pending transactions
end, the rollback segment will be marked as 'PENDING OFFLINE'. The rollback segment
will not be taken offline until the transactions complete.
Rollback segments store the before image when changes are made to
data in the database. A transaction can only be assigned to one rollback segment,
however a rollback segment can store images for multiple concurrent transactions.
The SYSTEM rollback segments record changes made to objects in the SYSTEM
tablespace.
Private rollback segments are acquired by an instance when they are named in the
parameter file.
Public rollback segments are in the pool of rollback segments.
Deferred rollback segments are created by the Oracle Server when a
user attempts to commit a transaction that will change data in a tablespace
that is offline. The Oracle Server will drop these segments when they are no
longer needed.
Use larger extent sizes.
Create rollback segments with higher OPTIMAL values.
Create rollback segments with high MINEXTENTS values
A snapshot too old error occurs when the Oracle Server cannot create a read
consistent image of the data when a query is executed. The error is returned when a
query is issued on a row with a lock, but before the query is finished the
transaction commits and the rollback segment is overwritten. Reduce the chances of
snapshot too old errors by creating rollback segments with high MINEXTENTS values,
larger extent sizes, and high OPTIMAL values.
20
MINEXTENTS for a rollback segment should be greater than or equal to two.
Oracle recommends a value of 20 to reduce the likelihood of
extension. OPTIMAL should be greater than or equal to MINEXTENTS.
PCTINCREASE cannot be specified and should be set to zero. INITIAL should always be
equal to NEXT. All the extents allocated to a rollback segment should be the same
size. Place rollback segments in tablespaces that do not contain data to minimize I/O
contention and fragment.
Before you can take a tablespace with active rollback segments offline:
Query DBA_ROLLBACK_SEGS to determine which rollback segments are in
the tablespace and take the rollback segments offline.
Query V$TRANSACTIONS to determine the transaction currently using
the rollback segments.
Query V$SESSION to determine the user with the pending transaction
and terminate their session.
DB_BLOCK_SIZE+(multiple of SORT_AREA_SIZE)
Temporary segments are based on the default storage parameters of the tablespace. For
the TEMPORARY tablespace, INITIAL and NEXT should be equal to each other and a
multiple of SORT_AREA_SIZE plus DB_BLOCK_SIZE. PCTINCREASE should always be equal to
zero.
Several transactions can simultaneously share the same sort segment.
To determine the number of extents allocated to a TEMPORARY tablespace containing a
sort segment
V$SESSION
V$SORT_USAGE
Join V$SESSION and V$SORT_USAGE to display the username, session address, tablespace,
contents, extents, and blocks for active sorts.
V$SORT_SEGMENT
KEEP
To deallocate all unused space in a table above the high water mark, use this
command:
ALTER TABLE table DEALLOCATE UNUSED;
To deallocate unused space while keeping a specified number of bytes above the high
water mark, use this command:
ALTER TABLE table DEALLOCATE UNUSED KEEP integer K/M;
If the size specified in the KEEP option is below MINEXTENTS, MINEXTENTS will be
kept.
TRUNCATE TABLE <schema.table_name> DROP STORAGE;
The ROWID_RELATIVE_FNO function in the DBMS_ROWID package returns
the relative file number of the file that stores the row. The
ROWID_BLOCK_NUMBER function in the DBMS_ROWID package returns the
block number stored in the row in the relative file. The
ROWID_ROW_NUMBER function in the DBMS_ROWID package returns the
sequence number of a given ROWID value.
Increase PCTFREE
If PCTFREE is set too low, there may not be enough room in the data blocks to update
existing rows. When there is insufficient space in the data block, the row is moved
to a new data block and the old block stores a pointer to the new location. Row
migration slows table scans because two blocks are scanned for one row.
3 ( 1 for columns length <= 250, 3 for columns length > 250 )
When row data is stored in a data block, the column length is recorded as either 1
byte for column lengths 250 bytes or less or 3 bytes for column lengths greater than
250 bytes.
REF
The REF datatype is a relationship type that stores a pointer to the data in another
table, rather than storing the actual data.
ANALYZE TABLE orders ESTIMATE STATISTICS;
How many rows will be sampled if the ORDERS table contains 5000 rows?
1064
To detect row migration or chaining in a table, use the ANALYZE TABLE command. The
COMPUTE STATISTICS option will return exact statistics because it analyzes all of the
rows in the table. The ESTIMATE STATISTICS option will compute statistics
based on 1064 rows. You can use the ESTIMATE STATISTICS option with a sample
integer or percent to specify the number or percentage of rows that are scanned.
70 Blocks (INITRANS Index > INITRANS Table)
The high water mark in a table is the last block ever used by the table. The
high water mark is not reset when data is deleted. When a full table scan is
performed, each block is read up to its high water mark.
Higher
Because index entries are small, there are usually more entries per block so the
INITRANS value should be higher than the table.
The TRUNCATE command deletes all the rows in the table and releases all the unused
space. The corresponding indexes will also be truncated. If the
REUSE option is used with the TRUNCATE command, the data is deleted from the table
and its corresponding indexes, but no space is deallocated. If the DROP option is
used with the TRUNCATE command, the extents specified by MINEXTENTS are retained. A
table cannot be truncated that is referenced by a foreign key.
Partitioned indexes allow an index to be spread across multiple tablespaces.
DBA_INDEXES
To perform a parallel direct-load insert, use the PARALLEL hint in the INSERT INTO
SELECT command. The parallel direct-load insert uses parallel query slaves to insert
the data. The data is written to temporary segments until the transaction
commits.
The order of import:
1. table definitions
2. table data
3. indexes on the table
4. integrity constraints, triggers, and bitmap indexes
The LOAD parameter in the SQL*Loader command specifies the number of
records to load. This value does not include any records that are skipped.
Other rows in the same table can be concurrently modified by other users.
Data can be quickly copied from one table into another within the same database
because it
bypasses the buffer cache.
Use the INSERT INTO SELECT command to perform a direct-load insert. This command can
be used to copy data from one table to another table in the same database. The buffer
cache is bypassed to speed up the insert. During a direct-load insert, users
can be concurrently modifying existing data in the table. Data is loaded above the
high water mark.
The QUOTA clause defines the amount of space in bytes that a user can use to
create and maintain objects in a specified tablespace. The default value is
no space and the maximum value is UNLIMITED.
ALTER USER bill QUOTA 0 ON data01;
If a 20M table exists in the DATA01 tablespace owned by user BILL, which effect
will this command have ?
The objects created by user BILL in the DATA01 tablespace will remain, but no new
extents can be allocated to the objects.
USER_TS_QUOTAS
USER_TS_QUOTAS displays the names of tablespaces that the user has a
QUOTA on, the number of bytes and blocks used, and the maximum number of bytes and
blocks allowed.
When assigning a COMPOSITE_LIMIT to a profile, Oracle calculates a weighted sum for
these resource limits:
PRIVATE_SGA
CPU_PER_SESSION
CONNECT_TIME
LOGICAL_READS_PER_SESSION
To specify the weights assigned to each limit, use the ALTER RESOURCE COST command.
ALTER PROFILE juan LIMIT
CPU_PER_SESSION 3600 (1/100 Sec)
IDLE_TIME 30; (Min)
How much time will be allowed in the next session for user JUAN?
36 seconds of cpu time and 30 minutes of inactivity
CPU_PER_SESSION is expressed in hundredths of seconds and IDLE_TIME is expressed in
minutes
After the VERIFY_FUNCTION is enabled and the DEFAULT profile is altered, a user must
change their password using these guidelines:
1. The new password must differ from the old password by more than three
characters.
2. The password must have at least 4 characters.
3. The password cannot be equal to the username.
4. The password must have at least one alpha, one numeric, and one special
character.
To disable resource limits, set the RESOURCE_LIMIT parameter to
FALSE. To enable the enforcement of resource limits for the current
instance, use the ALTER SYSTEM command to set the RESOURCE_LIMIT initialization
parameter to TRUE. To enable or disable the enforcement of resource limits for future
sessions, alter the RESOURCE_LIMIT initialization parameter in the parameter file.
DBA_USERS
When the PASSWORD_MAX_REUSE parameter is set to UNLIMITED, a user can never reuse the
same password.
Object privileges are granted WITH GRANT OPTION. When object
privileges are revoked, the revoke cascades.
-
Which system privilege do you need to grant a system privilege to another
user?
System privileges are granted WITH ADMIN OPTION. When system
privileges are revoked, the revoke does not cascade.
-
Which privilege is required to TRUNCATE a table owned by another user?
DROP ANY TABLE
To define a default role for a user, you must grant the role to the user with the
GRANT command. By default, all the roles granted to the user will be enabled when the
user connects unless the ALTER USER DEFAULT ROLE command is used to
limit the default roles.
ALTER USER jennifer
DEFAULT ROLE ALL;
Which task would this accomplish?
Set all roles as default for user JENNIFER
The NLS_SORT parameter can be used with the NLS_UPPER, NLS_LOWER, NLS_INITCAP, and
NLSSORT functions.
-
NLS Views
NLS_SESSION_PARAMETERS: This View lists NLS parameters of the user session.
NLS_DATABASE_PARAMETERS: This View lists permanent NLS parameters of the
database
NLS_INSTANCE_PARAMETERS: This View lists NLS parameters of the instance.
SQL> select * from nls_session_parameters;
PARAMETER
VALUE
------------------------------ ------------------------------
NLS_LANGUAGE
AMERICAN
NLS_TERRITORY
AMERICA
NLS_CURRENCY
$
NLS_ISO_CURRENCY
AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR
GREGORIAN
NLS_DATE_FORMAT
DD-MON-YY
NLS_DATE_LANGUAGE
AMERICAN
NLS_SORT
BINARY
NLS_TIME_FORMAT
HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT
DD-MON-YY HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT
HH.MI.SSXFF AM TZH:TZM
NLS_TIMESTAMP_TZ_FORMAT DD-MON-YY
HH.MI.SSXFF AM TZH:T
NLS_DUAL_CURRENCY
$
NLS_COMP
SQL> select * from nls_database_parameters;
PARAMETER
VALUE
------------------------------ ------------------------------
NLS_LANGUAGE
AMERICAN
NLS_TERRITORY
AMERICA
NLS_CURRENCY
$
NLS_ISO_CURRENCY
AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET
WE8ISO8859P1
NLS_CALENDAR
GREGORIAN
NLS_DATE_FORMAT
DD-MON-YY
NLS_DATE_LANGUAGE
AMERICAN
NLS_SORT
BINARY
NLS_TIME_FORMAT
HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT
DD-MON-YY HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT
HH.MI.SSXFF AM TZH:TZM
NLS_TIMESTAMP_TZ_FORMAT DD-MON-YY
HH.MI.SSXFF AM TZH:T
NLS_DUAL_CURRENCY
$
NLS_COMP
NLS_NCHAR_CHARACTERSET
WE8ISO8859P1
NLS_RDBMS_VERSION
8.1.5.0.0
Oracle-8 Database Tuning
SYS Option
Statement Level
Limits the number of blocks cached for each table.
- The Shared Pool is too small
- Data Segments are being written to the SYSTEM tablespace
- Sort Segments are being written to the SYSTEM tablespace
Transaction activity of the database during normal processing.
TRUNCATE
number
of blocks that contain rows
100 * -----------------------------------------------------
number of blocks below the high-water mark
For non-empty tables, which value should be your target?
100
- The database is using Parallel Query Option
- The application is performing many full table scans.
desc v$db_object_cache;
Name
Null? Type
------------------------------- -------- ----
OWNER
VARCHAR2(64)
NAME
VARCHAR2(1000)
DB_LINK
VARCHAR2(64)
NAMESPACE
VARCHAR2(15)
TYPE
VARCHAR2(24)
SHARABLE_MEM
NUMBER
LOADS
NUMBER
EXECUTIONS
NUMBER
LOCKS
NUMBER
PINS
NUMBER
KEPT
VARCHAR2(3)
-
Your database is installed on a multi-CPU system. If the Utlbstat/Utlestat utility
shows contention for the redo copy latch, which action should you take?
Increase the value of the LOG_SIMULTANEOUS_COPIES parameter. This Parameter
specifies the number of redo buffer copy latches. For Single CPU Machines set it to
0, for Multi CPU Machines set it to 2 x Number of CPUs.
-
Your system has limited physical memory. You have an application that uses many
large sorts. Which parameter can be set to improve the performance of these large
sorts?
SORT_DIRECT_WRITES = True/False/Auto (Bypass Buffer Cache)
Set it to True if there is abundant memory. This parameter controls whether
sort data will bypass the buffer cache to write intermediate sort to
disk.
0
ALTER INDEX REBUILD ...
Alert Log File
User Process Trace File
Query the V$RECENT_BUCKET view
Set the Parameter DB_BLOCK_LRU_STATISTICS to TRUE
V$LOCK (Lock currently held by the Oracle Server)
V$LOCKED_OBJECT (Lists all locks acuired by every transaction on the system)
MAXEXTENTS has been set too low
Tablespace for Rollback Segments too small
Limits the number of blocks cached for each table
8 (Good Performance set it to 2 x Number of CPU's)
SORT_AREA_SIZE=<SIZE> (Increasing the size improves the efficiency of large
sorts)
SORT_DIRECT_SORTS=TRUE (Can improve sort performance for abundant memory)
V$SYSTEM_EVENT (Total Waits on en event since Instance startup)
Modify the DB_BLOCK_LRU_LATCHES parameter
V$FILESTAT (I/O Statistics, V$LOADSTAT ist for SQL*Loader)
Redo Log Buffer, select CLASS = 2
Buffered Data
Hash Join
Recreate the table increasing the number of freelists.
Increase the value of the LOG_SIMULTANEOUS_COPIES parameter.
The MAX_DUMP_FILE_SIZE parameter value is specified in O/S blocks and specifically
limits the size of user trace files.
The V$EVENT_NAME view lists all event names and their associated
parameters.
SQL> select event#, substr(name,1,50) from v$event_name
where rownum < 10;
EVENT# SUBSTR(NAME,1,50)
--------- ---------------------------------------------
0 Null event
1 latch activity
2 latch free
3 free process state object
4 pmon timer
5 inactive session
V$SESSION_EVENTS
SELECT event, total_waits, total_timeouts,
time_waited, average_wait
FROM v$system_event;
Buffer Busy Waits
A user has complained about the time it is taking to execute an application process.
Active session's last wait time
V$SYSTEM_EVENT
A latch hit ratio of 99% is optimal. Latch contention should be
always be 1% or less. To resolve redo latch contention, increase
LOG_SIMULTANEOUS_COPIES and decrease LOG_SMALL_ENTRY_MAX_SIZE.
HIT_RATIO
The V$SYSSTAT view provides basic instance statistics cumulated
since instance start up.
-
Which view could be queried to determine the type of user connection?
SELECT sid, username, type, server
FROM v$session;
SELECT sid, username, type, server
FROM v$session;
SID USERNAME
TYPE SERVER
--------- ------------------------------ ---------- ---------
1
BACKGROUND DEDICATED
-
You have a hybrid system processing reports at night. What can you do to handle
heavy transaction processing during the day and report processing at night?
Use many small rollback segments during the day and fewer large rollback segments at
night.
The segments not being used for reporting should remain offline until they are
needed.
Reset the parameters for day and night using a separate pfile.
SQL Trace can be set at instance or session level and provides
detailed information regarding the execution of a SQL statement.
When DISTINCT is used, the data is sorted. If the statement selects
a large number of rows, the server may generate temporary segments to handle the sort
runs. This process incurs I/O and processing overhead, and should be
avoided.
Hash Join
Information about entries for attributes in the fact table. Lookup
tables are small tables joined to the fact table using a PK-FK join.
Performance and Resource Usage
DBMS_APPLICATION_INFO contains procedures needed to register and then track a module.
When an application calls these procedures, they record and read information into and
from the V$SESSION and V$SQLAREA views.
Statements including group functions impact performance. Use a WHERE clause to
exclude unwanted rows. HAVING should only be used with an aggregate condition.
Inappropriate use of the HAVING clause increases response time.
READ_MODULE reads the last module and action names set by SET_ACTION
or SET_MODULE. READ_CLIENT_INFO reads the last client information
set for the session.
DB_BLOCK_CHECKSUM
LOG_BLOCK_CHECKSUM
PRE_PAGE_SGA
Instance start up time is increased using this parameter, but it can reduce the
number of page faults that occur. The reduction in page faults allows the instance to
reach its maximum performance capability more quickly
The number of object definitions that have been aged out of the
library cache for lack of space.
The number of executions of SQL statements or procedures in the
library cache.
V$SESSTAT
V$STATNAME
SELECT SUM(value) || 'bytes' "Total session memory"
FROM v$sesstat, v$statname
WHERE name = 'session uga memory'
AND v$sesstat.statistic# = v$statname.statistic#;
Pinning sequences in the shared pool will prevent the numbers from
being lost, which is normal when they are aged out.
SELECT SUM(sharable_mem)
FROM v$db_object_cache
WHERE type = 'PACKAGE' OR type = 'PACKAGE BODY' OR
type = 'FUNCTION' OR type = 'PROCEDURE';
Oracle recommends allowing about 250 bytes in the shared pool per
user per open cursor. This can be tested during peak times using this query:
SELECT SUM(250 * users_opening)
FROM v$sqlarea;
SHARED_POOL_SIZE
DB_BLOCK_BUFFERS
Determine the size of each buffer pool by subtracting the number of buffers for each
buffer pool from the total number of buffers defined for the entire buffer cache. If
this constraint is violated, the database will not mount.
The buffer cache holds copies of the data blocks from the datafiles. The
DB_BLOCK_BUFFERS parameter specifies the number of blocks in the buffer
cache.
Cache Hint Clause: Using this method causes the Oracle server to
place the table blocks at the most recently used end of the LRU list.
V$SYSSTAT
V$RECENT_BUCKET
Number of additional cache hits gained by adding additional buffer cache blocks
Total number of buffer cache blocks used by each object. The RECYCLE buffer pool
should be large enough to be retained for the duration of the transaction.
Retain as many objects in memory as you can.
When the server process is searching the LRU list, DBWR managers the buffer cache by
writing dirty blocks to the datafiles to ensure that there are free blocks for
servers.
- When Dirty List exceeds threshold
- Independent of COMMIT
- No more free Blocks in LRU-List
when the dirty list exceeds its size threshold
V$CACHE is created with the catparr.sql script. It can be viewed to
monitor the number of buffer pool blocks by object and is intended for use with
Oracle Parallel Server.
Objects currently being cached
- Current block
- Read consistent block for rollback
The buffer cache may hold multiple copies of a single database block. Only one
current copy exists, but server processes may need to reconstruct
read-consistent copies using rollback information.
50 times the number of LRU latches
Number of buffers skipped to find a free buffer.
"Checkpoint not complete; unable to allocate file"
What does this indicate?
LGWR has waited for DBWR to finish.
The redo allocation latch controls the allocation of space for redo
entries in the redo log buffer. There is only one redo allocation latch.
When the process copies the entry into the buffer, there is only one
redo allocation latch per instance. The number of CPU's only has an effect on redo
copy latches.
The LOG_BUFFER parameter needs to be increased.
A checkpoint occurs.
One member of the log group will be archived.
Stored objects such as database triggers and packages are part of the data dictionary
and should be stored in the SYSTEM tablespace.
To allocate extents explicitly, use this command:
ALTER TABLE tablename
ALLOCATE EXTENT (DATAFILE 'filename' SIZE sizeM);
If you do not explicitly set the temporary tablespace during user creation, sorts
occur in the SYSTEM tablespace.
DISK_ASYNCH_IO
TAPE_ASYNCH_IO
Indexes on volatile tables can present a performance problem and should be monitored
and rebuilt frequently.
Having enough contiguous storage space
DELETE * FROM parts;
What will be the state of the high-water mark for the PARTS table after this
statement has completed?
not changed, DELETE statements have no effect on the high water mark. The
TRUNCATE statement must be issued to reset the high water mark.
ANALYZE INDEX index_name VALIDATE STRUCTURE;
PCTFREE
Migration is caused by PCTFREE being set too low; there is not enough room in
the block for updates. All tables that are updated should have
PCTFREE set to allow enough space within the block for updates.
multiple of 5 * DB_FILE_MULTIBLOCK_READ_COUNT
Multiply by five, because Oracle tries to allocate blocks on five block boundaries.
If you delete all entries for an index block, Oracle puts the block back on
the free list.
| 
