How to insert and select data in Oracle BLOB column:
Tuesday, December 29, 2015
Tuesday, December 8, 2015
Performance improvement methods and my understanding of index
Perormance improvement methods and my understanding of index to improve performance of SQL queries:
Some of the terminology worth knowing:
Tablespace
Tablespaces are a logical organization of space.
Tablespaces owns the database’s datafiles.
Database objects are stored within tablespaces.
Database: a logical collection of shared data stored in tablespaces.
File: A physical datafile belonging to a single tablespace.
Segment: A set of one or more extents that contains all the data for a specific structure within a tablespace. (TABLE, INDEX, CLUSTER, ROLLBACK, TEMP, etc.)
Extent: A set of contiguous data blocks with in a database that make up a segment.
Block: One or more physical file blocks allocated from an existing datafile.
TYPES OF INDEX:
B-TREE index.
Bitmap index.
What is a b-tree index?
In a tree, records are stored in locations called leaves. The starting point is called the root. The maximum number of children per node is called the order of the tree. The maximum number of access operations required to reach the desired leaf (data stored on the leaf) is called the depth (level). Oracle indexes are balanced b-trees; the order is the same at every node and the depth is the same for every leaf.
The bigger the order, the more leaves and nodes you can put at a certain depth. This means that there are fewer levels to traverse to get to the leaf (which contains the data you want). In all balanced b-trees, the number of hops to a leaf == depth
Characteristic of Bitmap Indexes
For columns with very few unique values (low cardinality)
Columns that have low cardinality are good candidates (if the cardinality of a column is <= 0.1 % that the column is ideal candidate, consider also 0.2% – 1%)
Tables that have no or little insert/update are good candidates (static data in warehouse)
Stream of bits: each bit relates to a column value in a single row of table
create bitmap index person_region on person (region); Row Region North East West South 1 North 1 0 0 0 2 East 0 1 0 0 3 West 0 0 1 0 4 West 0 0 1 0 5 South 0 0 0 1 6 North 1 0 0 0
Statistics generated include the following:
Table statistics
Number of rows
Number of blocks
Average row length
Column statistics
Number of distinct values (NDV) in column
Number of nulls in column
Data distribution (histogram)
Index statistics
Number of leaf blocks
Levels
Clustering factor
System statistics
I/O performance and utilization
CPU performance and utilization
To view statistics in the data dictionary, query the appropriate data dictionary view. The following list shows the DBA_ views:
DBA_TABLES
DBA_TAB_COL_STATISTICS
DBA_INDEXES
To verify that index statistics are available and decide which are the best indexes to use in an application, query the data dictionary view DBA_INDEXES
SQL> SELECT INDEX_NAME "NAME", NUM_ROWS, DISTINCT_KEYS "DISTINCT", 1 LEAF_BLOCKS, CLUSTERING_FACTOR "CF", BLEVEL "LEVEL", 2 AVG_LEAF_BLOCKS_PER_KEY "ALFBPKEY" 3 FROM DBA_INDEXES 4 WHERE owner = 'SH' 5* ORDER BY INDEX_NAME;
The optimizer uses the following criteria when determining which index to use:
Number of rows in the index (cardinality).
Number of distinct keys. These define the selectivity of the index.
Level or height of the index. This indicates how deeply the data 'probe' must search in order to find the data.
Number of leaf blocks in the index. This is the number of I/Os needed to find the desired rows of data.
Clustering factor (CF). This is the collocation amount of the index block relative to data blocks. The higher the CF, the less likely the optimizer is to select this index.
How index are created?
Automatically : A unique index is created automatically when you define a PRIMARY KEY or UNIQUE KEY constraint in a table definition. The name of index is name given to constraint.
Manually : Users can create non unique indexes on columns to speed up access to the rows.
Create an index on one or more columns
CREATE INDEX index_name
ON table (column[, column]…);
Example :
CREATE INDEX emp_last-name_idx
ON employees(last_name);
This will speed up query access to the LAST_NAME
column in the EMPLOYEES table.
Create an index if
A column contains a wide range of values
A column contains a large number of null values
One or more columns are frequently used together in a WHERE clause or a join condition
The table is large and most queries are expected to retrieve less than 2 to 4 percent of the rows
When not to create an Index
It is usually not worth creating an Index if :
The table is small
The columns are not often used as a condition in the query
Most queries are expected to retrieve more than 2 to 4 percent of rows in the table
The table is updated frequently
The indexed columns are referenced as part of an expression
Confirming Indexes:
Check the Index creation and its fields in
The USER_INDEXES data dictionary view contains the name of the index and its uniqueness
The USER_IND_COLUMNS view contains the index name, the table name, and the column name.
Example:
SELECT ic.index_name,ic.column_name, ix.uniqueness
FROM user_indexes ix, user_ind_columns ic
WHERE ic.index_name=ix.index_name
AND ic.table_name=‘EMPLOYEES’;
Function-Based Indexes:
The index expression is built from table columns, constants, SQL functions, and user-defined functions.
CREATE INDEX upper_dept_name_idx
ON departments(UPPER(department_name));
A function based index is an index based on expressions
Removing an Index:
Remove an index from the data dictionary by using the DROP INDEX command.
DROP INDEX index;
To drop an index, you must be the owner of the index or have the DROP ANY INDEX privilege
Example :
DROP INDEX upper_last_name-idx;
Index Selectivity:
B*TREE Indexes improve the performance of queries that select a small percentage of rows from a table. As a general guideline, we should create indexes on tables that are often queried for less than 15% of the table's rows. This value may be higher in situations where all data can be retrieved from an index, or where the indexed columns can be used for joining to other tables.
The ratio of the number of distinct values in the indexed column / columns to the number of records in the table represents the selectivity of an index. The ideal selectivity is 1. Such a selectivity can be reached only by unique indexes on NOT NULL columns.
Example with good Selectivity
A table having 100'000 records and one of its indexed column has 88000 distinct values, then the selectivity of this index is 88'000 / 10'0000 = 0.88.
Oracle implicitly creates indexes on the columns of all unique and primary keys that you define with integrity constraints. These indexes are the most selective and the most effective in optimizing performance. The selectivity of an index is the percentage of rows in a table having the same value for the indexed column. An index's selectivity is good if few rows have the same value.
Example with bad Selectivity:
lf an index on a table of 100'000 records had only 500 distinct values, then the index's selectivity is 500 / 100'000 = 0.005 and in this case a query which uses the limitation of such an index will return 100'000 / 500 = 200 records for each distinct value. It is evident that a full table scan is more efficient as using such an index where much more I/O is needed to scan repeatedly the index and the table.
How to Measure Index Selectivity ?
Manually measure index selectivity
The ratio of the number of distinct values to the total number of rows is the selectivity of the columns. This method is useful to estimate the selectivity of an index before creating it.
select count (distinct job) "Distinct Values" from emp;Distinct Values--------------- 5
select count(*) "Total Number Rows" from emp;Total Number Rows----------------- 14
Selectivity = Distinct Values / Total Number Rows = 5 / 14 = 0.35
Automatically measure index selectivity:
We can determine the selectivity of an index by dividing the number of distinct indexed values by the number of rows in the table.
create index idx_emp_job on emp(job); analyze table emp compute statistics;
select distinct_keys from user_indexes where table_name = 'EMP' and index_name = 'IDX_EMP_JOB';
DISTINCT_KEYS------------- 5
select num_rows from user_tables where table_name = 'EMP';
NUM_ROWS--------- 14 Selectivity = DISTINCT_KEYS / NUM_ROWS = 0.35
How to choose Composite Indexes ?
A composite index contains more than one key column. Composite indexes can provide additional advantages over single column indexes.
BETTER SELECTIVITY Sometimes two or more columns, each with poor selectivity, can be combined to form a composite index with good selectivity.
LEADING PORTION OF COMPOSITE INDEX:
An SQL statement can use an access path involving a composite index if the statement contains constructs that use a leading portion of the index. A leading portion of an index is a set of one or more columns that were specified first and consecutively in the list of columns in the CREATE INDEX statement that created the index. Consider this CREATE INDEX statement:
CREATE INDEX idx_composite ON EMP (x, y, z);
These combinations of columns are leading portions of the index: X, XY, and XYZ. These combinations of columns are not leading portions of the index: YZ and Z.
SQL Optimization techniques:
TIP 1: SQL cannot be shared within Oracle unless it is absolutely identical. Statements must have match exactly in case, white space and underlying schema objects to be shared within Oracle's memory. Oracle avoids the parsing step for each subsequent use of an identical statement
Writing efficient query:
TIP 2: Beware of WHERE clauses which do not use indexes at all. Even if there is an index over a column that is referenced by a WHERE clause included in this section, Oracle will ignore the index. All of these WHERE clauses can be re-written to use an index while returning the same values. In other words, don't perform operations on database objects referenced in the WHERE clause.
Do Not Use :
SELECT account_name, trans_date, amount FROM transaction WHERE SUBSTR(account_name,1,7) = 'CAPITAL';
In case of above use the below:
SELECT account_name, trans_date, amount FROM transaction WHERE account_name LIKE 'CAPITAL%';
Do not use:
SELECT account_name, trans_date, amount FROM transaction WHERE TRUNC (trans_date) = TRUNC (SYSDATE);
In case of above use the below:
SELECT account_name, trans_date, amount FROM transaction WHERE trans_date BETWEEN TRUNC (SYSDATE) AND TRUNC (SYSDATE) + .99999;
Do not use:
SELECT account_name, trans_date, amount FROM transaction WHERE amount + 3000 < 5000;
SELECT account_name, trans_date, amount FROM transaction WHERE amount != 0;
SELECT account_name, trans_date, amount FROM transaction WHERE amount NOT NULL;
In case of above use the following;
SELECT account_name, trans_date, amount FROM transaction WHERE amount < 2000;
SELECT account_name, trans_date, amount FROM transaction WHERE amount > 0;
SELECT account_name, trans_date, amount FROM transaction WHERE amount > 0;
TIP 3: Avoid including a HAVING clause in SELECT statements. The HAVING clause filters selected rows only after all rows have been fetched. Using a WHERE clause helps reduce overheads in sorting, summing, etc. HAVING clauses should only be used when columns with summary operations applied to them are restricted by the clause.
Do not use;
SELECT region, AVG (loc_size) FROM location GROUP BY region HAVING region != 'SYDNEY' AND region != 'PERTH';
Use the below:
SELECT region, AVG (loc_size) FROM location WHERE region != 'SYDNEY' AND region != 'PERTH'; GROUP BY region;
TIP 4: Minimize the number of table lookups (subquery blocks) in queries, particularly if your statements include subquery SELECTs or multicolumn UPDATEs.
Do not use:
SELECT emp_name FROM emp WHERE emp_cat = (SELECT MAX (category) FROM emp_categories) AND emp_range = (SELECT MAX (sal_range) FROM emp_categories) AND emp_dept = 0020;
Use the below:
SELECT emp_name FROM emp WHERE (emp_cat, sal_range) = (SELECT MAX (category), MAX (sal_range) FROM emp_categories) AND emp_dept = 0020;
TIP 5: Consider whether a UNION ALL will suffice in place of a UNION. The UNION clause forces all rows returned by each portion of the UNION to be sorted and merged and duplicates to be filtered before the first row is returned. A UNION ALL simply returns all rows including duplicates and does not have to perform any sort, merge or filter. If your tables are mutually exclusive (include no duplicate records), or you don't care if duplicates are returned, the UNION ALL is much more efficient.
UNION
SELECT acct_num, balance_amt FROM debit_transactions WHERE tran_date = '31-DEC-95' UNION SELECT acct_num, balance_amt FROM credit_transactions WHERE tran_date = '31-DEC-95
VS UNION ALL
SELECT acct_num, balance_amt FROM debit_transactions WHERE tran_date = '31-DEC-95' UNION ALL SELECT acct_num, balance_amt FROM credit_transactions WHERE tran_date = '31-DEC-95';
TIP 6: Oracle automatically performs simple column type conversions (or casting) when it compares columns of different types. Depending on the type of conversion, indexes may not be used. Make sure you declare your program variables as the same type as your Oracle columns, if the type is supported in the programming language you are using
Tip 7:Try to avoid to many joins in the where predicate.
Writing efficient query contd:
Note these considerations that apply to the cost-based and rule-based approaches:
The optimizer first determines whether joining two or more of the tables definitely results in a row source containing at most one row. The optimizer recognizes such situations based on UNIQUE and PRIMARY KEY constraints on the tables. If such a situation exists, the optimizer places these tables first in the join order. The optimizer then optimizes the join of the remaining set of tables.
For join statements with outer join conditions, the table with the outer join operator must come after the other table in the condition in the join order. The optimizer does not consider join orders that violate this rule.
Tip 8: Avoid writing correlated subquery if possible.
With a correlated subquery, the database must run the subquery for each evaluation because it is based on the outer query’s data.
E.g. select book_key,store_key,quantity from sales s where quantity < (select max(quantity) from sales where book_key = s.book_key);
Tip 9:JOIN-columns should be indexed. JOIN columns or Foreign Key columns may be indexed since queries based on these columns can be expected to be very frequent.
Tip 10:Do not create indexes for small tables. It increases database overheads
HINTS are the comments used in a SQL statement to pass instructions to the Oracle optimizer. The optimizer uses these hints as suggestions for choosing an execution plan for the statement.
Hints are compiler directives that change the execution plan for SQL statements. Of course, if the Oracle cost-based optimizer (CBO) and rule-based optimizer (RBO) always made optimal execution plans, hints would not be necessary.
When to use Hints?
You can use hints to specify the following:
The optimization approach for a SQL statement
The goal of the cost-based optimizer for a SQL statement
The access path for a table accessed by the statement
The join order for a join statement
A join operation in a join statement
Classes of Hints:
There are two classes of hints:
General mode hints—This class of hints changes the overall execution mode for Oracle queries. These hints include /*+ rule */ and /*+ first_rows */.
Detailed directive hints—This class of hints directs specific access paths, such as the use of a hash join over a nested loop join, /*+ use_hash */, and the use of a specific index to access a table
Efficient ways of writing SQL:
Better SQL writing practice:
How to write better SQL queries:
Here is some of my tips for Best SQL Coding Practices for efficient data retrieval:
1. select rowid
from emp_data
where rowid = 'AAAENeAABAAAKz6AAA‘
The rowid of a row specifies the datafile and data block containing the row and the location of the row in that block.
Locating a row by specifying its rowid is the fastest way to retrieve a single row, because the exact location of the row in the database is specified.
To access a table by rowid, Oracle first obtains the rowids of the selected rows, either from the statement's WHERE clause or through an index scan of one or more of the table's indexes.
Oracle then locates each selected row in the table based on its rowid.
2. select *
from emp_data
where substr(emp_code,1,3) = '934'
Vs
INDEX RANGE SCAN
select *
from emp_data
where emp_code like '934%'
3.An index range scan is a common operation for accessing selective data.
It can be bounded (bounded on both sides) or unbounded (on one or both sides).
Data is returned in the ascending order of index columns.
If data must be sorted by order, then use the ORDER BY clause, and do not rely on an index. If an index can be used to satisfy an ORDER BY clause, then the optimizer uses this option and avoids a sort
INDEX UNIQUE SCAN INDEX:
select *
from emp_data
where slno = 1
INDEX UNIQUE SCAN INDEX
An index range scan is a common operation for accessing selective data.
It can be bounded (bounded on both sides) or unbounded (on one or both sides).
Data is returned in the ascending order of index columns.
If data must be sorted by order, then use the ORDER BY clause, and do not rely on an index. If an index can be used to satisfy an ORDER BY clause, then the optimizer uses this option and avoids a sort
FULL TABLE SCAN
select *
from emp_data
where slno between 1 and 800
This type of scan reads all rows from a table and filters out those that do not meet the selection criteria.
During a full table scan, all blocks in the table that are under the high water mark are scanned. Each row is examined to determine whether it satisfies the statement's WHERE clause.
When Oracle performs a full table scan, the blocks are read sequentially. Because the blocks are adjacent, I/O calls larger than a single block can be used to speed up the process.
The size of the read calls range from one block to the number of blocks indicated by the initialization parameter DB_FILE_MULTIBLOCK_READ_COUNT.
Using multiblock reads means a full table scan can be performed very efficiently. Each block is read only once
Index Scan through Hints:
select /*+ index (emp_data,IDX_emp_data) */
*
from emp_data
where 1=1
and sc_name = 'AJANTA SOYA'
-- and emp_code = '519216'
and sc_group = 'B'
Index Scans through Date:
select *
from emp_data
where dt = sysdate
FTS
select *
from emp_data
where trunc(dt) = trunc(sysdate )
-- Tuned
select *
from emp_data
where dt between trunc(sysdate-1) and (trunc(sysdate-1) + .999)
Index Scan through where condition re-org:
select *
from emp_data
where emp_code + 10 = '934847'
Vs
select *
from emp_data
where emp_code = to_char(934847 - 10)
Having condition and performance:
select sc_group, count(*)
from emp_data
where 1=1
group by sc_group
having sc_group = 'Z '
Vs
select sc_group, count(*)
from emp_data
where sc_group = 'Z '
group by sc_group
UNION & UNION ALL:
select *
from emp_data
where sc_group = 'F '
UNION --ALL
select *
from emp_data
where sc_group = 'T '
Data Type sensitive Filters:
select *
from emp_data
where slno = '100'
Vs
-- Char will not use index if Num
select *
from emp_data
where emp_code = (519216)
Composite Indexes:
select *
from emp_data
where 1=1
--and emp_code = '519216'
--and sc_name = 'SOYA '
--and sc_group = 'B '
Concat Problem for performance:
select *
from emp_data
where emp_code|| sc_group = '934844F‘
Vs
select *
from emp_data
where emp_code = '934844'
and sc_group = 'F'
Try to check how does a count work:
select count(*)
from emp_data
Vs
select count(slno)
from emp_data
More on dates:
select *
from emp_data
where to_char(dt,'dd Mon yyyy') = '27 Nov 2012'
select *
from emp_data
where dt between to_date('27 Nov 2012') and to_date('27 Nov 2012') + .999
===========================================
What is the Difference between an empty string and a null value in Oracle
Difference between an empty string and a null value in Oracle:
What is the Difference between an empty string and a null value
--------------------------------------------------------------------------------
When we select those fields that are "empty" versus "null", we get two different result sets.
Answer: An empty string is treated as a null value in Oracle. Let's demonstrate.
Lets create a table called employee with the following table definition:
create table employee
( employee_id number,
employee_name varchar2(100));
Next, we'll insert two records into this table.
insert into employee (employee_id, employee_name ) values ( 10565, null );
insert into employee (employee_id, employee_name ) values ( 10567, '' );
The first statement inserts a record with a employee_name that is null, while the second statement inserts a record with an empty string as a employee_name.
Now, let's retrieve all rows with a employee_name that is an empty string value as follows:
select * from employee where employee_name = '';
When you run this statement, you'd expect to retrieve the row that you inserted above. But instead, this statement will not retrieve any records at all.
Now, try retrieving all employee_ids that contain a null value:
select * from employee where employee_name is null;
When you run this statement, you will retrieve both rows. This is because Oracle has now changed its rules so that empty strings behave as null values.
It is also important to note that the null value is unique in that you can not use the usual operands (=, <, >, etc) on a null value. Instead, you must use the IS NULL and IS NOT NULL conditions
SQL> create table employee
( employee_id number,
employee_name varchar2(100)); 2 3
Table created.
SQL> insert into employee (employee_id, employee_name ) values ( 10545, null );
1 row created.
SQL> commit;
Commit complete.
SQL> insert into employee (employee_id, employee_name ) values ( 10555, '' );
1 row created.
SQL> commit;
Commit complete.
SQL> select * from employee where employee_name = '';
no rows selected
SQL> select * from employee where employee_name is null;
EMPLOYEE_ID EMPLOYEE_NAME
--------------------------------------------------------------------------------
10545
10555
What is the Difference between an empty string and a null value
--------------------------------------------------------------------------------
When we select those fields that are "empty" versus "null", we get two different result sets.
Answer: An empty string is treated as a null value in Oracle. Let's demonstrate.
Lets create a table called employee with the following table definition:
create table employee
( employee_id number,
employee_name varchar2(100));
Next, we'll insert two records into this table.
insert into employee (employee_id, employee_name ) values ( 10565, null );
insert into employee (employee_id, employee_name ) values ( 10567, '' );
The first statement inserts a record with a employee_name that is null, while the second statement inserts a record with an empty string as a employee_name.
Now, let's retrieve all rows with a employee_name that is an empty string value as follows:
select * from employee where employee_name = '';
When you run this statement, you'd expect to retrieve the row that you inserted above. But instead, this statement will not retrieve any records at all.
Now, try retrieving all employee_ids that contain a null value:
select * from employee where employee_name is null;
When you run this statement, you will retrieve both rows. This is because Oracle has now changed its rules so that empty strings behave as null values.
It is also important to note that the null value is unique in that you can not use the usual operands (=, <, >, etc) on a null value. Instead, you must use the IS NULL and IS NOT NULL conditions
SQL> create table employee
( employee_id number,
employee_name varchar2(100)); 2 3
Table created.
SQL> insert into employee (employee_id, employee_name ) values ( 10545, null );
1 row created.
SQL> commit;
Commit complete.
SQL> insert into employee (employee_id, employee_name ) values ( 10555, '' );
1 row created.
SQL> commit;
Commit complete.
SQL> select * from employee where employee_name = '';
no rows selected
SQL> select * from employee where employee_name is null;
EMPLOYEE_ID EMPLOYEE_NAME
--------------------------------------------------------------------------------
10545
10555
Performance tuning with DBMS package
Performance tuning with DBMS package:
Many of the performance bottleneck in oracle could be related to the bad execution plan of the query.
Oracle has provided dbms_xplan to understand the bad execution plan and come up with a different plan of your own.
Here is all the functions in dbms_xplan package which could be used to get different view of the execution path:
SQL> desc dbms_xplan
FUNCTION BUILD_PLAN_XML RETURNS XMLTYPE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
TABLE_NAME VARCHAR2 IN DEFAULT
STATEMENT_ID VARCHAR2 IN DEFAULT
PLAN_ID NUMBER IN DEFAULT
FORMAT VARCHAR2 IN DEFAULT
FILTER_PREDS VARCHAR2 IN DEFAULT
PLAN_TAG VARCHAR2 IN DEFAULT
REPORT_REF VARCHAR2 IN DEFAULT
FUNCTION DIFF_PLAN RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SQL_TEXT CLOB IN
OUTLINE CLOB IN
USER_NAME VARCHAR2 IN DEFAULT
FUNCTION DIFF_PLAN_AWR RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SQL_ID VARCHAR2 IN
PLAN_HASH_VALUE1 NUMBER IN
PLAN_HASH_VALUE2 NUMBER IN
FUNCTION DIFF_PLAN_CURSOR RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SQL_ID VARCHAR2 IN
CURSOR_CHILD_NUM1 NUMBER IN
CURSOR_CHILD_NUM2 NUMBER IN
FUNCTION DIFF_PLAN_OUTLINE RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SQL_TEXT CLOB IN
OUTLINE1 CLOB IN
OUTLINE2 CLOB IN
USER_NAME VARCHAR2 IN DEFAULT
FUNCTION DIFF_PLAN_SQL_BASELINE RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
BASELINE_PLAN_NAME1 VARCHAR2 IN
BASELINE_PLAN_NAME2 VARCHAR2 IN
FUNCTION DISPLAY RETURNS DBMS_XPLAN_TYPE_TABLE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
TABLE_NAME VARCHAR2 IN DEFAULT
STATEMENT_ID VARCHAR2 IN DEFAULT
FORMAT VARCHAR2 IN DEFAULT
FILTER_PREDS VARCHAR2 IN DEFAULT
FUNCTION DISPLAY_AWR RETURNS DBMS_XPLAN_TYPE_TABLE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SQL_ID VARCHAR2 IN
PLAN_HASH_VALUE NUMBER(38) IN DEFAULT
DB_ID NUMBER(38) IN DEFAULT
FORMAT VARCHAR2 IN DEFAULT
FUNCTION DISPLAY_CURSOR RETURNS DBMS_XPLAN_TYPE_TABLE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SQL_ID VARCHAR2 IN DEFAULT
CURSOR_CHILD_NO NUMBER(38) IN DEFAULT
FORMAT VARCHAR2 IN DEFAULT
FUNCTION DISPLAY_PLAN RETURNS CLOB
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
TABLE_NAME VARCHAR2 IN DEFAULT
STATEMENT_ID VARCHAR2 IN DEFAULT
FORMAT VARCHAR2 IN DEFAULT
FILTER_PREDS VARCHAR2 IN DEFAULT
TYPE VARCHAR2 IN DEFAULT
FUNCTION DISPLAY_SQLSET RETURNS DBMS_XPLAN_TYPE_TABLE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SQLSET_NAME VARCHAR2 IN
SQL_ID VARCHAR2 IN
PLAN_HASH_VALUE NUMBER(38) IN DEFAULT
FORMAT VARCHAR2 IN DEFAULT
SQLSET_OWNER VARCHAR2 IN DEFAULT
FUNCTION DISPLAY_SQL_PLAN_BASELINE RETURNS DBMS_XPLAN_TYPE_TABLE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SQL_HANDLE VARCHAR2 IN DEFAULT
PLAN_NAME VARCHAR2 IN DEFAULT
FORMAT VARCHAR2 IN DEFAULT
FUNCTION FORMAT_NUMBER RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NUM NUMBER IN
FUNCTION FORMAT_NUMBER2 RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NUM NUMBER IN
FUNCTION FORMAT_SIZE RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NUM NUMBER IN
FUNCTION FORMAT_SIZE2 RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NUM NUMBER IN
FUNCTION FORMAT_TIME_S RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NUM NUMBER IN
FUNCTION GET_PLANDIFF_REPORT_XML RETURNS XMLTYPE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
REPORT_REF VARCHAR2 IN DEFAULT
TID NUMBER IN
METHOD VARCHAR2 IN
FUNCTION PREPARE_PLAN_XML_QUERY RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
PLAN_QUERY VARCHAR2 IN
FUNCTION PREPARE_RECORDS RETURNS DBMS_XPLAN_TYPE_TABLE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
PLAN_CUR REF CURSOR IN
I_FORMAT_FLAGS BINARY_INTEGER IN
FUNCTION VALIDATE_FORMAT RETURNS BOOLEAN
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
HASPLANSTATS BOOLEAN IN
FORMAT VARCHAR2 IN
FORMAT_FLAGS BINARY_INTEGER OUT
Some of the way you can retireve the data from the various functions are:
select * from table(dbms_xplan.display);
SELECT * FROM TABLE(dbms_xplan.display_awr('&SQL_ID'))
Many of the performance bottleneck in oracle could be related to the bad execution plan of the query.
Oracle has provided dbms_xplan to understand the bad execution plan and come up with a different plan of your own.
Here is all the functions in dbms_xplan package which could be used to get different view of the execution path:
SQL> desc dbms_xplan
FUNCTION BUILD_PLAN_XML RETURNS XMLTYPE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
TABLE_NAME VARCHAR2 IN DEFAULT
STATEMENT_ID VARCHAR2 IN DEFAULT
PLAN_ID NUMBER IN DEFAULT
FORMAT VARCHAR2 IN DEFAULT
FILTER_PREDS VARCHAR2 IN DEFAULT
PLAN_TAG VARCHAR2 IN DEFAULT
REPORT_REF VARCHAR2 IN DEFAULT
FUNCTION DIFF_PLAN RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SQL_TEXT CLOB IN
OUTLINE CLOB IN
USER_NAME VARCHAR2 IN DEFAULT
FUNCTION DIFF_PLAN_AWR RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SQL_ID VARCHAR2 IN
PLAN_HASH_VALUE1 NUMBER IN
PLAN_HASH_VALUE2 NUMBER IN
FUNCTION DIFF_PLAN_CURSOR RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SQL_ID VARCHAR2 IN
CURSOR_CHILD_NUM1 NUMBER IN
CURSOR_CHILD_NUM2 NUMBER IN
FUNCTION DIFF_PLAN_OUTLINE RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SQL_TEXT CLOB IN
OUTLINE1 CLOB IN
OUTLINE2 CLOB IN
USER_NAME VARCHAR2 IN DEFAULT
FUNCTION DIFF_PLAN_SQL_BASELINE RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
BASELINE_PLAN_NAME1 VARCHAR2 IN
BASELINE_PLAN_NAME2 VARCHAR2 IN
FUNCTION DISPLAY RETURNS DBMS_XPLAN_TYPE_TABLE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
TABLE_NAME VARCHAR2 IN DEFAULT
STATEMENT_ID VARCHAR2 IN DEFAULT
FORMAT VARCHAR2 IN DEFAULT
FILTER_PREDS VARCHAR2 IN DEFAULT
FUNCTION DISPLAY_AWR RETURNS DBMS_XPLAN_TYPE_TABLE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SQL_ID VARCHAR2 IN
PLAN_HASH_VALUE NUMBER(38) IN DEFAULT
DB_ID NUMBER(38) IN DEFAULT
FORMAT VARCHAR2 IN DEFAULT
FUNCTION DISPLAY_CURSOR RETURNS DBMS_XPLAN_TYPE_TABLE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SQL_ID VARCHAR2 IN DEFAULT
CURSOR_CHILD_NO NUMBER(38) IN DEFAULT
FORMAT VARCHAR2 IN DEFAULT
FUNCTION DISPLAY_PLAN RETURNS CLOB
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
TABLE_NAME VARCHAR2 IN DEFAULT
STATEMENT_ID VARCHAR2 IN DEFAULT
FORMAT VARCHAR2 IN DEFAULT
FILTER_PREDS VARCHAR2 IN DEFAULT
TYPE VARCHAR2 IN DEFAULT
FUNCTION DISPLAY_SQLSET RETURNS DBMS_XPLAN_TYPE_TABLE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SQLSET_NAME VARCHAR2 IN
SQL_ID VARCHAR2 IN
PLAN_HASH_VALUE NUMBER(38) IN DEFAULT
FORMAT VARCHAR2 IN DEFAULT
SQLSET_OWNER VARCHAR2 IN DEFAULT
FUNCTION DISPLAY_SQL_PLAN_BASELINE RETURNS DBMS_XPLAN_TYPE_TABLE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SQL_HANDLE VARCHAR2 IN DEFAULT
PLAN_NAME VARCHAR2 IN DEFAULT
FORMAT VARCHAR2 IN DEFAULT
FUNCTION FORMAT_NUMBER RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NUM NUMBER IN
FUNCTION FORMAT_NUMBER2 RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NUM NUMBER IN
FUNCTION FORMAT_SIZE RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NUM NUMBER IN
FUNCTION FORMAT_SIZE2 RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NUM NUMBER IN
FUNCTION FORMAT_TIME_S RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NUM NUMBER IN
FUNCTION GET_PLANDIFF_REPORT_XML RETURNS XMLTYPE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
REPORT_REF VARCHAR2 IN DEFAULT
TID NUMBER IN
METHOD VARCHAR2 IN
FUNCTION PREPARE_PLAN_XML_QUERY RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
PLAN_QUERY VARCHAR2 IN
FUNCTION PREPARE_RECORDS RETURNS DBMS_XPLAN_TYPE_TABLE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
PLAN_CUR REF CURSOR IN
I_FORMAT_FLAGS BINARY_INTEGER IN
FUNCTION VALIDATE_FORMAT RETURNS BOOLEAN
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
HASPLANSTATS BOOLEAN IN
FORMAT VARCHAR2 IN
FORMAT_FLAGS BINARY_INTEGER OUT
Some of the way you can retireve the data from the various functions are:
select * from table(dbms_xplan.display);
SELECT * FROM TABLE(dbms_xplan.display_awr('&SQL_ID'))
Wednesday, December 2, 2015
How to Automate data Transfer using FTP
Often we require to copy data using FTP which we do manually but if we require data transfer in a regular running job we can using this method.
First let us know the useful FTP commands:
Common FTP Commands
===========================================
As in many situation we require to copy them in a scheduled Job or inside a code using automation.
Here is a code snippet which is written by my end to copy data using FTP from one server to the other.
This is helpful for DBA and Developer who often require to copy data in between multiple servers.
This uses username and password however can be removed if we are using ssh based authentication.
LOG_TEMP_FILE=/tmp/to_load.tmp
CRITICAL=NO
HOST=`hostname`
FTP_ERROR=0
EXP_ERR=0
FILES_HOOPED=0
FAILURE_CODE=1
GZIP=/usr/bin/gzip
FTP_PROG=/bin/ftp
FTP_ADDR=100.16.120.222
FTP_USER=autouser
FTP_PASSWD=4utxxcccv
FtpData() {
${FTP_PROG} -vin ${FTP_ADDR} << EOLINE > ${LOG_DIR}/ftp.log
user ${FTP_USER} ${FTP_PASSWD}
ascii
prom
hash
mget FILENAME_*${TIME_STAMP}.csv
mdelete to_*
quit
EOLINE
}
Just pass this code in your shell script with appropriate values and start transferring the data between multiple servers.
First let us know the useful FTP commands:
Common FTP Commands
| Common FTP Commands | ||
| mput | to copy multiple files from the local machine to the remote machine; | |
| you are prompted for a y/n answer before transferring each file | ||
| open | to open a connection with another computer | |
| pwd | to find out the pathname of the current directory on the remote machine | |
| quit | to exit the FTP environment (same as bye) | |
| rmdir | to to remove (delete) a directory in the current remote directory | |
| put | to copy one file from the local machine to the remote machine | |
| cd | to change directory on the remote machine | |
| close | to terminate a connection with another computer | |
| close brubeck | closes the current FTP connection with brubeck, | |
| but still leaves you within the FTP environment. | ||
| delete | to delete (remove) a file in the current remote directory (same as rm in UNIX) | |
| get | to copy one file from the remote machine to the local machine | |
| get ABC DEF | copies file ABC in the current remote directory to (or on top of) a file named DEF in your current local directory. | |
| get XYZ | copies file XYZ in the current remote directory to (or on top of) a file with the same name, XYZ, in your current local directory. | |
| help | to request a list of all available FTP commands | |
| lcd | to change directory on your local machine (same as UNIX cd) | |
| ls | to list the names of the files in the current remote directory | |
| mkdir | to make a new directory within the current remote directory | |
| mget | to copy multiple files from the remote machine to the local machine; | |
| you are prompted for a y/n answer before transferring each file | ||
| mget * | copies all the files in the current remote directory to your current local directory, using the same filenames. Notice the use of the wild card character, *. | |
===========================================
As in many situation we require to copy them in a scheduled Job or inside a code using automation.
Here is a code snippet which is written by my end to copy data using FTP from one server to the other.
This is helpful for DBA and Developer who often require to copy data in between multiple servers.
This uses username and password however can be removed if we are using ssh based authentication.
LOG_TEMP_FILE=/tmp/to_load.tmp
CRITICAL=NO
HOST=`hostname`
FTP_ERROR=0
EXP_ERR=0
FILES_HOOPED=0
FAILURE_CODE=1
GZIP=/usr/bin/gzip
FTP_PROG=/bin/ftp
FTP_ADDR=100.16.120.222
FTP_USER=autouser
FTP_PASSWD=4utxxcccv
FtpData() {
${FTP_PROG} -vin ${FTP_ADDR} << EOLINE > ${LOG_DIR}/ftp.log
user ${FTP_USER} ${FTP_PASSWD}
ascii
prom
hash
mget FILENAME_*${TIME_STAMP}.csv
mdelete to_*
quit
EOLINE
}
Just pass this code in your shell script with appropriate values and start transferring the data between multiple servers.
Monday, October 5, 2015
Finding Parent table to Child table Relationship for different tables in a SCHEMA
Developer and DBA often requires Parent table to Child table relationship for various tables in the Schema.
I found couple of them but not with my satisfaction.
So posting this for them which one can understand easily and for very beginner:
Connect to the schema user for which this relation is required and then run the below query.
SELECT B.TABLE_NAME AS PARENT_TABLE,
A.R_CONSTRAINT_NAME AS PARENT_TABLE_CONSTRAINT,
B.CONSTRAINT_TYPE AS PARENT_CONS_TYPE,
A.TABLE_NAME AS CHILD_TABLE,
A.CONSTRAINT_NAME AS CHILD_TABLE_CONSTRAINT_NAME,
A.CONSTRAINT_TYPE AS CHILD_CONSTRAINT_TYPE,
A.R_CONSTRAINT_NAME AS CHILD_TABLE_REF_TO_CONS from USER_CONSTRAINTS A
JOIN USER_CONSTRAINTS B on A.R_CONSTRAINT_NAME=B.CONSTRAINT_NAME AND B.CONSTRAINT_TYPE='P'
where A.R_CONSTRAINT_NAME IS NOT NULL AND
(A.TABLE_NAME in(select TABLE_NAME from USER_CONSTRAINTS where OWNER=&2)) AND (A.CONSTRAINT_TYPE='R') AND (A.TABLE_NAME=&1)
or
select A.TABLE_NAME as PARENT_TABLE,A.CONSTRAINT_TYPE as PARENT_CONS_TYPE,B.TABLE_NAME AS CHILD_TABLE_NAME,B.CONSTRAINT_TYPE as CHILD_TAB_CONS_TYPE,B.R_CONSTRAINT_NAME AS CHILD_TAB_CONS,
(select COLUMN_NAME from user_cons_columns uc where uc.CONSTRAINT_NAME= B.CONSTRAINT_NAME ) CHILD_TABLE_REF_COLUMN
from USER_CONSTRAINTS A join USER_CONSTRAINTS B on A.CONSTRAINT_NAME=B.R_CONSTRAINT_NAME where A.TABLE_NAME='&1' and B.CONSTRAINT_TYPE='R' AND A.CONSTRAINT_TYPE='P'
Just Pass on the schema name and table name within quotes and enjoy!!!!
I found couple of them but not with my satisfaction.
So posting this for them which one can understand easily and for very beginner:
Connect to the schema user for which this relation is required and then run the below query.
SELECT B.TABLE_NAME AS PARENT_TABLE,
A.R_CONSTRAINT_NAME AS PARENT_TABLE_CONSTRAINT,
B.CONSTRAINT_TYPE AS PARENT_CONS_TYPE,
A.TABLE_NAME AS CHILD_TABLE,
A.CONSTRAINT_NAME AS CHILD_TABLE_CONSTRAINT_NAME,
A.CONSTRAINT_TYPE AS CHILD_CONSTRAINT_TYPE,
A.R_CONSTRAINT_NAME AS CHILD_TABLE_REF_TO_CONS from USER_CONSTRAINTS A
JOIN USER_CONSTRAINTS B on A.R_CONSTRAINT_NAME=B.CONSTRAINT_NAME AND B.CONSTRAINT_TYPE='P'
where A.R_CONSTRAINT_NAME IS NOT NULL AND
(A.TABLE_NAME in(select TABLE_NAME from USER_CONSTRAINTS where OWNER=&2)) AND (A.CONSTRAINT_TYPE='R') AND (A.TABLE_NAME=&1)
or
select A.TABLE_NAME as PARENT_TABLE,A.CONSTRAINT_TYPE as PARENT_CONS_TYPE,B.TABLE_NAME AS CHILD_TABLE_NAME,B.CONSTRAINT_TYPE as CHILD_TAB_CONS_TYPE,B.R_CONSTRAINT_NAME AS CHILD_TAB_CONS,
(select COLUMN_NAME from user_cons_columns uc where uc.CONSTRAINT_NAME= B.CONSTRAINT_NAME ) CHILD_TABLE_REF_COLUMN
from USER_CONSTRAINTS A join USER_CONSTRAINTS B on A.CONSTRAINT_NAME=B.R_CONSTRAINT_NAME where A.TABLE_NAME='&1' and B.CONSTRAINT_TYPE='R' AND A.CONSTRAINT_TYPE='P'
Just Pass on the schema name and table name within quotes and enjoy!!!!
Subscribe to:
Posts (Atom)
