Help Tune a Slow & Bad SQL statement 3

[tekpr00]

Hello All:
I have a statement that actually works, albeit not the best looking, but was fast:

 select owner, table_name, column_name from dba_tab_columns where (column_name like '%ADDRESS%' OR column_name like '%DOB%'OR column_name like '%NAME%'OR column_name like '%PHONE%'OR column_name like '%PHN%') and owner not in ('SYS','SYSTEM','OUTLN','DBSNMP','WMSYS','DBA_MONITOR','WEBSYS','XDB', 'CTXSYS', 'EXFSYS', 'DMSYS','ORDSYS','OLAPSYS','SYSMAN','MDSYS','PERFSTAT', 'SCOTT', 'WKSYS','FLOWS_030000','WK_TEST','FLOWS_FILES','RMAN','ODM','SIEBEL') and column_name not in ('PROD_NAME','PRODUCT_NAME', 'GROUP_NAME','DPT_NAME','APP_NAME', 'BLOCK_NAME','BUILDING_NAME', 'CHEMICAL_NAME') AND table_name not like '%SYS%' and table_name not like'%ORGANIZATIONS%' and table_name not like'%BIN%' and table_name not like'%TRIGGER%' and table_name not like'%AUTOINV%' and table_name not like'%SERVICE_TYPE%' and table_name not like'%AQ%' and table_name not like'%QS%' and table_name not like'%MV%' and table_name not like'%JAVA%' and table_name not like'%PEER%'

However, I wanted to filter out only tables that actually contain data, so I joined the original to DBA_TABLES where NUM_ROWS > 0 like so:

 select t.owner, c.table_name, c.column_name from dba_tables t,dba_tab_columns c where t.owner = c.owner and (c.column_name like '%ADDRESS%' OR c.column_name like '%DOB%'OR c.column_name like '%NAME%'OR c.column_name like '%PHONE%'OR c.column_name like '%PHN%') and t.owner not in ('SYS','SYSTEM','OUTLN','DBSNMP','WMSYS','DBA_MONITOR','WEBSYS','XDB', 'CTXSYS', 'EXFSYS', 'DMSYS','ORDSYS','OLAPSYS','SYSMAN','MDSYS','PERFSTAT', 'SCOTT', 'WKSYS','FLOWS_030000','WK_TEST','FLOWS_FILES','RMAN','ODM','SIEBEL') and c.column_name not in ('PROD_NAME','PRODUCT_NAME', 'GROUP_NAME','DPT_NAME','APP_NAME', 'BLOCK_NAME','BUILDING_NAME', 'CHEMICAL_NAME') and t.num_rows>0 AND c.table_name not like '%SYS%' and c.table_name not like'%ORGANIZATIONS%' and c.table_name not like'%BIN%' and c.table_name not like'%TRIGGER%' and c.table_name not like'%AUTOINV%' and c.table_name not like'%SERVICE_TYPE%' and c.table_name not like'%AQ%' and c.table_name not like'%QS%' and c.table_name not like'%MV%' and c.table_name not like'%JAVA%' and c.table_name not like'%PEER%'

Now the code is so slow and sometimes give the Error: ORA-01652: unable to extend temp segment by 128 in tablespace TEMP.

Please, can you help too this statement.
Thanks in advance.

 

[SantaMufasa]

Yes, I can see how adding the "NUM_ROWS"-check would cause the large consumption of both time and space: all joins/WHERE conditions must use full-table scans (i.e., not indexes), and your join is initially a Cartesian product of dba_tab_columns rows x dba_tab_columns rows. So...

I recommend using a couple of Oracle "global temporary tables":

create global temporary table tekpro
       (owner       varchar2(30)
       ,table_name  varchar2(30)
       ,column_name varchar2(30)
       )
       ON COMMIT PRESERVE ROWS;

create global temporary table tekpro_tabs
      (owner varchar2(30)
      ,table_name varchar2(30)
      )
      ON COMMIT PRESERVE ROWS;

Then to use them:

insert into tekpro 
select owner
      ,table_name
      ,column_name
  from dba_tab_columns
 where (   column_name like '%ADDRESS%'
        OR column_name like '%DOB%'
        OR column_name like '%NAME%'
        OR column_name like '%PHONE%'
        OR column_name like '%PHN%')
   and owner
       not in       ('SYS','SYSTEM','OUTLN','DBSNMP','WMSYS','DBA_MONITOR','WEBSYS','XDB'
                    ,'CTXSYS','EXFSYS','DMSYS','ORDSYS','OLAPSYS','SYSMAN'
                    ,'MDSYS','PERFSTAT','SCOTT','WKSYS','FLOWS_030000'
                    ,'WK_TEST','FLOWS_FILES','RMAN','ODM','SIEBEL')
   and column_name
       not in ('PROD_NAME','PRODUCT_NAME', 'GROUP_NAME','DPT_NAME','APP_NAME'
              ,'BLOCK_NAME','BUILDING_NAME', 'CHEMICAL_NAME')
   AND table_name not like '%SYS%'
   and table_name not like'%ORGANIZATIONS%'
   and table_name not like'%BIN%'
   and table_name not like'%TRIGGER%'
   and table_name not like'%AUTOINV%'
   and table_name not like'%SERVICE_TYPE%'
   and table_name not like'%AQ%'
   and table_name not like'%QS%'
   and table_name not like'%MV%'
   and table_name not like'%JAVA%'
   and table_name not like'%PEER%'
