Working with Oracle in Unix and getting Execution Plan of a Query:
1) Login to Unix Machine where Oracle is installed.
2) You need to check whether Oracle is running (Listener service is running). To check this type below command:
oracle@companyName:~$ lsnrctl status (press ENTER)
This will give around 30-Lines output, at the end, it will show whether this running/ready or not.
However if it is not running, start it using below command:
oracle@companyName:~$ lsnrctl start (press ENTER)
3) Type below command in Unix (this will set Oracle_Home path and export it):
oracle@companyName:~$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 (press ENTER)
oracle@companyName:~$ export ORACLE_SID=orcl (press ENTER)
oracle@companyName:~$ export PATH=$ORACLE_HOME/bin:$ORACLE_HOME:$PATH:. (press ENTER)
4) Now you need to login to Database, so type below command:
oracle@companyName:~$ sqlplus (press ENTER)
Enter user-name: YOUR_ORACLE_USER_NAME (press ENTER)
Enter password: YOUR_ORACLE_PASSWORD (press ENTER)
SQL> (You are logged in now...)
/*
For Logging as SYS DBA, required only for DBA activities, don't run oftenly:
oracle@companyName:~$ sqlplus / as sysdba
SQL>
SQL> exit
*/
5) Set Linesize and Pagesize as Unix(Oracle) will show fragmented pages. Type below commands:
SQL> set linesize 300;
SQL> set pagesize 2000;
6) Now to print Execution Plan of a Query to know CPU Utilization, IO Cost, Bytes Read, Index Scan etc, use below queries:
SQL> SET autotrace ON;
SQL> SELECT /*+ index(TABLE_NAME INDEX_TIME_STAMP) */ * FROM TABLE_NAME WHERE
TIME_STAMP>=To_Date('10/01/2014 00:04:20','dd/mm/yyyy hh24:mi:ss') AND TIME_STAMP<=To_Date('15/01/2014 17:04:20','dd/mm/yyyy hh24:mi:ss')
ORDER BY CARD_NO ASC;
Your output will look like below:
ROW DATA 1........
ROW DATA 2........
ROW DATA 3........
3 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1246977483
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 18 | 4122 | 4 (25)| 00:00:01 |
| 1 | SORT ORDER BY | | 18 | 4122 | 4 (25)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| TABLE_NAME | 18 | 4122 | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | INDEX_TIME_STAMP | 18 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("TIME_STAMP">=TIMESTAMP' 2014-01-10 00:04:20' AND "TIME_STAMP"<=TIMESTAMP'
2014-01-15 17:04:20')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
4397 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
10 rows processed
SQL> exit
7) Now the same above query output, can be achieved in ORACLE related UI interfaces like below
(above all execution happened in UNIX Black color command line interface), execution below 2 queries in UI interface like SQL Tools:
explain plan for
SELECT /*+ index(TABLE_NAME INDEX_TIME_STAMP) */ * FROM TABLE_NAME WHERE
TIME_STAMP>=To_Date('10/01/2014 00:04:20','dd/mm/yyyy hh24:mi:ss') AND TIME_STAMP<=To_Date('15/01/2014 17:04:20','dd/mm/yyyy hh24:mi:ss')
ORDER BY CARD_NO ASC;
SELECT PLAN_ID,CPU_COST,IO_COST,TIME,BYTES,COST,DEPTH,OPTIMIZER,OBJECT_TYPE,OBJECT_NAME,OBJECT_OWNER,OPTIONS,OPERATION,
TIMESTAMP FROM PLAN_TABLE;
==========================================END==============================================
Wednesday, January 22, 2014
Execution Plan of a Query in Oracle
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment