All that is comes from the mind: oracle

레이블이 oracle인 게시물을 표시합니다. 모든 게시물 표시

2014년 8월 13일 수요일

Improving Database Query Performance

referenced by : http://kb.tableausoftware.com/articles/knowledgebase/database-query-performance

For the DBA

If the above points are not sufficient to address your performance problems, it could be that the problems are at a deeper level than can be addressed by an end user. Tableau suggests that you engage your database administrator (DBA) and have them look at the following section for suggestions.

Seriously, know what you are asking

As pointed out earlier, knowing what you are asking the database to do is an important part of performance tuning. By running an audit or trace on the database, you can isolate the query that Tableau has passed to the query engine, and can check to see if it is what you expect. For example, does it have the expected GROUP BY and filter clauses; is it doing aggregations in the query as opposed to returning raw field values; etc.

As an example, in SQL Server start the Profiler tool to trace the queries running (filter by application name ="Tableau 6.1" or by your user name if the server is busy). This will allow you to see what the query was and how long it took to return.

Tune your indexes

Once you know the query being run, you can dump it into the execution plan estimator to see in more detail how the DBMS will process the query - this does not execute the query, but returns the estimated execution plan based on the statistics the server has collected.

Based on the information returned, you can determine whether additional indexes need to be created (i.e., the kind of query being asked by end users has changed and the current indexing model no longer reflects this accurately).

This is a deep topic but some basic principles are:

Make certain you have indexes on all columns that are part of table joins
Make certain you have indexes on any column used in a filter
Explicitly define primary keys
Explicitly define foreign key relationships
For large data sets, use table partitioning
Define columns as NOT NULL where possible

Use statistics

Databases engines collect statistical information about indexes and column data stored in the database. These statistics are used by the query optimizer to choose the most efficient plan for retrieving or updating data. Good statistics allow the optimizer to accurately assess the cost of different query plans, and choose a high-quality plan. For example, a common misunderstanding is that if you have indexes, databases will use those indexes to retrieve records in your query. Not necessarily. If you create, let's say, an index to a column City and <90% of the values are ‘Vancouver', the DBMS will most likely opt for a table scan instead of using the index if it knows these stats.

Ensuring that database statistics are being collected and used will help the database engine generate better queries, resulting in faster query performance.

Optimize the data model

Finally, the data model being queried can have a significant impact on the performance of queries. Ensuring that the structure of the data is aligned to the kinds of analysis the end users will do is critical for good query performance. If you find you are needing to design excessive joins, it could be an indication that the data model is not suited to the task at hand.

An example is where it could be beneficial to create summary tables if most of your queries only need aggregated data - not base level details records.

Again, this is a big topic beyond the scope of this article, but DBMS vendors have many whitepapers that describe their recommended best practices for data warehouse and data mart design.

Oracle Big Data SQL

linked by : https://blogs.oracle.com/datawarehousing/entry/oracle_big_data_sql_one

Oracle Big Data SQL: One Fast Query, All Your Data

By dan.mcclary on Jul 15, 2014

Introduction

Today we're pleased to announce Big Data SQL, Oracle's unique approach to providing unified query over data in Oracle Database, Hadoop, and select NoSQL datastores. Big Data SQL has been in development for quite a while now, and will be generally available in a few months. With today's announcement of the product, I wanted to take a chance to explain what we think is important and valuable about Big Data SQL.

SQL on Hadoop

As anyone paying attention to the Hadoop ecosystem knows, SQL-on-Hadoop has seen a proliferation of solutions in the last 18 months, and just as large a proliferation of press. From good, ol' Apache Hive to Cloudera Impala and SparkSQL, these days you can have SQL-on-Hadoop any way you like it. It does, however, prompt the question: Why SQL?

There's an argument to be made for SQL simply being a form of skill reuse. If people and tools already speak SQL, then give the people what they know. In truth, that argument falls flat when one considers the sheer pace at which the Hadoop ecosystem evolves. If there were a better language for querying Big Data, the community would have turned it up by now.

I think the reality is that the SQL language endures because it is uniquely suited to querying datasets. Consider, SQL is a declarative language for operating on relations in data. It's a domain-specific language where the domain is datasets. In and of itself, that's powerful: having language elements like FROM, WHERE and GROUP BY make reasoning about datasets simpler. It's set theory set into a programming language.

It goes beyond just the language itself. SQL is declarative, which means I only have to reason about the shape of the result I want, not the data access mechanisms to get there, the join algorithms to apply, how to serialize partial aggregations, and so on. SQL lets us think about answers, which lets us get more done.

SQL on Hadoop, then, is somewhat obvious. As data gets bigger, we would prefer to only have to reason about answers.

SQL On More Than Hadoop

For all the obvious goodness of SQL on Hadoop, there's a somewhat obvious drawback. Specifically, data rarely lives in a single place. Indeed, if Big Data is causing a proliferation of new ways to store and process data, then there are likely more places to store data then every before. If SQL on Hadoop is separate from SQL on a DBMS, I run the risk of constructing every IT architect's least favorite solution: the stovepipe.

If we want to avoid stovepipes, what we really need is the ability to run SQL queries that work seamlessly across multiple datastores. Ideally, in a Big Data world, SQL should "play data where it lies," using the declarative power of the language to provide answers from all data.

This is why we think Oracle Big Data SQL is obvious too.

It's just a little more complicated than SQL on any one thing. To pull it off, we have to do a few things:

Maintain the valuable characteristics of the system storing the data
Unify metadata to understand how to execute queries
Optimize execution to take advantage of the systems storing the data

For the case of a relational database, we might say that the valuable storage characteristics include things like: straight-through processing, change-data logging, fine-grained access controls, and a host of other things.

For Hadoop, I believe that the two most valuable storage characteristics are scalability and schema-on-read. Cost-effective scalability is one of the first things that people look to HDFS for, so any solution that does SQL over a relational database and Hadoop has to understand how HDFS scales and distributes data. Schema-on-read is at least equally important if not more. As Daniel Abadi recently wrote, the flexibility of schema-on-read is gives Hadoop tremendous power: dump data into HDFS, and access it without having to convert it to a specific format. So, then, any solution that does SQL over a relational database and Hadoop is going to have to respect the schemas of the database, but be able to really apply schema-on-read principals to data stored in Hadoop.

Oracle Big Data SQL maintains all of these valuable characteristics, and it does it specifically through the approaches taken for unifying metadata and optimizing performance.

Unifying Metadata

To unify metadata for planning and executing SQL queries, we require a catalog of some sort. What tables do I have? What are their column names and types? Are there special options defined on the tables? Who can see which data in these tables?

Given the richness of the Oracle data dictionary, Oracle Big Data SQL unifies metadata using Oracle Database: specifically as external tables. Tables in Hadoop or NoSQL databases are defined as external tables in Oracle. This makes sense, given that the data is external to the DBMS.

Wait a minute, don't lots of vendors have external tables over HDFS, including Oracle?

Yes, but Big Data SQL provides as an external table is uniquely designed to preserve the valuable characteristics of Hadoop. The difficulty with most external tables is that they are designed to work on flat, fixed-definition files, not distributed data which is intended to be consumed through dynamically invoked readers. That causes both poor parallelism and removes the value of schema-on-read.

The external tables Big Data SQL presents are different. They leverage the Hive metastore or user definitions to determine both parallelism and read semantics. That means that if a file in HFDS is 100 blocks, Oracle database understands there are 100 units which can be read in parallel. If the data was stored in a SequenceFile using a binary SerDe, or as Parquet data, or as Avro, that is how the data is read. Big Data SQL uses the exact same InputFormat, RecordReader, and SerDes defined in the Hive metastore to read the data from HDFS.

Once that data is read, we need only to join it with internal data and provide SQL on Hadoop and a relational database.

Optimizing Performance

Being able to join data from Hadoop with Oracle Database is a feat in and of itself. However, given the size of data in Hadoop, it ends up being a lot of data to shift around. In order to optimize performance, we must take advantage of what each system can do.

In the days before data was officially Big, Oracle faced a similar challenge when optimizing Exadata, our then-new database appliance. Since many databases are connected to shared storage, at some point database scan operations can become bound on the network between the storage and the database, or on the shared storage system itself. The solution the group proposed was remarkably similar to much of the ethos that infuses MapReduce and Apache Spark: move the work to the data and minimize data movement.

The effect is striking: minimizing data movement by an order of magnitude often yields performance increases of an order of magnitude.

Big Data SQL takes a play from both the Exadata and Hadoop books to optimize performance: it moves work to the data and radically minimizes data movement. It does this via something we call Smart Scan for Hadoop.

Moving the work to the data is straightforward. Smart Scan for Hadoop introduces a new service into to the Hadoop ecosystem, which is co-resident with HDFS DataNodes and YARN NodeManagers. Queries from the new external tables are sent to these services to ensure that reads are direct path and data-local. Reading close to the data speeds up I/O, but minimizing data movement requires that Smart Scan do some things that are, well, smart.

Smart Scan for Hadoop

Consider this: most queries don't select all columns, and most queries have some kind of predicate on them. Moving unneeded columns and rows is, by definition, excess data movement and impeding performance. Smart Scan for Hadoop gets rid of this excess movement, which in turn radically improves performance.

For example, suppose we were querying a 100 of TB set of JSON data stored in HDFS, but only cared about a few fields -- email and status -- and only wanted results from the state of Texas.

Once data is read from a DataNode, Smart Scan for Hadoop goes beyond just reading. It applies parsing functions to our JSON data, discards any documents which do not contain 'TX' for the state attribute. Then, for those documents which do match, it projects out only the email and status attributes to merge with the rest of the data. Rather than moving every field, for every document, we're able to cut down 100s of TB to 100s of GB.

The approach we take to optimizing performance with Big Data SQL makes Big Data much slimmer.

Summary

So, there you have it: fast queries which join data in Oracle Database with data in Hadoop while preserving the makes each system a valuable part of overall information architectures. Big Data SQL unifies metadata, such that data sources can be queried with the best possible parallelism and the correct read semantics. Big Data SQL optimizes performance using approaches inspired by Exadata: filtering out irrelevant data before it can become a bottleneck.

It's SQL that plays data where it lies, letting you place data where you think it belongs.

2014년 8월 12일 화요일

Before, after, each row and table level triggers [Oracle]

reference by : http://www.adp-gmbh.ch/ora/sql/trigger/before_after_each_row.html

The goal of this page is to demonstrate the most important differences between before and after triggers as well as the differences between for each row and table level triggers.

Before / for each row trigger

A before trigger is called before because it fires before the new values (:new.field_name) are stored in the table. That means that the new value can be changed in the trigger.

create table t_update_before_each_row (
  txt varchar2(10)
);

create table log (
  txt varchar2(20)
);


create trigger update_before_each_row 
  before update on t_update_before_each_row
  for each row
begin

  :new.txt := upper(:new.txt);

  insert into log values ('old: ' || :old.txt);
  insert into log values ('new: ' || :new.txt);

end update_before_each_row;
/

insert into t_update_before_each_row values('one');
insert into t_update_before_each_row values('two');
insert into t_update_before_each_row values('three');
insert into t_update_before_each_row values('four');

Updating (that is: concatenating the value with itself) the rows containing two and three:

update t_update_before_each_row set txt = txt || txt 
 where substr(txt,1,1) = 't';

select * from t_update_before_each_row;

As can be seen by the output of the select statement, the trigger changed the values of the new values; they're in uppercase now:

one
TWOTWO
THREETHREE
four

The log displays the old and new values:

select * from log;

old: two
new: TWOTWO
old: three
new: THREETHREE

Cleaning up:

drop table t_update_before_each_row;
drop table log;

After / for each row trigger

In contrast to a before trigger, an after trigger does not allow to change :new.field_name because the value is, when the trigger fires, already written to the table.

If one tries to assign a value to :new.field_name, Oracle throws an ORA-04084: cannot change NEW values for this trigger type.

create table t_update_after_each_row (
  txt varchar2(10)
);

create table log (
  txt varchar2(20)
);


create trigger update_after_each_row 
  after update on t_update_after_each_row
  for each row
begin

  -- :new.txt := upper(:old.txt); -- ORA-04084: cannot change NEW values for this trigger type

  insert into log values ('old: ' || :old.txt);
  insert into log values ('new: ' || :new.txt);

end update_after_each_row;
/

insert into t_update_after_each_row values('one');
insert into t_update_after_each_row values('two');
insert into t_update_after_each_row values('three');
insert into t_update_after_each_row values('four');

update t_update_after_each_row set txt = txt || txt 
 where substr(txt,1,1) = 't';

select * from t_update_after_each_row;

