Hi reader,

looked during a data load via SQL*LOADER on the top Activity page of the Oracle Db Console and was fascinated by these tree very nice painted bands of CPU,COMMIT and User I/O . Some kind of beauty ;-)

 

HTH :-)
Karl Reitschuster

 

 

Hi Reader,

after installing Oracle 10.2 64bit on Suse Linux 10.1 I started the dbconsole. A page i visited soon was Tablespace page. Usually on our Windows Server 2003 32Bit system, with the same Oracle version, the Tablespace query lasts for about 20 seconds! On the Linux System with the same number of Application tablespaces the Query response time was < 0.5 seconds. I thought this could not be the result being on just another operating system or driving the database with 64bit.

After reviewing the explain plan i detected the access to the Table RECYCLEBIN$; As by default Tables dropped are moved logically to the RECYCLEBIN$; The segments remain until the place in the tablespace exceeds; And now I recognized a big difference; On the slow system the RECYCLEBIN$ had about 2,600 rows on the fast system only 16 rows.

I started to purge all these tables of the different users and believe me or not the Query got faster and faster; Now with 12 rows I have the same response time for both Systems Linux 64 bit / Windows 2003 32 Bit;

So keep your Oracle 10.2 RECYCLEBIN$ as small as possible;

HTH Karl Reitschuster

The Query :

SELECT Dtp.Tablespace_Name "TABLESPACE_NAME",
       Dtp.Status "STATUS",
       Dtp.Contents "CONTENTS",
       Dtp.Extent_Management "EXTENT MANAGEMENT",
       Dtp.Allocation_Type "ALLOCATION TYPE",
       Dtp.Logging "LOGGING",
       Nvl(Ts.Bytes,
           0) "SIZE (M)",
       Dtp.Initial_Extent "INITIAL EXT SIZE (K)",
       Dtp.Next_Extent "Next Extent",
       Dtp.Pct_Increase "INCREMENT PCT",
       Dtp.Max_Extents "MAX EXTENTS",
       Nvl(Ts.Bytes - Nvl(f.Bytes,
                          0),
           0) "USED BYTES"
 FROM Sys.Dba_Tablespaces Dtp,
       (SELECT Tablespace_Name,
               SUM(Bytes) Bytes
          FROM Dba_Data_Files
         GROUP BY Tablespace_Name) Ts,
       (SELECT Tablespace_Name,
               SUM(Bytes) Bytes
          FROM Dba_Free_Space
         GROUP BY Tablespace_Name) f
 WHERE Dtp.Tablespace_Name = Ts.Tablespace_Name(+)
   AND Dtp.Tablespace_Name = f.Tablespace_Name(+)
   AND NOT
        (Dtp.Extent_Management LIKE ‘LOCAL’ AND Dtp.Contents LIKE ‘TEMPORARY’)
UNION ALL
SELECT Dtp.Tablespace_Name "TABLESPACE_NAME",
       Dtp.Status "STATUS",
       Dtp.Contents "CONTENTS",
       Dtp.Extent_Management "EXTENT MANAGEMENT",
       Dtp.Allocation_Type "ALLOCATION TYPE",
       Dtp.Logging "LOGGING",
       Nvl(a.Bytes,
           0) "SIZE (M)",
       Dtp.Initial_Extent "INITIAL EXT SIZE (K)",
       Dtp.Next_Extent "Next Extent",
       Dtp.Pct_Increase "INCREMENT PCT",
       Dtp.Max_Extents "MAX EXTENTS",
       Nvl(t.Bytes,
           0) "USED BYTES"
 FROM Sys.Dba_Tablespaces Dtp,
       (SELECT Tablespace_Name,
               SUM(Bytes) Bytes
          FROM Dba_Temp_Files
         GROUP BY Tablespace_Name) a,
       (SELECT Ss.Tablespace_Name,
               SUM((Ss.Used_Blocks * Ts.Blocksize)) Bytes
          FROM Gv$sort_Segment Ss,
               Sys.Ts$         Ts
         WHERE Ss.Tablespace_Name = Ts.NAME
         GROUP BY Ss.Tablespace_Name) t
 WHERE Dtp.Tablespace_Name = a.Tablespace_Name(+)
   AND Dtp.Tablespace_Name = t.Tablespace_Name(+)
   AND Dtp.Extent_Management LIKE ‘LOCAL’
   AND Dtp.Contents LIKE ‘TEMPORARY’

 

Hi reader,

with the new oracle scheduler it’s possible to start jobs as external programs from database. See how this can be done using the db Console.

• The Self Exporting database

HTH

Karl

Hi reader,

With Oracle 10G specially with 10.2 you often hear :

! DO NOT USE HINTS ANY MORE! THEY ARE NOT NEEDED ANY MORE ! 

So thinking of the possibility to use a hint for a statement gives you a bad feeling doing something wrong. It’s clear to use hints only if there is no way to bring the optimizer to the desired execution plan or if the plan stability is most important even independent from gathered table/index stats.

Yesterday i navigated with the Db Console thru some SQL-Statements run in the past. Accidentally i found an interesting statement:

As you see this SELECT statement uses the NO_MERGE and ORDERED hint. It was executed via Db Console well known as an Oracle product ;-). And even the RULE hint seems (it’s not) to be out in Oracle 10.2, with these hints you dictate the query optimizer what plan to generate.

The SQL again - better formatted for discussion:

SELECT Task_List.Task_Id
  FROM (SELECT /*+ NO_MERGE(T) ORDERED */
         t.Task_Id
          FROM (SELECT *
                  FROM Dba_Advisor_Tasks
                 ORDER BY Task_Id DESC) t,
               Dba_Advisor_Parameters_Proj P1,
               Dba_Advisor_Parameters_Proj P2
         WHERE t.Advisor_Name = ‘ADDM’
           AND t.Status = ‘COMPLETED’
           AND t.Execution_Start >= (SYSDATE - 1)
           AND t.How_Created = ‘AUTO’
           AND t.Task_Id = P1.Task_Id
           AND P1.Parameter_Name = ‘INSTANCE’
           AND P1.Parameter_Value = Sys_Context(‘USERENV’,
                                                ‘INSTANCE’)
           AND t.Task_Id = P2.Task_Id
           AND P2.Parameter_Name = ‘DB_ID’
           AND P2.Parameter_Value = To_Char(:B1)
         ORDER BY t.Task_Id DESC) Task_List
 WHERE Rownum = 1

The NO_MERGE hint dictates the optimizer not to merge the sub query

SELECT *
  FROM Dba_Advisor_Tasks
 ORDER BY Task_Id DESC

into the rest of the statement, some kind of rewriting the query for performance reasons. This means the sub query is executed as it is written.

The ORDERED hint dictates the optimizer the join order of the tables.  The join orders of the tables then is depending on the order in the FROM table list . So the execution plan accesses T(Dba_Advisor_Tasks), P1(Dba_Advisor_Parameters_Proj), P2(Dba_Advisor_Parameters_Proj);

Let’s take a look at the EXPLAIN PLAN :

SQL> explain plan for
  2  SELECT Task_List.Task_Id
  3    FROM (SELECT /*+ NO_MERGE(T) ORDERED */
  4           t.Task_Id
  5            FROM (SELECT *
  6                    FROM Dba_Advisor_Tasks
  7                   ORDER BY Task_Id DESC) t,
  8                 Dba_Advisor_Parameters_Proj P1,
  9                 Dba_Advisor_Parameters_Proj P2
 10           WHERE t.Advisor_Name = ‘ADDM’
 11             AND t.Status = ‘COMPLETED’
 12             AND t.Execution_Start >= (SYSDATE - 1)
 13             AND t.How_Created = ‘AUTO’
 14             AND t.Task_Id = P1.Task_Id
 15             AND P1.Parameter_Name = ‘INSTANCE’
 16             AND P1.Parameter_Value = Sys_Context(’USERENV’,
 17                                                  ‘INSTANCE’)
 18             AND t.Task_Id = P2.Task_Id
 19             AND P2.Parameter_Name = ‘DB_ID’
 20             AND P2.Parameter_Value = To_Char(:B1)
 21           ORDER BY t.Task_Id DESC) Task_List
 22   WHERE Rownum = 1
 23  ;

EXPLAIN PLAN ausgef³hrt.

SQL> SELECT * FROM table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
————————————————————————————————————
Plan hash value: 2107564592

