![]() ![]() SQL*Net message from dblink 1,004 0 61.02. SQL*Net message from client 1,004 0 15.69. It turned out that there was very little of interest in the session activity stats although the impact of SQL*Net compression is always worth a quick reminder ( here and here) on the other hand the wait events (specifically the wait counts) are most revealing:Įvent Waits Time_outs Csec Avg Csec Max Csec The two “snap” packages are some code I wrote a couple of decades ago to report changes in my session’s activity stats and wait events. ![]() The “feedback only” option is a 12c option that means the session doesn’t display the results and shows only the final “N rows selected” report. Prompt SQL*Plus 20,000 rows, arraysize 2000Īll I’ve done is select 20,000 rows from view all_objects in a remote database with two different settings for the array fetch size. Prompt SQL*Plus 20,000 rows, arraysize 20 For this type of testing it’s probably sensible to use two database rather than faking things with a loopback database link in case the loopback introduces some special features (a.k.a. It’s fairly easy to get some ideas by running a couple of queries and checking session activity stats and wait events – so here’s a little test conducted between a database running 12.2.0.1 and a database running 11.2.0.4. I don’t need to change them often but with the default values I don’t really have to.Precis: The effects of setting the SQL*Net SDU_SIZE with particular reference to db linksĪ recent thread on the Oracle developer forum raised the question of how data moves across a database link – is it row by row or array based (or other)? It’s a good question as it’s fairly common knowledge that a distributed query can be very much slower than the same query when executed on an equivalent set of local tables, so it’s worth having a little poke around to see if there’s anything about the way that data moves across the link that could be tweaked to improve performance. My actual script is quite a bit more complicated and has values for database size, file locations, etc. GRANT VIEW DEFINITION TO AS Īt this point I now have a script that I can use over and over again to create this database and apply the permissions. IF DATABASE_PRINCIPAL_ID('domain\abcdef') IS NULL CREATE USER FOR LOGIN WITH DEFAULT_SCHEMA = ĮXEC sp_addrolemember = 'db_execute_all_dbo', = 'domain\abcdef' ĮXEC sp_addrolemember = 'db_ddladmin', = 'domain\abcdef' ĮXEC sp_addrolemember = 'db_datareader', = 'domain\abcdef' ĮXEC sp_addrolemember = 'db_datawriter', = 'domain\abcdef' If you look back at the code you’ll see that they are set up like this: The green in the background is where the parameters are that are about to be filled in.Įach parameter has a name, a data type (that seems to be for display only), and a default value. When I use the shortcut it brings up the two parameters. It’s not one I use often so I need the help. You’ll notice I have a comment at the top to remind me of the shortcut to fill in the template parameters. IF DATABASE_PRINCIPAL_ID('') IS NULL CREATE USER FOR LOGIN WITH DEFAULT_SCHEMA = ĮXEC sp_addrolemember = 'db_execute_all_dbo', = '' ĮXEC sp_addrolemember = 'db_ddladmin', = '' ĮXEC sp_addrolemember = 'db_datareader', = '' ĮXEC sp_addrolemember = 'db_datawriter', = '' IF DATABASE_PRINCIPAL_ID('db_execute_all_dbo') IS NULL CREATE ROLE AUTHORIZATION
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |