Home » RDBMS Server » Performance Tuning » optimzer dynmic smpling
optimzer dynmic smpling [message #669004] Wed, 28 March 2018 08:59 Go to next message
elixir86
Messages: 12
Registered: March 2018
Location: Madison, Wisconsin
Junior Member
Hi Team,

GATHR_TbL_STS is taking a long time.

I researched online and found optimzer dynmic smpling has impact on performance.

How to find the current value of it, and change it thru sql plus, toad, or navigtr?

Report message to a moderator

Re: optimzer dynmic smpling [message #669005 is a reply to message #669004] Wed, 28 March 2018 09:07 Go to previous messageGo to next message
John Watson
Messages: 8961
Registered: January 2010
Location: Global Village
Senior Member
There is no relationship between the setting for OPTIMIZER_DYNAMIC_SAMPLING and the time required to execute DBMS_STATS.GATHER_TABLE_STATS. What is the reference you found that said that there is?

And, by the way, when you post please try to use as clear language as you can, without any SMS-type abbreviations. Properly written posts are easier to read and more likely to gain responses.

Report message to a moderator

Re: optimzer dynmic smpling [message #669006 is a reply to message #669005] Wed, 28 March 2018 10:22 Go to previous messageGo to next message
elixir86
Messages: 12
Registered: March 2018
Location: Madison, Wisconsin
Junior Member
I am trying to figure out the exhaustively long run times.

Report message to a moderator

Re: optimzer dynmic smpling [message #669007 is a reply to message #669006] Wed, 28 March 2018 10:27 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
elixir86 wrote on Wed, 28 March 2018 08:22
I am trying to figure out the exhaustively long run times.
one way to see where time is being spent is to enable SQL_TRACE=TRUE & the process trace file using TKPROF

Report message to a moderator

Re: optimzer dynmic smpling [message #669008 is a reply to message #669007] Wed, 28 March 2018 10:35 Go to previous messageGo to next message
elixir86
Messages: 12
Registered: March 2018
Location: Madison, Wisconsin
Junior Member
https://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams234.htm#REFRN10208

SQL_TRACE enables or disables the SQL trace facility. Setting this parameter to true provides information on tuning that you can use to improve performance. You can change the value using the DBMS_SYSTEM package.

Caution:

Using this initialization parameter to enable the SQL trace facility for the entire instance can have a severe performance impact. Enable the facility for specific sessions using the ALTER SESSION statement. If you must enable the facility on an entire production environment, then you can minimize performance impact by:
Maintaining at least 25% idle CPU capacity

Maintaining adequate disk space for the USER_DUMP_DEST location

Striping disk space over sufficient disks

See Also:

Oracle Database Performance Tuning Guide for more information about performance diagnostic tools
Note:

The SQL_TRACE parameter is deprecated. Oracle recommends that you use the DBMS_MONITOR and DBMS_SESSION packages instead. SQL_TRACE is retained for backward compatibility only.


Any additional suggestion is welcome!

Report message to a moderator

Re: optimzer dynmic smpling [message #669009 is a reply to message #669008] Wed, 28 March 2018 10:45 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
There is no need to mess with any parameter.
ALTER SESSION SET SQL_TRACE=TRUE; -- works fine & lasts a long time & yes, there are other ways to accomplish the same goal.
The fact remains if you want additional visibility where time is being spent, then you need to direct Oracle to provide those details.
Unless & until you really know where time is being spent, you are merely shooting in the dark & hoping to get lucky.
After you know where time is being spent, then you need to determine what, if anything, can be done to reduce that elapsed time.
HTH & YMMV!

Report message to a moderator

Re: optimzer dynmic smpling [message #669010 is a reply to message #669006] Wed, 28 March 2018 11:45 Go to previous messageGo to next message
John Watson
Messages: 8961
Registered: January 2010
Location: Global Village
Senior Member
elixir86 wrote on Wed, 28 March 2018 16:22
I am trying to figure out the exhaustively long run times.
Long run times of what? If it is executions of dbms_stats.gather_%_stats, the time is largely dependent on the options you specified and the size of the objects. However, why it would be a problem? Is it interfering with anything?

Report message to a moderator

Re: optimzer dynmic smpling [message #669011 is a reply to message #669004] Wed, 28 March 2018 12:13 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
elixir86 wrote on Wed, 28 March 2018 06:59
Hi Team,

GATHR_TbL_STS is taking a long time.


Post SQL & results that show a problem exists.

Report message to a moderator

Re: optimzer dynmic smpling [message #669017 is a reply to message #669010] Thu, 29 March 2018 02:25 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
John Watson wrote on Wed, 28 March 2018 17:45
elixir86 wrote on Wed, 28 March 2018 16:22
I am trying to figure out the exhaustively long run times.
Long run times of what? If it is executions of dbms_stats.gather_%_stats, the time is largely dependent on the options you specified and the size of the objects. However, why it would be a problem? Is it interfering with anything?

This.

I gathered stats on a ~30Tb database the other week in a little over 8 hours. I'll be surprised if it takes "a long time" on a reasonable size of data and with reasonable gather options.

Report message to a moderator

Re: optimzer dynmic smpling [message #669019 is a reply to message #669017] Thu, 29 March 2018 02:34 Go to previous message
John Watson
Messages: 8961
Registered: January 2010
Location: Global Village
Senior Member
Unless you give some basic information, no-one can help. Such as:
The command you are running.
How long it takes.
How long you want it to take.
The description of the table.
What problem this "long run time" is causing you.

--update Sorry, RC, I confused you with OP.

[Updated on: Thu, 29 March 2018 02:36]

Report message to a moderator

Previous Topic: Oracle Golden gate- Compression tables
Next Topic: Incosistent Wait Event
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Wed Dec 11 16:03:57 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.