think of attribute VOP as the visual version of VEX (e.g. point wrangle, primitive wrangle etc), instead of typing code you can use nodes inside the VOP to create and modify attributes. They can both achieve the same results, some just prefer visual scripting than coding and vice versa. Just like the attribute wrangle (VEX), Attribute VOP can work on any type (e.g. points, primitives etc), you just have to specify what type you want the VOP to run over (you can even type "primitive VOP" when searching for nodes and you'll be able to place one straightaway instead of having to manually configure the default attribute VOP which by default is set to run over points).
'@P' is a local point attribute used to store the points' positions, and houdini needs that information in order to know where to place points in 3D space. This point attribute is writable meaning you can modify it, hence why when you apply noise to the '@P' in point VOP you see expected results. For primitives the '@P' (which stores the center of a primitive) is a read-only attribute, you can't directly modify it hence why you can't see any results when you apply noise in primitive VOP.
Sorry for the long reply, hope this answers your questions!