Sunday, December 30, 2012

Exports/Imports IMPDP and EXPDP


For Particular Tables

expdp TEST/TEST@ORCL tables=EMP,DEPT directory=TEST_DIR dumpfile=test.dmp logfile=test.log

impdp TEST/TEST@ORCL tables=EMP,DEPT directory=TEST_DIR dumpfile=test.dmp logfile=test.log

 


 
 
For 

Schema Exports/Imports


expdp TEST/TEST@ORCL schemas=HR dumpfile=test.dmp logfile=test.log

impdp TEST/TEST@ORCL schemas=HR dumpfile=test.dmp logfile=test.log  


import recreate the new table as in the dumpfile. 

impdp  dumpfile=emp.dmp logfile=imp1.log table_exists_action=replace

 
 
 

Thursday, October 25, 2012

Basic Oracle space monitoring scripts


select tbs.tablespace_name, tot.bytes/1024 total, tot.bytes/1024-sum(nvl(fre.bytes,0))/1024 used, sum(nvl(fre.bytes,0))/1024 free, (1-sum(nvl(fre.bytes,0))/tot.bytes)*100 pct, decode( greatest((1-sum(nvl(fre.bytes,0))/tot.bytes)*100, 90), 90, '', '*' ) pct_warn from dba_free_space fre, (select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name) tot, dba_tablespaces tbs where tot.tablespace_name = tbs.tablespace_name and fre.tablespace_name(+) = tbs.tablespace_name group by tbs.tablespace_name, tot.bytes/1024, tot.bytes order by 5, 1 ;

What is Explain Plan in Oracle, & how do we use it?

An Explain Plan is a tool that you can use to have Oracle explain to you how it plans on executing your query. This is useful in tuning queries to the database to get them to perform better. Once you know how Oracle plans on executing your query, you can change your environment to run the query faster.
Before you can use the EXPLAIN PLAN command, you need to have a PLAN_TABLE installed. This can be done by simply running the $ORACLE_HOME/rdbms/admin/utlxplan.sql script in your schema. It creates the table for you. After you have the PLAN_TABLE created, you issue an EXPLAIN PLAN for the query you are interested in tuning. The command is of the form:
EXPLAIN PLAN SET STATEMENT_ID='somevalue' FOR some SQL statement;
You need to use a statement_id and then give your SQL statement. For instance, suppose I have a query to tune. How does it get executed? I issue the following in SQL*Plus:

SQL> explain plan set statement_id = 'q1' for 2 select object_name from test where object_name like 'T%'; Explained.
I used 'q1' for my statement id (short for query 1). But you can use anything you want. My SQL statement is the second line. Now I query the PLAN_TABLE to see how this statement is executed. This is done with the following query:

SQL> SELECT LPAD(' ',2*(level-1)) || operation || ' ' || options || ' ' || 2 object_name || ' ' || DECODE(id,0,'Cost = ' || position) AS "Query Plan",other 3 FROM plan_table 4 START WITH id = 0 5 AND statement_id='q1' 6 CONNECT BY PRIOR ID = PARENT_ID 7* AND statement_id = 'q1' Query Plan OTHER -------------------------------------------------- -------------------------------------------------- SELECT STATEMENT Cost = TABLE ACCESS FULL TEST
This tells me that my SQL statement will perform a FULL table scan on the TEST table (TABLE ACCESS FULL TEST). Now let's add an index on that table!

SQL> create index test_name_idx on test(object_name); Index created. SQL> truncate table plan_table; Table truncated. SQL> explain plan set statement_id = 'q1' for 2 select object_name from test where object_name like 'T%'; Explained. SQL> SELECT LPAD(' ',2*(level-1)) || operation || ' ' || options || ' ' || 2 object_name || ' ' || DECODE(id,0,'Cost = ' || position) AS "Query Plan",other 3 FROM plan_table 4 START WITH id = 0 5 AND statement_id='q1' 6 CONNECT BY PRIOR ID = PARENT_ID 7* AND statement_id = 'q1' Query Plan OTHER -------------------------------------------------- -------------------------------------------------- SELECT STATEMENT Cost = INDEX RANGE SCAN TEST_NAME_IDX
I added an index to the table. Before I issue another EXPLAIN PLAN, I truncate the contents of my PLAN_TABLE to prepare for the new plan. Then I query the PLAN TABLE. Notice that this time I'm using an index (TEST_NAME_IDX) that I created!! Hopefully, this query will run faster now that it has an index to use. But this may not always be the case.

