Transcript Jerry Held

Steve George
Sr. Delivery Manager
Oracle University
Oracle Corporation
Tips, tricks and
Techniques for the Oracle
Database
Overview
 This course concentrates on Oracle9i Release
2 new features applicable to database
administration
 Previous experience with Oracle databases is
required for a full understanding of many new
features, particularly Oracle8 and Oracle8i
Displaying Execution Plans
SQL*Plus Autotrace
• Create the plan_table table.
• Create and grant the plustrace role.
SQL> @$ORACLE_HOME/sqlplus/admin/plustrce.sql
•SQL> GRANT plustrace TO scott;
•
Autotrace syntax:
SET AUTOTRACE [ Off | On | Traceonly ]
[ Explain | Statistics ]
Generate the Execution Plan
•
•
Can be used without tracing
Needs the plan_table table utlxplan.sql
•
Create the explain plan:
SQL> EXPLAIN PLAN FOR
2 SELECT last_name FROM hr.employees;
Query the plan_table Table
Using dbms_xplan.display
Query plan_table to display the execution plans:
Query plan_table directly.
Use script utlxpls.sql (hide Parallel Query
information).
Use script utlxplp.sql (show Parallel Query
information).
Use the dbms_xplan package.
SQL> SELECT *
2 FROM TABLE(dbms_xplan.display);
Metadata API in Oracle9i
 A new package, DBMS_METADATA, is introduced.
 Using this package, you can extract metadata in:
–
–
Browsing mode, or
Programmatic mode where the program can specify:
 The types of objects to be retrieved
 Various selection criteria
 Transformation of the output by default is XML, but
can be of any format (uses XSL).
Metadata API in Oracle9i
Browsing Example
 The query can have a WHERE clause.
 Output is in SQL, but can be in XML format.
 Combine with a SPOOL statement for XML file
 Any combination of objects can be extracted,
depending on the result of the SELECT statement.
 Running a spool while extracting provides a file
that can be edited immediately.
SQL> SELECT dbms_metadata.get_ddl
('TABLE','SALES')
2 FROM
dual;
Default Partition for List
Partitioning
 Create a DEFAULT partition for all values not
covered by other list partitions:
CREATE TABLE customer
...
PARTITION BY LIST (state)
(PARTITION p1 VALUES ('CA','CO'),
PARTITION p2 VALUES ('FL','TX'),
PARTITION p3 VALUES ( DEFAULT )
);
Range List Composite
Partitioning
CREATE TABLE customer ...
PARTITION BY RANGE (month)
SUBPARTITION BY LIST (state) ...;
Range (month)
List
(state)
<3
<6
<9
< 12
'CA', 'CO'
p1_s1
p2_s1
p3_s1
p4_s1
'FL', 'TX'
p1_s2
p2_s2
p3_s2
p4_s2
Default
p1_s3
p2_s3
p3_s3
p4_s3
Composite Partitioning Using
a Template
Use a subpartition template for:
–
–
Range list partitions
Range hash partitions
CREATE TABLE state_sales ...
PARTITION BY RANGE (month)
SUBPARTITION BY LIST (state)
SUBPARTITION TEMPLATE
(SUBPARTITION s1 VALUES ('CA,''CO')
SUBPARTITION s2 VALUES ('FL','TX')
)
...
Rename Columns and
Constraints
SQL> ALTER TABLE employees
2 RENAME COLUMN salary TO sal;
SQL> ALTER TABLE employees
2 RENAME CONSTRAINT sys_c002691
3 TO pk_employees_id;
RMAN: Control Archived Log
Space Usage
 Avoid errors caused by insufficient disk space for
restored archived logs during recovery
 Define size available for storage of archived logs
copied to disk from backup sets
 Use MAXSIZE option in conjunction with DELETE
option
RMAN> RECOVER DATABASE
2> DELETE
3> ARCHIVELOG MAXSIZE 100K;
Backing Up the Server
Parameter File
–
–
Automatically backed up when CONFIGURE
CONTROLFILE AUTOBACK = ON
Explicitly backed up with BACKUP SPFILE
RMAN> BACKAUP COPIES 2 DEVICE TYPE sbt SPFILE;
RMAN> BACKUP SPFILE;
Persistent Configuration
Parameters
 Customizable configuration parameters simplify
RMAN operations.
 Default settings are set once and used for
subsequent jobs.
 A DBA can invoke RMAN and back up a database
with one command: BACKUP DATABASE.
 Oracle9i provides the new CONFIGURE command
to override default settings persistently.
 The configuration values are stored in the control
file and are resynchronized to the recovery catalog
as necessary.
Automatic Channel Allocation
 This feature applies to BACKUP, COPY, and
RESTORE commands.
 A channel is automatically allocated if one is not
explicitly specified in the RMAN command.
 Default values are specified with the CONFIGURE
command.
 The benefit of this feature is a usability
improvement due to the simplification of
subsequent commands.
CONFIGURE CHANNEL
Command
 Used to configure settings that will be used for