/

963 rows created.

Elapsed: 00:00:00.26

commit;

insert into tekpro_tabs
       select distinct owner, table_name
         from tekpro;

401 rows created.

Elapsed: 00:00:00.00

commit;

col last_analyzed format a13
select distinct d.num_rows, d.last_analyzed, d.owner||'.'||d.table_name table_name
  from dba_tables  d
      ,tekpro_tabs t
 where d.table_name = t.table_name
   and d.num_rows > 0
 order by table_name
/

  NUM_ROWS LAST_ANALYZED TABLE_NAME
---------- ------------- -------------------------------------
         3 30-MAR-10     APEX_030200.WWV_COLUMN_EXCEPTIONS
        10 06-DEC-12     APEX_030200.WWV_FLOWS
        12 06-DEC-12     APEX_030200.WWV_FLOW_BUTTON_TEMPLATES.
.
.
.
        33 29-JAN-13     SUMMIT.S_PRODUCT
         5 29-JAN-13     SUMMIT.S_REGION
         5 29-JAN-13     SUMMIT.S_WAREHOUSE

85 rows selected.

Elapsed: 00:00:00.07

As you can see from the above output, all of the INSERTs and SELECTs took 1/3 of a second, total.

Let us know if this meets your needs or if you have additional questions.

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[tekpr00]

Thanks for the help Santa as always.
I made a little change to the final code as below:

create global temporary table tekpro_tabs
      (owner varchar2(30)
      ,table_name varchar2(30)
      ,column_name varchar2(30)
      )
      ON COMMIT PRESERVE ROWS;

This allowed me to insert as follows:

insert into tekpro_tabs
       select distinct owner, table_name, column_name
         from tekpro;

And Finally select as so:

select distinct d.num_rows, d.last_analyzed, d.owner||'.'||d.table_name table_name, t.column_name
  from dba_tables  d
      ,tekpro_tabs t
 where d.table_name = t.table_name
   and d.num_rows > 0
 order by table_name

Since I am running this in production environment, for cleanups, I did the following:

truncate table tekpro_tabs;
drop table tekpro_tabs;
truncate table tekpro;
drop table tekpro;

[highlight #EF2929]For some wired reason, I am hoping that this WILL NOT affect original production data. Please let me know.[/highlight]

Again thanks.

 

[SantaMufasa]

Actually, Tekpro, that is one of the beauties of Oracle GLOBAL TEMPORARY TABLES...your data in such is automatically truncated at the end of the using session, and no other session (not even another session of yours) can ever see the data that your session inserted (or updates) in the temporary table. Additionally, an unlimited number of other sessions can use the temporary tables simultaneously without mixing of multi-session data (since no other session can see a different session's data). You only need to run the original CREATE GLOBAL TEMPORARY TABLE, and the table will always be there (empty at the beginning of any using session). Pretty cool, huh.

So, bottom line: you don't need the TRUNCATEs or the DROPs.

I'm glad you posted what you did so that we can clarify all of this. Post again if you need additional clarifications.

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[tekpr00]

Thanks for the feedback.
More importantly, is there anyway this could cause production data corruption?
I dont see that happening, but I am just seeking a second opinion.
Tekpro.

 

[SantaMufasa]

Tekpr00,

None of the above activities are touching any Production data -- you are simply Inserting into temporary tables that have nothing to do with Production, and you are doing SELECTs, which cannot impact any tables. So, no, you cannot affect Production with the above code.

Another clarification is that you are using NUM_ROWS as an element of your work. Keep in mind that NUM_ROWS is not updated in real time; it is updated when you gather statistics for the underlying table. That is why I selected LAST_ANALYZED during my query, above -- to see how fresh the NUM_ROWS value is.

If you have an active table (i.e., lots of INSERTs and DELETEs, which can change the overall number of rows in your table), and you haven't gathered statistics recently, then NUM_ROWS could be inaccurate, and, more importantly, Oracle's EXPLAIN PLANs for that table might very well cause poor performance against that table.

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.