Home » Other » Community Hangout » make_query_fast hint  () 1 Vote
make_query_fast hint [message #652661] Wed, 15 June 2016 15:16 Go to next message
Barbara Boehmer
Messages: 8779
Registered: November 2002
Location: California, USA
Senior Member
This wasn't on our forums, but I just had to share. I am used to people comically suggesting the make_query_fast hint and such, but I don't think I have ever seen anybody not realize that it was a joke and actually test it and post the results.

https://community.oracle.com/thread/3940746

Then it continues to get better. No index. Still no query or explain plant posted. Waiting to see what happens next.

[Updated on: Wed, 15 June 2016 15:18]

Report message to a moderator

Re: make_query_fast hint [message #652662 is a reply to message #652661] Wed, 15 June 2016 15:19 Go to previous messageGo to next message
John Watson
Messages: 7183
Registered: January 2010
Location: Global Village
Senior Member
I always tell people that they should write all the SQL keywords in upper case.
Re: make_query_fast hint [message #652663 is a reply to message #652662] Wed, 15 June 2016 15:21 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8779
Registered: November 2002
Location: California, USA
Senior Member
John Watson wrote on Wed, 15 June 2016 13:19
I always tell people that they should write all the SQL keywords in upper case.


Actually, I frequently do that. I got used to the standards in documentation and find it easier to read.
Re: make_query_fast hint [message #652664 is a reply to message #652663] Wed, 15 June 2016 15:22 Go to previous messageGo to next message
John Watson
Messages: 7183
Registered: January 2010
Location: Global Village
Senior Member
If you list the tables in alphabetical order that should help too Smile
Re: make_query_fast hint [message #652680 is a reply to message #652664] Thu, 16 June 2016 03:06 Go to previous messageGo to next message
gazzag
Messages: 906
Registered: November 2010
Location: Bristol, UK
Senior Member
It's an undocumented, secret parameter but just between friends:
SQL> ALTER SYSTEM SET _go_faster = 'YES' SCOPE = SPFILE;
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;
Re: make_query_fast hint [message #652683 is a reply to message #652680] Thu, 16 June 2016 03:25 Go to previous messageGo to next message
John Watson
Messages: 7183
Registered: January 2010
Location: Global Village
Senior Member
You can also reduce the temperature in the data centre. As it drops towards 0K, the superconductivity effect raises the machine's clock speed.
Re: make_query_fast hint [message #652757 is a reply to message #652683] Sat, 18 June 2016 01:38 Go to previous messageGo to next message
Frank Naude
Messages: 4531
Registered: April 1998
Senior Member
Oracle can be terribly confusing. Note how the MAKE_QUERY_FAST hint actually helped Smile

Quote:
Without any hint query takes 735.417 Seconds
ALL_ROWS it takes 334.225 Seconds
NO_MERGE it takes 702.571 Seconds
MAKE_QUERY_FAST it takes 453.109 Seconds


Can you blame the guy for believing it? The cache effect is not immediately obvious to newbies.

Regards.
Frank
Re: make_query_fast hint [message #658621 is a reply to message #652757] Thu, 22 December 2016 00:32 Go to previous messageGo to next message
rleishman
Messages: 3727
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Sorry to resurrect the dead, but this is my first visit back for a long time...

/*+ MAKE_QUERY_FAST */

You laugh, but in SQL Server, it exists (well, 'FAST' does). It's their version of the FIRST_ROWS hint. First time I saw it, I thought of this old Oracle meme.

Ross Leishman
Re: make_query_fast hint [message #658974 is a reply to message #658621] Tue, 03 January 2017 02:51 Go to previous message
Roachcoach
Messages: 1498
Registered: May 2010
Location: UK
Senior Member
The other reason this sometimes works is when there's a series of legacy and now outdated hints in the code, someone slaps an invalid one at the start, takes out all the ones after it by accident and voilla, magically it is better because the old hints are now not applied.

SQL>  explain plan for select /*+ full(t) */ count(*) from system.t t
  2  /

Explained.

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2966233522

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |   148   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T    | 33984 |   148   (0)| 00:00:01 |
-------------------------------------------------------------------

9 rows selected.

SQL>  explain plan for select /*+ GOD_PLEASE_GO_FASTER() full(t) */ count(*) from system.t t
  2  /

Explained.

SQL>  select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 325870156

----------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Cost (%CPU)| Time     |
----------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |     1 |    22   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE       |      |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| IDX1 | 33984 |    22   (0)| 00:00:01 |
----------------------------------------------------------------------

9 rows selected.

SQL>

[Updated on: Tue, 03 January 2017 02:56]

Report message to a moderator

Previous Topic: Come to Vietnam, why and why not?
Next Topic: Integer Overflow - Michel Cadot
Goto Forum:
  


Current Time: Wed Dec 13 03:30:42 CST 2017

Total time taken to generate the page: 0.08382 seconds