Thursday, October 18, 2012

Oracle Database Performance Tuning Steps.

Using Baselines

Comparative Performance Analysis with AWR Baselines
Automatic Workload Repository Baselines
Moving Window Baseline
Baselines in Performance Page Settings & Baseline Templates
AWR Baselines & Creating AWR Baselines
Managing Baselines with PL/SQL & Baseline Views
Performance Monitoring and Baselines & Defining Alert Thresholds Using a Static Baseline

Using AWR Based Tools

Automatic Maintenance Tasks
ADDM Performance Monitoring

Identifying Problem SQL Statements

SQL Statement Processing Phases & Role of the Oracle Optimizer
Identifying Bad SQL, Real Time SQL Monitoring (a 11.1 feature new lesson in NF L-15) & TOP SQL Reports
What Is an Execution Plan? Methods for Viewing Execution Plans & Uses of Execution Plans
DBMS_XPLAN Package: Overview & EXPLAIN PLAN Command
Reading an Execution Plan, Using the V$SQL_PLAN View & Querying the AWR
SQL*Plus AUTOTRACE & SQL Trace Facility
How to Use the SQL Trace Facility
Generate an Optimizer Trace

Influencing the Optimizer

Functions of the Query Optimizer, Selectivity, Cardinality and Cost & Changing Optimizer Behavior
Using Hints, Optimizer Statistics & Extended Statistics
Controlling the Behavior of the Optimizer with Parameters
Enabling Query Optimizer Features & Influencing the Optimizer Approach
Optimizing SQL Statements, Access Paths & Choosing an Access Path
Join & Sort Operations
How the Query Optimizer Chooses Execution Plans for Joins
Reducing the Cost

Using SQL Performance Analyzer

Real Application Testing: Overview & Use Cases
SQL Performance Analyzer: Process & Capturing the SQL Workload
Creating a SQL Performance Analyzer Task & SPA (NF Lesson 9) DBMS_SQLTUNE.CREATE_TUNING_TASK
Optimizer Upgrade Simulation & SQL Performance Analyzer Task Page
Comparison Report & Comparison Report SQL Detail
Tuning Regressing Statements & Preventing Regressions
Parameter Change Analysis & Guided Workflow Analysis
SQL Performance Analyzer: PL/SQL Example & Data Dictionary Views

SQL Performance Management

Maintaining SQL Performance and Optimizer Statistics & Automated Maintenance Tasks
Statistic Gathering Options & Setting Statistic Preferences
Restore Statistics
Deferred Statistics Publishing: Overview & Example
Automatic SQL Tuning: Overview
SQL Tuning Advisor: Overview
Using the SQL Access Advisor
SQL Plan Management: Overview

Tuning PGA and Temporary Space
SQL Memory Usage & Performance Impact
SQL Memory Manager
Configuring Automatic PGA Memory & Setting PGA_AGGREGATE_TARGET Initially
Monitoring & Tuning SQL Memory Usage
PGA Target Advice Statistics & Histograms
Automatic PGA and Enterprise Manager & Automatic PGA and AWR Reports
Temporary Tablespace Management: Overview & Monitoring Temporary Tablespace
Temporary Tablespace Shrink & Tablespace Option for Creating Temporary Table

DBA_TABLESPACES

DBA_TABLESPACES and USER_TABLESPACES views indicates if the tablespace is encrypted or not.

SELECT tablespace_name, encrypted FROM dba_tablespaces;
 
