Skip to Main Content
Forums

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

How can this bad query be improved?

jmft2012Feb 26 2013 — edited Mar 8 2013
Db:11.2.0.3
We have a 3rd party app and the web app runs very slow. We want to make the 3rd party to fix the issue. for the
app login process, I did an AWR , found the problem query it runs 10 mins. Then I did the sqltrace
here is it:
select clndr_id , count(*) 
from
 task where (clndr_id = :"SYS_B_0") group by clndr_id union select clndr_id , 
  count(*) from project where (clndr_id = :"SYS_B_1") group by clndr_id


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          0          0           0
Execute      1      0.01       0.00          0          0          0           0
Fetch        2     53.32     612.03      81650      58920          0           2
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4     53.34     612.04      81650      58920          0           2

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 34  (PX)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         2          2          2  SORT UNIQUE (cr=58923 pr=81650 pw=22868 time=113329109 us cost=58277 size=24 card=2)
         2          2          2   UNION-ALL  (cr=58923 pr=81650 pw=22868 time=113329001 us)
         1          1          1    SORT GROUP BY NOSORT (cr=58330 pr=81070 pw=22868 time=104312437 us cost=58128 size=7 card=1)
   5589739    5589739    5589739     VIEW  index$_join$_003 (cr=58330 pr=81070 pw=22868 time=619784236 us cost=57240 size=38875249 card=5553607)
   5589739    5589739    5589739      HASH JOIN  (cr=58330 pr=81070 pw=22868 time=617373467 us)
   5590158    5590158    5590158       INDEX RANGE SCAN NDX_TASK_CALENDAR (cr=21676 pr=21676 pw=0 time=113637058 us cost=11057 size=38875249 card=5553607)(object id 24749)
   6673774    6673774    6673774       INDEX FAST FULL SCAN NDX_TASK_PROJ_RSRC (cr=36651 pr=36526 pw=0 time=213370625 us cost=21921 size=38875249 card=5553607)(object id 217274)
         1          1          1    SORT GROUP BY NOSORT (cr=593 pr=580 pw=0 time=9016527 us cost=149 size=17 card=1)
    136390     136390     136390     INDEX FAST FULL SCAN NDX_PROJECT_CALENDAR (cr=593 pr=580 pw=0 time=165434 us cost=132 size=2315876 card=136228)(object id 154409)


Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   MODE: ALL_ROWS
      2   SORT (UNIQUE)
      2    UNION-ALL
      1     SORT (GROUP BY NOSORT)
5589739      TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF 'TASK' 
                 (TABLE)
5589739       INDEX   MODE: ANALYZED (RANGE SCAN) OF 
                  'NDX_TASK_CALENDAR' (INDEX)
5590158     SORT (GROUP BY NOSORT)
6673774      INDEX   MODE: ANALYZED (RANGE SCAN) OF 
                 'NDX_PROJECT_CALENDAR' (INDEX)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  Disk file operations I/O                        2        0.00          0.00
  db file sequential read                     22235        1.61        138.66
  direct path write                            1620        3.25        177.42
  db file scattered read                       2313        1.89        238.98
  direct path read                              385        1.72         19.52
  SQL*Net message from client                     2        0.11          0.21
Please make your comments.
Thanks in Advance.
This post has been answered by Jonathan Lewis on Mar 4 2013
Jump to Answer

Comments

Frank Kulash

Hi, @jackbox1098
When querying the dba_tab_cols againt the col_audit_tab only the matching tables are returned. We need to find all tables that do not yet exist in the col_audit_tab and insert them.
To find rows that are in one table but not another, you can use MINUS, NOT EXISTS, NOT IN or outer join.
We have attached some test data.
Post the sample data, the exact results you want from that sample data, and an explanation of how you get those results from that data right in this space. Do not use attachments. Not everyone who wants to help you can or will open attahements.

Stax

Hi, @jackbox1098
'COL_A','COL_B','COL_C' is constant?
SELECT
tc.table_name,
MAX(
CASE
WHEN tc.column_name = 'COL_A' THEN
tc.column_name
ELSE
NULL
END
) AS col_a,
MAX(
CASE
WHEN tc.column_name = 'COL_B' THEN
tc.column_name
ELSE
NULL
END
) AS col_b,
MAX(
CASE
WHEN tc.column_name = 'COL_C' THEN
tc.column_name
ELSE
NULL
END
) AS col_c
FROM
user_tab_cols tc
--RIGHT JOIN myuser.col_audit_tab at ON tc.table_name = at.table_name
WHERE
tc.table_name<>'COL_AUDIT_TAB' -- and owner='MYUSER'
and tc.column_name in ('COL_A','COL_B','COL_C')
and not exists (
select 1
from col_audit_tab at
where tc.table_name = at.table_name and (tc.column_name=at.COL_A or tc.column_name=at.COL_B or tc.column_name=at.COL_C)
)
GROUP By
tc.table_name

Solomon Yakobson

OP, if I got it right, is looking for both ways changes - additions to audit table and removal of what is no longer valid from audit table. Therefore it should be MERGE into audit table using full outer join between DBA_TAB_COLS and audit table. That is if OP wants to store results in a table. I'd look into creating a view as full outer join between DBA_TAB_COLS and audit table.
SY.

user545194

Thanks to all for your feedback!
@stax Thanks for the script. Still not returning the table names that are in the data dictionary, but not in the audit table.
@solomon-yakobson We need to add all table names to the audit table that are in the data dictionary. No deletions, only inserts.
Tables with the mentioned columns above must be saved in the col_audit_tab; i.e.
-> Audit table columns: Table_name col_a col_b col_c
-> Entries: Key_tab col_a col_b col_c
Key_tab2 col_a null col_c

Stax

@stax Thanks for the script. Still not returning the table names that are in the data dictionary, but not in the audit table.
I do not understand what is needed, you can example
ps
there is no information about KEY_COL_TAB2.COL_B in col_audit_tab, so the script returned it

user545194

Our goal is to query the dba_tab_cols and save all tables in the audit table that have these specific columns. The table names here are only examples.
We can use different table and column names i.e. Labels, Handbooks, Services. ID_CLIENT1, ID_CLIENT2, ID_CLIENT3 to make it clearer.

Stax

that have these specific columns
"specific columns" (COL_A,COL_B,COL_C) the constants in script?
example:

  1. exist the table DEPT, not present in  in the audit table
OWNER	TABLE_NAME	COLUMN_NAME
STAX	DEPT		DEPTNO
STAX	DEPT		DNAME
STAX	DEPT		LOC
  1. exist the table Key_tab2 and present in col_audit_tab (COL_B not in the audit table)
    that should return the script?
user545194

The output (COL_AUDIT_TAB) should be like this:
OWNER TABLE_NAME ID_CLIENT1 ID_CLIENT2 ID_CLIENT3
USER1 LABELS ID_CLIENT1 NULL ID_CLIENT3
USER1 SERVICES NULL ID_CLIENT2 NULL
Important is that all tables are saved that have at least 1 of these columns.
Thanks!

Solomon Yakobson
Answer

We need to add all table names to the audit table that are in the data dictionary. No deletions, only inserts.
So if column was dropped you'll still have it in audit table? Anyway, something like:

MERGE
  INTO COL_AUDIT_TAB T
  USING(
        SELECT  OWNER,
                TABLE_NAME,
                MAX(
                    CASE
                      WHEN COLUMN_NAME = 'COL_A' THEN 'COL_A'
                    END
                   ) COL_A,
                MAX(
                    CASE
                      WHEN COLUMN_NAME = 'COL_B' THEN 'COL_B'
                    END
                   ) COL_B,
                MAX(
                    CASE
                      WHEN COLUMN_NAME = 'COL_C' THEN 'COL_C'
                    END
                   ) COL_C
          FROM  DBA_TAB_COLS
          WHERE COLUMN_NAME IN ('COL_A','COL_B','COL_C')
          GROUP BY OWNER,
                   TABLE_NAME
       ) S
  ON (
      T.OWNER = S.OWNER,
      T.TABLE_NAME = S.TABLE_NAME
     )
  WHEN NOT MATCHED
    THEN
      INSERT
        VALUES(
               S.OWNER,
               S.TABLE_NAME
               S.COL_A,
               S.COL_B,
               S.COL_C
              )
/

SY.
P.S. Added ON clause I initially missed

Marked as Answer by user545194 · Jun 21 2022
1 person found this helpful
user545194

@solomon-yakobson Thanks a lot! This should do it. I will test it in our system tomorrow. Feedback to follow.

1 - 10
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Apr 5 2013
Added on Feb 26 2013
#general-database-discussions
40 comments
860 views