one
twotwo
threethree
four

select * from log;

As the log table shows, it is possible to use :new and :oldalthough it's not possible to assign something to :new.

old: two
new: twotwo
old: three
new: threethree

Cleaning up:

drop table t_update_after_each_row;
drop table log;

Table level trigger

A table level trigger is a trigger that doesn't fire for each row to be changed. Accordingly, it lacks the for each row. Consequently, both, the :new and :old are not permitted in the trigger's PL/SQL block, otherwise, an ORA-04082: NEW or OLD references not allowed in table level triggers is thrown.

create table t_update_before (
  txt varchar2(10)
);

create table log (
  txt varchar2(20)
);


create trigger update_before 
  before update on t_update_before
begin

  -- :new.txt := upper(:old.txt); -- ORA-04082

  insert into log values ('update trigger');

end update_before;
/

insert into t_update_before values('one');
insert into t_update_before values('two');
insert into t_update_before values('three');
insert into t_update_before values('four');

update t_update_before set txt = txt || txt 
 where substr(txt,1,1) = 't';

select * from t_update_before;

one
twotwo
threethree
four

select * from log;

Although two rows were updated, only one record is found in the log table:

select * from log;

update trigger

An update statement that doesn't update any row:

update t_update_before set txt = txt || txt 
 where txt = 'no update';

Still, the trigger fires...

select * from log;

... which results in another row found in the log table:

update trigger
update trigger

Cleaning up:

drop table t_update_before;
drop table log;

Order of execution

Oracle allows to create multiple triggers on the same table. The order of the execution of these triggers is undeterministic (or random, if you want this word) except that all before triggers fire before the after triggers.

2014년 8월 11일 월요일

oracle sql join

reference by : http://www.oreillynet.com/pub/a/network/2002/04/23/fulljoin.html

Among several new features, Oracle9i has introduced the ANSI standard join syntax. The new join syntax is not only SQL92 compliant, but it is elegant and makes the outer join syntax more intuitive. The biggest advantage of the new join syntax is its support for full outer joins, which is not directly feasible in Oracle8i. In this article I discuss the full outer join feature of Oracle9i, and provide some examples.

An outer join extends the result of an inner join by including rows from one table (say Table A) that don't have corresponding rows in another table (say Table B). An important thing to note here is that the outer join operation will not include the rows from Table B that don't have corresponding rows in Table A. In other words, an outer join is unidirectional. But there are situations when you may want a bidirectional outer join, i.e., you want to include all the rows from A and B:

rows from the result of the inner join
rows from A that don't have corresponding rows in B
rows from B that don't have corresponding rows in A

Let's look at an example to understand this further. Consider the following two tables:


SQL> desc part
 Name                                      Null?    Type
 ----------------------------------------- -------- -----------------
 PART_ID                                   NOT NULL VARCHAR2(4)
 SUPPLIER_ID                                        VARCHAR2(4)

SQL> select * from part;

PART SUPP
---- ----
P1   S1
P2   S2
P3
P4

SQL> desc supplier
 Name                                      Null?    Type
 ----------------------------------------- -------- -----------------
 SUPPLIER_ID                               NOT NULL VARCHAR2(4)
 SUPPLIER_NAME                             NOT NULL VARCHAR2(20)

SQL> select * from supplier;

SUPP SUPPLIER_NAME
---- --------------------
S1   Supplier#1
S2   Supplier#2
S3   Supplier#3

Notice above that there are two parts (P3 and P4) that don't have a supplier yet. Also, there is a supplier (S3) who doesn't yet supply any part.

Result of Inner Join

Let's say our company is consolidating all of the parts and the suppliers for managing the inventory of its parts properly, and I was asked to generate a report of all the parts and their corresponding suppliers. So I performed a join of these two tables, which gave the following result:

SQL> select p.part_id, s.supplier_name
  2  from part p, supplier s
  3  where p.supplier_id = s.supplier_id;

PART SUPPLIER_NAME
---- --------------------
P1   Supplier#1
P2   Supplier#2

The join shown above is an inner join, which results in just the rows that have corresponding rows in both tables. Therefore, the parts that don't have a supplier, or the suppliers that don't supply any part are excluded from the result set.

Result of Outer Join

If we want all parts to be listed in the result set, irrespective of whether they are supplied by any supplier or not, then we need to perform an outer join.

SQL> select p.part_id, s.supplier_name
  2  from part p, supplier s
  3  where p.supplier_id = s.supplier_id (+);

PART SUPPLIER_NAME
---- --------------------
P1   Supplier#1
P2   Supplier#2
P3
P4

The outer join above lists all of the parts. For the parts that don't have a corresponding supplier, null values are displayed for the SUPPLIER_NAME column. However, not all the suppliers are displayed. Since supplier S3 doesn't supply any parts, it gets excluded from the result set of the above outer join. If we want all the suppliers listed in the result set, irrespective of whether they supply any part or not, we need to perform an outer join like the following:

SQL> select p.part_id, s.supplier_name
  2  from part p, supplier s
  3  where p.supplier_id (+) = s.supplier_id;

PART SUPPLIER_NAME
---- --------------------
P1   Supplier#1
P2   Supplier#2
     Supplier#3

The outer join above lists all the suppliers. For the suppliers that don't supply any part, null values are displayed for the PART_ID column. However, not all the parts are displayed. Since parts P3 and P4 are not supplied by any suppliers, they get excluded from the result set of the above outer join.

Full Outer Join

If we want all the parts (irrespective of whether they are supplied by any supplier or not), and all the suppliers (irrespective of whether they supply any part or not) listed in the same result set, we have a problem. That's because the traditional outer join (using the '+' operator) is unidirectional, and you can't put (+) on both sides in the join condition. The following will result in an error:

SQL> select p.part_id, s.supplier_name
  2  from part p, supplier s
  3  where p.supplier_id (+) = s.supplier_id (+);
where p.supplier_id (+) = s.supplier_id (+)
                        *
ERROR at line 3:
ORA-01468: a predicate may reference only one outer-joined table

Up through Oracle8i, Oracle programmers have used a workaround to circumvent this limitation. The workaround involves two outer join queries combined by a UNION operator, as in the following example:

SQL> select p.part_id, s.supplier_name
  2  from part p, supplier s
  3  where p.supplier_id = s.supplier_id (+)
  4  union
  5  select p.part_id, s.supplier_name
  6  from part p, supplier s
  7  where p.supplier_id (+) = s.supplier_id;

PART SUPPLIER_NAME
---- --------------------
P1   Supplier#1
P2   Supplier#2
P3
P4
     Supplier#3

Notice the use of the UNION set operator to combine the results of two separate outer join queries, to list all the parts and all the suppliers in the same result set.

New Join Syntax

Oracle9i introduced the ANSI compliant join syntax. This new join syntax uses the new keywords inner join, left outer join, right outer join, and full outer join, instead of the (+) operator.

The inner join using this new join syntax will look like:

SQL> select p.part_id, s.supplier_name
  2  from part p inner join supplier s
  3  on p.supplier_id = s.supplier_id;

PART SUPPLIER_NAME
---- --------------------
P1   Supplier#1
P2   Supplier#2

Remember, if we want to retain all the parts in the result set, irrespective of whether any supplier supplies them or not, then we need to perform an outer join. The corresponding outer join query using the new syntax will be:

SQL> select p.part_id, s.supplier_name
  2  from part p left outer join supplier s
  3  on p.supplier_id = s.supplier_id;

PART SUPPLIER_NAME
---- --------------------
P1   Supplier#1
P2   Supplier#2
P4
P3

This is called a "left outer join" because all the rows from the table on the left (PART) are retained in the result set. If we want to retain all the suppliers in the result set, irrespective of whether they supply any part or not, then we need to perform a "right outer join". That would look like:

SQL> select p.part_id, s.supplier_name
  2  from part p right outer join supplier s
  3  on p.supplier_id = s.supplier_id;

PART SUPPLIER_NAME
---- --------------------
P1   Supplier#1
P2   Supplier#2
     Supplier#3

However, the biggest advantage of the new join syntax is its support for full outer joins. Introduction of the ANSI standard join syntax in Oracle9i greatly simplifies the full outer join query. We are no longer limited by unidirectional outer join, and no longer need to use the UNION operation to perform the full outer join. Oracle9iintroduced the full outer join operation to carry out such operations, as in the following example:

SQL> select p.part_id, s.supplier_name
  2  from part p full outer join supplier s
  3  on p.supplier_id = s.supplier_id;

