OraFAQ Forum: Performance Tuning » query recommedation

Home » RDBMS Server » Performance Tuning » query recommedation (Oracle 10.2.0.4 solaris 2.10)

Show: Today's Messages :: Show Polls :: Message Navigator
E-mail to friend

query recommedation [message #563756]

Thu, 16 August 2012 15:31

BeefStu
Messages: 184
Registered: October 2011

Senior Member

I have an APP that truncates tables and loads data, which in turn makes the stats stale. I ran the query advisor (see attachment) and of course it ecommends running stats or accept a profile.

I am a bit hestiant to accept a profile since I don't have much experience with that. As for running stats I really don't want to do that as it may cause a load on my DB.

In turn, I would like to consider having my APP team change the query to pass a hint to use the best query plan.

Can somebody please help me with the syntax to pass the hint to emulate good attached plan? Or is this a bad way to proceed?

Thanks to all who answer


select /* INDEX FAST FULL SCAN PK_PLACEMENT_REQUEST_QUEUE */
sum(lastshares) as "ROSEN"
from nyeo.fix_exec_reports fer, nyeo.placement_request_queue q,
nyeo.nyeo_block_control bc
where fer.clordid = q.sequence_number
and q.blockid = bc.blockid
and upper(bc.deskname) like '%ROSEN%'

Attachment: ORAEEOP.txt
(Size: 5.64KB, Downloaded 24 times)

Report message to a moderator

Re: query recommedation [message #563778 is a reply to message #563756]

Fri, 17 August 2012 01:31

John Watson
Messages: 3181
Registered: January 2010
Location: Global Village

Senior Member

Are you asking "what is the index fast full scan hint"? If so,

orcl> set autot traceonly exp
orcl> select empno from emp;

Execution Plan
----------------------------------------------------------
Plan hash value: 179099197

---------------------------------------------------------------------------
| Id  | Operation        | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT |        |    14 |    56 |     1   (0)| 00:00:01 |
|   1 |  INDEX FULL SCAN | PK_EMP |    14 |    56 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------

orcl> select /*+ index_ffs(emp pk_emp) */ empno from emp;

Execution Plan
----------------------------------------------------------
Plan hash value: 366039554

---------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |    14 |    56 |     2   (0)| 00:00:01 |
|   1 |  INDEX STORAGE FAST FULL SCAN| PK_EMP |    14 |    56 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

orcl>

Report message to a moderator

Re: query recommedation [message #563781 is a reply to message #563756]

Fri, 17 August 2012 01:36

Michel Cadot
Messages: 54719
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Did you read the documentation about hints?
Given your skills, I recommend you to NOT use hints but learn SQL.

Regards
Michel

Report message to a moderator

Previous Topic:	how to find all indexes scripts in particular schema
Next Topic:	Re: Get Unique row number for list of values (split from unrelated thread by bb)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jun 20 02:15:14 CDT 2013

Total time taken to generate the page: 0.17663 seconds