BOB: Business Objects Board
Not endorsed by or affiliated with SAP

Register | Login 

Want to sponsor BOB? 
Want to sponsor BOB? (Opens a new window)  

General Notice: No events within the next 45 days.

Oracle hints


 
Search this topic... | Search Semantic Layer / Universe Designer... | Search Box
Register or Login to Post    Forum Index -> Semantic Layer / Universe Designer  Previous TopicPrint TopicNext Topic
Author Message
Debbie
Forum Addict
Forum Addict



Joined: 01 Mar 2005

Posts: 2778
Location: Cheshire, England


flag
PostPosted: Mon Oct 07, 2019 7:26 am 
Post subject: Oracle hints

Can anyone explain hints to me in non-DBA speak, please?

I'm running an SQL query (to populate a dashboard). It joins 10 large tables (all except one with inner joins - the single outer join is against a small indexed lookup table). I'm filtering the data very tightly, so my final result set is only around 2.5k rows.

I can run this from my pc in SQL Developer with a admin account on the data warehouse. It runs in a couple of minutes with no issues.

If I try to run it via a script from a server, it gives me tablespace errors and falls over. I have no idea why. I am running more complex queries with bigger results with no problems at all.

I know I need to tune/optimise the SQL somehow, but I'm not a DBA and don't really understand what I am doing. I've tried adding a PARALLEL hint and an ORDERED hint - but I don't know what order the tables ought to be in to start with. Our resident Oracle DBA/guru retired in the summer.

The current error is
Code:
ERROR: 3   
OraOLEDB: ORA-12801: error signaled in parallel query server P466
ORA-01652: unable to extend temp segment by 64 in tablespace TEMP

Any advice gratefully received!

Debbie

ETA: Fixed (after two days of googling) by adding a PARALLEL hint, plus rewriting all the joins and filters into ANSI-92 syntax. It doesn't explain why other huge queries still run in non-ANSI-92, but at least it's working ...
Back to top
joepeters
Forum Fanatic
Forum Fanatic



Joined: 29 Aug 2002

Posts: 6625
Location: Connecticut, USA


flag
PostPosted: Tue Oct 08, 2019 7:09 am 
Post subject: Re: Oracle hints

It could just be luck, and not from the changes you made to the query. The easy answer is to add more temp space, but your DBA should do the analysis for you.
Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> Semantic Layer / Universe Designer  Previous TopicPrint TopicNext Topic
Page 1 of 1 All times are GMT - 5 Hours
 
Jump to:  

Index | About | FAQ | RAG | Privacy | Search |  Register |  Login 

Get community updates via Twitter:

Not endorsed by or affiliated with SAP
Powered by phpBB © phpBB Group
Generated in 0.0269 seconds using 17 queries. (SQL 0.0024 Parse 0.0008 Other 0.0236)
CCBot/2.0 (https://commoncrawl.org/faq/)
Hosted by ForumTopics.com | Terms of Service
phpBB Customizations by the phpBBDoctor.com
Shameless plug for MomentsOfLight.com Moments of Light Logo