Hi reader,

changing a type of a company form internal value 2 to 1 increases the number companies with type 1; Ok thats no rocket science. But i wanted to run the counting query in the past to get the old number companies with company type = 1;

Currently i get following result with 359 Companies :

SQL> SELECT COUNT(*)
  2            FROM Company
  3           WHERE Company.Companytype = 1
  4  ;

  COUNT(*)
———-
       359

with following query i get the result in the past (358 Companies). Undo Blocks were still available for this, look at the AS OF TIMESTAMP Clause:

SELECT *
  FROM (SELECT COUNT(*)
          FROM Company
         WHERE Company.Companytype = 1) AS OF TIMESTAMP SYSDATE - 0.1;

 

 SQL> SELECT *
  2    FROM (SELECT COUNT(*)
  3            FROM Company
  4           WHERE Company.Companytype = 1) AS OF TIMESTAMP SYSDATE - 0.1;

  COUNT(*)
———-
       358

SQL>

For a limited time you could query in the past even with COUNT(*)  - Queries;

HTH Karl

PS.: Unfortunately this works only for the past but not for the future - ;-)

 

 

Hi reader,

If you copy a schema from a database to another you could check the integrity of the operation via the DBA_OBJECTS View. A good idea but the data dictionary view is incomplete - not all object types are supported.

SELECT DISTINCT Do.Object_Type
  FROM Dba_Objects Do
 ORDER BY Do.Object_Type

;

SQL> SELECT DISTINCT Do.Object_Type
  2    FROM Dba_Objects Do
  3   ORDER BY Do.Object_Type

  4  ;

OBJECT_TYPE
——————-
CHAIN
CLUSTER
CONSUMER GROUP
CONTEXT
DATABASE LINK
DIRECTORY
EVALUATION CONTEXT
FUNCTION
INDEX
INDEX PARTITION
INDEXTYPE
JOB
JOB CLASS
LIBRARY
LOB
LOB PARTITION
MATERIALIZED VIEW
OPERATOR
PACKAGE
PACKAGE BODY

PROCEDURE
PROGRAM
QUEUE
RESOURCE PLAN
RULE
RULE SET
SCHEDULE
SEQUENCE
SYNONYM
TABLE
TABLE PARTITION
TRIGGER
TYPE
TYPE BODY
UNDEFINED
VIEW
WINDOW
WINDOW GROUP
XML SCHEMA

39 rows selected

SQL>

In a schema of the same database i queried all object types of the DBA_OBJECTS view i found object types not listed : 

SELECT Uml.Log_Table
  FROM User_Mview_Logs Uml
;

SELECT Urc.NAME,
              Urc.Refgroup
  FROM User_Refresh_Children Urc
;

SELECT Ur.Refgroup,
       Ur.Rname
  FROM User_Refresh Ur

;

 

SQL> SELECT Uml.Log_Table
  2    FROM User_Mview_Logs Uml

;

LOG_TABLE
——————————
MLOG$_COMPANY

SQL>

SQL> SELECT Ur.Refgroup,
  2         Ur.Rname
  3    FROM User_Refresh Ur

;

  REFGROUP RNAME
———- ——————————
       141 GMRV_FACTS_RGR_IFX
SQL>

SQL> SELECT Urc.NAME,
  2                 Urc.Refgroup
  3    FROM User_Refresh_Children Urc

;

NAME                             REFGROUP
—————————— ———-
GMRV_FACTS_MAX_PARENT_IFX             141
GMRV_FACTS_COUNTPART_IFX              141
GMRV_FACTS_MAX_VAL_IFX                141

SQL>  

As you can see clearly Refresh groups and Materialized View Logs are not supported and documented via the (DBA|ALL|USER)_OBJECTS Views.

 

HTH KARL

 

Hi reader,

with oracle 10.2 you have something like a segment fragment which could release space not needed anymore. First you enable row movement to enable reorganization. Then you start to shrink. With the dba_segments view you can query the segment properties of your table or index.

SELECT Us.Segment_Name,
       Us.Segment_Type,
       Us.Bytes,
       Us.Blocks
  FROM User_Segments Us
 WHERE Us.Segment_Name = ‘EVENTLOG’

;

SQL>                                      
SQL> SELECT Us.Segment_Name,
  2         Us.Segment_Type,
  3         Us.Bytes,
  4         Us.Blocks
  5    FROM User_Segments Us
  6   WHERE Us.Segment_Name = ‘EVENTLOG’
  7  ;

SEGMENT_NAME                                SEGMENT_TYPE            BYTES     BLOCKS
——————————————- —————— ———- ———-
EVENTLOG                                    TABLE                84934656      10368

SQL>

 

Like a diskdefragmenter must be able to move disk blocks of a file the table defragmentation only works effective if rows are allowed to be moved from one oracle block to another. This means the rowid of a table row changes.

ALTER TABLE EVENTLOG ENABLE ROW MOVEMENT

;
ALTER TABLE EVENTLOG SHRINK SPACE

;

SQL> ALTER TABLE EVENTLOG ENABLE ROW MOVEMENT;
Table altered
SQL> ALTER TABLE EVENTLOG SHRINK SPACE;
Table altered
SQL>

In this example the number blocks could be reduced from 10368 to 128! Also this means that the high water mark which is the end position of the Full Table Scan is reseted too causing reduced resource usage for Full Table Scans.

SQL>                                                                                
SQL> SELECT Us.Segment_Name,                                                        
  2         Us.Segment_Type,                                                        
  3         Us.Bytes,                                                               
  4         Us.Blocks                                                               
  5    FROM User_Segments Us                                                        
  6   WHERE Us.Segment_Name = ‘EVENTLOG’
  7  ;                                           
                                                                                    
SEGMENT_NAME                                SEGMENT_TYPE            BYTES     BLOCKS
——————————————- —————— ———- ———-
EVENTLOG                                    TABLE                 1048576        128
                                                                                    
SQL>                                                                                

How do you detect candidates for shrinking? simply look into Segment Advisor Recommendations of the database Homepage of the DbConsole or do this by SQL using the Dba_Advisor_Findings view :

SELECT Daf.Task_Id,
       Daf.Finding_Id,
       Daf.Task_Name,
       Daf.TYPE,
       Daf.Object_Id,
       Daf.Message
  FROM Dba_Advisor_Findings Daf
 WHERE Daf.Task_Name LIKE ‘SYS_AUTO_SPCADV%’
   AND Message LIKE ‘%EVENTLOG%’

;

SQL>
SQL> SELECT Daf.Task_Id,
  2         Daf.Finding_Id,
  3         Daf.Task_Name,
  4         Daf.TYPE,
  5         Daf.Object_Id,
  6         Daf.Message
  7    FROM Dba_Advisor_Findings Daf
  8   WHERE Daf.Task_Name LIKE ‘SYS_AUTO_SPCADV%’
  9     AND Message LIKE ‘%EVENTLOG%’
 10  ;

   TASK_ID FINDING_ID TASK_NAME                      TYPE         OBJECT_ID MESSAGE
———- ———- —————————— ———– ———- ——————————————————————————–
     14837         10 SYS_AUTO_SPCADV_250201882006   INFORMATION         28 Zeilenverschiebung der Tabelle EVENTLOG aktivieren und Verkleinerung vorneh
     14870         11 SYS_AUTO_SPCADV_25041982006    INFORMATION         28 Zeilenverschiebung der Tabelle EVENTLOG aktivieren und Verkleinerung vorneh
     15127          7 SYS_AUTO_SPCADV_410202182006   INFORMATION         23 Zeilenverschiebung der Tabelle EVENTLOG aktivieren und Verkleinerung vorneh
     15228          1 SYS_AUTO_SPCADV_921202282006   INFORMATION          1 Zeilenverschiebung der Tabelle EVENTLOG aktivieren und Verkleinerung vorneh

SQL>

HTH Karl

---

Links

• Segment Shrink 

 

Hi Reader,

I use a pattern to select access indexes of a table defined in our application schema. Now having Oracle 10G i changed the SQL from standard LIKE to REGEXP_LIKE. If you are used to work with regular expression specially on UNIX operating systems you will be very excited about the regular expression engine built in Oracle SQL.

The classic approach :

SELECT Ui.Index_Name
 FROM User_Indexes Ui
 WHERE Ui.Table_Name =‘NVDW_FACTS_V02_IFX’
   AND(   Ui.Index_Name LIKE Ui.Table_Name || ‘\_IX__’ ESCAPE ‘\’
       OR Ui.Index_Name LIKE Ui.Table_Name || ‘\_FX__’ ESCAPE ‘\’
       OR Ui.Index_Name LIKE Ui.Table_Name || ‘\_BX__’ ESCAPE ‘\’)

;

The new approach :

SELECT Ui.Index_Name
  FROM User_Indexes Ui
 WHERE Ui.Table_Name = ‘NVDW_FACTS_V02_IFX’
   AND regexp_like(ui.index_name, ui.table_name || ‘_[IFB]X[[:digit:]]{2}’)

;

The output :

SQL> SELECT Ui.Index_Name
  2    FROM User_Indexes Ui
  3   WHERE Ui.Table_Name = ‘NVDW_FACTS_V02_IFX’
  4     AND regexp_like(ui.index_name, ui.table_name || ‘_[IFB]X[[:digit:]]{2}’);

INDEX_NAME
——————————
NVDW_FACTS_V02_IFX_BX01
NVDW_FACTS_V02_IFX_BX02

SQL>

The new appraoach is more exact because the last to characters are exactly defined as two digits ‘[[:digit:]]{2}’, you could do the same with Oracle SQL too but with a very large where clause!

HTH
Karl

 

---

 

Links :

• Oracle Regular Expressions

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,

Gerhard is a colleague in the current project i work. An upload of documents via .NET was very slow (1M in 40 seconds). We found a workaround and reached 1M in 2 seconds. Here the article published in Gerhard’s Blog;

Slow Oracle BLOB upload with C# .NET and how to speed up.

Greetings
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