Oracle Database

CURSOR_SHARING determines what kind of SQL statements can share the same cursors.

Example:

SET TIMI ON

SHOW PARAMETER CURSOR_SHARING

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

cursor_sharing string EXACT

SQL> ALTER SYSTEM FLUSH SHARED_POOL;

System altered.

Elapsed: 00:00:00.18

SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;

System altered.

Elapsed: 00:00:10.37

SQL> CONN SCOTT/*****

Connected.

SQL> SELECT * FROM EMP WHERE DEPTNO=10;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

9001 SCOTT ANALYST 1500 10

7782 CLARK MANAGER 7839 09-JUN-81 2450 10

7839 KING PRESIDENT 17-NOV-81 5000 10

7934 MILLER CLERK 7782 23-JAN-82 1300 10

Elapsed: 00:00:00.06

SQL> SELECT * FROM EMP WHERE DEPTNO=20;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

7369 SMITH CLERK 7902 17-DEC-80 800 20

7566 JONES MANAGER 7839 02-APR-81 2975 20

7788 SCOTT ANALYST 7566 19-APR-87 3000 20

7876 ADAMS CLERK 7788 23-MAY-87 1100 20

7902 FORD ANALYST 7566 03-DEC-81 3000 20

Elapsed: 00:00:00.01

SQL> SELECT * FROM EMP WHERE DEPTNO=30;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30

7698 BLAKE MANAGER 7839 01-MAY-81 2850 30

7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30

7900 JAMES CLERK 7698 03-DEC-81 950 30

6 rows selected.

Elapsed: 00:00:00.01

SQL> SELECT * FROM EMP WHERE DEPTNO=40;

no rows selected

Elapsed: 00:00:00.00

SQL>

SQL> CONN SYS/****** AS SYSDBA

Connected.

SQL> DESC v$SQL

Name Null? Type

----------------------------------------------------------- -------- -----------------------------------------

SQL_TEXT VARCHAR2(1000)

SQL_FULLTEXT CLOB

SQL_ID VARCHAR2(13)

SHARABLE_MEM NUMBER

PERSISTENT_MEM NUMBER

RUNTIME_MEM NUMBER

SORTS NUMBER

LOADED_VERSIONS NUMBER

OPEN_VERSIONS NUMBER

USERS_OPENING NUMBER

FETCHES NUMBER

EXECUTIONS NUMBER

PX_SERVERS_EXECUTIONS NUMBER

END_OF_FETCH_COUNT NUMBER

USERS_EXECUTING NUMBER

LOADS NUMBER

FIRST_LOAD_TIME VARCHAR2(19)

INVALIDATIONS NUMBER

PARSE_CALLS NUMBER

DISK_READS NUMBER

DIRECT_WRITES NUMBER

BUFFER_GETS NUMBER

APPLICATION_WAIT_TIME NUMBER

CONCURRENCY_WAIT_TIME NUMBER

CLUSTER_WAIT_TIME NUMBER

USER_IO_WAIT_TIME NUMBER

PLSQL_EXEC_TIME NUMBER

JAVA_EXEC_TIME NUMBER

ROWS_PROCESSED NUMBER

COMMAND_TYPE NUMBER

OPTIMIZER_MODE VARCHAR2(10)

OPTIMIZER_COST NUMBER

OPTIMIZER_ENV RAW(797)

OPTIMIZER_ENV_HASH_VALUE NUMBER

PARSING_USER_ID NUMBER

PARSING_SCHEMA_ID NUMBER

PARSING_SCHEMA_NAME VARCHAR2(30)

KEPT_VERSIONS NUMBER

ADDRESS RAW(4)

TYPE_CHK_HEAP RAW(4)

HASH_VALUE NUMBER

OLD_HASH_VALUE NUMBER

PLAN_HASH_VALUE NUMBER

CHILD_NUMBER NUMBER

SERVICE VARCHAR2(64)

SERVICE_HASH NUMBER

MODULE VARCHAR2(64)

MODULE_HASH NUMBER

ACTION VARCHAR2(64)

ACTION_HASH NUMBER

SERIALIZABLE_ABORTS NUMBER

OUTLINE_CATEGORY VARCHAR2(64)

CPU_TIME NUMBER

ELAPSED_TIME NUMBER

OUTLINE_SID NUMBER

CHILD_ADDRESS RAW(4)

SQLTYPE NUMBER

REMOTE VARCHAR2(1)

OBJECT_STATUS VARCHAR2(19)

LITERAL_HASH_VALUE NUMBER

LAST_LOAD_TIME VARCHAR2(19)

IS_OBSOLETE VARCHAR2(1)

CHILD_LATCH NUMBER

SQL_PROFILE VARCHAR2(64)

PROGRAM_ID NUMBER

PROGRAM_LINE# NUMBER

EXACT_MATCHING_SIGNATURE NUMBER

FORCE_MATCHING_SIGNATURE NUMBER

LAST_ACTIVE_TIME DATE

BIND_DATA RAW(2000)

SQL> SELECT SQL_TEXT FROM v$SQL WHERE SQL_TEXT LIKE '%SELECT%*%FROM%EMP%';

SQL_TEXT

---------------------------------------------------------------------------------------------------------------

SELECT * FROM EMP WHERE DEPTNO=20

SELECT * FROM EMP WHERE DEPTNO=40

SELECT * FROM EMP WHERE DEPTNO=30

SELECT * FROM EMP WHERE DEPTNO=10

SELECT SQL_TEXT FROM v$SQL WHERE SQL_TEXT LIKE '%SELECT%*%FROM%EMP%'

Elapsed: 00:00:00.03

SQL>

SQL> ALTER SYSTEM SET cursor_sharing=SIMILAR;

System altered.

Elapsed: 00:00:00.12

SQL>

SQL> CONN SCOTT/TIGER

Connected.

SQL> SELECT * FROM EMP WHERE EMPNO=7788;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

7788 SCOTT ANALYST 7566 19-APR-87 3000 20

Elapsed: 00:00:00.01

SQL> SELECT * FROM EMP WHERE EMPNO=7839;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

7839 KING PRESIDENT 17-NOV-81 5000 10

Elapsed: 00:00:00.00

SQL> SELECT * FROM EMP WHERE EMPNO=9002;

no rows selected

Elapsed: 00:00:00.00

SQL> SELECT * FROM EMP WHERE EMPNO=9001;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

9001 SCOTT ANALYST 1500 10

Elapsed: 00:00:00.00

SQL> CONN / AS SYSDBA

Connected.

SQL> SELECT SQL_TEXT FROM v$SQL WHERE SQL_TEXT LIKE '%SELECT%*%FROM%EMP%';

SQL_TEXT

---------------------------------------------------------------------------------------------------------------

SELECT * FROM EMP WHERE DEPTNO=20

SELECT * FROM EMP WHERE DEPTNO=40

SELECT * FROM EMP WHERE DEPTNO=30

SELECT * FROM EMP WHERE EMPNO=:"SYS_B_0"

SELECT * FROM EMP WHERE DEPTNO=10

SELECT SQL_TEXT FROM v$SQL WHERE SQL_TEXT LIKE '%SELECT%*%FROM%EMP%'

6 rows selected.

Oracle 10g Database New Features

Simplicity vs Flexibility

• Automatic statistics gathering

• Advisories

• Automatic tuning

• Less than 30 basic (init.ora) parameters

– Basic, advanced, hidden parameters

• Easier operations

– alter tablespace rename

– flashback queries

– “undrop” table

Manageability

• SGA_TARGET

– Sets total size for all SGA components

– Buffer Cache, Shared Pool, Large Pool, Java Pool

– Dynamically and automatically adjustable

– Automatic changes persist in SPFILE

• PGA_AGGREGATE_TARGET

– Available since 9i

– Sets total size target for all server processes

– sort_area_size, sort_area_retained_size,
hash_area_size, bitmap_merge_area_size

– Contents automatically managed

Basic Parameters

COMPATIBLE

CONTROL_FILES

DB_BLOCK_SIZE

DB_CREATE_FILE_DEST

DB_CREATE_ONLINE_LOG_DEST

DB_DOMAIN

DB_NAME

DB_RECOVERY_FILE_DEST

DB_RECOVERY_FILE_DEST_SIZE

INSTANCE_NUMBER

JOB_QUEUE_PROCESSES

LOG_ARCHIVE_DEST_n

LOG_ARCHIVE_DEST_STATE_n

NLS_LANGUAGE

NLS_TERRITORY

OPEN_CURSORS

PROCESSES

REMOTE_LISTENER

REMOTE_LOGIN_PASSWORDFILE

ROLLBACK_SEGMENTS

SESSIONS

SHARED_SERVERS

STAR_TRANSFORMATION_ENABLED

UNDO_MANAGEMENT

UNDO_TABLESPACE

Rename Tablespace

• Useful in Transportable Tablespace scenarios

• ALTER TABLESPACE prod RENAME to arc1;

– Can’t rename SYSTEM or SYSAUX

– Tablespace and all datafiles must be online

– Can also rename READ ONLY tablespaces

Bigfile Tablespaces

• Support for sizes up to 8 Exabytes!

– 8 000 000 Terabytes

– Max 65535 files in database

– SYSTEM & SYSAUX can’t be bigfile tablespaces

Crossplatform Transportable TS

RMAN> CONVERT TABLESPACE sales_1,sales_2

2> TO PLATFORM ’Microsoft Windows NT’

3> FORMAT ’/temp/%U’;

...

Transporting Tablespaces Between Databases

input datafile fno=00004 name=/u01/oracle/oradata/salesdb/sales_101.dbf

converted datafile=/temp/data_D-10_I-3295731590_TS-EXAMPLE_FNO-4_06ek24vl

channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:45

Data Pump

• A server-managed data transportation tool

• Direct load/extract capabilities

• Very high performance/efficient with large data sets

• Replacement for exp/imp

– old exp/imp remain supported

• Commands expdp/impdp

• Can use files or direct network transfer

• Dynamic configuration, resumable operations

• Client can detach and reconnect

• Can be parallelized using PARALLEL

– Even loads to/from external text files

– Parallelization level can be changed on the fly for long running jobs

• Monitored through DBA_DATAPUMP_JOBS

• Fine-Grained Object Selection

– exclude=function

– exclude=procedure

– exclude=package:”like ‘PAYROLL%’ ”

– include=table

– content=metadata_only | data_only | both

– query=“modify_date > sysdate-1”

• DDL Transformations, DDL extract

FlashBack Database

• Flash Recovery Area must be configured

– Flashback logs are stored there

– Consisting of old database block images

– Fast “rollback” of database, no redologs required

FlashBack Database

Configuration parameters:

• DB_RECOVERY_FILE_DEST

• DB_RECOVERY_FILE_DEST_SIZE

• DB_FLASHBACK_RETENTION_TARGET

Commands:

• ALTER DATABASE FLASHBACK ON;

• ALTER DATABASE FLASHBACK OFF;

• ALTER TABLESPACE test1 FLASHBACK OFF;

• ALTER TABLESPACE test1 FLASHBACK ON;

Flashback Row History

SELECT versions_xid XID, versions_startscn START_SCN,

versions_endscn END_SCN, versions_operation OPERATION,

empname, salary FROM hr.employees_demo

VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE

where empno = 111;

XID START_SCN END_SCN OPERATION EMPNAME SALARY

--- --------- ------- --------- ------- ------

0004000700000058 113855 I Tom 927

000200030000002D 113564 D Mike 555

000200030000002E 112670 113564 I Mike 555

3 rows selected

• Useful for auditing

Flashback Transaction History

select xid, start_scn, commit_scn, operation,
undo_sql, table_name

from dba_transaction_query where xid = ’000200030000002D’;

XID START_SCN COMMIT_SCN OPERATION UNDO_SQL

--- --------- ---------- --------- -------------------------

000200030000002D 112670 113565 D insert into "SCOTT"."EMP"

("EMPNO","EMPNAME","SALARY") values ('111','Mike','655')

000200030000002D 112670 113565 I delete from "SCOTT"."DEPT"

where "DEPTNO" = '20' and "DEPTNAME" = 'Finance'

000200030000002D 112670 113565 D update “SCOTT”.”EMP”

set “SALARY” = ‘555’ where “EMPNO” = ‘111’ and ”EMPNAME” = ‘Mike’ and “SALARY”

= ‘655’

3 rows selected

Table Recovery using Flashback

• DROP TABLE X;

– Table is renamed internally, not dropped

– Indexes & other structures remain

– Table is purged when out of free space or quota

• SELECT * FROM RECYCLEBIN;

– Systemwide recyclebin DBA_RECYCLEBIN

– Or show recyclebin command in sqlplus

• FLASHBACK TABLE RB$$3560$TABLE$1
TO BEFORE DROP RENAME TO scott.emp;

• PURGE RECYCLEBIN;

• DROP TABLE X PURGE;

Performance Tuning

• RBO is dead, long live the CBO!

– Even data dictionary, SYS tables using CBO

– However, RBO is gone nowhere, it’s available

• Optimizer able to use run-time statistics

– exec dbms_stats.gather_system_statistics(…)

– OPTIMIZER_DYNAMIC_SAMPLING (default: 2)

• Multiple Advisors

– SQL Access & Tuning Advisor

– Memory Advisors (SGA, Shared Pool, etc..)

– Segment Advisor (Fragmentation, etc..)

– Undo Advisor

Performance Troubleshooting

• Automatic Workload Repository

– Runtime execution statistics are gathered in memory

– MMON background process flushes stats to disk

• V$SQL_BIND_CAPTURE

– Samples bind variables for all sessions

– Faster than sql_trace (10046 trace on level 4)

– But doesn’t capture all variable types

– And doesn’t capture occurences of bindings

• _cursor_bind_capture_interval defaults to 900 seconds

– Good for getting samples of database operations

SQLPLUS Changes

Improvements

• SPOOL CREATE | REPLACE | APPEND

– also works for SAVE command

• SHOW RECYCLE BIN

• SQLPROMPT runtime variable substitution

– SET SQLPROMPT "_USER'@'_CONNECT_IDENTIFIER >"

• glogin.sql and login.sql scripts are executed also on CONNECT

Other

• dbms_scheduler

– calendar Expressions: Yearly, Monthly, Weekly, Daily, Hourly, Minutely, Secondely

• alter system flush buffer_cache;

• drop database;

– database must be closed

– mounted exclusively

– restricted

• default user tablespace

– specifies default tablespace for new users, similar to default temporary tablespace in 9i

* NYOUG General Meeting March 2004

Oracle Database

Pages

Monday, January 10, 2011

Cursor_Sharing Parameter

Saturday, December 11, 2010

Oracle 10g Database New Features