- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- Re: improving oracle sql statement
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-08-2003 11:31 PM
тАО09-08-2003 11:31 PM
Re: improving oracle sql statement
First, the trivial:
From SQL*PLus, SET COLSEP "
select a||'
Second, your query is picking rows based on F4111.ILDCT and F46011.IQUWUM. Are these cols indexed? If the number of distinct values in each is low you should consider a bitmap index.
Other than that, once you've exhausted the explain plan and trace options, I should contact oracle support (assuming you have a contract).
-- Graham
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-09-2003 12:54 AM
тАО09-09-2003 12:54 AM
Re: improving oracle sql statement
Tkprof will give you much more information about where the bulk of the work is being done, then you can focus your tuning efforts there. As with any performance problem, change one thing at a time and measure with further tracing. If you can, set up a test system with a proportionally smaller version of the DB.
A couple of other points / things to try:
* Run the query at a quieter time - if there is loads of DML activity on the queried data performance will be severely hit because Oracle will have to keep pulling blocks out of rollback (and you run the risk of snapshot too old problems).
* Omit the complicated date calculations you're doing, just for comparison sake.
* Try doing some of the queries as temporary views (ie nested select statements in the from clause), especially the query on F4111 as its your largest table. That has helped me in the past.
* Measure other DB performance indicators such as buffer cache hit ratio while the query is being run - you may need to increase your SGA size.
And try posting to the comp.databases.oracle.server - there are many more SQL experts there!
Regards,
Steve
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-09-2003 02:09 AM
тАО09-09-2003 02:09 AM
Re: improving oracle sql statement
you should call SQL Trace and TKPROF to the rescue.
For more details see: http://otn.oracle.com/doc/oracle8i_816/server.816/a76992/ch14_str.htm
If possible post your output so that we can have a look.
hope this helps!
regards
Yogeeraj
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-09-2003 03:39 AM
тАО09-09-2003 03:39 AM
Re: improving oracle sql statement
This one only took 4 hours because I ran it on a test machine that isn't used much - about the same number of records though.
I did find out the following from looking at the where clause - the *LITM compares char(25)
and the 2 *MCU compares are char(12) - those might be expensive.
Any other insights? You guys are great!
SELECT F4111.ILLITM, F4111.ILMCU, F4111.ILDCT,
to_char(to_date(substr(to_char(F4111.ILTRDJ),1,3)+1900||substr(to_char(F4111.ILTRDJ),4,3),'YYYYDDD'),'YYYYMMDD') ,
F4111.ILTRQT, F4111.ILPAID, F4102.IBSRP6, F4801T.WALINE, F46011.IQUOM, F46011.IQGWEI, F46011.IQUWUM, F4111.ILRCD
FROM JDEDATA.F4111 , JDEDATA.F4102 , JDEDATA.F4801T , JDEDATA.F46011
WHERE
F4111.ILLITM = F4102.IBLITM AND
F4111.ILMCU = F4102.IBMCU AND
F4111.ILDOCO = F4801T.WADOCO AND
F4111.ILMCU = F46011.IQMCU AND
F4111.ILITM = F46011.IQITM AND (F4111.ILDCT = 'IC' OR F4111.ILDCT = 'IS') AND
F46011.IQUWUM = 'KG' AND F46011.IQUOM = 'EA'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 19827 10914.94 14058.22 253810 865676081 4 297376
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 19829 10914.94 14058.22 253810 865676081 4 297376
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 5
Rows Row Source Operation
------- ---------------------------------------------------
297376 NESTED LOOPS
325541 NESTED LOOPS
325541 NESTED LOOPS
31661 TABLE ACCESS FULL F46011
357200 TABLE ACCESS BY INDEX ROWID F4111
1312360 INDEX RANGE SCAN (object id 10281)
651080 TABLE ACCESS BY INDEX ROWID F4102
1901694724 INDEX RANGE SCAN (object id 7132)
297376 TABLE ACCESS BY INDEX ROWID F4801T
622916 INDEX UNIQUE SCAN (object id 8009)
********************************************************************************
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-09-2003 04:55 AM
тАО09-09-2003 04:55 AM
Re: improving oracle sql statement
first of all, it would be of help to know which indexes are defined.
SQL>
select TABLE_NAME, INDEX_NAME, COLUMN_POSITION, substr(column_name,1,30)
from DBA_IND_COLUMNS
where table_name in ( 'F46011','F4801T','F4102','F4111' )
order by TABLE_NAME, INDEX_NAME, COLUMN_POSITION ;
Second make sure that all tables and indexes are analyzed if possible with COMPUTE STATISTICS.
Third let's check, how the data is distributed for the indexed columns:
select a.TABLE_NAME, a.COLUMN_NAME, a.num_distinct
from dba_TAB_COL_STATISTICS a
where a.table_name in ( 'F46011','F4801T','F4102','F4111' )
and a.column_name in
( select distinct b.column_name from DBA_IND_COLUMNS b where b.table_name=a.table_name);
From the explain, one would suggest, that the FULL SCAN on F46011 ist the disturbing one (which is most likely) but it does not have to be this one.
Esp. it would be interesting to know, how many rows you get for
select count(*) from F46011 where
F46011.IQUWUM = 'KG' AND F46011.IQUOM = 'EA' ;
If this is significantly small against the 39508 rows, go for a combined index on those two columns and if it does not reduce the access time specify this index via hint on your query.
Good hunting
Volker
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-09-2003 08:07 AM
тАО09-09-2003 08:07 AM
Re: improving oracle sql statement
> 1901694724 INDEX RANGE SCAN (object id 7132)
Ouch... almost 2 billion rows compared when table F4102 is brought into the join.
For comparison sake how much faster is the query if you remove F4102 (there's only one selected field from this table)?
What fields on this table are indexed?
I'd really start experimenting on your test system. Break down the query into smaller chunks and work out exactly where all the work is being done; create the index others have suggested; try to change the join order (with the /* +ORDERED */ hint) so that F4102 is joined in last; try using star queries...
Regards,
Steve
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-09-2003 09:50 AM
тАО09-09-2003 09:50 AM
Re: improving oracle sql statement
This seems extremly expensive, esp. as F4102 has only 160.000 rows !
Since F4111 is the biggest one, I'd like to know in addition how many rows your get on
select count(*) from f4111
where ILDCT = 'IC';
select count(*) from f4111
where ILDCT = 'IS';
Thanks
Volker
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-10-2003 09:07 AM
тАО09-10-2003 09:07 AM
Re: improving oracle sql statement
which looks a lot better. I also tried someone's suggestion to make a small table to do the julian conversions via lookup - this turned out to be a very bad idea. I ran this test first and it ran for over 12 hours. It never finished because all the developers were yelling at me that the test system was hardly moving. I'll try it again tonight and use the function. I did find a much simpler version, so we will see.
Thanks for all your help ??? I???ll tell you what happens tomorrow.
---
OPERATIONS OPTIONS OBJECT_NAME
------------------------- --------------- -----------------------
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS
TABLE ACCESS BY INDEX ROWID F46011
INDEX RANGE SCAN F46011_V05
TABLE ACCESS BY INDEX ROWID F4111
INDEX RANGE SCAN F4111_6
TABLE ACCESS BY INDEX ROWID F4102
INDEX RANGE SCAN F4102_V01
TABLE ACCESS BY INDEX ROWID F4801T
INDEX UNIQUE SCAN F4801T_PK
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-10-2003 09:26 AM
тАО09-10-2003 09:26 AM
Re: improving oracle sql statement
just curious, as you did not tell how many rows your query returns :-)
If this is a whole lot ( +Mios ), there is another one:
Index on F4801T.WADOCO,F4801T.WALINE.
This is not obvious, because the Uniqe scan suggests that this join is good. But as you only need one field from this table (second largest), this index would turn the index uniqe scan and the following table access into a index fast full scan (without table access at all).
Statisticly this should cut your gets on this table by two. If the average row-length on this table is big it could be even better, because disk reads should go down as well.
...but only worth in overall number of rows for your query is large !
Good hunting once more
Volker
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-10-2003 09:37 AM
тАО09-10-2003 09:37 AM
Re: improving oracle sql statement
a lookup table seemed to me a good idea, sorry!
In any case,
glad to see that indexes are working.
Did you also refreshed the statistics ?
Massimo