———————————————————————————————————–
| Id  | Operation                        | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
———————————————————————————————————–
|   0 | SELECT STATEMENT                 |                        |     1 |    13 |    19   (6)| 00:00:01 |
|*  1 |  COUNT STOPKEY                   |                        |       |       |            |       |
|   2 |   VIEW                           |                        |     1 |    13 |    19   (6)| 00:00:01 |
|*  3 |    SORT ORDER BY STOPKEY         |                        |     1 |   102 |    19   (6)| 00:00:01 |
|   4 |     NESTED LOOPS                 |                        |     1 |   102 |    18   (0)| 00:00:01 |
|   5 |      NESTED LOOPS                |                        |     1 |    78 |    17   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
————————————————————————————————————
|   6 |       VIEW                       |                        |     1 |    54 |    16   (0)| 00:00:01 |
|*  7 |        TABLE ACCESS FULL         | WRI$_ADV_TASKS         |     1 |    29 |    16   (0)| 00:00:01 |
|*  8 |       TABLE ACCESS BY INDEX ROWID| WRI$_ADV_PARAMETERS    |     1 |    24 |     1   (0)| 00:00:01 |
|*  9 |        INDEX UNIQUE SCAN         | WRI$_ADV_PARAMETERS_PK |     1 |       |     1   (0)| 00:00:01 |
|* 10 |      TABLE ACCESS BY INDEX ROWID | WRI$_ADV_PARAMETERS    |     1 |    24 |     1   (0)| 00:00:01 |
|* 11 |       INDEX UNIQUE SCAN          | WRI$_ADV_PARAMETERS_PK |     1 |       |     1   (0)| 00:00:01 |
———————————————————————————————————–

The Explain Plan confirms the effectiveness of the described hints. In the example you see another interesting point. The plan starts with TABLE ACCESS FULL OPERATION on WRI$_ADV_TASKS. As next operation after a full table scan i would expect a HASH JOIN Operation. But the Optimizer uses a NESTED LOOPS instead. Why?

It’s all about expected CARDINALITY^[1]! Because only 1 Row is expected via TABLE ACCESS FULL Operation a NESTED LOOPS Join Operation is the correct answer.

I removed the Predicate^[2] Information section for better overview. How would be the Explain Plan without Hints?

———————————————————————————————————–
| Id  | Operation                        | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
———————————————————————————————————–
|   0 | SELECT STATEMENT                 |                        |     1 |    13 |    19   (6)| 00:00:01 |
|*  1 |  COUNT STOPKEY                   |                        |       |       |            |       |
|   2 |   VIEW                           |                        |     1 |    13 |    19   (6)| 00:00:01 |
|*  3 |    SORT ORDER BY STOPKEY         |                        |     1 |   102 |    19   (6)| 00:00:01 |
|   4 |     NESTED LOOPS                 |                        |     1 |   102 |    18   (0)| 00:00:01 |
|   5 |      NESTED LOOPS                |                        |     1 |    78 |    17   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
———————————————————————————————————–
|   6 |       VIEW                       |                        |     1 |    54 |    16   (0)| 00:00:01 |
|*  7 |        TABLE ACCESS FULL         | WRI$_ADV_TASKS         |     1 |    29 |    16   (0)| 00:00:01 |
|*  8 |       TABLE ACCESS BY INDEX ROWID| WRI$_ADV_PARAMETERS    |     1 |    24 |     1   (0)| 00:00:01 |
|*  9 |        INDEX UNIQUE SCAN         | WRI$_ADV_PARAMETERS_PK |     1 |       |     1   (0)| 00:00:01 |
|* 10 |      TABLE ACCESS BY INDEX ROWID | WRI$_ADV_PARAMETERS    |     1 |    24 |     1   (0)| 00:00:01 |
|* 11 |       INDEX UNIQUE SCAN          | WRI$_ADV_PARAMETERS_PK |     1 |       |     1   (0)| 00:00:01 |
———————————————————————————————————–

This is no pasting error - it was the same execution plan (i flushed the shared pool before)! But nevertheless without hint depending on sampled stats the execution plan could change.

Conclusion

With Oracle 10G the technique using hints is still a very important tool for statement tuning. In project field sometimes a hint  is used to fast. There are other possibilities to influence an execution plan.

• are indexes missing?
• change the physical structure using IOT’s , partitions, clusters
• Query rewrite using materialized views
• is the kind of data model weak designed
• change your gather statistics parameter/method
• use of sql profiles (Oracle 10.x)
• patching object/system statistics

CBO still is in need of manual assistance because it’s estimations are based on an ideal model which often does not fit the reality.

HTH Karl

---

^[1] Predicates are comparison expressions in the where clause of an SQL Statement.
^[2] Cardinality here is the number of expected rows of an operation of an explain plan.

Hi, sometime you could get problems to connect to your Oracle 10G Db Console.

Look at the article page not found

Greetings Karl