PART SUPPLIER_NAME
---- --------------------
P1   Supplier#1
P2   Supplier#2
P4
P3
     Supplier#3

The above SQL statement is not only smaller in size, it is much more elegant and intuitive as well. This ANSI join syntax is also more efficient than the UNION method of achieving a full outer join.

2014년 1월 27일 월요일

oracle index usage check

http://docs.oracle.com/cd/B10500_01/server.920/a96533/autotrac.htm

Autotrace Setting	Result
`SET AUTOTRACE OFF`	No `AUTOTRACE` report is generated. This is the default.
`SET AUTOTRACE ON EXPLAIN`	The `AUTOTRACE` report shows only the optimizer execution path.
`SET AUTOTRACE ON STATISTICS`	The `AUTOTRACE` report shows only the SQL statement execution statistics.
`SET AUTOTRACE ON`	The `AUTOTRACE` report includes both the optimizer execution path and the SQL statement execution statistics.
`SET AUTOTRACE TRACEONLY`	Similar to `SET` `AUTOTRACE` `ON`, but suppresses the printing of the user's query output, if any. If STATISTICS is enabled, query data is still fetched, but not printed.

Tracing Statements for Performance Statistics and Query Execution Path

If the SQL buffer contains the following statement:

SELECT E.LAST_NAME, E.SALARY, J.JOB_TITLE
  FROM EMPLOYEES E, JOBS J
    WHERE E.JOB_ID=J.JOB_ID AND E.SALARY>12000;

The statement can be automatically traced when it is run with the following:

SET AUTOTRACE ON
/

The output is similar to the following:

LAST_NAME                     SALARY JOB_TITLE
------------------------- ---------- -----------------------------------
King                           24000 President
Kochhar                        17000 Administration Vice President
De Haan                        17000 Administration Vice President
Russell                        14000 Sales Manager
Partners                       13500 Sales Manager
Hartstein                      13000 Marketing Manager

6 rows selected.

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'EMPLOYEES'
   2    1     NESTED LOOPS
   3    2       TABLE ACCESS (FULL) OF 'JOBS'
   4    2       INDEX (RANGE SCAN) OF 'EMP_JOB_IX' (NON-UNIQUE)

Statistics
----------------------------------------------------------
          0  recursive calls
          2  db block gets
         34  consistent gets
          0  physical reads
          0  redo size
        848  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          6  rows processed


Tracing Statements Without Displaying Query Data

To trace the same statement without displaying the query data, enter the following:
SET AUTOTRACE TRACEONLY



This option is useful when you are tuning a large query, but do not want to see the query output.

Tracing Statements Using a Database Link

To trace a statement using a database link, enter:
SET AUTOTRACE TRACEONLY EXPLAIN
SELECT * FROM EMPLOYEES@MY_LINK;

Execution Plan
-----------------------------------------------------------
0      SELECT STATEMENT (REMOTE) Optimizer=CHOOSE
1    0   TABLE ACCESS (FULL) OF 'EMPLOYEES'  MY_LINK.DB_DOMAIN




Monitoring Disk Reads and Buffer Gets

To monitor disk reads and buffer gets, execute the following command:
SET AUTOTRACE ON TRACEONLY STATISTICS



Example 11-4 shows typical results.

Example 11-4 Monitoring Disk Reads and Buffer Gets
Statistics
----------------------------------------------------------
        70 recursive calls
         0 db block gets
       591 consistent gets
       404 physical reads
         0 redo size
       315 bytes sent via SQL*Net to client
       850 bytes received via SQL*Net from client
         3 SQL*Net roundtrips to/from client
         3 sorts (memory)
         0 sorts (disk)
         0 rows processed



If consistent gets or physical reads is high relative to the amount of data returned, it indicates that the query is expensive and needs to be reviewed for optimization. For example, if you are expecting less than 1,000 rows back and consistent gets is 1,000,000 and physical reads is 10,000, further optimization is needed.





set autotrace off
set autotrace on
set autotrace traceonly

set autotrace on explain
set autotrace on statistics
set autotrace on explain statistics

set autotrace traceonly explain
set autotrace traceonly statistics
set autotrace traceonly explain statistics

set autotrace off explain
set autotrace off statistics
set autotrace off explain statistics



Setting autotrace allows to display some statistics and/or anquery execution plan for DML statements.

