Home » SQL & PL/SQL » SQL & PL/SQL » Problem related to view and long running query (Oracle 10.2.0.3.0, SunOS)
Problem related to view and long running query [message #400061] Sat, 25 April 2009 11:19 Go to next message
tariqahsan
Messages: 13
Registered: February 2009
Junior Member
Hi All,

We have created a view which is taking extremely long (several hours) to do a query like - 'select * from v_test;

The following is the basic structure of the sql used to create this view from 2 different tables which both have a total of around 330,000 rows with 90 columns in each -

CREATE OR REPLACE VIEW TEST.V_TEST AS
SELECT
COL1,
COL2,
COL3,
COL4,
...
COL90

FROM
(
SELECT
'|' COL1,
COL2,
' ' COL3,
COL4,
to_date('01/01/1970','MM/DD/YYYY') COL5,
...
COL90
FROM
TEST.TABLE1
)
UNION ALL
(
SELECT
'*' COL1,
A.COL2,
A.COL3,
A.COL4,
A.COl5,
B.COL6
...
B.COL90 FROM
TEST.TEST2 A, TEST.TEST2 B
WHERE
A.COL1 = B.COL1
)
ORDER BY A.COL1 ASC, A.COL2 ASC
/

Would appreciate if I could find the most efficient way to create the view and/or make our sql efficient which is being used within a Java application. By the way both these tables are properly indexed.

Thanks



Re: Problem related to view and long running query [message #400063 is a reply to message #400061] Sat, 25 April 2009 11:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can first read:
- OraFAQ Forum Guide, Performance Tuning section
- Performances Tuning sticky
- OraFAQ Performance Tuning
- Rosco's SQL Tuning Guide

Then provide the requested and usual information.

You can also have a look at Database Performance Tuning Guide.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).


Regards
Michel
Re: Problem related to view and long running query [message #400069 is a reply to message #400061] Sat, 25 April 2009 12:28 Go to previous messageGo to next message
tariqahsan
Messages: 13
Registered: February 2009
Junior Member
Re-posting the code with proper formatting -

CREATE OR REPLACE VIEW TEST.V_TEST AS 
    SELECT 
       COL1,
       COL2,
       COL3, 
       COL4, 
       ...
       COL90

    FROM 
    ( 
       SELECT 
          '|' COL1, 
          COL2, 
          ' ' COL3,
          COL4, 
          to_date('01/01/1970','MM/DD/YYYY') COL5,
          ...
          COL90
       FROM
       TEST.TABLE1
    ) 
    UNION ALL 
    ( 
       SELECT 
          '*' COL1, 
          A.COL2, 
          A.COL3,
          A.COL4, 
          A.COl5,
          B.COL6
          ...
          B.COL90 
        FROM
        TEST.TEST2 A, TEST.TEST2 B
        WHERE 
        A.COL1 = B.COL1
   ) 
   ORDER BY A.COL1 ASC, A.COL2 ASC
/


Re: Problem related to view and long running query [message #400070 is a reply to message #400069] Sat, 25 April 2009 12:44 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
So basically, what just happened is that you read Michel's post and felt that reposting with formatting is what he was asking you to do? Did you bother to read any of the links that he provided? If not, why not?
Re: Problem related to view and long running query [message #400071 is a reply to message #400061] Sat, 25 April 2009 12:56 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
Posting formatted code is more than some have learned how to do.
It is better than nothing, but exactly how much better is left up to each responder to decide.
Re: Problem related to view and long running query [message #400086 is a reply to message #400071] Sat, 25 April 2009 21:56 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
You claim that you use the SQL to SELECT * FROM VIEW. I assume that means you do not provide a WHERE clause to filter the results.

I also notice that in your view SQL, there are no filter clauses - just one join clause on the second part of the UNION ALL.

This seems very simple to me. The most efficient access path for the first part of the SQL will probably be a FULL TABLE SCAN. The most efficient access path for the second part will be FULL TABLE SCANs of both tables joined with a HASH JOIN. The whole shebang will be combined with a UNION ALL and sorted (for the ORDER BY). Furthermore, TableA should be the FIRST of the two tables in the hash join.

Run an Explain plan, if that is what you see, then it is optimised.

If the plan is optimal (as described) then you can still verify that your tables are not suffering from any HWM or chaining problems (easiest way: create copies of the two tables and see if the SQL is any faster on the copies).

Finally, the ORDER BY is sorting quite a WIDE data set. This will take a bit of memory and may spill to TEMP segments. Try the SQL without the ORDER BY and measure how long it takes to return EVERY row - not just the first row. That will give you an idea of the time it takes to sort and whether it is worth putting any effort into tuning that aspect (which would be very hard, but not impossible).

Run an explain plan of the actual query that takes a long time. If it is as I described above, try creating copies of the table and try it again. Post both sets of timings.

Ross Leishman
Re: Problem related to view and long running query [message #400252 is a reply to message #400086] Mon, 27 April 2009 05:51 Go to previous messageGo to next message
tariqahsan
Messages: 13
Registered: February 2009
Junior Member
Thanks Ross for your suggestions. I will try it out.
Re: Problem related to view and long running query [message #400363 is a reply to message #400252] Mon, 27 April 2009 21:31 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Received the following PM from the OP - via BlackSwan (the OP sure has a funny idea about efficient communication...)
SQL> set autotrace traceonly
SQL> select * from abom_own.V_LOCAL_MAN_CONJUNCTION;

13766 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2842210988

--------------------------------------------------------------------------------
-------------------------

| Id  | Operation             | Name                    | Rows  | Bytes |TempSpc
| Cost (%CPU)| Time     |

--------------------------------------------------------------------------------
-------------------------

|   0 | SELECT STATEMENT      |                         | 13766 |  7393K|
|  1212  (79)| 00:00:15 |

|   1 |  VIEW                 | V_LOCAL_MAN_CONJUNCTION | 13766 |  7393K|
|  1212  (79)| 00:00:15 |

|   2 |   SORT ORDER BY       |                         | 13766 |  2817K|    10M
|   266  (81)| 00:00:04 |

|   3 |    UNION-ALL          |                         |       |       |
|            |          |

|   4 |     TABLE ACCESS FULL | LOCAL_MAN               |  8307 |  1111K|
|    52   (2)| 00:00:01 |

|*  5 |     HASH JOIN         |                         |  5459 |  1705K|  1040K
|   214   (1)| 00:00:03 |

|   6 |      TABLE ACCESS FULL| LOCAL_MAN_TRX           |  5459 |   975K|
|    51   (0)| 00:00:01 |

|   7 |      TABLE ACCESS FULL| LOCAL_MAN               |  8307 |  1111K|
|    52   (2)| 00:00:01 |

--------------------------------------------------------------------------------
-------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("I"."HDR_GUID"="H"."HDR_GUID")


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        561  consistent gets
          0  physical reads
          0  redo size
    1947870  bytes sent via SQL*Net to client
      10579  bytes received via SQL*Net from client
        919  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      13766  rows processed

SQL>

Re: Problem related to view and long running query [message #400365 is a reply to message #400363] Mon, 27 April 2009 21:40 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
So, looking at this sql, plan and stats, we see:
- The SELECT from the view is unconstrained - every row from every table is used
- The plan is a Full Table Scan - Hash Join as hoped
- The sort (ORDER BY) is happening in-memory, not paging to disk.
- It read 561 buffers and returned 2MB of data. That would be about right if the database block size is 4KB.

To me, this looks optimal.

Please do the following:
- In SQL*Plus, type SHOW PARAMETER DB_BLOCK_SIZE. Hopefully that will show a value of 4096, otherwise your tables may have a high-water-mark problem.
- Tell us how long the SQL takes to run
- Tell us your performance expectations; i.e. How long do you expect is reasonable for the SQL.

Ross Leishman
Re: Problem related to view and long running query [message #400747 is a reply to message #400365] Wed, 29 April 2009 07:26 Go to previous messageGo to next message
tariqahsan
Messages: 13
Registered: February 2009
Junior Member
Hi Ross,

Here's some of the things you asked for -

SQL> show parameter DB_BLOCK_SIZE

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_size                        integer     8192
SQL>


About the sql run time I don't have an exact time. As I mentioned earlier the view data is used through a Hibernate object in our Java application to display on a web page. This has been taking quite a long time than what we expect to take. We are using other view data that are performing reasonably well in our other codes. We are suspecting the performance bottleneck could mostly related to our database query, view creation etc.
Here's the timing for just doing query returning 1000 rows -
select * from test.v_test where rownum < 1001
1000 rows selected.

Elapsed: 00:04:43.00
SQL>


Don't know if this should be acceptable for a view containing around 327,000 rows at a time

Thanks

Re: Problem related to view and long running query [message #400750 is a reply to message #400747] Wed, 29 April 2009 07:30 Go to previous messageGo to next message
tariqahsan
Messages: 13
Registered: February 2009
Junior Member
By the way, is there a good site to understand all the values of a 'set autotrace traceonly' output?
Re: Problem related to view and long running query [message #400775 is a reply to message #400750] Wed, 29 April 2009 08:20 Go to previous messageGo to next message
tariqahsan
Messages: 13
Registered: February 2009
Junior Member
My extreme bad. Posted the wrong autotrace output. Sorry here's the correct one. Also, the total number of rows is not around 327,000 as I mentioned earlier. It is 656034.

656034 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 374036124

--------------------------------------------------------------------------------
-------------------------

| Id  | Operation             | Name                    | Rows  | Bytes |TempSpc
| Cost (%CPU)| Time     |

--------------------------------------------------------------------------------
-------------------------

|   0 | SELECT STATEMENT      |                         |   656K|   358M|
| 78309  (80)| 00:15:40 |

|   1 |  VIEW                 | V_DIFM_DOTM_CONJUNCTION |   656K|   358M|
| 78309  (80)| 00:15:40 |

|   2 |   SORT ORDER BY       |                         |   656K|   205M|   683M
| 15847  (84)| 00:03:11 |

|   3 |    UNION-ALL          |                         |       |       |
|            |          |

|   4 |     TABLE ACCESS FULL | DIFM_DOTM_HEADER        |   328K|    65M|
|  2721   (2)| 00:00:33 |

|*  5 |     HASH JOIN         |                         |   327K|   139M|    69M
| 13126   (1)| 00:02:38 |

|   6 |      TABLE ACCESS FULL| DIFM_DOTM_HEADER        |   328K|    65M|
|  2721   (2)| 00:00:33 |

|   7 |      TABLE ACCESS FULL| DIFM_DOTM_ITEMS         |   327K|    74M|
|  3067   (2)| 00:00:37 |

--------------------------------------------------------------------------------
-------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("H"."HDR_GUID"="I"."HDR_GUID")


Statistics
----------------------------------------------------------
        131  recursive calls
         27  db block gets
      31057  consistent gets
      89252  physical reads
          0  redo size
  119301943  bytes sent via SQL*Net to client
     481577  bytes received via SQL*Net from client
      43737  SQL*Net roundtrips to/from client
          0  sorts (memory)
          1  sorts (disk)
     656034  rows processed

SQL>
Re: Problem related to view and long running query [message #400777 is a reply to message #400750] Wed, 29 April 2009 08:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
tariqahsan wrote on Wed, 29 April 2009 14:30
By the way, is there a good site to understand all the values of a 'set autotrace traceonly' output?

Did you try Oracle documentation?
Performance Tuning Guide
Chapter 19 Using EXPLAIN PLAN

Regards
Michel
Re: Problem related to view and long running query [message #400792 is a reply to message #400363] Wed, 29 April 2009 10:45 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Why have you got an Order by clause in the view?

If you want the rows t in a specific order, then you need to have the Order by in the outer select, rather than in the view.

Try removing it and see what happens.
Re: Problem related to view and long running query [message #400798 is a reply to message #400777] Wed, 29 April 2009 11:56 Go to previous messageGo to next message
tariqahsan
Messages: 13
Registered: February 2009
Junior Member
I haven't yet look into Chapter 19 about EXPLAIN PLAN. I will.
I will try to get the ORDER BY out of the view creation and see if it improves the query timing.

Thanks
Re: Problem related to view and long running query [message #400852 is a reply to message #400798] Wed, 29 April 2009 23:39 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
It's probably not a problem, but it would be worth checking whether you have a HWM problem.

Gather statistics with DBMS_STATS.GATHER_TABLE_STATS(), then check the three tables in the view USER_TABLES. Post the values of NUM_ROWS, BLOCKS, EMPTY_BLOCKS, AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN for each of the three tables.

Because of the ORDER BY, your timing of the first 1000 rows is not much help because all of the hard work (join and sort) is done before any rows are returned. If you run SET TIMING ON before the autotrace, that would be more helpful.

The other thing to note from the latest autotrace stats is that both the join and the sort are paging to disk (too big for memory).

Your data isn't that big (139MB join) - I'm surprised that it cannot be done in memory. You should discuss with your DBA the possibility of increasing your HASH_AREA_SIZE / SORT_AREA_SIZE or -if you are using it - PGA_AGGREGATE_TARGET.

Do a SHOW PARAMETER for all of these and post the results. Don't forget the table stats above as well.

Ross Leishman
Previous Topic: Sequence reduction
Next Topic: Passing partition name using a variable in insert statement.
Goto Forum:
  


Current Time: Wed Dec 07 11:01:32 CST 2016

Total time taken to generate the page: 0.08958 seconds