Tuesday, December 23, 2008

Understanding Execution Plan Statistics

Below is the output of an execution plan statistics.
SQL> set autot trace statistics
SQL> select * from tab;

107 rows selected.


Statistics
----------------------------------------------------------
8 recursive calls
0 db block gets
898 consistent gets
0 physical reads
0 redo size
3459 bytes sent via SQL*Net to client
458 bytes received via SQL*Net from client
9 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
107 rows processed

You sometimes want to know what these fields indicates. Below is the details of these fields.

1)recursive calls: For processing of sql statements oracle database maintain internal tables. Sometimes for processing sql statements change is needed in the internal tables and sometimes not. When Oracle Database needs to make a change to these tables, it internally generates an internal SQL statement, which in turn generates a recursive call.


2)db block gets: Number of times a CURRENT block was requested.

3)consistent gets: Number of times a consistent read was requested for a block. This is called the logical reads indicates for processing of a query how many blocks needs to be accessed.

4)physical reads: Total number of data blocks read from disk. This number equals the value of "physical reads direct" + all reads into buffer cache.

5)redo size: For processing of a query total amount of redo generated in bytes.

6)bytes sent through SQL*Net to client: Total number of bytes sent to the client from the foreground processes.

7)bytes received through SQL*Net from client: Total number of bytes received from the client over Oracle Net.

8)SQL*Net round-trips to/from client: Total number of Oracle Net messages sent to and received from the client.
9)sorts (memory):Number of sort operations that were performed completely in memory and did not require any disk writes.

10)sorts (disk):Number of sort operations that required at least one disk write.

11)rows processed: Number of rows processed during the operation.
Posted by Arju at 11:00 PM 0 comments Labels:
Reactions: 

Controlling the Autotrace Report in sql*plus

In sql*plus with AUTOTRACE command you can see the execution plan of successful SQL DML (that is, SELECT, DELETE, UPDATE and INSERT) statements which is useful for monitoring and tuning the performance of these statements.

You can control the report by setting the AUTOTRACE system variable.

Following is the available AUTOTRACE settings.

1)SET AUTOTRACE OFF: No AUTOTRACE report is generated. This is the default. Result of a query will be displayed on the console as usual.

2)SET AUTOTRACE ON EXPLAIN: The AUTOTRACE report shows the optimizer execution path along with query output and note but does not show statistics.

3)SET AUTOTRACE ON STATISTICS: The AUTOTRACE report shows the SQL statement execution statistics along with query output but does not display execution plan.

4)SET AUTOTRACE ON: The AUTOTRACE report includes both the optimizer execution path and the SQL statement execution statistics along with query output.

5)SET AUTOTRACE TRACEONLY: Report displays both optimizer execution path as well as execution statistics but does not display query output. If STATISTICS is enabled, query data is still fetched, but not printed.
Posted by Arju at 10:25 PM 3 comments Labels:
Reactions: 

Sunday, December 21, 2008

Formatting SQL*Plus Reports Part 2

Computing Summary of a column
With combination of BREAK and COMPUTE command you can do several calculations on SQL reports using sql*plus.
Remember the COMPUTE command has no effect without a corresponding BREAK command.

Below is the lists of compute functions of Sql*plus and their effects.

1)SUM: Sum of the values in the column.

2)MINIMUM: Minimum value in the column.

3)MAXIMUM: Maximum value in the column.

4)AVG: Average of the values in the column.

5)STD: Standard deviation of the values in the column.

6)VARIANCE: Variance of the values in the column.

7)COUNT: Number of non-null values in the column.

8)NUMBER: Number of rows in the column.

Let's look at our data,

SQL> select dept_id,name,salary from emp;

  DEPT_ID NAME             SALARY
---------- ------------ ----------
       10 Arju              60000
       10 Ershad            22000
       10 Dulal             40000
       20 Riaz              35000
       20 Hasib             60000
       30 Raihan            25000

6 rows selected.

Now we wish to compute the total of SALARY by department. To do that use,

SQL> break on dept_id
SQL> compute sum of salary on dept_id
SQL> /

  DEPT_ID NAME             SALARY
---------- ------------ ----------
       10 Arju              60000
          Ershad            22000
          Dulal             40000
**********              ----------
sum                         122000
       20 Riaz              35000
          Hasib             60000
**********              ----------
sum                          95000
       30 Raihan            25000
**********              ----------
sum                          25000

6 rows selected.


Note that the word sum appears in every break. If you don't want to print sum word then do as,

SQL> COLUMN DUMMY NOPRINT;
SQL> COMPUTE SUM OF SALARY ON DUMMY;
SQL> BREAK ON DUMMY SKIP 1;
SQL> select dept_id DUMMY,dept_id,name,salary from emp;

  DEPT_ID NAME             SALARY
---------- ------------ ----------
       10 Arju              60000
       10 Ershad            22000
       10 Dulal             40000
                       ----------
                           122000

       20 Riaz              35000
       20 Hasib             60000
                       ----------
                            95000

       30 Raihan            25000
                       ----------
                            25000
6 rows selected.

To compute the salaries just at the end of the report,

SQL> COLUMN DUMMY NOPRINT;
SQL> COMPUTE SUM OF SALARY ON DUMMY;
SQL> BREAK ON DUMMY;
SQL> SELECT NULL DUMMY,DEPT_ID,NAME,SALARY FROM EMP;

  DEPT_ID NAME             SALARY
---------- ------------ ----------
       10 Arju              60000
       10 Ershad            22000
       10 Dulal             40000
       20 Riaz              35000
       20 Hasib             60000
       30 Raihan            25000
                       ----------
                           242000

6 rows selected.


To calculate grand total of salary and make it a level do,

SQL> BREAK ON REPORT
SQL> COMPUTE SUM LABEL TOTAL OF SALARY ON REPORT
SQL> select dept_id,name,salary from emp;

  DEPT_ID NAME             SALARY
---------- ------------ ----------
       10 Arju              60000
       10 Ershad            22000
       10 Dulal             40000
       20 Riaz              35000
       20 Hasib             60000
       30 Raihan            25000
                       ----------
TOTAL                       242000

6 rows selected.

To compute the both average and sum of salaries of a department do,

SQL> BREAK ON DEPT_ID
SQL> COMPUTE AVG SUM OF SALARY ON DEPT_ID
SQL> select dept_id,name,salary from emp where dept_id=10;

  DEPT_ID NAME             SALARY
---------- ------------ ----------
       10 Arju              60000
          Ershad            22000
          Dulal             40000
**********              ----------
avg                     40666.6667
sum                         122000


To see current compute settings,
SQL> compute
COMPUTE sum LABEL 'sum' OF SALARY ON DUMMY
COMPUTE sum LABEL 'TOTAL' OF SALARY ON REPORT
COMPUTE avg LABEL 'avg' sum LABEL 'sum' OF SALARY ON DEPT_ID
To remove all computes definitions,
SQL> clear compute
computes cleared
Posted by Arju at 9:28 PM 0 comments Labels:
Reactions: 
Subscribe to: Posts (Atom)