set autotrace on: Shows the execution plan as well as statistics of the statement.
set autotrace on explain: Displays the execution plan only.
set autotrace on statistics: Displays the statistics only.
set autotrace traceonly: Displays the execution plan and the statistics (as set autotrace on does), but doesn't print a query's result.
set autotrace off: Disables all autotrace

SQL) conn id/pw
SQL) set autotrace on explain;
SQL) set linesize 300
SQL) set pagesize 1000

SQL) select count(*) FROM table

Execution Plan
----------------------------------------------------------

------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name                   | Rows  | Bytes | Cost (%CPU)| Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                        |     1 |   135 |   390   (2)|       |       |
|   1 |  SORT AGGREGATE                          |                        |     1 |   135 |            |       |       |
|   2 |   NESTED LOOPS OUTER                     |                        |    58 |  7830 |   390   (2)|       |       |
|   3 |    NESTED LOOPS OUTER                    |                        |    58 |  6438 |   352   (2)|       |       |
|   4 |     NESTED LOOPS OUTER                   |                        |    58 |  5974 |   293   (2)|       |       |
|*  5 |      HASH JOIN OUTER                     |                        |    58 |  5510 |   235   (2)|       |       |
|*  6 |       HASH JOIN OUTER                    |                        |    58 |  5220 |   218   (1)|       |       |
|   7 |        TABLE ACCESS BY GLOBAL INDEX ROWID| TABLE                  |    58 |  3654 |   213   (1)|    74 |    74 |
|*  8 |         INDEX RANGE SCAN                 | TABLE_IX_04            |    58 |       |     5   (0)|       |       |

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

5 - access("MAIN"."WHERE"="CODE"."CODE"(+))

SQL> SELECT A.uniqueness, b.* FROM ALL_INDEXES a, ALL_IND_COLUMNS b WHERE a.index_name = b.index_name AND a.table_name=upper('TABLE');

UNIQUENES INDEX_OWNER                    INDEX_NAME                     TABLE_OWNER                    TABLE_NAME
--------- ------------------------------ ------------------------------ ------------------------------ ------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
COLUMN_POSITION COLUMN_LENGTH CHAR_LENGTH DESC
--------------- ------------- ----------- ----
NONUNIQUE PID                            TABLE_IX_01               PID                            TABLE
COLUMN
              1             7           7 ASC

NONUNIQUE PID                            TABLE_IX_01               PID                            TABLE
COLUMN
2           200         200 ASC

select * from user_indexes a, user_ind_columns b where a.index_name = b.index_name and a.table_name = upper('TABLE');
SELECT A.uniqueness, b.* FROM ALL_INDEXES a, ALL_IND_COLUMNS b WHERE a.index_name = b.index_name AND a.table_name=upper('TABLE');

set autotrace on:	Shows the execution plan as well as statistics of the statement.
set autotrace on explain:	Displays the execution plan only.
set autotrace on statistics:	Displays the statistics only.
set autotrace traceonly:	Displays the execution plan and the statistics (as set autotrace on does), but doesn't print a query's result.
set autotrace off:	Disables all autotrace

전체 페이지뷰

2014년 8월 13일 수요일

Improving Database Query Performance

referenced by : http://kb.tableausoftware.com/articles/knowledgebase/database-query-performance

For the DBA

Seriously, know what you are asking

Tune your indexes

Use statistics

Optimize the data model

Oracle Big Data SQL

Oracle Big Data SQL: One Fast Query, All Your Data

By dan.mcclary on Jul 15, 2014

Introduction

SQL on Hadoop

SQL On More Than Hadoop

Unifying Metadata

Optimizing Performance

Smart Scan for Hadoop

Summary

2014년 8월 12일 화요일

Before, after, each row and table level triggers [Oracle]

Before / for each row trigger

After / for each row trigger

Table level trigger

Order of execution

2014년 8월 11일 월요일

oracle sql join

Result of Inner Join

Result of Outer Join

Full Outer Join

New Join Syntax

2014년 1월 27일 월요일

oracle index usage check

Tracing Statements for Performance Statistics and Query Execution Path

Tracing Statements Without Displaying Query Data

Tracing Statements Using a Database Link

Monitoring Disk Reads and Buffer Gets

Example 11-4 Monitoring Disk Reads and Buffer Gets