the channels (all or individual):
CONFIGURE CHANNEL [n] <channel_option_list>;
channel_option_list := TYPE, NAME, PARMS,
CONNECT STRING, DEBUG,
FORMAT, TRACE,
MAXPIECESIZE, RATE,
MAXOPENFILES, SEND
Enterprise Manager Backup
Wizard
Backup Wizard: Deletion of
Archived Logs
Backup Wizard: Override
RMAN Configuration
DBNEWID Utility
 The DBID of a database
 The DBNAME of a database
 Both the DBNAME and DBID of a database
d:\>nid
DBNEWID: Release 9.2.0.1.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All
rights reserved.
Keyword
Description
(Default)
---------------------------------------------------TARGET
Username/Password
(NONE)
DBNAME
New database name
(NONE)
LOGFILE
Output Log
(NONE)
REVERT
Revert failed change
NO
SETNAME
Set a new database name only
NO
APPEND
Append to output log
NO
HELP
Displays these messages
NO
Shared Pool Advisory
SQL> SELECT shared_pool_size_for_estimate AS pool_size,
2
estd_lc_size, estd_lc_time_saved
3 FROM
v$shared_pool_advice;
POOL_SIZE ESTD_LC_SIZE ESTD_LC_TIME_SAVED
--------- ------------ -----------------32
8
7868
40
15
7868
48
17
7868
56
17
7868
64
17
7868
72
17
7868
80
17
7868
88
17
7868
96
17
7868
Enterprise Manager
Shared Pool Size Advisor
Mean Time To Recover (MTTR)
Advisory
 Collect statistics by setting initialization parameters
–
–
FAST_START_MTTR_TARGET to non-zero value
STATISTICS_LEVEL to TYPICAL or ALL
 View estimated overhead for different settings of
FAST_START_MTTR_TARGET
–
–
In the rows of the view
v$mttr_target_advice
Through the Enterprise Manager graphical
interface to this view
View MTTR Advisory with
Enterprise Manager
V$PGA_TARGET_ADVICE
TARGET_MB CACHE_HIT_PERCENT ESTD_OVERALLOC_COUNT
---------- ----------------- -------------------63
23
367
125
24
30
250
30
3
375
39
1
500
58
0
600
59
0
700
59
0
800
60
0
900
60
0
1000
61
0
1500
67
0
2000
76
0
3000
83
0
4000
85
0
PGA Sizing Advice
Conclusions
LOW_KB HIGH_KB OPTIMAL ONEPASS
MPASS
------ ------- ------- ------- ------8
16 156107
0
0
16
32
148
0
0
32
64
89
0
0
64
128
13
0
0
128
256
58
0
0
256
512
10
0
0
512
1024
653
0
0
1024
2048
530
0
0
2048
4096
509
0
0
4096
8192
227
0
0
8192
16384
176
0
0
16384
32768
133
14
0
32768
65536
66
103
0
65536 131072
15
47
0
131072 262144
0
48
0
262144 524288
0
23
0
PGA Sizing Advisor Output in
Enterprise Manager
Data Compression
CREATE TABLE employees(
employee_id INTEGER,
first_name VACHAR2(20), … ) COMPRESS;
CREATE TABLESPACE sample
DATAFILE 'sample01.dbf' SIZE 20M
DEFAULT COMPRESS;
CREATE TABLE countries(
country_id
CHAR(2),
country_name VARCHAR2(40),
region_id
NUMBER ) NOCOMPRESS
TABLESPACE sample;
Data Compression
Optimization:
Enabling Row Movement
(both
partitioned and nonpartitioned)
CREATE TABLE employees
(employee_id NUMBER,
first_name VARCHAR2(20),
last_name
VARCHAR2(30),
...)
ENABLE ROW MOVEMENT;
Locally Managed SYSTEM
Tablespace
Create databases with a locally managed
SYSTEM tablespace:
CREATE DATABASE mydb
...
DATAFILE 'system01.dbf'
SIZE 100M
EXTENT MANAGEMENT LOCAL
...;
Flashback Queries
-- Query based on past SCN number:
SELECT employee_id, manager_id
FROM employees
AS OF SCN 81591;
-- Find changes made between 1-2 hours ago:
SELECT a.last_name, b.last_name
FROM employees AS OF TIMESTAMP SYSDATE-1/24 a
FULL OUTER JOIN
employees AS OF TIMESTAMP SYSDATE-2/24 b
ON (a.employee_id = b.employee_id)
-- Create new table based on old data:
CREATE TABLE new_employees AS
SELECT * FROM employees AS OF SCN 90932;
Reminder –
please complete the
OracleWorld session survey
Thank you.
Next Steps….
 Be sure to visit the DemoGrounds and see Oracle
software and services in action
 Check out Meet the Gurus in the DemoGrounds for
your opportunity to meet top Oracle developers
 Attend Mini Theatre presentations relevant to your job
role and education needs
 Don’t forget you can take exams onsite for only $50!
 Remember to register for any OU inClass course
within 60 days of OracleWorld and receive 15%
discount
Next Steps….
 See Your Business in Our Software
–
Visit the DEMOogrounds for a customized architectural
review, see a customized demo with Solutions Factory,
or receive a personalized proposal. Visit the
DEMOgrounds for more information.
 Relevant web sites to visit for more information
–
www.oracle.com/education
Reminder –
please complete the
OracleWorld session survey
Thank you.
QUESTIONS
ANSWERS