TABLESPACE_NAME                ENC
------------------------------ ---
SYSTEM                         NO
SYSAUX                         NO
UNDOTBS1                       NO
TEMP                           NO
USERS                          NO
 

 

Listing Tablespaces and Default Storage Parameters

 

SELECT TABLESPACE_NAME "TABLESPACE",INITIAL_EXTENT, NEXT_EXTENT ,MIN_EXTENTS,
MAX_EXTENTS ,PCT_INCREASEFROM DBA_TABLESPACES;

 

How to check tablespace usage in Oracle

select sysdate, a.owner username, a.tablespace_name, round(b.total_space/1024/1024,2) "Total (MB)", round(sum(a.bytes)/1024/1024,2) "Used (MB)", round(sum(a.bytes/b.total_space)*100,2) "% Used"
from dba_segments a, (select tablespace_name, sum(bytes) total_space
                      from dba_data_files
                      group by tablespace_name) b
where a.tablespace_name not in ('SYSAUX', 'SYSTEM', 'UNDOTBS1', 'UNDOTBS2')
and a.tablespace_name = b.tablespace_name
group by a.tablespace_name, a.owner, b.total_space/1024/1024
order by a.tablespace_name, a.owner;

 

 

Tablespace Data Dictionary Views


View
Description
V$TABLESPACE
Name and number of all tablespaces from the control file.
V$ENCRYPTED_TABLESPACES
Name and encryption algorithm of all encrypted tablespaces.
DBA_TABLESPACES, USER_TABLESPACES
Descriptions of all (or user accessible) tablespaces.
DBA_TABLESPACE_GROUPS
Displays the tablespace groups and the tablespaces that belong to them.
DBA_SEGMENTS, USER_SEGMENTS
Information about segments within all (or user accessible) tablespaces.
DBA_EXTENTS, USER_EXTENTS
Information about data extents within all (or user accessible) tablespaces.
DBA_FREE_SPACE, USER_FREE_SPACE
Information about free extents within all (or user accessible) tablespaces.
DBA_TEMP_FREE_SPACE
Displays the total allocated and free space in each temporary tablespace.
V$DATAFILE
Information about all datafiles, including tablespace number of owning tablespace.
V$TEMPFILE
Information about all tempfiles, including tablespace number of owning tablespace.
DBA_DATA_FILES
Shows files (datafiles) belonging to tablespaces.
DBA_TEMP_FILES
Shows files (tempfiles) belonging to temporary tablespaces.
V$TEMP_EXTENT_MAP
Information for all extents in all locally managed temporary tablespaces.
V$TEMP_EXTENT_POOL
For locally managed temporary tablespaces: the state of temporary space cached and used for by each instance.
V$TEMP_SPACE_HEADER
Shows space used/free for each tempfile.
DBA_USERS
Default and temporary tablespaces for all users.
DBA_TS_QUOTAS
Lists tablespace quotas for all users.
V$SORT_SEGMENT
Information about every sort segment in a given instance. The view is only updated when the tablespace is of the TEMPORARY type.
V$TEMPSEG_USAGE
Describes temporary (sort) segment usage by user for temporary or permanent tablespaces.

Statistics on Tables, Indexes and Columns

  • DBA_TABLES
  • DBA_OBJECT_TABLES
  • DBA_TAB_STATISTICS
  • DBA_TAB_COL_STATISTICS
  • DBA_TAB_HISTOGRAMS
  • DBA_TAB_COLS
  • DBA_COL_GROUP_COLUMNS
  • DBA_INDEXES
  • DBA_IND_STATISTICS
  • DBA_CLUSTERS
  • DBA_TAB_PARTITIONS
  • DBA_TAB_SUBPARTITIONS
  • DBA_IND_PARTITIONS
  • DBA_IND_SUBPARTITIONS
  • DBA_PART_COL_STATISTICS
  • DBA_PART_HISTOGRAMS
  • DBA_SUBPART_COL_STATISTICS
  • DBA_SUBPART_HISTOGRAMS