How to Prepare a SQL select statement for SQL Tuning in oracle?

How to Prepare a SQL select statement for SQL Tuning in oracle?



1) connect sqlplus, then set following command

set linesize 1000
set pagesize 1000
set trimout on trimspool on

2) then issue following command

alter session set statistics_level=all;

3) [then Run your query ]

4) then issue following command

select * from table ( sys.dbms_xplan.display_cursor( '','','ADVANCED ALLSTATS LAST'));

5)
and then copy&paste the sqlplus output for sql tuning purpose.



example:-


SQL*Plus: Release 10.2.0.1.0 - Production on Sun Oct 2 13:11:14 2011

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL>
SQL> conn scott/tiger
Connected.
SQL>
SQL>
SQL> set linesize 1000
SQL>
SQL> set pagesize 1000
SQL>
SQL> set trimout on trimspool on
SQL>
SQL> alter session set statistics_level=all;

Session altered.

SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> select ename,sum(sal) from emp where ename like '%L%' group by ename order by ename ;

ENAME SUM(SAL)
---------- ----------
ALLEN 1600
BLAKE 2850
CLARK 2450
MILLER 1300

SQL>
SQL> select * from table ( sys.dbms_xplan.display_cursor( '','','ADVANCED ALLSTATS LAST'));




PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------
SQL_ID 0rxmvbjh43c88, child number 0
-------------------------------------
select ename,sum(sal) from emp where ename like '%L%' group by ename order by ename

Plan hash value: 15469362

-------------------------------------------------------------------------------------------------------------------
-----------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers
| OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------
-----------------------------
| 1 | SORT GROUP BY | | 1 | 4 | 80 | 4 (25)| 00:00:01 | 4 |00:00:00.01 | 7
| 9216 | 9216 | 8192 (0)|
|* 2 | TABLE ACCESS FULL| EMP | 1 | 4 | 80 | 3 (0)| 00:00:01 | 4 |00:00:00.01 | 7
| | | |
-------------------------------------------------------------------------------------------------------------------
-----------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$1
2 - SEL$1 / EMP@SEL$1

Outline Data
-------------

/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "EMP"@"SEL$1")
END_OUTLINE_DATA
*/

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("ENAME" LIKE '%L%')

Column Projection Information (identified by operation id):
-----------------------------------------------------------

1 - (#keys=1) "ENAME"[VARCHAR2,10], SUM("SAL")[22]
2 - "ENAME"[VARCHAR2,10], "SAL"[NUMBER,22]

Note
-----
- dynamic sampling used for this statement


47 rows selected.

SQL>

In 32-bit Linux operating system, Memory utilization limit for oracle database

In 32 bit Linux operating system, the default maximum size of the SGA is 1.7GB. For this SHMMAX value needed lager of 1.7GB .
On a 32-bit Linux operating system, without Physical Address Extension (PAE), the physical memory is divided into a 3GB user space and a 1GB kernel space. It is therefore possible to create a 2.7GB SGA, but you will need make several changes at the Linux operating system level by changing the mapped base. In the case of a 2.7GB SGA, you would want to set the SHMMAX parameter to 3GB.

Maximum value of the SHMMAX parameter is 4GB In 32-bit Linux operating system .

ORA-03115:unsupported network datatype or representation in toad 9.6

Cause: A user bind or define, or an Oracle function, is not supported by this heterogeneous Net8 connection.

Action: Upgrade the older version of Oracle and try again.


Problem:-
i am connecting remote oracle database 10g using oracle developer 6i home as a "installed clients" conncetion location in Toad.
its give me this above error, when i am trying to execute AWR report via toad.


Solution:
after then I change the conncetion location home to "oraDB10g_home" in replace of developer 6i home.
then above error is gone.

so this problem is happen for lower version of installed clients net8 connection home.

ORA-28056:writing audit records to windows event log failed

This is because the Event Viewer log of windows is full
and could not write log anymore.

Solution:-

1) clear the event log

as follows

Control Panel->Administrative Tools->Event Viewer->
right click on specific event viewer then > Clear All Events

Note:- you can backup event viewer log. it is just log nothing else.

Oracle Streams and Oracle(CDC) Change Data Capture comparative description

Tom kytes says

think of streams like a brick.  think of CDC like a building made of brick.
streams can be used to build CDC.
CDC is built on top of streams (async CDC is anyway, sync CDC is  trigger based).
they are complimentary, not really competing. 

	CDC	Streams
Definition	Change Data Capture is database functionality that enables capturing incremental changes with predefined API against an Oracle Database and to make the change data available for further use.	Streams provides a flow mechanism in which, the database changes flow in a streamed manner, hence the name Streams. It is a very broad and flexible way of capturing changes, moving them, and applying them.
Purpose	CDC is for monitoring changes in a database. You can create subscriptions for applications so they can see only certain changed data.	For Information Sharing/Distribution Streams are for replicating data between databases for more high availability. The stream routes published information to subscribed destinations. You can route the flow of data through multiple databases without having the changes applied at each intermediate server.
Mechanism	Source TABLE => Change Table => Subscriber view =>TARGET system Change data capture does not depend on intermediate flat files to stage the data outside of the relational database. It captures the change data resulting from INSERT, UPDATE, and DELETE operations made to user tables. The change data is then stored in a database object called a change table, and the change data is made available to applications in a controlled way.	CATPURE => Staging => APPLY Streams, keeps track of the entire database changes. It then stages those changes into queues and later moves them to the destination queues where they are applied to the destination database objects.You make changes to the data before it is applied at the destination. Database objects and data are kept synchronized at all of the databases in the replication environment.
DML and DDL changes	You can capture only DML changes CDC is *NOT* supported for doing DDL operation	You can also capture DDL changes.Note 238457.1 What DML and DDL is Captured by Streams
Usage	CDC most commonly used to capture transactional changes from an OLTP system and publish the changes to one or more subscription systems use Change Data Capture to simplify ET L (extraction and transportation of relational data) in data warehouse applications. The advantage of CDC is that it is intended for extracting data in a data-warehousing environment so you can process only the changed data not entire tables and makes the change data available for further use.	Used for the following: Replicating data from one database to another Message Queuing Data Warehouse Loading. Event management and notification. Data provisioning in a grid environment High availability during database upgrade, platform migration, and application upgrade. Allows data to be transparently shared between both Oracle and non-Oracle data stores.
How does it work	Synchronous Change Data Capture uses triggers on the source database to capture change data. Asynchronous Change Data Capture uses the redo log files. Change data is captured after a SQL statement that performs a DML operation is committed. In this mode, change data is not captured as part of the transaction that is modifying the source table, and therefore has no effect on that transaction. Asynchronous Change Data Capture is available with Oracle Enterprise Edition only not the standard edition.	Streams works by scanning through the redo logs and capturing changes that have been made to specified objects or schema's.
Oracle and OS/platform compatibility	The internal Change Data Capture (CDC) tasks of Streams Capture and Apply are decoupled, thereby enabling a heterogeneous CDC setup with different operating systems and Oracle versions. The propagation between source and target database is a pure Streams implementation, enabling asynchronous CDC to leverage any existing Oracle9i Release 2 system as a source. Asynchronous Change Data Capture (CDC) no longer requires the same operating system for source and target. Furthermore, distributed asynchronous CDC capabilities expands the reach of CDC source systems to include Oracle9i Release 2. In case of Asynchronous AutoLog Mode the source database and the staging database must be running on the same hardware,operating system, and Oracle version. The Distributed HotLog mode of Change Data Capture supports the use of different hardware platforms or operating systems (or both) for the source and staging databases..	For bidirectional/unidirectional Streams replication does not have any restriction on Oracle version or OS . Both source and target can be on different platforms as well different Oracle base releases.. Operational Requirements for Downstream Capture The following are operational requirements for using downstream capture: ■ The source database must be running at least Oracle Database 10g and the downstream capture database must be running the same release of Oracle as the source database or later. ■ The downstream database must be running Oracle Database 10g Release 2 to configure real-time downstream capture. In this case, the source database must be running Oracle Database 10g Release 1 or later. ■ The operating system on the source and downstream capture sites must be the same, but the operating system release does not need to be the same. In addition, the downstream sites can use a different directory structure from the source site. ■ The hardware architecture on the source and downstream capture sites must be the same. For example, a downstream capture configuration with a source database on a 32-bit Sun system must have a downstream database that is configured on a 32-bit Sun system. Other hardware elements, such as the number of CPUs, memory size, and storage configuration, can be different between the source and downstream sites.
With respect to Non Oracle Databases	Does not support changed tables and subscriber view to be present on non-Oracle Databases	Allows data to be shared between oracle and non-oracle data stores.
With respect to logical Standby	CDC Async Distributed Hotlog Mode is *NOT* supported on LOGICAL STANDBY Logical standby as the source of Async CDC (in any mode)/Streams is not supported in any releases 9.2, 10.1, 10.2 and 11.1	Streams cannot be used in 9i and 10g with Logical Standby as logical standby cannot run jobs BUT in 11g you can use Streams with Logical Standby only in Combined Capture and Apply mode
Benefits	CDC offers cost savings by simplifying the extraction of change data from database as its part of Oracle 9i database and later versions. CDC Captures change data resultant of DML operations including the before and after update values of an update operation. Data changes are captured automatically to change table.Very friendly simple to use APIs to publish and subscribe to the changes.Can be scripted with very little effort. Asynchronous CDC captures data with very little performance impact. Best of both worlds.Automatic purge of consumed or obsolete change data captured in change table. CDC ensures that every subscriber sees all changes.Efficient tracking of multiple subscribers and provides a shared access to the changed data. Transactional consistency for changes across multiple source tables is guaranteed. Transparently coordinates sharing of change data across users and applications	Integrated Feature of Oracle9i Database No additional software to install. No special commands to learn.Takes advantage of reliability and security provided with Oracle9i Database. Manageable Provides maximum flexibility for configuration and administration with Oracle-supplied PL/SQL packages. Provides wizards and monitoring capabilities for ease of use with Streams tool in Oracle Enterprise Manager. Keeps users informed about their environment through Data dictionary view. Performance Hot mining of the online redo log reduces the latency of data capture. Parallel capture and apply processes ensure maximum throughput for concurrent events
LIMITATION	Asynchronous mode CDC purely worked based on logged operations, so any non-logged DML operations are not captured. Synchronous mode CDC does not support direct load insert. CDC cannot be implemented on table with TDE (Transparent Data Encryption) enabled. Asynchronous mode capture will not work without supplemental logging. Although direct select is possible on change table but the extraction of the changed data is valid/supported only via subscriber views. CDC is not a development solution to perform any validations or transformation or provide any application specific checks.	TDE columns are not supported by Oracle Streams in 9i and 10g but supported in 11g TDE is supported is 11g for Asyncronous CDC as specified in the Datawarehouse guide: Note that you cannot use any table that uses transparent data encryption as a source table for synchronous Change Data Capture. Asynchronous Change Data Capture supports transparent data encryption if both the source and staging databases have COMPATIBLE set to 11 or higher. Change Data Capture will not encrypt the data in the change table. A user who wants to encrypt a column in the change table can manually use an ALTER TABLE statement to encrypt the column in the change table. Oracle Streams provides database support for a wide variety of data types, but does not provide native support for data movement of some advanced data types. However, by using Extended Data type Support (EDS), you can take advantage of the flexibility of Streams to accommodate several more advanced data types. See http://www.oracle.com/technology/deploy/availability/pdf/maa_edtsoverview.pdf Note 238455.1 Streams DML Types Supported and Supported Data types http://download.oracle.com/docs/cd/B19306_01/server.102/b14229/strms_over.htm
Reference	Oracle® Database Data Warehousing Guide 10g Release 2 (10.2) Part Number B14223-02 Chapter 16 Change Data Capture http://www.oracle.com/technology/products/bi/db/10g/pdf/twp_cdc_cookbook_0206.pdf http://www.oracle.com/technology/oramag/oracle/03-nov/o63tech_bi.html http://download.oracle.com/docs/cd/B19306_01/server.102/b14223.pdf