Home » RDBMS Server » Server Utilities » TOP N SQL in dbms_workload_repository, (Oracle 10.2.0, OEL 4)
TOP N SQL in dbms_workload_repository, [message #567327] Wed, 26 September 2012 10:49 Go to next message
thiyagusham
Messages: 112
Registered: April 2012
Location: Chennai
Senior Member
Hi to all;

SQL > desc dbms_workload_repository

I googled Mostly i got info about retention , interval , dbid ..
I could n't understand purpose of TOPnsql ..
topnsql IN NUMBER DEFAULT NULL, 



Quote:

"topnsql IN NUMBER DEFAULT NULL, "
What is the special pupose of " TOP N SQL "DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS"
Someone setting topnsql =100 what it does ??


Thanks and Regards
Re: TOP N SQL in dbms_workload_repository, [message #567328 is a reply to message #567327] Wed, 26 September 2012 11:31 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From documentation:

Quote:
topnsql
* If NUMBER: Top N SQL size. The number of Top SQL to flush for each SQL criteria (Elapsed Time, CPU Time, Parse Calls, Shareable Memory, Version Count). The value for this setting will not be affected by the statistics/flush level and will override the system default behavior for the AWR SQL collection. The setting will have a minimum value of 30 and a maximum value of 100000000. Specifying NULL will keep the current setting.
* If VARCHAR2: Users are allowed to specify the following values: (DEFAULT, MAXIMUM, N), where N is the number of Top SQL to flush for each SQL criteria. Specifying DEFAULT will revert the system back to the default behavior of Top 30 for statistics level TYPICAL and Top 100 for statistics level ALL. Specifying MAXIMUM will cause the system to capture the complete set of SQL in the cursor cache. Specifying the number N is equivalent to setting the Top N SQL with the NUMBER type. Specifying NULL for this argument will keep the current setting.


Do this answer your question?

Regards
Michel
Previous Topic: load query result in ms-excel sheet
Next Topic: SQL Loader with WHEN and DISCARD
Goto Forum:
  


Current Time: Thu Mar 28 11:23:10 CDT 2024